EasyExcel-Multi-Sheet-Entity-Data-Read

• Easyexcel（1-注解介绍与使用）
• Easyexcel（2-文件多Sheet、不同对象、多数据读取）
• Easyexcel（3-文件导出）
• Easyexcel（4-模板文件定义与导出）
• Easyexcel（5-自定义列宽）
• Easyexcel（6-单元格合并）
• Easyexcel（7-自定义样式）
• Easyexcel（8-通用工具类）

同步读取

读取单个Sheet

1. 通过sheet方法指定对应的Sheet名称或下标读取文件信息
2. 通过 doReadSync 方法实现同步读取

@Data
public class UserExcel {

    @ExcelIgnore
    private Integer id;

    @ExcelProperty(index = 0, value = "姓名")
    private String name;

    @ExcelProperty(index = 1, value = "年龄")
    private Integer age;

    @DateTimeFormat(value = "yyyy-MM-dd")
    @ExcelProperty(index = 2, value = "出生日期")
    private Date birthday;
}

@RestController
public class Test02Controller {

    /**
     * 上传单个文件, 同步读取excel文件
     */
    @PostMapping("/uploadFile")
    public void uploadFile(MultipartFile file) {
        try (InputStream in = file.getInputStream()) {
            List<UserExcel> userExcelList = EasyExcel.read(in)
                    // 读取第一个sheet
                    .sheet(0)
                    // 如果第一行才是标题，第二行是数据，从第二行开始读取
                    .headRowNumber(1)
                    .head(UserExcel.class)
                    .doReadSync();
            for (UserExcel userExcel : userExcelList) {
                System.out.println(userExcel);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

读取多个Sheet（同一个对象）

使用 doReadAllSync 方法读取所有Sheet，适用于每个Sheet的对象都一致的情况

@PostMapping("/uploadFile2")
public void uploadFile2(MultipartFile file) {
    try (InputStream in = file.getInputStream()) {
        List<UserExcel> userExcelList = EasyExcel.read(in)
                // 如果第一行才是标题，第二行是数据，从第二行开始读取
                .headRowNumber(1)
                .head(UserExcel.class)
                .doReadAllSync();
        for (UserExcel userExcel : userExcelList) {
            System.out.println(userExcel);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

读取多个Sheet（不同对象）

当每个Sheet的对象不一致的情况下，使用 doReadAllSync 方法无法指定每个Sheet的对象，可以依次读取Sheet进行解析

注意：依次读取Sheet会出现重复读取流对象的情况，而一个流对象只能读取一次，重复使用会导致异常

@PostMapping("/uploadFile4")
public void uploadFile4(MultipartFile file) {
    InputStream in = null;
    try {
        in = file.getInputStream();
        List<UserExcel> userExcelList1 = EasyExcel.read(in)
                // 读取第一个sheet
                .sheet(0)
                // 如果第一行才是标题，第二行是数据，从第二行开始读取
                .headRowNumber(1)
                .head(UserExcel.class)
                .doReadSync();

        // 读取剩余的sheet
        in = file.getInputStream();
        List<UserExcel> userExcelList2 = EasyExcel.read(in)
                .sheet(1)
                // 如果第一行才是标题，第二行是数据，从第二行开始读取
                .headRowNumber(1)
                .head(UserExcel.class)
                .doReadSync();

        List<UserExcel> userExcelList = new ArrayList<>();
        userExcelList.addAll(userExcelList1);
        userExcelList.addAll(userExcelList2);
        for (UserExcel userExcel : userExcelList) {
            System.out.println(userExcel);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            if (in != null) {
                in.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

异步读取

监听器

查看监听器源码，通过实现 ReadListener 接口或继承 AnalysisEventListener 类可以自定义读取Sheet监听器

public interface ReadListener<T> extends Listener {
    
    // 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则继续读取下一行
    default void onException(Exception exception, AnalysisContext context) throws Exception {
        throw exception;
    }

    // 获取表头数据
    default void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {}

    // 一行行读取表格内容
    void invoke(T data, AnalysisContext context);

    // 读取条额外信息:批注、超链接、合并单元格信息等
    default void extra(CellExtra extra, AnalysisContext context) {}

   	// 读取完成后的操作
    void doAfterAllAnalysed(AnalysisContext context);

    // 是否还有数据
    default boolean hasNext(AnalysisContext context) {
        return true;
    }
}

public abstract class AnalysisEventListener<T> implements ReadListener<T> {

    // 解析表头数据
    @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
        invokeHeadMap(ConverterUtils.convertToStringMap(headMap, context), context);
    }

    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {}
}

异常处理

ExcelDateConvertException

表示数据转换异常错误，出现该异常时会继续解析文件信息

@Getter
@Setter
@EqualsAndHashCode
public class ExcelDataConvertException extends ExcelRuntimeException {

    private Integer rowIndex;

    private Integer columnIndex;

    private CellData<?> cellData;

    private ExcelContentProperty excelContentProperty;

    public ExcelDataConvertException(Integer rowIndex, Integer columnIndex, CellData<?> cellData,
        ExcelContentProperty excelContentProperty, String message) {
        super(message);
        this.rowIndex = rowIndex;
        this.columnIndex = columnIndex;
        this.cellData = cellData;
        this.excelContentProperty = excelContentProperty;
    }

    public ExcelDataConvertException(Integer rowIndex, Integer columnIndex, CellData<?> cellData,
        ExcelContentProperty excelContentProperty, String message, Throwable cause) {
        super(message, cause);
        this.rowIndex = rowIndex;
        this.columnIndex = columnIndex;
        this.cellData = cellData;
        this.excelContentProperty = excelContentProperty;
    }
}

ExcelAnalysisStopException

非数据转换异常错误，在onexcetpion中抛出该异常后停止解析

public class ExcelAnalysisStopException extends ExcelAnalysisException {

    public ExcelAnalysisStopException() {}

    public ExcelAnalysisStopException(String message) {
        super(message);
    }

    public ExcelAnalysisStopException(String message, Throwable cause) {
        super(message, cause);
    }

    public ExcelAnalysisStopException(Throwable cause) {
        super(cause);
    }
}

读取单个Sheet（不指定对象）

读取文件时使用doRead方法进行异步操作，同时指定对应的监听器解析文件数据

Map<Integer, String>中的key表示列号、value表示数据

public class UserExcelListener1 extends AnalysisEventListener<Map<Integer, String>> {
    Logger log = LoggerFactory.getLogger(getClass());

    private List<Map<Integer, String>> userExcelList = new ArrayList<>();

    @Override
    public void invoke(Map<Integer, String> map, AnalysisContext analysisContext) {
        log.info("解析到一条数据:{}", JSON.toJSONString(map));
        userExcelList.add(map);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("已解析完所有数据!");
        userExcelList.clear();
    }

    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException convertException = (ExcelDataConvertException) exception;
            Integer row = convertException.getRowIndex();
            log.error("第{}行数据转换失败，异常信息：{}", row, exception.getMessage());
        } else {
            log.error("导入其他异常信息：{}", exception.getMessage());
        }
    }

    public List<Map<Integer, String>> getUserExcelList() {
        return userExcelList;
    }

    public void setUserExcelList(List<Map<Integer, String>> userExcelList) {
        this.userExcelList = userExcelList;
    }
}

@PostMapping("/uploadFile1")
public void uploadFile1(MultipartFile file) {
    try (InputStream in = file.getInputStream()) {
        UserExcelListener1 listener = new UserExcelListener1();
        EasyExcel.read(in, listener)
                .sheet(0)
                .headRowNumber(1) // 第一行是标题, 从第二行开始读取
                .doRead();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

读取单个Sheet（指定对象）

public class UserExcelListener extends AnalysisEventListener<UserExcel> {
    Logger log = LoggerFactory.getLogger(getClass());

    private List<UserExcel> userExcelList = new ArrayList<>();

    @Override
    public void invoke(UserExcel userExcel, AnalysisContext analysisContext) {
        log.info("解析到一条数据:{}", JSON.toJSONString(userExcel));
        userExcelList.add(userExcel);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("已解析完所有数据!");
        userExcelList.clear();
    }

    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException convertException = (ExcelDataConvertException) exception;
            Integer row = convertException.getRowIndex();
            log.error("第{}行数据转换失败，异常信息：{}", row, exception.getMessage());
        } else {
            log.error("导入其他异常信息：{}", exception.getMessage());
        }
    }

    public List<UserExcel> getUserExcelList() {
        return userExcelList;
    }

    public void setUserExcelList(List<UserExcel> userExcelList) {
        this.userExcelList = userExcelList;
    }
}

@PostMapping("/uploadFile5")
public void uploadFile5(MultipartFile file) {
    try (InputStream in = file.getInputStream()) {
        UserExcelListener listener = new UserExcelListener();
        EasyExcel.read(in, UserExcel.class, listener)
                .sheet(0)
                .headRowNumber(1) // 第一行是标题, 从第二行开始读取
                .doRead();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

读取多个Sheet

1. 获取Sheet的总数，通过循环遍历的方式指定每个Sheet的监听器进行解析
2. 使用构造器的方式传入Sheet对应的下标，在抛出异常时获取SheetNo和对应的行号，方便进行排查

public class UserExcelListener2 extends AnalysisEventListener<UserExcel> {
    
    Logger log = LoggerFactory.getLogger(getClass());

    private Integer sheetNo;
    
    private List<UserExcel> userExcelList = new ArrayList<>();

    public UserExcelListener2(Integer sheetNo) {
        this.sheetNo = sheetNo;
    }

    @Override
    public void invoke(UserExcel userExcel, AnalysisContext analysisContext) {
        log.info("解析到一条数据:{}", JSON.toJSONString(userExcel));
        userExcelList.add(userExcel);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("已解析完所有数据!");
        userExcelList.clear();
    }

    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException convertException = (ExcelDataConvertException) exception;
            Integer row = convertException.getRowIndex();
            log.error("sheetNo：{}，第{}行数据转换失败，异常信息：{}", sheetNo, row, exception.getMessage());
        } else {
            log.error("导入其他异常信息：{}", exception.getMessage());
        }
    }

    public List<UserExcel> getUserExcelList() {
        return userExcelList;
    }

    public void setUserExcelList(List<UserExcel> userExcelList) {
        this.userExcelList = userExcelList;
    }
}

@PostMapping("/uploadFile6")
public void uploadFile6(MultipartFile file) {
    try (InputStream in = file.getInputStream();
         ExcelReader build = EasyExcel.read(in).build();) {
        List<ReadSheet> readSheets = build.excelExecutor().sheetList();
        for (int i = 0, len = readSheets.size(); i < len; i++) {
            UserExcelListener2 listener = new UserExcelListener2(i);
            ReadSheet sheet = EasyExcel.readSheet(readSheets.get(i).getSheetNo())
                    .head(UserExcel.class)
                    .headRowNumber(1)
                    .registerReadListener(listener)
                    .build();
            build.read(sheet);
        }
        build.finish();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

分批读取（线程池操作）

1. 使用构造器的方式传入Sheet对应的下标和自定义线程池，使用这种分批处理的方式，避免内存的消耗，加快文件的解析入库
2. 数据库入库时可以使用MySQL的批量插入语法，同时指定每次插入数据的大小，相较于MyBatisPlus的批量插入方法较快

/**
 * UserListener 不能被spring管理，要每次读取excel都要new，
 * 然后里面用到spring可以构造方法传进去
 */
public class UserExcelListener3 extends AnalysisEventListener<UserExcel> {

    Logger log = LoggerFactory.getLogger(getClass());

    private static final Integer BATCH_SIZE = 1000;

    private Integer sheetNo;

    private Executor executor;

    private List<UserExcel> userExcelList = new ArrayList<>();

    public UserExcelListener3(Integer sheetNo, Executor executor) {
        this.sheetNo = sheetNo;
        this.executor = executor;
    }

    @Override
    public void invoke(UserExcel userExcel, AnalysisContext analysisContext) {
        log.info("解析到一条数据:{}", JSON.toJSONString(userExcel));
        userExcelList.add(userExcel);
        if (userExcelList.size() >= BATCH_SIZE) {
            List<UserExcel> userExcels = BeanUtil.copyToList(userExcelList, UserExcel.class);
            CompletableFuture.runAsync(() -> {
                // 业务操作
                // saveToDB(userExcels);
            }, executor);
            userExcelList.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("已解析完所有数据!");
        if (!userExcelList.isEmpty()) {
            List<UserExcel> userExcels = BeanUtil.copyToList(userExcelList, UserExcel.class);
            CompletableFuture.runAsync(() -> {
                // 业务操作
                // saveToDB(userExcels);
            }, executor);
            userExcelList.clear();
        }
    }

    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException convertException = (ExcelDataConvertException) exception;
            Integer row = convertException.getRowIndex();
            log.error("sheetNo：{}，第{}行数据转换失败，异常信息：{}", sheetNo, row, exception.getMessage());
        } else {
            log.error("导入其他异常信息：{}", exception.getMessage());
        }
    }
}

@PostMapping("/uploadFile7")
public void uploadFile77(MultipartFile file) {
    try (InputStream in = file.getInputStream();
         ExcelReader build = EasyExcel.read(in).build();) {

        ThreadPoolExecutor executor = new ThreadPoolExecutor(10, 20, 60L,
                TimeUnit.MILLISECONDS, new ArrayBlockingQueue<>(1000), new ThreadPoolExecutor.AbortPolicy());
        List<ReadSheet> readSheets = build.excelExecutor().sheetList();
        for (int i = 0, len = readSheets.size(); i < len; i++) {
            UserExcelListener3 listener = new UserExcelListener3(i, executor);
            ReadSheet sheet = EasyExcel.readSheet(readSheets.get(i).getSheetNo())
                    .head(UserExcel.class)
                    .headRowNumber(1)
                    .registerReadListener(listener)
                    .build();
            build.read(sheet);
        }
        build.finish();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

事务操作

当使用监听器读取文件数据，使用分批插入数据的方法时，因为监听器不归Spring管理，所以无法使用Spring的事务注解进行事务的相关操作，怎么保证事务？

可以通过构造器的方式传入事务管理器，手动提交和回滚事务

@Slf4j
public class TestDataListener extends AnalysisEventListener<Test> {
    
    //每隔5条存储数据库，实际使用中可以设置为2500条，然后清理list ，方便内存回收
    private static final int BATCH_COUNT = 5;
    private List<Test> list = new ArrayList<>();

    //事务管理
    private DataSourceTransactionManager dataSourceTransactionManager;
    //事务定义
    private DefaultTransactionDefinition transactionDefinition;
    private TransactionStatus transactionStatus = null;

    private TestService testService;

    public TestDataListener(TestService testService,
                            DataSourceTransactionManager dataSourceTransactionManager,
                            TransactionDefinition transactionDefinition) {
        this.testService = testService;
        this.dataSourceTransactionManager = dataSourceTransactionManager;
        this.transactionDefinition = new DefaultTransactionDefinition(transactionDefinition);
        //设置事务的隔离级别 ：未提交读写
        this.transactionDefinition.setIsolationLevel(TransactionDefinition.ISOLATION_READ_UNCOMMITTED);
        // 手动开启事务
        this.transactionStatus = dataSourceTransactionManager.getTransaction(transactionDefinition);
    }

    @Override
    public void invoke(Test data, AnalysisContext context) {
        log.info("解析到一条数据:{}", JSON.toJSONString(data));
        boolean hasCompleted = transactionStatus.isCompleted();
        // 如果事务已经关闭，不执行业务代码
        if (hasCompleted){
            return;
        }
        list.add(data);
        if (list.size() >= BATCH_COUNT) {
            saveData();
            list.clear();
        }
    }

    // 这个方法会在easyexcel读取完文件中所有数据后执行
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        //判断事务是否已被处理，未处理则进行提交事务
        boolean hasCompleted = transactionStatus.isCompleted();
        if (hasCompleted){
            return;
        }
        saveData();
        log.info("所有数据解析完成！");
        if (!hasCompleted){
            //提交事务
            dataSourceTransactionManager.commit(transactionStatus);
            log.info("SensitiveWordListener doAfterAllAnalysed：当前事务已提交");
        }
    }

    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        log.info("导入过程中出现异常会进入该方法，重写了父类方法");
        log.info("结束前事务状态："+  transactionStatus.isCompleted());
        dataSourceTransactionManager.rollback(transactionStatus);
        log.info("结束后事务状态："+  transactionStatus.isCompleted());
        throw exception;
    }
    
    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", list.size());
        if (!CollectionUtils.isEmpty(list)) {
            testService.saveBatch(list);
            System.out.println(list);
        }
        //TODO 这里是测试事务，如有需要可以打开注释
        //int a = 1/0;
        log.info("存储数据库成功！");
    }
}