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();
}

So what is not working with what you have here? sorry not seeing a clear question. — workabyte, Feb 23 '15 at 17:29
Basically I have a column which can contain data such as "Annual leave", "Available, " "sick", "retired" etc and depending on this text I want to programmatically change the color of the containing cell. e.g light yellow if it says "Annual leave", green cell whenever it contains "available" and so on. At the moment it doesnt change color — wubblyjuggly, Feb 23 '15 at 17:32
so is what you have ^ not working? what is it doing? fully understand the desired result but what is stopping you from getting there? — workabyte, Feb 23 '15 at 17:33
I think its basically getting the value from the DataTable. I want it to change cell color when a row contains a value of "Annual Leave". It complies okay but doesnt do anything really — wubblyjuggly, Feb 23 '15 at 17:35
The value in the cell that you're giving it is the fifth cell in the first row. You need to iterate the rows looking for that value, and changing the cell address when you find it. — krillgar, Feb 23 '15 at 17:39

Ernie S · Accepted Answer · 2015-02-24T01:42:33.003

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();
    }

Nearly there! Gives an error of "Range is not valid for styling : E0", as using a loop it starts at 0. need to move the styling down 2 cells due to the header in excel and using a zero based index — wubblyjuggly, Feb 24 '15 at 09:49
@wubblyjuggly Cool. Yes, the 0 vs 1 index is very easy to forget - that is what the '+ 1' does above. Glad it works for you. — Ernie S, Feb 24 '15 at 13:11

score 8 · Answer 2 · answered Feb 24 '15 at 09:57

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);
            }
        }

score 0 · Answer 3 · edited May 23 '17 at 11:47

0

You could try using the conditional formatting option in EPPlus

here is their sample

and here is a SO post with someone else who has used this option

Generally using links as answers is not my style but no reason to remake those wheels, if the EPP sample goes away im guessing so did they and if the SO sample is gone then im guessing so with this answer.

Hope it helps

edited May 23 '17 at 11:47

Community

1
1

answered Feb 23 '15 at 17:41

workabyte

3,496
2
27
35

The problem with links is that they stop working. Like yours. – Roger C S Wernersson Oct 09 '18 at 09:33
1

That is why i linked to the SO answer that has the code used but the link itself does still work – workabyte Oct 09 '18 at 12:28

How to set cell color programmatically epplus?

3 Answers3

Linked