Conditional Formatting Cell Value with OpenXML SDK

The example shows how to insert dates occurring type conditional formattings with OpenXML SDK.

Output: ConditionalFormattingDatesOccurring.xlsx

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

                //TODO Date Format
                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;

                AddDataConditionalFormattingDatesOccurring(workbookPart, worksheetPart);

                var b1d1 = worksheet.AppendChild(new ConditionalFormatting());
                b1d1.SequenceOfReferences = new ListValue<StringValue>();
                b1d1.SequenceOfReferences.Items.Add("B1:D1");
                var cfRuleB1D1 = b1d1.AppendChild(new ConditionalFormattingRule());
                cfRuleB1D1.StdDev = 0;
                cfRuleB1D1.Priority = 0;
                cfRuleB1D1.FormatId = 0;
                cfRuleB1D1.Type = ConditionalFormatValues.TimePeriod;
                cfRuleB1D1.AppendChild(new Formula("FLOOR(B1,1)=TODAY()+1"));
                
                var b2f2 = worksheet.AppendChild(new ConditionalFormatting());
                b2f2.SequenceOfReferences = new ListValue<StringValue>();
                b2f2.SequenceOfReferences.Items.Add("B2:D2");
                var cfRuleB2F2 = b2f2.AppendChild(new ConditionalFormattingRule());
                cfRuleB2F2.StdDev = 0;
                cfRuleB2F2.Priority = 0;
                cfRuleB2F2.FormatId = 0;
                cfRuleB2F2.Type = ConditionalFormatValues.TimePeriod;
                cfRuleB2F2.AppendChild(new Formula("AND(MONTH(B2)=MONTH(EDATE(TODAY(),0-1)),YEAR(B2)=YEAR(EDATE(TODAY(),0-1)))"));
                
                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 AddDataConditionalFormattingDatesOccurring(WorkbookPart workbookPart, WorksheetPart worksheetPart)
        {
            SheetData sheetData = new SheetData();

            var sharedStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
            sharedStringPart.SharedStringTable = new SharedStringTable();
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Date Tomorrow")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Date Last Month")));
            
            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", CellFormula = new CellFormula("NOW()-1")});
            row1.Append(new Cell() { CellReference = "C1", CellFormula = new CellFormula("NOW()") });
            row1.Append(new Cell() { CellReference = "D1", CellFormula = new CellFormula("NOW()+1") });

            row2.Append(new Cell() { CellReference = "B2", CellFormula = new CellFormula("EDATE(NOW(),-1)") });
            row2.Append(new Cell() { CellReference = "C2", CellFormula = new CellFormula("NOW()") });
            row2.Append(new Cell() { CellReference = "D2", CellFormula = new CellFormula("EDATE(NOW(),1)") });

            worksheetPart.Worksheet.Append(sheetData);
        }