EasyExcel读取多sheet excel异常

EasyExcel读取多sheet excel异常

场景

使用Spring Boot集成EasyExcel进行导入excel多sheet，执行异常

代码

file是MultipartFile，入参，主要处理流程如下

InputStream is = file.getInputStream();
EasyExcel.read(is, SysDict.class, new SysDictReadListener(sysDictMapper, 0L))
        .sheet(0, "目录")
        .doRead();

List<SysDict> list = sysDictMapper.selectByDictClass1();
for (int i = 0; i < list.size(); i++) {
    SysDict sysDict = list.get(i);
    // sheet序号
    Integer sheetNo = i + 1;
    String sheetName = sysDict.getDictName();
    EasyExcel.read(is, SysDict.class, new SysDictReadListener(sysDictMapper, sysDict.getDictId()))
            .sheet(sheetNo, sheetName)
            .doRead();
}

问题描述

异常：com.alibaba.excel.exception.ExcelCommonException: Convert excel format exception.You can try specifying the ‘excelType’ yourself

按照描述，缺少excelType参数，补充后，代码大概如下：

EasyExcel.read(is, SysDict.class, new SysDictReadListener(sysDictMapper, 0L))
        .excelType(ExcelTypeEnum.XLSX)
        .sheet(0, "目录")
        .doRead();

异常：com.alibaba.excel.exception.ExcelAnalysisException: Can not create temporary file!

方案

按照上述补充excelType，并不能解决问题，经过检索，发现问题是多sheet会重复读取流对象，而一个流对象只能读取一次，重复使用，会导致上述异常，解决代码如下：

// 获取MultipartFile的流
is = file.getInputStream();

// 读取第一个sheet
EasyExcel.read(..);

// 读取剩余的sheet
is = file.getInputStream();
EasyExcel.read(..);