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

常用的excel导出方案,详情见Spring Boot 入门(十二):报表导出,对比poi、jxl和esayExcel的效率,其中jxl、esayEscel 底层都是基于 poi,它们仅仅是对 poi 进行了封装,使导出 excel 的代码更加简洁(封装 poi,导出 excel 的工具类有很多,jxl 和 esayExcel 使用的比较多)。所以,如果遇到 excel 导出效率低下,直接基于 poi 导出 Excel,效率肯定是最高的,只是代码比较繁琐与杂乱。

我主要是基于 esayExcel (目前使用比较多),对其 api 进行衍生与应用,解决导出大量 Excel 导致的 OOM,或者数据超界异常:文章来源地址https://www.yii666.com/article/764186.html网址:yii666.com

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

应用是基于100W条数据进行的测试,数据的获取代码如下网址:yii666.com<

 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),解决方案如下:文章来源地址:https://www.yii666.com/article/764186.html

方案一:查看 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>

常量的定义文章地址https://www.yii666.com/article/764186.html

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.该类的全部代码

记一次 Java 导出大批量 Excel 优化记一次 Java 导出大批量 Excel 优化
  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 }

版权声明:本文内容来源于网络,版权归原作者所有,此博客不拥有其著作权,亦不承担相应法律责任。文本页已经标记具体来源原文地址,请点击原文查看来源网址,站内文章以及资源内容站长不承诺其正确性,如侵犯了您的权益,请联系站长如有侵权请联系站长,将立刻删除

记一次 Java 导出大批量 Excel 优化-相关文章

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

  2. 史上最全的excel读写技术分享

  3. SpringBoot整合easyexcel实现Excel的导入与导出

    导出在一般不管大的或者小的系统中,各家的产品都一样,闲的无聊的时候都喜欢让我们这些程序员导出一些数据出来供他观赏,非说这是必须需求,非做不可,那么我们就只能苦逼的哼哧哼哧的写bug喽。之前使用POI导出excel需要自己先去创建excel文件,还要创建sheet,写表头

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信图片_20190322181744_03.jpg

微信扫一扫打赏

请作者喝杯咖啡吧~

支付宝扫一扫领取红包,优惠每天领

二维码1

zhifubaohongbao.png

二维码2

zhifubaohongbao2.png