Created
June 29, 2016 02:57
-
-
Save sunnydoll/e26f351aae8d595c454ae7ac11e72b38 to your computer and use it in GitHub Desktop.
Saving to xlsx file multiple times without closing the stream
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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