温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

如何实现前端表格自动计算

发布时间:2021-05-20 09:56:56 来源:亿速云 阅读:876 作者:小新 栏目:移动开发

这篇文章将为大家详细讲解有关如何实现前端表格自动计算,小编觉得挺实用的,因此分享给大家做个参考,希望大家阅读完这篇文章后可以有所收获。

序言

当我的团队进行税务系统模块开发的时候,我发现他们需要花费80%的时间去解决计算问题,尤其体现在表格(Grid)中的计算,这些时间花在:

  • 写前台js代码(因为用户在表格中的输入会影响其他单元格,所以需要即时将运算后的新值呈现给用户看)

  • 写后台代码(因为用户对表格数据的更改会影响其他表格,所以要在用户点击保存时更新受影响表格的数据)

  • 实施修改计算方法,导致开发者需要修改代码

于是我调研了税务其他模块的功能,发现税务系统大量使用表格控件,而其中或多或少都会涉及到计算问题。而处理计算的方法,都是采用硬编码。

计算,这个习以为常的编码动作,其实很容易让人联想到Excel中的公式,更何况需求文档本身就是以Excel的形式提供的。当我们在使用Excel的时候,可以在单元格中设置公式,通过改变源头单元格的值,Excel将自动计算单元格公式,将结果值赋予目标单元格。那么,我们是否可以参考这种模式,开发者不再需要写复杂难懂的计算逻辑,只需要根据实施提供的公式,将它们转成某种格式的语句,再调用某种计算引擎产出结果,将结果呈现给用户看或者持久化到数据库?答案是肯定的,而这一切的核心就是自动计算引擎——AutoCalculate。

作用

AutoCalculate是表格复杂运算的解决方案,可以让你省掉成百上千行的计算逻辑代码,从此写代码就像写Excel公式一般简单。

适用范围

前台:

适用于ElementUI表格、EasyUI Grid控件、ParamQuery Grid等所有js表格控件中带有公式的复杂运算

后台:

适用,需要V8引擎

前台用法

AutoCalculate由两部分组成,分别是公式和计算引擎,公式是就是根据特定语法编写的字符串,如:[Month22,1]#3 = [Month21,1] * 10,计算引擎即是AutoCalculate.js,负责解析公式。以下开始介绍如何书写公式。

单元格

假设有这样的场景,单元格①=单元格②+单元格③,对应的公式是:

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

如何实现前端表格自动计算

先来看看[Month2,1]代表什么,首先,中括号[ ]代表一个单元格,Month2即“1月”对应的列名,紧接着是一个逗号,,后面的1代表RowNo = 1,以此类推,

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

[Month2,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月也存在类似的公式,即:

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

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

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

……
……
……

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

也就是说我们需要写10条这样的公式,对于简单的场景来说,这不成问题,但是对于某些包含大量公式的表格,这种写法存在一些弊端,比如容易写错,还有,公式长的时候也需要花费较多时间才能写完。所以,便有了区域公式。

观察上面的公式可以发现,其实每条公式都可以用一条公式来代替,例如以下公式:

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

这里没有明确的列名,只是用了一个占位符@,但它足以代表以上10条公式。这个时候,我们只需要在适当的位置补上列名就可以了,所以,最终的公式就是:

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

你需要将列名用,隔开,并放置在大括号{ }内,如此,1条公式便相当于10条公式。

占位符不仅仅可以用于纵坐标,还可用于横坐标,如以下公式:

//公式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]

使用区域公式,可以写成:

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

由此可见,区域公式为公式的书写带来了极大的便利。

支持js语法

在实际场景中,我们经常会碰到一些复杂的公式,如下图,单元格公式使用了Excel自带的Max函数,对于这样的公式,我们可以这样写:

[Month2,9] = ([Month2,6] - [Month2,7] - [Month2,8] > 0 ? [Month2,6] - [Month2,7] - [Month2,8] : 0) + [Month2,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的值就是横坐标,那么公式就可以写成:

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

如果有RowNo,用RowNo做参考字段时这样写:

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

如何实现前端表格自动计算

跨数据源计算

何为跨数据源计算?用过Excel公式的朋友应该能看懂下面这个单元格的公式代表的意思。很明显这个单元格的值是其他Sheet的数据经过运算后的值,跨数据源计算就是专门处理这样的场景。

如何实现前端表格自动计算

我们很少甚至不会在前台做跨数据源计算,这里是想告诉大家如何书写公式及调用AutoCalculate的方法,以便在“后台用法”这一章节真正使用到它。

首先,为了取得其他数据源单元格的数据,我们需要拓展一下单元格,之前,我们的单元格是这样的:[y,x],暂且称为二元单元格吧,还有这样的单元格:[y],成为一元单元格,现在,你会看到这样的单元格:[外部数据源,y,x],即三元单元格,三元单元格的出现令到AutoCalculate定位单元格的能力从二维拓展到三维,即不管你有多少表,AutoCalculate都能找到你要的数据。

这是一条使用了三元单元格的公式:

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

其中OutputTax是某个数据源的名称,你可以任意取名,越简洁越好,否则复杂的公式会被写得很长,难以阅读。

下面这条公式会从两个数据源OutputTax和TaxRate取值:

[Month2,5] = [OutputTax,Month2,10] * (1 + [TaxRate,Month2,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已经是经过运算的最新数据,现在将这些数据保存到数据库

注意事项

书写公式时有两点需要注意:

单元格中不允许出现空格

/正确写法:
[Month22,1] = [Month21,1] * 10

//错误写法:
[Month22,1 ] = [ Month21, 1] * 10

小数位数标记与小数位数之前不能有空格

//正确写法:
[Month22,1] #3 = [Month21,1] * 10

//错误写法:
[Month22,1] # 3 = [Month21,1] * 10

关于“如何实现前端表格自动计算”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,使各位可以学到更多知识,如果觉得文章不错,请把它分享出去让更多的人看到。

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI