Skip to content

Instantly share code, notes, and snippets.

@sunnydoll
Created June 29, 2016 02:57
Show Gist options
  • Save sunnydoll/e26f351aae8d595c454ae7ac11e72b38 to your computer and use it in GitHub Desktop.
Save sunnydoll/e26f351aae8d595c454ae7ac11e72b38 to your computer and use it in GitHub Desktop.
Saving to xlsx file multiple times without closing the stream
public void Multi_Save_Test()
{
//http://stackoverflow.com/questions/28007087/how-to-write-to-excel-many-times-using-one-object-of-epplus-in-c-sharp
var existingFile = new FileInfo(@"c:\temp\temp.xlsx");
if (existingFile.Exists)
existingFile.Delete();
//Use memstream and create the package but WITHOUT the FI so it is a memory stream as well
//Avoid using and call manual dispose
var holdingstream = new MemoryStream();
var pack = new ExcelPackage();
var ExSheet = pack.Workbook.Worksheets.Add("Data");
ExSheet.Cells["A1"].Value = "wer";
ExSheet.Cells["B1"].Value = "sdf";
//Do an incremental save to the file and copy the stream before closing - ORDER COUNTS!
pack.SaveAs(existingFile);
holdingstream.SetLength(0);
pack.Stream.Position = 0;
pack.Stream.CopyTo(holdingstream);
//*********************************************************
//reopen the holding stream, make a change, and resave it
pack.Load(holdingstream);
ExSheet = pack.Workbook.Worksheets["Data"];
ExSheet.Cells["A2"].Value = "wer";
ExSheet.Cells["B2"].Value = "sdf";
//Another incremental change
pack.SaveAs(existingFile);
holdingstream.SetLength(0);
pack.Stream.Position = 0;
pack.Stream.CopyTo(holdingstream);
//*********************************************************
//reopen the holding stream, make a change, and resave it
pack.Load(holdingstream);
ExSheet = pack.Workbook.Worksheets["Data"];
ExSheet.Cells["A3"].Value = "wer";
ExSheet.Cells["B3"].Value = "sdf";
//Another incremental change
pack.SaveAs(existingFile);
holdingstream.SetLength(0);
pack.Stream.Position = 0;
pack.Stream.CopyTo(holdingstream);
//*********************************************************
//reopen the holding stream, make a change, and do a FINAL save
pack.Load(holdingstream);
ExSheet = pack.Workbook.Worksheets["Data"];
ExSheet.Cells["A4"].Value = "wer";
ExSheet.Cells["B4"].Value = "sdf";
//All done so only need to save it to the file
pack.SaveAs(existingFile);
//cleanup
pack.Dispose();
holdingstream.Dispose();
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment