Stream/write Excel file to ASP.NET MemoryStream with EPPlus

Published


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) '

    End Using
End Sub

 

 

Comments

Posted by Jared on 5/15/2018

Thanks, this definitely saved me some time!

Posted by Htin on 6/10/2018

Thank you. really helped me

Posted by Ray on 1/27/2019

Thanks, this was really helpful!

New Comment





This is to prove that you are not a spam robot posting links to dodgy sites!