Using the ExcelPackage class on a web page

Dr. John Tunicliffe wrote a nice class for creating excel 2007 spreadsheets and was nice enough to post his work on the codeplex web site.

http://www.codeplex.com/ExcelPackage

I was looking to use the ExcelPackage to create a spreadsheet on a website with out having to save the spreadsheet on the server first.  Well to do this we are going to have to modify the ExcelPackage class to have an constructor which accepts a stream.  After you download the ExcelPackageclass from codeplex add the following code to ExcelPackage.cs contructors region.

        #region ExcelPackage Constructors
        /// <summary>
        /// Creates a new instance of the ExcelPackage class based on a stream.
        /// </summary>
        /// <param name="stream">Creates a new ExcelPackage from a stream</param>
        public ExcelPackage(Stream stream)
        {
            _package = Package.Open(stream, FileMode.OpenOrCreate);
            // save a temporary part to create the default application/xml content type
            Uri uriDefaultContentType = new Uri("/default.xml", UriKind.Relative);
            PackagePart partTemp = _package.CreatePart(uriDefaultContentType, "application/xml");
            XmlDocument workbook = Workbook.WorkbookXml; // this will create the workbook xml in the package
            // create the relationship to the main part
            _package.CreateRelationship(Workbook.WorkbookUri, TargetMode.Internal, schemaRelationships + "/officeDocument");
            // remove the temporary part that created the default xml content type
            _package.DeletePart(uriDefaultContentType);
        }
Once you compile the class we can add it as a reference to a website which targets the .Net framework 3.0 or 3.5.
So to write a spreadsheet to webpage we need to first create a MemoryStream to save the spreadsheet to.  I use a MemoryStream because the Response.OutputStream will give you an error about the FileMode or FileAccess type not being valid for this stream. Once we write the spreadsheet to the MemoryStream  set the Response's ContextType. Then we can write the MemoryStream to the webpage.
Imports OfficeOpenXml
Imports System.IO
Partial Public Class _Default
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim ms As New MemoryStream
        Using pack As New ExcelPackage(ms)
            Dim ws As ExcelWorksheet = pack.Workbook.Worksheets.Add("Sheet1")
            ws.Cell(1, 1).Value = "Product Name"
            ws.Column(1).Width = 30
            ws.Cell(1, 2).Value = "Price"
            ws.Column(2).Width = 10
            For r As Integer = 0 To 9
                ws.Cell(r + 2, 1).Value = "Product " & r.ToString
                ws.Cell(r + 2, 2).Value = r.ToString()
            Next
            pack.Workbook.Properties.Author = "Ken Tucker"
            pack.Workbook.Properties.Title = "Create spreadsheet from web"
            pack.Save()
        End Using
        Response.ContentType = "application/vnd.openxmlformats"
        Response.AddHeader("Content-Disposition", "attachment; filename= Data.xlsx;")
        Response.OutputStream.Write(ms.GetBuffer, 0, CInt(ms.Length))
        Response.Flush()
        Response.Close()
        Response.End()
    End Sub
End Class
Hope this helps