How to set cell color programmatically epplus?
Solution 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();
}
Solution 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);
}
}
wubblyjuggly
C# developer - trying to find my way around this fabulous IT world. Develop in c#, vb, Java and php.
Updated on February 24, 2020Comments
-
wubblyjuggly about 4 years
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(); }
-
wubblyjuggly about 9 yearsNearly 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
-
Ernie S about 9 years@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.
-
Roger C S Wernersson over 5 yearsThe problem with links is that they stop working. Like yours.
-
workabyte over 5 yearsThat is why i linked to the SO answer that has the code used but the link itself does still work