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
  • Replied 15 January 2021, 1:09 am EST

    Hi,

    I am having a same kind of issue. I want to set maximum row count and also maximum column count. Is there any way to do this?

    Br,
    Petri
  • Replied 17 January 2021, 3:06 pm EST

    Hello Petri,

    We are getting in touch with the developers for this and will come back to you with the updates soon.

    Regards,

    Prabhat Sharma.
  • Replied 19 January 2021, 1:35 pm EST

    Hello Petri,

    As per the development, there is no concept of row count and column count in Excel and GCExcel, so there is no way to support this feature. As a workaround, you can delete all rows that does not contain data with the code given below:

    IRange usedRange = workbook.getActiveSheet().getUsedRange(EnumSet.of(UsedRangeType.Data));
    int lastRow = usedRange.getLastRow() + 1;
    workbook.getActiveSheet().getRange(lastRow,-1, 1048576 - lastRow, -1).delete();


    Regards,
    Prabhat Sharma.
  • Replied 29 January 2021, 1:24 am EST

    Hi,

    I have created a new enhancement request for GcExcel to add support for RowCount and ColumnCount in the exported SSJSON for SpreadJS. We can add the feature to make it easy to get and set the row and column counts of the worksheet, which will only take effect when saving SSJSON. This enhancement will be scheduled for the 4.2 release.

    Regards,
    Sean Lawyer
    GrapeCity Documents Team
Need extra support?

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

Learn More

Forum Channels