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
最后
如果觉得博文不错，关注我 vnjohn，后续会有更多实战、源码、架构干货分享！干货连连不断～
推荐专栏：Spring、MySQL，订阅一波不再迷路
大家的「关注❤️ + 点赞👍 + 收藏⭐」就是我创作的最大动力！谢谢大家的支持，我们下文见！
