博主精品——动态Excel导出（兼容单行及复杂表格数据的自动合并单元格，优雅~）

DBC 759 0
温馨提示

目前为基础使用版本，未来可以使用从设计模式方面改造一下，当前只要能够使用即可~
需求：我们只需要修改数据库中的数据，即可实现表格的动态导出，不需要如果有一点点的变化都需要去修改代码，经过公司定制化之后，基本可以实现线上的动态Excel导出~

本文章计划完成的功能有：
1.动态单行数据导出——已完成[aru_50]
2.动态表头修改（不是根据数据变化）——已完成[aru_50]
3.复杂数据导出可自动合并单元格数据——已完成[aru_50]
4.兼容百万数据导出——已完成[aru_50]
5.优化合并大数据导出卡顿问题——已完成[aru_50]

一、简单的数据导出（不涉及数据的合并）

温馨提示

此内容可实现单行数据形式的动态导出，如下图所示

导出效果

博主精品——动态Excel导出（兼容单行及复杂表格数据的自动合并单元格，优雅~）插图

必要的前期准备

1.基本的数据表

CREATE TABLE `excel_dynamic` (
  `id` bigint(22) NOT NULL AUTO_INCREMENT,
  `function_number` int(5) NOT NULL COMMENT '功能编号',
  `function_name` varchar(50) DEFAULT NULL COMMENT '功能名字',
  `header` varchar(255) DEFAULT NULL COMMENT '复杂表头使用',
  `field_name` varchar(50) DEFAULT NULL COMMENT '字段名字',
  `property_name` varchar(50) DEFAULT NULL COMMENT '标记属性名',
  `type` int(2) DEFAULT NULL COMMENT '类型（1：字符串 2：时间 3：金额 4：枚举）',
  `additional_data` varchar(20) DEFAULT NULL COMMENT '额外操作如：yyyy-mm-dd 或 枚举相关',
  `sort` int(5) unsigned DEFAULT NULL COMMENT '排序',
  PRIMARY KEY (`id`),
  KEY `index_function_number` (`function_number`)
) ENGINE=InnoDB AUTO_INCREMENT=1739178131555749905 DEFAULT CHARSET=utf8 COMMENT='动态Excel';

2.必要的实体类

@Data
public class ExcelDynamic {
    @TableId
    private Long id;
    /**
     * 功能编号
     */
    private Integer functionNumber;
    /**
     * 功能名字
     */
    private String functionName;
    /**
     * 表头 （预留）
     */
    private String header;
    /**
     * 字段名 中文
     */
    private String fieldName;
    /**
     * 字段名
     */
    private String propertyName;
    /**
     * 字段类型
     */
    private Integer type;
    /**
     * 额外标识
     */
    private String additionalData;
    /**
     * 排序
     */
    private Integer sort;


}
@Data
public class ExcelData {
    private List<List<String>> headList;
    private List<List<String>> dataList;
}

3.枚举

@Getter
@AllArgsConstructor
public enum ExcelDynamicsTypeEnum {
    STRING(1, "普通文本"),
    DATE(2, "时间"),
    MONEY(3, "金额"),
    // 枚举
    ENUM(4, "枚举"),
    ;

    /**
     * 序号
     */
    private final Integer code;

    /**
     * 内容
     */
    private final String content;
}

4.和数据库连接使用的Mapper

@Mapper
public interface ExcelDynamicMapper extends BaseMapper<ExcelDynamic> {
}

5.核心service

点击查看完整内容
温馨提示

到这里就已经基本具备了此功能，示例如下

示例代码及数据库配置

    @GetMapping("/detail")
    public void test() {
        List<UserDO> list = userService.list();
        List<UserVO> voList = new ArrayList<>();
        for (UserDO userDO : list) {
            UserVO userVO = new UserVO();
            userVO.setId(userDO.getId());
            userVO.setName1(userDO.getName());
            userVO.setName2(userDO.getName());
            userVO.setName3(userDO.getName());
            userVO.setName4(userDO.getName());
            userVO.setName5(userDO.getName());

            voList.add(userVO);
        }
        excelDynamicsService.getExcel("测试动态导出",
                "D:\\jar\\"+ IdUtil.getSnowflakeNextIdStr()+".xlsx", voList,1);
    }

博主精品——动态Excel导出（兼容单行及复杂表格数据的自动合并单元格，优雅~）插图2

温馨提示

好了，到了这里，即可实现我们最开始的导出效果了~[aru_42]

导出效果

