Search code examples
excelgembox-spreadsheet

How can I resize an Excel Table using Gembox.Spreadsheet?

I'm replacing Excel Table contents in an existing workbook with new contents from C# code using Gembox.Spreadsheet. Sometimes the data has more rows than the existing table, sometimes it has fewer. To resize the table my first attempt has been to incrementally add or remove rows. However, this can be slow if the difference in the number of rows is quite large. Here's the code:

var workbook = ExcelFile.Load("workbook.xlsx");
var table = workbook.Sheets["Sheet1"].Tables["Table1"];

var lastWrittenRowIndex = 0;
for(var rowIndex = 0; rowIndex < data.Count; rowIndex++)
{
  // If the table isn't big enough for this new row, add it
  if (rowIndex == table.Rows.Count) table.Rows.Add();

  // … Snipped code to add information in 'data' into 'table' … 

  lastWrittenRowIndex = rowIndex;
}

// All data written, now wipe out any unused rows
while (lastWrittenRowIndex + 1 < table.Rows.Count)
{
  table.Rows.RemoveAt(table.Rows.Count - 1);
}

Adding a profiler shows that by far the slowest operation is table.Rows.Add(). I haven't yet profiled a situation where I need to remove the rows, but I anticipate the same.

I know how large my data is before writing, so how can I prepare the table to be of the correct size in a smaller operation? There are formulae and pivot tables referencing the table and I don't want to break them.

Solution

  • Try again with this latest version that was just released (Full version: 45.0.35.1010):
    https://www.gemboxsoftware.com/spreadsheet/downloads/BugFixes.htm

    It has a Table.Rows.Add overload method that takes count.
    There are also similar ones for Insert and RemoveAt as well, see the following help page:
    https://www.gemboxsoftware.com/spreadsheet/help/html/Methods_T_GemBox_Spreadsheet_Tables_TableRowCollection.htm

    Last just as an FYI, you can additionally also set the following:

    workbook.AutomaticFormulaUpdate = false;

    This should improve the performances as well.
    Note, setting this property to false also improves the performances of all ExcelWorksheet.Rows and ExcelWorksheet.Columns insert and remove methods.