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

Question

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

tomRedox · Accepted Answer · 2018-10-19 08:38:35Z

2

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.

answered Oct 19, 2018 at 8:38

tomRedox

30.9k30 gold badges125 silver badges167 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Francois Botha · Accepted Answer · 2018-11-04 12:14:13Z

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

answered Nov 4, 2018 at 12:14

Francois Botha

4,8891 gold badge39 silver badges47 bronze badges

3 Comments

kevinob Over a year ago

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 Over a year ago

(60k rows, 9 columns)

Francois Botha Over a year ago

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related