博主精品——动态Excel导出（兼容单行及复杂表格数据的自动合并单元格，优雅~）插图

二、重头戏——复杂的数据自动合并导出

温馨提示
    • 此方案已经将前面的代码重构得七七八八，并且代码可读性增加。兼容前面的简单数据导出。
    • 设计思路
        • 先找到需要动态导出的所有列表
        • 找到这些列表之后，分析里面的数据，找到长度最长的一个，并定义好它（因为我们导出嵌套数组的时候需要知道最高长度是什么）
        • 根据最长的数组长度进行循环动态列表，如果循环的时候下标超过了短数组的长度，那么我们只需要补充空字符串即可
        • 假设导出的嵌套数组中有a、b，a长度为10，b长度为3，这样我们就得到了一个单条数据有10行的Excel表格（我们用最长数组长度来循环，所以是10条）
        • 根据工具类，获取我们需要合并的列，即可实现此功能

      总结：具体代码如下，里面注释非常详细

 

1.核心代码类

点击查看完整内容

2.合并相同字段的工具类

点击查看完整内容

3.相关类更新，以及数据库表的更新

实体类：ExcelDynamic

构造导出类：ExcelData

动态导出表

示例代码

        List<UserDO> list = userService.list();
        List<UserVO> voList = new ArrayList<>();
        for (UserDO userDO : list) {
            UserVO userVO = new UserVO();
            userVO.setId(userDO.getId());
            userVO.setName1(userDO.getName() +1);
            userVO.setName2(userDO.getName() +2);
            userVO.setName3(userDO.getName()+3);
            userVO.setName4(userDO.getName()+4);
            userVO.setName5(userDO.getName()+5);

            List<UserVO2> userVOList = new ArrayList<>();

            List<UserVO2> userVOList2 = new ArrayList<>();

            for (int i = 0; i < 3; i++) {
                UserVO2 userVO2 = new UserVO2();
                userVO2.setId(userDO.getId());
                userVO2.setName1(userDO.getName() +"我是需要合并的1号");
                userVO2.setName4(userDO.getName()+"我是需要合并的4号");
                userVO2.setName5(userDO.getName()+"我是需要合并的5号");
                userVOList.add(userVO2);
            }
            for (int i = 0; i < 10; i++) {
                UserVO2 userVO2 = new UserVO2();
                userVO2.setId(userDO.getId());
                userVO2.setName1(userDO.getName() +"我是第二块需要合并的1号");
                userVO2.setName2(userDO.getName() +"我是第二块需要合并的2号");
                userVO2.setName3(userDO.getName()+"我是第二块需要合并的3号");
                userVO2.setName4(userDO.getName()+"我是第二块需要合并的4号");
                userVO2.setName5(userDO.getName()+"我是第二块需要合并的5号");
                userVOList2.add(userVO2);
            }
            userVO.setUserVOList(userVOList);
            userVO.setUserVOList2(userVOList2);

            voList.add(userVO);
        }

        excelDynamicsService.getExcel("测试动态导出",
                "C:\\Users\\dbc\\Desktop\\project\\test\\"+ IdUtil.getSnowflakeNextIdStr()+".xlsx", voList,1);

数据库配置

博主精品——动态Excel导出（兼容单行及复杂表格数据的自动合并单元格，优雅~）插图4

最终导出效果

博主精品——动态Excel导出（兼容单行及复杂表格数据的自动合并单元格，优雅~）插图6
博主精品——动态Excel导出（兼容单行及复杂表格数据的自动合并单元格，优雅~）插图8

三、兼容百万数据导出

温馨提示

设计思路

  • 我们利用EasyExcel的ExcelWriter.write()方法，实现对大批量数据的分批写入
    • 细化操作
      • 1、首先获取我们所需要的动态格式定义（从动态Excel表中获取）
      • 2、构建所需要的表样式：表头内容，相关Excel格式之类，合并开始行，需合并类等。。。
      • 3、将初始的Excel表生成，注意这时候表中是没有任何数据的，第一步仅仅是生成一个带格式的Excel，可以这么理解
      • 4、循环获取大批量数据并且导出（通过数据库等操作获取数据）
      • 总结：我们之前定义封装的各种方法依然有效，仅仅是将之前的一步导出操作拆分为了两步：
        • 1、带格式的Excel空表格
        • 2、分批次导出Excel内容
  • 目前遇到问题
    • 1、合并大数据内容时，导出速度有问题，博主测试导出500条数据大概需要10多20秒。（暂未优化）

