针对业务数据导入，网上有很多方法。大多数对超大数据量的处理支持得不是太好。主要有两个大的方面存在问题：
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=false; 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;}/*** 行解析开始*/@Overridepublic void startRow(int rowNum) {if ( rowNum < beginRowNum ){return;}this.rowNum=rowNum;this.colNum= -1 ;}/*** 行解析完成*/@Overridepublic 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 (NoSuchFieldException e) {e.printStackTrace();if (errors.size()<100)errors.add(e);}});}private void setDataValue(int colIdx,String fieldName) throws NoSuchFieldException{String formattedValue=sheetRow.get(colIdx);String[] propNames=fieldName.split("\\.");Object currObj=data,field;String propName;for(int i=0;i<propNames.length-1;i++){propName=propNames[i];field=BeanUtils.forceGetProperty(currObj, propName);if (field==null){field=BeanUtils.getDeclaredField(currObj, propName);}currObj=field;}propName=propNames[propNames.length-1];Class<?> 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;}@Overridepublic 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;}@Overridepublic 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的消耗。