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

问题:

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

1. Add to non-existing file (works well).
2. 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?

回答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");

回答2:

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

回答3:

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.