Bid farewell to hard coding and let your front-end table calculate automatically

Time:2021-3-23

GitHub | Demo | Blog | Original link

preface

When my team developed the tax system module, I found that they needed to spend 80% of their time to solve the calculation problems, especially in the calculation in the grid

  1. Write the foreground 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)
  2. Write background code (because the user’s changes to the table data will affect other tables, update the data of the affected table when the user clicks save)
  3. The implementation of modified calculation method leads to the need for developers to modify the code

So I investigated the functions of other tax modules, and found that the tax system uses a lot of form controls, which more or less involve calculation problems. The method of processing calculation is hard coding.

Calculation, the usual coding action, is easy to associate with the formula in Excel, not to mention the requirement document itself is provided in the form of Excel. When we use Excel, we can set the formula in the cell. 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 mode, developers no longer need to write complex and difficult calculation logic, just need to convert them into statements in a certain format according to the formulas provided by the implementation, and then call a calculation engine to produce results, and present the results to users or persist them to the database? The answer is yes, and the core of all this is the automatic computing engine——AutoCalculate

effect

Autocalculate is a solution for complex table operation, which can save hundreds of lines of calculation logic code. From then on, writing code is as simple as writing excel formula.

Scope of application

Reception:

It is suitable for complex operations with formulas in all JS table controls such as elementui table, easyUI grid control and paramquery grid

backstage:

Applicable, requires V8 engine

Foreground usage

Autocalculate is composed of two parts: formula and calculation engine. Formula is a string written according to a specific syntax, such as: [month12,1] # – 3 = [month11,1] * 10. Calculation engine is AutoCalculate.js , which is responsible for resolving the formula. Here’s how to write a formula.

Cell

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

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

img

Let’s have a look first[Month1,1]What does it stand for? First of all, brackets[ ]Represents a cell. Month1 is the column name corresponding to “January”, followed by a comma,, followed by 1 for rowno = 1, and so on,

[Month1,2]Represents the cell listed as “January” and rowno = 2

[Month1,3]Represents the cell listed as “January” and rowno = 3

So we can use it[y,x]To represent a cell, y is the column name, also known as the ordinate, and X is the rowno value, also known as the abscissa

What if the table doesn’t have a rowno column? If you want to find out, please read on

Let the formula work

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

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

/*Call cal method
 *Griddata (required): table data
 *Reffield (required): refers to the value of the field X in cell [y, x]
 */
autoCal.cal(gridDatas, refField);

Regional formula

In fact, except January, February, March A similar formula exists in October

