记一次 Java 导出大批量 Excel 优化

java.lang.IllegalArgumentException: Invalid row number (1048576) outside allowable range (0..1048575)

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 }

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>

<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 /**
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 }

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)

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 }

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 }

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 }