代码里面注释非常详细，如下，开箱即用

1.关键核心导出方法

点击查看完整内容

略微优化过后的合并工具类

温馨提示

目前略微优化过第一版，将之前的列表循环查找换成了HashMap形式，将时间复杂度降低了一些，将在后面的章节主要优化这个部分[aru_61]

点击查看完整内容

3.略微有变动的导出实体

@Data
public class ExcelData {
    private List<List<String>> headList;
    private List<List<String>> dataList;
    private Integer mergeRowIndex;
    private int[] mergeColumnIndex;

    private AtomicInteger index = new AtomicInteger(1);

    private ExcelWriter excelWriter;

    private List<ExcelDynamic> excelDynamics;
}

4.基本示例代码以及导出部分截图

@GetMapping("/detail")
    public void test() throws NoSuchFieldException, IllegalAccessException {
        List<UserDO> list = userService.list();
        List<UserVO> voList = new ArrayList<>();
        for (UserDO userDO : list) {
            UserVO userVO = new UserVO();
            userVO.setId(userDO.getId());
            userVO.setName1(userDO.getName() +1);
            userVO.setName2(userDO.getName() +2);
            userVO.setName3(userDO.getName()+3);
            userVO.setName4(userDO.getName()+4);
            userVO.setName5(userDO.getName()+5);

            List<UserVO2> userVOList = new ArrayList<>();

            List<UserVO2> userVOList2 = new ArrayList<>();

            for (int i = 0; i < 3; i++) {
                UserVO2 userVO2 = new UserVO2();
                userVO2.setId(userDO.getId());
                userVO2.setName1(userDO.getName() +"我是需要合并的1号" + i);
                userVO2.setName2(userDO.getName() +"我是第二块需要合并的2号"+ i);
                userVO2.setName3(userDO.getName()+"我是第二块需要合并的3号"+ i);
                userVO2.setName4(userDO.getName()+"我是需要合并的4号"+ i);
                userVO2.setName5(userDO.getName()+"我是需要合并的5号"+ i);
                userVOList.add(userVO2);
            }
            for (int i = 0; i < 10; i++) {
                UserVO2 userVO2 = new UserVO2();
                userVO2.setId(userDO.getId());
                userVO2.setName1(userDO.getName() +"我是第二块需要合并的1号"+ i);
                userVO2.setName2(userDO.getName() +"我是第二块需要合并的2号"+ i);
                userVO2.setName3(userDO.getName()+"我是第二块需要合并的3号"+ i);
                userVO2.setName4(userDO.getName()+"我是第二块需要合并的4号"+ i);
                userVO2.setName5(userDO.getName()+"我是第二块需要合并的5号"+ i);
                userVOList2.add(userVO2);
            }
            userVO.setUserVOList(userVOList);
            userVO.setUserVOList2(userVOList2);

            for (int i = 0; i < 100; i++) {
                voList.add(userVO);
            }

        }
        ExcelData excelData = new ExcelData();
        excelData.setExcelDynamics(excelDynamicsService.getExcelDynamics(1));
        try {
            excelDynamicsService.getBigExcel("D:\\jar\\" + IdUtil.getSnowflakeNextIdStr() + ".xlsx", excelData);

        for (int i = 0; i < 1; i++) {
            excelDynamicsService.getBigExcel("测试动态导出",voList,excelData);
            System.out.println("这是第"+i+"次");
        }
        }finally {
            if (excelData.getExcelWriter() != null){
                excelData.getExcelWriter().finish();
            }

        }


    }

博主精品——动态Excel导出（兼容单行及复杂表格数据的自动合并单元格，优雅~）插图10

四、接近最终版本——99%完成度（仅差设计模式重构优化逻辑）

温馨提示

