update existing workbook using epplus C#

Question

I am trying to add new worksheet into existing workbook, code runs fine without any error. But changes are not being updated to the excel file. Here is my code

string path = "C:\\TestFileSave\\ABC.xlsx";
FileInfo filePath = new FileInfo(path);
if (File.Exists(path)) 
{
    using(ExcelPackage p = new ExcelPackage()) 
    {
        using(stream = new FileStream(path, FileMode.Open, FileAccess.ReadWrite)) 
        {
            p.Load(stream);
            ExcelWorksheet ws = p.Workbook.Worksheets.Add(wsName + wsNumber.ToString());
            ws.Cells[1, 1].Value = wsName;
            ws.Cells[1, 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
            ws.Cells[1, 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
            ws.Cells[1, 1].Style.Font.Bold = true;
            p.Save();
        }
    }
}

score 16 · Answer 1 · answered Dec 06 '15 at 15:22

The stream object is not tied to the package. The only relationship is it copies its bytes in your call to Load afterwards they are separate.

You do not need to even use a stream - better to let the package handle it on its own like this:

var fileinfo = new FileInfo(path);
if (fileinfo.Exists)
{
    using (ExcelPackage p = new ExcelPackage(fileinfo))
    {
        //using (stream = new FileStream(path, FileMode.Open, FileAccess.ReadWrite))
        {
            //p.Load(stream);
            ExcelWorksheet ws = p.Workbook.Worksheets.Add(wsName + wsNumber.ToString());
            ws.Cells[1, 1].Value = wsName;
            ws.Cells[1, 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
            ws.Cells[1, 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
            ws.Cells[1, 1].Style.Font.Bold = true;
            p.Save();
        }

    }

}

score 6 · Answer 2 · edited Jun 13 '16 at 18:06

Here I have shown to write data into exiting excel file by creating a new sheet in same file. To answer your question: try using last two lines File.WriteAllBytes instead of p.Save().

string strfilepath = "C:\\Users\\m\\Desktop\\Employeedata.xlsx";           
using (ExcelPackage p = new ExcelPackage())
{
    using (FileStream stream = new FileStream(strfilepath, FileMode.Open))
    {
        p.Load(stream);
       //deleting worksheet if already present in excel file
        var wk = p.Workbook.Worksheets.SingleOrDefault(x => x.Name == "Hola");
        if (wk != null) { p.Workbook.Worksheets.Delete(wk); }

        p.Workbook.Worksheets.Add("Hola");
        p.Workbook.Worksheets.MoveToEnd("Hola");
        ExcelWorksheet worksheet = p.Workbook.Worksheets[p.Workbook.Worksheets.Count];

        worksheet.InsertRow(5, 2);
        worksheet.Cells["A9"].LoadFromDataTable(dt1, true);
        // Inserting values in the 5th row
        worksheet.Cells["A5"].Value = "12010";
        worksheet.Cells["B5"].Value = "Drill";
        worksheet.Cells["C5"].Value = 20;
        worksheet.Cells["D5"].Value = 8;

        // Inserting values in the 6th row
        worksheet.Cells["A6"].Value = "12011";
        worksheet.Cells["B6"].Value = "Crowbar";
        worksheet.Cells["C6"].Value = 7;
        worksheet.Cells["D6"].Value = 23.48;                  
    }
    //p.Save() ;
    Byte[] bin = p.GetAsByteArray();
    File.WriteAllBytes(@"C:\Users\m\Desktop\Employeedata.xlsx", bin);
}

Please add some text explaining your answer. Don't just throw in the code. — Hunter Turner, Jun 13 '16 at 16:35

score 0 · Answer 3 · answered Dec 06 '16 at 16:09

I originally got the error code "A disk error occurred during a write operation. (Exception from HRESULT: 0x8003001D (STG_E_WRITEFAULT)) " from using this, but later learned that it was because the existing Excel file that I wanted to modify wasn't fully MS-Excel format compliant. I created thee original excel file in Open office as an .xls file, but EPPlus was not able to read it. When I regenerated this original excel file in Online Excel, everything worked fine.

update existing workbook using epplus C#

3 Answers3