大数据量的excel文件读取——excel2007（含代码及示例）_JAVA_编程开发_程序员俱乐部

excel2007文件格式与之前版本不同，之前版本采用的是微软自己的存储格式。07版内容的存储采用XML格式，所以，理所当然的，对大数据量的 xlsx文件的读取采用的也是XML的处理方式SAX。

??? 同之前的版本一样，大数据量文件的读取采用的是事件模型eventusermodel。usermodel模式需要将文件一次性全部读到内存中，07版的既然采用的存储模式是xml，解析用的DOM方式也是如此，这种模式操作简单，容易上手，但是对于大量数据占用的内存也是相当可观，在Eclipse中经常出现内存溢出。

??? 下面就是采用eventusermodel对07excel文件读取。

??? 同上篇，我将当前行的单元格数据存储到List中，抽象出 optRows 方法，该方法会在每行末尾时调用，方法参数为当前行索引curRow(int型)及存有行内单元格数据的List。继承类只需实现该行级方法即可。

?

????经测试，对12万条数据，7M大小的文件也能正常运行。无需设置vm的内存空间。

?

??? excel读取采用的API为POI3.6，使用前先下载此包，若运行中出现其他依赖包不存在，请下载相应依赖包。

?

抽象类：XxlsAbstract ，作用：遍历excel文件，提供行级操作方法 optRows

