CODEWITHSUNDEEP
c#excelclosedxml
White-N-Nerdy
White-N-Nerdy

Reputation: 31

ClosedXML Insert data from List into existing proper Excel Table (named range) c#

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);

Upvotes: 3

Views: 11427

Answers (2)

Francois Botha
Francois Botha

Reputation: 4839

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).

Upvotes: 0

tomRedox
tomRedox

Reputation: 30443

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.

Upvotes: 2

Related Questions