Thursday 5 June 2014

OpenXml - ASP.Net - How to create simple Excel file and add some text to cells - Step by step


Watch this example on YouTube

 here is the code:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.IO;
using System.IO.Packaging;


           MemoryStream ms = new MemoryStream();
            SpreadsheetDocument xl = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
            WorkbookPart wbp = xl.AddWorkbookPart();
            WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
            Workbook wb = new Workbook();
            FileVersion fv = new FileVersion();
            fv.ApplicationName = "Microsoft Office Excel";
            Worksheet ws = new Worksheet();

           //First cell
            SheetData sd = new SheetData();
            Row r1 = new Row() { RowIndex = (UInt32Value)1u };
            Cell c1 = new Cell();
            c1.DataType = CellValues.String;
            c1.CellValue = new CellValue("some value");
            r1.Append(c1);

           // Second cell
            Cell c2 = new Cell();
            c2.CellReference = "C1";
            c2.DataType = CellValues.String;
            c2.CellValue = new CellValue("other value");
            r1.Append(c2);
            sd.Append(r1);

            //third cell
            Row r2 = new Row() { RowIndex = (UInt32Value)2u };
            Cell c3 = new Cell();
            c3.DataType = CellValues.String;
            c3.CellValue = new CellValue("some string");
            r2.Append(c3);
            sd.Append(r2);

            ws.Append(sd);
            wsp.Worksheet = ws;
            wsp.Worksheet.Save();
            Sheets sheets = new Sheets();
            Sheet sheet = new Sheet();
            sheet.Name = "first sheet";
            sheet.SheetId = 1;
            sheet.Id = wbp.GetIdOfPart(wsp);
            sheets.Append(sheet);
            wb.Append(fv);
            wb.Append(sheets);

            xl.WorkbookPart.Workbook = wb;
            xl.WorkbookPart.Workbook.Save();
            xl.Close();
            string fileName = "testOpenXml.xlsx";
            Response.Clear();
            byte[] dt = ms.ToArray();

            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", fileName));
            Response.BinaryWrite(dt);
            Response.End();

6 comments:

  1. How de we add autowidth to a column ?

    ReplyDelete
  2. AutoFit? There is no easy way to do it. You have to manually calculate #of chars, font size, font type and based on this calculate width of the cell. I've done it, but I am using for my calculations code that i found on web, so cannot post here.

    ReplyDelete
  3. How do I add a new sheet to an Excel.xlsx file???

    ReplyDelete
  4. do u have any example of Loading data dynamically on header , columns and Row ?

    ReplyDelete
  5. Yeah, nice post. You rocks :)

    ReplyDelete
  6. What is Response?? I have red line on Response.Clear()
    Did I miss something?

    ReplyDelete