使用POI读取大文件Excel

前言：前段时间，由于项目中有一个功能，导入大量的数据，整个Excel的容量大概有200M左右，用以前的方法读取很慢，甚至会内存溢出，所以后面改用另外一种方式（驱动模式），其实我也不是很懂，是借鉴了一个前辈的思路（https://www.cnblogs.com/swordfall/p/8298386.html），下面直接上码

 

1.引入jar包，POI的依赖包

<!-- poi office -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.16</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.16</version>
        </dependency>

2.解析Excel2003的类ExcelXlsReader

public class ExcelXlsReader implements HSSFListener {

    private int minColums = -1;

    private POIFSFileSystem fs;

    /**
     * 总行数
     */
    private int totalRows=0;

    /**
     * 上一行row的序号
     */
    private int lastRowNumber;

    /**
     * 上一单元格的序号
     */
    private int lastColumnNumber;

    /**
     * 是否输出formula，还是它对应的值
     */
    private boolean outputFormulaValues = true;

    /**
     * 用于转换formulas
     */
    private EventWorkbookBuilder.SheetRecordCollectingListener workbookBuildingListener;

    //excel2003工作簿
    private HSSFWorkbook stubWorkbook;

    private SSTRecord sstRecord;

    private FormatTrackingHSSFListener formatListener;

    private final HSSFDataFormatter formatter = new HSSFDataFormatter();

    /**
     * 文件的绝对路径
     */
    private String filePath = "";

    //表索引
    private int sheetIndex = 0;

    private BoundSheetRecord[] orderedBSRs;

    @SuppressWarnings("unchecked")
    private ArrayList boundSheetRecords = new ArrayList();

    private int nextRow;

    private int nextColumn;

    private boolean outputNextStringRecord;

    //当前行
    private int curRow = 0;

    //存储一行记录所有单元格的容器
    private List<String> cellList = new ArrayList<String>();

    //第一个sheet列表
    private static List<List<String>> firstSheetList = new ArrayList<>();
    //第二个sheet列表
    private static List<List<String>> secondSheetList = new ArrayList<>();
    //第三个sheet列表
    private static List<List<String>> thirdSheetList = new ArrayList<>();

    private static Map<String,List<List<String>>> allListMap = new HashMap<>();


    /**
     * 判断整行是否为空行的标记
     */
    private boolean flag = false;

    @SuppressWarnings("unused")
    private String sheetName;

    /**
     * 遍历excel下所有的sheet
     *
     * @param file
     * @throws Exception
     */
    public Map<String,List<List<String>>> process(File file) throws Exception {
//        filePath = fileName;
        this.fs = new POIFSFileSystem(new FileInputStream(file));
        MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(this);
        formatListener = new FormatTrackingHSSFListener(listener);
        HSSFEventFactory factory = new HSSFEventFactory();
        HSSFRequest request = new HSSFRequest();
        if (outputFormulaValues) {
            request.addListenerForAllRecords(formatListener);
        } else {
            workbookBuildingListener = new EventWorkbookBuilder.SheetRecordCollectingListener(formatListener);
            request.addListenerForAllRecords(workbookBuildingListener);
        }
        factory.processWorkbookEvents(request, fs);

        allListMap.put("first",new ArrayList<>(firstSheetList));
        allListMap.put("second",new ArrayList<>(secondSheetList));
        allListMap.put("third",new ArrayList<>(thirdSheetList));
        firstSheetList.clear();
        secondSheetList.clear();
        thirdSheetList.clear();
        return allListMap;
    }

    /**
     * HSSFListener 监听方法，处理Record
     * 处理每个单元格
     * @param record
     */
    @SuppressWarnings("unchecked")
    public void processRecord(Record record) {
        int thisRow = -1;
        int thisColumn = -1;
        String thisStr = null;
        String value = null;
        switch (record.getSid()) {
            case BoundSheetRecord.sid:
                boundSheetRecords.add(record);
                break;
            case BOFRecord.sid: //开始处理每个sheet
                BOFRecord br = (BOFRecord) record;
                if (br.getType() == BOFRecord.TYPE_WORKSHEET) {
                    //如果有需要，则建立子工作簿
                    if (workbookBuildingListener != null && stubWorkbook == null) {
                        stubWorkbook = workbookBuildingListener.getStubHSSFWorkbook();
                    }

                    if (orderedBSRs == null) {
                        orderedBSRs = BoundSheetRecord.orderByBofPosition(boundSheetRecords);
                    }
                    sheetName = orderedBSRs[sheetIndex].getSheetname();
                    sheetIndex++;
                }
                break;
            case SSTRecord.sid:
                sstRecord = (SSTRecord) record;
                break;
            case BlankRecord.sid: //单元格为空白
                BlankRecord brec = (BlankRecord) record;
                thisRow = brec.getRow();
                thisColumn = brec.getColumn();
                thisStr = "