• 几百万数据量的 Excel 导出?


    之前遇到过,导出20万条数据,每条数据200个字段,会造成内存溢出和卡顿,根据这里的刚发解决问题!

    摘抄:几百万数据量的 Excel 导出会内存溢出和卡顿?那是你没用对方法!_l_瓶中精灵的博客-CSDN博客

    一. 简介

    这里使用阿里开源的EasyExcel框架,发现可以将解析的EXCEL的内存占用控制在KB级别,并且绝对不会内存溢出(内部实现待研究),还有就是速度极快,大概100W条记录,十几个字段,只需要70秒即可完成下载。

    EasyExcel的github地址是:https://github.com/alibaba/easyexcel

    二. 案例

    2.1 POM依赖

    1. <dependency>
    2. <groupId>com.alibabagroupId>
    3. <artifactId>easyexcelartifactId>
    4. <version>1.1.1version>
    5. dependency>

    2.2 代码演示

    2.2.1.数据量少的(20W以内吧):一个SHEET一次查询导出

    1. /**
    2. * 针对较少的记录数(20W以内大概)可以调用该方法一次性查出然后写入到EXCEL的一个SHEET中
    3. * 注意: 一次性查询出来的记录数量不宜过大,不会内存溢出即可。
    4. *
    5. * @throws IOException
    6. */
    7. @Test
    8. public void writeExcelOneSheetOnceWrite() throws IOException {
    9. // 生成EXCEL并指定输出路径
    10. OutputStream out = new FileOutputStream("E:\\temp\\withoutHead1.xlsx");
    11. ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
    12. // 设置SHEET
    13. Sheet sheet = new Sheet(1, 0);
    14. sheet.setSheetName("sheet1");
    15. // 设置标题
    16. Table table = new Table(1);
    17. List> titles = new ArrayList>();
    18. titles.add(Arrays.asList("用户ID"));
    19. titles.add(Arrays.asList("名称"));
    20. titles.add(Arrays.asList("年龄"));
    21. titles.add(Arrays.asList("生日"));
    22. table.setHead(titles);
    23. // 查询数据导出即可 比如说一次性总共查询出100条数据
    24. List> userList = new ArrayList<>();
    25. for (int i = 0; i < 100; i++) {
    26. userList.add(Arrays.asList("ID_" + i, "小明" + i, String.valueOf(i), new Date().toString()));
    27. }
    28. writer.write0(userList, sheet, table);
    29. writer.finish();
    30. }

    2.2.2.数据量适中(100W以内):一个SHEET分批查询导出

    1. /**
    2. * 针对105W以内的记录数可以调用该方法分多批次查出然后写入到EXCEL的一个SHEET中
    3. * 注意:
    4. * 每次查询出来的记录数量不宜过大,根据内存大小设置合理的每次查询记录数,不会内存溢出即可。
    5. * 数据量不能超过一个SHEET存储的最大数据量105W
    6. *
    7. * @throws IOException
    8. */
    9. @Test
    10. public void writeExcelOneSheetMoreWrite() throws IOException {
    11. // 生成EXCEL并指定输出路径
    12. OutputStream out = new FileOutputStream("E:\\temp\\withoutHead2.xlsx");
    13. ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
    14. // 设置SHEET
    15. Sheet sheet = new Sheet(1, 0);
    16. sheet.setSheetName("sheet1");
    17. // 设置标题
    18. Table table = new Table(1);
    19. List> titles = new ArrayList>();
    20. titles.add(Arrays.asList("用户ID"));
    21. titles.add(Arrays.asList("名称"));
    22. titles.add(Arrays.asList("年龄"));
    23. titles.add(Arrays.asList("生日"));
    24. table.setHead(titles);
    25. // 模拟分批查询:总记录数50条,每次查询20条, 分三次查询 最后一次查询记录数是10
    26. Integer totalRowCount = 50;
    27. Integer pageSize = 20;
    28. Integer writeCount = totalRowCount % pageSize == 0 ? (totalRowCount / pageSize) : (totalRowCount / pageSize + 1);
    29. // 注: 此处仅仅为了模拟数据,实用环境不需要将最后一次分开,合成一个即可, 参数为:currentPage = i+1; pageSize = pageSize
    30. for (int i = 0; i < writeCount; i++) {
    31. // 前两次查询 每次查20条数据
    32. if (i < writeCount - 1) {
    33. List> userList = new ArrayList<>();
    34. for (int j = 0; j < pageSize; j++) {
    35. userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
    36. }
    37. writer.write0(userList, sheet, table);
    38. } else if (i == writeCount - 1) {
    39. // 最后一次查询 查多余的10条记录
    40. List> userList = new ArrayList<>();
    41. Integer lastWriteRowCount = totalRowCount - (writeCount - 1) * pageSize;
    42. for (int j = 0; j < lastWriteRowCount; j++) {
    43. userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
    44. }
    45. writer.write0(userList, sheet, table);
    46. }
    47. }
    48. writer.finish();
    49. }

    2.2.3.数据量很大(几百万都行):多个SHEET分批查询导出

    1. /**
    2. * 针对几百万的记录数可以调用该方法分多批次查出然后写入到EXCEL的多个SHEET中
    3. * 注意:
    4. * perSheetRowCount % pageSize要能整除 为了简洁,非整除这块不做处理
    5. * 每次查询出来的记录数量不宜过大,根据内存大小设置合理的每次查询记录数,不会内存溢出即可。
    6. *
    7. * @throws IOException
    8. */
    9. @Test
    10. public void writeExcelMoreSheetMoreWrite() throws IOException {
    11. // 生成EXCEL并指定输出路径
    12. OutputStream out = new FileOutputStream("E:\\temp\\withoutHead3.xlsx");
    13. ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
    14. // 设置SHEET名称
    15. String sheetName = "测试SHEET";
    16. // 设置标题
    17. Table table = new Table(1);
    18. List> titles = new ArrayList>();
    19. titles.add(Arrays.asList("用户ID"));
    20. titles.add(Arrays.asList("名称"));
    21. titles.add(Arrays.asList("年龄"));
    22. titles.add(Arrays.asList("生日"));
    23. table.setHead(titles);
    24. // 模拟分批查询:总记录数250条,每个SHEET存100条,每次查询20条 则生成3个SHEET,前俩个SHEET查询次数为5, 最后一个SHEET查询次数为3 最后一次写的记录数是10
    25. // 注:该版本为了较少数据判断的复杂度,暂时perSheetRowCount要能够整除pageSize, 不去做过多处理 合理分配查询数据量大小不会内存溢出即可。
    26. Integer totalRowCount = 250;
    27. Integer perSheetRowCount = 100;
    28. Integer pageSize = 20;
    29. Integer sheetCount = totalRowCount % perSheetRowCount == 0 ? (totalRowCount / perSheetRowCount) : (totalRowCount / perSheetRowCount + 1);
    30. Integer previousSheetWriteCount = perSheetRowCount / pageSize;
    31. Integer lastSheetWriteCount = totalRowCount % perSheetRowCount == 0 ?
    32. previousSheetWriteCount :
    33. (totalRowCount % perSheetRowCount % pageSize == 0 ? totalRowCount % perSheetRowCount / pageSize : (totalRowCount % perSheetRowCount / pageSize + 1));
    34. for (int i = 0; i < sheetCount; i++) {
    35. // 创建SHEET
    36. Sheet sheet = new Sheet(i, 0);
    37. sheet.setSheetName(sheetName + i);
    38. if (i < sheetCount - 1) {
    39. // 前2个SHEET, 每个SHEET查5次 每次查20条 每个SHEET写满100行 2个SHEET合计200行 实用环境:参数:currentPage: j+1 + previousSheetWriteCount*i, pageSize: pageSize
    40. for (int j = 0; j < previousSheetWriteCount; j++) {
    41. List> userList = new ArrayList<>();
    42. for (int k = 0; k < 20; k++) {
    43. userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
    44. }
    45. writer.write0(userList, sheet, table);
    46. }
    47. } else if (i == sheetCount - 1) {
    48. // 最后一个SHEET 实用环境不需要将最后一次分开,合成一个即可, 参数为:currentPage = i+1; pageSize = pageSize
    49. for (int j = 0; j < lastSheetWriteCount; j++) {
    50. // 前俩次查询 每次查询20条
    51. if (j < lastSheetWriteCount - 1) {
    52. List> userList = new ArrayList<>();
    53. for (int k = 0; k < 20; k++) {
    54. userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
    55. }
    56. writer.write0(userList, sheet, table);
    57. } else if (j == lastSheetWriteCount - 1) {
    58. // 最后一次查询 将剩余的10条查询出来
    59. List> userList = new ArrayList<>();
    60. Integer lastWriteRowCount = totalRowCount - (sheetCount - 1) * perSheetRowCount - (lastSheetWriteCount - 1) * pageSize;
    61. for (int k = 0; k < lastWriteRowCount; k++) {
    62. userList.add(Arrays.asList("ID_" + Math.random(), "小明1", String.valueOf(Math.random()), new Date().toString()));
    63. }
    64. writer.write0(userList, sheet, table);
    65. }
    66. }
    67. }
    68. }
    69. writer.finish();
    70. }

    三、附一个将对象字段,按顺序转为list的工具

    1. /**
    2. * excel导出:字段数据映射
    3. * 为null的返回空串
    4. * dataList 对象
    5. * 需要转换的字段集合,顺序也是按照这个
    6. */
    7. public static List> getObjectData2(List dataList, List fields) {
    8. if (Objects.isNull(dataList) || Objects.isNull(fields)) {
    9. return null;
    10. }
    11. List> result = new ArrayList<>();
    12. Map map;
    13. for (T entity : dataList) {
    14. try {
    15. List data = new ArrayList<>();
    16. map = BeanUtils.describe(entity);
    17. String value;
    18. for (String field : fields) {
    19. value = map.get(field);
    20. value = StringUtils.isBlank(value) ? "" : value;
    21. data.add(value);
    22. }
    23. map.clear();
    24. result.add(data);
    25. } catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException e) {
    26. //log.error(e.getMessage(), e);
    27. e.printStackTrace();
    28. }
    29. }
    30. map = null;
    31. return result;
    32. }

    三、其他

    前后端实现分片实现大文件上传:

    https://gitee.com/ninesuntec/large-file-upload

  • 相关阅读:
    MySQL 慢查询
    服装品牌如何成为“数字化的头号玩家”?这个案例不容错过!
    JVM类加载机制
    requires ipykernel package
    OSI参考模型个人总结
    如何在熊市中寻找机会?
    用Python把附近的足浴店都给采集了一遍,好兄弟:针不戳~
    《深度学习进阶 自然语言处理》第四章:Embedding层和负采样介绍
    基于Servlet+jsp+mysql开发javaWeb学生管理系统(学生信息、学生选课、学生成绩、学生签到考勤)
    线程池 的一些事
  • 原文地址:https://blog.csdn.net/S_L__/article/details/128185268