記一次 Java 匯出大批量 Excel 優化

光頭才能強發表於2021-11-07
JavaExcel優化

常用的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.該類的全部程式碼

記一次 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 }
View Code

 

相關文章