EasyExcel导入导出功能优化技巧

前言

为解决文件导入导出出现内存占用高、处理速度慢等问题，本文旨在详细着重介绍EasyExcel基本使用方法以及功能优化分析，结合实际运用代码，在注释中描述清晰的业务逻辑，帮助读者轻松上手并高效利用该框架处理Excel文件，文章分为2个部分：

（1）EasyExcel的使用方法与熟悉读写监听器运行机制。

（2）从多线程并发角度与数据库优化出发，更深层次解析EasyExcel使用场景，为更好的匹配项目中的需求。

（3）分析EasyExcel新功能，从实际业务出发，允许开发者在读写过程中对其自定义优化。

目录

前言

一、背景说明

二、基本用法

1、引入依赖

2、读数据

1.1前端

1.2后端

3、写数据

三、优化分析

3.1优化原理

3.2优化业务层逻辑

3.3结合JDBC批量处理方式

四、EasyExcel新功能

五、结语

一、背景说明

        EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。不用考虑性能、内存的等因素，能够快速完成Excel的读、写等功能。

• 技术文档：https://easyexcel.opensource.alibaba.com/docs/current/
• B站地址：https://www.bilibili.com/video/BV1bF411D7M8
• gitee地址：https://gitee.com/easyexcel/easyexcel

二、基本用法

1、引入依赖

<dependency>

        <groupId>com.alibaba</groupId>

         <artifactId>easyexcel</artifactId>

         <version>2.1.7</version>

</dependency>

2、读数据

1.1前端

（1）文件上传按钮

<div class="layui-btn-container">
    <button type="button" class="layui-btn" id="test3">
        <i class="layui-icon"></i>上传文件
    </button>
</div>

（2）导入按钮点击事件-----基于LayUi的方式

