PIO导入大量数据时的解决内存溢出问题.doc

本人刚进公司，目前还处于干杂活状态。现在的任务就是把客户数据迁移到数据库中，由于客户提供的数据都存储在excel中，有些文件数据量还很大，在 usermodel模式下经常内存溢出，于是只能采用直接通过pl/sql往数据库复制或是用eventusermodel模式读取。直接复制倒是简单，但是速度太慢，一次复制的太多也会内存溢出，我没那耐心，没办法，只能用第二种办法了。在google上找，发现相关示例太少了，幸好在docjar 找到了一个示例，自己又改了一下，把原来的例子改为抽象类，提供了一个 optRows() 方法来对行级数据进行操作。 usermodel模式对excel操作前需要将文件全部转入内存，对较大文件来说内存开销很大。但是其使用简单。 eventusermodel模式采用事件模型，对文件边读取边处理，内存消耗较低，效率高，因为不用等待文件全部装入内存。但使用较复杂。 excel读取采用的API为POI3.6，使用前先下载此包，若运行中出现其他依赖包不存在，请下载相应依赖包。 下面展示的是excel2003及其之前版本的大文件读取方法。抽象类 HxlsAbstract： Java代码 1. packagecom.gaosheng.util.xls; 2. importjava.io.FileInputStream; 3. importjava.io.FileNotFoundException; 4. importjava.io.IOException; 5. importjava.io.PrintStream; 6. importjava.sql.SQLException; 7. importjava.util.ArrayList; 8. importjava.util.List; 9. 10. importorg.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener; 11. importorg.apache.poi.hssf.eventusermodel.HSSFEventFactory; 12. importorg.apache.poi.hssf.eventusermodel.HSSFListener; 13. importorg.apache.poi.hssf.eventusermodel.HSSFRequest; 14. importorg.apache.poi.hssf.eventusermodel.MissingRecordAwareHSSFListener; 15. importorg.apache.poi.hssf.eventusermodel.EventWorkbookBuilder.SheetRecordCollectingListener; 16. importorg.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord; 17. importorg.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord; 18. importorg.apache.poi.hssf.model.HSSFFormulaParser; 19. importorg.apache.poi.hssf.record.BOFRecord; 20. importorg.apache.poi.hssf.record.BlankRecord; 21. importorg.apache.poi.hssf.record.BoolErrRecord; 22. importorg.apache.poi.hssf.record.BoundSheetRecord; 23. importorg.apache.poi.hssf.record.FormulaRecord; 24. importorg.apache.poi.hssf.record.LabelRecord; 25. importorg.apache.poi.hssf.record.LabelSSTRecord; 26. importorg.apache.poi.hssf.record.NoteRecord; 27. importorg.apache.poi.hssf.record.NumberRecord; 28. importorg.apache.poi.hssf.record.RKRecord; 29. importorg.apache.poi.hssf.record.Record; 30. importorg.apache.poi.hssf.record.SSTRecord; 31. importorg.apache.poi.hssf.record.StringRecord; 32. importorg.apache.poi.hssf.usermodel.HSSFWorkbook; 33. importorg.apache.poi.poifs.filesystem.POIFSFileSystem; 34. 35. publicabstractclassHxlsAbstractimplementsHSSFListener 36. privateintminColumns; 37. privatePOIFSFileSystemfs; 38. privatePrintStreamoutput; 39. 40. privateintlastRowNumber; 41. privateintlastColumnNumber; 42. 43. /*Shouldweoutputtheformula,orthevalueithas?*/44. privatebooleanoutputFormulaValues=true; 45. 46. /*ForparsingFormulas*/47. privateSheetRecordCollectingListenerworkbookBuildingListener; 48. privateHSSFWorkbookstubWorkbook; 49. 50. /Recordswepickupasweprocess 51. privateSSTRecordsstRecord; 52. privateFormatTrackingHSSFListenerformatListener; 53. 54. /*Soweknownwhichsheetwereon*/55. privateintsheetIndex=-1; 56. privateBoundSheetRecordorderedBSRs; 57. SuppressWarnings(unchecked) 58. privateArrayListboundSheetRecords=newArrayList(); 59. 60. /Forhandlingformulaswithstringresults 61. privateintnextRow; 62. privateintnextColumn; 63. privatebooleanoutputNextStringRecord; 64. 65. privateintcurRow; 66. privateListrowlist; 67. SuppressWarnings(unused) 68. privateStringsheetName; 69. 70. publicHxlsAbstract(POIFSFileSystemfs) 71. throwsSQLException 72. this.fs=fs; 73. this.output=System.out; 74. this.minColumns=-1; 75. this.curRow=0; 76. this.rowlist=newArrayList(); 77. 78. 79. publicHxlsAbstract(Stringfilename)throwsIOException, 80. FileNotFoundException,SQLException 81. this(newPOIFSFileSystem(newFileInputStream(filename); 82. 83. 84. /excel记录行操作方法，以行索引和行元素列表为参数，对一行元素进行操作，元素为String类型 85. /publicabstractvoidoptRows(intcurRow,Listrowlist)throwsSQLException; 86. 87. /excel记录行操作方法，以sheet索引，行索引和行元素列表为参数，对sheet的一行元素进行操作，元素为String类型 88. publicabstractvoidoptRows(intsheetIndex,intcurRow,Listrowlist)throwsSQLException; 89. 90. /* 91. *遍历excel文件 92. */93. publicvoidprocess()throwsIOException 94. MissingRecordAwareHSSFListenerlistener=newMissingRecordAwareHSSFListener( 95. this); 96. formatListener=newFormatTrackingHSSFListener(listener); 97. 98. HSSFEventFactoryfactory=newHSSFEventFactory(); 99. HSSFRequestrequest=newHSSFRequest(); 100. 101. if(outputFormulaValues) 102. request.addListenerForAllRecords(formatListener); 103. else 104. workbookBuildingListener=newSheetRecordCollectingListener( 105. formatListener); 106. request.addListenerForAllRecords(workbookBuildingListener); 107. 108. 109. cessWorkbookEvents(request,fs); 110. 111. 112. /* 113. *HSSFListener监听方法，处理Record 114. */115. SuppressWarnings(unchecked) 116. publicvoidprocessRecord(Recordrecord) 117. intthisRow=-1; 118. intthisColumn=-1; 119. StringthisStr=null; 120. Stringvalue=null; 121. 122. switch(record.getSid() 123. caseBoundSheetRecord.sid: 124. boundSheetRecords.add(record); 125. break; 126. caseBOFRecord.sid: 127. BOFRecordbr=(BOFRecord)record; 128. if(br.getType()=BOFRecord.TYPE_WORKSHEET) 129. /Createsubworkbookifrequired 130. if(workbookBuildingListener!=null&stubWorkbook=null) 131. stubWorkbook=workbookBuildingListener 132. .getStubHSSFWorkbook(); 133. 134. 135. /WorksbyorderingtheBSRsbythelocationof 136. /theirBOFRecords,andthenknowingthatwe 137. /processBOFRecordsinbyteoffsetorder 138. sheetIndex+; 139. if(orderedBSRs=null) 140. orderedBSRs=BoundSheetRecord 141. .orderByBofPosition(boundSheetRecords); 142. 143. sheetName=orderedBSRssheetIndex.getSheetname(); 144. 145. break; 146. 147. caseSSTRecord.sid: 148. sstRecord=(SSTRecord)record; 149. break; 150. 151. caseBlankRecord.sid: 152. BlankRecordbrec=(BlankRecord)record; 153. 154. thisRow=brec.getRow(); 155. thisColumn=brec.getColumn(); 156. thisStr=; 157. break; 158. caseBoolErrRecord.sid: 159. BoolErrRecordberec=(BoolErrRecord)record; 160. 161. thisRow=berec.getRow(); 162. thisColumn=berec.getColumn(); 163. thisStr=; 164. break; 165. 166. caseFormulaRecord.sid: 167. FormulaRecordfrec=(FormulaRecord)record; 168. 169. thisRow=frec.getRow(); 170. thisColumn=frec.getColumn(); 171. 172. if(outputFormulaValues) 173. if(Double.isNaN(frec.getValue() 174. /Formularesultisastring 175. /Thisisstoredinthenextrecord 176. outputNextStringRecord=true; 177. nextRow=frec.getRow(); 178. nextColumn=frec.getColumn(); 179. else 180. thisStr=formatListener.formatNumberDateCell(frec); 181. 182. else 183. thisStr=+HSSFFormulaParser.toFormulaString(stubWorkbook, 184. frec.getParsedExpression()+; 185. 186. break; 187. caseStringRecord.sid: 188. if(outputNextStringRecord) 189. /Stringforformula 190. StringRecordsrec=(StringRecord)record; 191. thisStr=srec.getString(); 192. thisRow=nextRow; 193. thisColumn=nextColumn; 194. outputNextStringRecord=false; 195. 196. break; 197. 198. caseLabelRecord.sid: 199. LabelRecordlrec=(LabelRecord)record; 200. 201. curRow=thisRow=lrec.getRow(); 202. thisColumn=lrec.getColumn(); 203. value=lrec.getValue().trim(); 204. value=value.equals()?:value; 205. this.rowlist.add(thisColumn,value); 206. break; 207. caseLabelSSTRecord.sid: 208. LabelSSTRecordlsrec=(LabelSSTRecord)record; 209. 210. curRow=thisRow=lsrec.getRow(); 211. thisColumn=lsrec.getColumn(); 212. if(sstRecord=null) 213. rowlist.add(thisColumn,); 214. else 215. value=sstRecord 216. .getString(lsrec.getSSTIndex().toString().trim(); 217. value=value.equals()?:value; 218. rowlist.add(thisColumn,value); 219. 220. break; 221. caseNoteRecord.sid: 222. NoteRecordnrec=(NoteRecord)record; 223. 224. thisRow=nrec.getRow(); 225. thisColumn=nrec.getColumn(); 226. /TODO:Findobjecttomatchnrec.getShapeId() 227. thisStr=+(TODO)+; 228. break; 229. caseNumberRecord.sid: 230. NumberRecordnumrec=(NumberRecord)record; 231. 232. curRow=thisRow=numrec.getRow(); 233. thisColumn=numrec.getColumn(); 234. value=formatListener.formatNumberDateCell(numrec).trim(); 235. value=value.equals()?:value; 236. /Format 237. rowlist.add(thisColumn,value); 238. break; 239. caseRKRecord.sid: 240. RKRecordrkrec=(RKRecord)record; 241. 242. thisRow=rkrec.getRow(); 243. thisColumn=rkrec.getColumn(); 244. thisStr=+(TODO)+; 245. break; 246. default: 247. break; 248. 249. 250. /遇到新行的操作 251. if(thisRow!=-1&thisRow!=lastRowNumber) 252. lastColumnNumber=-1; 253. 254. 255. /空值的操作 256. if(recordinstanceofMissingCellDummyRecord) 257. MissingCellDummyRecordmc=(MissingCellDummyRecord)record; 258. curRow=thisRow=mc.getRow(); 259. thisColumn=mc.getColumn(); 260. rowlist.add(thisColumn,); 261. 262. 263. /如果遇到能打印的东西，在这里打印 264. if(thisStr!=null) 265. if(thisColumn0) 266. output.print(,); 267. 268. output.print(thisStr); 269. 270. 271. /更新行和列的值 272. if(thisRow-1) 273. lastRowNumber=thisRow; 274. if(thisColumn-1) 275. lastColumnNumber=thisColumn; 276. 277. /行结束时的操作 278. if(recordinstanceofLastCellOfRowDummyRecord) 279. if(minColumns0) 280. /列值重新置空 281. if(lastColumnNumber=-1) 282. lastColumnNumber=0; 283. 284. 285. /行结束时，调用optRows()方法 286. lastColumnNumber=-1; 287. try 288. optRows(sheetIndex,curRow,rowlist); 289. catch(SQLExceptione) 290. e.printStackTrace(); 291. 292. rowlist.clear(); 293. 294. 295. package com.gaosheng.util.xls;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.PrintStream;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import org.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener;import org.apache.poi.hssf.eventusermodel.HSSFEventFactory;import org.apache.poi.hssf.eventusermodel.HSSFListener;import org.apache.poi.hssf.eventusermodel.HSSFRequest;import org.apache.poi.hssf.eventusermodel.MissingRecordAwareHSSFListener;import org.apache.poi.hssf.eventusermodel.EventWorkbookBuilder.SheetRecordCollectingListener;import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord;import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord;import org.apache.poi.hssf.model.HSSFFormulaParser;import org.apache.poi.hssf.record.BOFRecord;import org.apache.poi.hssf.record.BlankRecord;import org.apache.poi.hssf.record.BoolErrRecord;import org.apache.poi.hssf.record.BoundSheetRecord;import org.apache.poi.hssf.record.FormulaRecord;import org.apache.poi.hssf.record.LabelRecord;import org.apache.poi.hssf.record.LabelSSTRecord;import org.apache.poi.hssf.record.NoteRecord;import org.apache.poi.hssf.record.NumberRecord;import org.apache.poi.hssf.record.RKRecord;import org.apache.poi.hssf.record.Record;import org.apache.poi.hssf.record.SSTRecord;import org.apache.poi.hssf.record.StringRecord;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.poifs.filesystem.POIFSFileSystem;public abstract class HxlsAbstract implements HSSFListener private int minColumns;private POIFSFileSystem fs;private PrintStream output;private int lastRowNumber;private int lastColumnNumber;/* Should we output the formula, or the value it has? */private boolean outputFormulaValues = true;/* For parsing Formulas */private SheetRecordCollectingListener workbookBuildingListener;private HSSFWorkbook stubWorkbook;/ Records we pick up as we processprivate SSTRecord sstRecord;private FormatTrackingHSSFListener formatListener;/* So we known which sheet were on */private int sheetIndex = -1;private BoundSheetRecord orderedBSRs;SuppressWarnings(unchecked)private ArrayList boundSheetRecords = new ArrayList();/ For handling formulas with string resultsprivate int nextRow;private int nextColumn;private boolean outputNextStringRecord;private int curRow;private List rowlist;SuppressWarnings( unused)private String sheetName;public HxlsAbstract(POIFSFileSystem fs)throws SQLException this.fs = fs;this.output = System.out;this.minColumns = -1;this.curRow = 0;this.rowlist = new ArrayList();public HxlsAbstract(String filename) throws IOException,FileNotFoundException, SQLException this(new POIFSFileSystem(new FileInputStream(filename);/excel记录行操作方法，以行索引和行元素列表为参数，对一行元素进行操作，元素为String类型/public abstract void optRows(int curRow, List rowlist) throws SQLException ;/excel记录行操作方法，以sheet索引，行索引和行元素列表为参数，对sheet的一行元素进行操作，元素为String类型public abstract void optRows(int sheetIndex,int curRow, List rowlist) throws SQLException;/* * 遍历 excel 文件 */public void process() throws IOException MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(this);formatListener = new FormatTrackingHSSFListener(listener);HSSFEventFactory factory = new HSSFEventFactory();HSSFRequest request = new HSSFRequest();if (outputFormulaValues) request.addListenerForAllRecords(formatListener); else workbookBuildingListener = new SheetRecordCollectingListener(formatListener);request.addListenerForAllRecords(workbookBuildingListener);cessWorkbookEvents(request, fs);/* * HSSFListener 监听方法，处理 Record */SuppressWarnings(unchecked)public void processRecord(Record record) int thisRow = -1;int thisColumn = -1;String thisStr = null;String value = null;switch (record.getSid() case BoundSheetRecord.sid:boundSheetRecords.add(record);break;case BOFRecord.sid:BOFRecord br = (BOFRecord) record;if (br.getType() = BOFRecord.TYPE_WORKSHEET) / Create sub workbook if requiredif (workbookBuildingListener != null & stubWorkbook = null) stubWorkbook = workbookBuildingListener.getStubHSSFWorkbook();/ Works by ordering the BSRs by the location of/ their BOFRecords, and then knowing that we/ process BOFRecords in byte offset ordersheetIndex+;if (orderedBSRs = null) orderedBSRs = Boun