EasyExcel 百万级别数据高效率导入/导出（下）-阿里云开发者社区

MyBatis

1、Mapper.xml 映射文件

<insert id="importToDb" parameterType="ActResultLogDO">
         insert into ACT_RESULT_LOG (onlineseqid,businessid,becifno,ivisresult,createdby,createddate,updateby,updateddate,risklevel)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (
            #{item.onlineseqid},#{item.businessid},#{item.becifno},#{item.ivisresult},#{item.createdby},#{item.createddate},
            #{item.updateby},#{item.updateddate},#{item.risklevel}
            )
        </foreach>
</insert>

Excel.read() 方法需要一个监听器，当（事件）数据到达时，通过你自定义的这个监听器进行数据的流转处理.

2、EasyExcel 监听器

public class EasyExcelGeneralDataMybatisListener extends AnalysisEventListener<ActResultLogVO> {
    private IActResultLogService actResultLogService;
    /**
     * 用于存储读取的数据
     */
    private List<ActResultLogVO> dataList = new ArrayList<>();
    public EasyExcelGeneralDataMybatisListener() {
    }
    public EasyExcelGeneralDataMybatisListener(IActResultLogService actResultLogService) {
        this.actResultLogService = actResultLogService;
    }
    @Override
    public void invoke(ActResultLogVO data, AnalysisContext context) {
        // 数据add进入集合
        dataList.add(data);
        // size是否为100000条:这里其实就是分批.当数据等于10w的时候执行一次插入
        if (dataList.size() >= ExcelConstants.GENERAL_ONCE_SAVE_TO_DB_ROWS_JDBC) {
            // 存入数据库:数据小于 1w 条使用 Mybatis 批量插入即可
            saveData();
            // 清理集合便于GC回收
            dataList.clear();
        }
    }
    /**
     * 保存数据到 DB
     */
    private void saveData() {
        if (dataList.size() > 0) {
            actResultLogService.import2DBFromExcel10wByMybatis(StarBeanUtils.copyList(dataList, ActResultLogDO.class));
            dataList.clear();
        }
    }
    /**
     * Excel 中所有数据解析完毕会调用此方法
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        dataList.clear();
    }
}

3、单元测试类

@Slf4j
@RunWith(SpringRunner.class)
@SpringBootTest(classes = StarApplication.class)
public class EasyExcelTest {
    @Resource
    private IActResultLogService actResultLogService;
    // EasyExcel的读取Excel数据的API
    @Test
    public void import2DBFromExcel10wTest() {
        String fileName = "/Users/vnjohn/Downloads/export-excel.xlsx";
        // 记录开始读取Excel时间,也是导入程序开始时间
        long startReadTime = System.currentTimeMillis();
        log.info("------开始读取Excel的Sheet时间(包括导入数据过程):" + startReadTime + " ms------");
        // 读取所有Sheet的数据.每次读完一个Sheet就会调用这个方法
        EasyExcel.read(fileName, ActResultLogVO.class, new EasyExcelGeneralDataMybatisListener(actResultLogService))
                .registerConverter(new EasyExcelLocalDateConvert()).doReadAll();
//        EasyExcel.read(fileName, new EasyExcelGeneralDataJDBCListener(actResultLogService)).doReadAll();
        long endReadTime = System.currentTimeMillis();
        log.info("------结束读取Excel的Sheet时间(包括导入数据过程):" + endReadTime + " ms------");
        log.info("------导入总花费时长：{}", ((endReadTime - startReadTime) / 1000) + "s------");
    }
}

通过 Mybatis 导入，需要映射到具体的实体，JDK1.8 以后时间类型大部分都是使用 LocalDate 这种类型了，在 EasyExcel 中没有提供这种 Excel 时间列值到时间类型的转换，所以我们需要自定义一个转换器用来适配

4、时间转换类

public class EasyExcelLocalDateConvert implements Converter<LocalDate> {
    @Override
    public Class<LocalDate> supportJavaTypeKey() {
        return LocalDate.class;
    }
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }
    @Override
    public LocalDate convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty,
                                       GlobalConfiguration globalConfiguration) throws Exception {
        return LocalDate.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd"));
    }
    @Override
    public WriteCellData<LocalDate> convertToExcelData(LocalDate value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return new WriteCellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
    }
}

最后，执行 @Test 方法，结果如下：

------开始读取Excel的Sheet时间(包括导入数据过程):1657257416753ms
------结束读取Excel的Sheet时间(包括导入数据过程):1657257794753 ms------
------导入总花费时长：378s------

JDBC

1、数据库配置 properties 文件

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
username=root
password=123456

使用 JDBC 读取 Excel 每一行数据返回的数据类型是 Map<Integer,String>，所以它对应的监听器的处理过程如下：

public class EasyExcelGeneralDataJDBCListener extends AnalysisEventListener<Map<Integer, String>> {
    private IActResultLogService actResultLogService;
    /**
     * 用于存储读取的数据
     */
    private List<Map<Integer, String>> dataList = new ArrayList<>();
    public EasyExcelGeneralDataJDBCListener() {
    }
    public EasyExcelGeneralDataJDBCListener(IActResultLogService actResultLogService) {
        this.actResultLogService = actResultLogService;
    }
    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) {
        // 数据add进入集合
        dataList.add(data);
        // size是否为100000条:这里其实就是分批.当数据等于10w的时候执行一次插入
        if (dataList.size() >= ExcelConstants.GENERAL_ONCE_SAVE_TO_DB_ROWS_JDBC) {
            // 存入数据库:数据小于 1w 条使用 Mybatis 批量插入即可
            saveData();
            // 清理集合便于GC回收
            dataList.clear();
        }
    }
    /**
     * 保存数据到 DB
     */
    private void saveData() {
        if (dataList.size() > 0) {
            actResultLogService.import2DBFromExcel10wByJDBC(dataList);
            dataList.clear();
        }
    }
    /**
     * Excel 中所有数据解析完毕会调用此方法
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        dataList.clear();
    }
}

最后，执行 @Test 方法后，结果如下：

------开始读取Excel的Sheet时间(包括导入数据过程):1657258268921 ms------
------结束读取Excel的Sheet时间(包括导入数据过程):1657258435024 ms------
------导入总花费时长：166s------

比较 MyBatis、原生 JDBC 执行后的结果可以看到，JDBC 的效率比 MyBatis 提升了 2.2 以上

导出

导出的数据量按照 200W 来进行测试，100W 存到一个 sheet 里面，从数据库先查询总数，然后 /100W 得出多少个 sheet，外层循环是 sheet 数量，内层是要读取数据库的次数，比如：一次性查询出 20W 数据，那么一个 sheet 就需要查 5 次，因此查询次数在 sheet数*5 这个范围；注意：在每次从数据库读取完以后，记得清空集合的元素，方便提前被 GC 回收，防止内存的空间占用

1、导出工具类

@Slf4j
@Component
public class EasyExcelUtil {
    @Resource
    private IActResultLogService actResultLogService;
    /**
     * 导出逻辑代码
     *
     * @param response
     */
    public void dataExport300w(HttpServletResponse response) {
        OutputStream outputStream = null;
        try {
            long startTime = System.currentTimeMillis();
            log.info("导出开始时间:{}", startTime);
            outputStream = response.getOutputStream();
            WriteWorkbook writeWorkbook = new WriteWorkbook();
            writeWorkbook.setOutputStream(outputStream);
            writeWorkbook.setExcelType(ExcelTypeEnum.XLSX);
            ExcelWriter writer = new ExcelWriter(writeWorkbook);
            String fileName = new String(("export-excel").getBytes(), StandardCharsets.UTF_8);
            // TODO WriteTable 标题这块可以作为公共的封装起来：通过反射获取变量上注解等
            WriteTable table = new WriteTable();
            List<List<String>> titles = new ArrayList<List<String>>();
            titles.add(Collections.singletonList("onlineseqid"));
            titles.add(Collections.singletonList("businessid"));
            titles.add(Collections.singletonList("becifno"));
            titles.add(Collections.singletonList("ivisresult"));
            titles.add(Collections.singletonList("createdby"));
            titles.add(Collections.singletonList("createddate"));
            titles.add(Collections.singletonList("updateby"));
            titles.add(Collections.singletonList("updateddate"));
            titles.add(Collections.singletonList("risklevel"));
            table.setHead(titles);
            // 记录总数：实际中需要根据查询条件（过滤数据）进行统计即可，
            // TODO 此处写入限定的条数进行自测
//            Integer totalCount = actResultLogService.count();
            Integer totalCount = 200 * 10000;
            // 每一个Sheet存放100w条数据
            Integer sheetDataRows = ExcelConstants.PER_SHEET_ROW_COUNT;
            // 每次写入的数据量20w
            Integer writeDataRows = ExcelConstants.PER_WRITE_ROW_COUNT;
            // 计算需要的Sheet数量
            int sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
            // 计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)
            int oneSheetWriteCount = totalCount > sheetDataRows ? sheetDataRows / writeDataRows : totalCount % writeDataRows > 0 ? totalCount / writeDataRows + 1 : totalCount / writeDataRows;
            // 计算最后一个sheet需要写入的次数
            int lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount / sheetDataRows / writeDataRows + 1));
            // 开始分批查询分次写入
            // 注意这次的循环就需要进行嵌套循环了,外层循环是Sheet数目,内层循环是写入次数
            List<List<String>> dataList = new ArrayList<>();
            for (int i = 0; i < sheetNum; i++) {
                //创建Sheet
                WriteSheet sheet = new WriteSheet();
                sheet.setSheetNo(i);
                sheet.setSheetName(fileName + i);
                // 循环写入次数: j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCount
                for (int j = 0; j < (i != sheetNum - 1 || i==0 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
                    // 集合复用,便于GC清理
                    dataList.clear();
                    // 分页查询一次20w
                    List<ActResultLogDO> resultList = actResultLogService.findByPage100w(j + 1 + oneSheetWriteCount * i, writeDataRows);
                    if (!CollectionUtils.isEmpty(resultList)) {
                        resultList.forEach(item -> {
                            dataList.add(Arrays.asList(item.getOnlineseqid(), item.getBusinessid(), item.getBecifno(), item.getIvisresult(), item.getCreatedby(), LocalDate.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")), item.getUpdateby(), LocalDate.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")), item.getRisklevel()));
                        });
                    }
                    // 写数据
                    writer.write(dataList, sheet, table);
                }
            }
            // 下载EXCEL 以下代码可以作为公共的进行封装.
            setExcelRespProp(response, fileName);
            writer.finish();
            outputStream.flush();
            // 导出时间结束
            long endTime = System.currentTimeMillis();
            log.info("导出结束时间:{}", endTime + "ms");
            log.info("导出所用时间:{}", (endTime - startTime) / 1000 + "秒");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
    /**
     * 公共响应参数
     */
    public static void setResponseParam(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
    // 下载EXCEL 以下代码可以作为公共的进行封装.
    response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), StandardCharsets.ISO_8859_1) + ".xlsx");
    response.setContentType("multipart/form-data");
    response.setCharacterEncoding("utf-8");
  }
  /**
     * 通过反射方式将头部作为公共部分进行设置
     */
  public void setTitles(Class clazz) {
    List<List<String>> titles = new ArrayList<List<String>>();
    for (Field declaredField : clazz.getDeclaredFields()) {
      ExcelProperty annotation = declaredField.getAnnotation(ExcelProperty.class);
      if (null != annotation) {
        titles.add(Arrays.asList(annotation.value()));
      }
    }
  }
    /**
     * 设置excel下载响应头属性
     */
    public static void setExcelRespProp(HttpServletResponse response, String rawFileName){
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = null;
        try {
            fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\\+", "%20");
        } catch (UnsupportedEncodingException e) {
            log.error("设置excel下载响应头属性，失败 {}",e.getMessage());
        }
        response.setHeader("Content-disposition", "attachment;filename=utf-8''" + fileName + ".xlsx");
    }
}

