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