link

  • GitHub
  • Demo
  • blog
  • The original link

The preface

When my team developed modules for the tax system, I found that they needed to spend 80% of their time to solve calculation problems, especially in the calculation of Grid, which was spent in:

  1. Write foreground JS code (since the user’s input in the table affects other cells, the new values need to be displayed to the user immediately)
  2. Write background code (since a user’s changes to table data affect other tables, update affected table data when the user clicks Save)
  3. Implementing a modified calculation method causes the developer to change the code

Therefore, I investigated the functions of other tax modules and found that the tax system uses a large number of form controls, which more or less involve calculation problems. And the way to do that is hard coded.

Computation, a routine coding activity, is reminiscent of formulas in Excel, especially since the requirements document itself is provided in Excel. When we use Excel, we can set the formula in the cell. By changing the value of the source cell, Excel will automatically evaluate the cell formula and assign the resulting value to the target cell. So, can we refer to this model, where the developer no longer needs to write complicated calculation logic, but only needs to convert the formulas provided by the implementation into statements of a certain format, and then call a calculation engine to produce results, which will be presented to users or persisted in the database? The answer is yes, and at the heart of it all is the automated computing engine, AutoCalculate.

role

AutoCalculate is a complex spreadsheet solution that saves you hundreds of lines of calculation logic and makes writing code as easy as writing Excel formulas.

Scope of application

Front desk:

Suitable for ElementUI table, EasyUI Grid control, ParamQuery Grid and other JS table controls with complex operations with formulas

Background:

Yes, requires a V8 engine

The front desk usage

The AutoCalculate consists of two parts, the formula and the calculation engine. The formula is a character string prepared according to a specific syntax, for example, [Month12,1]#3 = [Month11,1] * 10. The calculation engine is AutoCalculate. Here’s how to write the formula.

The cell

Suppose we have a scenario where cell ①= cell ②+ cell ③, the corresponding formula is:

[Month1,1] = [Month1,2] + [Month1,3]
Copy the code

First, the brackets “[]” represent a cell. Month1 is the name of the column for “January”, followed by a comma, followed by “1” to represent RowNo = 1, and so on.

[Month1,2] indicates the cell with January and RowNo = 2

[Month1,3] indicates the cell with January and RowNo = 3

So we can represent a cell by [y,x], where y is the column name, also known as the ordinate, and x is the value of RowNo, also known as the abscissa

What if the table does not have a RowNo column? To find out, read on

Make the formula work

// First introduce autocalculate.js
import AutoCalculate from '.. /components/AutoCalculate'; .// Define an AutoCalculate instance and formulas
let autoCal = new AutoCalculate(formulas);

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

Regional formula

In fact, except for January, February, March… A similar formula exists in October, namely:

[Month1, 1] = [Month1, 2] + [Month1, 3] [Month2, 1] = [Month2, 2] + [Month2, 3] [Month3, 1] = [Month3, 2] + [3] Month3,... ... ... [Month10,1] = [Month10,2] + [Month10,3]Copy the code

This means that we need to write 10 of these formulas. This is not a problem for simple scenarios, but for some tables with a large number of formulas, this method has some disadvantages, such as errors, and long formulas can take a lot of time to write. So, we have the region formula.

Looking at the above formula, it can be found that each formula can be replaced by a formula, such as the following formula:

[@,1] = [@,2] + [@,3]
Copy the code

There is no explicit column name, just a placeholder @, but it is sufficient to represent the above 10 formulas. In this case, we just need to fill in the column names in the appropriate places, so the final formula is:

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

You need to separate column names with, and place them inside curly braces {}, so that 1 formula equals 10 formulas.

Placeholders can be used not only for ordinates, but also for abscissa, 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]
Copy the code

Using the region formula, it can be written as:

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

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

Support for JS syntax

In practical scenarios, we often encounter some complex formulas. As shown in the following figure, the cell formula uses Excel’s Max function. For such formulas, we can write as follows:

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

As you can see, formulas support JS syntax. You can put a JS variable on the right-hand side of the equation, or even js functions, as long as the SYNTAX is understood by the JS parsing engine.

One thing to note here is that you can’t put array elements in a formula, because js array elements usually have a “[]” symbol, which conflicts with the cell representation “[]” in the formula, so array elements are prohibited. Note this.

[y] formula

Now, let me show you another scenario, as shown here, where there is a relationship like this:

Cell ① = cell ② – cell ③

You might quickly write down the following formula:

[column3,1] = [column2,1] - [column1,1]
[column3,2] = [column2,2] - [column1,2]
Copy the code

There’s nothing wrong with that, but I have to remind you that the rows here are not fixed, which means that the number of rows in the table depends on the database at the time, so it could be 3 rows today, 5 rows tomorrow, 50 rows the next day. It is impossible to increase the formula as the number of rows increases, so we have a new way of writing this table with an indeterminate number of rows. I will call it the [y] formula because it has no x-coordinate compared to the normal formula:

[column3] = [column2] - [column1]
Copy the code

