SpreadSheet export bad performance issue

4 posts, 0 answers
  1. Florin
    Florin avatar
    2 posts
    Member since:
    Mar 2018

    Posted 05 Mar 2018 Link to this post

    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!

  2. Veselin Tsvetanov
    Admin
    Veselin Tsvetanov avatar
    1201 posts

    Posted 07 Mar 2018 Link to this post

    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
    Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  3. Florin
    Florin avatar
    2 posts
    Member since:
    Mar 2018

    Posted 07 Mar 2018 in reply to Veselin Tsvetanov Link to this post

    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!

  4. Veselin Tsvetanov
    Admin
    Veselin Tsvetanov avatar
    1201 posts

    Posted 09 Mar 2018 Link to this post

    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
    Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
Back to Top