如何實(shí)現(xiàn)前端表格自動(dòng)計(jì)算

這篇文章將為大家詳細(xì)講解有關(guān)如何實(shí)現(xiàn)前端表格自動(dòng)計(jì)算，小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。

序言

當(dāng)我的團(tuán)隊(duì)進(jìn)行稅務(wù)系統(tǒng)模塊開(kāi)發(fā)的時(shí)候，我發(fā)現(xiàn)他們需要花費(fèi)80%的時(shí)間去解決計(jì)算問(wèn)題，尤其體現(xiàn)在表格（Grid）中的計(jì)算，這些時(shí)間花在：

• 寫前臺(tái)js代碼（因?yàn)橛脩粼诒砀裰械妮斎霑?huì)影響其他單元格，所以需要即時(shí)將運(yùn)算后的新值呈現(xiàn)給用戶看）

• 寫后臺(tái)代碼（因?yàn)橛脩魧?duì)表格數(shù)據(jù)的更改會(huì)影響其他表格，所以要在用戶點(diǎn)擊保存時(shí)更新受影響表格的數(shù)據(jù)）

• 實(shí)施修改計(jì)算方法，導(dǎo)致開(kāi)發(fā)者需要修改代碼

于是我調(diào)研了稅務(wù)其他模塊的功能，發(fā)現(xiàn)稅務(wù)系統(tǒng)大量使用表格控件，而其中或多或少都會(huì)涉及到計(jì)算問(wèn)題。而處理計(jì)算的方法，都是采用硬編碼。

計(jì)算，這個(gè)習(xí)以為常的編碼動(dòng)作，其實(shí)很容易讓人聯(lián)想到Excel中的公式，更何況需求文檔本身就是以Excel的形式提供的。當(dāng)我們?cè)谑褂肊xcel的時(shí)候，可以在單元格中設(shè)置公式，通過(guò)改變?cè)搭^單元格的值，Excel將自動(dòng)計(jì)算單元格公式，將結(jié)果值賦予目標(biāo)單元格。那么，我們是否可以參考這種模式，開(kāi)發(fā)者不再需要寫復(fù)雜難懂的計(jì)算邏輯，只需要根據(jù)實(shí)施提供的公式，將它們轉(zhuǎn)成某種格式的語(yǔ)句，再調(diào)用某種計(jì)算引擎產(chǎn)出結(jié)果，將結(jié)果呈現(xiàn)給用戶看或者持久化到數(shù)據(jù)庫(kù)？答案是肯定的，而這一切的核心就是自動(dòng)計(jì)算引擎——AutoCalculate。

作用

AutoCalculate是表格復(fù)雜運(yùn)算的解決方案，可以讓你省掉成百上千行的計(jì)算邏輯代碼，從此寫代碼就像寫Excel公式一般簡(jiǎn)單。

適用范圍

前臺(tái)：

適用于ElementUI表格、EasyUI Grid控件、ParamQuery Grid等所有js表格控件中帶有公式的復(fù)雜運(yùn)算

后臺(tái)：

適用，需要V8引擎

前臺(tái)用法

AutoCalculate由兩部分組成，分別是公式和計(jì)算引擎，公式是就是根據(jù)特定語(yǔ)法編寫的字符串，如：[Month22,1]#3 = [Month21,1] * 10，計(jì)算引擎即是AutoCalculate.js，負(fù)責(zé)解析公式。以下開(kāi)始介紹如何書(shū)寫公式。

單元格

假設(shè)有這樣的場(chǎng)景，單元格①=單元格②+單元格③，對(duì)應(yīng)的公式是：

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

先來(lái)看看[Month2,1]代表什么，首先，中括號(hào)[ ]代表一個(gè)單元格，Month2即“1月”對(duì)應(yīng)的列名，緊接著是一個(gè)逗號(hào),，后面的1代表RowNo = 1，以此類推，

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

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

所以我們可以用[y,x]來(lái)代表一個(gè)單元格，y即列名，也稱作縱坐標(biāo)， x即RowNo的值，也稱作橫坐標(biāo)

如果表格沒(méi)有RowNo列怎么辦？如想尋找答案，請(qǐng)繼續(xù)往下閱讀

讓公式生效

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

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

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

區(qū)域公式

實(shí)際上，除了1月，2月，3月……10月也存在類似的公式，即：

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

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

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

……
……
……

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

