EasyExcel动态分多个sheet页导出千万级数据

今天药忘吃喽~ 2022-10-27 12:16 1420阅读 0赞

## ##

### 目录 ###

*  [前言][Link 1]
 *  [使用背景][Link 2]
 *  [参考官方文档][Link 3]
 *  [代码部分][Link 4]
 *  [小结][Link 5]

# 前言 #

请等米下锅的同学直接阅读**代码部分**。

# 使用背景 #

最近项目组小伙伴们在开发使用`Apache`的`Poi`导出大批量数据时，总是出现内存溢出的情况。并且在生产环境，如果多个用户都导大批量数据报表时，服务器也很容易宕机。  
虽然说`Poi`有`SXSSFWorkbook`这个类可以帮助我们导出较大批量的数据。其原理是用硬盘空间换内存这个样子。但是Excel2007最大一个sheet页也就支持1048576行。如果超出这个行数就需要去动态分多个sheet页去写入。由于我们是使用的模板去写入数据的，所以我们需要动态的克隆sheet页。之前只用过`XSSFWorkbook`类的`cloneSheet`方法。在阅读`SXSSFWorkbook`类的源码后，嗯！没实现，好的，算了算了。这也就是使用alibaba的开源项目`EasyExcel`的由来。`EasyExcel`重写了`Poi`对07版Excel的解析，能够原本一个3M的excel用POI sax依然需要100M左右内存降低到几M，并且再大的excel不会出现内存溢出。所以有摩托车不用，非要蹬自行车吗？那不是傻么。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTA5NjA5_size_16_color_FFFFFF_t_70_pic_center]  
这里也就不介绍`EasyExcel`了，没有什么比官网更详细了。请各位看官去官网详细阅读。

# 参考官方文档 #

*  [EasyExcel官方文档][EasyExcel]
 *  [EasyExcel的github地址][EasyExcel_github]

# 代码部分 #

我们项目是使用Maven搭建的Springboot项目。

*  Maven依赖：

<dependency>
           <groupId>com.alibaba</groupId>
           <artifactId>easyexcel</artifactId>
           <version>2.2.6</version>
     </dependency>

*  工具类

1.  EasyExcelUtil

package com.sinosoft.service.util;
    
    import com.alibaba.excel.EasyExcel;
    import com.alibaba.excel.ExcelWriter;
    import com.alibaba.excel.support.ExcelTypeEnum;
    import com.alibaba.excel.write.builder.ExcelWriterBuilder;
    import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
    import com.alibaba.excel.write.metadata.WriteSheet;
    import com.alibaba.excel.write.metadata.style.WriteCellStyle;
    import com.alibaba.excel.write.metadata.style.WriteFont;
    import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
    import com.sinosoft.web.rest.util.SplitList;
    import org.apache.poi.ss.usermodel.BorderStyle;
    import org.apache.poi.ss.usermodel.HorizontalAlignment;
    import org.apache.poi.ss.usermodel.IndexedColors;
    import org.apache.poi.ss.usermodel.VerticalAlignment;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.*;
    import java.net.URLEncoder;
    import java.util.Date;
    import java.util.List;
    
    /**
     * @author QY
     * @since 2020-08-03
     * @description 使用easyExcel来导出xlsx的工具类
     */
    public class EasyExcelUtil {
    
        private static final Logger log = LoggerFactory.getLogger(EasyExcelUtil.class);
    
        private static final int MAXROWS = 1000000;
    
    
        /**
         * 获取默认表头内容的样式
         * @return
         */
        private static HorizontalCellStyleStrategy getDefaultHorizontalCellStyleStrategy(){
            /** 表头样式 **/
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            // 背景色（浅灰色）
            // 可以参考：https://www.cnblogs.com/vofill/p/11230387.html
            headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            // 字体大小
            WriteFont headWriteFont = new WriteFont();
            headWriteFont.setFontHeightInPoints((short) 10);
            headWriteCellStyle.setWriteFont(headWriteFont);
            //设置表头居中对齐
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            /** 内容样式 **/
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            // 内容字体样式（名称、大小）
            WriteFont contentWriteFont = new WriteFont();
            contentWriteFont.setFontName("宋体");
            contentWriteFont.setFontHeightInPoints((short) 10);
            contentWriteCellStyle.setWriteFont(contentWriteFont);
            //设置内容垂直居中对齐
            contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置内容水平居中对齐
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            //设置边框样式
            contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
            contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
            contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
            contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
            // 头样式与内容样式合并
            return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        }
    
        /**
         * 导出
         * @param response
         * @param data
         * @param fileName
         * @param sheetName
         * @param clazz
         * @throws Exception
         */
        public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class clazz) throws Exception {
            long exportStartTime = System.currentTimeMillis();
            log.info("报表导出Size: "+data.size()+"条。");
            EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(getDefaultHorizontalCellStyleStrategy()).doWrite(data);
            System.out.println("报表导出结束时间:"+ new Date()+";写入耗时: "+(System.currentTimeMillis()-exportStartTime)+"ms" );
        }
    
        /**
         * @author QiuYu
         * @createDate 2020-11-16
         * @param response
         * @param data  查询结果
         * @param fileName 导出文件名称
         * @param clazz 映射实体class类
         * @param <T>  查询结果类型
         * @throws Exception
         */
        public static<T> void writeExcel(HttpServletResponse response, List<T> data, String fileName, Class clazz) throws Exception {
            long exportStartTime = System.currentTimeMillis();
            log.info("报表导出Size: "+data.size()+"条。");
    
            List<List<T>> lists = SplitList.splitList(data,MAXROWS); // 分割的集合
    
            OutputStream out = getOutputStream(fileName, response);
            ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(out, clazz).excelType(ExcelTypeEnum.XLSX).registerWriteHandler(getDefaultHorizontalCellStyleStrategy());
            ExcelWriter excelWriter = excelWriterBuilder.build();
            ExcelWriterSheetBuilder excelWriterSheetBuilder;
            WriteSheet writeSheet;
            for (int i =1;i<=lists.size();i++){
                excelWriterSheetBuilder = new ExcelWriterSheetBuilder(excelWriter);
                excelWriterSheetBuilder.sheetNo(i);
                excelWriterSheetBuilder.sheetName("sheet"+i);
                writeSheet = excelWriterSheetBuilder.build();
                excelWriter.write(lists.get(i-1),writeSheet);
            }
            out.flush();
            excelWriter.finish();
            out.close();
            System.out.println("报表导出结束时间:"+ new Date()+";写入耗时: "+(System.currentTimeMillis()-exportStartTime)+"ms" );
        }
    
    
        private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
            fileName = URLEncoder.encode(fileName, "UTF-8");
            //  response.setContentType("application/vnd.ms-excel"); // .xls
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); // .xlsx
            response.setCharacterEncoding("utf8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
            return response.getOutputStream();
        }
    
    
    }

