当前位置： 首页 > article >正文

Easyexcel（2-文件读取）

article 2024/11/24 2:40:54

相关文章链接：

  1. Easyexcel（1-注解使用）
  2. Easyexcel（2-文件读取）

同步读取

读取单个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的批量插入方法较快（疑问：在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("存储数据库成功！");
    }
}

http://www.kler.cn/a/406578.html

相关文章：

  • HarmonyOS . 沉浸状态栏使用
  • 【强化学习的数学原理】第02课-贝尔曼公式-笔记
  • Vue 3 自定义插件开发
  • fastadmin实现站内通知功能
  • 呼叫中心怎么对接大模型？
  • 6. Spring Cloud Gateway网关超详细内容配置解析说明
  • Elasticsearch 中的热点以及如何使用 AutoOps 解决它们
  • 【Lambda基础】Python Lambda 函数的 9 种玩法
  • 【1.2 Getting Started---＞Installation Guide】
  • Cmakelist.txt之Linux-redis配置
  • Java、Android引用类型
  • rust中解决DPI-1047: Cannot locate a 64-bit Oracle Client library问题
  • C++ String
  • 【嵌入式】手把手教你入门STM32的GPIO：使用GPIO输入
  • SpringBoot入门准备——Maven的使用
  • Web3与智能合约：区块链技术下的数字信任体系
  • Python世界：力扣题解875，珂珂爱吃香蕉，中等
  • Python操作neo4j库py2neo使用之py2neo 删除及事务相关操作（三）
  • uniapp跨域问题解决方案
  • 博客文章怎么设计分类与标签
  • Java SE 与 Java EE：基础与进阶的探索之旅
  • PAL（Program-Aided Language Model）
  • android 使用MediaPlayer实现音乐播放--获取音乐数据
  • Spring Boot 3.0废弃了JavaEE，改用了Jakarta EE
  • Linux云平台Oracle 12c安装与数据迁移
  • 大数据-231 离线数仓 - DWS 层、ADS 层的创建 Hive 执行脚本