解决POI读取Excel内存溢出的问题

悠悠 2021-09-12 05:54 451阅读 0赞

Office软件一直是一个诲誉参半的软件，广大普通计算机用户用Office来满足日常办公需求，于是就产生了很多生产数据和文档，需要和企业单位的专用办公系统对接，而Office的解析工作一直是程序员非常头痛的问题，经常招致程序员的谩骂，也被誉为是微软最烂的发明之一。POI的诞生解决了Excel的解析难题（POI即“讨厌的电子表格”，确实很讨厌，我也很讨厌Excel），但如果用不好POI，也会导致程序出现一些BUG，例如内存溢出，假空行，公式等等问题。下面介绍一种解决POI读取Excel内存溢出的问题。

POI读取Excel有两种模式，一种是用户模式，一种是SAX模式，将xlsx格式的文档转换成CVS格式后再进行处理用户模式相信大家都很清楚，也是POI常用的方式，用户模式API接口丰富，我们可以很容易的使用POI的API读取Excel，但用户模式消耗的内存很大，当遇到很多sheet、[大数据][Link 1]网格、假空行、公式等问题时，很容易导致内存溢出。POI官方推荐解决内存溢出的方式使用CVS格式解析，我们不可能手工将Excel文件转换成CVS格式再上传，这样做太麻烦了，好再POI给出了xlsx转换CVS的例子，基于这个例子我进行了一下改造，即可解决用户模式读取Excel内存溢出的问题。下面附上代码:

**\[java\]**  [view plain][]   [copy][view plain]

