Append to excel file with ClosedXML

Question

I need to append new data to existing excel file created with ClosedXML.

How can I append to an excel file using ClosedXML? How can I get the row number of the last record and append to that or is there something other?

Thanks!

Kamikamikaze

Answer 1

Open the existing workbook and then use the Last*Used methods:

XLWorkbook Workbook = new XLWorkbook(@"C:\existing_excel_file.xlsx");
IXLWorksheet Worksheet = Workbook.Worksheet("Name or index of the sheet to use");

int NumberOfLastRow = Worksheet.LastRowUsed().RowNumber();
IXLCell CellForNewData = Worksheet.Cell(NumberOfLastRow + 1, 1);

And then use one of the following depending on your data:

CellForNewData.SetValue(data);
CellForNewData.InsertData(data2);

CellForNewData.InsertTable(datatable);

For more information see the documentation under Inserting Data or Inserting Tables.

Lighthouse

I have one problem with order of the data. Some variables are in the right cells and others are now. I can't seem to find how InsertData knows which goes in which cell. – Kamikamikaze

I have a class with properties that represent the data and I add the class to an ArrayList and some data go in correct cell, others don't and I don't understand what is the problem. – Kamikamikaze

If your data is a complex class, use a foreach loop and put each object on a new row. Then you can insert the properties in the cells you want them. – Lighthouse

Just to say that I've find out what the problem was. Order of the properties in the class must match the columns in the excel file. Thanks again for the help. – Kamikamikaze