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

DBC 365 0
温馨提示

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

本文章计划完成的功能有:
1.动态单行数据导出——已完成[aru_50]
2.动态表头修改(不是根据数据变化)——已完成[aru_50]
3.复杂数据导出可自动合并单元格数据——已完成[aru_50]
4.兼容百万数据导出——已完成[aru_50]
5.优化合并大数据导出卡顿问题——已完成[aru_50]
6.使用设计模式重构——待开发[aru_46]

一、简单的数据导出(不涉及数据的合并)

温馨提示

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

导出效果

博主精品——动态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.removeMergedRegionsheet.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

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

分享