Today I needed to dynamically-generate an Excel file (using the awesome EPPlus), without saving it as a physical file first. The whole thing needed to be created in-memory, then sent to the user, who would be presented with the file open/save dialog in their browser.
The examples online turned out to be somewhat awkward to follow (many were incomplete). I couldn't find a full working example. So here goes...
The important parts are highlighted with comments.
Protected Sub CreateExcelTemplateFile()
Using pck As New ExcelPackage()
' create worksheet '
Dim ws As ExcelWorksheet = pck.Workbook.Worksheets.Add("Sheet1")
' Add your content here... '
ws.Cells(1, 1).Value = "Hello World"
'Write to client'
Response.Clear() ' important'
Response.AddHeader("Cache-Control", "max-age=0") ' optional'
Response.ContentEncoding = Encoding.UTF8 ' optional'
Response.HeaderEncoding = Encoding.UTF8 ' optional'
Response.Charset = Encoding.UTF8.WebName ' optional'
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" ' important'
Response.AddHeader("content-disposition", "attachment; filename=MyFile.xlsx") ' important'
Response.BinaryWrite(pck.GetAsByteArray()) ' important'
Response.Flush() ' important - ensures content has been sent '
Response.End() ' Important - stops ASP.NET rendering page content and attaching it to the output stream (stops your Excel file containing the ASP page) '