通用导出Excel任意表数据工具

本文主要介绍工具，后一篇文章具体讲怎么使用
实战通用导出excel工具适配多数据源任意表数据-附源码

1.背景

随着系统的增加，系统都趋于微服务发展，但是服务之间传输当数据过大时也会存在超时现象，比如最常见的功能导入导出，如果同步等待数据量一大势必会超时，建议解决方案是单独抽一个服务做成存储通用服务。
可以通过统一存储服务连接多个数据源的方式实现导入导出，导入导出服务不影响线上其它业务服务。
但是问题又来了！
虽然新建了存储服务将业务服务与存储分离，但如果导大批量的数据数据库压力就来了，所以中等数据导数据需要要做成分页，分批处理，大数据量导出需要预约下载,

思考问题

在开发中会发现，导入功能涉及不同的业务很难统一，但是导出可以，很多功能代码都是冗余的，能不能设计一套通用的导出呢 ？
整理一下大概的需求点

• 适合多个平台公用 ，支持连接导出多个数据源的数据 ;
• 支持分页导出 ;
• 支持一些参数的基本转换(枚举转换为文本等)，例如时间格式化，小数格式化等 ;
• 支持普通导出列表 ;
• 支持自定义模板导出 ;
• 支持分页导出，由于往往会导出大批量数据，需要做分页支持,导出压缩包，根据配置的页码分多个excel形式 ;

2.通用导出设计

要实现的功能一句话概括，
只要是SQL查询出来导出的只需要配置好相关信息，既可以导出任意数据源下任意表数据，

2.1支持的功能

• 支持多数据源下，任意表数据导出 ;
• 支持配置分页大小，超出页码最大值，则导出一个zip压缩包，并根据配置的页码区分多个excel ;
• 支持普通列表导出 ;
• 支持根据模板导出对象 ;
• 支持导出主单+子单形式的数据 ;

2.2直接上图

超出最大分页值，自动导出压缩包区分多个excel

2.1外部依赖

• hutool工具 ;
• 阿里-easyexcel ;
• 阿里云oss ;
• fastjson ;
• dom4j ;

2.2实现过程

• 需要新增两张表，一张导出模板表存储导出配置相关,一张任务表存储导出任务 ;
• 在导出模板表新建导出模板;(配置导出所需，后边会讲) ;
• 目标系统用户发起导出 ;
• 目标系统调用存储服务，存储服务创建导出任务，并返回任务ID(考虑到查询频率与数据库压力，任务会加redis缓存) ;
• 存储服务根据模板配置与目标系统导出参数处理动态SQL，异步导出文件上传到OSS存储，并更新任务记录 ;
• 目标系统主查，或者数据量小情况下可以用前端异步轮训获取的方式查询导出结果 ;

流程图

3.数据库相关

3.1.导出模板配置表

用于配置需要导出的表SQL以及标题，字段映射，导出数据源，导出分页大小等 ;

CREATE TABLE `export_template_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `template_id` varchar(32) NOT NULL COMMENT '模板ID',
  `platform_id` varchar(32) NOT NULL COMMENT '平台方ID',
  `db_code` varchar(32) NOT NULL COMMENT '数据库编码',
  `template_child_id` varchar(32) NOT NULL DEFAULT '0' COMMENT '子模板ID 0-无',
  `template_title` varchar(255) NOT NULL DEFAULT '' COMMENT '模板标题',
  `template_status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '模板状态 1-启用; 2-停用',
  `template_type` tinyint(4) NOT NULL DEFAULT '1' COMMENT '模板类型 1-普通导出列表 ; 2-模板导出列表 ; 3-模板导出对象; 4-模板导出对象与列表',
  `table_columns` varchar(2000) NOT NULL COMMENT '表列名（使用 | 分隔）',
  `table_names` varchar(1000) NOT NULL DEFAULT '' COMMENT '查询表名集',
  `export_file_name` varchar(255) NOT NULL DEFAULT '' COMMENT '导出文件名',
  `export_fields_exp` varchar(2000) NOT NULL DEFAULT '' COMMENT '导出数据字段集（使用 | 分隔）',
  `query_condition` varchar(3000) NOT NULL DEFAULT '' COMMENT '查询条件，支持部分动态标签',
  `order_by_exp` varchar(255) NOT NULL COMMENT '排序条件表达式 示例：a.create_time desc',
  `page_size` int(10) NOT NULL DEFAULT '1000' COMMENT '分页大小',
  `download_url_expire` bigint(15) NOT NULL DEFAULT '7200' COMMENT '下载链接效期 单位:秒 -1-永久有效',
  `template_file_url` varchar(500) NOT NULL DEFAULT '' COMMENT '模板文件下载地址',
  `template_file_local_path` varchar(500) NOT NULL DEFAULT '' COMMENT '模板文件本地存储地址',
  `template_refresh_flag` tinyint(4) NOT NULL DEFAULT '1' COMMENT '模板更新标记 1-未更新 ;2-已更新',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uniq_template_id` (`template_id`) USING BTREE,
  KEY `idx_platform_id` (`platform_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='导出模板表';