class="dp-j">
1. package?com.gaosheng.util.xls; ??
2. ??
3. import?java.io.InputStream; ??
4. import?java.sql.SQLException; ??
5. import?java.util.ArrayList; ??
6. import?java.util.Iterator; ??
7. import?java.util.List; ??
8. ??
9. import?org.apache.poi.xssf.eventusermodel.XSSFReader; ??
10. import?org.apache.poi.xssf.model.SharedStringsTable; ??
11. import?org.apache.poi.xssf.usermodel.XSSFRichTextString; ??
12. import?org.apache.poi.openxml4j.opc.OPCPackage; ??
13. import?org.xml.sax.Attributes; ??
14. import?org.xml.sax.InputSource; ??
15. import?org.xml.sax.SAXException; ??
16. import?org.xml.sax.XMLReader; ??
17. import?org.xml.sax.helpers.DefaultHandler; ??
18. import?org.xml.sax.helpers.XMLReaderFactory; ??
19. ??
20. /**??
21. ?*?XSSF?and?SAX?(Event?API)??
22. ?*/??
23. public?abstract?class?XxlsAbstract?extends?DefaultHandler?{ ??
24. ????private?SharedStringsTable?sst; ??
25. ????private?String?lastContents; ??
26. ????private?boolean?nextIsString; ??
27. ??
28. ????private?int?sheetIndex?=?-1; ??
29. ????private?List<String>?rowlist?=?new?ArrayList<String>(); ??
30. ????private?int?curRow?=?0; ??
31. ????private?int?curCol?=?0; ??
32. ??
33. ????//excel记录行操作方法，以行索引和行元素列表为参数，对一行元素进行操作，元素为String类型???
34. //??public?abstract?void?optRows(int?curRow,?List<String>?rowlist)?throws?SQLException?;???
35. ???? ??
36. ????//excel记录行操作方法，以sheet索引，行索引和行元素列表为参数，对sheet的一行元素进行操作，元素为String类型???
37. ????public?abstract?void?optRows(int?sheetIndex,int?curRow,?List<String>?rowlist)?throws?SQLException; ??
38. ???? ??
39. ????//只遍历一个sheet，其中sheetId为要遍历的sheet索引，从1开始，1-3???
40. ????public?void?processOneSheet(String?filename,int?sheetId)?throws?Exception?{ ??
41. ????????OPCPackage?pkg?=?OPCPackage.open(filename); ??
42. ????????XSSFReader?r?=?new?XSSFReader(pkg); ??
43. ????????SharedStringsTable?sst?=?r.getSharedStringsTable(); ??
44. ???????? ??
45. ????????XMLReader?parser?=?fetchSheetParser(sst); ??
46. ??
47. ????????//?rId2?found?by?processing?the?Workbook???
48. ????????//?根据?rId#?或?rSheet#?查找sheet???
49. ????????InputStream?sheet2?=?r.getSheet("rId"+sheetId); ??
50. ????????sheetIndex++; ??
51. ????????InputSource?sheetSource?=?new?InputSource(sheet2); ??
52. ????????parser.parse(sheetSource); ??
53. ????????sheet2.close(); ??
54. ????} ??
55. ??
56. ????/**??
57. ?????*?遍历?excel?文件??
58. ?????*/??
59. ????public?void?process(String?filename)?throws?Exception?{ ??
60. ????????OPCPackage?pkg?=?OPCPackage.open(filename); ??
61. ????????XSSFReader?r?=?new?XSSFReader(pkg); ??
62. ????????SharedStringsTable?sst?=?r.getSharedStringsTable(); ??
63. ??
64. ????????XMLReader?parser?=?fetchSheetParser(sst); ??
65. ??
66. ????????Iterator<InputStream>?sheets?=?r.getSheetsData(); ??
67. ????????while?(sheets.hasNext())?{ ??
68. ????????????curRow?=?0; ??
69. ????????????sheetIndex++; ??
70. ????????????InputStream?sheet?=?sheets.next(); ??
71. ????????????InputSource?sheetSource?=?new?InputSource(sheet); ??
72. ????????????parser.parse(sheetSource); ??
73. ????????????sheet.close(); ??
74. ????????} ??
75. ????} ??
76. ??
77. ????public?XMLReader?fetchSheetParser(SharedStringsTable?sst) ??
78. ????????????throws?SAXException?{ ??
79. ????????XMLReader?parser?=?XMLReaderFactory ??
80. ????????????????.createXMLReader("org.apache.xerces.parsers.SAXParser"); ??
81. ????????this.sst?=?sst; ??
82. ????????parser.setContentHandler(this); ??
83. ????????return?parser; ??
84. ????} ??
85. ??
86. ????public?void?startElement(String?uri,?String?localName,?String?name, ??
87. ????????????Attributes?attributes)?throws?SAXException?{ ??
88. ????????//?c?=>?单元格???
89. ????????if?(name.equals("c"))?{ ??
90. ????????????//?如果下一个元素是?SST?的索引，则将nextIsString标记为true???
91. ????????????String?cellType?=?attributes.getValue("t"); ??
92. ????????????if?(cellType?!=?null?&&?cellType.equals("s"))?{ ??
93. ????????????????nextIsString?=?true; ??
94. ????????????}?else?{ ??
95. ????????????????nextIsString?=?false; ??
96. ????????????} ??
97. ????????} ??
98. ????????//?置空???
99. ????????lastContents?=?""; ??
100. ????} ??
101. ??
102. ????public?void?endElement(String?uri,?String?localName,?String?name) ??
103. ????????????throws?SAXException?{ ??
104. ????????//?根据SST的索引值的到单元格的真正要存储的字符串???
105. ????????//?这时characters()方法可能会被调用多次???
106. ????????if?(nextIsString)?{ ??
107. ????????????try?{ ??
108. ????????????????int?idx?=?Integer.parseInt(lastContents); ??
109. ????????????????lastContents?=?new?XSSFRichTextString(sst.getEntryAt(idx)) ??
110. ????????????????????????.toString(); ??
111. ????????????}?catch?(Exception?e)?{ ??
112. ??
113. ????????????} ??
114. ????????} ??
115. ??
116. ????????//?v?=>?单元格的值，如果单元格是字符串则v标签的值为该字符串在SST中的索引???
117. ????????//?将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符???
118. ????????if?(name.equals("v"))?{ ??
119. ????????????String?value?=?lastContents.trim(); ??
120. ????????????value?=?value.equals("")?"?":value; ??
121. ????????????rowlist.add(curCol,?value); ??
122. ????????????curCol++; ??
123. ????????}else?{ ??
124. ????????????//如果标签名称为?row?，这说明已到行尾，调用?optRows()?方法???
125. ????????????if?(name.equals("row"))?{ ??
126. ????????????????try?{ ??
127. ????????????????????optRows(sheetIndex,curRow,rowlist); ??
128. ????????????????}?catch?(SQLException?e)?{ ??
129. ????????????????????e.printStackTrace(); ??
130. ????????????????} ??
131. ????????????????rowlist.clear(); ??
132. ????????????????curRow++; ??
133. ????????????????curCol?=?0; ??
134. ????????????} ??
135. ????????} ??
136. ????} ??
137. ??
138. ????public?void?characters(char[]?ch,?int?start,?int?length) ??
139. ????????????throws?SAXException?{ ??
140. ????????//得到单元格内容的值???
141. ????????lastContents?+=?new?String(ch,?start,?length); ??
142. ????} ??
143. }??

