告別硬編碼月洛,讓你的前端表格自動計算

GitHub地址 | Demo | 博客

序言

當我的團隊進行稅務系統(tǒng)模塊開發(fā)的時候嚼黔,我發(fā)現(xiàn)他們需要花費80%的時間去解決計算問題唬涧,尤其體現(xiàn)在表格(Grid)中的計算碎节,這些時間花在:

  1. 寫前臺js代碼(因為用戶在表格中的輸入會影響其他單元格狮荔,所以需要即時將運算后的新值呈現(xiàn)給用戶看)
  2. 寫后臺代碼(因為用戶對表格數(shù)據(jù)的更改會影響其他表格殖氏,所以要在用戶點擊保存時更新受影響表格的數(shù)據(jù))
  3. 實施修改計算方法雅采,導致開發(fā)者需要修改代碼

于是我調(diào)研了稅務其他模塊的功能婚瓜,發(fā)現(xiàn)稅務系統(tǒng)大量使用表格控件巴刻,而其中或多或少都會涉及到計算問題胡陪。而處理計算的方法督弓,都是采用硬編碼愚隧。

計算狂塘,這個習以為常的編碼動作荞胡,其實很容易讓人聯(lián)想到Excel中的公式泪漂,更何況需求文檔本身就是以Excel的形式提供的萝勤。當我們在使用Excel的時候敌卓,可以在單元格中設置公式趟径,通過改變源頭單元格的值蜗巧,Excel將自動計算單元格公式惧蛹,將結(jié)果值賦予目標單元格香嗓。那么靠娱,我們是否可以參考這種模式像云,開發(fā)者不再需要寫復雜難懂的計算邏輯,只需要根據(jù)實施提供的公式迅诬,將它們轉(zhuǎn)成某種格式的語句腋逆,再調(diào)用某種計算引擎產(chǎn)出結(jié)果,將結(jié)果呈現(xiàn)給用戶看或者持久化到數(shù)據(jù)庫侈贷?答案是肯定的惩歉,而這一切的核心就是自動計算引擎——AutoCalculate

作用

AutoCalculate是表格復雜運算的解決方案俏蛮,可以讓你省掉成百上千行的計算邏輯代碼撑蚌,從此寫代碼就像寫Excel公式一般簡單搏屑。

適用范圍

前臺:

適用于ElementUI表格争涌、EasyUI Grid控件、ParamQuery Grid等所有js表格控件中帶有公式的復雜運算

后臺:

適用辣恋,需要V8引擎

前臺用法

AutoCalculate由兩部分組成亮垫,分別是公式和計算引擎,公式是就是根據(jù)特定語法編寫的字符串抑党,如:[Month12,1]#3 = [Month11,1] * 10包警,計算引擎即是AutoCalculate.js,負責解析公式底靠。以下開始介紹如何書寫公式害晦。

單元格

假設有這樣的場景,單元格①=單元格②+單元格③暑中,對應的公式是:

[Month1,1] = [Month1,2] + [Month1,3]
img

先來看看[Month1,1]代表什么壹瘟,首先,中括號[ ]代表一個單元格鳄逾,Month1即“1月”對應的列名稻轨,緊接著是一個逗號,,后面的1代表RowNo = 1雕凹,以此類推殴俱,

[Month1,2]代表列為“1月”且RowNo = 2的單元格

[Month1,3]代表列為“1月”且RowNo = 3的單元格

所以我們可以用[y,x]來代表一個單元格,y即列名枚抵,也稱作縱坐標线欲, x即RowNo的值,也稱作橫坐標

如果表格沒有RowNo列怎么辦汽摹?如想尋找答案李丰,請繼續(xù)往下閱讀

讓公式生效

//首先引入AutoCalculate.js
import AutoCalculate from '../components/AutoCalculate';
...

//定義一個AutoCalculate實例,formulas為公式數(shù)組
let autoCal = new AutoCalculate(formulas);

/* 調(diào)用cal方法
 * gridDatas(必填):表格數(shù)據(jù)
 * refField(必填):參考字段逼泣,即單元格[y,x]中x是哪個字段的值
 */
autoCal.cal(gridDatas, refField);