layui.use(['upload', 'element', 'layer'], function() {
        var $ = layui.jquery
            , upload = layui.upload
            , layer = layui.layer;

        upload.render({
            elem: '#test3'
            , url: '/import'  //此处配置你自己的上传接口即可
            , accept: 'file'  //普通文件
            , done: function (res) {
                layer.msg(res.msg);
                text()  // 对导入成功的数据进行展示
            }
});

1.2后端

（1）实体类定义

@Data
public class Student{ //学生数据传输对象

  @ExcelProperty("序号")
  private Integer id;

  @ExcelProperty("编号")
  private Integer number;   //学生编号

  @ExcelProperty("姓名")
  private String name;       //姓名

  @ExcelProperty("年龄")
  private Integer age;       //年龄

  @ExcelProperty("时间")  
  private String time; //入学时间
}

@ExcelProperty注解：用于指定Excel中的列与Java实体类字段之间的映射关系。

（2）数据访问层

<insert id="save">
    INSERT INTO student(
    id,
    number,
    name,
    age,
    time
    ) VALUES
    <foreach collection="list" item="item" index="index" separator=",">
        (
        #{item.id},
        #{item.number},
        #{item.name},
        #{item.age},
        #{item.time}
        )
    </foreach>
</insert>

（3）业务层

/*
 Exception:出现运行时异常或编译时异常都进行回滚
*/
@Transactional(rollbackFor = Exception.class)  // 通过事务方式保证导入数据完整性
@Override
public void importData(InputStream stream) {//使用流的方式去解析Excel表格
    EasyExcel.read(
            stream,     // 接收的文件流文件
            Student.class,      // 映射实体类对象
            /* dao层（数据访问层）对象----因为监听器未被Spring容器管理，所以用构造方法方式向监听器传入Bean对象*/
            new ExcelStudentDTOListener(dao)
    ).sheet().doRead();   // 默认只读取第一页数据，存在多页时，后面sheet不会被读取到
  log.info("入库单导入成功");
}

（4）监听器

        基于观察者模式监听Excel文件解析过程中的数据流，实时更新AnalysisEventListener实例。EasyExcel通过一行一行的解析Excel文件内容，每当解析到一行数据时，EasyExcel就会调用观察者的invoke方法，并将解析得到的数据作为参数传递给该方法，在invoke方法中实现业务逻辑。

// EasyExcel监听器
@Slf4j
@NoArgsConstructor
public class ExcelStudentDTOListener extends AnalysisEventListener<Student> {

    //数据存储列表
    private List<Student> list = new ArrayList();


    //每记录10条数据后就进行存储。
    private static final int BATCH_CONUT = 10;

    long start = 0; //开始时间
    long end = 0;   //结束时间

    private JDBCDao dao;

    //通过构造函数接收业务层发送过来的dao依赖对象
    public ExcelStudentDTOListener(JDBCDao dao){
        this.dao = dao;
    }

    /**
     * @param data 需要解析的数据
     * @param analysisContext
     * @action 用于解析数据
     */
    @Override
    public void invoke(Student data, AnalysisContext analysisContext) {

        log.info("数据的解析:{}",data);

        start = System.currentTimeMillis();
        log.info("入库单导入开始时间:{}",start);

        
        //将数据填入到数据列表中
        list.add(data);

        //将导入的Excel表格解析完成后，将数据插入到数据库中
        if (list.size() >= BATCH_CONUT){
            //DAO调用save方法插入数据
            dao.save(list);
            list.clear();  //10条数据存完后，请列表清空，释放内存

            
            // 使用JDBC批量处理结合事务插入数据至数据库
            // processBatch(list);

        }

    }

    /**
     * @param analysisContext
     * @action 解析数据完毕后执行。
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

        //当最后的数据不足10条数据时，在这里进行收尾
        dao.save(list);
        end = System.currentTimeMillis();
        long result = end - start;


        // 使用JDBC批量处理结合事务插入数据至数据库
        // processBatch(list);

        log.info("导入所用时间:",result);
    }

}

（5）控制器层

    @PostMapping("/import")
    public Map<String, String> EsxcelImportText(@RequestParam MultipartFile file){ //MultipartFile:处理文件上传的对象

        Map<String, String> map = new HashMap<String, String>();

         // 文件判空
        if(file == null || file.isEmpty())
        {
            //文件未上传或为空
            map.put("msg","文件为空");
            return map;
        }

        try {

            InputStream inputStream = file.getInputStream();  // 获取上传文件的输入流

            Service.importData(inputStream);    // 调用业务层方法处理解析数据 

        } catch (IOException e) {
            e.printStackTrace();
        }
        map.put("msg","上传成功");
        return map;
    }

3、写数据

（1）数据访问层

<select id="selectExcel" resultType="com.excmple.easyexcel.text.Student">
  SELECT id,
    id,
    number,
    name,
    age,
    time
   FROM student
 </select>

（3）业务层

    @Override
    public List<Student> Listdata() {

        List<Student> list = dao.selectExcel();

        return list;
    }

（3）控制器层

// 导出Excel
    @GetMapping("/deriveExcel")
    public void derive(HttpServletResponse response) throws IOException {

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 防止中文乱码
        String fileName = URLEncoder.encode("mydict", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), DataTransmissionObject.class).sheet("学生信息").doWrite(Service.Listdata());
    }

三、优化分析

        Excel表格中存在多个sheet，并且每个sheet中都存大量数据时，在业务逻辑层利用多线程并发读取这些Sheet，并且在数据访问层结合JDBC批量处理数据。通过ThreadPoolTaskExecutor管理线程池，配置线程池核心参数，为每个线程分配任务，具备独立的文件流与监听器，一个线程读取一个Sheet，使用join()方法阻塞当前线程，直到所有任务都完成。

3.1优化原理

3.2优化业务层逻辑

@Slf4j
@Service
public class ImplJDBCService implements JDBCService {

     @Autowired
     private JDBCDao dao;

     private final ThreadPoolTaskExecutor taskExecutor;

    /*
    为什么使用构造函数的方式创建线程池对象：
    （1）ThreadPoolTaskExecutor是 Spring 框架提供的一个用于管理线程池的类，可以用于并发任务的执行。通过
        构造方法进行依赖注入，进而由Spring容器对线程池对象进行管理。
    （2）由Spring管理之后，线程池的重用可以减少创建新线程的开销，下次使用不需要再次创建线程池。
    （3）@Lazy作用：只有在需要使用线程池进行初始化时才会创建线程池对象，这样可以避免在引用ImplJDBCService对象时，而
      因为构造方法去创建线程池对象。
     */
    public ImplJDBCService(@Lazy ThreadPoolTaskExecutor taskExecutor) {

        // 配置线程池核心参数
        taskExecutor.setCorePoolSize(10); // 核心线程数（根据服务器CPU参数进行考量配置）
        taskExecutor.setMaxPoolSize(20); // 最大线程数
        taskExecutor.setQueueCapacity(30); // 队列容量
        taskExecutor.setThreadNamePrefix("MyThread-"); // 线程名称前缀
        taskExecutor.initialize(); // 初始化线程池
        this.taskExecutor = taskExecutor;

        /*
        我在这里直接是通过构造方法的方式去配置线程池，显得不灵活了，可以通过配置类的方式去配置线程池，
        使用 @Configuration 注解映射配置类，在配置方法中通过@Bean注解将 ThreadPoolTaskExecutor 线程
        池对象交由 Spring 容器进行管理，在需要使用时通过 @Autowired 注解实现依赖注入即可使用。
         */
    }

    /*
      Exception:出现运行时异常或编译时异常都进行回滚
     */
    @Transactional(rollbackFor = Exception.class)  // 保证导入数据完整性
    @Override
    public void importData(MultipartFile file){

        // 创建一个列表来存储多个异步任务的 CompletableFuture 对象（保存线程对象处理数据状态信息），管理和监控多个异步任务的状态
        List<CompletableFuture<Void>> futures = new ArrayList<>();
        InputStream stream = null;
        try {
            stream = file.getInputStream();

            // 假设我们知道文件中有多少个 sheet，可以确定线程数量
            int totalSheets = getSheetCount(stream); // 获取表格中sheet数量

            // 使用多线程并发处理每个 sheet
            for (int i = 0; i < totalSheets; i++) {

                final int sheetIndex = i;  // 基于Lambda表达式读取数据，要用final关键字修饰

                // 用于线程异步执行任务，由线程池去处理表格数据的读取-----给单个线程分配任务
                CompletableFuture<Void> future = CompletableFuture.runAsync(() -> {

                    /* 使用线程池并发处理多个 sheet，每个线程使用独立的流，避免线程在同步时，数据写入同一个
                       流，从而出现数据错乱（覆盖）的情况，并且可以避免出现两个线程以上为争抢同一个流而出现死锁现象。
                     */
                    try (InputStream inputStream = file.getInputStream()) {

                        // 对数据进行读取操作，随即会进入自定义监听器中监听数据读取状态
                        ExcelReaderBuilder readerBuilder = EasyExcel.read(inputStream, DataTransmissionObject.class, new ExcelStudentDTOListener(dao));
                        ExcelReaderSheetBuilder sheetBuilder = readerBuilder.sheet(sheetIndex);  // 依次处理Excel表格中的sheet
                        sheetBuilder.doRead();

                    } catch (IOException e) {
                        e.printStackTrace();
                    }

                /*
                   taskExecutor作为参数的作用：使用自定义线程池的方式去处理多线程
                   异步执行任务，若不使用自定义线程池，JVM默认的ForkJoinPool.commonPool() 来
                   执行任务，这样不利于对线程池的使用进行管理，要结合线程数、等待时间（核心参数）等等去考量。
                 */
                }, taskExecutor);

                futures.add(future); // 将线程处理任务结果保存至列表中
            }
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            // 等待所有线程任务完成。join()会阻塞当前线程，直到所有任务都完成。
            CompletableFuture.allOf(futures.toArray(new CompletableFuture[0])).join();  // 这里会根据 futures 列表保存的状态信息进行判断所有线程是否已全部完成读取。

            try {
                System.out.println("关闭线程池，文件流，所有线程完成对表格数据的读取");
                taskExecutor.shutdown();  // 关闭线程池
                stream.close(); // 确保无论如何都释放资源
            } catch (IOException e) {
                e.printStackTrace();
            }

        }

    // 获取sheet数量
    private static int getSheetCount(InputStream excelFile) {
        ExcelReaderBuilder readerBuilder = EasyExcel.read(excelFile);
        return readerBuilder.build().excelExecutor().sheetList().size();
    }
}

3.3结合JDBC批量处理方式

    // 读取的数据以方法参数形式被JDBC保存至数据库中
    public void processBatch(List<DataTransmissionObject> dataList) {

        // JDBC分批插入+事务操作完成对10w数据的插入
        Connection conn = null;
        PreparedStatement ps = null;
        try {

            long startTime = System.currentTimeMillis();
            log.info("{} 条,开始导入到数据库时间:{}", dataList.size(), startTime + "ms");
            conn = JDBC();
            // 控制事务:默认不提交
            conn.setAutoCommit(false);
            String sql = "insert into student (id,number,name,age,time) values";
            sql += "(?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            // 循环结果集:这里循环不支持"烂布袋"表达式
            for (int i = 0; i < dataList.size(); i++) {
                System.out.println("获取到的内容是："+dataList.get(i));
                ps.setInt(1, dataList.get(i).getId());
                ps.setString(2, dataList.get(i).getNumber());
                ps.setInt(3, dataList.get(i).getDifferential_ship());
                ps.setString(4, dataList.get(i).getToem());
                // 将一组参数添加到此 PreparedStatement 对象的批处理命令中。
                ps.addBatch();
            }
            // 执行批处理
            ps.executeBatch();
            // 手动提交事务
            conn.commit();
            long endTime = System.currentTimeMillis();


        } catch (Exception e) {

            e.printStackTrace();
        } finally {
            try {
                conn.close();  // 关连接
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

        以10w数据来测试，两种方式读取时间分别为：Mybatis批量处理是378s，JDBC批量处理是166s，比较 MyBatis、原生 JDBC 执行后的结果可以看到，JDBC 的效率比 MyBatis 提升了 2.2 以上。

四、EasyExcel新功能

        EasyExcel 3.0版本及以上引入了一系列新的功能和改进，比如引入监听器WriteHandler，支持在写入时自定义样式，可以使用TableStyle来自定义整张表格的样式，以及通过实现AbstractCellWriteHandler接口来自定义单元格的样式，如字体、颜色、边框等。在数据转换方面自定义数据转换、跳过空行等新功能。

五、结语

        EasyExcel 的核心优势之一是它的内存占用非常低。它通过流式读写的方式，边读取边解析，边写入边生成，避免了将整个 Excel 文件加载到内存中。上述案例中只是针对EasyExcel中的某一个点进行了优化，还可以从线程、文件流、线程分段读写等方面继续深入优化。