Show / Hide Table of Contents

Conditional Formatting Cell Value with OpenXML SDK

The example shows how to following cell value based conditional formattings with OpenXML SDK:

  • Cell value between 2 and 4
  • Cell value not between 2 and 4
  • Cell value equals 3
  • Cell value does not equal 3
  • Cell value greater than 3
  • Cell value less than 3
  • Cell value greater than or equal to 3
  • Cell value less than or equal to 3

Output: ConditionalFormattingCellValue.xlsx

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

                AddDataConditionalFormattingCellValue(workbookPart, worksheetPart);

                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.Operator = ConditionalFormattingOperatorValues.Between;
                cfRuleB1F1.Priority = 0;
                cfRuleB1F1.FormatId = 0;
                cfRuleB1F1.Type = ConditionalFormatValues.CellIs;
                cfRuleB1F1.AppendChild(new Formula("2"));
                cfRuleB1F1.AppendChild(new Formula("4"));

                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.Operator = ConditionalFormattingOperatorValues.NotBetween;
                cfRuleB2F2.Priority = 0;
                cfRuleB2F2.FormatId = 0;
                cfRuleB2F2.Type = ConditionalFormatValues.CellIs;
                cfRuleB2F2.AppendChild(new Formula("2"));
                cfRuleB2F2.AppendChild(new Formula("4"));

                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.Operator = ConditionalFormattingOperatorValues.Equal;
                cfRuleB3F3.Priority = 0;
                cfRuleB3F3.FormatId = 0;
                cfRuleB3F3.Type = ConditionalFormatValues.CellIs;
                cfRuleB3F3.AppendChild(new Formula("3"));

                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.Operator = ConditionalFormattingOperatorValues.NotEqual;
                cfRuleB4F4.Priority = 0;
                cfRuleB4F4.FormatId = 0;
                cfRuleB4F4.Type = ConditionalFormatValues.CellIs;
                cfRuleB4F4.AppendChild(new Formula("3"));

                var b5f5 = worksheet.AppendChild(new ConditionalFormatting());
                b5f5.SequenceOfReferences = new ListValue<StringValue>();
                b5f5.SequenceOfReferences.Items.Add("B5:F5");
                var cfRuleB5F5 = b5f5.AppendChild(new ConditionalFormattingRule());
                cfRuleB5F5.StdDev = 0;
                cfRuleB5F5.Operator = ConditionalFormattingOperatorValues.GreaterThan;
                cfRuleB5F5.Priority = 0;
                cfRuleB5F5.FormatId = 0;
                cfRuleB5F5.Type = ConditionalFormatValues.CellIs;
                cfRuleB5F5.AppendChild(new Formula("3"));

                var b6f6 = worksheet.AppendChild(new ConditionalFormatting());
                b6f6.SequenceOfReferences = new ListValue<StringValue>();
                b6f6.SequenceOfReferences.Items.Add("B6:F6");
                var cfRuleB6F6 = b6f6.AppendChild(new ConditionalFormattingRule());
                cfRuleB6F6.StdDev = 0;
                cfRuleB6F6.Operator = ConditionalFormattingOperatorValues.LessThan;
                cfRuleB6F6.Priority = 0;
                cfRuleB6F6.FormatId = 0;
                cfRuleB6F6.Type = ConditionalFormatValues.CellIs;
                cfRuleB6F6.AppendChild(new Formula("3"));

                var b7f7 = worksheet.AppendChild(new ConditionalFormatting());
                b7f7.SequenceOfReferences = new ListValue<StringValue>();
                b7f7.SequenceOfReferences.Items.Add("B7:F7");
                var cfRuleB7F7 = b7f7.AppendChild(new ConditionalFormattingRule());
                cfRuleB7F7.StdDev = 0;
                cfRuleB7F7.Operator = ConditionalFormattingOperatorValues.GreaterThanOrEqual;
                cfRuleB7F7.Priority = 0;
                cfRuleB7F7.FormatId = 0;
                cfRuleB7F7.Type = ConditionalFormatValues.CellIs;
                cfRuleB7F7.AppendChild(new Formula("3"));

                var b8f8 = worksheet.AppendChild(new ConditionalFormatting());
                b8f8.SequenceOfReferences = new ListValue<StringValue>();
                b6f6.SequenceOfReferences.Items.Add("B8:F8");
                var cfRuleB8F8 = b8f8.AppendChild(new ConditionalFormattingRule());
                cfRuleB8F8.StdDev = 0;
                cfRuleB8F8.Operator = ConditionalFormattingOperatorValues.LessThan;
                cfRuleB8F8.Priority = 0;
                cfRuleB8F8.FormatId = 0;
                cfRuleB8F8.Type = ConditionalFormatValues.CellIs;
                cfRuleB8F8.AppendChild(new Formula("3"));

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

            var sharedStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
            sharedStringPart.SharedStringTable = new SharedStringTable();
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Cell Value between 2 and 4")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Cell Value not between 2 and 4")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Cell Value equal to 3")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Cell Value not equal to 3")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Cell Value greater than 3")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Cell Value less than 3")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Cell Value greater than or equal to 3")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Cell Value less than or equal to 3")));

            var row1 = new Row() { RowIndex = 1 };
            var row2 = new Row() { RowIndex = 2 };
            var row3 = new Row() { RowIndex = 3 };
            var row4 = new Row() { RowIndex = 4 };
            var row5 = new Row() { RowIndex = 5 };
            var row6 = new Row() { RowIndex = 6 };
            var row7 = new Row() { RowIndex = 7 };
            var row8 = new Row() { RowIndex = 8 };
            sheetData.Append(row1);
            sheetData.Append(row2);
            sheetData.Append(row3);
            sheetData.Append(row4);
            sheetData.Append(row5);
            sheetData.Append(row6);
            sheetData.Append(row7);
            sheetData.Append(row8);

            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");
            var cellA3 = new Cell() { CellReference = "A3", DataType = new EnumValue<CellValues>(CellValues.SharedString) };
            cellA3.CellValue = new CellValue("2");
            var cellA4 = new Cell() { CellReference = "A4", DataType = new EnumValue<CellValues>(CellValues.SharedString) };
            cellA4.CellValue = new CellValue("3");
            var cellA5 = new Cell() { CellReference = "A5", DataType = new EnumValue<CellValues>(CellValues.SharedString) };
            cellA5.CellValue = new CellValue("4");
            var cellA6 = new Cell() { CellReference = "A6", DataType = new EnumValue<CellValues>(CellValues.SharedString) };
            cellA6.CellValue = new CellValue("5");
            var cellA7 = new Cell() { CellReference = "A7", DataType = new EnumValue<CellValues>(CellValues.SharedString) };
            cellA7.CellValue = new CellValue("6");
            var cellA8 = new Cell() { CellReference = "A8", DataType = new EnumValue<CellValues>(CellValues.SharedString) };
            cellA8.CellValue = new CellValue("7");

            row1.Append(cellA1);
            row2.Append(cellA2);
            row3.Append(cellA3);
            row4.Append(cellA4);
            row5.Append(cellA5);
            row6.Append(cellA6);
            row7.Append(cellA7);
            row8.Append(cellA8);

            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") });

            row3.Append(new Cell() { CellReference = "B3", CellValue = new CellValue("1") });
            row3.Append(new Cell() { CellReference = "C3", CellValue = new CellValue("2") });
            row3.Append(new Cell() { CellReference = "D3", CellValue = new CellValue("3") });
            row3.Append(new Cell() { CellReference = "E3", CellValue = new CellValue("4") });
            row3.Append(new Cell() { CellReference = "F3", CellValue = new CellValue("5") });

            row4.Append(new Cell() { CellReference = "B4", CellValue = new CellValue("1") });
            row4.Append(new Cell() { CellReference = "C4", CellValue = new CellValue("2") });
            row4.Append(new Cell() { CellReference = "D4", CellValue = new CellValue("3") });
            row4.Append(new Cell() { CellReference = "E4", CellValue = new CellValue("4") });
            row4.Append(new Cell() { CellReference = "F4", CellValue = new CellValue("5") });

            row5.Append(new Cell() { CellReference = "B5", CellValue = new CellValue("1") });
            row5.Append(new Cell() { CellReference = "C5", CellValue = new CellValue("2") });
            row5.Append(new Cell() { CellReference = "D5", CellValue = new CellValue("3") });
            row5.Append(new Cell() { CellReference = "E5", CellValue = new CellValue("4") });
            row5.Append(new Cell() { CellReference = "F5", CellValue = new CellValue("5") });

            row6.Append(new Cell() { CellReference = "B6", CellValue = new CellValue("1") });
            row6.Append(new Cell() { CellReference = "C6", CellValue = new CellValue("2") });
            row6.Append(new Cell() { CellReference = "D6", CellValue = new CellValue("3") });
            row6.Append(new Cell() { CellReference = "E6", CellValue = new CellValue("4") });
            row6.Append(new Cell() { CellReference = "F6", CellValue = new CellValue("5") });

            row7.Append(new Cell() { CellReference = "B7", CellValue = new CellValue("1") });
            row7.Append(new Cell() { CellReference = "C7", CellValue = new CellValue("2") });
            row7.Append(new Cell() { CellReference = "D7", CellValue = new CellValue("3") });
            row7.Append(new Cell() { CellReference = "E7", CellValue = new CellValue("4") });
            row7.Append(new Cell() { CellReference = "F7", CellValue = new CellValue("5") });

            row8.Append(new Cell() { CellReference = "B8", CellValue = new CellValue("1") });
            row8.Append(new Cell() { CellReference = "C8", CellValue = new CellValue("2") });
            row8.Append(new Cell() { CellReference = "D8", CellValue = new CellValue("3") });
            row8.Append(new Cell() { CellReference = "E8", CellValue = new CellValue("4") });
            row8.Append(new Cell() { CellReference = "F8", CellValue = new CellValue("5") });
            worksheetPart.Worksheet.Append(sheetData);
        }
Back to top Generated by DocFX