區(qū)域公式

實際上趴泌,除了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條這樣的公式秃励,對于簡單的場景來說,這不成問題吉捶,但是對于某些包含大量公式的表格莺治,這種寫法存在一些弊端,比如容易寫錯帚稠,還有,公式長的時候也需要花費較多時間才能寫完床佳。所以滋早,便有了區(qū)域公式。

觀察上面的公式可以發(fā)現(xiàn)砌们,其實每條公式都可以用一條公式來代替杆麸,例如以下公式:

[@,1] = [@,2] + [@,3]

這里沒有明確的列名,只是用了一個占位符@浪感,但它足以代表以上10條公式昔头。這個時候,我們只需要在適當?shù)奈恢醚a上列名就可以了影兽,所以揭斧,最終的公式就是:

{Month1, Month2, Month3, Month4, Month5, Month6, Month7, Month8, Month9, Month10}[@,1] = [@,2] + [@,3]

你需要將列名用,隔開,并放置在大括號{ }內(nèi)峻堰,如此讹开,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]

使用區(qū)域公式,可以寫成:

{2, 3, 4, 5, 6, 7, 9, 12, 13}[YearTotal,@] = [Month1,@] + [Month2,@] + [Month3,@] + [Month4,@] + [Month5,@] + [Month6,@] + [Month7,@] + [Month8,@] + [Month9,@] + [Month10,@]

由此可見镶蹋,區(qū)域公式為公式的書寫帶來了極大的便利成艘。

支持js語法

在實際場景中,我們經(jīng)常會碰到一些復雜的公式贺归,如下圖淆两,單元格公式使用了Excel自帶的Max函數(shù),對于這樣的公式牧氮,我們可以這樣寫:

[Month1,9] = ([Month1,6] - [Month1,7] - [Month1,8] > 0 ? [Month1,6] - [Month1,7] - [Month1,8] : 0) + [Month1,5]
img

如你所見琼腔,公式支持js語法,你可以在公式等號右邊放入一個js變量踱葛,甚至js函數(shù)丹莲,只要是js解析引擎認識的語法光坝,都被支持。

這里有個需要注意的地方甥材,就是不可以將數(shù)組元素放入公式中盯另,因為js的數(shù)組元素通常帶有“[ ]”符號,這與公式當中的單元格表示符”[ ]”產(chǎn)生沖突洲赵,所以數(shù)組元素被禁止使用鸳惯,請留意這一點。

[y]公式

接下來叠萍,帶大家看一看另外一種場景芝发,如圖,存在這樣的關(guān)系:

單元格① = 單元格② - 單元格③

你可能很快就寫出了以下公式:

[column3,1] = [column2,1] - [column1,1]
[column3,2] = [column2,2] - [column1,2]
img

這樣寫本身沒有錯苛谷,但是我得提醒你辅鲸,這里的行是不固定的,也就是說表格有多少行完全取決于當時的數(shù)據(jù)庫情況腹殿,有可能今天只有3行數(shù)據(jù)独悴,明天會有5行,后天會有50行锣尉。我們不可能隨著行數(shù)增多而增加公式刻炒,所以對于這種行數(shù)不確定的表格,我們有一種新的寫法自沧,我將它稱為[y]公式坟奥,因為跟普通公式相比,它沒有橫坐標:

[column3] = [column2] - [column1]

只需要一行公式拇厢,AutoCalculate便會將公式應用于指定列名下的所有行筏勒。

合計列與小數(shù)位數(shù)

有時候,我們需要求某一列的和旺嬉,雖然求某一列的和可能不是我們的最終目的管行,但卻是我們完成計算的必要步驟,如存在以下關(guān)系:

單元格③ = 單元格① / 單元格②

單元格②是GroupApprovedTotal列的合計值邪媳,我們用<列名>來表示捐顷,即:<GroupApprovedTotal>。加上這里的行不固定雨效,需要用到[y]公式迅涮,所以公式應該寫成:

[GroupApprovedTotalPercent] = [GroupApprovedTotal] / <GroupApprovedTotal>
img

我們知道,在除法中徽龟,除數(shù)是不可以為0的叮姑,所以正確的寫法應該是:

