关于葡萄城开发技术:React-Springboot-Quartz从0实现Excel报表自动化

一、我的项目背景

企业日常工作中须要制作大量的报表，比方商品的销量、销售额、库存详情、员工打卡信息、保险报销、办公用品洽购、差旅报销、我的项目进度等等，都须要制作统计图表以更直观地查阅。然而报表的制作往往须要消耗大量的工夫，即便复用制作好的报表模版，一次次周期性对数据的复制粘贴操作也很耗人，同时模版在此过程中也会逐步变得面目全非。
基于此，咱们须要开掘数据背地暗藏的关联信息，将人工的常规性操作抽离进去，应用工具和代码去实现，这个过程就称之为报表自动化。文内附前后端 demo 我的项目源码，家人们自行下载即可。

二、报表自动化的劣势

报表自动化带来的价值有哪些呢？
1、节省时间，提高效率
身处信息爆炸的时代，任何一家企业都有体量宏大、结构复杂、各种各样的数据，多类数据交互交融，对其进行剖析常常随同着大量人力资源的耗费。自动化报表通过正当的设计，独立出各个业务功能模块，后续反复援用该模块，实现反复操作的代码复用。
对于固定流程或逻辑的一些操作，计算机的执行速度是人力不可及的，为咱们节俭了工夫，能够投入更有意义的工作。
2、升高出错率
人工操作总是受太多不可控因素影响，存在各种出错的潜在可能。与之相比，自动化意味着通过编码伎俩实现了长久化的逻辑、流程，通过反复的测试验证之后，便可齐全信赖该程序。在重复性的工作场景下机器产出的稳定性远高于人工操作。
3、时效性高
日报、周报、月报这种周期性的报表，人为操作很难管制工夫的准确性，然而通过代码管制能够最大水平的保障其定点触发操作。

三、零碎性能点

1. 工作配置灵便：反对依据业务需要，通过界面操作控制工作的启停状态，工作对应生成的文件类型等。
2. 报表模版设计自在：业务人员可依据对应工作配置的预览数据自定义报表模版，而后将其保留失效。
3. 前端预览：反对从前端预览报表详情。
4. 定时发送：定时生成报表文件并将其同步到微信群。
5. 反对多种类型：反对 Excel、PDF、图表等文件类型的报表格局。
6. 数据主动抽取：动静读取数据库中的数据生成报表。
7. 模版和数据独立存储：应用在线表格设计器编辑模版，存储时只保留模版，数据从数据库加载。

四、方案设计

1、整体流程

报表自动化的终点是能对接数据源，期间能自动化的生成当时设计好格局的报表，最终通过企业微信主动推送音讯。具体流程能够分为 3 个步骤：
1. 报表模版设计
2. 对接数据源：从数据库中读取数据，动静适配数据模版。
3. 自动化过程实现：利用定时工作，定时捞取数据，借助 GcExcel 生成对应类型的文件，通过对接企业微信的 API，将文件同步到微信群。

2、技术栈

前端：React+SpreadJS
Demo 地址：https://gcdn.grapecity.com.cn…
后端：Java+GcExcel+Mysql+Quartz
Demo 地址：
https://gcdn.grapecity.com.cn…

3、具体实现

3.1 数据库表设计

note：此为测试 demo，故没有创立主键索引之外的索引。
3.1.1 工作配置列表 task_config
依赖 Quartz 组件实现定时工作。读取工作配置表中启动状态的工作配置，依照工作类型读取对应数据源的增量更新数据。

SQL
CREATE TABLE `task_config` (`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    `task_id` VARCHAR(64) NOT NULL DEFAULT ''COMMENT' 工作 ID',
    `task_type` VARCHAR(64) DEFAULT NULL DEFAULT ''COMMENT' 工作类型 ',
    `task_name` VARCHAR(128) NOT NULL DEFAULT ''COMMENT' 工作名称 ',
    `task_status` TINYINT(4) NOT NULL DEFAULT 0 COMMENT '工作状态：1: 启动   0: 终止',
    `task_context` text COMMENT '工作模版信息',
    `time_span` TINYINT(4) NOT NULL DEFAULT 0 COMMENT '工夫距离，单位 s',
    `operator` VARCHAR(64) NOT NULL DEFAULT ''COMMENT' 操作人 ',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创立工夫',
    `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新工夫',
    PRIMARY KEY (`id`) USING BTREE
)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 COMMENT='工作配置表';

3.1.2 订单信息表 order
订单相干数据源信息，对应订单类的报表工作。

