采用POI实现大数据量EXCEL2007的数据解析和导入
程序员文章站
2022-07-13 13:15:07
...
针对业务数据导入，网上有很多方法。大多数对超大数据量的处理支持得不是太好。主要有两个大的方面存在问题：
1.由于采用list,当面对大数据量的数据解析导入时，会出现OOM异常而无法解析超大数量。
2.通用性不好，一个excel一个类，代码重用性非常低。
当然还有一些点，比如解析验证、样例数据输出等。
本文档介绍如何将包含超大数据量的EXCEL2007文件，解析为业务对象，通过回调模式，交给回调来处理解析转化好的业务数据对象。
下面直接上代码
一、需要的包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
二、excel2007解析器
解析器对excel2007文件逐行解析，并组装为业务对象。解析器通过反射机制，将行数据转化为业务对象。
使用者只要实现DataHandler，就可以处理转化好的业务数据，以及解析和组装数据出错时的错误信息。
解析器提供三方面功能：
1.解析和组织数据
2.获取前面一定行数的样例数据
3.测试解析组织，验证解析和组装时否有存在错误，并可以获得错误信息
源代码
import org.apache.poi.ooxml.util.SAXHelper;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.binary.XSSFBSheetHandler.SheetContentsHandler;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;
import com.winshare.util.BeanUtils;
import com.winshare.util.DateFormatTool;
/**
* 本解析器实现对excel2007版本的解析，本类采用回调模式，逐个Sheet逐行处理数据，转化为业务对象，
* 本类通过反射机制，实现对业务对象属性值设定，通用化数据处理，方便开发。
*
* @author tbr
*
* @param <T>
*/
public class ExcelParser<T> implements SheetContentsHandler{
private File excelFile;
private DataHandler<T> dataHandler;
private int rowNum;
private int colNum;
transient private Map<Integer,String> sheetRow=new HashMap<>();
private Class<T> clazz;
private T data;
private Map<Integer,String> fieldMap;
//开始解析的行号，sheet行号从0开始编号。
private int beginRowNum;
transient private boolean isTestParse=false;
//样例数据区
transient private boolean sampleParse=false;
transient private int sampleRowNum=0;
transient private List<Map<Integer,String>> sampleRows=new ArrayList<>() ;
//记录处理过程中的错误信息，多余100个，记录前面100个错误信息
transient private List<Throwable> errors=new ArrayList<>();
public ExcelParser(String excelFile,Class<T> clazz,Map<Integer,String> fieldMap, DataHandler<T> dataHandler){
this(new File(excelFile),clazz,fieldMap,0,dataHandler);
}
/**
* 构造解析器，后续调用解析方法，进行数据解析。
* @param excelFile excel文件名
* @param clazz 业务对象类名，即将数据行解析为业务对象
* @param fieldMap excel列对应的业务对象属性列表
* @param beginRowNum 开始解析的sheet行号，sheet行从0开始编号
* @param dataHandler 解析为业务对象后，业务对象的处理器
*/
public ExcelParser(String excelFile,Class<T> clazz,Map<Integer,String> fieldMap,int beginRowNum, DataHandler<T> dataHandler){
this(new File(excelFile),clazz,fieldMap,beginRowNum,dataHandler);
}
public ExcelParser(File excelFile,Class<T> clazz,Map<Integer,String> fieldMap, DataHandler<T> dataHandler){
this(excelFile,clazz,fieldMap,0,dataHandler);
}
public ExcelParser(File excelFile,Class<T> clazz,Map<Integer,String> fieldMap,int beginRowNum, DataHandler<T> dataHandler){
this.excelFile=excelFile;
this.clazz=clazz;
this.fieldMap=fieldMap;
this.beginRowNum = beginRowNum;
this.dataHandler=dataHandler;
}
/**
* 获取指定sheet的取样例数据
*
* @param sheetNum sheet号，编号从1开始。
* @param sampleRowNum 获取的样例行数
* @return
* @throws Exception
*/
public List<Map<Integer,String>> parseSampleRow(int sheetNum,int sampleRowNum) throws Exception{
sampleParse=true;
this.sampleRowNum=sampleRowNum;
if ( ! excelFile.exists() )
return null;
sampleRows.clear();
errors.clear();
OPCPackage pkg = null;
ReadOnlySharedStringsTable sharedStringsTable = null;
InputStream sheet = null;
try{
pkg= OPCPackage.open(excelFile,PackageAccess.READ);
sharedStringsTable = new ReadOnlySharedStringsTable(pkg);
// 创建表阅读器
XSSFReader xssfReader = new XSSFReader(pkg);
StylesTable styles = xssfReader.getStylesTable();
sheet=xssfReader.getSheet("rId"+sheetNum);
parseSheet(styles,sharedStringsTable,sheet);
}finally{
if (pkg!=null)
pkg.close();
if (sheet!=null)
sheet.close();
}
return sampleRows;
}
/**
* 测试指定sheet的数据，逐行解析，转为业务数据对象，验证数据格式是否正确。
*
* @param sheetNum sheet号，编号从1开始。
* @param sampleRowNum
* @return
* @throws Exception
*/
public void testParse(int sheetNum) throws Exception{
isTestParse=true;
doParse( sheetNum);
}
/**
* 解析指定sheet的数据，逐行解析，转为业务数据对象，并逐个业务对象调用处理器处理业务数据。
*
* @param sheetNum sheet号，编号从1开始。
* @param sampleRowNum
* @return
* @throws Exception
*/
public void parse(int sheetNum) throws Exception{
isTestParse=false;
doParse( sheetNum);
}
public void doParse(int sheetNum) throws Exception{
if ( ! excelFile.exists() )
return ;
sampleParse=false;
errors.clear();
OPCPackage pkg = null;
ReadOnlySharedStringsTable sharedStringsTable = null;
InputStream sheet = null;
try{
pkg= OPCPackage.open(excelFile,PackageAccess.READ);
sharedStringsTable = new ReadOnlySharedStringsTable(pkg);
// 创建表阅读器
XSSFReader xssfReader = new XSSFReader(pkg);
StylesTable styles = xssfReader.getStylesTable();
sheet=xssfReader.getSheet("rId"+sheetNum);
parseSheet(styles,sharedStringsTable,sheet);
}finally{
if (pkg!=null)
pkg.close();
if (sheet!=null)
sheet.close();
}
}
/**
* 逐个sheet的测试，逐行解析，转为业务数据对象，验证数据格式是否正确。
*
* @param sheetNum sheet号，编号从1开始。
* @param sampleRowNum
* @return
* @throws Exception
*/
public void testParse() throws Exception{
isTestParse=true;
doParse();
}
/**
* 逐个sheet、逐行解析，转为业务数据对象，并逐个业务对象调用处理器处理业务数据。
*
* @throws Exception
*/
public void parse() throws Exception{
isTestParse=false;
doParse();
}
public void doParse() throws Exception{
if ( ! excelFile.exists() )
return ;
sampleParse=false;
errors.clear();
OPCPackage pkg = null;
ReadOnlySharedStringsTable sharedStringsTable = null;
InputStream sheet = null;
try{
pkg= OPCPackage.open(excelFile,PackageAccess.READ);
sharedStringsTable = new ReadOnlySharedStringsTable(pkg);
// 创建表阅读器
XSSFReader xssfReader = new XSSFReader(pkg);
StylesTable styles = xssfReader.getStylesTable();
XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
//逐个处理sheet，
while(sheets.hasNext()){
sheet=sheets.next();
parseSheet(styles,sharedStringsTable,sheet);
sheet.close();
}
}finally{
if (pkg!=null)
pkg.close();
if (sheet!=null)
sheet.close();
}
}
private void parseSheet(StylesTable styles,ReadOnlySharedStringsTable sharedStringsTable,
InputStream sheetInputStream) {
XMLReader sheetParser;
try {
sheetParser = SAXHelper.newXMLReader();
} catch (Exception e) {
return ;
}
DataFormatter formatter = new DataFormatter();
InputSource sheetSource = new InputSource(sheetInputStream);
ContentHandler handler = new XSSFSheetXMLHandler(styles, null, sharedStringsTable,
this, formatter, false);
sheetParser.setContentHandler(handler);
try {
sheetParser.parse(sheetSource);
} catch (Exception e) {
dataHandler.handleException(e);
if (errors.size()<100)
errors.add(e);
}
}
/**
* 获取解析错误信息
*
* @return
*/
public List<Throwable> getErrors(){
List<Throwable> retErrors=new ArrayList<>();
retErrors.addAll(errors);
return retErrors;
}
/**
* 行解析开始
*/
@Override
public void startRow(int rowNum) {
if ( rowNum < beginRowNum ){
return;
}
this.rowNum=rowNum;
this.colNum= -1 ;
}
/**
* 行解析完成
*/
@Override
public void endRow(int rowNum) {
if ( rowNum < beginRowNum ){
return;
}
if (sampleParse){
//读取样例行，不进行业务对象转化
if ( sampleRows.size() < sampleRowNum ){
Map<Integer,String> row=new HashMap<>();
row.putAll(sheetRow);
sampleRows.add(row);
}
return;
}
createEntity();
if ( ! isTestParse)
dataHandler.handleData(data);
}
private void createEntity(){
try {
data=clazz.newInstance();
} catch (Exception e) {
dataHandler.handleException(e);
if (errors.size()<100)
errors.add(e);
}
fieldMap.forEach((colIdx,fieldName)->{
try {
setDataValue(colIdx,fieldName);
} catch (Exception e) {
e.printStackTrace();
if (errors.size()<100)
errors.add(e);
}
});
}
private void setDataValue(int colIdx,String fieldName) throws Exception{
String formattedValue=sheetRow.get(colIdx);
String[] propNames=fieldName.split("\\.");
Object currObj=data,field;
String propName;
Class<?> propClz;
for(int i=0;i<propNames.length-1;i++){
propName=propNames[i];
field=BeanUtils.forceGetProperty(currObj, propName);
if (field==null){
propClz=BeanUtils.getDeclaredField(currObj, propName).getDeclaringClass();
field=propClz.newInstance();
}
currObj=field;
}
propName=propNames[propNames.length-1];
propClz=BeanUtils.getDeclaredField(currObj, propName).getDeclaringClass();
Object fieldValue=castType(propClz, formattedValue);
BeanUtils.forceSetProperty(currObj, propName, fieldValue);
}
private Object castType(Class<?> type,String formattedValue) {
if (type==null){
return formattedValue;
}
if (type == String.class){
return formattedValue;
}
if (type == Boolean.class || type == boolean.class){
return Boolean.valueOf(formattedValue);
}
if (type == java.sql.Date.class){
return new java.sql.Date(DateFormatTool.parseDate(formattedValue).getTime());
}
if (type == java.util.Date.class){
return DateFormatTool.parseDate(formattedValue);
}
if (type == Timestamp.class){
return DateFormatTool.parseTimestamp(formattedValue);
}
if (type == Integer.class || type == int.class){
return Integer.valueOf(formattedValue);
}
if (type == Long.class || type == long.class){
return Long.valueOf(formattedValue);
}
if (type == Float.class || type == float.class){
return Float.valueOf(formattedValue);
}
if (type == Double.class || type == double.class){
return Double.valueOf(formattedValue);
}
return formattedValue;
}
@Override
public void cell(String cellReference, String formattedValue,XSSFComment comment) {
if ( rowNum < beginRowNum ){
return;
}
if (cellReference == null) {
cellReference = new CellAddress(rowNum, colNum).formatAsString();
}
int thisCol = (new CellReference(cellReference)).getCol();
//处理数据中间存在空白
for (int i = colNum+1; i < thisCol-1; i++) {
sheetRow.put(i,"");
}
sheetRow.put(thisCol, formattedValue);
colNum = thisCol;
}
@Override
public void hyperlinkCell(String arg0, String arg1, String arg2, String arg3, XSSFComment arg4) {
// TODO Auto-generated method stub
}
/**
* 业务对象数据处理接口
*
* @author tbr
*
* @param <T>
*/
public interface DataHandler<T> {
/**
* 业务数据处理方法，
* @param data
*/
void handleData(T data);
/**
* excel解析抛出异常时的处理方法
*
* @param exception
*/
void handleException(Exception exception);
}
}
三、使用例子
String filename="..../测试模板文件.xlsx";//用于测试的xlsx文件名
Map<Integer,String> fieldMap=new HashMap<>();
String[] fields={"appId","name","appType","authType","signKey","ipList","describe"};
for(int i=0;i<fields.length;i++){
fieldMap.put(i, fields[i]);
}
ExcelParser.DataHandler<XXXXBizEntity> dataHandler=new ExcelParser.DataHandler<XXXXBizEntity>(){
public List<XXXXBizEntity> appinfos=new ArrayList<>();
public void handleData(XXXXBizEntity data){
appinfos.add(data);
}
public void handleException(Exception exception){
exception.printStackTrace();
}
public String toString(){
return appinfos.toString();
}
};
ExcelParser<XXXXBizEntity> ep=new ExcelParser<XXXXBizEntity>(filename, XXXXBizEntity.class, fieldMap, 1 , dataHandler);
ep.parse();
四、写在后面
1.需要开发者自己实现DataHandler，比如调用数据持久化到数据库的代码。实现数据集解析出来，调用DataHandler,将数据存入数据库中
2.可以修改这个解析类，使得支持一个文件多个sheet解析不同业务数据。
3.要支持超大数据量，处理的思路基本相同。采用边解析边处理的方法，以减少内存和CPU的消耗。
上一篇： java 导出Excel 大数据量（一）
下一篇： java实现大数据量Excel导出