By default, SpreadJS is a very fast and powerful JavaScript spreadsheet that can handle the most demanding enterprise applications needs. However, you may have times where you need to push the limits a bit, including handling tens of thousands of rows of data and/or supporting thousands of calculations. While SpreadJS will support these, there are ways you can better optimize your code to get the best performance.
This blog will briefly introduce you to some best practices for SpreadJS to optimize your code.
Optimizing the Painting
Painting is the process by which Spread refreshes itself, since it is implemented in a JavaScript canvas. Every time there is a change in Spread, the Spread.Sheets is refreshed/repainted. In most cases this is perfectly fine, but a large number of concurrent changes could significantly reduce the performance. However, we have built in functionality to disable the painting temporarily until changes are finished; that way Spread will only refresh after all the changes are complete.
The suspendPaint and resumePaint methods are used to accomplish this, using suspendPaint before making changes, and then resumePaint after:
spread.suspendPaint();
for (var i = 0; i < 10000; i++) {
for (var j = 0; j < 10000; j++) {
sheet.setValue(i, j, new Date(), GC.Spread.Sheets.SheetArea.viewport);
}
}
spread.resumePaint();
Read more information about speeding up the painting in Spread.
Working with Many Formulas
When a user changes or adds formulas, Spread.Sheets calculates the sheet again. However, there are some instances where a lot of formula changes need to be made, which can slow Spread down. To stop and start the calculating, you can use the suspendCalcService and resumeCalcService methods before and after changing formulas:
sheet.suspendCalcService(true);
for (var i = 0; i < 10000; i++) {
for (var j = 0; j < 100; j++) {
sheet.setFormula(i, j, "DATE(2019, 3, 11)", GC.Spread.Sheets.SheetArea.viewport):
}
}
sheet.resumeCalcService(false);
To learn more about formula performance, see here.
Working with Large Amounts of Data
Typically when adding data to Spread a developer would use the setValue method. However, doing this for thousands of cells could begin to hinder performance. Instead, using a method like setArray allows large amounts of cells to be filled with data simultaneously:
var dataArray = [
["", 'Chrome', 'Firefox', 'IE', 'Safari', 'Edge', 'Opera', 'Other'],
["2017", 0.6360, 0.1304, 0.0834, 0.0589, 0.0443, 0.0223, 0.0246],
["2018", 0.3260, 0.2638, 0.1828, 0.0367, 0.9721, 0.2732, 0.3762],
];
sheet.setArray(0, 0, dataArray, false);
For more information about working with large data, see here.
Optimizing Calculation Functions
Most of the common functions like SUM only change when any dependent cell values are changed. However, there are some cases where volatile functions are used, like INDIRECT, RAND, or NOW. In this case, the values are recalculated no matter if a cell in the dependency was changed or not. If there are any performance issues with large spreadsheets that use these volatile functions, they might be contributing to those issues.
One solution is to avoid using these types of functions all together, but if the spreadsheet requires the use of these functions then nesting the volatile functions could significantly reduce any performance issues. An example of this is setting a volatile function in a single cell, and then referencing that cell's value in other formulas.
To see an in-depth explanation of how to avoid volatile functions, see here.
Suspending Events from Firing
Actions and API that the user invokes in Spread.Sheets are based on events. These include making changes to data, modifying styles, etc. As a result of multiple events being triggered repeatedly within a short amount of time, significant performance issues could be encountered. This is where the suspendEvent and resumeEvent methods come in handy: calling these before and after working with data can increase the performance. These are typically used in addition to the suspendPaint and resumePaint functions:
spread.suspendPaint();
spread.suspendEvent();
for (var i = 0; i < 20; i++) {
for (var j = 0; j < 10; j++) {
sheet.setValue(i, j, "111");
}
}
spread.resumeEvent();
spread.resumePaint();
For more information about event performance, see here.
Sorting Large Amounts of Data
One of the features of Spread.Sheets is to keep track of cell values that have been changed. Not only does this allow developers to control cell modifications and other such behavior, but it can also have an impact on performance. This can be especially prevalent when sorting large amounts of data, as this causes every cell value that was moved to be marked as dirty.
To stop performance from being affected, we can use the suspendDirty and resumeDirty methods to stop and start Spread from marking the dirty status of cells:
spread.bind(GC.Spread.Sheets.Events.RangeSorting, function (sender, args) {
sheet.supsendDirty();
});
spread.bind(GC.Spread.Sheets.Events.RangeSorted, function (sendder, args) {
sheet.resumeDirty();
});
To see more about how this works, take a look here.
These are just a few of the best practices with Spread.Sheets that can keep your spreadsheets running smoothly.
In another article, we discuss data validation features in the SpreadJS 12 Service Pack 1.
Happy coding, be sure to leave any thoughts or comments below.
Kevin Ashley