我的一贯风格，代码说明一切。。
废话不多说了，直接给大家贴代码了，具体代码如下所示：
|
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
|
package Tools;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import jxl.Sheet;
import jxl.Workbook;
import entity.Student;
public class ConvertXMSToSQL {
/**
* 从xls表格中获取数据，生成可执行的sql文件，用以插入数据库，注意需要引入jxl包！支持int,Integer,long,Long,String
* ,可自行扩展
*
* @param args
* @throws IllegalArgumentException
* @throws IllegalAccessException
*/
public static void main(String[] args) throws IllegalArgumentException,
IllegalAccessException {
// 得到表格中所有的数据
List<Student> listExcel = getAllByExcel("C:\\Users\\xxx\\Desktop\\zzz.xls");
try {
String path = "C:\\Users\\xxx\\Desktop\\convert.sql";// 文件保存路径、名字
File file = new File(path);
BufferedWriter ow = new BufferedWriter(new FileWriter(file));
for (Student c : listExcel) {
String sql = "insert into cfg_avatar values (" + outSql(c)
+ ")";
ow.write(sql + ";" + "\n");
}// 写入内容
ow.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 从xls中获取数据
*
* @param file
* @return
*/
public static List<Student> getAllByExcel(String file) {
List<Student> list = new ArrayList<Student>();
try {
Workbook rwb = Workbook.getWorkbook(new File(file));
Sheet rs = rwb.getSheet(0);
int clos = rs.getColumns();// 得到所有的列
int rows = rs.getRows();// 得到所有的行
// 样例中，数据从第三列第一行开始
for (int i = 2; i < rows; i++) {
// 取得的每一行的所有数据存入listString
List<String> listString = new ArrayList<String>();
for (int j = 0; j < clos; j++) {
String str = rs.getCell(j, i).getContents();
listString.add(str);
}
Student Student = (Student) newObject(new Student(), listString);
list.add(Student);
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
/**
* 使用反射设置数据。此例中可设置的数据类型有限，没有的请自己添加！！！
*
* @param obj
* @param list
* @return
* @throws IllegalArgumentException
* @throws IllegalAccessException
*/
public static Object newObject(Object obj, List<String> list)
throws IllegalArgumentException, IllegalAccessException {
Field[] field = obj.getClass().getDeclaredFields();
for (int i = 0; i < field.length; i++) {
Field f = field[i];
f.setAccessible(true);
if (f.getType() == String.class) {
f.set(obj, list.get(i));
}
if (f.getType() == Integer.class) {
f.set(obj, Integer.parseInt(list.get(i)));
}
if (f.getType() == int.class) {
f.set(obj, Integer.parseInt(list.get(i)));
}
if (f.getType() == Long.class) {
f.set(obj, Long.parseLong(list.get(i)));
}
if (f.getType() == long.class) {
f.set(obj, Long.parseLong(list.get(i)));
}
}
return obj;
}
/**
*
* @param obj
* @return
* @throws IllegalArgumentException
* @throws IllegalAccessException
*/
public static String outSql(Object obj) throws IllegalArgumentException,
IllegalAccessException {
StringBuffer buffer = new StringBuffer();
Field[] field = obj.getClass().getDeclaredFields();
for (int i = 0; i < field.length; i++) {
Field f = field[i];
f.setAccessible(true);
if (f.getType() == String.class) {
buffer.append("'");
}
buffer.append(f.get(obj));
if (f.getType() == String.class) {
buffer.append("'");
}
if (i < field.length - 1) {
buffer.append(",");
}
}
return buffer.toString();
}
}
|
有关Java实现把excel xls中数据转为可直接插入数据库的sql文件的知识，小编就给大家介绍这么多，希望对大家有所帮助！