How to append a new row to an Excel file using C# and ClosedXML?

Question

I should append a new row to an existing Excel file. The task consists of two parts:

Add to non-existing file (works well).
Add to existing file (doesn't work: it doesn't make NEW record, displaying only the old record from "else" body).

Here is my code:

private static void ExportToEXCEL(DataTable dt, string paymentStoryPath)
{
    if (File.Exists(paymentStoryPath))
    {
        XLWorkbook currentWorkbook = new XLWorkbook(paymentStoryPath);
        IXLWorksheet currentWsh = currentWorkbook.Worksheet("Payment history");
        //IXLCell cellForNewData = index.Cell(index.LastRowUsed().RowNumber() + 1, 1);
        IXLRow rowForNewData = currentWsh.Row(currentWsh.LastRowUsed().RowNumber()+1);
        rowForNewData.InsertRowsBelow(1);
        rowForNewData.Value = dt;
        currentWorkbook.Save();
    }
    else
    {
        //not exist
        XLWorkbook wb = new XLWorkbook();
        wb.Worksheets.Add(dt, "Payment history");
        wb.SaveAs(paymentStoryPath);
    }
}

What is wrong and what should I change in my code?

I'm not surprised that code doesn't work, `rowForNewData.Value = dt;` is the problem, you're basically saying the value of a single row equals multiple rows:) You need some method of taking one row of the data for one row in Excel...see my answer for an example. — cjb110, Mar 23 '15 at 11:26

score 5 · Answer 1 · answered Mar 23 '15 at 10:32

To add a DataTable use the InsertTable() method:

    XLWorkbook currentWorkbook = new XLWorkbook(paymentStoryPath);
    IXLWorksheet currentWsh = currentWorkbook.Worksheet("Payment history");
    IXLCell cellForNewData = currentWsh.Cell(currentWsh.LastRowUsed().RowNumber() + 1, 1);
    cellForNewData.InsertTable(dt);
    currentWorkbook.Save();

score 4 · Answer 2 · answered Mar 23 '15 at 11:22

I've got the following code from one of my projects that inserts a DataTable into Excel.

//insert rows below a range from the cell going table rows down
ws.Range(
    cell.Address.RowNumber
    , cell.Address.ColumnNumber
    , cell.Address.RowNumber + DocDataSet.Tables[tableNo].Rows.Count
    , cell.Address.ColumnNumber)
    .InsertRowsBelow(DocDataSet.Tables[tableNo].Rows.Count);

//InsertData returns a range covering the inserted data
var ra = ws.Cell(cell.Address.RowNumber, cell.Address.ColumnNumber)
    .InsertData(DocDataSet.Tables[tableNo].AsEnumerable());

//apply the style of the table token cell to the whole range
ra.Style = cell.Style;

Its been a while since I wrote it, but as far as I know the idea is, create a range that will cover the rows and columns that will be populated. The Cell object has a InsertData method that can take any IEnumerable source.

You might not need the ws.Range line, I was inserting into a template so I had to create the space first.

score -1 · Accepted Answer · answered Jul 28 '16 at 18:32

-1

I took @raidri example and took it one step further where I have an extension method to handle this.

public static class Extensions
{
    public static void ToExcelFile(this DataTable dataTable, string filename, string worksheetName = "Sheet1")
    {
        using(var workbook = new XLWorkbook())
        {
            workbook.Worksheets.Add(dataTable, worksheetName);

            workbook.SaveAs(filename);
        }
    }
}

Use

myDataTable.ToExcelFile(@"C:\temp\myFile.xlsx");

answered Jul 28 '16 at 18:32

bkorzynski

541
1
5
15

This doesn't append a row, it just creates a new sheet with no control over the inserted data or where. – cjb110 Aug 09 '16 at 08:27
My thought is that you get the data table exactly how you want it exported then use this method to export it to an Excel file. Data row manipulation is outside the scope of exporting a data table to an Excel file. – bkorzynski Aug 09 '16 at 16:16

How to append a new row to an Excel file using C# and ClosedXML?

3 Answers3

Linked