也就是說(shuō)我們需要寫10條這樣的公式，對(duì)于簡(jiǎn)單的場(chǎng)景來(lái)說(shuō)，這不成問(wèn)題，但是對(duì)于某些包含大量公式的表格，這種寫法存在一些弊端，比如容易寫錯(cuò)，還有，公式長(zhǎng)的時(shí)候也需要花費(fèi)較多時(shí)間才能寫完。所以，便有了區(qū)域公式。

觀察上面的公式可以發(fā)現(xiàn)，其實(shí)每條公式都可以用一條公式來(lái)代替，例如以下公式：

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

這里沒(méi)有明確的列名，只是用了一個(gè)占位符@，但它足以代表以上10條公式。這個(gè)時(shí)候，我們只需要在適當(dāng)?shù)奈恢醚a(bǔ)上列名就可以了，所以，最終的公式就是：

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

你需要將列名用,隔開(kāi)，并放置在大括號(hào){ }內(nèi)，如此，1條公式便相當(dāng)于10條公式。

占位符不僅僅可以用于縱坐標(biāo)，還可用于橫坐標(biāo)，如以下公式：

//公式1：
[YearTotal,3] = [Month2,3] + [Month3,3] + [Month4,3] + [Month5,3] + [Month6,3] + [Month7,3] + [Month7,3] + [Month8,3] + [Month9,3] + [Month20,3]

//公式2：
[YearTotal,4] = [Month2,4] + [Month3,4] + [Month4,4] + [Month5,4] + [Month6,4] + [Month7,4] + [Month7,4] + [Month8,4] + [Month9,4] + [Month20,4]

//公式3：
[YearTotal,5] = [Month2,5] + [Month3,5] + [Month4,5] + [Month5,5] + [Month6,5] + [Month7,5] + [Month7,5] + [Month8,5] + [Month9,5] + [Month20,5]

//公式4：
[YearTotal,6] = [Month2,6] + [Month3,6] + [Month4,6] + [Month5,6] + [Month6,6] + [Month7,6] + [Month7,6] + [Month8,6] + [Month9,6] + [Month20,6]

//公式5：
[YearTotal,2] = [Month2,2] + [Month3,2] + [Month4,2] + [Month5,2] + [Month6,2] + [Month7,2] + [Month7,2] + [Month8,2] + [Month9,2] + [Month20,2]

//公式6：
[YearTotal,7] = [Month2,7] + [Month3,7] + [Month4,7] + [Month5,7] + [Month6,7] + [Month7,7] + [Month7,7] + [Month8,7] + [Month9,7] + [Month20,7]

//公式7：
[YearTotal,9] = [Month2,9] + [Month3,9] + [Month4,9] + [Month5,9] + [Month6,9] + [Month7,9] + [Month7,9] + [Month8,9] + [Month9,9] + [Month20,9]

//公式8：
[YearTotal,12] = [Month2,12] + [Month3,12] + [Month4,12] + [Month5,12] + [Month6,12] + [Month7,12] + [Month7,12] + [Month8,12] + [Month9,12] + [Month20,12]

//公式9：
[YearTotal,13] = [Month2,13] + [Month3,13] + [Month4,13] + [Month5,13] + [Month6,13] + [Month7,13] + [Month7,13] + [Month8,13] + [Month9,13] + [Month20,13]

使用區(qū)域公式，可以寫成：

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

由此可見(jiàn)，區(qū)域公式為公式的書(shū)寫帶來(lái)了極大的便利。

支持js語(yǔ)法

在實(shí)際場(chǎng)景中，我們經(jīng)常會(huì)碰到一些復(fù)雜的公式，如下圖，單元格公式使用了Excel自帶的Max函數(shù)，對(duì)于這樣的公式，我們可以這樣寫：

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

如你所見(jiàn)，公式支持js語(yǔ)法，你可以在公式等號(hào)右邊放入一個(gè)js變量，甚至js函數(shù)，只要是js解析引擎認(rèn)識(shí)的語(yǔ)法，都被支持。

這里有個(gè)需要注意的地方，就是不可以將數(shù)組元素放入公式中，因?yàn)閖s的數(shù)組元素通常帶有“[ ]”符號(hào)，這與公式當(dāng)中的單元格表示符”[ ]”產(chǎn)生沖突，所以數(shù)組元素被禁止使用，請(qǐng)留意這一點(diǎn)。

[y]公式