With just one 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, if the following relationship exists:

Cell ③ = cell ① / cell ②

Cell ② is the total value of the GroupApprovedTotal column, which is represented by < column name >, i.e.

. In addition, the rows here are not fixed, and the [y] formula is needed, so the formula should be written as:

[GroupApprovedTotalPercent] = [GroupApprovedTotal] / <GroupApprovedTotal>
Copy the code

We know that you can’t divide by 0, so the correct way to write it is:

[GroupApprovedTotalPercent] = <GroupApprovedTotal> === 0 ? 0 : [GroupApprovedTotal] / <GroupApprovedTotal>
Copy the code

When you put this formula in your code and start the program, you should be smart enough to quickly realize that the value you get is not precise enough. For example, cell ③ above shows 66.91%. If your cells ① and ② match the figure, your cell ③ is probably 67%.

By default, AutoCalculate will keep two decimal places, 67%, or 0.67. If you want 66.91%, or 0.6691, that is four decimal places. You need to tell AutoCalculate that you need four decimal places, so the full form should read:

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

To the left of the equal sign of the formula, and to the right of the assigned cell, add a “#” sign, followed by the decimal number. Note that there is no space between the “#” and the decimal number, but before and after the decimal number.

There is no table for RowNo

Finally, it’s time to answer this question, and I want to ask you, how do we find a point in a plane? The answer is you need the x-coordinate and the y-coordinate of this point, and again, in a table, how do you find a cell? First of all, we can determine the y-coordinate, because all the column names are known, but the key is to determine the y-coordinate. The use of RowNo is familiar because it looks like the ordinal number on the left side of Excel, but it does not mean that only numbers can be used as abscis. As long as the values are unique, that is, not repeated, they can be used as abscissa.

For example, if the following table is a fixed two-row table with no RowNo, but it can be seen that the company number (BuCode) is unique, then BuCode can be used as the 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]Copy the code

If there is a RowNo, write the following for the RowNo reference field:

[SumDiffMonth1,2] = [GroupApprovalMonth1,2] - [GroupApprovalMonth1,2] [SumDiffMonth1,3] = [GroupApprovalMonth1,3] - [GroupApprovalMonth1,3]Copy the code

Computation across data sources

What is cross-data computing? Those of you who have used the Excel formula should be able to see what this cell formula represents. It is clear that the value of this cell is the computed value of the data in the other sheets, and the cross-data source calculation is designed to deal with such scenarios.

We rarely or never do cross-data calculations in the foreground, but here we show you how to write the formula and call the AutoCalculate method so that you can actually use it in the “Background Usage” section.

First, in order to get data from other cells, we need to expand the cell. Before, we had a cell like [y,x], which we’ll call binary, and a cell like [y], which we’ll call unary. Now, you’ll see something like this: [External data sources, Y, X], or teradata cells, have expanded AutoCalculate’s ability to locate cells from two to three dimensions, meaning that it can find the data you need no matter how many tables you have.

Here’s a formula that uses three cells:

[Month1, 4] = [7] OutputTax, Month1,Copy the code

Where OutputTax is the name of a data source, you can call it whatever you want, as succinct as possible, otherwise the complex formula will be long and hard to read.

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

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

I believe that by reading the previous chapters, you have been able to understand the following formula, the first three lines of which use external data sources and are written in conjunction with the region formula.

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

Look what we did:

① Get an external data source outputTaxDatas

② Get the data source of the current table, payableTaxDatas

③ Get taxRateDatas, another external data source, from the database

The AutoCalculate constructor takes two parameters:

Formulas, so they’re an array of formulas

Options: Optional parameters, an object object

Options has an attribute externalDatas, which represents an external data source and is an array. Because there can be multiple data sources, each array element is an object and has three attributes:

Name: the name of the external data source, what is the name here, corresponding to the external data source name in the formula

RefField: Reference field

Datas: Data source

After the AutoCalculate is instantiated, a new method called calculate takes two parameters:

GridDatas: 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 this method, because any JS table control can extract table data (pure data), usually in the form of an array, just pass the array in.

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

The interface after running the program:

Click to get the data:

The background usage

To call AutoCalculate in the background, we need to use V8 engine, and it is also important to use formulas. What we did before was to put all formulas in the Controller file of Extjs, as shown below:

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

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

The call method is shown as follows:

Step by step:

① Save the data of the current table

② Obtain the js file directory where the formula resides

③ Get two external data sources

(4) call the encapsulated background method, using the data obtained in step ② and step ③, where FormulaExpression is the FormulaExpression, that is, through this expression is to find the formula in the js file provided by you

⑤ The newDatas returned in the previous step is the latest calculated data, now save this data to the database

Matters needing attention

There are two things to note when writing formulas:

  1. No Spaces are allowed in cells

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

// Error:
[Month12,1 ] = [ Month11, 1] * 10
Copy the code

  2. Decimal marks cannot be preceded by Spaces

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

// Error:
[Month12,1] # 3 = [Month11,1] * 10
Copy the code