阿里開源（EasyExcel）

https://blog.csdn.net/qq_35206261/article/details/88579151

一. 簡介

         導出是后臺管理系統的常用功能，當數據量特別大的時候會內存溢出和卡頓頁面，曾經自己封裝過一個導出，POI百萬級大數據量EXCEL導出 采用了分批查詢數據來避免內存溢出和使用SXSSFWorkbook方式緩存數據到文件上以解決下載大文件EXCEL卡死頁面的問題。不過一是存在封裝不太友好使用不方便的問題，二是這些poi的操作方式仍然存在內存占用過大的問題,三是存在空循環和整除的時候數據有缺陷的問題，以及存在內存溢出的隱患。無意間查詢到阿里開源的EasyExcel框架，發現可以將解析的EXCEL的內存占用控制在KB級別，并且絕對不會內存溢出（內部實現待研究）,還有就是速度極快， 大概100W條記錄，十幾個字段， 只需要70秒即可完成下載。遂拋棄自己封裝的，轉戰研究阿里開源的EasyExcel. 不過 說實話，當時自己封裝的那個還是有些技術含量的，例如 外觀模式，模板方法模式，以及委托思想，組合思想，可以看看。

         EasyExcel的github地址是：https://github.com/alibaba/easyexcel 

二. 案例

2.1 POM依賴

        <!-- 阿里開源EXCEL -->

        <dependency>

            <groupId>com.alibaba</groupId>

            <artifactId>easyexcel</artifactId>

            <version>1.1.1</version>

        </dependency>

2.2 POJO對象

package com.authorization.privilege.excel;

import java.util.Date;

/**

 * @author qjwyss

 * @date 2019/3/15

 * @description

 */

public class User {

    private String uid;

    private String name;

    private Integer age;

    private Date birthday;

    public User() {

    }

    public User(String uid, String name, Integer age, Date birthday) {

        this.uid = uid;

        this.name = name;

        this.age = age;

        this.birthday = birthday;

    }

    public String getUid() {

        return uid;

    }

    public void setUid(String uid) {

        this.uid = uid;

    }

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.name = name;

    }

    public Integer getAge() {

        return age;

    }

    public void setAge(Integer age) {

        this.age = age;

    }

    public Date getBirthday() {

        return birthday;

    }

    public void setBirthday(Date birthday) {

        this.birthday = birthday;

    }

}

2.3 測試環境

2.3.1 數據量少的(20W以內吧)：一個SHEET一次查詢導出

    /**

     * 針對較少的記錄數(20W以內大概)可以調用該方法一次性查出然后寫入到EXCEL的一個SHEET中

     * 注意： 一次性查詢出來的記錄數量不宜過大，不會內存溢出即可。

     *

     * @throws IOException

     */

    @Test

    public void writeExcelOneSheetOnceWrite() throws IOException {

        // 生成EXCEL并指定輸出路徑

        OutputStream out = new FileOutputStream("E:\\temp\\withoutHead1.xlsx");

        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);

        // 設置SHEET

        Sheet sheet = new Sheet(1, 0);

        sheet.setSheetName("sheet1");

        // 設置標題

        Table table = new Table(1);

        List<List<String>> titles = new ArrayList<List<String>>();

        titles.add(Arrays.asList("用戶ID"));

        titles.add(Arrays.asList("名稱"));

        titles.add(Arrays.asList("年齡"));

        titles.add(Arrays.asList("生日"));

        table.setHead(titles);

        // 查詢數據導出即可 比如說一次性總共查詢出100條數據

        List<List<String>> userList = new ArrayList<>();

        for (int i = 0; i < 100; i++) {

            userList.add(Arrays.asList("ID_" + i, "小明" + i, String.valueOf(i), new Date().toString()));

        }

        writer.write0(userList, sheet, table);

        writer.finish();

    }

