Add Conditional Formatting OpenXML C#

Question

How can I add conditional formatting in OpenXML using C# .Net. I would like the following conditions applied:

=INDIRECT("D"&ROW())="Disapproved" then the rule should apply to: =$1:$3,$N$4:$XFD$4,$5:$1048576

I have my function setup like so:

using (SpreadsheetDocument document = SpreadsheetDocument.Open(openFileDialog1.FileName, true))
{
   // apply conditions here
}

Answer 1

To add conditional formats, you need to add a ConditionalFormatting instance to the Worksheet. This object will hold the list of references that the conditional format should be applied to. The ConditionalFormatting instance needs a ConditionalFormattingRule to define the rule to be used which in turn is defined via a Formula.

In order for the conditional format to have an effect on the spreadsheet you'll also need to define a style to be used. This needs to be added to a DifferentialFormat, which in turn is added to a DifferentialFormats.

The following code will take an existing document and add the conditional format that you're after:

using (SpreadsheetDocument document = SpreadsheetDocument.Open(filename, true))
{
    WorkbookPart workbookPart = document.WorkbookPart;
    //get the correct sheet
    Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).First();
    WorksheetPart worksheetPart = workbookPart.GetPartById(sheet.Id) as WorksheetPart;
    SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();

    //grab the stylesPart so we can add the style to apply (create one if one doesn't already exist)
    WorkbookStylesPart stylesPart = document.WorkbookPart.GetPartsOfType<WorkbookStylesPart>().FirstOrDefault();
    if (stylesPart == null)
    {
        stylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
        stylesPart.Stylesheet = new Stylesheet();
    }

    //create a fills object to hold the background colour we're going to apply
    Fills fills = new Fills() { Count = 1U };

    //grab the differential formats part so we can add the style to apply (create one if one doesn't already exist)
    bool addDifferentialFormats = false;
    DifferentialFormats differentialFormats = stylesPart.Stylesheet.GetFirstChild<DifferentialFormats>();
    if (differentialFormats == null)
    {
        differentialFormats = new DifferentialFormats() { Count = 1U };
        addDifferentialFormats = true;
    }

    //create the conditional format reference
    ConditionalFormatting conditionalFormatting = new ConditionalFormatting()
    {
        SequenceOfReferences = new ListValue<StringValue>()
        {
            InnerText = "A1:XFD3 N4:XFD4 A5:XFD1048576"
        }
    };

    //create a style to assign to the conditional format
    DifferentialFormat differentialFormat = new DifferentialFormat();
    Fill fill = new Fill();
    PatternFill patternFill = new PatternFill();
    BackgroundColor backgroundColor = new BackgroundColor() { Rgb = new HexBinaryValue() { Value = "0000ff00" } };
    patternFill.Append(backgroundColor);
    fill.Append(patternFill);
    differentialFormat.Append(fill);
    differentialFormats.Append(differentialFormat);

    //create the formula
    Formula formula1 = new Formula();
    formula1.Text = "INDIRECT(\"D\"&ROW())=\"Disapproved\"";

    //create a new conditional formatting rule with a type of Expression
    ConditionalFormattingRule conditionalFormattingRule = new ConditionalFormattingRule()
    {
        Type = ConditionalFormatValues.Expression,
        FormatId = 0U,
        Priority = 1
    };

    //append the formula to the rule
    conditionalFormattingRule.Append(formula1);

    //append th formatting rule to the formatting collection
    conditionalFormatting.Append(conditionalFormattingRule);

    //add the formatting collection to the worksheet
    //note the ordering is important; there are other elements that should be checked for here really.
    //See the spec for all of them and see https://stackoverflow.com/questions/25398450/why-appending-autofilter-corrupts-my-excel-file-in-this-example/25410242#25410242
    //for more details on ordering
    PageMargins margins = worksheetPart.Worksheet.GetFirstChild<PageMargins>();
    if (margins != null)
        worksheetPart.Worksheet.InsertBefore(conditionalFormatting, margins);
    else
        worksheetPart.Worksheet.Append(conditionalFormatting);

    //add the differential formats to the stylesheet if it didn't already exist
    if (addDifferentialFormats)
        stylesPart.Stylesheet.Append(differentialFormats);
}

Note that OpenXml is fussy about the order of elements so (when working with an existing document particularly) you need to ensure you are adding elements in the correct place within the tree.