2

There was a similar question, it didn't seem answered (maybe there's not an answer). How do you "update" an existing Excel Table (named range) from Closed XML. I know how to get the table info, and delete the existing data (see below)

    var ws = wb.Worksheet(sheetName);
    var table = ws.Table("data");
    table.Clear();

then ?? I have a list called "listdata" per say, which matches the table headers exactly...

Do I need to loop through the table one at a time like this (which seems like a waste):

foreach (var item in listdata){table.InsertRowsBelow(1); ws.Cells(2,1).InsertData(item)}

I guess maybe it would be kinda simpler if you did something like this:

table.InsertRowsBelow(listdata.Count()); ws.Cells(2,1).InsertData(listdata);

Or is there a way to bulk load into "table" (similar to .AddRange(listdata) or .Union(listdata)). Currently, I just delete the entire sheet then recreate the sheet and paste the new table:

      wb.Worksheets.Delete(sheetName);
      var ws = wb.Worksheets.Add(sheetName);
      ws.Cell(1, 1).InsertTable(listdata, "data", true);
White-N-Nerdy
  • 21
  • 1
  • 4

2 Answers2

1

As far as I know, the approach you discuss in your answer is the simplest one, i.e.

  • get a reference to the table
  • remove its data
  • insert your list.

InsertData acts as a bulk insert, it will take any IEnumerable collection and output that whole collection into the Excel spreadsheet starting at the selected cell.

So, for example, you can do this to populate a table from a list (where the first row of the worksheet is the table's header row):

private void PopulateTable(XLWorkbook wb, string workSheetName, string tableName, IEnumerable list)
{
    var ws = wb.Worksheet(workSheetName);
    var table = ws.Table(tableName);

    ws.Cell(2, 1).InsertData(list);
}

Cell also has an InsertTable function that will insert a DataTable instead of an IEnumerable.

tomRedox
  • 28,092
  • 24
  • 117
  • 154
0

In https://github.com/ClosedXML/ClosedXML/pull/932 some additions have been made to IXLTable. You might be interested in IXLTable.ReplaceData(data) and IXLTable.AppendData(data).

Francois Botha
  • 4,520
  • 1
  • 34
  • 46
  • Hi Francois, I noticed ReplaceData is extremely slow compared to InsertData. I'm inserting 60k items collection in 6 sec whereas ReplaceData takes 12.5 min! (with or without propagate arg). I tried to clear content or rows and then AppendData but I always get errors. Is there a faster alternative? thank you! – kevinob Mar 06 '20 at 09:26
  • (60k rows, 9 columns) – kevinob Mar 06 '20 at 10:49
  • @kevinob Log an issue on the github repo and fully complete the issue template, including minimal reproducible test case and I'll look into it. – Francois Botha Mar 06 '20 at 14:04