2.3.2 數據量適中（100W以內）： 一個SHEET分批查詢導出

    /**

     * 針對105W以內的記錄數可以調用該方法分多批次查出然后寫入到EXCEL的一個SHEET中

     * 注意：

     * 每次查詢出來的記錄數量不宜過大，根據內存大小設置合理的每次查詢記錄數，不會內存溢出即可。

     * 數據量不能超過一個SHEET存儲的最大數據量105W

     *

     * @throws IOException

     */

    @Test

    public void writeExcelOneSheetMoreWrite() throws IOException {

        // 生成EXCEL并指定輸出路徑

        OutputStream out = new FileOutputStream("E:\\temp\\withoutHead2.xlsx");

        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);

        // 設置SHEET

        Sheet sheet = new Sheet(1, 0);

        sheet.setSheetName("sheet1");

        // 設置標題

        Table table = new Table(1);

        List<List<String>> titles = new ArrayList<List<String>>();

        titles.add(Arrays.asList("用戶ID"));

        titles.add(Arrays.asList("名稱"));

        titles.add(Arrays.asList("年齡"));

        titles.add(Arrays.asList("生日"));

        table.setHead(titles);

        // 模擬分批查詢：總記錄數50條，每次查詢20條，  分三次查詢 最后一次查詢記錄數是10

        Integer totalRowCount = 50;

        Integer pageSize = 20;

        Integer writeCount = totalRowCount % pageSize == 0 ? (totalRowCount / pageSize) : (totalRowCount / pageSize + 1);

        // 注： 此處僅僅為了模擬數據，實用環境不需要將最后一次分開，合成一個即可， 參數為： currentPage = i+1;  pageSize = pageSize

        for (int i = 0; i < writeCount; i++) {

            // 前兩次查詢 每次查20條數據

            if (i < writeCount - 1) {

                List<List<String>> userList = new ArrayList<>();

                for (int j = 0; j < pageSize; j++) {

                    userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));

                }

                writer.write0(userList, sheet, table);

            } else if (i == writeCount - 1) {

                // 最后一次查詢 查多余的10條記錄

                List<List<String>> userList = new ArrayList<>();

                Integer lastWriteRowCount = totalRowCount - (writeCount - 1) * pageSize;

                for (int j = 0; j < lastWriteRowCount; j++) {

                    userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));

                }

                writer.write0(userList, sheet, table);

            }

        }

        writer.finish();

    }

2.3.3 數據量很大（幾百萬都行）： 多個SHEET分批查詢導出 

    /**

     * 針對幾百萬的記錄數可以調用該方法分多批次查出然后寫入到EXCEL的多個SHEET中

     * 注意：

     * perSheetRowCount % pageSize要能整除  為了簡潔，非整除這塊不做處理

     * 每次查詢出來的記錄數量不宜過大，根據內存大小設置合理的每次查詢記錄數，不會內存溢出即可。

     *

     * @throws IOException

     */

    @Test

    public void writeExcelMoreSheetMoreWrite() throws IOException {

        // 生成EXCEL并指定輸出路徑

        OutputStream out = new FileOutputStream("E:\\temp\\withoutHead3.xlsx");

        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);

        // 設置SHEET名稱

        String sheetName = "測試SHEET";

        // 設置標題

        Table table = new Table(1);

        List<List<String>> titles = new ArrayList<List<String>>();

        titles.add(Arrays.asList("用戶ID"));

        titles.add(Arrays.asList("名稱"));

        titles.add(Arrays.asList("年齡"));

        titles.add(Arrays.asList("生日"));

        table.setHead(titles);

        // 模擬分批查詢：總記錄數250條，每個SHEET存100條，每次查詢20條  則生成3個SHEET，前倆個SHEET查詢次數為5， 最后一個SHEET查詢次數為3 最后一次寫的記錄數是10

        // 注：該版本為了較少數據判斷的復雜度，暫時perSheetRowCount要能夠整除pageSize， 不去做過多處理  合理分配查詢數據量大小不會內存溢出即可。

        Integer totalRowCount = 250;

        Integer perSheetRowCount = 100;

        Integer pageSize = 20;

        Integer sheetCount = totalRowCount % perSheetRowCount == 0 ? (totalRowCount / perSheetRowCount) : (totalRowCount / perSheetRowCount + 1);

        Integer previousSheetWriteCount = perSheetRowCount / pageSize;

        Integer lastSheetWriteCount = totalRowCount % perSheetRowCount == 0 ?

                previousSheetWriteCount :

                (totalRowCount % perSheetRowCount % pageSize == 0 ? totalRowCount % perSheetRowCount / pageSize : (totalRowCount % perSheetRowCount / pageSize + 1));

        for (int i = 0; i < sheetCount; i++) {

            // 創建SHEET

            Sheet sheet = new Sheet(i, 0);

            sheet.setSheetName(sheetName + i);

            if (i < sheetCount - 1) {

                // 前2個SHEET, 每個SHEET查5次 每次查20條 每個SHEET寫滿100行  2個SHEET合計200行  實用環境：參數： currentPage: j+1 + previousSheetWriteCount*i, pageSize: pageSize

                for (int j = 0; j < previousSheetWriteCount; j++) {

                    List<List<String>> userList = new ArrayList<>();

                    for (int k = 0; k < 20; k++) {

                        userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));

                    }

                    writer.write0(userList, sheet, table);

                }

            } else if (i == sheetCount - 1) {

                // 最后一個SHEET 實用環境不需要將最后一次分開，合成一個即可， 參數為： currentPage = i+1;  pageSize = pageSize

                for (int j = 0; j < lastSheetWriteCount; j++) {

                    // 前倆次查詢 每次查詢20條

                    if (j < lastSheetWriteCount - 1) {

                        List<List<String>> userList = new ArrayList<>();

                        for (int k = 0; k < 20; k++) {

                            userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));

                        }

                        writer.write0(userList, sheet, table);

                    } else if (j == lastSheetWriteCount - 1) {

                        // 最后一次查詢 將剩余的10條查詢出來

                        List<List<String>> userList = new ArrayList<>();

                        Integer lastWriteRowCount = totalRowCount - (sheetCount - 1) * perSheetRowCount - (lastSheetWriteCount - 1) * pageSize;

                        for (int k = 0; k < lastWriteRowCount; k++) {

                            userList.add(Arrays.asList("ID_" + Math.random(), "小明1", String.valueOf(Math.random()), new Date().toString()));

                        }

                        writer.write0(userList, sheet, table);

                    }

                }

            }

        }

        writer.finish();

    }

