SpreadSheet export bad performance issue

[Florin]

Hello,

I have noticed a bad performance issue with the export mechanism of a SpreadSheet, more precisely with the ".toJSON()" function.

If I have, for example, a Workbook with 1 Sheet which has 5000 rows and 5000 columns, and you call ".toJSON()" on that sheet, it will take around 1 minute to process it, even if the sheet is empty (for 10000 rows and 10000 columns it takes around 4 minutes). The main issue is that when data is collected to be exported it iterates over all columns and rows even if they do not have any data.

Can there be made a fix in which the iteration is only made over the cells / property bags that actually have any data?

Here is an example with 5000 rows and columns which it will take around 1 minute to export the data: https://dojo.telerik.com/iYenOJoq/2

Thank you!

[Veselin Tsvetanov]

Hi Florin,

The toJSON() method of the Spreadsheet widget would iterate all cells in all the sheets to check if they have value, formatting, validation or formula set. If a respective cell does not have any of the above it is not converted and included in the JSON result. Without iterating over its cells the Spreadsheet could not know which of the cells have values and which do not.

I hope, that the above explains the case. If you have any other questions, please do not hesitate to contact us.

Regards,
Veselin Tsvetanov
Progress Telerik

[Florin]

Hello,

Thank you for your response. I understand that this is how it's working now, in order to retrieve the value, format etc.

I was wondering if there could be a fix/refactoring which would improve this iteration. As I saw, all the values (value, format, border, formula etc) are stored in property bags whose values are linked to a range of cells by an index calculated based on the cell's row and column.
Wouldn't it be better to iterate over the property bags instead of each cell? Because the property bag would contain values only for cells that actually have some.

So then something like this could be done:

foreach(sheet.propertyBags, function(bag) {

     foreach(bag.values, function(value) {

        foreach(value.ranges, function(range) {

               if(valuesAreDefault(range.values)) { return }

               cells = getCellsInRange(range);

               cells.setValues(range.values, bag.valueType);

          })
     })
})

 

What is your opinion on this approach? Would it be feasible for a future version?

 

Thank you!

[Veselin Tsvetanov]

Hello Florin,

Thank you for the insight on the current toJSON() implementation of the Spreadsheet widget. We will review the possibility to use the propertyBags instead of iterating over cells and if appropriate, we will apply the discussed optimization.

Regards,
Veselin Tsvetanov
Progress Telerik