Conditional Formatting using EPPlus

Question

I am trying to achieve the following: I have a C# application which does some data processing and then outputs to a .xlsx using EPPlus. I want to add some conditional formatting to the excel and tried the following method, first I made a template blank excel with all conditional formatting rules set up and then tried dumping the data in it. The snippet below is my approach. p is an Excel package. Currently this does not work, the data is written correctly however the formatting rules that I set up are lost. I'm guessing because it basically clears everything before writing. Any help will be appreciated!

Byte[] bin = p.GetAsByteArray();
        File.Copy("C:\\template.xlsx", "C:\\result.xlsx");
        using (FileStream fs = File.OpenWrite("C:\\result.xlsx")) { 
        fs.Write(bin, 0, bin.Length);
        }

Note :: I tried the following as well to avoid the whole external template situation.. check snippet below. The problem with this is that, after the .xlsx is generated and I open it, it says the file has unreadable or not displayable content and that it needs to repair it and after I do that, everything is fine and the conditional formatting has also worked. I have no clue why its doing that or how I can get rid of the error upon file opening.

 string _statement = "$E1=\"3\"";
                var _cond = ws.ConditionalFormatting.AddExpression(_formatRangeAddress);
                _cond.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                _cond.Style.Fill.BackgroundColor.Color = Color.LightCyan;
                _cond.Formula = _statement;

Any help will be appreciated!!

Answer 1

The method of using fs.Write will simply overwrite the copied file with the epplus generated file since you are doing it at the byte/stream level. So that will not get you what you want. (@MatthewD was showing you this in his post).

As for applying the format itself, what you have should work but if you are getting that kind of error I suspect you are mixing epplus and non-epplus manipulation of the excel file. This is how you should be doing it roughly:

[TestMethod]
public void Conditional_Format_Test()
{
    //http://stackoverflow.com/questions/31296039/conditional-formatting-using-epplus

    var existingFile = new FileInfo(@"c:\temp\temp.xlsx");
    if (existingFile.Exists)
        existingFile.Delete();

    //Throw in some data
    var datatable = new DataTable("tblData");
    datatable.Columns.Add(new DataColumn("Col1", typeof(int)));
    datatable.Columns.Add(new DataColumn("Col2", typeof(int)));
    datatable.Columns.Add(new DataColumn("Col3", typeof(int)));

    for (var i = 0; i < 20; i++)
    {
        var row = datatable.NewRow();
        row["Col1"] = i;
        row["Col2"] = i * 10;
        row["Col3"] = i * 100;
        datatable.Rows.Add(row);
    }

    using (var pack = new ExcelPackage(existingFile))
    {
        var ws = pack.Workbook.Worksheets.Add("Content");
        ws.Cells["E1"].LoadFromDataTable(datatable, true);

        //Override E1
        ws.Cells["E1"].Value = "3";

        string _statement = "$E1=\"3\"";
        var _cond = ws.ConditionalFormatting.AddExpression(new ExcelAddress(ws.Dimension.Address));
        _cond.Style.Fill.PatternType = ExcelFillStyle.Solid;
        _cond.Style.Fill.BackgroundColor.Color = Color.LightCyan;
        _cond.Formula = _statement;

        pack.SaveAs(existingFile);
    }
}

Answer 2

To expand on @Ernie code sample, here's a working example that colors a range according to cell's value. Each cell of the range can have any of three colors depending on the cell's value (<.01, <.05, <.1).

ExcelRange rng = ws.Cells[statsTableRowStart, 10, statsTableRowStart + gud.levels.level.Count() - 1, 10];
OfficeOpenXml.ConditionalFormatting.Contracts.IExcelConditionalFormattingExpression _condp01 = ws.ConditionalFormatting.AddExpression(rng);
_condp01.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
_condp01.Style.Fill.BackgroundColor.Color = System.Drawing.Color.OrangeRed;
_condp01.Formula = new ExcelFormulaAddress(rng.Address) + "<.01";
OfficeOpenXml.ConditionalFormatting.Contracts.IExcelConditionalFormattingExpression _condp05 = ws.ConditionalFormatting.AddExpression(rng);
_condp05.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
_condp05.Style.Fill.BackgroundColor.Color = System.Drawing.Color.OliveDrab;
_condp05.Formula = new ExcelFormulaAddress(rng.Address) + "<.05";
OfficeOpenXml.ConditionalFormatting.Contracts.IExcelConditionalFormattingExpression _condp1 = ws.ConditionalFormatting.AddExpression(rng);
_condp1.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
_condp1.Style.Fill.BackgroundColor.Color = System.Drawing.Color.LightCyan;
_condp1.Formula = new ExcelFormulaAddress(rng.Address) + "<.1";