1. package?com.gaosheng.util.xls;??
2. ??
3. import?java.io.InputStream;??
4. import?java.sql.SQLException;??
5. import?java.util.ArrayList;??
6. import?java.util.Iterator;??
7. import?java.util.List;??
8. ??
9. import?org.apache.poi.xssf.eventusermodel.XSSFReader;??
10. import?org.apache.poi.xssf.model.SharedStringsTable;??
11. import?org.apache.poi.xssf.usermodel.XSSFRichTextString;??
12. import?org.apache.poi.openxml4j.opc.OPCPackage;??
13. import?org.xml.sax.Attributes;??
14. import?org.xml.sax.InputSource;??
15. import?org.xml.sax.SAXException;??
16. import?org.xml.sax.XMLReader;??
17. import?org.xml.sax.helpers.DefaultHandler;??
18. import?org.xml.sax.helpers.XMLReaderFactory;??
19. ??
20. /**?
21. ?*?XSSF?and?SAX?(Event?API)?
22. ?*/??
23. public?abstract?class?XxlsAbstract?extends?DefaultHandler?{??
24. ????private?SharedStringsTable?sst;??
25. ????private?String?lastContents;??
26. ????private?boolean?nextIsString;??
27. ??
28. ????private?int?sheetIndex?=?-1;??
29. ????private?List<String>?rowlist?=?new?ArrayList<String>();??
30. ????private?int?curRow?=?0;??
31. ????private?int?curCol?=?0;??
32. ??
33. ????//excel记录行操作方法，以行索引和行元素列表为参数，对一行元素进行操作，元素为String类型??
34. //??public?abstract?void?optRows(int?curRow,?List<String>?rowlist)?throws?SQLException?;??
35. ??????
36. ????//excel记录行操作方法，以sheet索引，行索引和行元素列表为参数，对sheet的一行元素进行操作，元素为String类型??
37. ????public?abstract?void?optRows(int?sheetIndex,int?curRow,?List<String>?rowlist)?throws?SQLException;??
38. ??????
39. ????//只遍历一个sheet，其中sheetId为要遍历的sheet索引，从1开始，1-3??
40. ????public?void?processOneSheet(String?filename,int?sheetId)?throws?Exception?{??
41. ????????OPCPackage?pkg?=?OPCPackage.open(filename);??
42. ????????XSSFReader?r?=?new?XSSFReader(pkg);??
43. ????????SharedStringsTable?sst?=?r.getSharedStringsTable();??
44. ??????????
45. ????????XMLReader?parser?=?fetchSheetParser(sst);??
46. ??
47. ????????//?rId2?found?by?processing?the?Workbook??
48. ????????//?根据?rId#?或?rSheet#?查找sheet??
49. ????????InputStream?sheet2?=?r.getSheet("rId"+sheetId);??
50. ????????sheetIndex++;??
51. ????????InputSource?sheetSource?=?new?InputSource(sheet2);??
52. ????????parser.parse(sheetSource);??
53. ????????sheet2.close();??
54. ????}??
55. ??
56. ????/**?
57. ?????*?遍历?excel?文件?
58. ?????*/??
59. ????public?void?process(String?filename)?throws?Exception?{??
60. ????????OPCPackage?pkg?=?OPCPackage.open(filename);??
61. ????????XSSFReader?r?=?new?XSSFReader(pkg);??
62. ????????SharedStringsTable?sst?=?r.getSharedStringsTable();??
63. ??
64. ????????XMLReader?parser?=?fetchSheetParser(sst);??
65. ??
66. ????????Iterator<InputStream>?sheets?=?r.getSheetsData();??
67. ????????while?(sheets.hasNext())?{??
68. ????????????curRow?=?0;??
69. ????????????sheetIndex++;??
70. ????????????InputStream?sheet?=?sheets.next();??
71. ????????????InputSource?sheetSource?=?new?InputSource(sheet);??
72. ????????????parser.parse(sheetSource);??
73. ????????????sheet.close();??
74. ????????}??
75. ????}??
76. ??
77. ????public?XMLReader?fetchSheetParser(SharedStringsTable?sst)??
78. ????????????throws?SAXException?{??
79. ????????XMLReader?parser?=?XMLReaderFactory??
80. ????????????????.createXMLReader("org.apache.xerces.parsers.SAXParser");??
81. ????????this.sst?=?sst;??
82. ????????parser.setContentHandler(this);??
83. ????????return?parser;??
84. ????}??
85. ??
86. ????public?void?startElement(String?uri,?String?localName,?String?name,??
87. ????????????Attributes?attributes)?throws?SAXException?{??
88. ????????//?c?=>?单元格??
89. ????????if?(name.equals("c"))?{??
90. ????????????//?如果下一个元素是?SST?的索引，则将nextIsString标记为true??
91. ????????????String?cellType?=?attributes.getValue("t");??
92. ????????????if?(cellType?!=?null?&&?cellType.equals("s"))?{??
93. ????????????????nextIsString?=?true;??
94. ????????????}?else?{??
95. ????????????????nextIsString?=?false;??
96. ????????????}??
97. ????????}??
98. ????????//?置空??
99. ????????lastContents?=?"";??
100. ????}??
101. ??
102. ????public?void?endElement(String?uri,?String?localName,?String?name)??
103. ????????????throws?SAXException?{??
104. ????????//?根据SST的索引值的到单元格的真正要存储的字符串??
105. ????????//?这时characters()方法可能会被调用多次??
106. ????????if?(nextIsString)?{??
107. ????????????try?{??
108. ????????????????int?idx?=?Integer.parseInt(lastContents);??
109. ????????????????lastContents?=?new?XSSFRichTextString(sst.getEntryAt(idx))??
110. ????????????????????????.toString();??
111. ????????????}?catch?(Exception?e)?{??
112. ??
113. ????????????}??
114. ????????}??
115. ??
116. ????????//?v?=>?单元格的值，如果单元格是字符串则v标签的值为该字符串在SST中的索引??
117. ????????//?将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符??
118. ????????if?(name.equals("v"))?{??
119. ????????????String?value?=?lastContents.trim();??
120. ????????????value?=?value.equals("")?"?":value;??
121. ????????????rowlist.add(curCol,?value);??
122. ????????????curCol++;??
123. ????????}else?{??
124. ????????????//如果标签名称为?row?，这说明已到行尾，调用?optRows()?方法??
125. ????????????if?(name.equals("row"))?{??
126. ????????????????try?{??
127. ????????????????????optRows(sheetIndex,curRow,rowlist);??
128. ????????????????}?catch?(SQLException?e)?{??
129. ????????????????????e.printStackTrace();??
130. ????????????????}??
131. ????????????????rowlist.clear();??
132. ????????????????curRow++;??
133. ????????????????curCol?=?0;??
134. ????????????}??
135. ????????}??
136. ????}??
137. ??
138. ????public?void?characters(char[]?ch,?int?start,?int?length)??
139. ????????????throws?SAXException?{??
140. ????????//得到单元格内容的值??
141. ????????lastContents?+=?new?String(ch,?start,?length);??
142. ????}??
143. }??

