SpreadJS was designed to be a high-performance spreadsheet that can load and calculate your most demanding data and spreadsheet files under most normal circumstances.
There are several ways you can further optimize SpreadJS to get even faster performance for loading, calculating, and interacting with vast amounts of formulas and/or data. This blog will introduce some of these tips to help you better optimize the SpreadJS performance.
1. Use Suspend/Resume Paint Method
Each time any change is made to SpreadJS, a refresh occurs to show the change(s). If you are making many changes at once, this can significantly affect the performance.
An excellent example of this is when first initializing and setting up a spreadsheet in code. Many changes are being made at one time, so suspendPaint and resumePaint can, in most cases, drastically speed this process up.
The suspendPaint method allows you to stop the repaint process while doing the modifications. After integrating all the changes, you can invoke the resumePaint method to repaint the spreadsheet.
Example
The following function sets some sample values to all the rows:
let setSampleData = (sheet,rowCount, colCount) => {
for (let row = 0; row < rowCount; row++) {
for (let col = 0; col < colCount; col++) {
sheet.setValue(row, col, "data( " + row + ", " + col + " )");
}
}
};
The below sample takes 542ms to update the ten rows and ten columns:
setSampleData(spread.getActiveSheet(),10,10);But when using the same function with suspend/resumePaint, it only takes 19 ms to complete.
sheet.suspendPaint();
setSampleData(sheet,10, 10);
sheet.resumePaint();
While this is an elementary sample, you can quickly see the benefits of using the paint methods when initializing the date to get better performance.
2. Use Suspend and Resume CalcService Methods
Each time we change the formulas or add new formulas to the sheet, SpreadJS will recalculate all the formulas to reflect the changes.
Suppose your sheet or file has many formulas, or you are setting or loading multiple formulas to the cells. In that case, you can use the suspendCalcService and resumeCalcService methods as they can drastically help with increasing the performance.
The suspendCalcService method suspends all calculations. Once all the formulas have been added, use resumeCalcService to perform the calculations.
The following code snippet applies the Sum Formula formula from row 1 to the provided row count and column count:
let setFormulaData = (sheet,rowCount, colCount) => {
for (let row = 1; row < rowCount; row++) {
for (let col = 0; col < colCount; col++) {
sheet.setFormula(row, col, "=SUM($A$1:$J$1)");
}
}
};
The below sample takes 110 ms to update the formulas of ten rows and ten columns:
sheet.suspendPaint()
setDummyFormula(sheet,10, 10);
sheet.resumePaint()
But when using the suspendCalcService and resumeCalcService methods. It only takes 50-60 ms.
sheet.suspendPaint();
sheet.suspendCalcService();
setDummyFormula(sheet, 10, 10);
sheet.resumeCalcService(false);
sheet.resumePaint();
This is a fundamental example in which you can observe the benefits of using these methods.
3. Use CalcOnDemand, DoNotRecalculateAfterLoad Flags
When a file is loaded into SpreadJS, the spread instance recalculates the formulas in the workbook, affecting the performance if the file has many formulas. To prevent this, SpreadJS supports the doNotRecalculateAfterLoad flag.
SpreadJS recalculates the sheet immediately after any changes are made on cells, and we can prevent this using the calcOnDemand flag.
When doNotRecalculateAfterLoad is set to true while loading, SpreadJS will not recalculate the workbook after loading the file.
On the other hand, calcOnDemand prevents unnecessary recalculation on the sheet and only calculates if any cells are affected by the changes made on the sheet.
The syntax using these flags is demonstrated below:
workbook.fromJSON(jsonData,{
doNotRecalculateAfterLoad:true
});
workbook.options.calcOnDemand=true;
4. Reduce Circular Dependency
Iterative calculations, also known as circular references, let you run calculations repeatedly using the previous calculation results. This is useful for calculating a customer's future investment value or adding timestamps to cells. In some cases, using circular references extensively could impact performance as these calculations are single-threaded.
Another performance issue could be using circular references that span multiple worksheets. Instead of making the SpreadJS calculation engine jump from sheet to sheet, try condensing the circular references to a single worksheet, which can help optimize the process and avoid additional calculations that aren't needed.
In SpreadJS, you can enable iterative calculation in worksheets using the iterativeCalculation property. You can also specify the number of times the formula should recalculate by setting the iterativeCalculationMaximumIterations property.
Further, you can also limit the maximum amount of change between two calculation values by setting the iterativeCalculationMaximumChange property.
workbook.fromJSON(jsonData,{
doNotRecalculateAfterLoad:true
});
workbook.options.calcOnDemand=true;
5. Use Incremental Loading for Importing Large Excel/JSON files
By default, each time a file is loaded in SpreadJS, it loads the entire JSON file at once. However, loading a huge file could take a longer time to load, affecting the performance and user experience.
This is where the SpreadJS incremental loading feature comes in, which will import the workbook in incremental steps resulting in faster loading and a better experience for the customer.
Here is the syntax for using incremental loading:
function fromJSON(json) {
spread.fromJSON(json,{
incrementalLoading:
{
loading: function (progress, args) {
console.log(progress, args.sheet.name());
},
loaded: function () {
console.log("file loaded");
}
}
});
}
6. Use SJS Tables and Named Ranges
In everyday life, names are widely used to refer to people, objects, and geographical locations. For example, instead of saying "the city lying at latitude 40.7128° N and longitude 74.0059° W, you simply say "New York City.”
Similarly, in SpreadJS, you can give a human-readable name to a single cell or a range of cells and refer to those cells by name rather than by reference.
SpreadJS named ranges and tables are two unique features that make cell referencing much easier. It may take a while to get used to, but when you start using it, you'll quickly find it makes working with and maintaining the spreadsheets a simple task.
Creating data-driven dashboards or PivotTables is almost always a good idea to convert your data into a SpreadJS Table. Using structured references means that formula references will automatically adjust when data is added to the tables.
One of the most significant benefits is that tables automatically add named ranges, which can make your formulas more comprehensible, like:
=SUM(MyTable[Sales])
Standard named ranges can also be used outside of tables to make data easier to read. For example, this syntax:
=Sales Price-Cost Price
is easier to understand than this:
=SUM(A1:A10)-SUM(G1:G10)
7. Convert Unused Formulas to Static Values
When you don’t need it, don’t keep it.
Lots of formulas could result in a slow SpreadJS workbook, and if you have formulas that are not even being used, they could be contributing to that slowness. As a rule of thumb, if you don’t need formulas, it’s better to convert them into a static value (by simply pasting the formula results as values).
8. Use Array Formulas
An array formula allows you to perform multiple calculations at once or perform one or more calculations multiple times within a selected cell range.
These formulas can be beneficial for replacing large numbers of similar formulas. As a simple example, you might have a function that is repeated for multiple cells like fn(A1, B1), fn(A2, B2), fn(A3, B3), etc. up to fn(A1000, B1000). You could simplify this to fn(A1:A1000, B1:B1000).
Instead of thousands of calls to the same function, the array formula would make it a single function call. As you can probably guess, this means fewer formulas to parse and evaluate and, therefore, faster loading times.
The key to optimizing the calculation speed of large numbers of array formulas is to ensure that the number of cells and expressions evaluated in the array formula is as minimal as possible.
Remember that an array formula is a bit like a volatile formula: if any one of the cells that it references has changed, is volatile, or has been recalculated, the array formula calculates all the cells in the formula and evaluates all the virtual cells it needs to do the calculation.
If you need to repeatedly use the same function within multiple array formulas, try calculating those outside the array formula and then reference the result.
9. Use Conditional Formatting with Caution
Conditional formatting is a feature that allows you to apply specific formatting to cells that meet certain criteria. It is often used as color-based formatting to highlight, emphasize, or differentiate among data and information stored in a spreadsheet.
While conditional formatting makes it easy to flag cells outside a range of values, the formatting can be super volatile. Every time your worksheet recalculates, the conditional formatting rules are reevaluated. When this involves many cells, the worksheet may become very slow and unresponsive.
For example: if we use ISBLANK(K1:Q200) in conditional formatting, it will travel through each cell in the range, which means ISBLANK will be evaluated 1200*1200 = 1,440,000 times.
We could replace the above formula with the conditional formula ISBLANK(K1), then the conditional format will adjust the base column and base row to each cell in the condition range.
10. Use Named Styles
A named style is a style object that can be shared with different ranges on the sheet. Usually, when you have different cell ranges that you need to apply styles, you could create a new style object for each range.
However, doing this could cause memory issues and affect performance. Using the setStyle method will create a new style object each time it is called, so if you did this for hundreds of ranges, you might see some performance degradation.
If your workbook has many essentially the same styles, it would be better first to create a style object, apply the style properties you want, name it, and then add it as a named style. You can use setStyleName instead of setStyle to use a previously defined style object.
Doing so means that instead of having hundreds of different style objects for hundreds of different cell ranges, you could have just a few style objects that are shared between them, significantly improving the memory and performance.
// Example code for setting backcolor style from row 0 to row 10
let style = new GC.Spread.Sheets.Style();
style.backColor = "red"
for (let row = 0; row <= 100; row++) {
for (let col = 0; col < sheet.getColumnCount(); col++) {
sheet.setStyle(row, col, style.clone())
}
}
// Using setStyleName it is more efficient
let style = new GC.Spread.Sheets.Style1();
style.name = "backColorStyle";
style.backColor = "red";
sheet.addNamedStyle("backColorStyle");
for (let row = 0; row<= 100;row++) {
for (let col =0; col<sheet.getColumnCount(); col++) {
sheet.setStyleName(row, col, "backColorStyle");
}
}
11. Use SetArray Instead of SetValue
In SpreadJS, the setValue method allows you to set a value in an individual cell. While this might be precisely what you are looking for, if you are setting values into more than one cell, it would be better to use the setArray method.
This method lets you set the values of a specified two-dimensional array of objects into a specific range of cells in the worksheet. It can help improve the performance by limiting the amount of memory needed compared to using setValue for each cell in the cell range.
You may also refer to the following attached sample that demonstrates the performance differences: spreadjs-performance-demo.zip
For more help with optimizing SpreadJS, check out our Best Practices section in our documentation here: /spreadjs/docs/latest/online/BestPractices.html
Tags:
Related Blogs
Try Our JavaScript Spreadsheet Components
SpreadJS Resources
Avinash Pathak