【性能优化】-多线程实现百万级导出/导入到excel

前言

随着我们的业务越来越负责，数据量上来之后，不仅仅只是功能上的实现了，实现简单的导入、导出即可了，数据量上来之后很可能造成OOM，
我的博文中有一篇介绍了如果实现不同负责模板的导出效果SpringBoot整合easyexcel实现Excel的导出（包括复杂的导出）
本次博文介绍的是如何实现百万级的导出、导入

概括

第一种方案-分批导出

    @OperationLog(module = ModuleEnum.VEHICLE_TEST_CAR, operationType = OperationTypeEnum.EXPORT)
    @ApiOperation(value = "车辆列表导出", notes = "searchText,orderField,orderType", httpMethod = "POST")
    @RequestMapping(value = "/export", method = RequestMethod.POST)
    public void exportPartType(@RequestBody VehicleDTO req, HttpServletResponse response) {
        req.setIsTest(2);
        vehicleService.exportTestVehicleList(req,response);
    }

    @Override
    public void exportTestVehicleList(VehicleDTO req, HttpServletResponse response) {
        //1.测试车辆列表查询
        log.info("查询测试车辆列表");
        List<TestVehicleListExportVO> vehicleListVOS = vehicleMapper.queryTestVehicleExportVOList(req);

        if (vehicleListVOS.size() == 0) {
            throw new CustomException(ResultEnum.EXCEL_EXPORT_LIST_IS_EMPTY);
        } else if (ExcelConstants.MAX_EXCEL_TOTAL < vehicleListVOS.size()) {
            throw new CustomException(ResultEnum.EXCEL_EXPORT_LIST_TOO_MUCH);
        }

        try {
            log.info("填充数据，导出数据");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码
            String fileName = URLEncoder.encode(ExcelConstants.TEST_VEHICLE, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();

            //0 表示 sheet 的编号
            EasyExcelUtil.writeSheetUtil(0, ExcelConstants.TEST_VEHICLE, TestVehicleListExportVO.class, excelWriter, vehicleListVOS);
            excelWriter.finish();
        } catch (IOException e) {
            throw new CustomException(ResultEnum.EXCEL_EXPORT_LIST_FAIL);
        }
    }

package com.abupdate.iov.base.util;

import com.abupdate.iov.base.constant.ExcelConstants;
import com.abupdate.vota.common.enums.ResultEnum;
import com.abupdate.vota.common.exception.CustomException;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.WriteSheet;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class EasyExcelUtil {

    /**
     * 分 sheet 写入方法
     * @param sheetNo 从 0 开始，默认从第一个 sheet 开始写入
     * @param sheetName
     * @param objectClass
     * @param excelWriter
     * @param datas
     * @param <T> 数据类型
     * @return sheet 页的最大编号，可用于后续作为 sheetNo 的参数使用，在同一个文件中继续写入 sheet
     */
    public static <T> int writeSheetUtil(int sheetNo, String sheetName, Class<T> objectClass, ExcelWriter excelWriter, List<T> datas){
        int count = 0;
        //临时数组，用于存储每一批用于写入的数据
        int index = 0;
        List<Object> tmpList = new ArrayList<>();
        for (Object demo : datas){
            count ++;
            tmpList.add(demo);
            if (count == ExcelConstants.SHEET_MAX_TOTAL){
                count = 0;
                //写入 sheet 页
                sheetNo ++;
                index ++;
                WriteSheet writeSheet = EasyExcel.writerSheet(sheetNo, sheetName + index).head(objectClass).build();
                excelWriter.write(tmpList, writeSheet);
                tmpList = new ArrayList<>();
            }
        }
        if (tmpList.size() > 0){
            //写入个 sheet 页
            sheetNo ++;
            index ++;
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetNo, sheetName + index).head(objectClass).build();
            excelWriter.write(tmpList, writeSheet);
        }
        return sheetNo;
    }

    /**
     * 校验导入列表的头信息
     * @param headMap
     * @param context
     */
    public static void checkHeadData(Map<Integer, String> headMap, AnalysisContext context){
        //获取在对象中使用 @ExcelProperty 注解设置的头信息映射，用于检验表格头信息是否正确
        Map<Integer, Head> ObjectHeadMap =  context.currentReadHolder().excelReadHeadProperty().getHeadMap();
        //根据“是否指定了索引”为条件，获取需要判断的列数
        int columnCount = 0;
        for (Map.Entry<Integer,Head> entry : ObjectHeadMap.entrySet()){
            if (entry.getValue().getForceIndex()){
                columnCount ++;