?

继承类：XxlsBig，作用：将数据转出到数据库临时表

1. package?com.gaosheng.util.examples.xls; ??
2. ??
3. import?java.io.FileInputStream; ??
4. import?java.io.IOException; ??
5. import?java.sql.Connection; ??
6. import?java.sql.DriverManager; ??
7. import?java.sql.PreparedStatement; ??
8. import?java.sql.SQLException; ??
9. import?java.sql.Statement; ??
10. import?java.util.List; ??
11. import?java.util.Properties; ??
12. ??
13. import?com.gaosheng.util.xls.XxlsAbstract; ??
14. ??
15. public?class?XxlsBig?extends?XxlsAbstract?{ ??
16. ????public?static?void?main(String[]?args)?throws?Exception?{ ??
17. ????????XxlsBig?howto?=?new?XxlsBig("temp_table"); ??
18. ????????howto.processOneSheet("F:/new.xlsx",1); ??
19. ????????howto.process("F:/new.xlsx"); ??
20. ????????howto.close(); ??
21. ????} ??
22. ???? ??
23. ????public?XxlsBig(String?tableName)?throws?SQLException{ ??
24. ????????this.conn?=?getNew_Conn(); ??
25. ????????this.statement?=?conn.createStatement(); ??
26. ????????this.tableName?=?tableName; ??
27. ????} ??
28. ??
29. ????private?Connection?conn?=?null; ??
30. ????private?Statement?statement?=?null; ??
31. ????private?PreparedStatement?newStatement?=?null; ??
32. ??
33. ????private?String?tableName?=?"temp_table"; ??
34. ????private?boolean?create?=?true; ??
35. ???? ??
36. ????public?void?optRows(int?sheetIndex,int?curRow,?List<String>?rowlist)?throws?SQLException?{ ??
37. ????????if?(sheetIndex?==?0?&&?curRow?==?0)?{ ??
38. ????????????StringBuffer?preSql?=?new?StringBuffer("insert?into?"?+?tableName ??
39. ????????????????????+?"?values("); ??
40. ????????????StringBuffer?table?=?new?StringBuffer("create?table?"?+?tableName ??
41. ????????????????????+?"("); ??
42. ????????????int?c?=?rowlist.size(); ??
43. ????????????for?(int?i?=?0;?i?<?c;?i++)?{ ??
44. ????????????????preSql.append("?,"); ??
45. ????????????????table.append(rowlist.get(i)); ??
46. ????????????????table.append("??varchar2(100)?,"); ??
47. ????????????} ??
48. ??
49. ????????????table.deleteCharAt(table.length()?-?1); ??
50. ????????????preSql.deleteCharAt(preSql.length()?-?1); ??
51. ????????????table.append(")"); ??
52. ????????????preSql.append(")"); ??
53. ????????????if?(create)?{ ??
54. ????????????????statement?=?conn.createStatement(); ??
55. ????????????????try{ ??
56. ????????????????????statement.execute("drop?table?"+tableName); ??
57. ????????????????}catch(Exception?e){ ??
58. ???????????????????? ??
59. ????????????????}finally{ ??
60. ????????????????????System.out.println("表?"+tableName+"?删除成功"); ??
61. ????????????????} ??
62. ????????????????if?(!statement.execute(table.toString()))?{ ??
63. ????????????????????System.out.println("创建表?"+tableName+"?成功"); ??
64. ????????????????????//?return;???
65. ????????????????}?else?{ ??
66. ????????????????????System.out.println("创建表?"+tableName+"?失败"); ??
67. ????????????????????return; ??
68. ????????????????} ??
69. ????????????} ??
70. ????????????conn.setAutoCommit(false); ??
71. ????????????newStatement?=?conn.prepareStatement(preSql.toString()); ??
72. ??
73. ????????}?else?if(curRow>0)?{ ??
74. ????????????//?一般行???
75. ????????????int?col?=?rowlist.size(); ??
76. ????????????for?(int?i?=?0;?i?<?col;?i++)?{ ??
77. ????????????????newStatement.setString(i?+?1,?rowlist.get(i).toString()); ??
78. ????????????} ??
79. ????????????newStatement.addBatch(); ??
80. ????????????if?(curRow?%?1000?==?0)?{ ??
81. ????????????????newStatement.executeBatch(); ??
82. ????????????????conn.commit(); ??
83. ????????????} ??
84. ????????} ??
85. ????} ??
86. ???? ??
87. ????private?static?Connection?getNew_Conn()?{ ??
88. ????????Connection?conn?=?null; ??
89. ????????Properties?props?=?new?Properties(); ??
90. ????????FileInputStream?fis?=?null; ??
91. ??
92. ????????try?{ ??
93. ????????????fis?=?new?FileInputStream("D:/database.properties"); ??
94. ????????????props.load(fis); ??
95. ????????????DriverManager.registerDriver(new?oracle.jdbc.driver.OracleDriver()); ??
96. ????????????//?String?jdbcURLString?=???
97. ????????????//?"jdbc:oracle:thin:@192.168.0.28:1521:orcl";???
98. ????????????StringBuffer?jdbcURLString?=?new?StringBuffer(); ??
99. ????????????jdbcURLString.append("jdbc:oracle:thin:@"); ??
100. ????????????jdbcURLString.append(props.getProperty("host")); ??
101. ????????????jdbcURLString.append(":"); ??
102. ????????????jdbcURLString.append(props.getProperty("port")); ??
103. ????????????jdbcURLString.append(":"); ??
104. ????????????jdbcURLString.append(props.getProperty("database")); ??
105. ????????????conn?=?DriverManager.getConnection(jdbcURLString.toString(),?props ??
106. ????????????????????.getProperty("user"),?props.getProperty("password")); ??
107. ????????}?catch?(Exception?e)?{ ??
108. ????????????e.printStackTrace(); ??
109. ????????}?finally?{ ??
110. ????????????try?{ ??
111. ????????????????fis.close(); ??
112. ????????????}?catch?(IOException?e)?{ ??
113. ????????????????e.printStackTrace(); ??
114. ????????????} ??
115. ????????} ??
116. ????????return?conn; ??
117. ????} ??
118. ???? ??
119. ????public?int?close()?{ ??
120. ????????try?{ ??
121. ????????????newStatement.executeBatch(); ??
122. ????????????conn.commit(); ??
123. ????????????System.out.println("数据写入完毕"); ??
124. ????????????this.newStatement.close(); ??
125. ????????????this.statement.close(); ??
126. ????????????this.conn.close(); ??
127. ????????????return?1; ??
128. ????????}?catch?(SQLException?e)?{ ??
129. ????????????return?0; ??
130. ????????} ??
131. ????} ??
132. }??

