Conditional Formatting Above and Below Average with OpenXML SDK

The example shows how to set above and below average conditional formatting with OpenXML SDK.

Output: ConditionalFormattingAboveAverage.xlsx

public void Run()
{
    using (var spreadsheetDocument = SpreadsheetDocument.Create("ConditionalFormattingAboveAverage.xlsx",
                SpreadsheetDocumentType.Workbook))
    {
        var workbookPart = spreadsheetDocument.AddWorkbookPart();
        var workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
        var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
        var workbook = new Workbook();

        var worksheet = new Worksheet();
        worksheetPart.Worksheet = worksheet;

        AddDataConditionalFormattingAboveAverage(workbookPart, worksheetPart);

        //Add the differential format with a themed color background fill
        workbookStylesPart.Stylesheet = new Stylesheet();
        var styleSheet = workbookStylesPart.Stylesheet;
        var dxfs = styleSheet.AppendChild(new DifferentialFormats());
        var dxf = dxfs.AppendChild(new DifferentialFormat());
        dxf.Fill = new Fill();
        dxf.Fill.PatternFill = new PatternFill();
        dxf.Fill.PatternFill.PatternType = PatternValues.Solid;
        dxf.Fill.PatternFill.BackgroundColor = new BackgroundColor();
        dxf.Fill.PatternFill.BackgroundColor.Theme = 4;

        var b1f1 = worksheet.AppendChild(new ConditionalFormatting());
        b1f1.SequenceOfReferences = new ListValue<StringValue>();
        b1f1.SequenceOfReferences.Items.Add("B1:F1");
        var cfRuleB1F1 = b1f1.AppendChild(new ConditionalFormattingRule());
        cfRuleB1F1.StdDev = 0;
        cfRuleB1F1.Priority = 0;
        cfRuleB1F1.FormatId = 0;
        cfRuleB1F1.Type = ConditionalFormatValues.AboveAverage;
        cfRuleB1F1.AppendChild(new Formula("SUM(B1)=0"));

        var b2f2 = worksheet.AppendChild(new ConditionalFormatting());
        b2f2.SequenceOfReferences = new ListValue<StringValue>();
        b2f2.SequenceOfReferences.Items.Add("B2:F2");
        var cfRuleB2F2 = b2f2.AppendChild(new ConditionalFormattingRule());
        cfRuleB2F2.StdDev = 0;
        cfRuleB2F2.Priority = 0;
        cfRuleB2F2.FormatId = 0;
        cfRuleB2F2.Type = ConditionalFormatValues.AboveAverage;
        //This sets to look for the Bottom values
        cfRuleB2F2.Bottom = true;
        cfRuleB2F2.AppendChild(new Formula("SUM(B2)=0"));

        worksheetPart.Worksheet.Save();
        var sheets = new Sheets();
        var sheet = new Sheet();
        sheet.Name = "Sheet1";
        sheet.SheetId = 1;
        sheet.Id = workbookPart.GetIdOfPart(worksheetPart);
        sheets.Append(sheet);
        workbook.Append(sheets);

        spreadsheetDocument.WorkbookPart.Workbook = workbook;
        spreadsheetDocument.WorkbookPart.Workbook.Save();
        spreadsheetDocument.Close();
    }
}

internal void AddDataConditionalFormattingAboveAverage(WorkbookPart workbookPart, WorksheetPart worksheetPart)
{
    SheetData sheetData = new SheetData();

    var sharedStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
    sharedStringPart.SharedStringTable = new SharedStringTable();
    sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Above Average Accent3")));
    sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Below Average Accent2")));

    var row1 = new Row() { RowIndex = 1 };
    var row2 = new Row() { RowIndex = 2 };

    sheetData.Append(row1);
    sheetData.Append(row2);

    var cellA1 = new Cell() { CellReference = "A1", DataType = new EnumValue<CellValues>(CellValues.SharedString) };
    cellA1.CellValue = new CellValue("0");
    var cellA2 = new Cell() { CellReference = "A2", DataType = new EnumValue<CellValues>(CellValues.SharedString) };
    cellA2.CellValue = new CellValue("1");

    row1.Append(cellA1);
    row2.Append(cellA2);

    row1.Append(new Cell() { CellReference = "B1", CellValue = new CellValue("1") });
    row1.Append(new Cell() { CellReference = "C1", CellValue = new CellValue("2") });
    row1.Append(new Cell() { CellReference = "D1", CellValue = new CellValue("3") });
    row1.Append(new Cell() { CellReference = "E1", CellValue = new CellValue("4") });
    row1.Append(new Cell() { CellReference = "F1", CellValue = new CellValue("5") });

    row2.Append(new Cell() { CellReference = "B2", CellValue = new CellValue("1") });
    row2.Append(new Cell() { CellReference = "C2", CellValue = new CellValue("2") });
    row2.Append(new Cell() { CellReference = "D2", CellValue = new CellValue("3") });
    row2.Append(new Cell() { CellReference = "E2", CellValue = new CellValue("4") });
    row2.Append(new Cell() { CellReference = "F2", CellValue = new CellValue("5") });

    worksheetPart.Worksheet.Append(sheetData);
        }