2.4 生產環境

2.4.0 Excel常量類

package com.authorization.privilege.constant;

/**

 * @author qjwyss

 * @date 2019/3/18

 * @description EXCEL常量類

 */

public class ExcelConstant {

    /**

     * 每個sheet存儲的記錄數 100W

     */

    public static final Integer PER_SHEET_ROW_COUNT = 1000000;

    /**

     * 每次向EXCEL寫入的記錄數(查詢每頁數據大小) 20W

     */

    public static final Integer PER_WRITE_ROW_COUNT = 200000;

}

注： 為了書寫方便，此處倆個必須要整除，可以省去很多不必要的判斷。  另外如果自己測試，可以改為100,20。 

2.4.1 數據量少的(20W以內吧)：一個SHEET一次查詢導出 

    @Override

    public ResultVO<Void> exportSysSystemExcel(SysSystemVO sysSystemVO, HttpServletResponse response) throws Exception {

        ServletOutputStream out = null;

        try {

            out = response.getOutputStream();

            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);

            // 設置EXCEL名稱

            String fileName = new String(("SystemExcel").getBytes(), "UTF-8");

            // 設置SHEET名稱

            Sheet sheet = new Sheet(1, 0);

            sheet.setSheetName("系統列表sheet1");

            // 設置標題

            Table table = new Table(1);

            List<List<String>> titles = new ArrayList<List<String>>();

            titles.add(Arrays.asList("系統名稱"));

            titles.add(Arrays.asList("系統標識"));

            titles.add(Arrays.asList("描述"));

            titles.add(Arrays.asList("狀態"));

            titles.add(Arrays.asList("創建人"));

            titles.add(Arrays.asList("創建時間"));

            table.setHead(titles);

            // 查數據寫EXCEL

            List<List<String>> dataList = new ArrayList<>();

            List<SysSystemVO> sysSystemVOList = this.sysSystemReadMapper.selectSysSystemVOList(sysSystemVO);

            if (!CollectionUtils.isEmpty(sysSystemVOList)) {

                sysSystemVOList.forEach(eachSysSystemVO -> {

                    dataList.add(Arrays.asList(

                            eachSysSystemVO.getSystemName(),

                            eachSysSystemVO.getSystemKey(),

                            eachSysSystemVO.getDescription(),

                            eachSysSystemVO.getState().toString(),

                            eachSysSystemVO.getCreateUid(),

                            eachSysSystemVO.getCreateTime().toString()

                    ));

                });

            }

            writer.write0(dataList, sheet, table);

            // 下載EXCEL

            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

            response.setContentType("multipart/form-data");

            response.setCharacterEncoding("utf-8");

            writer.finish();

            out.flush();

        } finally {

            if (out != null) {

                try {

                    out.close();

                } catch (Exception e) {

                    e.printStackTrace();

                }

            }

        }

        return ResultVO.getSuccess("導出系統列表EXCEL成功");

    }

