title: C# OpenXML-only specific cell styling for Excel export
body: <p>I'm relatively new to C#. Below is the code I currently have. It works for converting a DataTable into an Excel-format exportable Byte array. I first call Create() on a populated DataSet, storing it in Byte[], which is then populated by data from the DataSet. Note that all the code below works perfectly and I'm looking to add some styling to the exported excel file as per certain conditions. I'm wondering where such 'if'(?) statements could go, as in where exactly could I append this styling info, given my specific constraints (I'm supposed to identify data in particular rows and highlight them.) I've tried a lot of resources online and there's an issue with almost each one, given the implementation I'm using. I'm also not allowed to use any libraries outside of standard OpenXML.</p>
  
  <pre><code>    public static Byte[] Create(DataSet ds)
      {
          Dictionary&lt;String, List&lt;OpenXmlElement&gt;&gt; sets = ToSheets(ds);
          Byte[] _data = new Byte[] { };
  
          using (MemoryStream _ms = new MemoryStream())
          {
              using (SpreadsheetDocument package = SpreadsheetDocument.Create(_ms, SpreadsheetDocumentType.Workbook))
              {
                  WorkbookPart workbookpart = package.AddWorkbookPart();
                  workbookpart.Workbook = new Workbook();
  
                  Sheets sheets = workbookpart.Workbook.AppendChild(new Sheets());
                  foreach (KeyValuePair&lt;String, List&lt;OpenXmlElement&gt;&gt; set in sets)
                  {
                      WorksheetPart worksheetpart = workbookpart.AddNewPart&lt;WorksheetPart&gt;();
                      worksheetpart.Worksheet = new Worksheet(new SheetData(set.Value));
                      worksheetpart.Worksheet.Save();
  
                      Sheet sheet = new Sheet()
                      {
                          Id = workbookpart.GetIdOfPart(worksheetpart),
                          SheetId = Convert.ToUInt32(sheets.Count() + 1),
                          Name = set.Key
                      };
                      sheets.AppendChild(sheet);
                  }
                  workbookpart.Workbook.Save();
              }
              _data = _ms.ToArray();
          }
          return _data;
      }
  </code></pre>
  
  <p>Above is the main Create() function and below is the ToSheets() function. It should be self-explanatory.</p>
  
  <pre><code>    public static Dictionary&lt;String, List&lt;OpenXmlElement&gt;&gt; ToSheets(DataSet ds)
      {
          return
              (from dt in ds.Tables.OfType&lt;DataTable&gt;()
               select new
               {
                   Key = dt.TableName,
                   Value = (
                   new List&lt;OpenXmlElement&gt;(
                      new OpenXmlElement[] 
              {
                  new Row(
                      from d in dt.Columns.OfType&lt;DataColumn&gt;()
                      select (OpenXmlElement)new Cell()
                      {
                          CellValue = new CellValue(d.ColumnName),
                          DataType = CellValues.String
                      })
              })).Union
                   ((from dr in dt.Rows.OfType&lt;DataRow&gt;()
                     select ((OpenXmlElement)new Row(from dc in dr.ItemArray
                                                     select (OpenXmlElement)new Cell()
                                                     {
                                                         CellValue = new CellValue(dc.ToString()),
                                                         DataType = CellValues.String
                                                     })))).ToList()
               }).ToDictionary(p =&gt; p.Key, p =&gt; p.Value);
      }
  </code></pre>
  
  <p>I would appreciate it if anyone could point me in the right direction as to where I can append the style info and how I could go about doing that (remember I have to style rows based on data in the rows, ie. rows with certain cell values should have that row styled in a particular way.) Specifically, I'm looking to style cells in rows with a blue background and no borders when the first cell in said rows contains data, say "X".</p>
  
  <p>Thank you for your time!</p>