title: apply background color to specific cell in excel using openxml
body: <p>Following is the code I have and in the final method below, I do all the operation. Please ignore the return types of methods, I have changed it later.</p>
  
  <pre><code>public static byte[] CreateExcelDocument&lt;T&gt;(List&lt;T&gt; list, string filename)
          {
              DataSet ds = new DataSet();
              ds.Tables.Add(ListToDataTable(list));
              byte[] byteArray = CreateExcelDocumentAsStream(ds, filename);
              return byteArray;
          }
  public static bool CreateExcelDocumentAsStream(DataSet ds, string filename, System.Web.HttpResponse Response)
          {
              try
              {
                  System.IO.MemoryStream stream = new System.IO.MemoryStream();
                  using (SpreadsheetDocument document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook, true))
                  {
                      WriteExcelFile(ds, document);
                  }
                  stream.Flush();
                  stream.Position = 0;
  
                  Response.ClearContent();
                  Response.Clear();
                  Response.Buffer = true;
                  Response.Charset = "";
  
                  //  NOTE: If you get an "HttpCacheability does not exist" error on the following line, make sure you have
                  //  manually added System.Web to this project's References.
  
                  Response.Cache.SetCacheability(System.Web.HttpCacheability.NoCache);
                  Response.AddHeader("content-disposition", "attachment; filename=" + filename);
                  Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                  byte[] data1 = new byte[stream.Length];
                  stream.Read(data1, 0, data1.Length);
                  stream.Close();
                  Response.BinaryWrite(data1);
                  Response.Flush();
                  Response.End();
  
                  return true;
              }
              catch (Exception ex)
              {
                  Trace.WriteLine("Failed, exception thrown: " + ex.Message);
                  return false;
              }
          }
  
  private static void WriteExcelFile(DataSet ds, SpreadsheetDocument spreadsheet)
          {
              //  Create the Excel file contents.  This function is used when creating an Excel file either writing 
              //  to a file, or writing to a MemoryStream.
              spreadsheet.AddWorkbookPart();
              spreadsheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
  
              //  My thanks to James Miera for the following line of code (which prevents crashes in Excel 2010)
              spreadsheet.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));
  
              //  If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
              WorkbookStylesPart workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart&lt;WorkbookStylesPart&gt;("rIdStyles");
              //var workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart&lt;WorkbookStylesPart&gt;();
              Stylesheet stylesheet = new Stylesheet(new Fills(
                  // Index 0 - required, reserved by Excel - no pattern
                      new Fill(new PatternFill { PatternType = PatternValues.None }),
                  // Index 1 - required, reserved by Excel - fill of gray 125
                      new Fill(new PatternFill { PatternType = PatternValues.Gray125 }),
                  // Index 2 - no pattern text on gray background
                      new Fill(new PatternFill
                      {
                          PatternType = PatternValues.Solid,
                          BackgroundColor = new BackgroundColor { Indexed = 64U },
                          ForegroundColor = new ForegroundColor { Rgb = "FFD9D9D9" }
                      })
                  ));            
              workbookStylesPart.Stylesheet = stylesheet;
              workbookStylesPart.Stylesheet.Save();
              // create a solid red fill
  
  
  
              //  Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
              uint worksheetNumber = 1;
              Sheets sheets = spreadsheet.WorkbookPart.Workbook.AppendChild&lt;Sheets&gt;(new Sheets());
              foreach (DataTable dt in ds.Tables)
              {
                  //  For each worksheet you want to create
                  string worksheetName = dt.TableName;
  
                  //  Create worksheet part, and add it to the sheets collection in workbook
                  WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart&lt;WorksheetPart&gt;();
                  Sheet sheet = new Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart), SheetId = worksheetNumber, Name = worksheetName };
                  sheets.Append(sheet);
  
                  //  Append this worksheet's data to our Workbook, using OpenXmlWriter, to prevent memory problems
                  WriteDataTableToExcelWorksheet(dt, newWorksheetPart);
  
                  worksheetNumber++;
              }
  
              spreadsheet.WorkbookPart.Workbook.Save();            
              spreadsheet.Close();
          }
  
          private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart)
          {
              OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart);
              writer.WriteStartElement(new Worksheet());
              writer.WriteStartElement(new SheetData());
  
              string cellValue = "";
  
              //  Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
              //
              //  We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
              //  cells of data, we'll know if to write Text values or Numeric cell values.
              int numberOfColumns = dt.Columns.Count;
              bool[] IsNumericColumn = new bool[numberOfColumns];
  
              string[] excelColumnNames = new string[numberOfColumns];
              for (int n = 0; n &lt; numberOfColumns; n++)
                  excelColumnNames[n] = GetExcelColumnName(n);
  
              //
              //  Create the Header row in our Excel Worksheet
              //
              uint rowIndex = 1;
  
              writer.WriteStartElement(new Row { RowIndex = rowIndex });
              for (int colInx = 0; colInx &lt; numberOfColumns; colInx++)
              {
                  DataColumn col = dt.Columns[colInx];
                  //AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, ref writer);
                  AppendTextCell1(excelColumnNames[colInx] + "1", col.ColumnName, ref writer);
                  IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single");
              }
              writer.WriteEndElement();   //  End of header "Row"
  
              //
              //  Now, step through each row of data in our DataTable...
              //
              double cellNumericValue = 0;
              foreach (DataRow dr in dt.Rows)
              {
                  // ...create a new row, and append a set of this row's data to it.
                  ++rowIndex;
  
                  writer.WriteStartElement(new Row { RowIndex = rowIndex });
  
                  for (int colInx = 0; colInx &lt; numberOfColumns; colInx++)
                  {
                      cellValue = dr.ItemArray[colInx].ToString();
  
                      // Create cell with data
                      if (IsNumericColumn[colInx])
                      {
                          //  For numeric cells, make sure our input data IS a number, then write it out to the Excel file.
                          //  If this numeric value is NULL, then don't write anything to the Excel file.
                          cellNumericValue = 0;
                          if (double.TryParse(cellValue, out cellNumericValue))
                          {
                              cellValue = cellNumericValue.ToString();
                              AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
                          }
                      }
                      else
                      {
                          //  For text cells, just write the input data straight out to the Excel file.
                          AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
                      }
                  }
                  writer.WriteEndElement(); //  End of Row
              }
              writer.WriteEndElement(); //  End of SheetData
              writer.WriteEndElement(); //  End of worksheet
  
              writer.Close();
          }
  
          private static void AppendTextCell(string cellReference, string cellStringValue, ref OpenXmlWriter writer)
          {
              //  Add a new Excel Cell to our Row 
              //writer.WriteElement(new Cell { CellValue = new CellValue(cellStringValue), CellReference = cellReference, DataType = CellValues.String });
              writer.WriteElement(new Cell { CellValue = new CellValue(cellStringValue), CellReference = cellReference, DataType = CellValues.String });            
          }
          private static void AppendTextCell1(string cellReference, string cellStringValue, ref OpenXmlWriter writer)
          {
              //  Add a new Excel Cell to our Row 
              //writer.WriteElement(new Cell { CellValue = new CellValue(cellStringValue), CellReference = cellReference, DataType = CellValues.String });
              writer.WriteElement(new Cell(new CellValue(cellStringValue)) { CellReference = cellReference, DataType = CellValues.String, StyleIndex = 2 });
          }
  
          private static void AppendNumericCell(string cellReference, string cellStringValue, ref OpenXmlWriter writer)
          {
              //  Add a new Excel Cell to our Row 
              writer.WriteElement(new Cell { CellValue = new CellValue(cellStringValue), CellReference = cellReference, DataType = CellValues.Number });
          }
  </code></pre>
  
  <p>Above is my code. I do try to add color by filling in stylesheet, however <strong>i dont understand the concept of styleindex</strong> to apply to a cell. Please help. </p>