用户需求将数据每天自动导出到一个Excel，一天一个Sheet，超过30天之后则每导出一天的就删掉前面一天的。
简单地实现一下：通过XSSFWorkbook读Excel，写入当天的数据，根据Sheet数量决定是否删掉前面的Sheet。
需求的数据每天大概20w条左右，列较多。大概到第10个sheet之后，-xxs设置成16G都扛不住，发现在读入Excel时就已经OOM了。
知道POI确实比较占内存，但以前没概念，没想到一个上百M的Excel就会占用了十几G的内存。

解决写大Excel的问题

解决写的问题比较容易，使用SXSSFWorkbook代替XSSFWorkbook即可。SXSSFWorkbook写入时只保留一部分Rows在内存中，其他的都写入到硬盘中。
但是SXSSFWorkbook是不能直接读Excel的，要读取时还是要通过XSSFWorkbook。也就是说一旦打开还是要爆内存。
那么解决方案就有两种：

  1. 每次捞30天的数据，分页通过SXSSFWorkbook写入新Excel覆盖掉之前文件。
  2. 还是要想办法将之前那个Excel读出来，和当天数据汇总后通过SXSSFWorkbook写入新Excel覆盖掉之前文件。

虽然1看起来比较容易实现，但是查一个月的数据的话数据库那块的耗时就大了。
所以还是不能放弃2。

解决读大Excel的问题