2.4.2 數據量適中（100W以內）： 一個SHEET分批查詢導出 

    @Override

    public ResultVO<Void> exportSysSystemExcel(SysSystemVO sysSystemVO, HttpServletResponse response) throws Exception {

        ServletOutputStream out = null;

        try {

            out = response.getOutputStream();

            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);

            // 設置EXCEL名稱

            String fileName = new String(("SystemExcel").getBytes(), "UTF-8");

            // 設置SHEET名稱

            Sheet sheet = new Sheet(1, 0);

            sheet.setSheetName("系統列表sheet1");

            // 設置標題

            Table table = new Table(1);

            List<List<String>> titles = new ArrayList<List<String>>();

            titles.add(Arrays.asList("系統名稱"));

            titles.add(Arrays.asList("系統標識"));

            titles.add(Arrays.asList("描述"));

            titles.add(Arrays.asList("狀態"));

            titles.add(Arrays.asList("創建人"));

            titles.add(Arrays.asList("創建時間"));

            table.setHead(titles);

            // 查詢總數并 【封裝相關變量 這塊直接拷貝就行 不要改動】

            Integer totalRowCount = this.sysSystemReadMapper.selectCountSysSystemVOList(sysSystemVO);

            Integer pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;

            Integer writeCount = totalRowCount % pageSize == 0 ? (totalRowCount / pageSize) : (totalRowCount / pageSize + 1);

            // 寫數據 這個i的最大值直接拷貝就行了 不要改

            for (int i = 0; i < writeCount; i++) {

                List<List<String>> dataList = new ArrayList<>();

                // 此處查詢并封裝數據即可 currentPage, pageSize這個變量封裝好的 不要改動

                PageHelper.startPage(i + 1, pageSize);

                List<SysSystemVO> sysSystemVOList = this.sysSystemReadMapper.selectSysSystemVOList(sysSystemVO);

                if (!CollectionUtils.isEmpty(sysSystemVOList)) {

                    sysSystemVOList.forEach(eachSysSystemVO -> {

                        dataList.add(Arrays.asList(

                                eachSysSystemVO.getSystemName(),

                                eachSysSystemVO.getSystemKey(),

                                eachSysSystemVO.getDescription(),

                                eachSysSystemVO.getState().toString(),

                                eachSysSystemVO.getCreateUid(),

                                eachSysSystemVO.getCreateTime().toString()

                        ));

                    });

                }

                writer.write0(dataList, sheet, table);

            }

            // 下載EXCEL

            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

            response.setContentType("multipart/form-data");

            response.setCharacterEncoding("utf-8");

            writer.finish();

            out.flush();

        } finally {

            if (out != null) {

                try {

                    out.close();

                } catch (Exception e) {

                    e.printStackTrace();

                }

            }

        }

        return ResultVO.getSuccess("導出系統列表EXCEL成功");

    }

2.4.3 數據里很大（幾百萬都行）： 多個SHEET分批查詢導出 

    @Override

    public ResultVO<Void> exportSysSystemExcel(SysSystemVO sysSystemVO, HttpServletResponse response) throws Exception {

        ServletOutputStream out = null;

        try {

            out = response.getOutputStream();

            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);

            // 設置EXCEL名稱

            String fileName = new String(("SystemExcel").getBytes(), "UTF-8");

            // 設置SHEET名稱

            String sheetName = "系統列表sheet";

            // 設置標題

            Table table = new Table(1);

            List<List<String>> titles = new ArrayList<List<String>>();

            titles.add(Arrays.asList("系統名稱"));

            titles.add(Arrays.asList("系統標識"));

            titles.add(Arrays.asList("描述"));

            titles.add(Arrays.asList("狀態"));

            titles.add(Arrays.asList("創建人"));

            titles.add(Arrays.asList("創建時間"));

            table.setHead(titles);

            // 查詢總數并封裝相關變量(這塊直接拷貝就行了不要改)

            Integer totalRowCount = this.sysSystemReadMapper.selectCountSysSystemVOList(sysSystemVO);

            Integer perSheetRowCount = ExcelConstant.PER_SHEET_ROW_COUNT;

            Integer pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;

            Integer sheetCount = totalRowCount % perSheetRowCount == 0 ? (totalRowCount / perSheetRowCount) : (totalRowCount / perSheetRowCount + 1);

            Integer previousSheetWriteCount = perSheetRowCount / pageSize;

            Integer lastSheetWriteCount = totalRowCount % perSheetRowCount == 0 ?

                    previousSheetWriteCount :

                    (totalRowCount % perSheetRowCount % pageSize == 0 ? totalRowCount % perSheetRowCount / pageSize : (totalRowCount % perSheetRowCount / pageSize + 1));

            for (int i = 0; i < sheetCount; i++) {

                // 創建SHEET

                Sheet sheet = new Sheet(i, 0);

                sheet.setSheetName(sheetName + i);

                // 寫數據 這個j的最大值判斷直接拷貝就行了，不要改動

                for (int j = 0; j < (i != sheetCount - 1 ? previousSheetWriteCount : lastSheetWriteCount); j++) {

                    List<List<String>> dataList = new ArrayList<>();

                    // 此處查詢并封裝數據即可 currentPage, pageSize這倆個變量封裝好的 不要改動

                    PageHelper.startPage(j + 1 + previousSheetWriteCount * i, pageSize);

                    List<SysSystemVO> sysSystemVOList = this.sysSystemReadMapper.selectSysSystemVOList(sysSystemVO);

                    if (!CollectionUtils.isEmpty(sysSystemVOList)) {

                        sysSystemVOList.forEach(eachSysSystemVO -> {

                            dataList.add(Arrays.asList(

                                    eachSysSystemVO.getSystemName(),

                                    eachSysSystemVO.getSystemKey(),

                                    eachSysSystemVO.getDescription(),

                                    eachSysSystemVO.getState().toString(),

                                    eachSysSystemVO.getCreateUid(),

                                    eachSysSystemVO.getCreateTime().toString()

                            ));

                        });

                    }

                    writer.write0(dataList, sheet, table);

                }

            }

            // 下載EXCEL

            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

            response.setContentType("multipart/form-data");

            response.setCharacterEncoding("utf-8");

            writer.finish();

            out.flush();

        } finally {

            if (out != null) {

                try {

                    out.close();

                } catch (Exception e) {

                    e.printStackTrace();

                }

            }

        }

        return ResultVO.getSuccess("導出系統列表EXCEL成功");

    }

三、總結

            造的假數據，100W條記錄，18個字段，測試導出是70s。  在實際上產環境使用的時候，具體的還是要看自己寫的sql的性能。 sql性能快的話，會很快。 有一點推薦一下： 在做分頁的時候使用單表查詢， 對于所需要處理的外鍵對應的冗余字段，在外面一次性查出來放到map里面(推薦使用@MapKey注解)，然后遍歷list的時候根據外鍵從map中獲取對應的名稱。一個宗旨：少發查詢sql, 才能更快的導出。   

            題外話： 如果數據量過大，在使用count(1)查詢總數的時候會很慢，可以通過調整mysql的緩沖池參數來加快查詢，請參見博主的另一篇博文MYSQL單表數據量過大查詢過慢配置優化innodb_buffer_pool_size。  還有就是遇到了一個問題，使用pagehelper的時候，數據量大的時候，limit 0,20W;  limit 20W,40W,  limit 40W,60W, limit 60W,80W 查詢有的時候會很快，有的時候會很慢，待研究。

--------------------- 

作者：請叫我猿叔叔 

來源：CSDN 

原文：https://blog.csdn.net/qq_35206261/article/details/88579151 

版權聲明：本文為博主原創文章，轉載請附上博文鏈接！