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

标签: 自研小工具

本文主要介绍工具,后一篇文章具体讲怎么使用

1.背景

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

思考问题

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

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

2.通用导出设计

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

2.1支持的功能

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

2.2直接上图

image.png

image.png

超出最大分页值,自动导出压缩包区分多个excel
image.png

2.1外部依赖

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.附言

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

原文链接:加载失败,请重新获取