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!
