Conditional Formatting Cell Value with OpenXML SDK

The example shows how to insert specific text type conditional formattings with OpenXML SDK.

Output: ConditionalFormattingSpecificText.xlsx

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

                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 worksheet = new Worksheet();
                worksheetPart.Worksheet = worksheet;

                AddDataConditionalFormattingSpecificText(workbookPart, worksheetPart);

                var b1e1 = worksheet.AppendChild(new ConditionalFormatting());
                b1e1.SequenceOfReferences = new ListValue<StringValue>();
                b1e1.SequenceOfReferences.Items.Add("B1:E1");
                var cfRuleB1E1 = b1e1.AppendChild(new ConditionalFormattingRule());
                cfRuleB1E1.StdDev = 0;
                cfRuleB1E1.Text = "BC";
                cfRuleB1E1.Priority = 0;
                cfRuleB1E1.FormatId = 0;
                cfRuleB1E1.Type = ConditionalFormatValues.ContainsText;
                cfRuleB1E1.AppendChild(new Formula("ISERROR(SEARCH(\"BC\",B1))"));

                var b2e2 = worksheet.AppendChild(new ConditionalFormatting());
                b2e2.SequenceOfReferences = new ListValue<StringValue>();
                b2e2.SequenceOfReferences.Items.Add("B2:E2");
                var cfRuleB2E2 = b2e2.AppendChild(new ConditionalFormattingRule());
                cfRuleB2E2.StdDev = 0;
                cfRuleB2E2.Text = "FG";
                cfRuleB2E2.Operator = ConditionalFormattingOperatorValues.ContainsText;
                cfRuleB2E2.Priority = 0;
                cfRuleB2E2.FormatId = 0;
                cfRuleB2E2.Type = ConditionalFormatValues.ContainsText;
                cfRuleB2E2.AppendChild(new Formula("RIGHT(B2,2)=\"FG\""));

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

            var sharedStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
            sharedStringPart.SharedStringTable = new SharedStringTable();
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("ABC")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("BCD")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("DEF")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("EFG")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Text does not contain BC")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Text ending with FG")));

            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("4");
            var cellA2 = new Cell() { CellReference = "A2", DataType = new EnumValue<CellValues>(CellValues.SharedString) };
            cellA2.CellValue = new CellValue("5");

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

            row1.Append(new Cell
            {
                CellReference = "B1", CellValue = new CellValue("0"),
                DataType = new EnumValue<CellValues>(CellValues.SharedString)
            });
            row1.Append(new Cell
            {
                CellReference = "C1", CellValue = new CellValue("1"),
                DataType = new EnumValue<CellValues>(CellValues.SharedString)
            });
            row1.Append(new Cell
            {
                CellReference = "D1", CellValue = new CellValue("2"),
                DataType = new EnumValue<CellValues>(CellValues.SharedString)
            });
            row1.Append(new Cell
            {
                CellReference = "E1", CellValue = new CellValue("3"),
                DataType = new EnumValue<CellValues>(CellValues.SharedString)
            });

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

            row2.Append(new Cell
            {
                CellReference = "C2",
                CellValue = new CellValue("1"),
                DataType = new EnumValue<CellValues>(CellValues.SharedString)
            });
            row2.Append(new Cell
            {
                CellReference = "D2",
                CellValue = new CellValue("2"),
                DataType = new EnumValue<CellValues>(CellValues.SharedString)
            });
            row2.Append(new Cell
            {
                CellReference = "E2",
                CellValue = new CellValue("3"),
                DataType = new EnumValue<CellValues>(CellValues.SharedString)
            });

            worksheetPart.Worksheet.Append(sheetData);
        }