Say goodbye to hard-coded example code that lets your front-end tables automatically calculate

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 the calculations in the grid. This time was spent on:

So I investigated the functions of other tax modules and found that the tax system uses a large number of table controls, which more or less involve calculation problems. The calculation methods are all hard-coded.

Calculation, this common coding action, is actually very reminiscent of the formulas in Excel, not to mention that the requirements 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 result value to the target cell. So, can we refer to this model? Developers no longer need to write complicated and difficult calculation logic. They only need to convert them into statements in a certain format according to the formulas provided by the implementation, and then call a certain calculation engine to output the results. , present the results to the user or persist them to the database? The answer is yes, and the core of it all is the automatic calculation engine-AutoCalculate.

effect

AutoCalculate is a solution for complex table calculations, allowing you to save 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:

Suitable for complex operations with formulas in all js table controls such as ElementUI tables, EasyUI Grid controls, ParamQuery Grid, etc.

Backstage:

Applicable, requires V8 engine

Front desk usage

AutoCalculate consists of two parts, namely the formula and the 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. Responsible for parsing formulas. Let’s start with how to write formulas.

cell

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

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

Let’s first take a look at what [Month1,1] represents. First, the square brackets [ ] represent a cell, Month1 is the column name corresponding to “January”, followed by a comma , , and the following 1 represents RowNo = 1. And so on,

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

[Month1,3] represents the cell whose column is "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 has no RowNo column? If you want to find the answer, please continue reading

Let the formula take effect

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

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

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

area formula

In fact, in addition to January, February, March... there is also a similar formula for October, 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]

In other words, we need to write 10 such formulas. For simple scenarios, this is not a problem. However, for some tables containing a large number of formulas, this writing method has some disadvantages, such as it is easy to make mistakes, and the formula is long. It also takes more time to finish writing. Therefore, there is the area formula.

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

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

There is no clear column name here, just a placeholder @ is used, but it is enough to represent the above 10 formulas. At this time, we only need to fill in the column names at the appropriate positions, 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 place them within curly brackets { } , so that 1 formula is equivalent to 10 formulas.

Placeholders can be used not only for the ordinate, but also for the abscissa, as shown 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 area formula brings great convenience to the writing of formulas.

Support js syntax

In actual scenarios, we often encounter some complex formulas, as shown 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 js function on the right side of the equal sign of the formula. As long as it is a syntax recognized by the js parsing engine, it is supported.

One thing to note here is that you cannot put array elements into formulas, because js array elements usually have "[ ]" symbols, which conflicts with the cell indicator "[ ]" in formulas, so Array elements are prohibited, please be aware of this.

[y]Formula

Next, let’s take a look at another scenario, as shown in the figure, where 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 this way, but I have to remind you that the rows here are not fixed. That is to say, the number of rows in the table depends entirely on the database situation at that time. It is possible that there are only 3 rows of data today, 5 rows tomorrow, and the day after tomorrow. There will be 50 lines. It is impossible for us to 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. I call it the [y] formula, because compared with ordinary formulas, it has no horizontal coordinate:

[column3] = [column2] - [column1]

With just one row of formulas, AutoCalculate will apply the formula to all rows under the specified column name.

Total columns and decimal places

Sometimes, we need to find the sum of a certain column. Although finding the sum of a certain column may not be our ultimate goal, it is a necessary step for us to complete the calculation. For example, the following relationship exists:

Cell ③ = Cell ① / Cell ②

Cell ② is the total value of GroupApprovedTotal column, which we use <列名> to represent, that is: <GroupApprovedTotal> . In addition, the rows here are not fixed and the [y] formula needs to be used, 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 be smart and 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 ② has the same value as the picture above. Your cell ③ is probably 67%. Why is this?

By default, AutoCalculate will keep the calculation result to 2 decimal places, 67%, which is 0.67. If you want to get 66.91%, which is 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>

To the left of the equal sign in the formula and to the right of the assigned cell, add a "#" sign, followed by the number of decimal places. Note that there cannot be a space between the "#" and the number of decimal places, and there can be spaces before and after.

Table without RowNo

It's finally time to answer this question. I want to ask everyone, how do we find a point on a plane? The answer is that we need the abscissa and ordinate of this point. Similarly, how to find a cell in a table? First we can determine the ordinate because all column names are known. The key lies in determining the abscissa. If you use RowNo for positioning, you will definitely feel familiar, 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 abscissa. As long as the value is unique, that is, not repeated, it can be used as the abscissa.

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

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

If there is RowNo, use RowNo as the reference field and write like this:

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

Compute across data sources

What is cross-data source computing? Friends who have used Excel formulas should be able to understand what the formula in the cell below represents. Obviously, the value of this cell is the calculated value of other Sheet data. 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 I want to tell you how to write formulas and call AutoCalculate so that you can actually use it in the "Backend Usage" chapter.

First, in order to obtain data from other data source cells, we need to expand the cells. Before, our cells looked like this: [y,x]. Let’s call them binary cells for now. There are also cells like this :[y], becomes a single cell. Now, you will see a single cell like this Cell: [External Data Source, y, Find the data you want.

This is a formula using three-element cells:

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

Among them, OutputTax is the name of a certain data source. You can name it arbitrarily. The more concise the better, otherwise complex formulas will be written very long and difficult to read.

The following formula will take values ​​from two data sources, OutputTax and TaxRate:

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

I believe that by reading the content of the previous chapters, you can already understand the meaning of the following formulas. The first three rows of formulas use external data sources and are combined with the writing method of regional 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 outputTaxDatas from an external data source

② Get the data source payableTaxDatas of the current table

③ Obtain another external data source taxRateDatas from the database

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

formulas: formula, an array

options: optional parameter, an object object

Options has an attribute externalDatas, which represents an external data source. It is an array, because there may be multiple data. Each array element is an object and has 3 attributes:

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

refField: reference field

datas: data source

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

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

refField: reference field

AutoCalculate supports all js table controls and can be called in the background with the help of this method, because no matter what kind of js table control it is, it can extract table data (pure data). The data is usually in the form of an array. As long as this Just pass in the array.

⑤ After calling calculate, the value of payableTaxDatas is already the latest calculated value. Now just bind it to the current table.

The interface after running the program:

After clicking to get data:

Backend usage

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

In order to facilitate background calls, we extract the formula as a separate file

The AutoCalculate background call is encapsulated in the project and is very simple to use.

The calling method is as shown in the figure:

Or analyze it step by step:

① 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, using the data obtained in steps ② and ③, where FormulaExpression is the formula expression, that is, using this expression to find the formula in the js file you provided

⑤ The newDatas returned in the previous step are already the latest data after calculation. Now save these data to the database.

Things to note

There are two points to note when writing formulas:

No spaces are allowed in cells

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

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

There must be no spaces before the decimal place mark and the decimal place.

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

//wrong writing:
[Month12,1] # 3 = [Month11,1] * 10

This concludes this article about saying goodbye to hard coding and allowing your front-end tables to automatically calculate. For more information on automatic front-end table calculations, please search previous articles on downcodes.com or continue to browse the related articles below. I hope you will do so in the future. Support downcodes.com a lot!