1.  SplitList

package com.sinosoft.web.rest.util;
    
    
    
    import java.util.ArrayList;
    import java.util.Arrays;
    import java.util.List;
    
    public class SplitList {
    
        /**
         * update qy 2020-09-25
         * @param list 待切割集合
         * @param len 集合按照多大size来切割
         * @param <T>
         * @return
         */
        public static<T> List<List<T>> splitList(List<T> list, int len) {
            if (list == null || list.size() == 0 || len < 1) {
                return null;
            }
            List<List<T>> result = new ArrayList<List<T>>();
            int size = list.size();
            int count = (size + len - 1) / len;
    
            for (int i = 0; i < count; i++) {
                List<T> subList = list.subList(i * len, ((i + 1) * len > size ? size : len * (i + 1)));
                result.add(subList);
            }
            return result;
        }
    
    
    
        /**
         * @version add QY 2020-09-25
         * @description 集合平均分组
         * @param source 源集合
         * @param n      分成n个集合
         * @param <T>    集合类型
         * @return
         */
        public static <T> List<List<T>> groupList(List<T> source, int n) {
            if (source == null || source.size() == 0 || n < 1) {
                return null;
            }
            if (source.size() < n) {
                return Arrays.asList(source);
            }
            List<List<T>> result = new ArrayList<List<T>>();
            int number = source.size() / n;
            int remaider = source.size() % n;
            int offset = 0; // 偏移量，每有一个余数分配，就要往右偏移一位
            for (int i = 0; i < n; i++) {
                List<T> list1 = null;
                if (remaider > 0) {
                    list1 = source.subList(i * number + offset , (i + 1) * number + offset + 1);
                    remaider--;
                    offset++;
                } else {
                    list1 = source.subList(i * number + offset, (i + 1) * number + offset);
                }
                result.add(list1);
            }
    
            return result;
        }
    
    
    }

*  Resource/Controller 层

// EasyExcel样例
        @PostMapping("/test/printByEasyExcel")
        public void printByEasyExcel(@RequestBody Map<String,Object> map, HttpServletResponse response) throws IOException {
           easyExcelService.printByEasyExcel(map,response);
        }

*  Service层

public void printByEasyExcel(Map<String,Object> map, HttpServletResponse response) throws IOException {
            //查询要导出的明细信息
            List<?> modelList = easyExcelQueryService.queryResult(map);
            try {
                EasyExcelUtil.writeExcel(response, modelList, "导出的报表名称", PrintModel.class);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

*  打印映射的实体类

import com.alibaba.excel.annotation.ExcelProperty;
    import com.alibaba.excel.annotation.write.style.ColumnWidth;
    import com.alibaba.excel.annotation.write.style.ContentRowHeight;
    import com.alibaba.excel.annotation.write.style.HeadRowHeight;
    import lombok.*;
    
    import java.io.Serializable;
    
    
    @NoArgsConstructor
    @AllArgsConstructor
    @ContentRowHeight(15)
    @HeadRowHeight(17)
    public class PrintModel implements Serializable {
    
        @ColumnWidth(10)
        @ExcelProperty(value = "月份", index = 0)
        private String yearMonth;
    
        @ColumnWidth(10)
        @ExcelProperty(value = "销售渠道", index = 1)
        private String branchType;
    
        @ColumnWidth(15)
        @ExcelProperty(value = "保单号码", index = 2)
        private String mainPolNo;
    
        @ColumnWidth(15)
        @ExcelProperty(value = "承保日期", index = 3)
        private String signDate;
    
        @ColumnWidth(15)
        @ExcelProperty(value = "发生日期", index = 4)
        private String getPolDate;
    
        @ColumnWidth(15)
        @ExcelProperty(value = "代理人编号", index = 5)
        private String date;
    
        @ColumnWidth(10)
        @ExcelProperty(value = "代理人姓名", index = 6)
        private String doubleData;
    
        @ColumnWidth(15)
        @ExcelProperty(value = "保险产品代码", index = 7)
        private String riskCode;
    
        @ColumnWidth(10)
        @ExcelProperty(value = "金额", index = 8)
        private String fyc;
    
        @ColumnWidth(15)
        @ExcelProperty(value = "回机日期", index = 9)
        private String tMakeDate;
    
        @ColumnWidth(10)
        @ExcelProperty(value = "计佣年月", index = 10)
        private String statWageNo;
    
        @ColumnWidth(8)
        @ExcelProperty(value = "账龄日", index = 11)
        private String ageDay;
    
        @ColumnWidth(10)
        @ExcelProperty(value = "分公司代码", index = 12)
        private String branchName;
    
        public String getYearMonth() {
            return yearMonth;
        }
    
        public void setYearMonth(String yearMonth) {
            this.yearMonth = yearMonth;
        }
    
        public String getBranchType() {
            return branchType;
        }
    
        public void setBranchType(String branchType) {
            this.branchType = branchType;
        }
    
        public String getMainPolNo() {
            return mainPolNo;
        }
    
        public void setMainPolNo(String mainPolNo) {
            this.mainPolNo = mainPolNo;
        }
    
        public String getSignDate() {
            return signDate;
        }
    
        public void setSignDate(String signDate) {
            this.signDate = signDate;
        }
    
        public String getGetPolDate() {
            return getPolDate;
        }
    
        public void setGetPolDate(String getPolDate) {
            this.getPolDate = getPolDate;
        }
    
        public String getDate() {
            return date;
        }
    
        public void setDate(String date) {
            this.date = date;
        }
    
        public String getDoubleData() {
            return doubleData;
        }
    
        public void setDoubleData(String doubleData) {
            this.doubleData = doubleData;
        }
    
        public String getRiskCode() {
            return riskCode;
        }
    
        public void setRiskCode(String riskCode) {
            this.riskCode = riskCode;
        }
    
        public String getFyc() {
            return fyc;
        }
    
        public void setFyc(String fyc) {
            this.fyc = fyc;
        }
    
        public String gettMakeDate() {
            return tMakeDate;
        }
    
        public void settMakeDate(String tMakeDate) {
            this.tMakeDate = tMakeDate;
        }
    
        public String getStatWageNo() {
            return statWageNo;
        }
    
        public void setStatWageNo(String statWageNo) {
            this.statWageNo = statWageNo;
        }
    
        public String getAgeDay() {
            return ageDay;
        }
    
        public void setAgeDay(String ageDay) {
            this.ageDay = ageDay;
        }
    
        public String getBranchName() {
            return branchName;
        }
    
        public void setBranchName(String branchName) {
            this.branchName = branchName;
        }
    }

# 小结 #

循环写了2500000数据，然后测试导出，导出三个Sheet页，写入耗时: 171873ms。报表 81.1 MB

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTA5NjA5_size_16_color_FFFFFF_t_70_pic_center 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTA5NjA5_size_16_color_FFFFFF_t_70_pic_center 2]  
所以建议业务上涉及大批量数据导出时，表头没必要弄得花里胡哨，效率为主。尽可能的使用`EasyExcel`来导出数据，使用Poi很容易在并发时导致内存溢出。当然，Poi在导出某些花里胡哨的报表，小批量数据导出时，还是很香的。  
再啰嗦一句，像数据量很大很大的报表，在导出时，可以把内容的样式去了。Style非常占用空间，工具类`EasyExcelUtil`代码做出相应调整即可。

[Link 1]: https://blog.csdn.net/m0_46109609/article/details/109721783?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-12.control&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-12.control#_1
[Link 2]: https://blog.csdn.net/m0_46109609/article/details/109721783?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-12.control&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-12.control#_3
[Link 3]: https://blog.csdn.net/m0_46109609/article/details/109721783?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-12.control&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-12.control#_8
[Link 4]: https://blog.csdn.net/m0_46109609/article/details/109721783?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-12.control&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-12.control#_12
[Link 5]: https://blog.csdn.net/m0_46109609/article/details/109721783?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-12.control&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-12.control#_444
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTA5NjA5_size_16_color_FFFFFF_t_70_pic_center]: /images/20221024/a3db367905ba4efcb6d3623bcba45f0e.png
[EasyExcel]: https://www.yuque.com/easyexcel/doc/easyexcel
[EasyExcel_github]: https://github.com/alibaba/easyexcel
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTA5NjA5_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221024/12103ac326034b1f93b98eabc282b166.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTA5NjA5_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221024/298ca84bf0d945c6a77b4349a794b678.png