接下來(lái)，帶大家看一看另外一種場(chǎng)景，如圖，存在這樣的關(guān)系：

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

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

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

這樣寫本身沒(méi)有錯(cuò)，但是我得提醒你，這里的行是不固定的，也就是說(shuō)表格有多少行完全取決于當(dāng)時(shí)的數(shù)據(jù)庫(kù)情況，有可能今天只有3行數(shù)據(jù)，明天會(huì)有5行，后天會(huì)有50行。我們不可能隨著行數(shù)增多而增加公式，所以對(duì)于這種行數(shù)不確定的表格，我們有一種新的寫法，我將它稱為[y]公式，因?yàn)楦胀ü较啾龋鼪](méi)有橫坐標(biāo)：

[column3] = [column2] - [column1]

只需要一行公式，AutoCalculate便會(huì)將公式應(yīng)用于指定列名下的所有行。

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

有時(shí)候，我們需要求某一列的和，雖然求某一列的和可能不是我們的最終目的，但卻是我們完成計(jì)算的必要步驟，如存在以下關(guān)系：

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

單元格②是GroupApprovedTotal列的合計(jì)值，我們用<列名>來(lái)表示，即：<GroupApprovedTotal>。加上這里的行不固定，需要用到[y]公式，所以公式應(yīng)該寫成：

[GroupApprovedTotalPercent] = [GroupApprovedTotal] / <GroupApprovedTotal>

我們知道，在除法中，除數(shù)是不可以為0的，所以正確的寫法應(yīng)該是：

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

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

默認(rèn)的，AutoCalculate會(huì)將計(jì)算結(jié)果保留2位小數(shù)，67%，即0.67，如果想得到66.91%，即0.6691，那就是需要保留4位小數(shù)，這時(shí)，你需要告訴AutoCalculate，你需要保留4位小數(shù)，所以，完整的寫法應(yīng)該是：

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

在公式的等號(hào)左邊，被賦值單元格的右邊，加“#”號(hào)，緊跟著寫上小數(shù)位數(shù)，注意，“#”和小數(shù)位數(shù)之間不能有空格，前后可以有空格。

沒(méi)有RowNo的表格

終于到了回答這個(gè)問(wèn)題的時(shí)候，我想問(wèn)問(wèn)大家，我們是如何在一個(gè)平面找到一個(gè)點(diǎn)的？答案就是需要這個(gè)點(diǎn)的橫坐標(biāo)和縱坐標(biāo)，同樣的，在一個(gè)表中，如何找到一個(gè)單元格？首先我們可以確定縱坐標(biāo)，因?yàn)樗械牧忻际且阎?，關(guān)鍵就在于橫坐標(biāo)的確定。采用RowNo來(lái)定位，大家一定會(huì)覺(jué)得似曾相識(shí)，因?yàn)樗鶨xcel左側(cè)的序號(hào)很像，但不代表只有數(shù)字才能作為橫坐標(biāo)。只要值具有唯一性，即不重復(fù)，就可以作為橫坐標(biāo)。

舉個(gè)例子，假設(shè)以下的表格是固定兩行，沒(méi)有RowNo，但是可以看出公司編號(hào)（BuCode）具有唯一性，那么BuCode就可以作為參考字段，BuCode的值就是橫坐標(biāo)，那么公式就可以寫成：

[SumDiffMonth2,F1136] = [GroupApprovalMonth2,F1136] - [Month2,F1136]
[SumDiffMonth2,F2056] = [GroupApprovalMonth2,F2056] - [Month2,F2056]

如果有RowNo，用RowNo做參考字段時(shí)這樣寫：

[SumDiffMonth2,2] = [GroupApprovalMonth2,2] - [Month2,2]
[SumDiffMonth2,3] = [GroupApprovalMonth2,3] - [Month2,3]

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

何為跨數(shù)據(jù)源計(jì)算？用過(guò)Excel公式的朋友應(yīng)該能看懂下面這個(gè)單元格的公式代表的意思。很明顯這個(gè)單元格的值是其他Sheet的數(shù)據(jù)經(jīng)過(guò)運(yùn)算后的值，跨數(shù)據(jù)源計(jì)算就是專門處理這樣的場(chǎng)景。

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

