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

常用的excel导出方案，详情见Spring Boot 入门（十二）：报表导出，对比poi、jxl和esayExcel的效率，其中jxl、esayEscel 底层都是基于 poi，它们仅仅是对 poi 进行了封装，使导出 excel 的代码更加简洁（封装 poi，导出 excel 的工具类有很多，jxl 和 esayExcel 使用的比较多）。所以，如果遇到 excel 导出效率低下，直接基于 poi 导出 Excel，效率肯定是最高的，只是代码比较繁琐与杂乱。文章来源地址:https://www.yii666.com/article/764186.html

我主要是基于 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 的应用，代码如下文章来源地址https://www.yii666.com/article/764186.html

 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文章地址https://www.yii666.com/article/764186.html网址:yii666.com<

将数据写入到不同的 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，具体代码如下网址:yii666.com

 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 }

小戴BOTAOY演示博客

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

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

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

支付宝扫一扫打赏

微信扫一扫打赏

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

二维码1

二维码2