Conditional Formatting Duplicate and Unique with OpenXML SDK

The example shows how to set duplicate and unique conditional formatting with OpenXML SDK.

Output: ConditionalFormattingDuplicateUnique.xlsx

        public void Run()
        {
            using (var spreadsheetDocument = SpreadsheetDocument.Create("ConditionalFormattingDuplicateUnique.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;

                AddDataConditionalFormattingDuplicateUnique(workbookPart, worksheetPart);

                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.DuplicateValues;
                cfRuleB1F1.AppendChild(new Formula("SUM(B1)"));

                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.UniqueValues;
                cfRuleB2F2.AppendChild(new Formula("SUM(B2)"));

                var b3f3 = worksheet.AppendChild(new ConditionalFormatting());
                b3f3.SequenceOfReferences = new ListValue<StringValue>();
                b3f3.SequenceOfReferences.Items.Add("B3:F3");
                var cfRuleB3F3 = b3f3.AppendChild(new ConditionalFormattingRule());
                cfRuleB3F3.StdDev = 0;
                cfRuleB3F3.Priority = 0;
                cfRuleB3F3.FormatId = 0;
                cfRuleB3F3.Type = ConditionalFormatValues.DuplicateValues;
                cfRuleB3F3.AppendChild(new Formula("SUM(B3)"));

                var b4f4 = worksheet.AppendChild(new ConditionalFormatting());
                b4f4.SequenceOfReferences = new ListValue<StringValue>();
                b4f4.SequenceOfReferences.Items.Add("B4:F4");
                var cfRuleB4F4 = b4f4.AppendChild(new ConditionalFormattingRule());
                cfRuleB4F4.StdDev = 0;
                cfRuleB4F4.Priority = 0;
                cfRuleB4F4.FormatId = 0;
                cfRuleB4F4.Type = ConditionalFormatValues.DuplicateValues;
                cfRuleB4F4.AppendChild(new Formula("SUM(B4)"));

                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 AddDataConditionalFormattingDuplicateUnique(WorkbookPart workbookPart, WorksheetPart worksheetPart)
        {
            SheetData sheetData = new SheetData();

            var sharedStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
            sharedStringPart.SharedStringTable = new SharedStringTable();
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("AB")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("BC")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("CD")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("DE")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Duplicate Numbers")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Unique Numbers")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Duplicate Text")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Unique Text")));

            var row1 = new Row() { RowIndex = 1 };
            var row2 = new Row() { RowIndex = 2 };
            var row3 = new Row() { RowIndex = 3 };
            var row4 = new Row() { RowIndex = 4 };

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

            var cellA1 = new Cell() { CellReference = "A1", DataType = new EnumValue<CellValues>(CellValues.SharedString) };
            cellA1.CellValue = new CellValue("4");
            var cellA2 = new Cell() { CellReference = "A2", DataType = new EnumValue<CellValues>(CellValues.SharedString) };
            cellA2.CellValue = new CellValue("5");
            var cellA3 = new Cell() { CellReference = "A3", DataType = new EnumValue<CellValues>(CellValues.SharedString) };
            cellA3.CellValue = new CellValue("6");
            var cellA4 = new Cell() { CellReference = "A4", DataType = new EnumValue<CellValues>(CellValues.SharedString) };
            cellA4.CellValue = new CellValue("7");

            row1.Append(cellA1);
            row2.Append(cellA2);
            row3.Append(cellA3);
            row4.Append(cellA4);

            row3.Append(new Cell
            {
                CellReference = "B3",
                CellValue = new CellValue("0"),
                DataType = new EnumValue<CellValues>(CellValues.SharedString)
            });
            row3.Append(new Cell
            {
                CellReference = "C3",
                CellValue = new CellValue("1"),
                DataType = new EnumValue<CellValues>(CellValues.SharedString)
            });
            row3.Append(new Cell
            {
                CellReference = "D3",
                CellValue = new CellValue("2"),
                DataType = new EnumValue<CellValues>(CellValues.SharedString)
            });
            row3.Append(new Cell
            {
                CellReference = "E3",
                CellValue = new CellValue("3"),
                DataType = new EnumValue<CellValues>(CellValues.SharedString)
            });
            row3.Append(new Cell
            {
                CellReference = "F3",
                CellValue = new CellValue("0"),
                DataType = new EnumValue<CellValues>(CellValues.SharedString)
            });


            row4.Append(new Cell
            {
                CellReference = "B4",
                CellValue = new CellValue("0"),
                DataType = new EnumValue<CellValues>(CellValues.SharedString)
            });

            row4.Append(new Cell
            {
                CellReference = "C4",
                CellValue = new CellValue("1"),
                DataType = new EnumValue<CellValues>(CellValues.SharedString)
            });
            row4.Append(new Cell
            {
                CellReference = "D4",
                CellValue = new CellValue("2"),
                DataType = new EnumValue<CellValues>(CellValues.SharedString)
            });
            row4.Append(new Cell
            {
                CellReference = "E4",
                CellValue = new CellValue("3"),
                DataType = new EnumValue<CellValues>(CellValues.SharedString)
            });
            row4.Append(new Cell
            {
                CellReference = "F4",
                CellValue = new CellValue("0"),
                DataType = new EnumValue<CellValues>(CellValues.SharedString)
            });
            worksheetPart.Worksheet.Append(sheetData);
        }