采用POI实现大数据量EXCEL2007的数据解析和导入

针对业务数据导入，网上有很多方法。大多数对超大数据量的处理支持得不是太好。主要有两个大的方面存在问题：

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的消耗。