3.2.导出任务表

用于记录导出任务

CREATE TABLE `export_task_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `task_id` varchar(32) NOT NULL COMMENT '任务ID',
  `platform_id` varchar(32) NOT NULL COMMENT '平台方ID',
  `template_id` varchar(32) NOT NULL COMMENT '模板ID',
  `task_title` varchar(255) NOT NULL DEFAULT '' COMMENT '任务标题',
  `task_status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '任务状态 1-处理中 ; 2-处理成功; 3-处理失败',
  `task_param` varchar(2000) NOT NULL DEFAULT '' COMMENT '任务参数(JSON)',
  `download_url` varchar(512) NOT NULL DEFAULT '' COMMENT '下载文件URL',
  `task_dual` bigint(20) NOT NULL DEFAULT '0' COMMENT '执行耗时毫秒数',
  `task_rows` int(11) NOT NULL DEFAULT '0' COMMENT '任务行数',
  `ext_info` varchar(512) NOT NULL DEFAULT '' COMMENT '扩展信息',
  `remarks` varchar(1024) NOT NULL DEFAULT '' COMMENT '任务备注(失败原因)',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uniq_task_id` (`task_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='导出任务表';

4.模板核心配置

4.1.模板表详细介绍

字段名称	字段含义	备注	示例
template_id	模板ID	模板的唯一ID
platform_id	平台方ID	该模板属于哪个平台
db_code	数据库编码	导出查询数据库自定义编码	DB001
template_child_id	子模板ID 0-无	用于一对多导出主单+子单业务	0
template_title	模板标题		用户信息
template_status	模板状态	1-启用; 2-停用
template_type	模板类型	1-普通导出列表 ; 2-模板导出列表 ; 3-模板导出对象; 4-模板导出对象与列表
table_columns	表列名	多个使用	分隔，多表，当字段名相同时需自定义别名，支持SQL别名语法 注意：字段名与sql返回的一致
table_names	查询表名集	无需添加关键字 “from” 支持关联表	user_info a inner join dept b on a.dept_id=b.dept_id
export_file_name	导出文件名		用户信息
export_fields_exp	导出数据字段集，需要与table_columns配置一致，支持别名（使用	分隔）	未填写，默认使用table_columns，excel列顺序按照 table_columns 已填写，使用当前内容作为excel字段，按照当前字段排序， 支持表达式与值的格式转换
query_condition	查询条件，	无需填写 where关键字，支持部分动态标签,写法与mybatis xml文件一致 目前支持 if、foreach标签	AND a.user_id = #{userId,jdbcType=VARCHAR}
order_by_exp	排序条件表达式	无需添加 order by	a.create_time desc
page_size	分页大小	导出列表根据此参数分页	100
download_url_expire	下载链接效期	单位:秒, -1-永久有效
template_file_url	模板文件下载地址
template_file_local_path	模板文件本地存储地址
template_refresh_flag	模板更新标记	1-未更新 ;2-已更新 若模板文件在本地存在 该标记为1则覆盖更新， 改标记为2则不更新模板

4.2导出值格式转换规则

属性	含义	使用说明	示例
title	标题	excel列标题，不填则默认为字段名	&title=我是标题
replace	替换	使用”_“分隔，原值_替换后,多个逗号分隔	&replace=1_启用,2_停用
dateFormat	时间格式化	支持时间类型字段格式化，与数字类型yyyyMMdd转换为yyyy_MM_dd	&dateFormat=yyyyMMdd 或&dateFormat=yyyyMMdd_yyyy-MM-dd
prefix	前缀		&prefix=我是前缀
suffix	后缀		&suffix=%
numFormat	数字格式化	使用”_“分隔，规则_数值，多个规则逗号分隔 乘以2 示例 : multiply_2 除以2 示例 : divide_2 保留2位小数 示例 : format_2	&numFormat=multiply_2,format_2

5.excel模板导出规则

6.实现愿景

• 减少重复劳动

7.缺点

• 不支持通过代码程序拼装数据的导出方式，只能使用SQL查询导出 ;

8.附言

万物有痕，望大佬多多指点