1
2
3
引用：
*mysql的jar文件
*Spring_HOME/lib/poi/*.jar
2、编写数据库链接类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
package com.zzg.db
import java.sql.Connection
import java.sql.DriverManager
public class DbUtils {
private static Connection conn
static {
try {
Class.forName("com.mysql.jdbc.Driver")
conn = DriverManager.getConnection("jdbc:mysql://localhost/test","root","123456")
} catch (Exception e) {
e.printStackTrace()
}
}
public static Connection getConn() {
return conn
}
public static void setConn(Connection conn) {
DbUtils.conn = conn
}
}
3、编写数据库 *** 作类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
package com.zzg.db
import java.sql.Connection
import java.sql.PreparedStatement
import java.sql.SQLException
public class ExcuteData {
private PreparedStatement pstmt
public boolean ExcuData(String sql) {
Connection conn = DbUtils.getConn()
boolean flag=false
try {
pstmt = conn.prepareStatement(sql)
flag=pstmt.execute()
} catch (SQLException e) {
e.printStackTrace()
}
return flag
}
}
4、编写Excel表格实体类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
package com.zzg.model
public class TableCell {
private String _name
private String _value
public String get_name() {
return _name
}
public void set_name(String _name) {
this._name = _name
}
public String get_value() {
return _value
}
public void set_value(String _value) {
this._value = _value
}
}
5、编写主键生成方法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
package com.zzg.util
import java.text.SimpleDateFormat
import java.util.Date
import java.util.Random
public class GenericUtil {
public static String getPrimaryKey()
{
String primaryKey
primaryKey = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())
Random r = new Random()
primaryKey +=r.nextInt(100000)+100000
return primaryKey
}
}
6、编写Excel *** 作类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
package com.zzg.deployData
import java.io.File
import java.io.FileInputStream
import java.io.FileNotFoundException
import java.io.IOException
import java.io.Serializable
import java.util.ArrayList
import java.util.List
import org.apache.poi.hssf.usermodel.HSSFCell
import org.apache.poi.hssf.usermodel.HSSFRow
import org.apache.poi.hssf.usermodel.HSSFSheet
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import com.zzg.db.ExcuteData
import com.zzg.model.TableCell
import com.zzg.util.GenericUtil
public class OperExcel<T extends Serializable>{
private HSSFWorkbook workbook
private String tableName
private Class<T>type
private String sheetName
public OperExcel(File excelFile, String tableName, Class<T>type,
String sheetName) throws FileNotFoundException,
IOException {
workbook = new HSSFWorkbook(new FileInputStream(excelFile))
this.tableName = tableName
this.type = type
this.sheetName = sheetName
InsertData()
}
// 向表中写入数据
public void InsertData() {
System.out.println("yyy")
ExcuteData excuteData = new ExcuteData()
List<List>datas = getDatasInSheet(this.sheetName)
// 向表中添加数据之前先删除表中数据
String strSql = "delete from " + this.tableName
excuteData.ExcuData(strSql)
// 拼接sql语句
for (int i = 1i <datas.size()i++) {
strSql = "insert into " + this.tableName + "("
List row = datas.get(i)
for (short n = 0n <row.size()n++) {
TableCell excel = (TableCell) row.get(n)
if (n != row.size() - 1)
strSql += excel.get_name() + ","
else
strSql += excel.get_name() + ")"
}
strSql += " values ("
for (short n = 0n <row.size()n++) {
TableCell excel = (TableCell) row.get(n)
try {
if (n != row.size() - 1) {
strSql += getTypeChangeValue(excel) + ","
} else
strSql += getTypeChangeValue(excel) + ")"
} catch (RuntimeException e) {
e.printStackTrace()
} catch (Exception e) {
e.printStackTrace()
}
}
//执行sql
excuteData.ExcuData(strSql)
}
}
/**
* 获得表中的数据
* @param sheetName 表格索引(EXCEL 是多表文档,所以需要输入表索引号)
* @return 由LIST构成的行和表
*/
public List<List>getDatasInSheet(String sheetName) {
List<List>result = new ArrayList<List>()
// 获得指定的表
HSSFSheet sheet = workbook.getSheet(sheetName)
// 获得数据总行数
int rowCount = sheet.getLastRowNum()
if (rowCount <1) {
return result
}
// 逐行读取数据
for (int rowIndex = 0rowIndex <rowCountrowIndex++) {
// 获得行对象
HSSFRow row = sheet.getRow(rowIndex)
if (row != null) {
List<TableCell>rowData = new ArrayList<TableCell>()
// 获得本行中单元格的个数
int columnCount = sheet.getRow(0).getLastCellNum()
// 获得本行中各单元格中的数据
for (short columnIndex = 0columnIndex <columnCountcolumnIndex++) {
HSSFCell cell = row.getCell(columnIndex)
// 获得指定单元格中数据
Object cellStr = this.getCellString(cell)
TableCell TableCell = new TableCell()
TableCell.set_name(getCellString(
sheet.getRow(0).getCell(columnIndex)).toString())
TableCell.set_value(cellStr == null ? "" : cellStr
.toString())
rowData.add(TableCell)
}
result.add(rowData)
}
}
return result
}
/**
* 获得单元格中的内容
*
* @param cell
* @return result
*/
protected Object getCellString(HSSFCell cell) {
Object result = null
if (cell != null) {
int cellType = cell.getCellType()
switch (cellType) {
case HSSFCell.CELL_TYPE_STRING:
result = cell.getStringCellValue()
break
case HSSFCell.CELL_TYPE_NUMERIC:
result = cell.getNumericCellValue()
break
case HSSFCell.CELL_TYPE_FORMULA:
result = cell.getNumericCellValue()
break
case HSSFCell.CELL_TYPE_ERROR:
result = null
break
case HSSFCell.CELL_TYPE_BOOLEAN:
result = cell.getBooleanCellValue()
break
case HSSFCell.CELL_TYPE_BLANK:
result = null
break
}
}
return result
}
// 根据类型返回相应的值
@SuppressWarnings("unchecked")
public String getTypeChangeValue(TableCell excelElement)
throws RuntimeException, Exception {
String colName = excelElement.get_name()
String colValue = excelElement.get_value()
String retValue = ""
if (colName.equals("id")) {
retValue = "'" + GenericUtil.getPrimaryKey() + "'"
return retValue
}
if (colName == null) {
retValue = null
}
if (colName.equals("class_createuser")) {
retValue = "yaa101"
return "'" + retValue + "'"
}
retValue = "'" + colValue + "'"
return retValue
}
}
7、编写调用 *** 作Excel类的方法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
package com.zzg.deployData
import java.io.File
import java.io.FileNotFoundException
import java.io.IOException
public class DeployData {
private File fileOut
public void excute(String filepath) {
fileOut = new File(filepath)
this.deployUserInfoData()
}
public void deployUserInfoData() {
try {
new OperExcel(fileOut, "test", Object.class, "Sheet1")
} catch (FileNotFoundException e) {
e.printStackTrace()
} catch (IOException e) {
e.printStackTrace()
}
}
}
8、编写客户端
1
2
3
4
5
6
7
8
9
10
package com.zzg.client
import com.zzg.deployData.DeployData
public class DeployClient {
public static void main(String[] args) {
DeployData deployData = new DeployData()
deployData.excute("D://test.xls")
}
}
一般通过邮件附件发送文件的方式分享excel表格，想把一个excel表格分享给更多人时，只要把表格嵌入在网页里将是一个再好不过的方法。我们在后台对excle表格里的数据进行修改后，网页上嵌入的表格也可以自动更新.
具体步骤
注册一个Zoho Docs账号，将你的excel表格上传。
在Zoho Docs里打开上传完成的excel表格，点击共享,嵌入。
在d出的窗口中，获取嵌入代码。
将获取的嵌入代码复制到网页源代码里，你可以定义excel表格放置的位置。
这样，我们就成功地将这个excel表格嵌入到了网页里。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）