我的一貫風格，代碼說明一切。。
廢話不多說了，直接給大家貼代碼了，具體代碼如下所示：
|
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++) {// 取得的每一行的所有數據存入listStringList<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文件的知識，小編就給大家介紹這么多，希望對大家有所幫助！
