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.
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
/// Creates a new instance of the ExcelPackage class based on a stream.
/// <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
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.
Partial Public Class _Default
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()
pack.Workbook.Properties.Author = "Ken Tucker"
pack.Workbook.Properties.Title = "Create spreadsheet from web"
Response.ContentType = "application/vnd.openxmlformats"
Response.AddHeader("Content-Disposition", "attachment; filename= Data.xlsx;")
Response.OutputStream.Write(ms.GetBuffer, 0, CInt(ms.Length))
Hope this helps
If you have the Anniversary update windows 10 pro or greater you can enable long file names in group policy. Search for group policy edit...
If you are having problems getting your hyper-v emulators to run here are the steps I recommend to fix them First and this usually fixes t...
There is a file 2A3A8A6D6826EF6CC653030E7D695C41.zip in the folder C:\Users\[User Name]\AppData\Local\Xamarin\zips that downloads the first...