[![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

1.  /\* ==================================================================== 
2.     Licensed to the Apache Software Foundation (ASF) under one or more 
3.     contributor license agreements.  See the NOTICE file distributed with 
4.     this work for additional information regarding copyright ownership. 
5.     The ASF licenses this file to You under the Apache License, Version 2.0 
6.     (the "License"); you may not use this file except in compliance with 
7.     the License.  You may obtain a copy of the License at 
8.   
9.         http://www.apache.org/licenses/LICENSE-2.0 
10.  
11.    Unless required by applicable law or agreed to in writing, software 
12.    distributed under the License is distributed on an "AS IS" BASIS, 
13.    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 
14.    See the License for the specific language governing permissions and 
15.    limitations under the License. 
16. ==================================================================== \*/  
17.   
18. **import** java.io.IOException;  
19. **import** java.io.InputStream;  
20. **import** java.io.PrintStream;  
21. **import** java.text.SimpleDateFormat;  
22. **import** java.util.ArrayList;  
23. **import** java.util.Date;  
24. **import** java.util.List;  
25.   
26. **import** javax.xml.parsers.ParserConfigurationException;  
27. **import** javax.xml.parsers.SAXParser;  
28. **import** javax.xml.parsers.SAXParserFactory;  
29.   
30. **import** org.apache.poi.hssf.usermodel.HSSFDateUtil;  
31. **import** org.apache.poi.openxml4j.exceptions.OpenXML4JException;  
32. **import** org.apache.poi.openxml4j.opc.OPCPackage;  
33. **import** org.apache.poi.openxml4j.opc.PackageAccess;  
34. **import** org.apache.poi.ss.usermodel.BuiltinFormats;  
35. **import** org.apache.poi.ss.usermodel.DataFormatter;  
36. **import** org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;  
37. **import** org.apache.poi.xssf.eventusermodel.XSSFReader;  
38. **import** org.apache.poi.xssf.model.StylesTable;  
39. **import** org.apache.poi.xssf.usermodel.XSSFCellStyle;  
40. **import** org.apache.poi.xssf.usermodel.XSSFRichTextString;  
41. **import** org.xml.sax.Attributes;  
42. **import** org.xml.sax.InputSource;  
43. **import** org.xml.sax.SAXException;  
44. **import** org.xml.sax.XMLReader;  
45. **import** org.xml.sax.helpers.DefaultHandler;  
46.   
47. /\*\* 
48.  \* 使用CVS模式解决XLSX文件，可以有效解决用户模式内存溢出的问题 
49.  \* 该模式是POI官方推荐的读取大数据的模式，在用户模式下，数据量较大、Sheet较多、或者是有很多无用的空行的情况 
50.  \* ，容易出现内存溢出,用户模式读取Excel的典型代码如下： FileInputStream file=new 
51.  \* FileInputStream("c:\\\\test.xlsx"); Workbook wb=new XSSFWorkbook(file); 
52.  \*  
53.  \*  
54.  \* @author 山人 
55.  \*/  
56. **public** **class** XLSXCovertCSVReader \{  
57.   
58.     /\*\* 
59.      \* The type of the data value is indicated by an attribute on the cell. The 
60.      \* value is usually in a "v" element within the cell. 
61.      \*/  
62.     **enum** xssfDataType \{  
63.         BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER,  
64.     \}  
65.   
66.     /\*\* 
67.      \* 使用xssf\_sax\_API处理Excel,请参考： http://poi.apache.org/spreadsheet/how-to.html\#xssf\_sax\_api 
68.      \* <p/> 
69.      \* Also see Standard ECMA-376, 1st edition, part 4, pages 1928ff, at 
70.      \* http://www.ecma-international.org/publications/standards/Ecma-376.htm 
71.      \* <p/> 
72.      \* A web-friendly version is http://openiso.org/Ecma/376/Part4 
73.      \*/  
74.     **class** MyXSSFSheetHandler **extends** DefaultHandler \{  
75.   
76.         /\*\* 
77.          \* Table with styles 
78.          \*/  
79.         **private** StylesTable stylesTable;  
80.   
81.         /\*\* 
82.          \* Table with unique strings 
83.          \*/  
84.         **private** ReadOnlySharedStringsTable sharedStringsTable;  
85.   
86.         /\*\* 
87.          \* Destination for data 
88.          \*/  
89.         **private** **final** PrintStream output;  
90.   
91.         /\*\* 
92.          \* Number of columns to read starting with leftmost 
93.          \*/  
94.         **private** **final** **int** minColumnCount;  
95.   
96.         // Set when V start element is seen  
97.         **private** **boolean** vIsOpen;  
98.   
99.         // Set when cell start element is seen;  
100.         // used when cell close element is seen.  
101.         **private** xssfDataType nextDataType;  
102.   
103.         // Used to format numeric cell values.  
104.         **private** **short** formatIndex;  
105.         **private** String formatString;  
106.         **private** **final** DataFormatter formatter;  
107.   
108.         **private** **int** thisColumn = -1;  
109.         // The last column printed to the output stream  
110.         **private** **int** lastColumnNumber = -1;  
111.   
112.         // Gathers characters as they are seen.  
113.         **private** StringBuffer value;  
114.         **private** String\[\] record;  
115.         **private** List<String\[\]> rows = **new** ArrayList<String\[\]>();  
116.         **private** **boolean** isCellNull = **false**;  
117.   
118.         /\*\* 
119.          \* Accepts objects needed while parsing. 
120.          \*  
121.          \* @param styles 
122.          \*            Table of styles 
123.          \* @param strings 
124.          \*            Table of shared strings 
125.          \* @param cols 
126.          \*            Minimum number of columns to show 
127.          \* @param target 
128.          \*            Sink for output 
129.          \*/  
130.         **public** MyXSSFSheetHandler(StylesTable styles,  
131.                 ReadOnlySharedStringsTable strings, **int** cols, PrintStream target) \{  
132.             **this**.stylesTable = styles;  
133.             **this**.sharedStringsTable = strings;  
134.             **this**.minColumnCount = cols;  
135.             **this**.output = target;  
136.             **this**.value = **new** StringBuffer();  
137.             **this**.nextDataType = xssfDataType.NUMBER;  
138.             **this**.formatter = **new** DataFormatter();  
139.             record = **new** String\[**this**.minColumnCount\];  
140.             rows.clear();// 每次读取都清空行集合  
141.         \}  
142.   
143.         /\* 
144.          \* (non-Javadoc) 
145.          \*  
146.          \* @see 
147.          \* org.xml.sax.helpers.DefaultHandler\#startElement(java.lang.String, 
148.          \* java.lang.String, java.lang.String, org.xml.sax.Attributes) 
149.          \*/  
150.         **public** **void** startElement(String uri, String localName, String name,  
151.                 Attributes attributes) **throws** SAXException \{  
152.   
153.             **if** ("inlineStr".equals(name) || "v".equals(name)) \{  
154.                 vIsOpen = **true**;  
155.                 // Clear contents cache  
156.                 value.setLength(0);  
157.             \}  
158.             // c => cell  
159.             **else** **if** ("c".equals(name)) \{  
160.                 // Get the cell reference  
161.                 String r = attributes.getValue("r");  
162.                 **int** firstDigit = -1;  
163.                 **for** (**int** c = 0; c < r.length(); ++c) \{  
164.                     **if** (Character.isDigit(r.charAt(c))) \{  
165.                         firstDigit = c;  
166.                         **break**;  
167.                     \}  
168.                 \}  
169.                 thisColumn = nameToColumn(r.substring(0, firstDigit));  
170.   
171.                 // Set up defaults.  
172.                 **this**.nextDataType = xssfDataType.NUMBER;  
173.                 **this**.formatIndex = -1;  
174.                 **this**.formatString = **null**;  
175.                 String cellType = attributes.getValue("t");  
176.                 String cellStyleStr = attributes.getValue("s");  
177.                 **if** ("b".equals(cellType))  
178.                     nextDataType = xssfDataType.BOOL;  
179.                 **else** **if** ("e".equals(cellType))  
180.                     nextDataType = xssfDataType.ERROR;  
181.                 **else** **if** ("inlineStr".equals(cellType))  
182.                     nextDataType = xssfDataType.INLINESTR;  
183.                 **else** **if** ("s".equals(cellType))  
184.                     nextDataType = xssfDataType.SSTINDEX;  
185.                 **else** **if** ("str".equals(cellType))  
186.                     nextDataType = xssfDataType.FORMULA;  
187.                 **else** **if** (cellStyleStr != **null**) \{  
188.                     // It's a number, but almost certainly one  
189.                     // with a special style or format  
190.                     **int** styleIndex = Integer.parseInt(cellStyleStr);  
191.                     XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);  
192.                     **this**.formatIndex = style.getDataFormat();  
193.                     **this**.formatString = style.getDataFormatString();  
194.                     **if** (**this**.formatString == **null**)  
195.                         **this**.formatString = BuiltinFormats  
196.                                 .getBuiltinFormat(**this**.formatIndex);  
197.                 \}  
198.             \}  
199.   
200.         \}  
201.   
202.         /\* 
203.          \* (non-Javadoc) 
204.          \*  
205.          \* @see org.xml.sax.helpers.DefaultHandler\#endElement(java.lang.String, 
206.          \* java.lang.String, java.lang.String) 
207.          \*/  
208.         **public** **void** endElement(String uri, String localName, String name)  
209.                 **throws** SAXException \{  
210.   
211.             String thisStr = **null**;  
212.   
213.             // v => contents of a cell  
214.             **if** ("v".equals(name)) \{  
215.                 // Process the value contents as required.  
216.                 // Do now, as characters() may be called more than once  
217.                 **switch** (nextDataType) \{  
218.   
219.                 **case** BOOL:  
220.                     **char** first = value.charAt(0);  
221.                     thisStr = first == '0' ? "FALSE" : "TRUE";  
222.                     **break**;  
223.   
224.                 **case** ERROR:  
225.                     thisStr = "\\"ERROR:" + value.toString() + '"';  
226.                     **break**;  
227.   
228.                 **case** FORMULA:  
229.                     // A formula could result in a string value,  
230.                     // so always add double-quote characters.  
231.                     thisStr = '"' + value.toString() + '"';  
232.                     **break**;  
233.   
234.                 **case** INLINESTR:  
235.                     // TODO: have seen an example of this, so it's untested.  
236.                     XSSFRichTextString rtsi = **new** XSSFRichTextString(  
237.                             value.toString());  
238.                     thisStr = '"' + rtsi.toString() + '"';  
239.                     **break**;  
240.   
241.                 **case** SSTINDEX:  
242.                     String sstIndex = value.toString();  
243.                     **try** \{  
244.                         **int** idx = Integer.parseInt(sstIndex);  
245.                         XSSFRichTextString rtss = **new** XSSFRichTextString(  
246.                                 sharedStringsTable.getEntryAt(idx));  
247.                         thisStr = '"' + rtss.toString() + '"';  
248.                     \} **catch** (NumberFormatException ex) \{  
249.                         output.println("Failed to parse SST index '" + sstIndex  
250.                                 + "': " + ex.toString());  
251.                     \}  
252.                     **break**;  
253.   
254.                 **case** NUMBER:  
255.                     String n = value.toString();  
256.                     // 判断是否是日期格式  
257.                     **if** (HSSFDateUtil.isADateFormat(**this**.formatIndex, n)) \{  
258.                         Double d = Double.parseDouble(n);  
259.                         Date date=HSSFDateUtil.getJavaDate(d);  
260.                         thisStr=formateDateToString(date);  
261.                     \} **else** **if** (**this**.formatString != **null**)  
262.                         thisStr = formatter.formatRawCellContents(  
263.                                 Double.parseDouble(n), **this**.formatIndex,  
264.                                 **this**.formatString);  
265.                     **else**  
266.                         thisStr = n;  
267.                     **break**;  
268.   
269.                 **default**:  
270.                     thisStr = "(TODO: Unexpected type: " + nextDataType + ")";  
271.                     **break**;  
272.                 \}  
273.   
274.                 // Output after we've seen the string contents  
275.                 // Emit commas for any fields that were missing on this row  
276.                 **if** (lastColumnNumber == -1) \{  
277.                     lastColumnNumber = 0;  
278.                 \}  
279.                 //判断单元格的值是否为空  
280.                 **if** (thisStr == **null** || "".equals(isCellNull)) \{  
281.                     isCellNull = **true**;// 设置单元格是否为空值  
282.                 \}  
283.                 record\[thisColumn\] = thisStr;  
284.                 // Update column  
285.                 **if** (thisColumn > -1)  
286.                     lastColumnNumber = thisColumn;  
287.   
288.             \} **else** **if** ("row".equals(name)) \{  
289.   
290.                 // Print out any missing commas if needed  
291.                 **if** (minColumns > 0) \{  
292.                     // Columns are 0 based  
293.                     **if** (lastColumnNumber == -1) \{  
294.                         lastColumnNumber = 0;  
295.                     \}  
296.                     **if** (isCellNull == **false** && record\[0\] != **null**  
297.                             && record\[1\] != **null**)// 判断是否空行  
298.                     \{  
299.                         rows.add(record.clone());  
300.                         isCellNull = **false**;  
301.                         **for** (**int** i = 0; i < record.length; i++) \{  
302.                             record\[i\] = **null**;  
303.                         \}  
304.                     \}  
305.                 \}  
306.                 lastColumnNumber = -1;  
307.             \}  
308.   
309.         \}  
310.   
311.         **public** List<String\[\]> getRows() \{  
312.             **return** rows;  
313.         \}  
314.   
315.         **public** **void** setRows(List<String\[\]> rows) \{  
316.             **this**.rows = rows;  
317.         \}  
318.   
319.         /\*\* 
320.          \* Captures characters only if a suitable element is open. Originally 
321.          \* was just "v"; extended for inlineStr also. 
322.          \*/  
323.         **public** **void** characters(**char**\[\] ch, **int** start, **int** length)  
324.                 **throws** SAXException \{  
325.             **if** (vIsOpen)  
326.                 value.append(ch, start, length);  
327.         \}  
328.   
329.         /\*\* 
330.          \* Converts an Excel column name like "C" to a zero-based index. 
331.          \*  
332.          \* @param name 
333.          \* @return Index corresponding to the specified name 
334.          \*/  
335.         **private** **int** nameToColumn(String name) \{  
336.             **int** column = -1;  
337.             **for** (**int** i = 0; i < name.length(); ++i) \{  
338.                 **int** c = name.charAt(i);  
339.                 column = (column + 1) \* 26 + c - 'A';  
340.             \}  
341.             **return** column;  
342.         \}  
343.   
344.         **private** String formateDateToString(Date date) \{  
345.             SimpleDateFormat sdf = **new** SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//格式化日期  
346.             **return** sdf.format(date);  
347.   
348.         \}  
349.   
350.     \}  
351.   
352.     // /  
353.   
354.     **private** OPCPackage xlsxPackage;  
355.     **private** **int** minColumns;  
356.     **private** PrintStream output;  
357.     **private** String sheetName;  
358.   
359.     /\*\* 
360.      \* Creates a new XLSX -> CSV converter 
361.      \*  
362.      \* @param pkg 
363.      \*            The XLSX package to process 
364.      \* @param output 
365.      \*            The PrintStream to output the CSV to 
366.      \* @param minColumns 
367.      \*            The minimum number of columns to output, or -1 for no minimum 
368.      \*/  
369.     **public** XLSXCovertCSVReader(OPCPackage pkg, PrintStream output,  
370.             String sheetName, **int** minColumns) \{  
371.         **this**.xlsxPackage = pkg;  
372.         **this**.output = output;  
373.         **this**.minColumns = minColumns;  
374.         **this**.sheetName = sheetName;  
375.     \}  
376.   
377.     /\*\* 
378.      \* Parses and shows the content of one sheet using the specified styles and 
379.      \* shared-strings tables. 
380.      \*  
381.      \* @param styles 
382.      \* @param strings 
383.      \* @param sheetInputStream 
384.      \*/  
385.     **public** List<String\[\]> processSheet(StylesTable styles,  
386.             ReadOnlySharedStringsTable strings, InputStream sheetInputStream)  
387.             **throws** IOException, ParserConfigurationException, SAXException \{  
388.   
389.         InputSource sheetSource = **new** InputSource(sheetInputStream);  
390.         SAXParserFactory saxFactory = SAXParserFactory.newInstance();  
391.         SAXParser saxParser = saxFactory.newSAXParser();  
392.         XMLReader sheetParser = saxParser.getXMLReader();  
393.         MyXSSFSheetHandler handler = **new** MyXSSFSheetHandler(styles, strings,  
394.                 **this**.minColumns, **this**.output);  
395.         sheetParser.setContentHandler(handler);  
396.         sheetParser.parse(sheetSource);  
397.         **return** handler.getRows();  
398.     \}  
399.   
400.     /\*\* 
401.      \* 初始化这个处理程序 将 
402.      \*  
403.      \* @throws IOException 
404.      \* @throws OpenXML4JException 
405.      \* @throws ParserConfigurationException 
406.      \* @throws SAXException 
407.      \*/  
408.     **public** List<String\[\]> process() **throws** IOException, OpenXML4JException,  
409.             ParserConfigurationException, SAXException \{  
410.   
411.         ReadOnlySharedStringsTable strings = **new** ReadOnlySharedStringsTable(  
412.                 **this**.xlsxPackage);  
413.         XSSFReader xssfReader = **new** XSSFReader(**this**.xlsxPackage);  
414.         List<String\[\]> list = **null**;  
415.         StylesTable styles = xssfReader.getStylesTable();  
416.         XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader  
417.                 .getSheetsData();  
418.         **int** index = 0;  
419.         **while** (iter.hasNext()) \{  
420.             InputStream stream = iter.next();  
421.             String sheetNameTemp = iter.getSheetName();  
422.             **if** (**this**.sheetName.equals(sheetNameTemp)) \{  
423.                 list = processSheet(styles, strings, stream);  
424.                 stream.close();  
425.                 ++index;  
426.             \}  
427.         \}  
428.         **return** list;  
429.     \}  
430.   
431.     /\*\* 
432.      \* 读取Excel 
433.      \*  
434.      \* @param path 
435.      \*            文件路径 
436.      \* @param sheetName 
437.      \*            sheet名称 
438.      \* @param minColumns 
439.      \*            列总数 
440.      \* @return 
441.      \* @throws SAXException 
442.      \* @throws ParserConfigurationException 
443.      \* @throws OpenXML4JException 
444.      \* @throws IOException 
445.      \*/  
446.     **private** **static** List<String\[\]> readerExcel(String path, String sheetName,  
447.             **int** minColumns) **throws** IOException, OpenXML4JException,  
448.             ParserConfigurationException, SAXException \{  
449.         OPCPackage p = OPCPackage.open(path, PackageAccess.READ);  
450.         XLSXCovertCSVReader xlsx2csv = **new** XLSXCovertCSVReader(p, System.out,  
451.                 sheetName, minColumns);  
452.         List<String\[\]> list = xlsx2csv.process();  
453.         p.close();  
454.         **return** list;  
455.     \}  
456.   
457.     **public** **static** **void** main(String\[\] args) **throws** Exception \{  
458.         List<String\[\]> list = XLSXCovertCSVReader  
459.                 .readerExcel(  
460.                         "F:\\\\test.xlsx",  
461.                         "Sheet1", 17);  
462.         **for** (String\[\] record : list) \{  
463.             **for** (String cell : record) \{  
464.                 System.out.print(cell + "  ");  
465.             \}  
466.             System.out.println();  
467.         \}  
468.     \}  
469.   
470. \}  
471.

[Link 1]: http://lib.csdn.net/base/hadoop
[view plain]: http://blog.csdn.net/lishengbo/article/details/40711769#
[CODE]: https://code.csdn.net/assets/CODE_ico.png
[CODE_CODE]: https://code.csdn.net/snippets/505280
[ico_fork.svg]: https://code.csdn.net/assets/ico_fork.svg
[ico_fork.svg 1]: https://code.csdn.net/snippets/505280/fork