title: Open XML Excel Cell Formatting
body: <p>I am trying to export to excel using Open XML with simple formatting. Export to Excel is working. The problem is with formatting the data. I am trying to have very basic formatting. i.e. the Column names should be in bold and rest of the content in normal font. This is what I did. Please let me know where am I going wrong.</p>
  
  <pre><code>private Stylesheet GenerateStyleSheet()
  {
      return new Stylesheet(
          new Fonts(
              new Font(new DocumentFormat.OpenXml.Spreadsheet.FontSize { Val = 12},
                  new Bold(),
              new Font(new DocumentFormat.OpenXml.Spreadsheet.FontSize { Val = 12}))
          )
      );
  }
   protected void ExportExcel(DataTable dtExport)
      {
          Response.ClearHeaders();
          Response.ClearContent();
          Response.Clear();
          Response.Buffer = true;
          Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml";
          //"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" '"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" '"application/vnd.ms-excel"
          Response.AddHeader("content-disposition", "attachment; filename=Test.xlsx");
          Response.Charset = "";
          this.EnableViewState = false;
          MemoryStream ms = new MemoryStream();
          SpreadsheetDocument objSpreadsheet = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
          WorkbookPart objWorkbookPart = objSpreadsheet.AddWorkbookPart();
          objWorkbookPart.Workbook = new Workbook();
          WorksheetPart objSheetPart = objWorkbookPart.AddNewPart&lt;WorksheetPart&gt;();
          objSheetPart.Worksheet = new Worksheet(new SheetData());
          Sheets objSheets = objSpreadsheet.WorkbookPart.Workbook.AppendChild&lt;Sheets&gt;(new Sheets());
          Sheet objSheet = new Sheet();
          objSheet.Id = objSpreadsheet.WorkbookPart.GetIdOfPart(objSheetPart);
          objSheet.SheetId = 1;
          objSheet.Name = "mySheet";
          objSheets.Append(objSheet);
  
          WorkbookStylesPart stylesPart = objSpreadsheet.WorkbookPart.AddNewPart&lt;WorkbookStylesPart&gt;();
          stylesPart.Stylesheet = GenerateStyleSheet();
          stylesPart.Stylesheet.Save();
          objSheetPart.Worksheet.Save();
  
          objSpreadsheet.WorkbookPart.Workbook.Save();
          for (int cols = 0; cols &lt; dtExport.Columns.Count; cols++)
          {
              Cell objCell = InsertCellInWorksheet(GetColumnName(cols), 1, objSheetPart);
              objCell.CellValue = new CellValue(dtExport.Columns[cols].ColumnName);
              objCell.DataType = new EnumValue&lt;CellValues&gt;(CellValues.String);
              objCell.StyleIndex = 0;
          }
          objSheetPart.Worksheet.Save();
          objSpreadsheet.WorkbookPart.Workbook.Save();
          for (uint row = 0; row &lt; dtExport.Rows.Count; row++)
          {
              for (int cols = 0; cols &lt; dtExport.Columns.Count; cols++)
              {
                  //row + 2 as we need to start adding data from second row. First row is left for header
                  Cell objCell = InsertCellInWorksheet(GetColumnName(cols), row + 2, objSheetPart);
                  objCell.CellValue = new CellValue(Convert.ToString(dtExport.Rows[Convert.ToInt32(row)][cols]));
                  objCell.DataType = new EnumValue&lt;CellValues&gt;(CellValues.String);
                  objCell.StyleIndex = 1;
              }
          }
          objSheetPart.Worksheet.Save();
          objSpreadsheet.WorkbookPart.Workbook.Save();
          objSpreadsheet.Close();
          ms.WriteTo(Response.OutputStream);
          Response.Flush();
          Response.End();
  
      }
   // Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
      // If the cell already exists, return it. 
      private Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
      {
          Worksheet worksheet = worksheetPart.Worksheet;
          SheetData sheetData = worksheet.GetFirstChild&lt;SheetData&gt;();
          string cellReference = (columnName + rowIndex.ToString());
  
          // If the worksheet does not contain a row with the specified row index, insert one. 
          Row row = default(Row);
  
          if ((sheetData.Elements&lt;Row&gt;().Where(r =&gt; r.RowIndex.Value == rowIndex).Count() != 0))
          {
              row = sheetData.Elements&lt;Row&gt;().Where(r =&gt; r.RowIndex.Value == rowIndex).First();
          }
          else
          {
              row = new Row();
              row.RowIndex = rowIndex;
              sheetData.Append(row);
          }
  
          // If there is not a cell with the specified column name, insert one. 
          if ((row.Elements&lt;Cell&gt;().Where(c =&gt; c.CellReference.Value == columnName + rowIndex.ToString()).Count() &gt; 0))
          {
              return row.Elements&lt;Cell&gt;().Where(c =&gt; c.CellReference.Value == cellReference).First();
          }
          else
          {
              // Cells must be in sequential order according to CellReference. Determine where to insert the new cell. 
              Cell refCell = null;
              foreach (Cell cell in row.Elements&lt;Cell&gt;())
              {
                  if ((string.Compare(cell.CellReference.Value, cellReference, true) &gt; 0))
                  {
                      refCell = cell;
                      break; // TODO: might not be correct. Was : Exit For
                  }
              }
              Cell newCell = new Cell();
              newCell.CellReference = cellReference;
  
              row.InsertBefore(newCell, refCell);
              return newCell;
          }
  
      }
  </code></pre>