前面已经把控制器类贴出来了，请求地址：前缀/excel/export，我这边采用的测试工具为 safari 浏览器，如果是 windows 可以使用自带的微软浏览器，Google、 Postman 我这边在测试的时候可能是因为数据量太大的原因导致内存爆满，程序就异常退出了.

导出的时间结果如下：

导出结束时间:1657259099773ms
导出所用时间:71秒

操作时遇到的错误

1、MySQL 允许最大的包文件参数调整

在进行 MyBatis 测试时，一下子堆积 10W 数据批量插入，MySQL 抛出异常提示传输的包文件过大，超出了参数的配置，导致无法正常插入，需要调整 show VARIABLES like ‘%max_allowed_packet%’，Windows 是在 server 根目录下的 my.ini 文件中，默认为 8M，将其调整 64M 即可；Linux/Mac 是在 server 根目录下的 my.cnf 文件里，Mac 无须调整，参数大小足够支撑了.

2、导入 Excel 列数据无法与实体属性映射问题

刚开始 ActResultLogVO 类上只是加了 @Getter/@Setter 注解，不是使用的 @Data 注解，导致无法映射，最终修改为 @Data 注解该问题不再出现，具体原因后续文章进行详细分析.

足以见识到 EasyExcel 支撑的强大数据量，对此底层实现后续可以详细了解和阅读，学习其强大的内功心法加粗样式

参考文献

https://www.freesion.com/article/8852561865/

https://blog.csdn.net/weixin_44848900/article/details/117701981

EasyExcel 案例源码

最后

如果觉得博文不错，关注我 vnjohn，后续会有更多实战、源码、架构干货分享！干货连连不断～

推荐专栏：Spring、MySQL，订阅一波不再迷路

大家的「关注❤️ + 点赞👍 + 收藏⭐」就是我创作的最大动力！谢谢大家的支持，我们下文见！

EasyExcel 百万级别数据高效率导入/导出（下）

MyBatis

JDBC

导出

操作时遇到的错误

参考文献

最后

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

EasyExcel 百万级别数据高效率导入/导出（下）

MyBatis

JDBC

导出

操作时遇到的错误

参考文献

最后

热门文章

最新文章

相关课程

相关电子书

相关实验场景