EasyExcel性能测试和使用代码示例，表头解析，行数读取，解析加密文档

easy excel的使用

众所周知，一般官方数据都是会有点水分的；

easyExcel官方说 64M内存1分钟内读取75M(46W行25列)的Excel

实际测试的时候也还好40M的excel文件，25W行，这个成绩相当不错。

基础的类

@Data
public class ExcelRow {
    @ExcelIgnore
    private int lineNo;
}

@Data
public class ExcelParseResult {

    private Integer total;

    private Set<String> columnNames;

    private String error;

    public ExcelParseResult() {
        total = 0;
        columnNames = Sets.newHashSet();
    }

    public boolean isError(){
        return !StringUtils.isEmpty(error);
    }
}

解析表头和行数

只需要解析表头，重写一下hasNext，直接返回即可，

获取行数官方有说可以用context.readSheetHolder().getApproximateTotalRowNumber()获取，但是不准确，想要准确的需要自己获取。

但是感觉在doAfterAllAnalysed 中使用context.readRowHolder().getRowIndex()好像也可以。

public class ParseColumnAndTotalListener<T extends ExcelRow> extends AnalysisEventListener<T> {

    private final ExcelParseResult excelParseResult;

    private Integer total;

    private final boolean isNeedTotalLine;

    public ParseColumnAndTotalListener(ExcelParseResult excelParseResult, boolean isNeedTotalLine) {
        this.excelParseResult = excelParseResult;
        this.isNeedTotalLine = isNeedTotalLine;
        total = 0;
    }

    @Override
    public void invoke(T t, AnalysisContext analysisContext) {
        total++;
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        super.invokeHeadMap(headMap, context);
        excelParseResult.setColumnNames(Sets.newHashSet(headMap.values()));
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        excelParseResult.setTotal(total);
    }

    @Override
    public boolean hasNext(AnalysisContext context) {
        if (!isNeedTotalLine) {
            return false;
        }

        return super.hasNext(context);
    }
}

批量读取并处理

public class ParseBatchHandleListener<T extends ExcelRow> extends AnalysisEventListener<T> {

    private static final Integer BATCH_SIZE = 200;

    private final List<T> objectBatch;

    private final ExcelParseResult excelParseResult;

    private final Consumer<List<T>> consumer;

    public ParseBatchHandleListener(ExcelParseResult excelParseResult, Consumer<List<T>> consumer) {
        Preconditions.checkNotNull(consumer);
        this.excelParseResult = excelParseResult;
        this.consumer = consumer;
        objectBatch = new ArrayList<>(BATCH_SIZE);
    }

    @Override
    public void invoke(T t, AnalysisContext analysisContext) {
        t.setLineNo(analysisContext.readRowHolder().getRowIndex());
        objectBatch.add(t);
        if (BATCH_SIZE <= objectBatch.size()) {
            handleAndClear();
        }
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        super.invokeHeadMap(headMap, context);
        excelParseResult.setColumnNames(Sets.newHashSet(headMap.values()));
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        handleAndClear();
        excelParseResult.setTotal(context.readRowHolder().getRowIndex());
    }

    private void handleAndClear() {
        if (!CollectionUtils.isEmpty(objectBatch)) {
            consumer.accept(objectBatch);
            objectBatch.clear();
        }
    }
}

使用

//使用
public static <T> ExcelParseResult readBatch(String pathName, Class<T> clazz, Consumer<List<T>> function){
  ExcelParseResult parseResult = new ExcelParseResult();
  ParseBatchHandleListener listener = new ParseBatchHandleListener(parseResult, function);
  EasyExcel.read(pathName, clazz, listener).sheet().doRead();
  return parseResult;
}

@Test
public void read() {
  String path = "/Users/justdj/Desktop/25w.xlsx";
  ExcelFileUtils.readBatch(path, ImportedTrade.class, a -> {});
}

文档加密

读取的时候直接指定密码就好了

ExcelReaderBuilder builder = EasyExcel.read(path, clazz, listener);
if (!StringUtils.isEmpty(password)) {
		builder.password(password);
}

有关加密文件的异常处理

private static void doRead(ExcelReaderBuilder builder, ExcelParseResult result) {
    try {
    	builder.sheet().doRead();
    } catch (EncryptedDocumentException e) {
    	e.printStackTrace();
    	result.setError("文件密码错误");
    } catch (OLE2NotOfficeXmlFileException e) {
    	e.printStackTrace();
    	result.setError("文件已被加密，需要密码");
    } catch (Exception e) {
    	e.printStackTrace();
    	result.setError("文件内容错误");
    }
}

easyExcel 2.*版本的坑比较多，哎，升级的话能解决一些问题，但还是有坑的，使用的时候需要注意，后面总结一下