Say goodbye to hard coding and let your front-end table automatically calculate the instance code

Preface

When my team was developing the tax system module, I found that they needed to spend 80% of their time solving calculation problems, especially calculations in the grid. This time was spent on:

• Write the front-end js code (because the user's input in the table will affect other cells, the new value after calculation needs to be presented to the user immediately)
• Write background code (because the user's changes to the table data will affect other tables, so the data of the affected tables must be updated when the user clicks Save)
• Implementing changes to calculation methods requires developers to modify their code

So I researched the functions of other tax modules and found that the tax system makes extensive use of table controls, which more or less involve calculation issues. The methods for processing calculations are all hard-coded.

Calculation, this common coding action, is actually easy to associate with formulas in Excel, not to mention that the requirement document itself is provided in the form of Excel. When we use Excel, we can set formulas in cells. By changing the value of the source cell, Excel will automatically calculate the cell formula and assign the resulting value to the target cell. So, can we refer to this model, where developers no longer need to write complex and difficult-to-understand calculation logic, but only need to convert them into statements in a certain format based on the formulas provided by the implementation, and then call a certain calculation engine to produce the results, present the results to users or persist them in the database? The answer is yes, and the core of all this is the automatic calculation engine - AutoCalculate.

effect

AutoCalculate is a solution for complex table operations. It can save you hundreds or thousands of lines of calculation logic code. From now on, writing code is as easy as writing Excel formulas.

Scope of application

front desk:

Applicable to complex calculations with formulas in all js table controls such as ElementUI table, EasyUI Grid control, ParamQuery Grid, etc.

Backstage:

Applicable, requires V8 engine

Front desk usage

AutoCalculate consists of two parts: formula and calculation engine. The formula is a string written according to a specific syntax, such as [Month12,1]#3 = [Month11,1] * 10. The calculation engine is AutoCalculate.js, which is responsible for parsing the formula. The following is an introduction to how to write formulas.

Cell

Assume that there is such a scenario, cell ① = cell ② + cell ③, the corresponding formula is:

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

Let's first look at what [Month1,1] represents. First, the brackets [ ] represent a cell. Month1 is the column name corresponding to "January", followed by a comma , and the 1 behind it represents RowNo = 1, and so on.

[Month1,2] represents the cell with column "January" and RowNo = 2

[Month1,3] represents the cell with column "January" and RowNo = 3

So we can use [y,x] to represent a cell, y is the column name, also called the ordinate, x is the value of RowNo, also called the abscissa

What if the table does not have a RowNo column? If you want to find the answer, please continue reading

Make the formula work

//First import AutoCalculate.js
import AutoCalculate from '../components/AutoCalculate';
...

//Define an AutoCalculate instance, formulas is a formula array let autoCal = new AutoCalculate(formulas);

/* Call cal method* gridDatas (required): table data* refField (required): reference field, that is, the value of which field in the cell [y,x] is x*/
autoCal.cal(gridDatas, refField);

Area formula

In fact, in addition to January, February, March...October, there is a similar formula, namely:

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

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

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

…
…
…

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

That is to say, we need to write 10 such formulas. For simple scenarios, this is not a problem, but for some tables containing a large number of formulas, this writing method has some disadvantages, such as easy to make mistakes, and when the formula is long, it takes a long time to write it. So, there is the regional formula.

By observing the above formulas, we can find that each formula can be replaced by a formula, such as the following formula:

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

There is no explicit column name here, just a placeholder @, but it is enough to represent the above 10 formulas. At this time, we only need to fill in the column name in the appropriate position, so the final formula is:

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

You need to separate the column names with , and put them in curly braces { } , so that 1 formula is equivalent to 10 formulas.

Placeholders can be used not only for the ordinate, but also for the abscissa, such as in the following formula:

//Formula 1:
[YearTotal,3] = [Month1,3] + [Month2,3] + [Month3,3] + [Month4,3] + [Month5,3] + [Month6,3] + [Month7,3] + [Month8,3] + [Month9,3] + [Month10,3]

//Formula 2:
[YearTotal,4] = [Month1,4] + [Month2,4] + [Month3,4] + [Month4,4] + [Month5,4] + [Month6,4] + [Month7,4] + [Month8,4] + [Month9,4] + [Month10,4]

//Formula 3:
[YearTotal,5] = [Month1,5] + [Month2,5] + [Month3,5] + [Month4,5] + [Month5,5] + [Month6,5] + [Month7,5] + [Month8,5] + [Month9,5] + [Month10,5]

//Formula 4:
[YearTotal,6] = [Month1,6] + [Month2,6] + [Month3,6] + [Month4,6] + [Month5,6] + [Month6,6] + [Month7,6] + [Month8,6] + [Month9,6] + [Month10,6]

//Formula 5:
[YearTotal,2] = [Month1,2] + [Month2,2] + [Month3,2] + [Month4,2] + [Month5,2] + [Month6,2] + [Month7,2] + [Month8,2] + [Month9,2] + [Month10,2]

//Formula 6:
[YearTotal,7] = [Month1,7] + [Month2,7] + [Month3,7] + [Month4,7] + [Month5,7] + [Month6,7] + [Month7,7] + [Month8,7] + [Month9,7] + [Month10,7]

//Formula 7:
[YearTotal,9] = [Month1,9] + [Month2,9] + [Month3,9] + [Month4,9] + [Month5,9] + [Month6,9] + [Month7,9] + [Month8,9] + [Month9,9] + [Month10,9]

//Formula 8:
[YearTotal,12] = [Month1,12] + [Month2,12] + [Month3,12] + [Month4,12] + [Month5,12] + [Month6,12] + [Month7,12] + [Month8,12] + [Month9,12] + [Month10,12]

//Formula 9:
[YearTotal,13] = [Month1,13] + [Month2,13] + [Month3,13] + [Month4,13] + [Month5,13] + [Month6,13] + [Month7,13] + [Month8,13] + [Month9,13] + [Month10,13]

Using the area formula, this can be written as:

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

It can be seen that the regional formula brings great convenience to the writing of formulas.

Support js syntax

In actual scenarios, we often encounter some complex formulas. As shown in the figure below, the cell formula uses the Max function that comes with Excel. For such a formula, we can write it like this:

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

As you can see, the formula supports js syntax. You can put a js variable or even a js function on the right side of the equal sign in the formula. As long as the syntax is recognized by the js parsing engine, it is supported.

One thing to note here is that you cannot put array elements into the formula, because js array elements usually have the "[ ]" symbol, which conflicts with the cell representation symbol "[ ]" in the formula, so array elements are prohibited from being used. Please pay attention to this.

[y] Formula

Next, let's take a look at another scenario. As shown in the figure, there is such a relationship:

Cell① = Cell② - Cell③

You might quickly write the following formula:

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

There is nothing wrong with writing it this way, but I have to remind you that the rows here are not fixed, that is, the number of rows in the table depends entirely on the database situation at the time. It is possible that there are only 3 rows of data today, 5 rows tomorrow, and 50 rows the day after tomorrow. We can't add formulas as the number of rows increases, so for this kind of table with an uncertain number of rows, we have a new way of writing it. I call it the [y] formula because it has no horizontal axis compared to the ordinary formula:

[column3] = [column2] - [column1]

You only need one line of formula and AutoCalculate will apply the formula to all rows under the specified column name.

Total columns with decimal places

Sometimes, we need to find the sum of a column. Although finding the sum of a column may not be our ultimate goal, it is a necessary step to complete the calculation, such as the following relationship:

Cell ③ = Cell ① / Cell ②

Cell ② is the total value of the GroupApprovedTotal column, which we represent using <列名> , i.e.: <GroupApprovedTotal> . In addition, the rows here are not fixed, so the [y] formula is needed, so the formula should be written as:

[GroupApprovedTotalPercent] = [GroupApprovedTotal] / <GroupApprovedTotal>

We know that in division, the divisor cannot be 0, so the correct way to write it should be:

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

When you put this formula in your code and start the program, you should quickly find that the value you get is not accurate enough. For example, the value displayed in cell ③ above is 66.91%. If your cell ① and cell ② have the same values ​​as the above picture, your cell ③ is likely to be 67%. Why is that?

By default, AutoCalculate will keep 2 decimal places for the calculation result. 67% is 0.67. If you want to get 66.91% (0.6691), you need to keep 4 decimal places. At this time, you need to tell AutoCalculate that you need to keep 4 decimal places. Therefore, the complete writing should be:

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

On the left side of the equal sign in the formula, and on the right side of the cell to be assigned, add the “#” sign, followed by the number of decimal places. Note that there should be no space between the “#” and the decimal places, but there can be spaces before and after.

Table without RowNo

It's finally time to answer this question. I would like to ask everyone, how do we find a point on a plane? The answer is that we need the horizontal and vertical coordinates of this point. Similarly, how do we find a cell in a table? First, we can determine the vertical axis. Since all the column names are known, the key lies in determining the horizontal axis. Using RowNo for positioning may seem familiar to you, because it is very similar to the serial number on the left side of Excel, but it does not mean that only numbers can be used as the horizontal axis. As long as the value is unique, that is, not repeated, it can be used as the horizontal axis.

For example, suppose the following table has two fixed rows and no RowNo, but it can be seen that the company number (BuCode) is unique, so BuCode can be used as a reference field, and the value of BuCode is the horizontal axis, so the formula can be written as:

[SumDiffMonth1,F1136] = [GroupApprovalMonth1,F1136] - [Month1,F1136]
[SumDiffMonth1,F2056] = [GroupApprovalMonth1,F2056] - [Month1,F2056]

If there is a RowNo, write it like this when using RowNo as a reference field:

[SumDiffMonth1,2] = [GroupApprovalMonth1,2] - [Month1,2]
[SumDiffMonth1,3] = [GroupApprovalMonth1,3] - [Month1,3]

Cross-data source calculation

What is cross-data source computing? Friends who have used Excel formulas should be able to understand what the formula in the cell below means. Obviously, the value of this cell is the value after the data in other sheets are calculated. Cross-data source calculation is specifically designed to handle such scenarios.

We rarely or even never do cross-data source calculations in the foreground. Here we want to tell you how to write formulas and call AutoCalculate so that you can actually use it in the "Background Usage" chapter.

First, in order to obtain data from other data source cells, we need to expand the cells. Before, our cells were like this: [y,x], which we will call binary cells for now. There were also cells like this: [y], which became univariate cells. Now, you will see cells like this: [external data source, y, x], which are ternary cells. The appearance of ternary cells expands AutoCalculate's ability to locate cells from two dimensions to three dimensions, that is, no matter how many tables you have, AutoCalculate can find the data you want.

Here is a formula using a ternary cell:

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

OutputTax is the name of a data source. You can name it anything you want. The simpler the better. Otherwise, a complex formula will be too long to read.

The following formula takes values ​​from two data sources: OutputTax and TaxRate:

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

I believe that after reading the previous chapters, you can already understand the meaning of the following formulas, in which the first three rows of formulas use external data sources and combine them with the writing method of area formulas.

It's time to call our calculation method. To demonstrate the effect, I added a button and wrote the method in the button event.

See what we did:

① Get an external data source outputTaxDatas

② Get the data source payableTaxDatas of the current table

③ Get another external data source taxRateDatas from the database

④ Here is the key point. Let’s first look at the constructor of AutoCalculate. There are two parameters:

formulas: formula, an array

options: optional parameter, an object

options has a property externalDatas, which represents the external data source. It is an array. Because there may be multiple data, each array element is an object with 3 properties:

name: the name of the external data source. The name here corresponds to the external data source name in the formula.

refField: reference field

datas: data source

After instantiating AutoCalculate, a new method calculate is called here, which has 2 parameters:

gridDatas: table data that needs to be recalculated, an array

refField: reference field

The reason why AutoCalculate supports all js table controls and can be called by the background is with the help of this method, because no matter which js table control, the table data (pure data) can be extracted. The data is usually in array form, so just pass the array in.

⑤ After calling calculate, the value of payableTaxDatas is already the latest value after calculation. Now you can bind it to the current table.

The interface after running the program:

After clicking Get Data:

Backend usage

To call AutoCalculate in the background, we need to use the V8 engine. Another important point is that the formula is also needed to call AutoCalculate in the background. Our previous practice is to put all the formulas in the Extjs Controller file, as shown below:

To facilitate background calls, we extract the formula as a separate file

The AutoCalculate background call is encapsulated in the project, which is very easy to use.

The calling method is as shown in the figure:

Or step-by-step analysis:

① Save the data of the current table

② Get the directory of the js file where the formula is located

③ Obtain two external data sources

④ Call the encapsulated background method and use the data obtained in steps ② and ③. FormulaExpression is the formula expression, that is, this expression is used to find the formula in the js file you provided.

⑤ The newDatas returned in the previous step is the latest data after calculation. Now save this data to the database

Precautions

There are two points to note when writing formulas:

No spaces are allowed in cells

/Correct writing:
[Month12,1] = [Month11,1] * 10

//Wrong way of writing:
[Month12,1 ] = [ Month11, 1] * 10

There should be no space between the decimal place marker and the decimal place

//Correct way to write:
[Month12,1] #3 = [Month11,1] * 10

//Wrong way of writing:
[Month12,1] # 3 = [Month11,1] * 10

This concludes the article on Saying Goodbye to Hard Coding and Making Your Front-End Tables Automatically Calculate. For more information on automatic calculation of front-end tables, please search previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!