title: export to excel in asp.net using openxml
body: <p>Hello everyone i am working on a project which requires me to export some data to excel on a button click <strong>using open xml</strong>.Here is the class i am using for exporting to excel:</p>
  
  <pre><code>using System;
  using System.IO;
  using System.Collections.Generic;
  using System.Linq;
  using System.Text;
  using System.Drawing;
  using DocumentFormat.OpenXml;
  using DocumentFormat.OpenXml.Packaging;
  using DocumentFormat.OpenXml.Spreadsheet;
  using DocumentFormat.OpenXml.Drawing.Spreadsheet;
  using _14junexcel.Account;
  
  namespace _14junexcel
  {
      public class CreateExcelOpen
      {
          public void BuildWorkbook1(string filename)
          {
              string sFile = @"D:\\ExcelOpenXmlWithImageAndStyles.xlsx";
              if (File.Exists(sFile))
              {
                  File.Delete(sFile);
              }
              BuildWorkbook(sFile);
          }
  
          private static void BuildWorkbook(string filename)
          {
              try
              {
                  using (SpreadsheetDocument xl = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
                  {
                      WorkbookPart wbp = xl.AddWorkbookPart();
                      WorksheetPart wsp = wbp.AddNewPart&lt;WorksheetPart&gt;();
                      Workbook wb = new Workbook();
                      FileVersion fv = new FileVersion();
                      fv.ApplicationName = "Microsoft Office Excel";
                      Worksheet ws = new Worksheet();
                      SheetData sd = new SheetData();
  
                      WorkbookStylesPart wbsp = wbp.AddNewPart&lt;WorkbookStylesPart&gt;();
                      wbsp.Stylesheet = CreateStylesheet();
                      wbsp.Stylesheet.Save();
  
                      string sImagePath = @"C:\\Users\\Public\\Pictures\\Sample Pictures\\Jellyfish.jpg";
                      DrawingsPart dp = wsp.AddNewPart&lt;DrawingsPart&gt;();
                      ImagePart imgp = dp.AddImagePart(ImagePartType.Png, wsp.GetIdOfPart(dp));
                      using (FileStream fs = new FileStream(sImagePath, FileMode.Open))
                      {
                          imgp.FeedData(fs);
                      }
  
                      NonVisualDrawingProperties nvdp = new NonVisualDrawingProperties();
                      nvdp.Id = 1025;
                      nvdp.Name = "Picture 1";
                      nvdp.Description = "polymathlogo";
                      DocumentFormat.OpenXml.Drawing.PictureLocks picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks();
                      picLocks.NoChangeAspect = true;
                      picLocks.NoChangeArrowheads = true;
                      NonVisualPictureDrawingProperties nvpdp = new NonVisualPictureDrawingProperties();
                      nvpdp.PictureLocks = picLocks;
                      NonVisualPictureProperties nvpp = new NonVisualPictureProperties();
                      nvpp.NonVisualDrawingProperties = nvdp;
                      nvpp.NonVisualPictureDrawingProperties = nvpdp;
  
                      DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch();
                      stretch.FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle();
  
                      BlipFill blipFill = new BlipFill();
                      DocumentFormat.OpenXml.Drawing.Blip blip = new DocumentFormat.OpenXml.Drawing.Blip();
                      blip.Embed = dp.GetIdOfPart(imgp);
                      blip.CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print;
                      blipFill.Blip = blip;
                      blipFill.SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle();
                      blipFill.Append(stretch);
  
                      DocumentFormat.OpenXml.Drawing.Transform2D t2d = new DocumentFormat.OpenXml.Drawing.Transform2D();
                      DocumentFormat.OpenXml.Drawing.Offset offset = new DocumentFormat.OpenXml.Drawing.Offset();
                      offset.X = 0;
                      offset.Y = 0;
                      t2d.Offset = offset;
                      Bitmap bm = new Bitmap(sImagePath);
                      //http://en.wikipedia.org/wiki/English_Metric_Unit#DrawingML
                      //http://stackoverflow.com/questions/1341930/pixel-to-centimeter
                      //http://stackoverflow.com/questions/139655/how-to-convert-pixels-to-points-px-to-pt-in-net-c
                      DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();
                      extents.Cx = (long)bm.Width * (long)((float)0 / bm.HorizontalResolution);
                      extents.Cy = (long)bm.Height * (long)((float)0 / bm.VerticalResolution);
                      bm.Dispose();
                      t2d.Extents = extents;
                      ShapeProperties sp = new ShapeProperties();
                      sp.BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto;
                      sp.Transform2D = t2d;
                      DocumentFormat.OpenXml.Drawing.PresetGeometry prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry();
                      prstGeom.Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle;
                      prstGeom.AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList();
                      sp.Append(prstGeom);
                      sp.Append(new DocumentFormat.OpenXml.Drawing.NoFill());
  
                      DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture();
                      picture.NonVisualPictureProperties = nvpp;
                      picture.BlipFill = blipFill;
                      picture.ShapeProperties = sp;
  
                      Position pos = new Position();
                      pos.X = 0;
                      pos.Y = 0;
                      Extent ext = new Extent();
                      ext.Cx = extents.Cx;
                      ext.Cy = extents.Cy;
                      AbsoluteAnchor anchor = new AbsoluteAnchor();
                      anchor.Position = pos;
                      anchor.Extent = ext;
                      anchor.Append(picture);
                      anchor.Append(new ClientData());
                      WorksheetDrawing wsd = new WorksheetDrawing();
                      wsd.Append(anchor);
                      Drawing drawing = new Drawing();
                      drawing.Id = dp.GetIdOfPart(imgp);
                      wsd.Save(dp);
  
                      UInt32 index;
                      Random rand = new Random();
  
                      sd.Append(CreateHeader(2));
                      sd.Append(CreateColumnHeader(4));
  
                      for (index = 5; index &lt; 6; ++index)
                      {
                          sd.Append(CreateContent(index, ref rand));
                      }
  
                      ws.Append(sd);
                      ws.Append(drawing);
                      wsp.Worksheet = ws;
                      wsp.Worksheet.Save();
                      Sheets sheets = new Sheets();
                      Sheet sheet = new Sheet();
                      sheet.Name = "Sheet1";
                      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.WorkbookPart.Workbook.Save();
                      xl.Close();
                  }
              }
              catch (Exception e)
              {
                  Console.WriteLine(e.ToString());
                  Console.ReadLine();
              }
          }
  
  
          private static Stylesheet CreateStylesheet()
          {
              Stylesheet ss = new Stylesheet();
  
              Fonts fts = new Fonts();
              DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
              FontName ftn = new FontName();
              ftn.Val = StringValue.FromString("Calibri");
              FontSize ftsz = new FontSize();
              ftsz.Val = DoubleValue.FromDouble(11);
              ft.FontName = ftn;
              ft.FontSize = ftsz;
              fts.Append(ft);
  
              ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
              ftn = new FontName();
              ftn.Val = StringValue.FromString("Palatino Linotype");
              ftsz = new FontSize();
              ftsz.Val = DoubleValue.FromDouble(18);
              ft.FontName = ftn;
              ft.FontSize = ftsz;
              fts.Append(ft);
  
              fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
  
              Fills fills = new Fills();
              Fill fill;
              PatternFill patternFill;
              fill = new Fill();
              patternFill = new PatternFill();
              patternFill.PatternType = PatternValues.None;
              fill.PatternFill = patternFill;
              fills.Append(fill);
  
              fill = new Fill();
              patternFill = new PatternFill();
              patternFill.PatternType = PatternValues.Gray125;
              fill.PatternFill = patternFill;
              fills.Append(fill);
  
              fill = new Fill();
              patternFill = new PatternFill();
              patternFill.PatternType = PatternValues.Solid;
              patternFill.ForegroundColor = new ForegroundColor();
              patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString("00ff9728");
              patternFill.BackgroundColor = new BackgroundColor();
              patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
              fill.PatternFill = patternFill;
              fills.Append(fill);
  
              fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
  
              Borders borders = new Borders();
              Border border = new Border();
              border.LeftBorder = new LeftBorder();
              border.RightBorder = new RightBorder();
              border.TopBorder = new TopBorder();
              border.BottomBorder = new BottomBorder();
              border.DiagonalBorder = new DiagonalBorder();
              borders.Append(border);
  
              border = new Border();
              border.LeftBorder = new LeftBorder();
              border.LeftBorder.Style = BorderStyleValues.Thin;
              border.RightBorder = new RightBorder();
              border.RightBorder.Style = BorderStyleValues.Thin;
              border.TopBorder = new TopBorder();
              border.TopBorder.Style = BorderStyleValues.Thin;
              border.BottomBorder = new BottomBorder();
              border.BottomBorder.Style = BorderStyleValues.Thin;
              border.DiagonalBorder = new DiagonalBorder();
              borders.Append(border);
              borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
  
  
  
              CellStyleFormats csfs = new CellStyleFormats();
              CellFormat cf = new CellFormat();
              cf.NumberFormatId = 0;
              cf.FontId = 0;
              cf.FillId = 0;
              cf.BorderId = 1;
              csfs.Append(cf);
              csfs.Count = UInt32Value.FromUInt32((uint)csfs.ChildElements.Count);
  
              uint iExcelIndex = 164;
              NumberingFormats nfs = new NumberingFormats();
              CellFormats cfs = new CellFormats();
  
              cf = new CellFormat();
              cf.NumberFormatId = 0;
              cf.FontId = 0;
              cf.FillId = 0;
              cf.BorderId = 0;
              cf.FormatId = 0;
              cfs.Append(cf);
  
              NumberingFormat nfDateTime = new NumberingFormat();
              nfDateTime.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
              nfDateTime.FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss");
              nfs.Append(nfDateTime);
  
              NumberingFormat nf4decimal = new NumberingFormat();
              nf4decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
              nf4decimal.FormatCode = StringValue.FromString("#,##0.0000");
              nfs.Append(nf4decimal);
  
              // #,##0.00 is also Excel style index 4
              NumberingFormat nf2decimal = new NumberingFormat();
              nf2decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
              nf2decimal.FormatCode = StringValue.FromString("#,##0.00");
              nfs.Append(nf2decimal);
  
              // @ is also Excel style index 49
              NumberingFormat nfForcedText = new NumberingFormat();
              nfForcedText.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
              nfForcedText.FormatCode = StringValue.FromString("@");
              nfs.Append(nfForcedText);
              //Alignment
              Alignment align = new Alignment(){Horizontal = HorizontalAlignmentValues.General,Vertical=VerticalAlignmentValues.Center};
              //wraptext
             // Alignment align1 = new Alignment(){Horizontal=HorizontalAlignmentValues.CenterContinuous,Vertical=VerticalAlignmentValues.Center};
  
  
              // index 1
              cf = new CellFormat();
              cf.NumberFormatId = nfDateTime.NumberFormatId;
              cf.FontId = 0;
              cf.FillId = 0;
              cf.BorderId = 1;
              cf.FormatId = 0;
  
              cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
              cfs.Append(cf);
  
              // index 2
              cf = new CellFormat();
              cf.NumberFormatId = nf4decimal.NumberFormatId;
              cf.FontId = 0;
              cf.FillId = 0;
              cf.BorderId = 1;
              cf.FormatId = 0;
              cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
              cfs.Append(cf);
  
              // index 3
              cf = new CellFormat();
              cf.NumberFormatId = nf2decimal.NumberFormatId;
              cf.FontId = 0;
              cf.FillId = 0;
              cf.BorderId = 1;
              cf.FormatId = 0;
              cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
              cfs.Append(cf);
  
              // index 4
              cf = new CellFormat();
              cf.NumberFormatId = nfForcedText.NumberFormatId;
              cf.FontId = 0;
              cf.FillId = 0;
              cf.BorderId = 1;
              cf.FormatId = 0;
              cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
              cfs.Append(cf);
  
              // index 5
              // Header text
              cf = new CellFormat();
              cf.NumberFormatId = nfForcedText.NumberFormatId;
              cf.FontId = 1;
              cf.FillId = 0;
              cf.BorderId = 0;
              cf.FormatId = 0;
              cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
              cfs.Append(cf);
  
              // index 6
              // column text
              cf = new CellFormat();
              cf.NumberFormatId = nfForcedText.NumberFormatId;
              cf.FontId = 1;
              cf.FillId = 1;
              cf.BorderId = 1;
              cf.FormatId = 1;
              cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
              cfs.Append(cf);
  
              // index 7
              // coloured 2 decimal text
              cf = new CellFormat();
              cf.NumberFormatId = nf2decimal.NumberFormatId;
              cf.FontId = 0;
              //cf.FillId = 2;
              cf.FillId = 0;
              cf.BorderId = 1;
              cf.FormatId =0;
              cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
              cfs.Append(cf);
              //cf.Append(align);
              cf.Append(align);
  
              // index 8
              // coloured column text
              cf = new CellFormat();
              cf.NumberFormatId = nfForcedText.NumberFormatId;
              cf.FontId = 0;
              //cf.FillId = 2;
              cf.FillId = 0;
              cf.BorderId = 1;
              cf.FormatId = 0;
              cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
              cfs.Append(cf);
  
              nfs.Count = UInt32Value.FromUInt32((uint)nfs.ChildElements.Count);
              cfs.Count = UInt32Value.FromUInt32((uint)cfs.ChildElements.Count);
  
              ss.Append(nfs);
              ss.Append(fts);
              ss.Append(fills);
              ss.Append(borders);
              ss.Append(csfs);
              ss.Append(cfs);
  
              CellStyles css = new CellStyles();
              CellStyle cs = new CellStyle();
              cs.Name = StringValue.FromString("Normal");
              cs.FormatId = 0;
              cs.BuiltinId = 0;
              css.Append(cs);
              css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
              ss.Append(css);
  
              DifferentialFormats dfs = new DifferentialFormats();
              dfs.Count = 0;
              ss.Append(dfs);
  
              TableStyles tss = new TableStyles();
              tss.Count = 0;
              tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9");
              tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
              ss.Append(tss);
  
              return ss;
          }
  
          private static Row CreateHeader(UInt32 index)
          {
              Row r = new Row();
              r.RowIndex = index;
              Cell c = new Cell();
              c.DataType = CellValues.String;
              c.StyleIndex = 5;
              c.CellReference = "A" + index.ToString();
              c.CellValue = new CellValue("REPORT");
              r.Append(c);
              return r;
          }
  
          private static Row CreateColumnHeader(UInt32 index)
          {
              Row r = new Row();
              r.RowIndex = index;
  
              Cell c;
              c = new Cell();
              c.DataType = CellValues.String;
              c.StyleIndex = 7;
              c.CellReference = "A" + index.ToString();
              c.CellValue = new CellValue("Retail Customer Group");
              r.Append(c);
  
              c = new Cell();
              c.DataType = CellValues.String;
              c.StyleIndex = 7;
              c.CellReference = "B" + index.ToString();
              c.CellValue = new CellValue("Product Promotion Group");
              r.Append(c);    
              return r;
          }
  
          private static Row CreateContent(UInt32 index, ref Random rd)
          {
              Row r = new Row();
              r.RowIndex = index;
  
              Cell c;
              c = new Cell();
              c.StyleIndex = 7;
              c.CellReference = "A" + index.ToString();
              //c.CellValue = new CellValue(rd.Next(1000000000).ToString());
              c.CellValue = new CellValue("12334");
              r.Append(c);
  
              DateTime dtEpoch = new DateTime(1900, 1, 1, 0, 0, 0, 0);
              DateTime dt = dtEpoch.AddDays(rd.NextDouble() * 100000.0);
              TimeSpan ts = dt - dtEpoch;
              double fExcelDateTime;
              // Excel has "bug" of treating 29 Feb 1900 as valid
              // 29 Feb 1900 is 59 days after 1 Jan 1900, so just skip to 1 Mar 1900
              if (ts.Days &gt;= 59)
              {
                  fExcelDateTime = ts.TotalDays + 2.0;
              }
              else
              {
                  fExcelDateTime = ts.TotalDays + 1.0;
              }
              c = new Cell();
              c.StyleIndex = 7;
              c.CellReference = "B" + index.ToString();
              c.CellValue = new CellValue("124515");
              //c.CellValue = new CellValue(fExcelDateTime.ToString());
              r.Append(c);
  
  
              return r;
          }
      }
  }
  </code></pre>
  
  <p>and this is the function for button click :</p>
  
  <pre><code>protected void Button1_Click(object sender, EventArgs e)
          {
              string qwe = TextBox1.Text;
              string ert = TextBox2.Text;
              string filename = @"D:\\ExcelOpenXmlWithImageAndStyles.xlsx";
              CreateExcelOpen exp = new CreateExcelOpen();
              exp.BuildWorkbook1(filename);
          }
  </code></pre>
  
  <p>here are my requirements:</p>
  
  <blockquote>
    <p><strong>1</strong>.I need to pass the values of two textboxes to the two cells(which now contain values 12334 and 124515).</p>
    
    <p><strong>2</strong>. The heading column does not adjust itself to accomodate the values which i have provided(Retail customer group etc).Please suggest the
    modifications required in this class to enable the autofit.</p>
  </blockquote>
  
  <p>Thank you.</p>