Excel大批量数据的导入和导出,如何做优化?

开发 后端
这篇文章跟大家说下如果避免踩POI的坑,以及分别对于xls和xlsx文件怎么优化大批量数据的导入和导出。

 概要

Java对Excel的操作一般都是用POI,但是数据量大的话可能会导致频繁的FGC或OOM,这篇文章跟大家说下如果避免踩POI的坑,以及分别对于xls和xlsx文件怎么优化大批量数据的导入和导出。

一次线上问题

这是一次线上的问题,因为一个大数据量的Excel导出功能,而导致服务器频繁FGC,具体如图所示

可以看出POI的对象以及相关的XML对象占用了绝大部分的内存消耗,频繁FGC说明这些对象一直存活,没有被回收。

原因是由于导出的数据比较大量,大概有10w行 * 50列,由于后台直接用XSSFWorkbook导出,在导出结束前内存有大量的Row,Cell,Style等,以及基于XLSX底层存储的XML对象没有被释放。

Excel的存储格式

下面的优化内容涉及Excel的底层存储格式,所以要先跟大家讲一下。

XLS

03版的XLS采用的是一种名为BIFF8(Binary-Interchange-File-Format),基于OLE2规范的二进制文件格式。大概就是一种结构很复杂的二进制文件,具体细节我也不是很清楚,大家也没必要去了解它,已经被淘汰了。想了解的话可以看看Excel XLS文件格式

XLSX

07版的XLSX则是采用OOXML(Office Open Xml)的格式存储数据。简单来说就是一堆xml文件用zip打包之后文件。这个对于大家来说就熟悉了,把xlsx文件后缀名改为zip后,再解压出来就可以看到文件结构

打开sheet1.xml,可以看到是描述第一个sheet的内容

导出优化

事例源码基于POI3.17版本

XLSX

由于xlsx底层使用xml存储,占用内存会比较大,官方也意识到这个问题,在3.8版本之后,提供了SXSSFWorkbook来优化写性能。

官方说明

https://poi.apache.org/components/spreadsheet/how-to.html#sxssf

使用

SXSSFWorkbook使用起来特别的简单,只需要改一行代码就OK了。

原来你的代码可能是长这样的 

  1. Workbook workbook = new XSSFWorkbook(inputStream); 

那么你只需要改成这样子,就可以用上SXSSFWorkbook了 

  1. Workbook workbook = new SXSSFWorkbook(new XSSFWorkbook(inputStream)); 

其原理是可以定义一个window size(默认100),生成Excel期间只在内存维持window size那么多的行数Row,超时window size时会把之前行Row写到一个临时文件并且remove释放掉,这样就可以达到释放内存的效果。

SXSSFSheet在创建Row时会判断并刷盘、释放超过window size的Row。 

  1. @Override  
  2.     public SXSSFRow createRow(int rownum)  
  3.     {  
  4.         int maxrow = SpreadsheetVersion.EXCEL2007.getLastRowIndex();  
  5.         if (rownum < 0 || rownum > maxrow) {  
  6.             throw new IllegalArgumentException("Invalid row number (" + rownum  
  7.                     + ") outside allowable range (0.." + maxrow + ")");  
  8.         }  
  9.         // attempt to overwrite a row that is already flushed to disk  
  10.         if(rownum <= _writer.getLastFlushedRow() ) {  
  11.             throw new IllegalArgumentException(  
  12.                     "Attempting to write a row["+rownum+"] " +  
  13.                     "in the range [0," + _writer.getLastFlushedRow() + "] that is already written to disk.");  
  14.         }  
  15.         // attempt to overwrite a existing row in the input template  
  16.         if(_sh.getPhysicalNumberOfRows() > 0 && rownum <= _sh.getLastRowNum() ) {  
  17.             throw new IllegalArgumentException(  
  18.                     "Attempting to write a row["+rownum+"] " +  
  19.                             "in the range [0," + _sh.getLastRowNum() + "] that is already written to disk.");  
  20.         }  
  21.         SXSSFRow newnewRow=new SXSSFRow(this);  
  22.         _rows.put(rownum,newRow);  
  23.         allFlushed = false 
  24.         //如果大于窗口的size,就会flush  
  25.         if(_randomAccessWindowSize>=0&&_rows.size()>_randomAccessWindowSize)  
  26.         {  
  27.             try  
  28.             {  
  29.                flushRows(_randomAccessWindowSize);  
  30.             }  
  31.             catch (IOException ioe)  
  32.             {  
  33.                 throw new RuntimeException(ioe);  
  34.             }  
  35.         }  
  36.         return newRow;  
  37.     }  
  38.     public void flushRows(int remaining) throws IOException  
  39.     {  
  40.         //flush每一个row  
  41.         while(_rows.size() > remaining) {  
  42.             flushOneRow();  
  43.         }  
  44.         if (remaining == 0) {  
  45.             allFlushed = true 
  46.         }  
  47.     }  
  48.     private void flushOneRow() throws IOException  
  49.     {  
  50.         Integer firstRowNum = _rows.firstKey();  
  51.         if (firstRowNum!=null) {  
  52.             int rowIndex = firstRowNum.intValue();  
  53.             SXSSFRow row = _rows.get(firstRowNum);  
  54.             // Update the best fit column widths for auto-sizing just before the rows are flushed  
  55.             _autoSizeColumnTracker.updateColumnWidths(row);  
  56.             //写盘  
  57.             _writer.writeRow(rowIndex, row);  
  58.             //然后把row remove掉,这里的_rows是一个TreeMap结构  
  59.             _rows.remove(firstRowNum);  
  60.             lastFlushedRowNumber = rowIndex 
  61.         }  
  62.     } 