[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 scenes, 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 it takes more time to finish the formula when it is long. So, there is a 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, just a place holder @, but it is enough to represent the above 10 formulas. At this time, we just 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 use,Separate and place in braces{ }In this way, one formula is equivalent to 10 formulas.

The placeholder can be used not only for the ordinate, but also for the abscissa, such as 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 region formula, it can be written as follows:

{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 regional formula brings great convenience for formula writing.

Support JS syntax

In the actual scene, we often encounter some complex formulas, as shown in the figure below. The cell formula uses the max function of Excel. For such a formula, we can write as follows:

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

img

As you can see, formulas support JS syntax. You can put a JS variable or even a JS function to the right of the formula equal sign. As long as the syntax recognized by JS parsing engine is supported.

One thing to note here is that you can’t put the array elements into the formula, because the array elements of JS usually have “[]” symbol, which conflicts with the “[]” symbol in the formula, so the array elements are prohibited. Please note this.

[y] Formula

Let’s look at another kind of relationship

Cell ① = cell ② – cell ③

You may soon have written the following formula:

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

img

There is nothing wrong with writing like this, 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. There may be only 3 rows of data today, 5 rows tomorrow and 50 rows the day after tomorrow. It is impossible for us to add formulas with the increase of the number of rows, so we have a new way to write this kind of table with uncertain number of rows. I call it [y] formula, because compared with ordinary formula, it has no abscissa:

[column3] = [column2] - [column1]

With only one row of formula, autocalculate applies 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 3 = cell 1 / cell 2

Cell 2 isGroupApprovedTotalColumn, we useIn other words:. In addition, the line here is not fixed, so we need to use the formula [y], so the formula should be written as follows:

[GroupApprovedTotalPercent] = [GroupApprovedTotal] /

img

We know that in division, divisor can not be zero, so the correct way to write it is as follows:

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

When you put this formula into your code and start the program, you should quickly find that the value you get is not accurate enough. For example, the value shown in cell 3 above is 66.91%. If your cell 1 and cell 2 are the same as the above figure, your cell 3 is likely to be 67%. Why?

By default, autocalculate will keep 2 decimal places, 67%, or 0.67. If you want to get 66.91%, or 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

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

Add “#” to the left of the equal sign of the formula and the right of the assigned cell, and then write the decimal places. Note that there can be no spaces between “#” and the decimal places, and there can be spaces before and after.

Table without rowno

Finally, it’s time to answer this question. How do we find a point in a plane? The answer is to need the abscissa and ordinate of this point. Similarly, how to find a cell in a table? First of all, we can determine the ordinate, because all column names are known. The key is to determine the abscissa. Using rowno to locate, you will 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 abscissa. As long as the value is unique, that is, it is not repeated, it can be used as abscissa.

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. Then, bucode can be used as a reference field, and the value of bucode is the abscissa. Then, the formula can be written as follows:

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

If there is rowno, when using rowno as the reference field, write as follows:

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

img

Cross data source computing

What is cross data source computing? Friends who have used Excel formula should be able to understand the meaning of the formula in the following cell. Obviously, the value of this cell is the calculated value of the data of other sheets. Cross data source calculation is specifically used to deal with such scenarios.

img

We rarely or even will not do cross data source calculation in the foreground. Here we want to tell you how to write formulas and call autocalculate methods, so that we can really use it in the chapter “background usage”.

First of all, in order to obtain the data of cells from other data sources, we need to expand the cells. Before, our cells were like this: [y, x], let’s call them binary cells for the time being. There are also such cells: [y], which become unary cells. Now, you will see such cells: [external data source, y, x] 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.

This is a formula that uses ternary cells:

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

Among them, outputtax is the name of a data source. You can name it at will. The more concise, the better. Otherwise, complex formulas will be written very long and difficult 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 by reading the previous chapters, you can understand the meaning of the following formula, in which the first three lines of formula use external data sources, combined with the regional formula.

img

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

img

Look what we’ve done:

① Get an external data source, outputtaxdata

② Gets the data source of the current table, payabletaxdata

③ Get another external data source taxratedata from the database

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

img

Formula: formula, an array

Options: optional parameter, an object object

Options has an attribute externaldata, which represents an external data source and is an array. Because there may be multiple data, each array element is an object and has three attributes

Name: the name of the external data source. What is the name here? It corresponds to the name of the external data source in the formula

Reffield: reference field

Data: data source

After instantiating autocalculate, a new method, calculate, is called here. It has two parameters:

img

Griddata: the table data to be recalculated is an array

Reffield: reference field

Autocalculate supports all JS table controls and can be called in the background. It is with this method, because no matter what kind of JS table control is, it can extract table data (pure data). The data is usually in the form of an array, as long as the array is passed in.

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

Interface after running the program:

img

Click to get data:

img

Background usage

When we call autocalculate in the background, we need to use V8 engine. Another important point is that we also need to use formulas when we call autocalculate in the background. Our previous practice is to put all formulas in the controller file of extjs, as shown in the following figure:

img

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

img

The project encapsulates the background call of autocalculate, which is very simple to use.

img

The calling method is as follows:

img

Or step by step

① Save the data of the current table

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

③ Get two external data sources

④ Call the encapsulated background method, using the data obtained in step 2 and step 3, where formulaexpression is a formula expression, that is, to find the formula in the JS file you provided

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

matters needing attention

There are two points to note when writing formulas

  1. Spaces are not allowed in cells

    //Correct writing:
    [Month12,1] = [Month11,1] * 10
    
    //Wrong writing:
    [Month12,1 ] = [ Month11, 1] * 10
  2. The decimal mark and decimal number cannot be preceded by spaces

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

Recommended Today

Review of SQL Sever basic command

catalogue preface Installation of virtual machine Commands and operations Basic command syntax Case sensitive SQL keyword and function name Column and Index Names alias Too long to see? Space Database connection Connection of SSMS Connection of command line Database operation establish delete constraint integrity constraint Common constraints NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY DEFAULT […]