Limiting the maximum number of rows

Posted by: morten on 3 May 2020, 11:06 pm EST

  • Posted 3 May 2020, 11:06 pm EST

    Hi there,

    We have an excel file template (containing around 14k rows with conditional formatting rules and formulas) that is used as a template. In practice, on average only 100 rows actually get filled in the template, so there are a lot of unused rows. In order to keep the workbook efficient, we are deleting all the unused rows using the pseudo code below:

    //next row after the last data row
    var firstEmptyRow = lastUsedRow + 1;

    //get the range containing all the conditional formatting rules
    var lastRow = sheet.Rows.LastRow;

    // get the used ranged, excluding the rows containing written data
    var unusedRows = sheet.Range[firstEmptyRow, 0, lastRow - firstEmptyRow, 1].Rows;

    unusedRows.Delete();


    This code correctly deletes the conditional formatting and formulas from the unused range. However, when loading the generated workbook in, for example, spreadjs, it is still possible to scroll down to row 14000. We're thinking this negatively impacts performance, as scrolling in this workbook drops the framerate to around 2 fps. In contrast, when filling in 100 rows into an empty workbook, it is not possible to scroll beyond those 100.

    Is there a way to either truly delete rows (instead of merely making them empty) or otherwise set the maximum number of rows in a sheet? When inspecting the generated ssjson file, there are still around 14k empty json objects in the sheets.[sheetName].rows array and sheets.[sheetName].rowCount is still set to 14k.

    Thanks in advance!
  • Replied 4 May 2020, 12:57 am EST

    I see the code from the original question was wrong. It should selected .EntireRows, instead of .Rows.


    var unusedRows = sheet.Range[firstEmptyRow, 0, lastRow - firstEmptyRow, 1].EntireRows;


    However, the result is the same.
  • Replied 5 May 2020, 2:27 am EST

    Hi,

    Thank you for the code snippet you are using, I am investigating further on this requirement [DocXls:2425] and will back to you on this soon.

    Regards,
    Ruchir
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels