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.
