POI处理大excel

综述

POI常规的api(Workbook、Sheet等)在处理excel时，会将整个excel的内容加载到内存，碰到大excel时容易OOM。这里“大”并不是单纯的指文件size，一是因为excel是经过压缩，虽然size不大，但是实际内存可能很多；二是excel实际上是使用XML描述的，XML完整的加载到内存，映射成对象时，除了字段的值占内存外，字段本身（指针）也占内存（参考容纳同样的信息，Map比Vo占内存），有些excel比较复杂，会包含大量的元数据，也会造成excel加载到内存后，占用空间较大。
基于上述问题，POI也开放了其他的API，用于在内存受限的场景处理Excel。分别是

• Event API (HSSF Only)
• Event API with extensions to be Record Aware (HSSF Only)
• XSSF and SAX (Event API)
• SXSSF (Streaming User API)

Event API (HSSF Only)、Event API with extensions to be Record Aware (HSSF Only)用于XLS格式的读取；
XSSF and SAX (Event API)用于XLSX格式的读取；
SXSSF (Streaming User API)用于XLSX格式的写入。
以下文档翻译至POI官网：https://poi.apache.org/components/spreadsheet/how-to.html

Event API (HSSF Only)

event API比User API新。它适用于愿意学习一点低层 API 的中级开发人员。它使用起来相对简单，但需要对 Excel 文件的各个部分有基本的了解。它可以在读取XLS时占用较小的内存。

/**
 * This example shows how to use the event API for reading a file.
 */
public class EventExample
        implements HSSFListener
{
    private SSTRecord sstrec;
    /**
     * This method listens for incoming records and handles them as required.
     * @param record    The record that was found while reading.
     */
    public void processRecord(Record record)
    {
        switch (record.getSid())
        {
            // the BOFRecord can represent either the beginning of a sheet or the workbook
            case BOFRecord.sid:
                BOFRecord bof = (BOFRecord) record;
                if (bof.getType() == bof.TYPE_WORKBOOK)
                {
                    System.out.println("Encountered workbook");
                    // assigned to the class level member
                } else if (bof.getType() == bof.TYPE_WORKSHEET)
                {
                    System.out.println("Encountered sheet reference");
                }
                break;
            case BoundSheetRecord.sid:
                BoundSheetRecord bsr = (BoundSheetRecord) record;
                System.out.println("New sheet named: " + bsr.getSheetname());
                break;
            case RowRecord.sid:
                RowRecord rowrec = (RowRecord) record;
                System.out.println("Row found, first column at "
                        + rowrec.getFirstCol() + " last column at " + rowrec.getLastCol());
                break;
            case NumberRecord.sid:
                NumberRecord numrec = (NumberRecord) record;
                System.out.println("Cell found with value " + numrec.getValue()
                        + " at row " + numrec.getRow() + " and column " + numrec.getColumn());
                break;
                // SSTRecords store a array of unique strings used in Excel.
            case SSTRecord.sid:
                sstrec = (SSTRecord) record;
                for (int k = 0; k < sstrec.getNumUniqueStrings(); k++)
                {
                    System.out.println("String table value " + k + " = " + sstrec.getString(k));
                }
                break;
            case LabelSSTRecord.sid:
                LabelSSTRecord lrec = (LabelSSTRecord) record;
                System.out.println("String cell found with value "
                        + sstrec.getString(lrec.getSSTIndex()));
                break;
        }
    }
    /**
     * Read an excel file and spit out what we find.
     *
     * @param args      Expect one argument that is the file to read.
     * @throws IOException  When there is an error processing the file.
     */
    public static void main(String[] args) throws IOException
    {
        // create a new file input stream with the input file specified
        // at the command line
        FileInputStream fin = new FileInputStream(args[0]);
        // create a new org.apache.poi.poifs.filesystem.Filesystem
        POIFSFileSystem poifs = new POIFSFileSystem(fin);
        // get the Workbook (excel part) stream in a InputStream
        InputStream din = poifs.createDocumentInputStream("Workbook");
        // construct out HSSFRequest object
        HSSFRequest req = new HSSFRequest();
        // lazy listen for ALL records with the listener shown above
        req.addListenerForAllRecords(new EventExample());
        // create our event factory
        HSSFEventFactory factory = new HSSFEventFactory();
        // process our events based on the document input stream
        factory.processEvents(req, din);
        // once all the events are processed close our file input stream
        fin.close();
        // and our document input stream (don't want to leak these!)
        din.close();
        System.out.println("done.");
    }
}

待续