GitHub | Demo | 博客 | 原文鏈接
序言
當我的團隊進行稅務系統模塊開發的時候，我發現他們需要花費80%的時間去解決計算問題，尤其體現在表格（Grid）中的計算，這些時間花在：
- 寫前臺js代碼（因為用戶在表格中的輸入會影響其他單元格，所以需要即時將運算后的新值呈現給用戶看）
- 寫后臺代碼（因為用戶對表格資料的更改會影響其他表格，所以要在用戶點擊保存時更新受影響表格的資料）
- 實施修改計算方法，導致開發者需要修改代碼
于是我調研了稅務其他模塊的功能，發現稅務系統大量使用表格控制元件，而其中或多或少都會涉及到計算問題，而處理計算的方法，都是采用硬編碼，
計算，這個習以為常的編碼動作，其實很容易讓人聯想到Excel中的公式，更何況需求檔案本身就是以Excel的形式提供的，當我們在使用Excel的時候，可以在單元格中設定公式，通過改變源頭單元格的值，Excel將自動計算單元格公式，將結果值賦予目標單元格，那么，我們是否可以參考這種模式，開發者不再需要寫復雜難懂的計算邏輯，只需要根據實施提供的公式，將它們轉成某種格式的陳述句，再呼叫某種計算引擎產出結果，將結果呈現給用戶看或者持久化到資料庫？答案是肯定的，而這一切的核心就是自動計算引擎——AutoCalculate，
作用
AutoCalculate是表格復雜運算的解決方案，可以讓你省掉成百上千行的計算邏輯代碼，從此寫代碼就像寫Excel公式一般簡單，
適用范圍
前臺：
適用于ElementUI表格、EasyUI Grid控制元件、ParamQuery Grid等所有js表格控制元件中帶有公式的復雜運算
后臺：
適用，需要V8引擎
前臺用法
AutoCalculate由兩部分組成，分別是公式和計算引擎，公式是就是根據特定語法撰寫的字串，如：[Month12,1]#3 = [Month11,1] * 10，計算引擎即是AutoCalculate.js，負責決議公式，以下開始介紹如何書寫公式，
單元格
假設有這樣的場景，單元格①=單元格②+單元格③，對應的公式是：
[Month1,1] = [Month1,2] + [Month1,3]
先來看看[Month1,1]代表什么，首先，中括號[ ]代表一個單元格，Month1即“1月”對應的列名，緊接著是一個逗號,，后面的1代表RowNo = 1，以此類推，
[Month1,2]代表列為“1月”且RowNo = 2的單元格
[Month1,3]代表列為“1月”且RowNo = 3的單元格
所以我們可以用[y,x]來代表一個單元格，y即列名，也稱作縱坐標， x即RowNo的值，也稱作橫坐標
如果表格沒有RowNo列怎么辦？如想尋找答案，請繼續往下閱讀
讓公式生效
//首先引入AutoCalculate.js
import AutoCalculate from '../components/AutoCalculate';
...
//定義一個AutoCalculate實體，formulas為公式陣列
let autoCal = new AutoCalculate(formulas);
/* 呼叫cal方法
* gridDatas（必填）：表格資料
* refField（必填）：參考欄位，即單元格[y,x]中x是哪個欄位的值
*/
autoCal.cal(gridDatas, refField);
區域公式
實際上，除了1月，2月，3月……10月也存在類似的公式，即：
[Month1,1] = [Month1,2] + [Month1,3]
[Month2,1] = [Month2,2] + [Month2,3]
[Month3,1] = [Month3,2] + [Month3,3]
……
……
……
[Month10,1] = [Month10,2] + [Month10,3]
也就是說我們需要寫10條這樣的公式，對于簡單的場景來說，這不成問題，但是對于某些包含大量公式的表格，這種寫法存在一些弊端，比如容易寫錯，還有，公式長的時候也需要花費較多時間才能寫完，所以，便有了區域公式，
觀察上面的公式可以發現，其實每條公式都可以用一條公式來代替，例如以下公式：
[@,1] = [@,2] + [@,3]
這里沒有明確的列名，只是用了一個占位符@，但它足以代表以上10條公式，這個時候，我們只需要在適當的位置補上列名就可以了，所以，最終的公式就是：
{Month1, Month2, Month3, Month4, Month5, Month6, Month7, Month8, Month9, Month10}[@,1] = [@,2] + [@,3]
你需要將列名用,隔開，并放置在大括號{ }內，如此，1條公式便相當于10條公式，
占位符不僅僅可以用于縱坐標，還可用于橫坐標，如以下公式：
//公式1：
[YearTotal,3] = [Month1,3] + [Month2,3] + [Month3,3] + [Month4,3] + [Month5,3] + [Month6,3] + [Month7,3] + [Month8,3] + [Month9,3] + [Month10,3]
//公式2：
[YearTotal,4] = [Month1,4] + [Month2,4] + [Month3,4] + [Month4,4] + [Month5,4] + [Month6,4] + [Month7,4] + [Month8,4] + [Month9,4] + [Month10,4]
//公式3：
[YearTotal,5] = [Month1,5] + [Month2,5] + [Month3,5] + [Month4,5] + [Month5,5] + [Month6,5] + [Month7,5] + [Month8,5] + [Month9,5] + [Month10,5]
//公式4：
[YearTotal,6] = [Month1,6] + [Month2,6] + [Month3,6] + [Month4,6] + [Month5,6] + [Month6,6] + [Month7,6] + [Month8,6] + [Month9,6] + [Month10,6]
//公式5：
[YearTotal,2] = [Month1,2] + [Month2,2] + [Month3,2] + [Month4,2] + [Month5,2] + [Month6,2] + [Month7,2] + [Month8,2] + [Month9,2] + [Month10,2]
//公式6：
[YearTotal,7] = [Month1,7] + [Month2,7] + [Month3,7] + [Month4,7] + [Month5,7] + [Month6,7] + [Month7,7] + [Month8,7] + [Month9,7] + [Month10,7]
//公式7：
[YearTotal,9] = [Month1,9] + [Month2,9] + [Month3,9] + [Month4,9] + [Month5,9] + [Month6,9] + [Month7,9] + [Month8,9] + [Month9,9] + [Month10,9]
//公式8：
[YearTotal,12] = [Month1,12] + [Month2,12] + [Month3,12] + [Month4,12] + [Month5,12] + [Month6,12] + [Month7,12] + [Month8,12] + [Month9,12] + [Month10,12]
//公式9：
[YearTotal,13] = [Month1,13] + [Month2,13] + [Month3,13] + [Month4,13] + [Month5,13] + [Month6,13] + [Month7,13] + [Month8,13] + [Month9,13] + [Month10,13]
使用區域公式，可以寫成：
{2, 3, 4, 5, 6, 7, 9, 12, 13}[YearTotal,@] = [Month1,@] + [Month2,@] + [Month3,@] + [Month4,@] + [Month5,@] + [Month6,@] + [Month7,@] + [Month8,@] + [Month9,@] + [Month10,@]
由此可見，區域公式為公式的書寫帶來了極大的便利，
支持js語法
在實際場景中，我們經常會碰到一些復雜的公式，如下圖，單元格公式使用了Excel自帶的Max函式，對于這樣的公式，我們可以這樣寫：
[Month1,9] = ([Month1,6] - [Month1,7] - [Month1,8] > 0 ? [Month1,6] - [Month1,7] - [Month1,8] : 0) + [Month1,5]
如你所見，公式支持js語法，你可以在公式等號右邊放入一個js變數，甚至js函式，只要是js決議引擎認識的語法，都被支持，
這里有個需要注意的地方，就是不可以將陣列元素放入公式中，因為js的陣列元素通常帶有“[ ]”符號，這與公式當中的單元格表示符”[ ]”產生沖突，所以陣列元素被禁止使用，請留意這一點，
[y]公式
接下來，帶大家看一看另外一種場景，如圖，存在這樣的關系：
單元格① = 單元格② - 單元格③
你可能很快就寫出了以下公式：
[column3,1] = [column2,1] - [column1,1]
[column3,2] = [column2,2] - [column1,2]
這樣寫本身沒有錯，但是我得提醒你，這里的行是不固定的，也就是說表格有多少行完全取決于當時的資料庫情況，有可能今天只有3行資料，明天會有5行，后天會有50行，我們不可能隨著行數增多而增加公式，所以對于這種行數不確定的表格，我們有一種新的寫法，我將它稱為[y]公式，因為跟普通公式相比，它沒有橫坐標：
[column3] = [column2] - [column1]
只需要一行公式，AutoCalculate便會將公式應用于指定列名下的所有行，
合計列與小數位數
有時候，我們需要求某一列的和，雖然求某一列的和可能不是我們的最終目的，但卻是我們完成計算的必要步驟，如存在以下關系：
單元格③ = 單元格① / 單元格②
單元格②是GroupApprovedTotal列的合計值，我們用<列名>來表示，即：<GroupApprovedTotal>，加上這里的行不固定，需要用到[y]公式，所以公式應該寫成：
[GroupApprovedTotalPercent] = [GroupApprovedTotal] / <GroupApprovedTotal>
我們知道，在除法中，除數是不可以為0的，所以正確的寫法應該是：
[GroupApprovedTotalPercent] = <GroupApprovedTotal> === 0 ? 0 : [GroupApprovedTotal] / <GroupApprovedTotal>
當你將這條公式放你的代碼，并啟動程式后，聰明的你應該很快發現，你得到的值不夠精確，如上面單元格③顯示的數值是66.91%，如果你的單元格①和單元格②跟上圖的數值相同，你的單元格③很可能是67%，這是為什么呢？
默認的，AutoCalculate會將計算結果保留2位小數，67%，即0.67，如果想得到66.91%，即0.6691，那就是需要保留4位小數，這時，你需要告訴AutoCalculate，你需要保留4位小數，所以，完整的寫法應該是：
[GroupApprovedTotalPercent]#4 = <GroupApprovedTotal> === 0 ? 0 : [GroupApprovedTotal] / <GroupApprovedTotal>
在公式的等號左邊，被賦值單元格的右邊，加“#”號，緊跟著寫上小數位數，注意，“#”和小數位數之間不能有空格，前后可以有空格，
沒有RowNo的表格
終于到了回答這個問題的時候，我想問問大家，我們是如何在一個平面找到一個點的？答案就是需要這個點的橫坐標和縱坐標，同樣的，在一個表中，如何找到一個單元格？首先我們可以確定縱坐標，因為所有的列名都是已知的，關鍵就在于橫坐標的確定，采用RowNo來定位，大家一定會覺得似曾相識，因為它跟Excel左側的序號很像，但不代表只有數字才能作為橫坐標，只要值具有唯一性，即不重復，就可以作為橫坐標，
舉個例子，假設以下的表格是固定兩行，沒有RowNo，但是可以看出公司編號（BuCode）具有唯一性，那么BuCode就可以作為參考欄位，BuCode的值就是橫坐標，那么公式就可以寫成：
[SumDiffMonth1,F1136] = [GroupApprovalMonth1,F1136] - [Month1,F1136]
[SumDiffMonth1,F2056] = [GroupApprovalMonth1,F2056] - [Month1,F2056]
如果有RowNo，用RowNo做參考欄位時這樣寫：
[SumDiffMonth1,2] = [GroupApprovalMonth1,2] - [Month1,2]
[SumDiffMonth1,3] = [GroupApprovalMonth1,3] - [Month1,3]
跨資料源計算
何為跨資料源計算？用過Excel公式的朋友應該能看懂下面這個單元格的公式代表的意思，很明顯這個單元格的值是其他Sheet的資料經過運算后的值，跨資料源計算就是專門處理這樣的場景，
我們很少甚至不會在前臺做跨資料源計算，這里是想告訴大家如何書寫公式及呼叫AutoCalculate的方法，以便在“后臺用法”這一章節真正使用到它，
首先，為了取得其他資料源單元格的資料，我們需要拓展一下單元格，之前，我們的單元格是這樣的：[y,x]，暫且稱為二元單元格吧，還有這樣的單元格：[y]，成為一元單元格，現在，你會看到這樣的單元格：[外部資料源,y,x]，即三元單元格，三元單元格的出現令到AutoCalculate定位單元格的能力從二維拓展到三維，即不管你有多少表，AutoCalculate都能找到你要的資料，
這是一條使用了三元單元格的公式：
[Month1,4] = [OutputTax,Month1,7]
其中OutputTax是某個資料源的名稱，你可以任意取名，越簡潔越好，否則復雜的公式會被寫得很長，難以閱讀，
下面這條公式會從兩個資料源OutputTax和TaxRate取值：
[Month1,5] = [OutputTax,Month1,10] * (1 + [TaxRate,Month1,1] / 100)
我相信通過閱讀前面章節的內容，你已經能夠看懂下面公式的意思，其中前三行公式使用了外部資料源，并結合了區域公式的寫法，
是時候呼叫我們的計算方法了，為了演示效果，我添加了一個按鈕，并將方法寫在按鈕事件中
看看我們做了什么：
① 取得某個外部資料源outputTaxDatas
② 取得當前表格的資料源payableTaxDatas
③ 從資料庫獲取另一個外部資料源taxRateDatas
④ 這里是重點，先來看看AutoCalculate 的建構式，這里有兩個引數：
formulas：公式，一個陣列
options：可選引數，一個object物件
options有個屬性externalDatas，表示外部資料源，是一個陣列，因為資料可能有多個，每個陣列元素都是一個物件，有3個屬性：
name：外部資料源名稱，這里取什么名稱，對應公式中的外部資料源名稱
refField：參考欄位
datas：資料源
實體化AutoCalculate后，這里呼叫了一個新的方法calculate，它有2個引數：
gridDatas：需要重新計算的表格資料，是一個陣列
refField：參考欄位
AutoCalculate之所有支持所有的js表格控制元件以及能被后臺呼叫，就是借助于這個方法，因為不論是哪種js表格控制元件，都能夠提取出表格資料（純資料），資料通常是陣列形式，只要將這個陣列傳進來就可以了，
⑤ 呼叫calculate后，payableTaxDatas的值已經是運算過的最新值，現在將它系結到當前的表格即可，
運行程式后的界面：
點擊獲取資料后：
后臺用法
后臺呼叫AutoCalculate，我們需要用到V8引擎，還有一點很重要，后臺呼叫AutoCalculate也需要用到公式，我們之前的做法是將所有公式放在Extjs的Controller檔案中，如下圖：
為了方便后臺呼叫，我們將公式提取出來作為一個單獨的檔案
專案中對AutoCalculate后臺呼叫進行了封裝，使用非常簡單，
呼叫方法如圖：
還是分步決議：
① 保存當前表格的資料
② 獲取公式所在js檔案的目錄
③ 獲取兩個外部資料源
④ 呼叫封裝后的后臺方法，使用了第②步和第③步獲取的資料，其中FormulaExpression是公式運算式，即通過這個表達是來找到你提供的js檔案中的公式
⑤ 上一步回傳的newDatas已經是經過運算的最新資料，現在將這些資料保存到資料庫
注意事項
書寫公式時有兩點需要注意：
-
單元格中不允許出現空格
//正確寫法： [Month12,1] = [Month11,1] * 10 //錯誤寫法： [Month12,1 ] = [ Month11, 1] * 10 -
小數位數標記與小數位數之前不能有空格
//正確寫法： [Month12,1] #3 = [Month11,1] * 10 //錯誤寫法： [Month12,1] # 3 = [Month11,1] * 10
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/134948.html
標籤：其他
下一篇：elementUI-分頁功能實作