How to set cell color programmatically epplus?

Question

I was wondering if it is possible to set cell color programmatically using epplus?

I load my data from a sql stored procedure and it works well, but my users want cells that contain the words 'Annual Leave' to have a background color of light yellow instead of the default white. Is there a way to do this? perhaps by iterating through a datatable perhaps? Below is where

public void ExportTableData(DataTable dtdata)
{
    //Using EPPLUS to export Spreadsheets
    ExcelPackage pck = new ExcelPackage();
    var ws = pck.Workbook.Worksheets.Add("Availability list");

    ws.Cells["A1"].LoadFromDataTable(dtdata, true);

    ws.Cells["A1:G1"].Style.Font.Bold = true;
    ws.Cells["A1:G1"].Style.Font.UnderLine = true;

    //change cell color depending on the text input from stored proc?
    if (dtdata.Rows[4].ToString() == "Annual Leave")
    {
        ws.Cells["E1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
        ws.Cells["E1"].Style.Fill.BackgroundColor.SetColor(Color.LightYellow);
    }

    pck.SaveAs(Response.OutputStream);
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AddHeader("content-disposition", "attachment;  filename=Availability.xlsx");
    Response.End();
}

Answer 1

Check your line:

if (dtdata.Rows[4].ToString() == "Annual Leave") 

If it is a standard .net table wouldnt .ToString() evaluate to "System.Data.DataRow"? Also ws.Cells["E1"] will need to be adjusted for each cell after looping through the row count (basically what krillgar was saying).

Something like that:

[TestMethod] public void Cell_Color_Background_Test() {     //http://stackoverflow.com/questions/28679602/how-to-set-cell-color-programmatically-epplus      //Throw in some data     var dtdata = new DataTable("tblData");     dtdata.Columns.Add(new DataColumn("Col1", typeof(string)));     dtdata.Columns.Add(new DataColumn("Col2", typeof(int)));     dtdata.Columns.Add(new DataColumn("Col3", typeof(int)));      for (var i = 0; i < 20; i++)     {         var row = dtdata.NewRow();         row["Col1"] = "Available";         row["Col2"] = i * 10;         row["Col3"] = i * 100;         dtdata.Rows.Add(row);     }     //throw in one cell that triggers     dtdata.Rows[10]["Col1"] = "Annual leave";      var existingFile = new FileInfo(@"c:\temp\temp.xlsx");     if (existingFile.Exists)         existingFile.Delete();      using (var pck = new ExcelPackage(existingFile))     {         //Using EPPLUS to export Spreadsheets         var ws = pck.Workbook.Worksheets.Add("Availability list");          ws.Cells["A1"].LoadFromDataTable(dtdata, true);          ws.Cells["A1:G1"].Style.Font.Bold = true;         ws.Cells["A1:G1"].Style.Font.UnderLine = true;          //change cell color depending on the text input from stored proc?         //if (dtdata.Rows[4].ToString() == "Annual Leave")         for (var i = 0; i < dtdata.Rows.Count; i++)         {             if (dtdata.Rows[i]["Col1"].ToString() == "Annual leave")             {                 ws.Cells[i + 1, 1].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;                 ws.Cells[i + 1, 1].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightYellow);             }         }          pck.Save();     } 

Answer 2

Thanks Ernie! I changed it slightly to allow for my header in excel and to also to make sure that the code doesnt start at E1. I used ws.cells[i + 2, 5] to do this. Cheers!

   for (var i = 0; i < dtdata.Rows.Count; i++)
        {

            if (dtdata.Rows[i]["typeName"].ToString() == "Annual Leave")
            {
                ws.Cells[i + 2, 5].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                ws.Cells[i + 2, 5].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightYellow);
            }

            else if (dtdata.Rows[i]["typeName"].ToString() == "Available")
            {
                ws.Cells[i + 2, 5].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                ws.Cells[i + 2, 5].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGreen);
            }
            else
            {
                ws.Cells[i + 2, 5].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                ws.Cells[i + 2, 5].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.White);
            }
        }