我们再看看刷盘的具体操作

SXSSFSheet在创建的时候,都会创建一个SheetDataWriter,刷盘动作正是由这个类完成的

看下SheetDataWriter的初始化 

  1. public SheetDataWriter() throws IOException {  
  2.     //创建临时文件  
  3.     _fd = createTempFile();  
  4.     //拿到文件的BufferedWriter  
  5.     _out = createWriter(_fd);  
  6.  
  7. //在本地创建了一个临时文件前缀为poi-sxssf-sheet,后缀为.xml  
  8. public File createTempFile() throws IOException {  
  9.     return TempFile.createTempFile("poi-sxssf-sheet", ".xml"); 
  10.   
  11. public static File createTempFile(String prefix, String suffix) throws IOException {  
  12.     //用一个策略去创建文件  
  13.     return strategy.createTempFile(prefix, suffix);  
  14.  
  15. //这个策略就是在执行路径先创建一个目录(如果不存在的话),然后再在里面创建一个随机唯一命名的文件  
  16. public File createTempFile(String prefix, String suffix) throws IOException {  
  17.     // Identify and create our temp dir, if needed  
  18.     createPOIFilesDirectory(); 
  19.      // Generate a unique new filename   
  20.     File newFile = File.createTempFile(prefix, suffix, dir);  
  21.     // Set the delete on exit flag, unless explicitly disabled  
  22.     if (System.getProperty(KEEP_FILES) == null) {  
  23.         newFile.deleteOnExit();  
  24.     }  
  25.     // All done  
  26.     return newFile;  

POI就是把超过window size的Row刷到临时文件里,然后再把临时文件转为正常的xlsx文件格式输出。

我们看看刷盘时写了什么,SheetDataWriter的writeRow方法 

  1. public void writeRow(int rownum, SXSSFRow row) throws IOException {  
  2.     if (_numberOfFlushedRows == 0)  
  3.         _lowestIndexOfFlushedRows = rownum 
  4.     _numberLastFlushedRow = Math.max(rownum, _numberLastFlushedRow);  
  5.     _numberOfCellsOfLastFlushedRow = row.getLastCellNum();  
  6.     _numberOfFlushedRows++;  
  7.     beginRow(rownum, row);  
  8.     Iterator<Cell> cells = row.allCellsIterator();  
  9.     int columnIndex = 0 
  10.     while (cells.hasNext()) {  
  11.         writeCell(columnIndex++, cells.next());  
  12.     }  
  13.     endRow(); 
  14.   
  15. void beginRow(int rownum, SXSSFRow row) throws IOException {  
  16.     _out.write("<row");  
  17.     writeAttribute("r", Integer.toString(rownum + 1)); 
  18.      if (row.hasCustomHeight()) { 
  19.          writeAttribute("customHeight", "true");  
  20.         writeAttribute("ht", Float.toString(row.getHeightInPoints()));  
  21.     }  
  22.     if (row.getZeroHeight()) {  
  23.         writeAttribute("hidden", "true");  
  24.     }  
  25.     if (row.isFormatted()) { 
  26.          writeAttribute("s", Integer.toString(row.getRowStyleIndex()));  
  27.         writeAttribute("customFormat", "1");  
  28.     }  
  29.     if (row.getOutlineLevel() != 0) {  
  30.         writeAttribute("outlineLevel", Integer.toString(row.getOutlineLevel()));  
  31.     }  
  32.     if(row.getHidden() != null) { 
  33.          writeAttribute("hidden", row.getHidden() ? "1" : "0");  
  34.     }  
  35.     if(row.getCollapsed() != null) {  
  36.         writeAttribute("collapsed", row.getCollapsed() ? "1" : "0");  
  37.     }  
  38.      _out.write(">\n");  
  39.     this._rownum = rownum;  
  40. void endRow() throws IOException {  
  41.     _out.write("</row>\n");  
  42.  
  43. public void writeCell(int columnIndex, Cell cell) throws IOException {  
  44.     if (cell == null) {  
  45.         return;  
  46.     }  
  47.     String ref = new CellReference(_rownum, columnIndex).formatAsString();  
  48.     _out.write("<c");  
  49.     writeAttribute("r", ref);  
  50.     CellStyle cellcellStyle = cell.getCellStyle();  
  51.     if (cellStyle.getIndex() != 0) {  
  52.         // need to convert the short to unsigned short as the indexes can be up to 64k  
  53.         // ideally we would use int for this index, but that would need changes to some more   
  54.         // APIs  
  55.         writeAttribute("s", Integer.toString(cellStyle.getIndex() & 0xffff));  
  56.     }  
  57.     CellType cellcellType = cell.getCellTypeEnum();  
  58.     switch (cellType) { 
  59.          case BLANK: {  
  60.             _out.write('>');  
  61.             break;  
  62.         }  
  63.         case FORMULA: {  
  64.             _out.write("><f>");  
  65.             outputQuotedString(cell.getCellFormula());  
  66.             _out.write("</f>");  
  67.             switch (cell.getCachedFormulaResultTypeEnum()) {  
  68.                 case NUMERIC:  
  69.                     double nval = cell.getNumericCellValue();  
  70.                     if (!Double.isNaN(nval)) {  
  71.                         _out.write("<v>");  
  72.                         _out.write(Double.toString(nval));  
  73.                         _out.write("</v>");  
  74.                     }  
  75.                     break;  
  76.                 default:  
  77.                     break;  
  78.             }  
  79.             break;  
  80.         }  
  81.         case STRING: {  
  82.             if (_sharedStringSource != null) {  
  83.                 XSSFRichTextString rt = new XSSFRichTextString(cell.getStringCellValue());  
  84.                 int sRef = _sharedStringSource.addEntry(rt.getCTRst());  
  85.                 writeAttribute("t", STCellType.S.toString());  
  86.                 _out.write("><v>");  
  87.                 _out.write(String.valueOf(sRef));  
  88.                 _out.write("</v>");  
  89.             } else {  
  90.                 writeAttribute("t", "inlineStr");  
  91.                 _out.write("><is><t");  
  92.                 if (hasLeadingTrailingSpaces(cell.getStringCellValue())) {  
  93.                     writeAttribute("xml:space", "preserve");  
  94.                 }  
  95.                 _out.write(">");  
  96.                 outputQuotedString(cell.getStringCellValue());  
  97.                 _out.write("</t></is>");  
  98.             }  
  99.             break;  
  100.         }  
  101.         case NUMERIC: {  
  102.             writeAttribute("t", "n");  
  103.             _out.write("><v>");  
  104.             _out.write(Double.toString(cell.getNumericCellValue()));  
  105.             _out.write("</v>");  
  106.             break;  
  107.         }  
  108.         case BOOLEAN: { 
  109.              writeAttribute("t", "b");  
  110.             _out.write("><v>");  
  111.             _out.write(cell.getBooleanCellValue() ? "1" : "0");  
  112.             _out.write("</v>");  
  113.             break;  
  114.         }  
  115.         case ERROR: {  
  116.             FormulaError error = FormulaError.forInt(cell.getErrorCellValue());  
  117.             writeAttribute("t", "e");  
  118.             _out.write("><v>");  
  119.             _out.write(error.getString());  
  120.             _out.write("</v>");  
  121.             break;  
  122.         }  
  123.         default: {  
  124.             throw new IllegalStateException("Invalid cell type: " + cellType);  
  125.         }  
  126.     }  
  127.     _out.write("</c>");  

可以看到临时文件里内容跟xlsx的文件格式是保持一致的。

测试

本地测试使用SXSSFWorkbook导出30w行 * 10列内存使用情况

可以看出内存有被回收的情况,比较平稳。

XLS

POI没有像XLSX那样对XLS的写做出性能的优化,原因是:

  •  官方认为XLS的不像XLSX那样占内存
  •  XLS一个Sheet最多也只能有65535行数据

导入优化

POI对导入分为3种模式,用户模式User Model,事件模式Event Model,还有Event User Model。

用户模式

用户模式(User Model)就类似于dom方式的解析,是一种high level api,给人快速、方便开发用的。缺点是一次性将文件读入内存,构建一颗Dom树。并且在POI对Excel的抽象中,每一行,每一个单元格都是一个对象。当文件大,数据量多的时候对内存的占用可想而知。

用户模式就是类似用 WorkbookFactory.create(inputStream),poi 会把整个文件一次性解析,生成全部的Sheet,Row,Cell以及对象,如果导入文件数据量大的话,也很可能会导致OOM。

本地测试用户模式读取XLSX文件,数据量10w行 * 50列,内存使用如下

事件模式

事件模式(Event Model)就是SAX解析。Event Model使用的方式是边读取边解析,并且不会将这些数据封装成Row,Cell这样的对象。而都只是普通的数字或者是字符串。并且这些解析出来的对象是不需要一直驻留在内存中,而是解析完使用后就可以回收。所以相比于User Model,Event Model更节省内存,效率也更。

但是作为代价,相比User Model功能更少,门槛也要高一些。我们需要去学习Excel存储数据的各个Xml中每个标签,标签中的属性的含义,然后对解析代码进行设计。

User Event Model

User Event Model也是采用流式解析,但是不同于Event Model,POI基于Event Model为我们封装了一层。我们不再面对Element的事件编程,而是面向StartRow,EndRow,Cell等事件编程。而提供的数据,也不再像之前是原始数据,而是全部格式化好,方便开发者开箱即用。大大简化了我们的开发效率。

XLSX

POI对XLSX支持Event Model和Event User Model

XLSX的Event Model

使用

官网例子:

http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/examples/xssf/eventusermodel/FromHowTo.java

简单来说就是需要继承DefaultHandler,覆盖其startElement,endElement方法。然后方法里获取你想要的数据。

原理

DefaultHandler相信熟悉的人都知道,这是JDK自带的对XML的SAX解析用到处理类,POI在进行SAX解析时,把读取到每个XML的元素时则会回调这两个方法,然后我们就可以获取到想用的数据了。

我们回忆一下上面说到的XLSX存储格式中sheet存储数据的格式。

再看看官方例子中的解析过程 

  1. @Override  
  2. public void startElement(String uri, String localName, String name,  
  3.                             Attributes attributes) throws SAXException {  
  4.     //c代表是一个单元格cell,判断c这个xml元素里面属性attribute t  
  5.     // c => cell  
  6.     if(name.equals("c")) {  
  7.         // Print the cell reference  
  8.         System.out.print(attributes.getValue("r") + " - ");  
  9.         // Figure out if the value is an index in the SST  
  10.         String cellType = attributes.getValue("t");  
  11.         nextIsString = cellType != null && cellType.equals("s");  
  12.         inlineStr = cellType != null && cellType.equals("inlineStr");  
  13.     }  
  14.     // Clear contents cache  
  15.     lastContents = "" 
  16.  
  17. @Override 
  18.  public void endElement(String uri, String localName, String name)  
  19.         throws SAXException {  
  20.     // Process the last contents as required.  
  21.     // Do now, as characters() may be called more than once  
  22.     if(nextIsString) {  
  23.         Integer idx = Integer.valueOf(lastContents);  
  24.         lastContents = lruCache.get(idx);  
  25.         if (lastContents == null && !lruCache.containsKey(idx)) {  
  26.             lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();  
  27.             lruCache.put(idx, lastContents);  
  28.         }  
  29.         nextIsString = false 
  30.     }  
  31.     //v 元素代表这个cell的内容  
  32.     // v => contents of a cell  
  33.     // Output after we've seen the string contents  
  34.     if(name.equals("v") || (inlineStr && name.equals("c"))) {  
  35.         System.out.println(lastContents);  
  36.     }  

可以看出你需要对XLSX的XML格式清楚,才能获取到你想要的东西。

XLSX的Event User Model

使用

官方例子

https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/examples/xssf/eventusermodel/XLSX2CSV.java

简单来说就是继承XSSFSheetXMLHandler.SheetContentsHandler,覆盖其startRow,endRow,cell,endSheet 等方法。POI每开始读行,结束读行,读取一个cell,结束读取一个sheet时回调的方法。从方法名上看Event User Model有更好的用户体验。

原理

其实Event User Model也是 Event Model的封装,在XSSFSheetXMLHandler(其实也是一个DefaultHandler来的)中持有一个SheetContentsHandler,在其startElement,endElement方法中会调用SheetContentsHandler的startRow,endRow,cell,endSheet等方法。

我们看看XSSFSheetXMLHandler的startElement和endElement方法 

  1. public void startElement(String uri, String localName, String qName,  
  2.                          Attributes attributes) throws SAXException {  
  3.     if (uri != null && ! uri.equals(NS_SPREADSHEETML)) {  
  4.         return;  
  5.     }  
  6.     if (isTextTag(localName)) {  
  7.         vIsOpen = true 
  8.         // Clear contents cache  
  9.         value.setLength(0);  
  10.     } else if ("is".equals(localName)) { 
  11.        // Inline string outer tag  
  12.        isIsOpen = true 
  13.     } else if ("f".equals(localName)) {  
  14.        // Clear contents cache  
  15.        formula.setLength(0);    
  16.         // Mark us as being a formula if not already  
  17.        if(nextDataType == xssfDataType.NUMBER) {  
  18.           nextDataType = xssfDataType.FORMULA;  
  19.        }      
  20.         // Decide where to get the formula string from  
  21.        String type = attributes.getValue("t");  
  22.        if(type != null && type.equals("shared")) {  
  23.           // Is it the one that defines the shared, or uses it?  
  24.           String ref = attributes.getValue("ref");  
  25.           String si = attributes.getValue("si");          
  26.            if(ref != null) { 
  27.              // This one defines it  
  28.              // TODO Save it somewhere  
  29.              fIsOpen = true 
  30.           } else {  
  31.              // This one uses a shared formula  
  32.              // TODO Retrieve the shared formula and tweak it to   
  33.              //  match the current cell  
  34.              if(formulasNotResults) {  
  35.                  logger.log(POILogger.WARN, "shared formulas not yet supported!");  
  36.              } /*else {  
  37.                 // It's a shared formula, so we can't get at the formula string yet  
  38.                 // However, they don't care about the formula string, so that's ok!  
  39.              }*/  
  40.           }  
  41.        } else {  
  42.           fIsOpen = true 
  43.        }  
  44.     } 
  45.      else if("oddHeader".equals(localName) || "evenHeader".equals(localName) ||  
  46.           "firstHeader".equals(localName) || "firstFooter".equals(localName) ||  
  47.           "oddFooter".equals(localName) || "evenFooter".equals(localName)) {  
  48.        hfIsOpen = true 
  49.        // Clear contents cache  
  50.        headerFooter.setLength(0);  
  51.     }  
  52.     else if("row".equals(localName)) {  
  53.         String rowNumStr = attributes.getValue("r");  
  54.         if(rowNumStr != null) {  
  55.             rowNum = Integer.parseInt(rowNumStr) - 1;  
  56.         } else {  
  57.             rowNum = nextRowNum 
  58.         }  
  59.         //回调了SheetContentsHandler的startRow方法  
  60.         output.startRow(rowNum);  
  61.     }  
  62.     // c => cell  
  63.     else if ("c".equals(localName)) {  
  64.         // Set up defaults. 
  65.          this.nextDataType = xssfDataType.NUMBER;  
  66.         this.formatIndex = -1;  
  67.         this.formatString = null 
  68.         cellRef = attributes.getValue("r");  
  69.         String cellType = attributes.getValue("t");  
  70.         String cellStyleStr = attributes.getValue("s");  
  71.         if ("b".equals(cellType))  
  72.             nextDataType = xssfDataType.BOOLEAN;  
  73.         else if ("e".equals(cellType))  
  74.             nextDataType = xssfDataType.ERROR;  
  75.         else if ("inlineStr".equals(cellType))  
  76.             nextDataType = xssfDataType.INLINE_STRING;  
  77.         else if ("s".equals(cellType))  
  78.             nextDataType = xssfDataType.SST_STRING;  
  79.         else if ("str".equals(cellType))  
  80.             nextDataType = xssfDataType.FORMULA;  
  81.         else {  
  82.             // Number, but almost certainly with a special style or format  
  83.             XSSFCellStyle style = null
  84.              if (stylesTable != null) {  
  85.                 if (cellStyleStr != null) {  
  86.                     int styleIndex = Integer.parseInt(cellStyleStr);  
  87.                     style = stylesTable.getStyleAt(styleIndex);  
  88.                 } else if (stylesTable.getNumCellStyles() > 0) {  
  89.                     style = stylesTable.getStyleAt(0);  
  90.                 }  
  91.             }  
  92.             if (style != null) {  
  93.                 this.formatIndex = style.getDataFormat();  
  94.                 this.formatString = style.getDataFormatString();  
  95.                 if (this.formatString == null)  
  96.                     this.formatString = BuiltinFormats.getBuiltinFormat(this.formatIndex);  
  97.             }  
  98.         }  
  99.     }  

  1. @Override  
  2.  public void endElement(String uri, String localName, String qName)  
  3.         throws SAXException {  
  4.     if (uri != null && ! uri.equals(NS_SPREADSHEETML)) {  
  5.         return;  
  6.     }  
  7.     String thisStr = null 
  8.     // v => contents of a cell  
  9.     if (isTextTag(localName)) {  
  10.         vIsOpen = false      
  11.          // Process the value contents as required, now we have it all  
  12.         switch (nextDataType) {  
  13.             case BOOLEAN:  
  14.                 char first = value.charAt(0);  
  15.                 thisStr = first == '0' ? "FALSE" : "TRUE";  
  16.                 break;  
  17.             case ERROR:  
  18.                 thisStr = "ERROR:" + value;  
  19.                 break;  
  20.             case FORMULA:  
  21.                 if(formulasNotResults) {  
  22.                    thisStr = formula.toString();  
  23.                 } else {  
  24.                    String fv = value.toString();                  
  25.                     if (this.formatString != null) { 
  26.                        try {  
  27.                          // Try to use the value as a formattable number  
  28.                          double d = Double.parseDouble(fv);  
  29.                          thisStr = formatter.formatRawCellContents(d, this.formatIndex, this.formatString);  
  30.                       } catch(NumberFormatException e) {  
  31.                          // Formula is a String result not a Numeric one  
  32.                          thisStr = fv 
  33.                       } 
  34.                     } else {  
  35.                       // No formatting applied, just do raw value in all cases  
  36.                       thisStr = fv 
  37.                    }  
  38.                 }  
  39.                 break;  
  40.             case INLINE_STRING:  
  41.                 // TODO: Can these ever have formatting on them?  
  42.                 XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());  
  43.                 thisStr = rtsi.toString();  
  44.                 break;  
  45.             case SST_STRING:  
  46.                 String sstIndex = value.toString();  
  47.                 try {  
  48.                     int idx = Integer.parseInt(sstIndex);  
  49.                     XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx));  
  50.                     thisStr = rtss.toString();  
  51.                 }  
  52.                 catch (NumberFormatException ex) {  
  53.                     logger.log(POILogger.ERROR, "Failed to parse SST index '" + sstIndex, ex);  
  54.                 }  
  55.                 break;  
  56.             case NUMBER:  
  57.                 String n = value.toString();  
  58.                 if (this.formatString != null && n.length() > 0)  
  59.                     thisStr = formatter.formatRawCellContents(Double.parseDouble(n), this.formatIndex, this.formatString);  
  60.                 else  
  61.                     thisStr = n 
  62.                 break; 
  63.             default:  
  64.                 thisStr = "(TODO: Unexpected type: " + nextDataType + ")";  
  65.                 break;  
  66.         }      
  67.          // Do we have a comment for this cell?  
  68.         checkForEmptyCellComments(EmptyCellCommentsCheckType.CELL);  
  69.         XSSFComment comment = commentsTable != null ? commentsTable.findCellComment(new CellAddress(cellRef)) : null;        
  70.          //回调了SheetContentsHandler的cell方法  
  71.         // Output  
  72.         output.cell(cellRef, thisStr, comment);  
  73.     } else if ("f".equals(localName)) {  
  74.        fIsOpen = false 
  75.     } else if ("is".equals(localName)) {  
  76.        isIsOpen = false 
  77.     } else if ("row".equals(localName)) {  
  78.        // Handle any "missing" cells which had comments attached  
  79.        checkForEmptyCellComments(EmptyCellCommentsCheckType.END_OF_ROW);       
  80.         //回调了SheetContentsHandler的endRow方法  
  81.        // Finish up the row  
  82.        output.endRow(rowNum);        
  83.        // some sheets do not have rowNum set in the XML, Excel can read them so we should try to read them as well  
  84.        nextRowNum = rowNum + 1;  
  85.     } else if ("sheetData".equals(localName)) {  
  86.         // Handle any "missing" cells which had comments attached  
  87.         checkForEmptyCellComments(EmptyCellCommentsCheckType.END_OF_SHEET_DATA); 
  88.     }  
  89.     else if("oddHeader".equals(localName) || "evenHeader".equals(localName) ||  
  90.           "firstHeader".equals(localName)) {  
  91.        hfIsOpen = false 
  92.        output.headerFooter(headerFooter.toString(), true, localName);  
  93.     }  
  94.     else if("oddFooter".equals(localName) || "evenFooter".equals(localName) ||  
  95.           "firstFooter".equals(localName)) {  
  96.        hfIsOpen = false 
  97.        output.headerFooter(headerFooter.toString(), false, localName);  
  98.     }  

代码有点多,

  •  一是为了展示一下XSSFSheetXMLHandler解析XML的过程,大家可以粗略看看
  •  二是可以看出Event User Model也是Event Model的封装

测试

本地测试使用Event User Model读取XLSX文件,数据量10w行 * 50列

可以看出内存有回收的情况,比User Model好多了。

XLS

POI对XLS支持Event Model

使用

官方例子

http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/examples/hssf/eventusermodel/XLS2CSVmra.java

需要继承HSSFListener,覆盖processRecord 方法,POI每读取到一个单元格的数据则会回调次方法。

原理

这里涉及BIFF8格式以及POI对其的封装,大家可以了解一下(因为其格式比较复杂,我也不是很清楚)

总结

POI优化了对XLSX的大批量写,以及支持对XLS和XLSX的SAX读,我们在实际开发时需要根据业务量来选择正确的处理,不然可能会导致OOM。

希望这篇文章能给大家启发。另外阿里开源了一个easyexcel,其实做的事情也差不多,大家可以看下。 

 

责任编辑:庞桂玉 来源: Java知音
相关推荐

2020-11-02 09:53:13

Hive数据算法

2018-08-09 08:59:56

数据库MySQL性能优化

2021-06-28 10:25:47

MySQL数据库重复数据

2021-09-14 13:15:43

MySQL数据库脚本

2010-04-26 14:52:05

Oracle大批量数据

2010-11-02 10:52:15

批量清理文件

2010-07-16 09:57:44

SQL Server

2022-08-03 09:11:31

React性能优化

2022-12-29 08:49:40

SpringBootExcel

2012-09-05 09:34:13

AD域导入导出帐号

2020-10-06 18:57:14

PostgreSQL数据库数据导入

2023-09-20 10:04:04

Python工具

2010-10-28 11:55:47

oracle数据导出

2023-12-29 10:04:47

数据分析

2019-11-22 10:24:17

开源技术 趋势

2010-07-21 14:17:36

SQL Server数

2022-09-01 08:42:36

SQL数据项目

2024-02-05 13:28:00

Excel优化服务器

2023-06-29 08:22:43

数据Excel模板

2021-01-26 07:11:26

Redis数据同步数据迁移
点赞
收藏

51CTO技术栈公众号