Thursday, 22 May 2014

ASP.NET - How to create Excel file in memory (on the fly)


Watch this example on YouTube:

1. First download (and inatall) OpenXml from http://www.microsoft.com/en-ca/download/details.aspx?id=30425
2. Add references
 - WindowsBase
 - DocumentFormat.OpenXml

3. Add new webform
4. Add button to the form
5. Add the following using
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.IO;

6. Add the following on click method:

protected void Unnamed1_Click(object sender, EventArgs e)
        {
            MemoryStream ms = new MemoryStream();
            SpreadsheetDocument sd = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
            WorkbookPart wbp = sd.AddWorkbookPart();
            wbp.Workbook = new Workbook();
            WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
            wsp.Worksheet = new Worksheet(new SheetData());
            Sheets st = sd.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
            Sheet sts = new Sheet()
            {
                Id = sd.WorkbookPart.GetIdOfPart(wsp),
                SheetId = 1,
                Name = "anyName"
            };
            st.Append(sts);
            wbp.Workbook.Save();
            sd.Close();
            string strFileName = "TestName.xlsx";

            Response.Clear();
            byte[] dataByte = ms.ToArray();
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("Content-Disposition", string.Format("attachment; filenale={0}", strFileName));
            Response.BinaryWrite(dataByte);
            Response.End();
        }

7. That's it, now when user clicks the button - program will create xmls file and will provide user with options to open it or save it to local computer.

2 comments:

  1. Thanks this was helpful.

    ReplyDelete
  2. Hello a nice example.
    You have a typo in ....string.Format("attachment; filenale={0}",.....
    Thanks

    ReplyDelete