SQL
CREATE TABLE `order` (`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    `order_id` VARCHAR(64) NOT NULL DEFAULT ''COMMENT' 订单 ID',
    `order_amount` DECIMAL DEFAULT NULL DEFAULT 0 COMMENT '订单金额',
    `order_discount` DECIMAL DEFAULT NULL DEFAULT 0 COMMENT '订单折扣金额',
    `shipping_fee` DECIMAL DEFAULT NULL DEFAULT 0 COMMENT '运费',
    `receiver_name` VARCHAR(64) NOT NULL DEFAULT ''COMMENT' 收件人姓名 ',
    `receiver_state` VARCHAR(64) NOT NULL DEFAULT ''COMMENT' 省 ',
    `receiver_city` VARCHAR(64) NOT NULL DEFAULT ''COMMENT' 市 ',
    `receiver_district` VARCHAR(64) NOT NULL DEFAULT ''COMMENT' 区 ',
    `receiver_address` VARCHAR(128) NOT NULL DEFAULT ''COMMENT' 具体地址 ',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创立工夫',
    `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新工夫',
    PRIMARY KEY (`id`) USING BTREE
)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 COMMENT='订单表';

3.1.3 发货单表 waybill
发货单相干数据源信息，对应发货单类的报表工作。

SQL
CREATE TABLE `waybill` (`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    `order_id` VARCHAR(64) NOT NULL DEFAULT ''COMMENT' 订单 ID',
    `waybill_id` VARCHAR(64) NOT NULL DEFAULT ''COMMENT' 发货单 ID',
    `express_sn` VARCHAR(128) DEFAULT NULL DEFAULT ''COMMENT' 物流单号 ',
    `express_name` VARCHAR(64) DEFAULT NULL DEFAULT ''COMMENT' 物流公司名称 ',
    `receiver_name` VARCHAR(64) NOT NULL DEFAULT ''COMMENT' 收件人姓名 ',
    `receiver_state` VARCHAR(64) NOT NULL DEFAULT ''COMMENT' 省 ',
    `receiver_city` VARCHAR(64) NOT NULL DEFAULT ''COMMENT' 市 ',
    `receiver_district` VARCHAR(64) NOT NULL DEFAULT ''COMMENT' 区 ',
    `receiver_address` VARCHAR(128) NOT NULL DEFAULT ''COMMENT' 具体地址 ',
    `out_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '发货工夫',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创立工夫',
    `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新工夫',
    PRIMARY KEY (`id`) USING BTREE
)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 COMMENT='发货单表';

3.2 性能拆解

1、前端实现
前端应用 React 框架，嵌入了 SpreadJS 组件，初始化时从后端读取工作配置列表数据并展现。可从前端配置工作规定，次要是配置模版信息。

2、后端
后端是一个 SpringBoot 我的项目，嵌入 GcExcel 组件对编辑、导出等操作，同时借助 Quartz 定时任务调度框架实现定时工作的治理，并接入了企业微信裸露的群机器人音讯对接 API，发送音讯到企业微信群。
定时任务调度框架 Quartz
Quartz 的三个外围概念：调度器、工作、触发器，三者之间的关系是：
一个作业，比拟重要的三个因素就是 Scheduler，JobDetail，Trigger；而 Trigger 对于 Job 而言就好比一个驱动器，没有触发器来定时驱动作业，作业就无奈运行；对于 Job 而言，一个 Job 能够对应多个 Trigger，但对于 Trigger 而言，一个 Trigger 只能对应一个 Job，所以一个 Trigger 只能被指派给一个 Job；如果你须要一个更简单的触发打算，能够创立多个 Trigger 并指派它们给同一个 Job。

调度器的次要 API

Java
// 绑定 jobDetail 与 trigger
scheduler.scheduleJob(jobDetail, trigger);
// 查看 JobDetail 是否存在 
scheduler.checkExists(JobKey.jobKey(name, group))  
// 查看 Trigger 是否存在      
scheduler.checkExists(TriggerKey.triggerKey(name, group)) 
// 删除 jobDetail       
scheduler.deleteJob(JobKey.jobKey(name, group)) 
// 立刻执行一次指定的工作               
scheduler.triggerJob(JobKey.jobKey(name, group), dataMap) 
// 启动任务调度       
scheduler.start();       
// 暂停指定的 job         
scheduler.pauseJob(jobKey);   
// 任务调度挂起，即暂停操作     
scheduler.standby();   
// 敞开任务调度，同 shutdown(false)     
scheduler.shutdown();    
// 示意期待所有正在执行的 Job 执行结束之后，再敞开 Scheduler    
scheduler.shutdown(true);    
// 示意间接敞开 Scheduler   
scheduler.shutdown(false);

定时工作登程规定：
1、应用 cron 表达式 定时发送

Java
Trigger trigger = TriggerBuilder.newTrigger()
        .withIdentity("trigger1", "group1")
        .withSchedule(CronScheduleBuilder.cronSchedule("0/5 * * * * ?"))  // 日历
        .build();

2、应用 simpleTrigger 触发器
为那种须要在特定的日期 / 工夫启动，且以一个可能的间隔时间反复执行 n 次的 Job 所设计的。

JavaScript
// 立刻开始执行，2 秒执行一次，反复 3 次，3 秒后完结执行（当反复次数或者完结工夫有一个先达到时，就会进行执行）Trigger trigger = TriggerBuilder.newTrigger()
                .withIdentity("trigger1", "triggerGroup1")
                .startNow()
                .withSchedule(SimpleScheduleBuilder.simpleSchedule().withIntervalInSeconds(2).withRepeatCount(3))
                .endAt(new Date(new Date().getTime() + 3000L))
                .build();

五、成果演示

应用步骤阐明：

整个页面布局能够分为两大部分，上半部分为从数据库中读取的工作配置列表，下半部分为 SpreadJS 的 Designer 模块。在前端配置工作规定后，后端服务会读取具体的工作配置信息，调度工作进行生产。整个操作能够分为以下几个步骤：
1)、读取工作配置数据到 React 表格中。
2)、选中特定的工作配置项，读取对应数据源的数据到 Worksheet 中展现。
3)、编辑报表工作模版并保留。
因为 json 文件是存储在 mysql 数据库表中的一个字段中，若字段太大会导致溢出且影响性能，故仅保留款式，后端进行数据源动静查问去适配生成报表。
一个残缺的 json 对象示例如下所示：

JSON
{"version":"15.0.2","sheetCount":1,"customList":[],"sheets":{"order":{"name":"order","isSelected":true,"rowCount":20,"columnCount":13,"frozenTrailingRowStickToEdge":true,"frozenTrailingColumnStickToEdge":true,"theme":"Office","data":{"defaultDataNode":{"style":{"themeFont":"Body"}}},"rowHeaderData":{"defaultDataNode":{"style":{"themeFont":"Body"}}},"colHeaderData":{"defaultDataNode":{"style":{"themeFont":"Body"}}},"columns":[{"name":"receiverName","displayName":"姓名","size":60,"visible":true},{"name":"orderAmount","displayName":"订单金额","size":80,"visible":true},{"name":"orderDiscount","displayName":"订单折扣","size":60,"visible":true}],"leftCellIndex":0,"topCellIndex":0,"selections":{"activeSelectedRangeIndex":-1,"length":0},"autoGenerateColumns":false,"rowOutlines":{"items":[]},"columnOutlines":{"items":[]},"cellStates":{},"states":{},"outlineColumnOptions":{},"autoMergeRangeInfos":[],"charts":[{"name":"Chart 1","x":4,"y":4,"width":480,"height":300,"startRow":0,"startRowOffset":4,"startColumn":0,"startColumnOffset":4,"endRow":15,"endRowOffset":4,"endColumn":7,"endColumnOffset":36,"isSelected":true,"typeName":"2","chartSpace":{"typeName":"chartSpace","roundedCorners":false,"chart":{"title":{"tx":{"rich":{"p":[{"elements":[{"elementType":0,"t":"Amount","rPr":{"latin":{"typeface":"+mn-lt"},"sz":18.67,"b":false,"solidFill":{"schemeClr":{"val":1,"lumMod":[65000],"lumOff":[35000]}}}}],"pPr":{"defRPr":{"latin":{"typeface":"+mn-lt"},"sz":18.67,"b":false,"solidFill":{"schemeClr":{"val":1,"lumMod":[65000],"lumOff":[35000]}}}},"endParaRPr":{}}],"bodyPr":{},"lstStyle":{}}},"overlay":false,"spPr":{"noFill":true,"ln":{"noFill":true},"effectLst":{}}},"autoTitleDeleted":false,"plotArea":{"axes":[{"axisType":0,"axId":59604390,"delete":false,"majorTickMark":2,"minorTickMark":2,"tickLblPos":2,"title":null,"axPos":0,"scaling":{"orientation":1},"spPr":{"ln":{"solidFill":{"schemeClr":{"val":1,"lumMod":[15000],"lumOff":[85000]}}}},"numFmt":{"formatCode":"General"},"txPr":{"p":[{"elements":[{"elementType":0,"t":"","rPr":{"latin":{"typeface":"+mn-lt"},"sz":12,"b":false,"solidFill":{"schemeClr":{"val":1,"lumMod":[65000],"lumOff":[35000]}}}}],"pPr":{"defRPr":{"latin":{"typeface":"+mn-lt"},"sz":12,"b":false,"solidFill":{"schemeClr":{"val":1,"lumMod":[65000],"lumOff":[35000]}}}},"endParaRPr":{}}]},"auto":true,"lblOffset":0,"tickMarkSkip":1,"noMultiLvlLbl":true,"AxisGroup":0,"AxisType":0,"crosses":1,"crossAx":36407267},{"axisType":3,"axId":36407267,"delete":false,"majorTickMark":2,"minorTickMark":2,"tickLblPos":2,"title":null,"axPos":1,"scaling":{"orientation":1},"spPr":{"ln":{"solidFill":{"schemeClr":{"val":1,"lumMod":[15000],"lumOff":[85000]}}}},"numFmt":{"formatCode":"General"},"txPr":{"p":[{"elements":[{"elementType":0,"t":"","rPr":{"latin":{"typeface":"+mn-lt"},"sz":12,"b":false,"solidFill":{"schemeClr":{"val":1,"lumMod":[65000],"lumOff":[35000]}}}}],"pPr":{"defRPr":{"latin":{"typeface":"+mn-lt"},"sz":12,"b":false,"solidFill":{"schemeClr":{"val":1,"lumMod":[65000],"lumOff":[35000]}}}},"endParaRPr":{}}]},"majorGridlines":{"spPr":{"ln":{"solidFill":{"srgbClr":{"val":[217,217,217]}},"w":1},"effectLst":{}}},"AxisGroup":0,"AxisType":1,"crosses":1,"crossBetween":0,"crossAx":59604390}],"chartGroups":[{"chartType":6,"ser":[{"seriesType":0,"idx":0,"order":0,"cat":{"strRef":{"f":"order!$A$1:$A$2"}},"val":{"numRef":{"f":"order!$B$1:$B$2","numCache":{"formatCode":"General"}}},"shape":2,"invertIfNegative":false},{"seriesType":0,"idx":1,"order":1,"cat":{"strRef":{"f":"order!$A$1:$A$2"}},"val":{"numRef":{"f":"order!$C$1:$C$2","numCache":{"formatCode":"General"}}},"shape":2,"invertIfNegative":false}],"axId":[59604390,36407267],"barDir":1,"grouping":1,"gapWidth":150,"varyColors":false,"overlap":-27}],"spPr":{"noFill":true,"ln":{"noFill":true}}},"legend":{"legendPos":4,"spPr":{"noFill":true,"ln":{"noFill":true}},"txPr":{"p":[{"elements":[{"elementType":0,"t":"","rPr":{"latin":{"typeface":"+mn-lt"},"sz":12,"b":false,"solidFill":{"schemeClr":{"val":1,"lumMod":[65000],"lumOff":[35000]}}}}],"pPr":{"defRPr":{"latin":{"typeface":"+mn-lt"},"sz":12,"b":false,"solidFill":{"schemeClr":{"val":1,"lumMod":[65000],"lumOff":[35000]}}}},"endParaRPr":{}}]}},"plotVisOnly":true,"dispBlanksAs":1,"dispNaAsBlank":false},"spPr":{"solidFill":{"schemeClr":{"val":0}},"ln":{"solidFill":{"schemeClr":{"val":1,"lumMod":[15000],"lumOff":[85000]}},"w":1}},"txPr":{"p":[{"elements":[{"elementType":0,"t":"","rPr":{"latin":{"typeface":"+mn-lt"},"b":false,"solidFill":{"schemeClr":{"val":1,"lumMod":[65000],"lumOff":[35000]}}}}],"pPr":{"defRPr":{"latin":{"typeface":"+mn-lt"},"b":false,"solidFill":{"schemeClr":{"val":1,"lumMod":[65000],"lumOff":[35000]}}}},"endParaRPr":{}}]}},"useAnimation":false}],"preserveUnsupportedChartFlag":false,"printInfo":{"paperSize":{"width":850,"height":1100,"kind":1}},"shapeCollectionOption":{"snapMode":0},"index":0}},"sheetTabCount":0,"pivotCaches":{},"i0c":0}

更多纯前端表格在线 demo 示例 :https://demo.grapecity.com.cn…
挪动端示例（可扫码体验）：http://demo.grapecity.com.cn/…