[GroupApprovedTotalPercent] = <GroupApprovedTotal> === 0 ? 0 : [GroupApprovedTotal] / <GroupApprovedTotal>

當你將這條公式放你的代碼,并啟動程序后,聰明的你應該很快發(fā)現(xiàn)传透,你得到的值不夠精確耘沼,如上面單元格③顯示的數(shù)值是66.91%,如果你的單元格①和單元格②跟上圖的數(shù)值相同朱盐,你的單元格③很可能是67%群嗤,這是為什么呢?

默認的兵琳,AutoCalculate會將計算結(jié)果保留2位小數(shù)狂秘,67%,即0.67躯肌,如果想得到66.91%者春,即0.6691,那就是需要保留4位小數(shù)清女,這時碧查,你需要告訴AutoCalculate,你需要保留4位小數(shù)校仑,所以,完整的寫法應該是:

[GroupApprovedTotalPercent]#4 = <GroupApprovedTotal> === 0 ? 0 : [GroupApprovedTotal] / <GroupApprovedTotal>

在公式的等號左邊传惠,被賦值單元格的右邊迄沫,加“#”號,緊跟著寫上小數(shù)位數(shù)卦方,注意羊瘩,“#”和小數(shù)位數(shù)之間不能有空格,前后可以有空格盼砍。

沒有RowNo的表格

終于到了回答這個問題的時候尘吗,我想問問大家,我們是如何在一個平面找到一個點的浇坐?答案就是需要這個點的橫坐標和縱坐標睬捶,同樣的,在一個表中近刘,如何找到一個單元格擒贸?首先我們可以確定縱坐標,因為所有的列名都是已知的觉渴,關(guān)鍵就在于橫坐標的確定介劫。采用RowNo來定位,大家一定會覺得似曾相識案淋,因為它跟Excel左側(cè)的序號很像座韵,但不代表只有數(shù)字才能作為橫坐標。只要值具有唯一性踢京,即不重復誉碴,就可以作為橫坐標宦棺。

舉個例子,假設以下的表格是固定兩行翔烁,沒有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]
img

跨數(shù)據(jù)源計算

何為跨數(shù)據(jù)源計算慨默?用過Excel公式的朋友應該能看懂下面這個單元格的公式代表的意思贩耐。很明顯這個單元格的值是其他Sheet的數(shù)據(jù)經(jīng)過運算后的值,跨數(shù)據(jù)源計算就是專門處理這樣的場景厦取。

img

我們很少甚至不會在前臺做跨數(shù)據(jù)源計算潮太,這里是想告訴大家如何書寫公式及調(diào)用AutoCalculate的方法,以便在“后臺用法”這一章節(jié)真正使用到它虾攻。

首先铡买,為了取得其他數(shù)據(jù)源單元格的數(shù)據(jù),我們需要拓展一下單元格霎箍,之前奇钞,我們的單元格是這樣的:[y,x],暫且稱為二元單元格吧漂坏,還有這樣的單元格:[y]景埃,成為一元單元格,現(xiàn)在顶别,你會看到這樣的單元格:[外部數(shù)據(jù)源,y,x]谷徙,即三元單元格,三元單元格的出現(xiàn)令到AutoCalculate定位單元格的能力從二維拓展到三維驯绎,即不管你有多少表完慧,AutoCalculate都能找到你要的數(shù)據(jù)。

這是一條使用了三元單元格的公式:

[Month1,4] = [OutputTax,Month1,7] 

其中OutputTax是某個數(shù)據(jù)源的名稱剩失,你可以任意取名骗随,越簡潔越好,否則復雜的公式會被寫得很長赴叹,難以閱讀鸿染。

下面這條公式會從兩個數(shù)據(jù)源OutputTax和TaxRate取值:

[Month1,5] = [OutputTax,Month1,10] * (1 + [TaxRate,Month1,1] / 100)

我相信通過閱讀前面章節(jié)的內(nèi)容,你已經(jīng)能夠看懂下面公式的意思乞巧,其中前三行公式使用了外部數(shù)據(jù)源涨椒,并結(jié)合了區(qū)域公式的寫法。

