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/forum.php?mod=attachment&aid=MjM4NzkxfDFhNjNlYjc2fDE2NjYyNDUxMjZ8NjI2NzZ8OTk3MTg%3D
後端：Java+GcExcel+mysql+Quartz
Demo地址：
https://gcdn.grapecity.com.cn/forum.php?mod=attachment&aid=MjM4NzkyfGExOWYxNmQzfDE2NjYyNDUxMjZ8NjI2NzZ8OTk3MTg%3D

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/spreadjs/gc-sjs-samples/index.html
行動端範例（可掃碼體驗）：http://demo.grapecity.com.cn/spreadjs/mobilesample/