Create excel file and download it without saving it into the server, is it possible?

Question

i have managed to create an excel file with interop, and now i want that excel file to be downloaded in web browser instead of saving in in drive.

     var xlApp = new Excel.Application();
     Excel.Workbook xlWorkBook = xlApp.Workbooks.Add();
     Excel.Worksheet xlWorkSheet = xlWorkBook.Sheets[1];
     Excel.Range chartRange;

      var attendance_list = CTX.schedules.Where(s => s.event_id == Convert.ToInt32(param));
      if (attendance_list.Count() > 0)
      {
             xlWorkSheet.Cells[1, 1] = "PIC : ";
             xlWorkSheet.Cells[2, 1] = "Tempat : ";

             xlWorkSheet.Cells[1, 2] = attendance_list.FirstOrDefault().calendar_event.PIC;
             xlWorkSheet.Cells[2, 2] = attendance_list.FirstOrDefault().calendar_event.sched_loc;

             xlWorkSheet.Shapes.AddPicture(@Server.MapPath("~/Images/" + "oto_group2.png"), MsoTriState.msoFalse, MsoTriState.msoCTrue, 50, 50, 100, 45);

             xlWorkSheet.Shapes.AddPicture(@Server.MapPath("~/Images/" + "oto_group3.png"), MsoTriState.msoFalse, MsoTriState.msoCTrue, 280, 50, 100, 45);

             int row = 10;
             int idx = 0;

             xlWorkSheet.Cells[9, 1] = "No";
             xlWorkSheet.Cells[9, 2] = "Name";
             xlWorkSheet.Cells[9, 3] = "Phone";
             xlWorkSheet.Cells[9, 4] = "Time";
             xlWorkSheet.Cells[9, 5] = "Branch";
             xlWorkSheet.Cells[9, 6] = "Sign";
             xlWorkSheet.Cells[9, 7] = "Note";

             chartRange = (xlWorkSheet.get_Range("b9"));
             chartRange.ColumnWidth = 40;

             chartRange = (xlWorkSheet.get_Range("c9"));
             chartRange.ColumnWidth = 20;

             chartRange = (xlWorkSheet.get_Range("g9"));
             chartRange.ColumnWidth = 20;

             var key = CTX.translate_value_ms.Where(t => t.PSF_type == "HRS_PHONE_TYPE"
                                                                     && t.value == "CELL").FirstOrDefault().translate_value_id;

             foreach (var item in attendance_list)
              {
                 idx++;

                  var hp = item.user_list.user_phones.Where(p => p.phone_type_id == key).FirstOrDefault();

                  xlWorkSheet.Cells[row, 1] = idx;
                  xlWorkSheet.Cells[row, 2] = displayFullName(item.user_list.fname, item.user_list.mname, item.user_list.lname);
                  xlWorkSheet.Cells[row, 3] = hp.phone_number;
                  xlWorkSheet.Cells[row, 4] = item.calendar_event.time_range;
                  xlWorkSheet.Cells[row, 5] = item.calendar_event.branch;
                  xlWorkSheet.Cells[row, 6] = "";
                  xlWorkSheet.Cells[row, 7] = "";

                     row++;
             }

             chartRange = xlWorkSheet.get_Range("a9", "g9");
             chartRange.Font.Bold = true;
             chartRange.Interior.Color = System.Drawing.Color.Gray;

             chartRange = xlWorkSheet.get_Range("a9",  "g" + (row - 1));
             chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);

             xlWorkBook.SaveAs("csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal);

             xlWorkBook.Close(true);
             xlApp.Quit();

             Marshal.ReleaseComObject(xlApp);
     }

this is what i have tried so far to create excel file. need some englightment. "this code save excel file to drive, which is not the result that i want to achieve."

this is my generated excel :

Answer 1

Paste this snippet code own project under Marshal.ReleaseComObject(xlApp); this line.
After that set path where your file save in system.
I hope this code will work.

    string fileName = "";
    Response.ContentType = "application/vnd.ms-excel";
    fileName = Server.MapPath("~/WriteReadData/Excelfiles/Excelfile.xls");  
   //Give path name\file name.

    Response.AppendHeader("Content-Disposition", "attachment; filename="Excelfile.xls");

    //Specify the file name which needs to be displayed while prompting

    Response.TransmitFile(fileName);

    Response.End();

Answer 2

Using a mix of your work and the example I made using NPOI you can do something like this

public FileStreamResult MyAction(parameters)
{
   //Here you create a workBook with your actual code
    var workBook = CreateWorkbook(parameters);
    var fileName = "routeToFileWhereYourAppCanWrite.xls"
    //Here you save the file to the file system
    workbook.SaveToFile(fileName);
    using (MemoryStream ms = new MemoryStream())
    {
        using (FileStream fileStream = new FileStream("file.bin", FileMode.Open, FileAccess.Read))
        {
            fileStream.CopyTo(ms);
        }
       //Here you delete the saved file
        File.Delete(fileName);
        ms.Position = 0;   
        return File(stream, "attachment;filename=myfile.xls", "myfile.xls");
    }
}

In summary, create the file as you do now, save it, load it into memory, delete it and return the stream

Answer 3

I used https://npoi.codeplex.com/ to create excel and returned a FileStereamResult on my controller action. I think you cannot do that with the Excel library you are using

How to write an Excel workbook to a MemoryStream in .NET?

But check out npoi is really simple and they even have an example on what you are looking, which was something like this

public FileStreamResult MyAction(parameters)
{
    var workBook = CreateWorkbook(parameters);
    var stream = new MemoryStream();
    workBook.Write(stream);
    stream.Position = 0;   
    return File(stream, "attachment;filename=myfile.xls", "myfile.xls");
}

Like this you dont save anything to the file system.

Answer 4

You will need to save the file on the server temporarily in order to serve it to the client.

You can save it in a temp path using utilities in System.IO.Path like GetTempPath() to put the file in a place the OS will clean the file up automatically when it's not needed.

I don't know what web server you're using but if you're using MVC you'd do something like this to serve the file in your controller

var filePath = System.IO.Path.GetTempFileName();
//then save the file to the filePath
return File(filePath);