分享

阿里开源(EasyExcel)

 柳7it48xq2tc7q 2019-03-22

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 

版权声明:本文为博主原创文章,转载请附上博文链接!

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多