Location>code7788 >text

SpreadJS Formula Filling Solution for Data Filling

Popularity：565 ℃/2024-11-06 02:49:25

Requirements

Many users use theSpreadJS The data filling function of the The general usage is: design a template and populate the data source. In this process, there may be issues where formulas are set up in the template, and when the data source is populated, the formulas are not carried down.

Let's say we define a template:

Next, use setDataSpurce() to populate the data source, and after populating it, it is found that only one line has a formula value, and the other lines have no data

So, what operations should we do? Or what are the options?
There are currently four programs, which arefillAuto、copyTo、clipboardPaste、setColumnDataFormula

Let's look at the specific usage usage and performance of each of these four options.

Let's first get the table area, define baseRow , baseCol

 let row = ().row
        let baseRow = row + 1;
        let baseCol = 4
        let rowCount = 0

Then after setDataSource, modify the rowCount value

        ('btn6').addEventListener('click', function () {
            (new (data2))
            rowCount = ().rowCount
        })

I. Programs

1、fillAuto

let start = new (baseRow, baseCol, 1, 1)
            let end = new (baseRow, baseCol, rowCount - 1, 1)
            (start, end, {
                fillType: ,
                series: 0,
                direction: 
            });

2、copyTo

 for (let r = baseRow + 1; r < row + rowCount; r++) {
                (baseRow, baseCol, r, baseCol, 1, 1, )
            }

3、clipboardPaste

 let fromRanges = [new (baseRow, baseCol, 1, 1)]
            let pastedRanges = [new (baseRow + 1, baseCol, rowCount - 2, 1)]
            ().execute({
                cmd: "clipboardPaste",
                sheetName: (),
                fromSheet: sheet,
                fromRanges,
                pastedRanges,
                isCutting: false,
                clipboardText: "",
                pasteOption: 
            });

4、setColumnDataFormula

(baseCol, (baseRow, baseCol));

All four of the above scenarios achieve formula filling with the following results:

II. Performance Comparison

1. 100 articles
So, let's take a look at the performance of these four scenarios next. First, let's set up 100 data sources that

 let sales = [], dataLength = 100
        for (let i = 0; i < dataLength; i++) {
            ({orderDate: '1/6/2013', item: 'book', units: '95', quantity: 1.99})
        }
        (dataLength + 1)

Then set up a button that is used to clear the data after each scenario is executed.

  ('btn5').addEventListener('click', function () {
            (2, 4, (), 1,,);
        })

The results are as follows:

With 100 pieces of data, the performance of all four schemes is not very different.

2. 1,000 articles
Next, we set up 10,000 pieces of data and change the dataLength to 1000.
The results are as follows:

3. 10,000 articles
Next, we set up 10,000 pieces of data, changing dataLength to 10,000.
The results are as follows:

Looking at the above graph, we see that setColumnDataFormula and clipboardPaste have better performance, while fillAuto has the worst performance.

4, 10,000
We next change the amount of data to100,000data and observe the performance of setColumnDataFormula with clipboardPaste.

Summarize these data below:

summarize

As you can see from the above, in the case of a small amount of data, the four ways are more or less the same, but in the case of a large amount of data, it is recommended to use setColumnDataFormula way to fill the formula.

Extended Links:

[Dry Goods Release] The key points of financial statement collation analysis, read all in one article!

Why aren't your financial statements great? It's recommended that you understand these four design points and

Pure front-end Excel spreadsheet control for reporting and analyzing scenarios

<Previous Next>