Changing specific cell background color in excel sheet using OpenXml

Question

My requirement to change background color of cells in a row of a excel sheet.

ex : if A1 cell value is less than 100, i need to show it in Red Background.

I searched alot, i found some code to create stylesheets from this

http://blogs.msdn.com/b/chrisquon/archive/2009/11/30/stylizing-your-excel-worksheets-with-open-xml-2-0.aspx

that code is using StyleIndex property. And its very complex. Is there any other solution...???????

Answer 1

For manipulating spreadsheets in OpenXML format there are several wrappers around the raw SDK that make things much simpler, e.g.

• ClosedXML
• EPPlus
• SpreadsheetLight

Using ClosedXML you could use conditional formatting to achieve your desired result (see documentation):

using (var wb = new XLWorkbook())
{
    using (var ws = wb.AddWorksheet("Test"))
    {
        ws.Cell("A1").Value = 42;
        ws.Cell("A1").AddConditionalFormat().WhenLessThan(100)
            .Fill.SetBackgroundColor(XLColor.Red);
    }
    wb.SaveAs(@"C:\Dev\Test.xlsx");
}