前言
网上关于POI处理大文件的方案有很多,但是都比较古老,我集中整理了一下,可以直接copy到项目中使用。关于小数等精度问题也处理好了,大家使用自己注意下就好。目前我配置2G内存,支持100m左右的excel,不会内存溢出,体验良好
上代码
先写接口
/**
* <code>BigDataParseExcel</code>
*
* @see
* @since 2019/4/15 v1.0
*/
public interface BigDataParseExcel {
abstract void optRows(int sheetIndex, int curRow,
List<String> rowList);
}
07版的抽象类
/**
* <code>BigDataParseExcelUtil</code>
* <p>
* XSSF and SAX (Event API)
* </p>
*
* @author daguozb
* @link https://www.cnblogs.com/daguozb/p/10031970.html
* @see
* @since 2019/4/15 v1.0
*/
/**
* @author y
* @create 2018-01-18 14:28
* @desc POI读取excel有两种模式,一种是用户模式,一种是事件驱动模式
* 采用SAX事件驱动模式解决XLSX文件,可以有效解决用户模式内存溢出的问题,
* 该模式是POI官方推荐的读取大数据的模式,
* 在用户模式下,数据量较大,Sheet较多,或者是有很多无用的空行的情况下,容易出现内存溢出
* <p>
* 用于解决.xlsx2007版本大数据量问题
**/
public abstract class BigDataParseExcelUtil extends DefaultHandler implements BigDataParseExcel {
/**
* 单元格中的数据可能的数据类型
*/
enum CellDataType {
BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL
}
/**
* 共享字符串表
*/
private SharedStringsTable sst;
/**
* 上一次的索引值
*/
private String lastIndex;
/**
* 文件的绝对路径
*/
private String filePath = "";
/**
* 工作表索引
*/
private int sheetIndex = 0;
/**
* sheet名
*/
private String sheetName = "";
/**
* 总行数
*/
private int totalRows=0;
/**
* 一行内cell集合
*/
private List<String> cellList = new ArrayList<>();
/**
* 判断整行是否为空行的标记
*/
private boolean flag = false;
/**
* 当前行
*/
private int curRow = 1;
/**
* 当前列
*/
private int curCol = 0;
/**
* T元素标识
*/
private boolean isTElement;
/**
* 单元格数据类型,默认为字符串类型
*/
private CellDataType nextDataType = CellDataType.SSTINDEX;
private final DataFormatter formatter = new DataFormatter();
/**
* 单元格日期格式的索引
*/
private short formatIndex;
/**
* 日期格式字符串
*/
private String formatString;
//定义前一个元素和当前元素的位置,用来计算其中空的单元格数量,如A6和A8等
private String preRef = null, ref = null;
//定义该文档一行最大的单元格数,用来补全一行最后可能缺失的单元格
private String maxRef = null;
/**
* 单元格
*/
private StylesTable stylesTable;
/**
* 遍历工作簿中所有的电子表格
* 并缓存在mySheetList中
*
* @throws Exception
*/
public int process(OPCPackage pkg) throws Exception {
XSSFReader xssfReader = new XSSFReader(pkg);
stylesTable = xssfReader.getStylesTable();
SharedStringsTable sst = xssfReader.getSharedStringsTable();
XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
this.sst = sst;
parser.setContentHandler(this);
XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
while (sheets.hasNext()) {
//遍历sheet
curRow = 1; //标记初始行为第一行
sheetIndex++;
InputStream sheet = sheets.next(); //sheets.next()和sheets.getSheetName()不能换位置,否则sheetName报错
sheetName = sheets.getSheetName();
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource); //解析excel的每条记录,在这个过程中startElement()、characters()、endElement()这三个函数会依次执行
sheet.close();
}
return totalRows; //返回该excel文件的总行数,不包括首列和空行
}
public void readExcel(String fileName) throws Exception {
process(OPCPackage.open(fileName));
}
public void readExcel(InputStream inputStream) throws Exception {
process(OPCPackage.open(inputStream));
}
/**
* 第一个执行
*
* @param uri
* @param localName
* @param name
* @param attributes
* @throws SAXException
*/
@Override
public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
//c => 单元格
if ("c".equals(name)) {
//前一个单元格的位置
if (preRef == null) {
preRef = attributes.getValue("r");
} else {
preRef = ref;
}
//当前单元格的位置
ref = attributes.getValue("r");
//设定单元格类型
this.setNextDataType(attributes);
}
//当元素为t时
if ("t".equals(name)) {
isTElement = true;
} else {
isTElement = false;
}
//置空
lastIndex = "";
}
/**
* 第二个执行
* 得到单元格对应的索引值或是内容值
* 如果单元格类型是字符串、INLINESTR、数字、日期,lastIndex则是索引值
* 如果单元格类型是布尔值、错误、公式,lastIndex则是内容值
* @param ch
* @param start
* @param length
* @throws SAXException
*/
@Override
public void characters(char[] ch, int start, int length) throws SAXException {
lastIndex += new String(ch, start, length);
}
/**
* 第三个执行
*
* @param uri
* @param localName
* @param name
* @throws SAXException
*/
@Override
public void endElement(String uri, String localName, String name) throws SAXException {
//t元素也包含字符串
if (isTElement) {
//这个程序没经过
//将单元格内容加入rowlist中,在这之前先去掉字符串前后的空白符
String value = lastIndex.trim();
cellList.add(curCol, value);
curCol++;
isTElement = false;
//如果里面某个单元格含有值,则标识该行不为空行
if (value != null && !"".equals(value)) {
flag = true;
}
} else if ("v".equals(name)) {
//v => 单元格的值,如果单元格是字符串,则v标签的值为该字符串在SST中的索引
String value = this.getDataValue(lastIndex.trim(), "");//根据索引值获取对应的单元格值
//补全单元格之间的空单元格
if (!ref.equals(preRef)) {
int len = countNullCell(ref, preRef);
for (int i