SpreadJS Excel Loading Performance Optimization 2

- Experience identifying and improving unnecessary data binding operations through execution time measurement -

1. Problem Situation

In the project, we are implementing an Excel business screen based on React and TypeScript, using SpreadJS (v17.0.1) so that Excel files can be viewed and edited on the web. The structure saves an Excel file uploaded by the user as a template, and on the detail screen, connects the actual data to that template and displays it.

The problem was that the initial loading time was excessively long when entering the Excel detail screen. When entering the screen, Excel was not displayed immediately. Instead, a blank screen appeared first, followed by a loading screen, and then the Excel screen appeared after some additional time. Measurements in the development environment showed that the total loading time was approximately 25–30 seconds. The Excel file being used consisted of a total of 28 sheets and had a relatively complex structure, including references between sheets and formulas.

I would like to share the process of finding and improving the bottleneck by measuring, step by step, which part of this complex loading process was actually consuming time.

2. Checking the Excel File and Row/Column Ranges

At first, I checked the size of the Excel file and the row/column ranges of its sheets. I had previously improved a performance issue in SpreadJS where unnecessarily large row/column ranges, beyond the actual data, affected loading performance, so I first checked whether the same cause might be involved this time as well.

(※ A previous performance improvement case related to row/column ranges can be found https://www.nextree.io/spreadjs-egsel-roding-seongneung-coejeoghwa/ here.)

In the previous case, the actual data consisted of only about 100 rows, but the row range of the internal Excel sheet was set to the maximum of 1,048,576 rows. Because a large range had to be processed regardless of the actual data, the loading time increased, and performance was improved by reducing the unnecessarily configured range.

This time as well, after checking the Excel row/column ranges, I found that the ranges of some sheets were set much larger than the actual data. Since the project was configured to use Excel input ranges of up to 1,000 rows and 1,000 columns, I conducted a test by limiting and saving them within that range. As a result, the size of the SJS data decreased as follows.

- Before modification: approximately 5.35 MB (5,357,864 bytes)

- After modification: approximately 4.67 MB (4,675,857 bytes) 

However, although the file size decreased, the loading time did not decrease significantly. Therefore, I concluded that the main cause of this problem was not simply the Excel file size or the row/column ranges. Previously, performance had been improved by reducing unnecessary ranges within Excel itself. This time, however, it was necessary to examine the operations performed on the screen after Excel was loaded.

3. Measuring Execution Time for Each Loading Step

If the file itself was not the cause, it was necessary to determine which operation was actually taking time. Therefore, I divided the process of displaying Excel on the screen into several steps and measured the execution time. The representative measurement results were as follows.

Contrary to expectations, `workbook.open()`—which actually reads the Excel file—took approximately 2.3 seconds, while the process of checking BindingPath or processing the data afterward took mostly no more than several tens of milliseconds. In contrast, executing `setDataSource()` took approximately 18 seconds. This confirmed that the main cause of the loading delay was not reading the Excel file, but connecting the data to the sheets after Excel had been read.

4. Checking Data Binding Processing for All Sheets

To investigate the cause in more detail, I examined the existing code. The existing implementation executed `setDataSource()` for all sheets.

const dataSource = new GC.Spread.Sheets.Bindings.CellBindingSource(data);

for (let i = 0; i < workbook.getSheetCount(); i++) {
    const sheet = workbook.getSheet(i);
    sheet.setDataSource(dataSource);
}

This Excel file consisted of a total of 28 sheets, but only some of them actually required data binding.

I then measured the execution time for each sheet.

- Sheet 1: approximately 1,021 ms

- Sheet 2: approximately 836 ms

- Sheet 3: approximately 805 ms

- Sheet 4: approximately 812 ms

- Sheet 5: approximately 783 ms

- Sheet 6: approximately 771 ms

Processing a single sheet took approximately 0.7–0.9 seconds. More importantly, calling `setDataSource()` was taking a considerable amount of time even for sheets that had no BindingPath to bind. Therefore, I concluded that the main cause of the problem was repeatedly performing the same operation on sheets that did not require binding.

However, I plan to separately investigate later why calling setDataSource() itself takes time even on sheets without a binding path, including the internal behavior of SpreadJS.

5. Selecting Sheets to Be Data-Bound

Previously, `setDataSource()` was executed for every sheet, but I changed the structure so that only sheets that actually require data binding are selected and processed.

const dataSource = new GC.Spread.Sheets.Bindings.CellBindingSource(data);

for (let i = 0; i < workbook.getSheetCount(); i++) {
    const sheet = workbook.getSheet(i);

    if (!sheet.visible()) {
        continue;
    }

    if (!isBindingTargetSheet(sheet)) {
        continue;
    }

    sheet.setDataSource(dataSource);
}

- Hidden sheets used only for formulas/references by other sheets were also excluded from data binding to prevent unnecessary calculations.

- `isBindingTargetSheet()` checks whether a BindingPath exists on the sheet. If even one BindingPath is found, the sheet is considered a data binding target.

The revised structure is as follows.

6. Results of Improving Loading Time

In the existing implementation, the total loading time for the detail screen was approximately 25–30 seconds in the test environment. After changing the implementation to process only the sheets that require data binding, it was reduced to approximately 15–20 seconds.

In the test environment, we confirmed an approximately 40–50% reduction in loading time. (※ Results may vary depending on the user's PC specifications, browser environment, and Excel structure.)

Although reducing the file size alone did not significantly improve the loading delay, we were able to substantially improve it by reducing the scope of tasks that were unnecessarily performed after loading.

7. Conclusion

This performance improvement was a case in which we identified the bottleneck by measuring the actual loading process step by step, rather than assuming that the file size or Excel structure was the cause.

At first, based on past experience, we suspected that the row and column ranges in Excel were the cause, but reducing the file size did not significantly improve the loading time. We then measured the execution time for each stage of the loading process and found that `setDataSource()` took significantly longer than `workbook.open()`. By measuring each sheet, we discovered that this operation was also being performed on sheets that did not require data binding.

With this improvement, we were able to reduce the initial loading time from approximately 25–30 seconds to 15–20 seconds in the test environment. However, since we determined that the final loading time could still be improved, we plan to expand the scope of optimization to include other tasks performed after Excel loading.

When investigating performance issues in complex Excel screens, we confirmed that it is important not only to consider the file size, but also to measure which tasks consume how much time during the actual loading process and reduce the scope so that only the necessary tasks are performed.

Reference Documents

[Official SpreadJS Data Binding Documentation]

https://developer.mescius.com/spreadjs/docs/v19/features/binding

JY