经过博主的努力优化，基本实现了百万导出（理论）及合并极其复杂的Excel了。博主亲测，win上电脑4核，接近两万的极端合并耗时在10秒内（Linux上可能会更快，线上更不用说~），一般这种极端合并正常数据中是不会有的。（极端合并：每一条数据都需要合并大量数据且多列的数据）
设计思路

  • 1、回顾之前我们的合并逻辑，我们是自己维护好每一列的序号，然后通过序号来合并对应的单元格。
  • 2、在最初的版本中，我们会对每一行每一列都单独的做处理，大致处理如下
    • 判断是否是用户需要合并的单元格，如果是就进行操作
    • 判断序号是否一致，如果一致就操作
    • 循环判断整个目前已经合并的单元格，和我们目前x行x列的值是否需要合并，然后进行删除合并或者添加合并，这是一个O（n）级别的操作，又因为是有多行多列我们都会遍历一次，就变成了多个O（n）级别的操作，性能下降极其严重
  • 经过研究，添加单元的操作极其消耗性能，也就是sheet.addMergedRegion，我们之前还在N个循环中反复的sheet.removeMergedRegion、sheet.addMergedRegion，当导出的数量大，且序号合并的单元格增多之后，导出的速度也是线性的上升，虽然不是指数级别，但是基本也是不可以忍受了。
  • 3、既然删除合并单元格和添加单元格耗时严重，我们处理数据时只保存需要合并的单元格信息，导出完成再统一添加合并信息到sheet即可。
  • 4、重构现有的数据结构，使用更合适的数据结构，将O（n）级别的操作调整为O（1）级别，也就是将循环变成map的查找，map的查询速度是极快的，那么难点就是将一个List数组如何转换为一个合适的map数组。
  • 5、具体代码里面都有详细的注释，详情看代码即可

1.完整关键合并类

点击查看完整内容

示例代码和导出截图

示例代码

   List<UserDO> list = userService.list();
        List<UserVO> voList = new ArrayList<>();
        for (UserDO userDO : list) {
            UserVO userVO = new UserVO();
            userVO.setId(userDO.getId());
            userVO.setName1(userDO.getName() + 1);
            userVO.setName2(userDO.getName() + 2);
            userVO.setName3(userDO.getName() + 3);
            userVO.setName4(userDO.getName() + 4);
            userVO.setName5(userDO.getName() + 5);

            List<UserVO2> userVOList = new ArrayList<>();

            List<UserVO2> userVOList2 = new ArrayList<>();

            for (int i = 0; i < 10; i++) {
                UserVO2 userVO2 = new UserVO2();
                userVO2.setId(userDO.getId());
                userVO2.setName1(userDO.getName() + "我是需要合并的1号" + i);
                userVO2.setName2(userDO.getName() + "我是第二块需要合并的2号" + i);
                userVO2.setName3(userDO.getName() + "我是第二块需要合并的3号" + i);
                userVO2.setName4(userDO.getName() + "我是需要合并的4号" + i);
                userVO2.setName5(userDO.getName() + "我是需要合并的5号" + i);
                userVOList.add(userVO2);
            }
            for (int i = 0; i < 20; i++) {
                UserVO2 userVO2 = new UserVO2();
                userVO2.setId(userDO.getId());
                userVO2.setName1(userDO.getName() + "我是第二块需要合并的1号" + i);
                userVO2.setName2(userDO.getName() + "我是第二块需要合并的2号" + i);
                userVO2.setName3(userDO.getName() + "我是第二块需要合并的3号" + i);
                userVO2.setName4(userDO.getName() + "我是第二块需要合并的4号" + i);
                userVO2.setName5(userDO.getName() + "我是第二块需要合并的5号" + i);
                userVOList2.add(userVO2);
            }
            userVO.setUserVOList(userVOList);
            userVO.setUserVOList2(userVOList2);

            for (int i = 0; i < 20; i++) {
                voList.add(userVO);
            }

        }

        ExcelData excelData = new ExcelData();
        excelData.setExcelDynamics(excelDynamicsService.getExcelDynamics(1));
        // 开始时间
        long begin = System.currentTimeMillis();
        try {
            excelDynamicsService.getBigExcel("D:\\jar\\" + IdUtil.getSnowflakeNextIdStr() + ".xlsx", excelData);

            for (int i = 0; i < 10; i++) {
                excelDynamicsService.getBigExcel("测试动态导出", voList, excelData);
                System.out.println("这是第" + (i+1) + "次");
            }
        } finally {
            if (excelData.getExcelWriter() != null) {
                excelData.getExcelWriter().finish();
                // 结束时间
                long end = System.currentTimeMillis();
                // 耗时
                System.out.println("耗时：" + (end - begin) + "ms");
                ExcelSheet excelSheet = excelSheetThreadLocal.get();
                excelSheetThreadLocal.remove();
            }

        }

导出截图

博主精品——动态Excel导出（兼容单行及复杂表格数据的自动合并单元格，优雅~）插图12
博主精品——动态Excel导出（兼容单行及复杂表格数据的自动合并单元格，优雅~）插图14

本文作者为DBC，转载请注明。

发表评论 取消回复
表情 图片 链接 代码

分享