EasyExcel实现Excel文件导入导出

1、EasyExcel简介

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。

github地址: https://github.com/alibaba/easyexcel

官方文档: https://easyexcel.opensource.alibaba.com/docs/current/

2、EasyExcel使用

2.1 EasyExcel相关依赖

添加maven依赖, 依赖的poi最低版本3.17

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.2</version>
</dependency>

2.2 下载

2.2.1Controller层

    /**
     * 文件下载  方法一
     *
     * @since 2.1.1
     */
    @GetMapping("/download")
    public void download(HttpServletResponse response) throws IOException {
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), Employee.class).sheet("模板").doWrite(employeeService.getData());
    }

    /**
     * 文件下载 方法二
     * 失败的时候返回json（默认失败了会返回一个有部分数据的Excel）
     *
     * @since 2.1.1
     */
    @GetMapping("/downloadFailedUsingJson")
    public void downloadFailedUsingJson(HttpServletResponse response) throws IOException {
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            // 这里需要设置不关闭流
            EasyExcel.write(response.getOutputStream(), Employee.class).autoCloseStream(Boolean.FALSE).sheet("模板")
                    .doWrite(employeeService.getData());
        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = MapUtils.newHashMap();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }
    }

2.2.2 ServiceImpl层

    @Value("${uploadFiles.filePath}")
    private String filePath;

    @Value("${uploadFiles.batchSize}")
    private int batchSize;

    @Value("${uploadFiles.threadNum}")
    private int threadNum;
    
    
    @Override
    public List<Employee> getData() {
        int count = employeeMapper.selectEmployeeCount();
        int pages = count / batchSize;
        if (count % batchSize != 0) {
            pages++;
        }
        ExecutorService executorService = Executors.newFixedThreadPool(threadNum);
        List<Employee> employeeLists = new ArrayList<>();
        for (int i = 0; i < pages; i++) {
            int index = i;
            executorService.execute(new Runnable() {
                @Override
                public void run() {
                    List<Employee> employeeData = getEmployeeData(index + 1, batchSize);
                    employeeLists.addAll(employeeData);
                }
            });
        }
        executorService.shutdown();
        while (true) {
            if (executorService.isTerminated()) {
                log.info("成功下载{}条数据！", count);
                break;
            }
        }
        return employeeLists;

    }

    private List<Employee> getEmployeeData(int currentPage, int pageSize) {
        List<Employee> employeeList = employeeMapper.selectAllEmployee((currentPage - 1) * pageSize, pageSize);
        return employeeList;
    }

2.3 上传

2.2.1 Controller层

    /**
     * 文件上传
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link Employee}
     * <p>
     * 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link EasyExcelListenerUtils}
     * <p>
     * 3. 直接读即可
     */
    @PostMapping("/upload")
    public String upload(MultipartFile file) throws IOException {
        // doReadAll()读取全部sheet
        EasyExcel.read(file.getInputStream(), Employee.class, EasyExcelListenerUtils.getReadListener(batchSize)).doReadAll();
        // 获取Excel的全部数据
        List tempLists = EasyExcelListenerUtils.getTempLists();
        // 批量多线程保存
        int count = employeeService.batchSaveData(tempLists);
        // 清空工具类向外提供数据的集合
        EasyExcelListenerUtils.setTempLists(new ArrayList<>());
        return "一共上传" + count + "条数据！";
    }

2.2.2 ServiceImpl层

    @Value("${uploadFiles.filePath}")
    private String filePath;

    @Value("${uploadFiles.batchSize}")
    private int batchSize;

    @Value("${uploadFiles.threadNum}")
    private int threadNum;


    @Override
    public int batchSaveData(List batchDataList) {
        ExecutorService executorService = Executors.newFixedThreadPool(threadNum);
        int count = batchDataList.size();
        // 分批处理列表
        for (int i = 0; i < batchDataList.size(); i += batchSize) {
            int start = i;
            int end = Math.min(i + batchSize, batchDataList.size());
            List<Employee> batchList = batchDataList.subList(start, end);

            // 提交任务到线程池
            executorService.execute(new Runnable() {
                @Override
                public void run() {
                    employeeMapper.save(batchList);
                    log.info("成功上传{}条数据！", batchList.size());
                }
            });
        }
        executorService.shutdown();
        while (true) {
            if (executorService.isTerminated()) {
                log.info("一共上传{}条数据！", count);
                break;
            }
        }
        return count;
    }

2.2.3 EasyExcelListenerUtils监听器工具类

public class EasyExcelListenerUtils<T> {
    private static List tempLists = new ArrayList<>();

    public static List getTempLists() {
        return tempLists;
    }

    public static void setTempLists(List tempLists) {
        EasyExcelListenerUtils.tempLists = tempLists;
    }

    public static <T> AnalysisEventListener<T> getReadListener(int batchCount) {
        return new AnalysisEventListener<T>() {
            /**
             * 存储解析的数据 T t
             */
            // ArrayList基于数组实现, 查询更快
            // List<T> tempDataList = new ArrayList<>(batchCount);
            // LinkedList基于双向链表实现, 插入和删除更快
            List<T> tempDataList = new LinkedList<>();

            /**
             * 每解析一行数据事件调度中心都会通知到这个方法, 订阅者1
             * @param data 解析的每行数据
             * @param context
             */
            @Override
            public void invoke(T data, AnalysisContext context) {
                tempDataList.add(data);
                // 达到阈值就处理一次存储的数据
                if (tempDataList.size() >= batchCount) {
                    tempLists.addAll(tempDataList);
                    tempDataList = new LinkedList<>();
                }
            }

            /**
             * excel文件解析完成后,事件调度中心会通知到该方法, 订阅者2
             * @param context
             */
            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                // 最后阈值外的数据做处理
                if (tempDataList.size() > 0) {
                    tempLists.addAll(tempDataList);
                    tempDataList = new LinkedList<>();
                }
            }
        };
    }
}