EPPlus: How do I have 2 ExcelPackage.SaveAs?

Question

I am using EPPlus for my export to Excel function. I need to have 2 SaveAs, 1st SaveAs which is a save dialogue to allow user Open/Save/SaveAs and my 2nd SaveAs to allow the Excel file to be saved directly into the specified folder in the server as a backup copy.

Thus my issue here is that my 2nd SaveAs does not work (no error popup during debug, no files generated either for 2nd SaveAs).

Please advice. Thanks!

ExcelPackage package = new ExcelPackage();
.....
code for loading data table
.....
var filename = @"REPORT_" + datetime.ToString("dd-MM-yyyy_hh-mm-ss") + ".xlsx";

The below codes works (my 1st SaveAs for user to choose to Open/Save/SaveAs):

Response.Clear();
package.SaveAs(Response.OutputStream);
Response.AddHeader("content-disposition", "attachment; filename=" + filename + ";");
Response.Charset = "";
Response.ContentType = "application/vnd.xlsx";
Response.End();

The below code does not work (my 2nd SaveAs to save file directly into server):

string path = @"C:\Users\testacc\Desktop\Test\" + filename +";";
Stream stream = File.Create(path);
package.SaveAs(stream);
stream.Close();
byte[] data = File.ReadAllBytes(path);

Solution 1

Why not look at it and achieve the other way, in the reverse order. First save the generated file on the server and then transmit the saved file to the client.

1. Create the package

   ExcelPackage package = new ExcelPackage();
   .....
   code for loading data table
   .....
   var filename = @"REPORT_" + datetime.ToString("dd-MM-yyyy_hh-mm-ss") + ".xlsx";
   

2. Save to the server

   string path = @"C:\Users\testacc\Desktop\Test\" + filename +";";
   Stream stream = File.Create(path);
   package.SaveAs(stream);
   stream.Close();
   

3. Transmit saved file to the client

   try {
       response.Clear();
       response.ContentType = "application/vnd.xlsx";
       response.AddHeader("content-disposition", "attachment; filename=" + filename + ";");
       response.TransmitFile(path);
       response.Flush();
   } catch (Exception ex) {
       // any error handling mechanism
   } finally {
       HttpContext.Current.ApplicationInstance.CompleteRequest();
   }
   

Solution 2

Anytime you call .Save, .SaveAs, or .GetAsByteArray() (might be others) in EPPlus they have the side effect of closing the package. So you would need to rebuild your package by doing something like package = new ExcelPackage.. and somehow re-read the file.

Since you already have the package in memory why not just copy the bytes and save that twice avoiding additional trips to IO. Something like this:

ExcelPackage package = new ExcelPackage();
var workbook = package.Workbook;
var hoja = workbook.Worksheets.Add("Sheet1");

//Copy the package in memory
byte[] data = package.GetAsByteArray();

//Write to web
Response.OutputStream.Write(data, 0, data.Length);

//Write to file
var filename = @"REPORT_" + DateTime.Now.ToString("dd-MM-yyyy_hh-mm-ss") + ".xlsx";
path = @"C:\temp\" + filename + ";";
stream = File.Create(path);
stream.Write(data, 0, data.Length);
stream.Close();

Comments

[Admin]

package.SaveAs() asks for confirmation.. is there a way to block it?

[haraman]

@WashingtonGuedes What confirmation?

[Admin]

A windows prompt saying if I really want to save the file.

[haraman]

Not sure about any such confirmation, I don't get any. Is it any file overwrite confirmation or something else? If possible please post a question along with some related sample code