1. package?com.gaosheng.util.examples.xls;??
2. ??
3. import?java.io.FileInputStream;??
4. import?java.io.IOException;??
5. import?java.sql.Connection;??
6. import?java.sql.DriverManager;??
7. import?java.sql.PreparedStatement;??
8. import?java.sql.SQLException;??
9. import?java.sql.Statement;??
10. import?java.util.List;??
11. import?java.util.Properties;??
12. ??
13. import?com.gaosheng.util.xls.XxlsAbstract;??
14. ??
15. public?class?XxlsBig?extends?XxlsAbstract?{??
16. ????public?static?void?main(String[]?args)?throws?Exception?{??
17. ????????XxlsBig?howto?=?new?XxlsBig("temp_table");??
18. ????????howto.processOneSheet("F:/new.xlsx",1);??
19. ????????howto.process("F:/new.xlsx");??
20. ????????howto.close();??
21. ????}??
22. ??????
23. ????public?XxlsBig(String?tableName)?throws?SQLException{??
24. ????????this.conn?=?getNew_Conn();??
25. ????????this.statement?=?conn.createStatement();??
26. ????????this.tableName?=?tableName;??
27. ????}??
28. ??
29. ????private?Connection?conn?=?null;??
30. ????private?Statement?statement?=?null;??
31. ????private?PreparedStatement?newStatement?=?null;??
32. ??
33. ????private?String?tableName?=?"temp_table";??
34. ????private?boolean?create?=?true;??
35. ??????
36. ????public?void?optRows(int?sheetIndex,int?curRow,?List<String>?rowlist)?throws?SQLException?{??
37. ????????if?(sheetIndex?==?0?&&?curRow?==?0)?{??
38. ????????????StringBuffer?preSql?=?new?StringBuffer("insert?into?"?+?tableName??
39. ????????????????????+?"?values(");??
40. ????????????StringBuffer?table?=?new?StringBuffer("create?table?"?+?tableName??
41. ????????????????????+?"(");??
42. ????????????int?c?=?rowlist.size();??
43. ????????????for?(int?i?=?0;?i?<?c;?i++)?{??
44. ????????????????preSql.append("?,");??
45. ????????????????table.append(rowlist.get(i));??
46. ????????????????table.append("??varchar2(100)?,");??
47. ????????????}??
48. ??
49. ????????????table.deleteCharAt(table.length()?-?1);??
50. ????????????preSql.deleteCharAt(preSql.length()?-?1);??
51. ????????????table.append(")");??
52. ????????????preSql.append(")");??
53. ????????????if?(create)?{??
54. ????????????????statement?=?conn.createStatement();??
55. ????????????????try{??
56. ????????????????????statement.execute("drop?table?"+tableName);??
57. ????????????????}catch(Exception?e){??
58. ??????????????????????
59. ????????????????}finally{??
60. ????????????????????System.out.println("表?"+tableName+"?删除成功");??
61. ????????????????}??
62. ????????????????if?(!statement.execute(table.toString()))?{??
63. ????????????????????System.out.println("创建表?"+tableName+"?成功");??
64. ????????????????????//?return;??
65. ????????????????}?else?{??
66. ????????????????????System.out.println("创建表?"+tableName+"?失败");??
67. ????????????????????return;??
68. ????????????????}??
69. ????????????}??
70. ????????????conn.setAutoCommit(false);??
71. ????????????newStatement?=?conn.prepareStatement(preSql.toString());??
72. ??
73. ????????}?else?if(curRow>0)?{??
74. ????????????//?一般行??
75. ????????????int?col?=?rowlist.size();??
76. ????????????for?(int?i?=?0;?i?<?col;?i++)?{??
77. ????????????????newStatement.setString(i?+?1,?rowlist.get(i).toString());??
78. ????????????}??
79. ????????????newStatement.addBatch();??
80. ????????????if?(curRow?%?1000?==?0)?{??
81. ????????????????newStatement.executeBatch();??
82. ????????????????conn.commit();??
83. ????????????}??
84. ????????}??
85. ????}??
86. ??????
87. ????private?static?Connection?getNew_Conn()?{??
88. ????????Connection?conn?=?null;??
89. ????????Properties?props?=?new?Properties();??
90. ????????FileInputStream?fis?=?null;??
91. ??
92. ????????try?{??
93. ????????????fis?=?new?FileInputStream("D:/database.properties");??
94. ????????????props.load(fis);??
95. ????????????DriverManager.registerDriver(new?oracle.jdbc.driver.OracleDriver());??
96. ????????????//?String?jdbcURLString?=??
97. ????????????//?"jdbc:oracle:thin:@192.168.0.28:1521:orcl";??
98. ????????????StringBuffer?jdbcURLString?=?new?StringBuffer();??
99. ????????????jdbcURLString.append("jdbc:oracle:thin:@");??
100. ????????????jdbcURLString.append(props.getProperty("host"));??
101. ????????????jdbcURLString.append(":");??
102. ????????????jdbcURLString.append(props.getProperty("port"));??
103. ????????????jdbcURLString.append(":");??
104. ????????????jdbcURLString.append(props.getProperty("database"));??
105. ????????????conn?=?DriverManager.getConnection(jdbcURLString.toString(),?props??
106. ????????????????????.getProperty("user"),?props.getProperty("password"));??
107. ????????}?catch?(Exception?e)?{??
108. ????????????e.printStackTrace();??
109. ????????}?finally?{??
110. ????????????try?{??
111. ????????????????fis.close();??
112. ????????????}?catch?(IOException?e)?{??
113. ????????????????e.printStackTrace();??
114. ????????????}??
115. ????????}??
116. ????????return?conn;??
117. ????}??
118. ??????
119. ????public?int?close()?{??
120. ????????try?{??
121. ????????????newStatement.executeBatch();??
122. ????????????conn.commit();??
123. ????????????System.out.println("数据写入完毕");??
124. ????????????this.newStatement.close();??
125. ????????????this.statement.close();??
126. ????????????this.conn.close();??
127. ????????????return?1;??
128. ????????}?catch?(SQLException?e)?{??
129. ????????????return?0;??
130. ????????}??
131. ????}??
132. }??

?继承类：XxlsPrint，作用：将数据输出到控制台

1. package?com.gaosheng.util.examples.xls; ??
2. ??
3. import?java.sql.SQLException; ??
4. import?java.util.List; ??
5. ??
6. import?com.gaosheng.util.xls.XxlsAbstract; ??
7. ??
8. public?class?XxlsPrint?extends?XxlsAbstract?{ ??
9. ??
10. ????@Override??
11. ????public?void?optRows(int?sheetIndex,int?curRow,?List<String>?rowlist)?throws?SQLException?{ ??
12. ????????for?(int?i?=?0;?i?<?rowlist.size();?i++)?{ ??
13. ????????????System.out.print("'"?+?rowlist.get(i)?+?"',"); ??
14. ????????} ??
15. ????????System.out.println(); ??
16. ????} ??
17. ??
18. ????public?static?void?main(String[]?args)?throws?Exception?{ ??
19. ????????XxlsPrint?howto?=?new?XxlsPrint(); ??
20. ????????howto.processOneSheet("F:/new.xlsx",1); ??
21. //??????howto.processAllSheets("F:/new.xlsx");???
22. ????} ??
23. }??

1. package?com.gaosheng.util.examples.xls;??
2. ??
3. import?java.sql.SQLException;??
4. import?java.util.List;??
5. ??
6. import?com.gaosheng.util.xls.XxlsAbstract;??
7. ??
8. public?class?XxlsPrint?extends?XxlsAbstract?{??
9. ??
10. ????@Override??
11. ????public?void?optRows(int?sheetIndex,int?curRow,?List<String>?rowlist)?throws?SQLException?{??
12. ????????for?(int?i?=?0;?i?<?rowlist.size();?i++)?{??
13. ????????????System.out.print("'"?+?rowlist.get(i)?+?"',");??
14. ????????}??
15. ????????System.out.println();??
16. ????}??
17. ??
18. ????public?static?void?main(String[]?args)?throws?Exception?{??
19. ????????XxlsPrint?howto?=?new?XxlsPrint();??
20. ????????howto.processOneSheet("F:/new.xlsx",1);??
21. //??????howto.processAllSheets("F:/new.xlsx");??
22. ????}??
23. }??

?源代码在附件中，还包含了说明文件、数据库配置文件、以及整合xls文件和xlsx文件读取的类：Xls2Do。

?

• src.rar (9.7 KB)
• 下载次数: 0