img

是時候調(diào)用我們的計算方法了,為了演示效果蚕冬,我添加了一個按鈕免猾,并將方法寫在按鈕事件中

<img src="http://www.fenghaitao.net/wp-content/uploads/2020/07/AutoCalculate_8.png" alt="img" style="zoom:80%;" />

看看我們做了什么:

① 取得某個外部數(shù)據(jù)源outputTaxDatas

② 取得當前表格的數(shù)據(jù)源payableTaxDatas

③ 從數(shù)據(jù)庫獲取另一個外部數(shù)據(jù)源taxRateDatas

④ 這里是重點,先來看看AutoCalculate 的構(gòu)造函數(shù)囤热,這里有兩個參數(shù):

<img src="http://www.fenghaitao.net/wp-content/uploads/2020/07/AutoCalculate_9.png" alt="img" style="zoom:80%;" />

formulas:公式猎提,一個數(shù)組

options:可選參數(shù),一個object對象

options有個屬性externalDatas旁蔼,表示外部數(shù)據(jù)源锨苏,是一個數(shù)組,因為數(shù)據(jù)可能有多個棺聊,每個數(shù)組元素都是一個對象伞租,有3個屬性:

name:外部數(shù)據(jù)源名稱,這里取什么名稱限佩,對應公式中的外部數(shù)據(jù)源名稱

refField:參考字段

datas:數(shù)據(jù)源

實例化AutoCalculate后葵诈,這里調(diào)用了一個新的方法calculate,它有2個參數(shù):

img

gridDatas:需要重新計算的表格數(shù)據(jù)祟同,是一個數(shù)組

refField:參考字段

AutoCalculate之所有支持所有的js表格控件以及能被后臺調(diào)用作喘,就是借助于這個方法,因為不論是哪種js表格控件晕城,都能夠提取出表格數(shù)據(jù)(純數(shù)據(jù))泞坦,數(shù)據(jù)通常是數(shù)組形式,只要將這個數(shù)組傳進來就可以了广辰。

⑤ 調(diào)用calculate后毡咏,payableTaxDatas的值已經(jīng)是運算過的最新值翩活,現(xiàn)在將它綁定到當前的表格即可。

運行程序后的界面:

img

點擊獲取數(shù)據(jù)后:

img

后臺用法

后臺調(diào)用AutoCalculate攒庵,我們需要用到V8引擎槽奕,還有一點很重要几睛,后臺調(diào)用AutoCalculate也需要用到公式,我們之前的做法是將所有公式放在Extjs的Controller文件中粤攒,如下圖:

img

為了方便后臺調(diào)用所森,我們將公式提取出來作為一個單獨的文件

img

項目中對AutoCalculate后臺調(diào)用進行了封裝,使用非常簡單夯接。

img

調(diào)用方法如圖:

<img src="http://www.fenghaitao.net/wp-content/uploads/2020/07/AutoCalculate_16.png" alt="img" style="zoom: 80%;" />

還是分步解析:

① 保存當前表格的數(shù)據(jù)

② 獲取公式所在js文件的目錄

③ 獲取兩個外部數(shù)據(jù)源

④ 調(diào)用封裝后的后臺方法焕济,使用了第②步和第③步獲取的數(shù)據(jù),其中FormulaExpression是公式表達式盔几,即通過這個表達是來找到你提供的js文件中的公式

⑤ 上一步返回的newDatas已經(jīng)是經(jīng)過運算的最新數(shù)據(jù)晴弃,現(xiàn)在將這些數(shù)據(jù)保存到數(shù)據(jù)庫

注意事項

書寫公式時有兩點需要注意:

  1. 單元格中不允許出現(xiàn)空格

    //正確寫法:
    [Month12,1] = [Month11,1] * 10
    
    //錯誤寫法:
    [Month12,1 ] = [ Month11, 1] * 10
    
  2. 小數(shù)位數(shù)標記與小數(shù)位數(shù)之前不能有空格

    //正確寫法:
    [Month12,1] #3 = [Month11,1] * 10
    
    //錯誤寫法:
    [Month12,1] # 3 = [Month11,1] * 10
    
