记一次 Java 导出大批量 Excel 优化
常用的excel导出方案，详情见Spring Boot 入门（十二）：报表导出，对比poi、jxl和esayExcel的效率，其中jxl、esayEscel 底层都是基于 poi，它们仅仅是对 poi 进行了封装，使导出 excel 的代码更加简洁（封装 poi，导出 excel 的工具类有很多，jxl 和 esayExcel 使用的比较多）。所以，如果遇到 excel 导出效率低下，直接基于 poi 导出 Excel，效率肯定是最高的，只是代码比较繁琐与杂乱。
我主要是基于 esayExcel （目前使用比较多），对其 api 进行衍生与应用，解决导出大量 Excel 导致的 OOM，或者数据超界异常：
java.lang.IllegalArgumentException: Invalid row number (1048576) outside allowable range (0..1048575)
应用是基于100W条数据进行的测试，数据的获取代码如下
1 package bean; 2 3 import com.alibaba.excel.annotation.ExcelProperty; 4 import com.alibaba.excel.annotation.format.DateTimeFormat; 5 import com.alibaba.excel.annotation.format.NumberFormat; 6 import lombok.Data; 7 import lombok.experimental.Accessors; 8 9 import java.util.Date; 10 11 /** 12 * @author dz 13 * @date 2021-11-06 上午 9:14 14 */ 15 @Accessors(chain = true) 16 @Data 17 public class ExcelBean { 18 19 @ExcelProperty("主键id") 20 private String id; 21 22 @ExcelProperty("姓名") 23 private String name; 24 25 @ExcelProperty("地址") 26 private String address; 27 28 @ExcelProperty("年龄") 29 private Integer age; 30 31 @ExcelProperty("数量") 32 private Integer number; 33 34 @NumberFormat("#.##") 35 @ExcelProperty("身高") 36 private Double high; 37 38 @ExcelProperty("距离") 39 private Double distance; 40 41 @DateTimeFormat("yyyy-MM-dd HH:mm:ss") 42 @ExcelProperty("开始时间") 43 private Date startTime; 44 45 @ExcelProperty("结束时间") 46 private Date endTime; 47 }
1 /** 2 * 获取excel 导出的数据 3 * 4 * @return list 集合 5 */ 6 private List<ExcelBean> getDate() { 7 log.info("开始生成数据"); 8 Date date = new Date(); 9 long startTime = System.currentTimeMillis(); 10 List<ExcelBean> list = Lists.newArrayList(); 11 for (int i = 0; i < 1000000; i++) { 12 ExcelBean bean = new ExcelBean(); 13 bean.setId(UUID.randomUUID().toString()). 14 setName("隔壁老樊" + i). 15 setAddress("北京市朝阳区酒仙桥" + i + "路"). 16 setAge(i). 17 setNumber(i + 10000). 18 setHigh(1.234 * i). 19 setDistance(1.234 * i). 20 setStartTime(date). 21 setEndTime(date); 22 list.add(bean); 23 } 24 log.info("数据生成结束，数据量={}，耗时={}ms", list.size(), System.currentTimeMillis() - startTime); 25 return list; 26 }
pom 依赖
1 <dependency> 2 <groupId>org.projectlombok</groupId> 3 <artifactId>lombok</artifactId> 4 </dependency> 5 <dependency> 6 <groupId>com.alibaba</groupId> 7 <artifactId>easyexcel</artifactId> 8 <version>2.2.10</version> 9 </dependency>
依赖 esayexcel 时，如果项目已经依赖了 poi，有可能会产生jar 包依赖冲突（easyexcel底层也是基于 poi），解决方案如下：
方案一：查看 easyexcel 中依赖的 poi 的版本，然后将项目其余地方的版本修改成该版本，使项目依赖的 poi 版本和 easyexcel 依赖的版本一致
方案二：在 esayexcel 中将 poi 的依赖排除掉，如下
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.10</version> <exclusions> <exclusion> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> </exclusion> <exclusion> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> </exclusion> <exclusion> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> </exclusion> </exclusions> </dependency>
常量的定义
1 public static final String FILE_NAME = "D:\\test_" + System.currentTimeMillis() + ".xlsx"; 2 // 每个 sheet 写入的数据 3 public static final int NUM_PER_SHEET = 300000; 4 // 每次向 sheet 中写入的数据（分页写入） 5 public static final int NUM_BY_TIMES = 50000;
1.EasyExcel 导出 excel 应用
使用 esayExcel 自带的 api 导出 excel 的应用，代码如下
1 /** 2 * 方法一：将数据写入到excel 3 * 直接调用api，适合小数据量 4 * 100W条数据33s 5 */ 6 @Test 7 public void writeExcelByApi() { 8 String fileName = FILE_NAME; 9 log.info("导出excel名称={}", fileName); 10 long startTime = System.currentTimeMillis(); 11 // 直接调用api 12 List<ExcelBean> date = getDate(); 13 EasyExcel.write(fileName, ExcelBean.class).sheet().doWrite(date); 14 log.info("导出excel结束,数据量={}，耗时={}ms", date.size(), System.currentTimeMillis() - startTime); 15 }
当 list 对象数据量太多，就会产生异常：原因是单个 excel 一个 sheet 的最大数据量为 1048575
1 java.lang.IllegalArgumentException: Invalid row number (1048576) outside allowable range (0..1048575) 2 3 at org.apache.poi.xssf.streaming.SXSSFSheet.createRow(SXSSFSheet.java:123) 4 at org.apache.poi.xssf.streaming.SXSSFSheet.createRow(SXSSFSheet.java:65) 5 at com.alibaba.excel.util.WorkBookUtil.createRow(WorkBookUtil.java:70) 6 at com.alibaba.excel.write.executor.ExcelWriteAddExecutor.addOneRowOfDataToExcel(ExcelWriteAddExecutor.java:67) 7 at com.alibaba.excel.write.executor.ExcelWriteAddExecutor.add(ExcelWriteAddExecutor.java:56) 8 at com.alibaba.excel.write.ExcelBuilderImpl.addContent(ExcelBuilderImpl.java:58) 9 at com.alibaba.excel.ExcelWriter.write(ExcelWriter.java:161) 10 at com.alibaba.excel.ExcelWriter.write(ExcelWriter.java:146) 11 at com.alibaba.excel.write.builder.ExcelWriterSheetBuilder.doWrite(ExcelWriterSheetBuilder.java:61) 12 at mytest.TestExcel.writeExcelByApi(TestExcel.java:40) 13 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 14 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 15 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 16 at java.lang.reflect.Method.invoke(Method.java:498) 17 at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59) 18 at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) 19 at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56) 20 at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) 21 at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306) 22 at org.junit.runners.BlockJUnit4ClassRunner$1.evaluate(BlockJUnit4ClassRunner.java:100) 23 at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366) 24 at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:103) 25 at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:63) 26 at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331) 27 at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79) 28 at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329) 29 at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66) 30 at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293) 31 at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306) 32 at org.junit.runners.ParentRunner.run(ParentRunner.java:413) 33 at org.junit.runner.JUnitCore.run(JUnitCore.java:137) 34 at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69) 35 at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33) 36 at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:220) 37 at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:53)
2.EasyExcel 导出 excel 应用优化一：execl 数据量超过1048575
将数据写入到不同的 sheet，保证每个 sheet 的数据量小于 1048575 行，解决此问题，代码如下
1 /** 2 * 方法二：导出多个sheet 3 * easyExcel 底层是 POI 实现的，POI 单个sheet 最多只能导出 1048576 行，超过该行数，会产生如下异常 4 * java.lang.IllegalArgumentException: Invalid row number (1048576) outside allowable range (0..1048575) 5 * <p> 6 * 11:57:55.541 [main] INFO mytest.TestExcel - 写入sheet=sheet0，数据量300000-0=300000，耗时=6055ms 7 * 11:57:59.701 [main] INFO mytest.TestExcel - 写入sheet=sheet1，数据量600000-300000=300000，耗时=4159ms 8 * 11:58:03.827 [main] INFO mytest.TestExcel - 写入sheet=sheet2，数据量900000-600000=300000，耗时=4126ms 9 * 11:58:05.193 [main] INFO mytest.TestExcel - 写入sheet=sheet3，数据量1000000-900000=100000，耗时=1366ms 10 * 11:58:17.418 [main] INFO mytest.TestExcel - 导出excel结束,总数据量=1000000，耗时=31297ms 11 */ 12 @Test 13 public void writeExcelByMulSheet() { 14 String fileName = FILE_NAME; 15 log.info("导出excel名称={}", fileName); 16 long startTime = System.currentTimeMillis(); 17 // 获取数据 18 List<ExcelBean> date = getDate(); 19 // 获取 sheet 的个数 20 int sheetNum = date.size() % NUM_PER_SHEET == 0 ? date.size() / NUM_PER_SHEET : date.size() / NUM_PER_SHEET + 1; 21 // 指定写入的文件 22 ExcelWriter excelWriter = EasyExcel.write(fileName, ExcelBean.class).build(); 23 for (int i = 0; i < sheetNum; i++) { 24 long l = System.currentTimeMillis(); 25 // 设置 sheet 的名字（sheet不能相同） 26 String sheetName = "sheet" + i; 27 WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetName).build(); 28 int startNum = i * NUM_PER_SHEET; 29 int endNum = i == sheetNum - 1 ? date.size() : (i + 1) * NUM_PER_SHEET; 30 excelWriter.write(date.subList(startNum, endNum), writeSheet); 31 log.info("写入sheet={}，数据量{}-{}={}，耗时={}ms", sheetName, endNum, startNum, endNum - startNum, System.currentTimeMillis() - l); 32 } 33 // 最好放在 finally中 34 excelWriter.finish(); 35 log.info("导出excel结束,总数据量={}，耗时={}ms", date.size(), System.currentTimeMillis() - startTime); 36 }
3.EasyExcel 导出 excel 应用优化二：数据源 list 太大，直接读取全部的 list 数据导致 OOM
将 list 数据进行分页读取，并进行分页写入到 excel。这样还有个好处，每次每页读取部分数据，然后写入到 excel 中（相当于该批数据已经从内存刷到了磁盘），也增加了写入的效率；poi 中的导出excel，为此专门提供了一个刷新磁盘的 api，具体代码如下
1 /** 2 * 方法三：同一个 Sheet，分批多次写入 3 * 当单次读取的 list 数据量过大，会产生 OOM 异常，所以需要分页读取并写入到 excel 4 * 11:55:01.590 [main] INFO mytest.TestExcel - 写入数量50000-0=50000，耗时=2227ms 5 * 11:55:02.429 [main] INFO mytest.TestExcel - 写入数量100000-50000=50000，耗时=838ms 6 * 11:55:03.188 [main] INFO mytest.TestExcel - 写入数量150000-100000=50000，耗时=759ms 7 * 11:55:03.951 [main] INFO mytest.TestExcel - 写入数量200000-150000=50000，耗时=762ms 8 * 11:55:04.708 [main] INFO mytest.TestExcel - 写入数量250000-200000=50000，耗时=757ms 9 * 11:55:05.471 [main] INFO mytest.TestExcel - 写入数量300000-250000=50000，耗时=762ms 10 * 11:55:06.172 [main] INFO mytest.TestExcel - 写入数量350000-300000=50000，耗时=701ms 11 * 11:55:06.921 [main] INFO mytest.TestExcel - 写入数量400000-350000=50000，耗时=749ms 12 * 11:55:07.688 [main] INFO mytest.TestExcel - 写入数量450000-400000=50000，耗时=767ms 13 * 11:55:08.437 [main] INFO mytest.TestExcel - 写入数量500000-450000=50000，耗时=749ms 14 * 11:55:09.141 [main] INFO mytest.TestExcel - 写入数量550000-500000=50000，耗时=704ms 15 * 11:55:09.899 [main] INFO mytest.TestExcel - 写入数量600000-550000=50000，耗时=758ms 16 * 11:55:10.597 [main] INFO mytest.TestExcel - 写入数量650000-600000=50000，耗时=698ms 17 * 11:55:11.353 [main] INFO mytest.TestExcel - 写入数量700000-650000=50000，耗时=756ms 18 * 11:55:12.055 [main] INFO mytest.TestExcel - 写入数量750000-700000=50000，耗时=701ms 19 * 11:55:12.820 [main] INFO mytest.TestExcel - 写入数量800000-750000=50000，耗时=765ms 20 * 11:55:13.576 [main] INFO mytest.TestExcel - 写入数量850000-800000=50000，耗时=756ms 21 * 11:55:14.287 [main] INFO mytest.TestExcel - 写入数量900000-850000=50000，耗时=711ms 22 * 11:55:15.055 [main] INFO mytest.TestExcel - 写入数量950000-900000=50000，耗时=768ms 23 * 11:55:15.773 [main] INFO mytest.TestExcel - 写入数量1000000-950000=50000，耗时=718ms 24 * 11:55:28.016 [main] INFO mytest.TestExcel - 导出excel结束,总数据量=1000000，耗时=31738ms 25 * 26 * Process finished with exit code 0 27 */ 28 @Test 29 public void writeExcelByMulWrite() { 30 String fileName = FILE_NAME; 31 log.info("导出excel名称={}", fileName); 32 long startTime = System.currentTimeMillis(); 33 // 获取数据 34 List<ExcelBean> date = getDate(); 35 ExcelWriter excelWrite = EasyExcel.write(fileName, ExcelBean.class).build(); 36 WriteSheet writeSheet = EasyExcel.writerSheet("testSheet").build(); 37 // 计算需要写入的次数 38 int times = date.size() % NUM_BY_TIMES == 0 ? date.size() / NUM_BY_TIMES : date.size() / NUM_BY_TIMES + 1; 39 for (int i = 0; i < times; i++) { 40 long l = System.currentTimeMillis(); 41 int startNum = i * NUM_BY_TIMES; 42 int endNum = i == times - 1 ? date.size() : (i + 1) * NUM_BY_TIMES; 43 excelWrite.write(date.subList(startNum, endNum), writeSheet); 44 log.info("写入数量{}-{}={}，耗时={}ms", endNum, startNum, endNum - startNum, System.currentTimeMillis() - l); 45 } 46 // 需要放入 finally 中 47 if (excelWrite != null) { 48 excelWrite.finish(); 49 } 50 log.info("导出excel结束,总数据量={}，耗时={}ms", date.size(), System.currentTimeMillis() - startTime); 51 }
4.EasyExcel 导出 excel 应用优化三：结合前面两种方案
将 list 数据进行分页读取，并且每个 sheet 分多次写入，且写入到多个 sheet 中
1 /** 2 * 方案四：写入多个 sheet，并且每个 sheet 写入多次数据（结合方案二、三） 3 * 数据量大，导致一个 sheet 存储不下；同时单次读入的数据量太大。可以采用这个方法
4 * 12:02:18.751 [main] INFO mytest.TestExcel - 写入sheet=sheet0，数据量=50000-0=50000，耗时=1558 5 * 12:02:19.542 [main] INFO mytest.TestExcel - 写入sheet=sheet0，数据量=100000-50000=50000，耗时=791 6 * 12:02:20.282 [main] INFO mytest.TestExcel - 写入sheet=sheet0，数据量=150000-100000=50000，耗时=740 7 * 12:02:21.037 [main] INFO mytest.TestExcel - 写入sheet=sheet0，数据量=200000-150000=50000，耗时=755 8 * 12:02:21.781 [main] INFO mytest.TestExcel - 写入sheet=sheet0，数据量=250000-200000=50000，耗时=744 9 * 12:02:22.524 [main] INFO mytest.TestExcel - 写入sheet=sheet0，数据量=300000-250000=50000，耗时=742 10 * 12:02:23.201 [main] INFO mytest.TestExcel - 写入sheet=sheet1，数据量=350000-300000=50000，耗时=677 11 * 12:02:23.852 [main] INFO mytest.TestExcel - 写入sheet=sheet1，数据量=400000-350000=50000，耗时=651 12 * 12:02:24.451 [main] INFO mytest.TestExcel - 写入sheet=sheet1，数据量=450000-400000=50000，耗时=599 13 * 12:02:25.100 [main] INFO mytest.TestExcel - 写入sheet=sheet1，数据量=500000-450000=50000，耗时=649 14 * 12:02:25.753 [main] INFO mytest.TestExcel - 写入sheet=sheet1，数据量=550000-500000=50000，耗时=653 15 * 12:02:26.350 [main] INFO mytest.TestExcel - 写入sheet=sheet1，数据量=600000-550000=50000，耗时=597 16 * 12:02:26.995 [main] INFO mytest.TestExcel - 写入sheet=sheet2，数据量=650000-600000=50000，耗时=645 17 * 12:02:27.588 [main] INFO mytest.TestExcel - 写入sheet=sheet2，数据量=700000-650000=50000，耗时=593 18 * 12:02:28.244 [main] INFO mytest.TestExcel - 写入sheet=sheet2，数据量=750000-700000=50000，耗时=656 19 * 12:02:28.893 [main] INFO mytest.TestExcel - 写入sheet=sheet2，数据量=800000-750000=50000，耗时=648 20 * 12:02:29.506 [main] INFO mytest.TestExcel - 写入sheet=sheet2，数据量=850000-800000=50000，耗时=613 21 * 12:02:30.163 [main] INFO mytest.TestExcel - 写入sheet=sheet2，数据量=900000-850000=50000，耗时=657 22 * 12:02:30.760 [main] INFO mytest.TestExcel - 写入sheet=sheet3，数据量=950000-900000=50000，耗时=597 23 * 12:02:31.419 [main] INFO mytest.TestExcel - 写入sheet=sheet3，数据量=1000000-950000=50000，耗时=659 24 * 12:02:43.235 [main] INFO mytest.TestExcel - 导出excel结束,总数据量=1000000，耗时=28818ms 25 * 26 * Process finished with exit code 0 27 */ 28 @Test 29 public void writeExcelByMulSheetAndWriteChange() { 30 String fileName = FILE_NAME; 31 log.info("导出excel名称={}", fileName); 32 long startTime = System.currentTimeMillis(); 33 // 获取数据 34 List<ExcelBean> date = getDate(); 35 // 获取 sheet 的个数 36 int sheetNum = date.size() % NUM_PER_SHEET == 0 ? date.size() / NUM_PER_SHEET : date.size() / NUM_PER_SHEET + 1; 37 // 获取每个sheet 写入的次数 38 int writeNumPerSheet = NUM_PER_SHEET % NUM_BY_TIMES == 0 ? NUM_PER_SHEET / NUM_BY_TIMES : NUM_PER_SHEET / NUM_BY_TIMES + 1; 39 // 最后一个 sheet 写入的数量 40 int writeNumLastSheet = date.size() - (sheetNum - 1) * NUM_PER_SHEET; 41 // 最后一个 sheet 写入的次数 42 int writeNumPerLastSheet = writeNumLastSheet % NUM_BY_TIMES == 0 ? writeNumLastSheet / NUM_BY_TIMES : writeNumLastSheet / NUM_BY_TIMES + 1; 43 // 指定写入的文件 44 ExcelWriter excelWriter = EasyExcel.write(fileName, ExcelBean.class).build(); 45 for (int i = 0; i < sheetNum; i++) { 46 String sheetName = "sheet" + i; 47 WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetName).build(); 48 int writeNum = i == sheetNum - 1 ? writeNumPerLastSheet : writeNumPerSheet; // 每个sheet 写入的次数 49 int endEndNum = i == sheetNum - 1 ? date.size() : (i + 1) * NUM_PER_SHEET; // 每个sheet 最后一次写入的最后行数 50 for (int j = 0; j < writeNum; j++) { 51 long l = System.currentTimeMillis(); 52 int startNum = i * NUM_PER_SHEET + j * NUM_BY_TIMES; 53 int endNum = j == writeNum - 1 ? endEndNum : i * NUM_PER_SHEET + (j + 1) * NUM_BY_TIMES; 54 excelWriter.write(date.subList(startNum, endNum), writeSheet); 55 log.info("写入sheet={}，数据量={}-{}={}，耗时={}", sheetName, endNum, startNum, endNum - startNum, System.currentTimeMillis() - l); 56 } 57 } 58 // 需要放入 finally 中 59 if (excelWriter != null) { 60 excelWriter.finish(); 61 } 62 log.info("导出excel结束,总数据量={}，耗时={}ms", date.size(), System.currentTimeMillis() - startTime); 63 }
5.该类的全部代码
1 package mytest; 2 3 import bean.ExcelBean; 4 import com.alibaba.excel.EasyExcel; 5 import com.alibaba.excel.ExcelWriter; 6 import com.alibaba.excel.write.metadata.WriteSheet; 7 import com.google.common.collect.Lists; 8 import lombok.extern.slf4j.Slf4j; 9 import org.junit.Test; 10 11 import java.util.Date; 12 import java.util.List; 13 import java.util.UUID; 14 15 /** 16 * @author dengzeng 17 * @date 2021-11-06 上午 8:57 18 * 19 */ 20 @Slf4j 21 public class TestExcel { 22 public static final String FILE_NAME = "D:\\test_" + System.currentTimeMillis() + ".xlsx"; 23 // 每个 sheet 写入的数据 24 public static final int NUM_PER_SHEET = 300000; 25 // 每次向 sheet 中写入的数据（分页写入） 26 public static final int NUM_BY_TIMES = 50000; 27 28 /** 29 * 方法一：将数据写入到excel 30 * 直接调用api，适合小数据量 31 * 100W条数据33s 32 */ 33 @Test 34 public void writeExcelByApi() { 35 String fileName = FILE_NAME; 36 log.info("导出excel名称={}", fileName); 37 long startTime = System.currentTimeMillis(); 38 // 直接调用api 39 List<ExcelBean> date = getDate(); 40 EasyExcel.write(fileName, ExcelBean.class).sheet().doWrite(date); 41 log.info("导出excel结束,数据量={}，耗时={}ms", date.size(), System.currentTimeMillis() - startTime); 42 } 43 44 /** 45 * 方法二：导出多个sheet 46 * easyExcel 底层是 POI 实现的，POI 单个sheet 最多只能导出 1048576 行，超过该行数，会产生如下异常 47 * java.lang.IllegalArgumentException: Invalid row number (1048576) outside allowable range (0..1048575) 48 * <p> 49 * 11:57:55.541 [main] INFO mytest.TestExcel - 写入sheet=sheet0，数据量300000-0=300000，耗时=6055ms 50 * 11:57:59.701 [main] INFO mytest.TestExcel - 写入sheet=sheet1，数据量600000-300000=300000，耗时=4159ms 51 * 11:58:03.827 [main] INFO mytest.TestExcel - 写入sheet=sheet2，数据量900000-600000=300000，耗时=4126ms 52 * 11:58:05.193 [main] INFO mytest.TestExcel - 写入sheet=sheet3，数据量1000000-900000=100000，耗时=1366ms 53 * 11:58:17.418 [main] INFO mytest.TestExcel - 导出excel结束,总数据量=1000000，耗时=31297ms 54 */ 55 @Test 56 public void writeExcelByMulSheet() { 57 String fileName = FILE_NAME; 58 log.info("导出excel名称={}", fileName); 59 long startTime = System.currentTimeMillis(); 60 // 获取数据 61 List<ExcelBean> date = getDate(); 62 // 获取 sheet 的个数 63 int sheetNum = date.size() % NUM_PER_SHEET == 0 ? date.size() / NUM_PER_SHEET : date.size() / NUM_PER_SHEET + 1; 64 // 指定写入的文件 65 ExcelWriter excelWriter = EasyExcel.write(fileName, ExcelBean.class).build(); 66 for (int i = 0; i < sheetNum; i++) { 67 long l = System.currentTimeMillis(); 68 // 设置 sheet 的名字（sheet不能相同） 69 String sheetName = "sheet" + i; 70 WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetName).build(); 71 int startNum = i * NUM_PER_SHEET; 72 int endNum = i == sheetNum - 1 ? date.size() : (i + 1) * NUM_PER_SHEET; 73 excelWriter.write(date.subList(startNum, endNum), writeSheet); 74 log.info("写入sheet={}，数据量{}-{}={}，耗时={}ms", sheetName, endNum, startNum, endNum - startNum, System.currentTimeMillis() - l); 75 } 76 // 最好放在 finally中 77 excelWriter.finish(); 78 log.info("导出excel结束,总数据量={}，耗时={}ms", date.size(), System.currentTimeMillis() - startTime); 79 } 80 81 /** 82 * 方法三：同一个 Sheet，分批多次写入 83 * 当单次读取的 list 数据量过大，会产生 OOM 异常，所以需要分页读取并写入到 excel 84 * 11:55:01.590 [main] INFO mytest.TestExcel - 写入数量50000-0=50000，耗时=2227ms 85 * 11:55:02.429 [main] INFO mytest.TestExcel - 写入数量100000-50000=50000，耗时=838ms 86 * 11:55:03.188 [main] INFO mytest.TestExcel - 写入数量150000-100000=50000，耗时=759ms 87 * 11:55:03.951 [main] INFO mytest.TestExcel - 写入数量200000-150000=50000，耗时=762ms 88 * 11:55:04.708 [main] INFO mytest.TestExcel - 写入数量250000-200000=50000，耗时=757ms 89 * 11:55:05.471 [main] INFO mytest.TestExcel - 写入数量300000-250000=50000，耗时=762ms 90 * 11:55:06.172 [main] INFO mytest.TestExcel - 写入数量350000-300000=50000，耗时=701ms 91 * 11:55:06.921 [main] INFO mytest.TestExcel - 写入数量400000-350000=50000，耗时=749ms 92 * 11:55:07.688 [main] INFO mytest.TestExcel - 写入数量450000-400000=50000，耗时=767ms 93 * 11:55:08.437 [main] INFO mytest.TestExcel - 写入数量500000-450000=50000，耗时=749ms 94 * 11:55:09.141 [main] INFO mytest.TestExcel - 写入数量550000-500000=50000，耗时=704ms 95 * 11:55:09.899 [main] INFO mytest.TestExcel - 写入数量600000-550000=50000，耗时=758ms 96 * 11:55:10.597 [main] INFO mytest.TestExcel - 写入数量650000-600000=50000，耗时=698ms 97 * 11:55:11.353 [main] INFO mytest.TestExcel - 写入数量700000-650000=50000，耗时=756ms 98 * 11:55:12.055 [main] INFO mytest.TestExcel - 写入数量750000-700000=50000，耗时=701ms 99 * 11:55:12.820 [main] INFO mytest.TestExcel - 写入数量800000-750000=50000，耗时=765ms 100 * 11:55:13.576 [main] INFO mytest.TestExcel - 写入数量850000-800000=50000，耗时=756ms 101 * 11:55:14.287 [main] INFO mytest.TestExcel - 写入数量900000-850000=50000，耗时=711ms 102 * 11:55:15.055 [main] INFO mytest.TestExcel - 写入数量950000-900000=50000，耗时=768ms 103 * 11:55:15.773 [main] INFO mytest.TestExcel - 写入数量1000000-950000=50000，耗时=718ms 104 * 11:55:28.016 [main] INFO mytest.TestExcel - 导出excel结束,总数据量=1000000，耗时=31738ms 105 * <p> 106 * Process finished with exit code 0 107 */ 108 @Test 109 public void writeExcelByMulWrite() { 110 String fileName = FILE_NAME; 111 log.info("导出excel名称={}", fileName); 112 long startTime = System.currentTimeMillis(); 113 // 获取数据 114 List<ExcelBean> date = getDate(); 115 ExcelWriter excelWrite = EasyExcel.write(fileName, ExcelBean.class).build(); 116 WriteSheet writeSheet = EasyExcel.writerSheet("testSheet").build(); 117 // 计算需要写入的次数 118 int times = date.size() % NUM_BY_TIMES == 0 ? date.size() / NUM_BY_TIMES : date.size() / NUM_BY_TIMES + 1; 119 for (int i = 0; i < times; i++) { 120 long l = System.currentTimeMillis(); 121 int startNum = i * NUM_BY_TIMES; 122 int endNum = i == times - 1 ? date.size() : (i + 1) * NUM_BY_TIMES; 123 excelWrite.write(date.subList(startNum, endNum), writeSheet); 124 log.info("写入数量{}-{}={}，耗时={}ms", endNum, startNum, endNum - startNum, System.currentTimeMillis() - l); 125 } 126 // 需要放入 finally 中 127 if (excelWrite != null) { 128 excelWrite.finish(); 129 } 130 log.info("导出excel结束,总数据量={}，耗时={}ms", date.size(), System.currentTimeMillis() - startTime); 131 } 132 133 134 /** 135 * 方案四：写入多个 sheet，并且每个 sheet 写入多次数据（结合方案二、三） 136 * 数据量大，导致一个 sheet 存储不下；同时单次读入的数据量太大。可以采用这个方法 137 * 12:02:18.751 [main] INFO mytest.TestExcel - 写入sheet=sheet0，数据量=50000-0=50000，耗时=1558 138 * 12:02:19.542 [main] INFO mytest.TestExcel - 写入sheet=sheet0，数据量=100000-50000=50000，耗时=791 139 * 12:02:20.282 [main] INFO mytest.TestExcel - 写入sheet=sheet0，数据量=150000-100000=50000，耗时=740 140 * 12:02:21.037 [main] INFO mytest.TestExcel - 写入sheet=sheet0，数据量=200000-150000=50000，耗时=755 141 * 12:02:21.781 [main] INFO mytest.TestExcel - 写入sheet=sheet0，数据量=250000-200000=50000，耗时=744 142 * 12:02:22.524 [main] INFO mytest.TestExcel - 写入sheet=sheet0，数据量=300000-250000=50000，耗时=742 143 * 12:02:23.201 [main] INFO mytest.TestExcel - 写入sheet=sheet1，数据量=350000-300000=50000，耗时=677 144 * 12:02:23.852 [main] INFO mytest.TestExcel - 写入sheet=sheet1，数据量=400000-350000=50000，耗时=651 145 * 12:02:24.451 [main] INFO mytest.TestExcel - 写入sheet=sheet1，数据量=450000-400000=50000，耗时=599 146 * 12:02:25.100 [main] INFO mytest.TestExcel - 写入sheet=sheet1，数据量=500000-450000=50000，耗时=649 147 * 12:02:25.753 [main] INFO mytest.TestExcel - 写入sheet=sheet1，数据量=550000-500000=50000，耗时=653 148 * 12:02:26.350 [main] INFO mytest.TestExcel - 写入sheet=sheet1，数据量=600000-550000=50000，耗时=597 149 * 12:02:26.995 [main] INFO mytest.TestExcel - 写入sheet=sheet2，数据量=650000-600000=50000，耗时=645 150 * 12:02:27.588 [main] INFO mytest.TestExcel - 写入sheet=sheet2，数据量=700000-650000=50000，耗时=593 151 * 12:02:28.244 [main] INFO mytest.TestExcel - 写入sheet=sheet2，数据量=750000-700000=50000，耗时=656 152 * 12:02:28.893 [main] INFO mytest.TestExcel - 写入sheet=sheet2，数据量=800000-750000=50000，耗时=648 153 * 12:02:29.506 [main] INFO mytest.TestExcel - 写入sheet=sheet2，数据量=850000-800000=50000，耗时=613 154 * 12:02:30.163 [main] INFO mytest.TestExcel - 写入sheet=sheet2，数据量=900000-850000=50000，耗时=657 155 * 12:02:30.760 [main] INFO mytest.TestExcel - 写入sheet=sheet3，数据量=950000-900000=50000，耗时=597 156 * 12:02:31.419 [main] INFO mytest.TestExcel - 写入sheet=sheet3，数据量=1000000-950000=50000，耗时=659 157 * 12:02:43.235 [main] INFO mytest.TestExcel - 导出excel结束,总数据量=1000000，耗时=28818ms 158 * 159 * Process finished with exit code 0 160 */ 161 @Test 162 public void writeExcelByMulSheetAndWriteChange() { 163 String fileName = FILE_NAME; 164 log.info("导出excel名称={}", fileName); 165 long startTime = System.currentTimeMillis(); 166 // 获取数据 167 List<ExcelBean> date = getDate(); 168 // 获取 sheet 的个数 169 int sheetNum = date.size() % NUM_PER_SHEET == 0 ? date.size() / NUM_PER_SHEET : date.size() / NUM_PER_SHEET + 1; 170 // 获取每个sheet 写入的次数 171 int writeNumPerSheet = NUM_PER_SHEET % NUM_BY_TIMES == 0 ? NUM_PER_SHEET / NUM_BY_TIMES : NUM_PER_SHEET / NUM_BY_TIMES + 1; 172 // 最后一个 sheet 写入的数量 173 int writeNumLastSheet = date.size() - (sheetNum - 1) * NUM_PER_SHEET; 174 // 最后一个 sheet 写入的次数 175 int writeNumPerLastSheet = writeNumLastSheet % NUM_BY_TIMES == 0 ? writeNumLastSheet / NUM_BY_TIMES : writeNumLastSheet / NUM_BY_TIMES + 1; 176 // 指定写入的文件 177 ExcelWriter excelWriter = EasyExcel.write(fileName, ExcelBean.class).build(); 178 for (int i = 0; i < sheetNum; i++) { 179 String sheetName = "sheet" + i; 180 WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetName).build(); 181 int writeNum = i == sheetNum - 1 ? writeNumPerLastSheet : writeNumPerSheet; // 每个sheet 写入的次数 182 int endEndNum = i == sheetNum - 1 ? date.size() : (i + 1) * NUM_PER_SHEET; // 每个sheet 最后一次写入的最后行数 183 for (int j = 0; j < writeNum; j++) { 184 long l = System.currentTimeMillis(); 185 int startNum = i * NUM_PER_SHEET + j * NUM_BY_TIMES; 186 int endNum = j == writeNum - 1 ? endEndNum : i * NUM_PER_SHEET + (j + 1) * NUM_BY_TIMES; 187 excelWriter.write(date.subList(startNum, endNum), writeSheet); 188 log.info("写入sheet={}，数据量={}-{}={}，耗时={}", sheetName, endNum, startNum, endNum - startNum, System.currentTimeMillis() - l); 189 } 190 } 191 // 需要放入 finally 中 192 if (excelWriter != null) { 193 excelWriter.finish(); 194 } 195 log.info("导出excel结束,总数据量={}，耗时={}ms", date.size(), System.currentTimeMillis() - startTime); 196 } 197 198 199 /** 200 * 获取excel 导出的数据 201 * 202 * @return list 集合 203 */ 204 private List<ExcelBean> getDate() { 205 log.info("开始生成数据"); 206 Date date = new Date(); 207 long startTime = System.currentTimeMillis(); 208 List<ExcelBean> list = Lists.newArrayList(); 209 for (int i = 0; i < 1000000; i++) { 210 ExcelBean bean = new ExcelBean(); 211 bean.setId(UUID.randomUUID().toString()). 212 setName("隔壁老樊" + i). 213 setAddress("北京市朝阳区酒仙桥" + i + "路"). 214 setAge(i). 215 setNumber(i + 10000). 216 setHigh(1.234 * i). 217 setDistance(1.234 * i). 218 setStartTime(date). 219 setEndTime(date); 220 list.add(bean); 221 } 222 log.info("数据生成结束，数据量={}，耗时={}ms", list.size(), System.currentTimeMillis() - startTime); 223 return list; 224 } 225 }
