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();
}
}
}
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();
}
}
}
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);
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With