Java 通过继承POI解析大数据量Excel处理方法_java解析大数据量的excel

//解决思路：重写process()，processRecord()两个方法，其中processRecord是核心方法，用于处理sheetName和各种单元格数字类型。

package org.poi;

import org.apache.poi.hssf.eventusermodel.;
import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord;
import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord;
import org.apache.poi.hssf.model.HSSFFormulaParser;
import org.apache.poi.hssf.record.;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;

/**

• @author y

• @desc 用于解决.xls2003版本大数据量问题
  **/
  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 cellList = new ArrayList();

  /**

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

  @SuppressWarnings(“unused”)
  private String sheetName;

  /**

  • 遍历excel下所有的sheet

  • @param fileName

  • @throws Exception
    */
    public int process(String fileName) throws Exception {
    filePath = fileName;
    this.fs = new POIFSFileSystem(new FileInputStream(fileName));
    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);

    return totalRows; //返回该excel文件的总行数，不包括首列和空行
    }

  /**

  • 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 = "";
         cellList.add(thisColumn, thisStr);
         break;
     case BoolErrRecord.sid: //单元格为布尔类型
         BoolErrRecord berec = (BoolErrRecord) record;
         thisRow = berec.getRow();
         thisColumn = berec.getColumn();
         thisStr = berec.getBooleanValue() + "";
         cellList.add(thisColumn, thisStr);
         checkRowIsNull(thisStr);  //如果里面某个单元格含有值，则标识该行不为空行
         break;
     case FormulaRecord.sid://单元格为公式类型
         FormulaRecord frec = (FormulaRecord) record;
         thisRow = frec.getRow();
         thisColumn = frec.getColumn();
         if (outputFormulaValues) {
             if (Double.isNaN(frec.getValue())) {
                 outputNextStringRecord 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31