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

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

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

    

[java]   view plain  copy
  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. }  
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值