最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個濱河市,隨后出現(xiàn)的幾起案子上鞠,更是在濱河造成了極大的恐慌际邻,老刑警劉巖,帶你破解...
    沈念sama閱讀 217,277評論 6 503
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件芍阎,死亡現(xiàn)場離奇詭異世曾,居然都是意外死亡,警方通過查閱死者的電腦和手機谴咸,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 92,689評論 3 393
  • 文/潘曉璐 我一進店門轮听,熙熙樓的掌柜王于貴愁眉苦臉地迎上來,“玉大人寿冕,你說我怎么就攤上這事蕊程。” “怎么了驼唱?”我有些...
    開封第一講書人閱讀 163,624評論 0 353
  • 文/不壞的土叔 我叫張陵藻茂,是天一觀的道長。 經(jīng)常有香客問我玫恳,道長辨赐,這世上最難降的妖魔是什么? 我笑而不...
    開封第一講書人閱讀 58,356評論 1 293
  • 正文 為了忘掉前任京办,我火速辦了婚禮掀序,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘惭婿。我一直安慰自己不恭,他們只是感情好,可當我...
    茶點故事閱讀 67,402評論 6 392
  • 文/花漫 我一把揭開白布财饥。 她就那樣靜靜地躺著换吧,像睡著了一般。 火紅的嫁衣襯著肌膚如雪钥星。 梳的紋絲不亂的頭發(fā)上沾瓦,一...
    開封第一講書人閱讀 51,292評論 1 301
  • 那天,我揣著相機與錄音谦炒,去河邊找鬼贯莺。 笑死,一個胖子當著我的面吹牛宁改,可吹牛的內(nèi)容都是我干的缕探。 我是一名探鬼主播,決...
    沈念sama閱讀 40,135評論 3 418
  • 文/蒼蘭香墨 我猛地睜開眼还蹲,長吁一口氣:“原來是場噩夢啊……” “哼撕蔼!你這毒婦竟也來了豁鲤?” 一聲冷哼從身側(cè)響起,我...
    開封第一講書人閱讀 38,992評論 0 275
  • 序言:老撾萬榮一對情侶失蹤鲸沮,失蹤者是張志新(化名)和其女友劉穎琳骡,沒想到半個月后,有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體讼溺,經(jīng)...
    沈念sama閱讀 45,429評論 1 314
  • 正文 獨居荒郊野嶺守林人離奇死亡楣号,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 37,636評論 3 334
  • 正文 我和宋清朗相戀三年,在試婚紗的時候發(fā)現(xiàn)自己被綠了怒坯。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片炫狱。...
    茶點故事閱讀 39,785評論 1 348
  • 序言:一個原本活蹦亂跳的男人離奇死亡,死狀恐怖剔猿,靈堂內(nèi)的尸體忽然破棺而出视译,到底是詐尸還是另有隱情,我是刑警寧澤归敬,帶...
    沈念sama閱讀 35,492評論 5 345
  • 正文 年R本政府宣布酷含,位于F島的核電站,受9級特大地震影響汪茧,放射性物質(zhì)發(fā)生泄漏椅亚。R本人自食惡果不足惜,卻給世界環(huán)境...
    茶點故事閱讀 41,092評論 3 328
  • 文/蒙蒙 一舱污、第九天 我趴在偏房一處隱蔽的房頂上張望呀舔。 院中可真熱鬧,春花似錦扩灯、人聲如沸媚赖。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,723評論 0 22
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽惧磺。三九已至,卻和暖如春丧失,著一層夾襖步出監(jiān)牢的瞬間豺妓,已是汗流浹背惜互。 一陣腳步聲響...
    開封第一講書人閱讀 32,858評論 1 269
  • 我被黑心中介騙來泰國打工布讹, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留,地道東北人训堆。 一個月前我還...
    沈念sama閱讀 47,891評論 2 370
  • 正文 我出身青樓描验,卻偏偏與公主長得像,于是被迫代替她去往敵國和親坑鱼。 傳聞我的和親對象是個殘疾皇子膘流,可洞房花燭夜當晚...
    茶點故事閱讀 44,713評論 2 354