首先，為了取得其他數(shù)據(jù)源單元格的數(shù)據(jù)，我們需要拓展一下單元格，之前，我們的單元格是這樣的：[y,x]，暫且稱為二元單元格吧，還有這樣的單元格：[y]，成為一元單元格，現(xiàn)在，你會(huì)看到這樣的單元格：[外部數(shù)據(jù)源,y,x]，即三元單元格，三元單元格的出現(xiàn)令到AutoCalculate定位單元格的能力從二維拓展到三維，即不管你有多少表，AutoCalculate都能找到你要的數(shù)據(jù)。

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

[Month2,4] = [OutputTax,Month2,7]

其中OutputTax是某個(gè)數(shù)據(jù)源的名稱，你可以任意取名，越簡(jiǎn)潔越好，否則復(fù)雜的公式會(huì)被寫得很長(zhǎng)，難以閱讀。

下面這條公式會(huì)從兩個(gè)數(shù)據(jù)源OutputTax和TaxRate取值：

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

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

是時(shí)候調(diào)用我們的計(jì)算方法了，為了演示效果，我添加了一個(gè)按鈕，并將方法寫在按鈕事件中

看看我們做了什么：

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

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

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

④ 這里是重點(diǎn)，先來(lái)看看AutoCalculate 的構(gòu)造函數(shù)，這里有兩個(gè)參數(shù)：

formulas：公式，一個(gè)數(shù)組

options：可選參數(shù)，一個(gè)object對(duì)象

options有個(gè)屬性externalDatas，表示外部數(shù)據(jù)源，是一個(gè)數(shù)組，因?yàn)閿?shù)據(jù)可能有多個(gè)，每個(gè)數(shù)組元素都是一個(gè)對(duì)象，有3個(gè)屬性：

name：外部數(shù)據(jù)源名稱，這里取什么名稱，對(duì)應(yīng)公式中的外部數(shù)據(jù)源名稱

refField：參考字段

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

實(shí)例化AutoCalculate后，這里調(diào)用了一個(gè)新的方法calculate，它有2個(gè)參數(shù)：

gridDatas：需要重新計(jì)算的表格數(shù)據(jù)，是一個(gè)數(shù)組

refField：參考字段

AutoCalculate之所有支持所有的js表格控件以及能被后臺(tái)調(diào)用，就是借助于這個(gè)方法，因?yàn)椴徽撌悄姆Njs表格控件，都能夠提取出表格數(shù)據(jù)（純數(shù)據(jù)），數(shù)據(jù)通常是數(shù)組形式，只要將這個(gè)數(shù)組傳進(jìn)來(lái)就可以了。

⑤ 調(diào)用calculate后，payableTaxDatas的值已經(jīng)是運(yùn)算過(guò)的最新值，現(xiàn)在將它綁定到當(dāng)前的表格即可。

運(yùn)行程序后的界面：

點(diǎn)擊獲取數(shù)據(jù)后：

后臺(tái)用法

后臺(tái)調(diào)用AutoCalculate，我們需要用到V8引擎，還有一點(diǎn)很重要，后臺(tái)調(diào)用AutoCalculate也需要用到公式，我們之前的做法是將所有公式放在Extjs的Controller文件中，如下圖：

為了方便后臺(tái)調(diào)用，我們將公式提取出來(lái)作為一個(gè)單獨(dú)的文件

項(xiàng)目中對(duì)AutoCalculate后臺(tái)調(diào)用進(jìn)行了封裝，使用非常簡(jiǎn)單。

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

還是分步解析：

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

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

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

④ 調(diào)用封裝后的后臺(tái)方法，使用了第②步和第③步獲取的數(shù)據(jù)，其中FormulaExpression是公式表達(dá)式，即通過(guò)這個(gè)表達(dá)是來(lái)找到你提供的js文件中的公式

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

注意事項(xiàng)

書(shū)寫公式時(shí)有兩點(diǎn)需要注意：

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

/正確寫法：
[Month22,1] = [Month21,1] * 10

//錯(cuò)誤寫法：
[Month22,1 ] = [ Month21, 1] * 10

小數(shù)位數(shù)標(biāo)記與小數(shù)位數(shù)之前不能有空格

//正確寫法：
[Month22,1] #3 = [Month21,1] * 10

//錯(cuò)誤寫法：
[Month22,1] # 3 = [Month21,1] * 10

關(guān)于“如何實(shí)現(xiàn)前端表格自動(dòng)計(jì)算”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，使各位可以學(xué)到更多知識(shí)，如果覺(jué)得文章不錯(cuò)，請(qǐng)把它分享出去讓更多的人看到。