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

最新推荐文章于 2023-09-22 14:28:52 发布

m0_54603737

最新推荐文章于 2023-09-22 14:28:52 发布

阅读量528

点赞数

文章标签： java excel 开发语言

本文链接：https://blog.csdn.net/m0_54603737/article/details/128564350

版权

本文介绍了Java通过实现HSSFListener接口，使用POI的事件驱动模式来处理大数据量的Excel文件。主要涉及重写process()和processRecord()方法，针对.xls和.xlsx文件的不同处理方式，以及如何避免内存溢出。代码示例展示了如何读取Excel中的数据，同时提供了一个通用的读取工具类ExcelReaderUtil。

摘要由CSDN通过智能技术生成

//解决思路：重写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 
```