读了一些POI的文档，使用SAX读取大Excel并将Excel的内容以文本方式搞到内存。
直接上代码：

  1. package com.csot.util;
  3. import java.io.File;
  4. import java.io.IOException;
  5. import java.io.InputStream;
  6. import java.util.ArrayList;
  7. import java.util.HashMap;
  8. import java.util.List;
  10. import javax.xml.parsers.ParserConfigurationException;
  12. import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
  13. import org.apache.poi.openxml4j.opc.OPCPackage;
  14. import org.apache.poi.openxml4j.opc.PackageAccess;
  15. import org.apache.poi.ss.usermodel.DataFormatter;
  16. import org.apache.poi.ss.util.CellAddress;
  17. import org.apache.poi.ss.util.CellReference;
  18. import org.apache.poi.util.SAXHelper;
  19. import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
  20. import org.apache.poi.xssf.eventusermodel.XSSFReader;
  21. import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
  22. import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler;
  23. import org.apache.poi.xssf.model.StylesTable;
  24. import org.apache.poi.xssf.usermodel.XSSFComment;
  25. import org.xml.sax.ContentHandler;
  26. import org.xml.sax.InputSource;
  27. import org.xml.sax.SAXException;
  28. import org.xml.sax.XMLReader;
  30. public class XlsxReader {
  32.     private OPCPackage xlsxPackage;
  33.     private StringBuilder stringBuilder;
  34.     private List<String> sheetNames;
  35.     private HashMap<String, StringBuilder> sheets = new HashMap<String, StringBuilder>(15);
  37.     public void tryOpen(String path) throws Exception{
  38.         File xlsxFile = new File(path);
  39.         if (!xlsxFile.exists()) {
  40.             System.err.println("Not found or not a file: " + xlsxFile.getPath());
  41.             throw new Exception("File not found");
  42.         }
  44.         OPCPackage pack = OPCPackage.open(xlsxFile.getPath(), PackageAccess.READ);
  45.         pack.close();
  46.     }
  48.     public void read(String path) throws Exception{
  49.         this.sheetNames = new ArrayList<String>();
  50.         this.sheets.clear();
  52.         File xlsxFile = new File(path);
  53.         if (!xlsxFile.exists()) {
  54.             System.err.println("Not found or not a file: " + xlsxFile.getPath());
  55.             throw new Exception("File not found");
  56.         }
  58.         this.xlsxPackage = OPCPackage.open(xlsxFile.getPath(), PackageAccess.READ);
  59.         this.process();
  60.         this.xlsxPackage.close();
  61.     }
  63.     public List<String> getSheetNames(){
  64.         return this.sheetNames;
  65.     }
  67.     public HashMap<String, StringBuilder> getSheetContents(){
  68.         return this.sheets;
  69.     }
  71.     public StringBuilder getSheetContent(String name){
  72.         return this.sheets.get(name);
  73.     }
  75.     /**
  76.      * Initiates the processing of the XLS workbook file to CSV.
  77.      *
  78.      * @throws IOException
  79.      * @throws OpenXML4JException
  80.      * @throws ParserConfigurationException
  81.      * @throws SAXException
  82.      */
  83.     private void process()
  84.             throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {
  85.         ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(this.xlsxPackage);
  86.         XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
  87.         StylesTable styles = xssfReader.getStylesTable();
  88.         XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
  89.         while (iter.hasNext()) {
  90.             InputStream stream = iter.next();
  91.             String sheetName = iter.getSheetName();
  92.             this.stringBuilder = new StringBuilder();
  93.             this.sheetNames.add(sheetName);
  94.             processSheet(styles, strings, new SheetToCSV(), stream);
  95.             this.sheets.put(sheetName, this.stringBuilder);
  96.             stream.close();
  97.         }
  98.     }
  100.     /**
  101.      * Parses and shows the content of one sheet
  102.      * using the specified styles and shared-strings tables.
  103.      *
  104.      * @param styles
  105.      * @param strings
  106.      * @param sheetInputStream
  107.      */
  108.     private void processSheet(
  109.             StylesTable styles,
  110.             ReadOnlySharedStringsTable strings,
  111.             SheetContentsHandler sheetHandler,
  112.             InputStream sheetInputStream)
  113.             throws IOException, ParserConfigurationException, SAXException {
  114.         DataFormatter formatter = new DataFormatter();
  115.         InputSource sheetSource = new InputSource(sheetInputStream);
  116.         try {
  117.             XMLReader sheetParser = SAXHelper.newXMLReader();
  118.             ContentHandler handler = new XSSFSheetXMLHandler(
  119.                     styles, null, strings, sheetHandler, formatter, false);
  120.             sheetParser.setContentHandler(handler);
  121.             sheetParser.parse(sheetSource);
  122.         } catch (ParserConfigurationException e) {
  123.             throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());
  124.         }
  125.     }
  127.     private class SheetToCSV implements SheetContentsHandler {
  128.         private boolean firstCellOfRow = false;
  129.         private int currentRow = -1;
  130.         private int currentCol = -1;
  132.         private void outputMissingRows(int number) {
  133.             for (int i = 0; i < number; i++) {
  134.                 stringBuilder.append('\n');
  135.             }
  136.         }
  138.         @Override
  139.         public void startRow(int rowNum) {
  140.             // If there were gaps, output the missing rows
  141.             outputMissingRows(rowNum - currentRow - 1);
  142.             // Prepare for this row
  143.             firstCellOfRow = true;
  144.             currentRow = rowNum;
  145.             currentCol = -1;
  146.         }
  148.         @Override
  149.         public void endRow(int rowNum) {
  150.             stringBuilder.append('\n');
  151.         }
  153.         @Override
  154.         public void cell(String cellReference, String formattedValue,
  155.                          XSSFComment comment) {
  156.             if (firstCellOfRow) {
  157.                 firstCellOfRow = false;
  158.             } else {
  159.                 stringBuilder.append(',');
  160.             }
  162.             // gracefully handle missing CellRef here in a similar way as XSSFCell does
  163.             if (cellReference == null) {
  164.                 cellReference = new CellAddress(currentRow, currentCol).formatAsString();
  165.             }
  167.             // Did we miss any cells?
  168.             int thisCol = (new CellReference(cellReference)).getCol();
  169.             int missedCols = thisCol - currentCol - 1;
  170.             for (int i = 0; i < missedCols; i++) {
  171.                 stringBuilder.append(',');
  172.             }
  173.             currentCol = thisCol;
  175.             // Number or string?
  176.             try {
  177.                 Double.parseDouble(formattedValue);
  178.                 stringBuilder.append(formattedValue);
  179.             } catch (NumberFormatException e) {
  180.                 //stringBuilder.append('"');
  181.                 stringBuilder.append(formattedValue);
  182.                 //stringBuilder.append('"');
  183.             }
  184.         }
  186.         @Override
  187.         public void headerFooter(String text, boolean isHeader, String tagName) {
  188.         }
  189.     }
  190. }

通过这个Reader将Excel读到一个Map中，就只要几百兆内存了。

其他

其实写的时候突然想到，可以用一个临时表装这些数据，每天通过ETL把数据放到临时表，然后每次导出30天的数据。
但是这不重要了(逃)。