实现的功能：
1、添加jxl.jarmysql-connector-java.1.7-bin.jar包到项目的lib目录下-
2、Excel文件目录:D://book.xls
3、数据库名：javenforexcel
4、表名:stu
5、编写类：连接mysql的字符串方法、插入的方法、实体类--
表结构如下 ：
连接数据库的工具类
|
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
|
package
com.javen.db;
import
java.sql.Connection;
import
java.sql.DriverManager;
import
java.sql.PreparedStatement;
import
java.sql.ResultSet;
import
java.sql.SQLException;
public
class
DBhepler {
/*String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String url = "jdbc:sqlserver://127.0.0.1;DatabaseName=javenforexcel";*/
String driver =
"com.mysql.jdbc.Driver"
;
//String url = "jdbc:mysql://127.0.0.1:3306/javenforexcel?useUnicode=true&characterEncoding=UTF8&autoReconnect=true&autoReconnectForPools=true"; //这段代码的话是防止从Excel导入数据到数据库时乱码的
Connection con =
null
;
ResultSet res =
null
;
public
void
DataBase() {
try
{
Class.forName(driver);
con = DriverManager.getConnection(url,
"root"
,
"root"
);
}
catch
(ClassNotFoundException e) {
// TODO Auto-generated catch block
System.err.println(
"装载 JDBC/ODBC 驱动程序失败。"
);
e.printStackTrace();
}
catch
(SQLException e) {
// TODO Auto-generated catch block
System.err.println(
"无法连接数据库"
);
e.printStackTrace();
}
}
// 查询
public
ResultSet Search(String sql, String str[]) {
DataBase();
try
{
PreparedStatement pst =con.prepareStatement(sql);
if
(str !=
null
) {
for
(
int
i =
0
; i < str.length; i++) {
pst.setString(i +
1
, str[i]);
}
}
res = pst.executeQuery();
}
catch
(Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return
res;
}
// 增删修改
public
int
AddU(String sql, String str[]) {
int
a =
0
;
DataBase();
try
{
PreparedStatement pst = con.prepareStatement(sql);
if
(str !=
null
) {
for
(
int
i =
0
; i < str.length; i++) {
pst.setString(i +
1
, str[i]);
}
}
a = pst.executeUpdate();
}
catch
(Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return
a;
}
}
|
表的实体如下
|
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
|
package
com.javen.entity;
/**
* @author Javen
* @Email zyw205@gmail.com
*
*/
public
class
StuEntity {
private
int
id;
private
String name;
private
String sex;
private
int
num;
public
StuEntity() {
}
public
StuEntity(
int
id, String name, String sex,
int
num) {
this
.id = id;
this
.name = name;
this
.sex = sex;
this
.num = num;
}
@Override
public
String toString() {
return
"StuEntity [id="
+ id +
", name="
+ name +
", sex="
+ sex
+
", num="
+ num +
"]"
;
}
public
int
getId() {
return
id;
}
public
void
setId(
int
id) {
this
.id = id;
}
public
String getName() {
return
name;
}
public
void
setName(String name) {
this
.name = name;
}
public
String getSex() {
return
sex;
}
public
void
setSex(String sex) {
this
.sex = sex;
}
public
int
getNum() {
return
num;
}
public
void
setNum(
int
num) {
this
.num = num;
}
}
|
Java实现Excel导入数据核心类读取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
|
package
com.javen.service;
import
java.io.File;
import
java.sql.ResultSet;
import
java.sql.SQLException;
import
java.util.ArrayList;
import
java.util.List;
import
jxl.Sheet;
import
jxl.Workbook;
import
com.javen.db.DBhepler;
import
com.javen.entity.StuEntity;
/**
* @author Javen
* @Email zyw205@gmail.com
*
*/
public
class
StuService {
/**
* 查询stu表中所有的数据
* @return
*/
public
static
List<StuEntity> getAllByDb(){
List<StuEntity> list=
new
ArrayList<StuEntity>();
try
{
DBhepler db=
new
DBhepler();
String sql=
"select * from stu"
;
ResultSet rs= db.Search(sql,
null
);
while
(rs.next()) {
int
id=rs.getInt(
"id"
);
String name=rs.getString(
"name"
);
String sex=rs.getString(
"sex"
);
int
num=rs.getInt(
"num"
);
//System.out.println(id+" "+name+" "+sex+ " "+num);
list.add(
new
StuEntity(id, name, sex, num));
}
}
catch
(SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return
list;
}
/**
* 查询指定目录中电子表格中所有的数据
* @param file 文件完整路径
* @return
*/
public
static
List<StuEntity> getAllByExcel(String file){
List<StuEntity> list=
new
ArrayList<StuEntity>();
try
{
Workbook rwb=Workbook.getWorkbook(
new
File(file));
Sheet rs=rwb.getSheet(
"Test Shee 1"
);
//或者rwb.getSheet(0)
int
clos=rs.getColumns();
//得到所有的列
int
rows=rs.getRows();
//得到所有的行
System.out.println(clos+
" rows:"
+rows);
for
(
int
i =
1
; i < rows; i++) {
for
(
int
j =
0
; j < clos; j++) {
//第一个是列数，第二个是行数
String id=rs.getCell(j++, i).getContents();
//默认最左边编号也算一列 所以这里得j++
String name=rs.getCell(j++, i).getContents();
String sex=rs.getCell(j++, i).getContents();
String num=rs.getCell(j++, i).getContents();
System.out.println(
"id:"
+id+
" name:"
+name+
" sex:"
+sex+
" num:"
+num);
list.add(
new
StuEntity(Integer.parseInt(id), name, sex, Integer.parseInt(num)));
}
}
}
catch
(Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return
list;
}
/**
* 通过Id判断是否存在
* @param id
* @return
*/
public
static
boolean
isExist(
int
id){
try
{
DBhepler db=
new
DBhepler();
ResultSet rs=db.Search(
"select * from stu where id=?"
,
new
String[]{id+
""
});
if
(rs.next()) {
return
true
;
}
}
catch
(SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return
false
;
}
public
static
void
main(String[] args) {
/*List<stuentity> all=getAllByDb();
for (StuEntity stuEntity : all) {
System.out.println(stuEntity.toString());
}*/
System.out.println(isExist(
1
));
}
}
|
数据的数据导入到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
|
package
com.javen.excel;
import
java.io.File;
import
java.util.List;
import
com.javen.entity.StuEntity;
import
com.javen.service.StuService;
import
jxl.Workbook;
import
jxl.write.Label;
import
jxl.write.WritableSheet;
import
jxl.write.WritableWorkbook;
public
class
TestDbToExcel {
public
static
void
main(String[] args) {
try
{
WritableWorkbook wwb =
null
;
// 创建可写入的Excel工作簿
File file=
new
File(fileName);
if
(!file.exists()) {
file.createNewFile();
}
//以fileName为文件名来创建一个Workbook
wwb = Workbook.createWorkbook(file);
// 创建工作表
WritableSheet ws = wwb.createSheet(
"Test Shee 1"
,
0
);
//查询数据库中所有的数据
List<StuEntity> list= StuService.getAllByDb();
//要插入到的Excel表格的行号，默认从0开始
Label labelId=
new
Label(
0
,
0
,
"编号(id)"
);
//表示第
Label labelName=
new
Label(
1
,
0
,
"姓名(name)"
);
Label labelSex=
new
Label(
2
,
0
,
"性别(sex)"
);
Label labelNum=
new
Label(
3
,
0
,
"薪水(num)"
);
ws.addCell(labelId);
ws.addCell(labelName);
ws.addCell(labelSex);
ws.addCell(labelNum);
for
(
int
i =
0
; i < list.size(); i++) {
Label labelId_i=
new
Label(
0
, i+
1
, list.get(i).getId()+
""
);
Label labelName_i=
new
Label(
1
, i+
1
, list.get(i).getName());
Label labelSex_i=
new
Label(
2
, i+
1
, list.get(i).getSex());
Label labelNum_i=
new
Label(
3
, i+
1
, list.get(i).getNum()+
""
);
ws.addCell(labelId_i);
ws.addCell(labelName_i);
ws.addCell(labelSex_i);
ws.addCell(labelNum_i);
}
//写进文档
wwb.write();
// 关闭Excel工作簿对象
wwb.close();
}
catch
(Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
|
Excel表中的数据导入到MySql数据库
|
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
|
package
com.javen.excel;
import
java.util.List;
import
com.javen.db.DBhepler;
import
com.javen.entity.StuEntity;
import
com.javen.service.StuService;
/**
* @author Javen
* @Email zyw205@gmail.com
*
*/
public
class
TestExcelToDb {
public
static
void
main(String[] args) {
//得到表格中所有的数据
/*//得到数据库表中所有的数据
List<stuentity> listDb=StuService.getAllByDb();*/
DBhepler db=
new
DBhepler();
for
(StuEntity stuEntity : listExcel) {
int
id=stuEntity.getId();
if
(!StuService.isExist(id)) {
//不存在就添加
String sql=
"insert into stu (name,sex,num) values(?,?,?)"
;
String[] str=
new
String[]{stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum()+
""
};
db.AddU(sql, str);
}
else
{
//存在就更新
String sql=
"update stu set name=?,sex=?,num=? where id=?"
;
String[] str=
new
String[]{stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum()+
""
,id+
""
};
db.AddU(sql, str);
}
}
}
}
|
转载链接：https://www.2cto.com/kf/201612/581259.html
java 实现Excel数据导入数据库时，中文乱码问题：
参考链接（中文乱码）：https://blog.csdn.net/sampson_lj/article/details/51275716
1448
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
