Conditional Formatting Icon Set with OpenXML SDK
The example shows how to insert icon set conditional formatting with OpenXML SDK.
Output: ConditionalFormattingIconSet.xlsx
public void ConditionalFormattingIconSet()
{
using (var spreadsheetDocument = SpreadsheetDocument.Create("ConditionalFormattingIconSet.xlsx",
SpreadsheetDocumentType.Workbook))
{
var workbookPart = spreadsheetDocument.AddWorkbookPart();
var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
var workbook = new Workbook();
var worksheet = new Worksheet();
worksheetPart.Worksheet = worksheet;
AddDataConditionalFormattingIconSet(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.Priority = 0;
cfRuleB1F1.Type = ConditionalFormatValues.IconSet;
var iconSetB1F1 = cfRuleB1F1.AppendChild(new IconSet());
iconSetB1F1.Percent = BooleanValue.FromBoolean(false);
iconSetB1F1.IconSetValue = IconSetValues.ThreeArrows;
iconSetB1F1.AppendChild(new ConditionalFormatValueObject()
{ Type = ConditionalFormatValueObjectValues.Percent, Val = "0" });
iconSetB1F1.AppendChild(new ConditionalFormatValueObject()
{ Type = ConditionalFormatValueObjectValues.Percent, Val = "33" });
iconSetB1F1.AppendChild(new ConditionalFormatValueObject()
{ Type = ConditionalFormatValueObjectValues.Percent, Val = "67" });
var b2f2 = worksheet.AppendChild(new ConditionalFormatting());
b2f2.SequenceOfReferences = new ListValue<StringValue>();
b2f2.SequenceOfReferences.Items.Add("B2:F2");
var cfRuleB2F2 = b1f1.AppendChild(new ConditionalFormattingRule());
cfRuleB2F2.StdDev = 0;
cfRuleB2F2.Priority = 0;
cfRuleB2F2.Type = ConditionalFormatValues.IconSet;
var iconSetB2F2 = cfRuleB2F2.AppendChild(new IconSet());
iconSetB2F2.Percent = BooleanValue.FromBoolean(false);
iconSetB2F2.IconSetValue = IconSetValues.ThreeTrafficLights2;
iconSetB2F2.AppendChild(new ConditionalFormatValueObject()
{ Type = ConditionalFormatValueObjectValues.Percent, Val = "0" });
iconSetB2F2.AppendChild(new ConditionalFormatValueObject()
{ Type = ConditionalFormatValueObjectValues.Percent, Val = "50" });
iconSetB2F2.AppendChild(new ConditionalFormatValueObject()
{ Type = ConditionalFormatValueObjectValues.Percent, Val = "75" });
var b3f3 = worksheet.AppendChild(new ConditionalFormatting());
b3f3.SequenceOfReferences = new ListValue<StringValue>();
b3f3.SequenceOfReferences.Items.Add("B3:F3");
var cfRuleB3F3 = b1f1.AppendChild(new ConditionalFormattingRule());
cfRuleB3F3.StdDev = 0;
cfRuleB3F3.Priority = 0;
cfRuleB3F3.Type = ConditionalFormatValues.IconSet;
var iconSetB3F3 = cfRuleB3F3.AppendChild(new IconSet());
iconSetB3F3.Percent = BooleanValue.FromBoolean(false);
iconSetB3F3.IconSetValue = IconSetValues.FiveRating;
iconSetB3F3.AppendChild(new ConditionalFormatValueObject()
{ Type = ConditionalFormatValueObjectValues.Percent, Val = "0" });
iconSetB3F3.AppendChild(new ConditionalFormatValueObject()
{ Type = ConditionalFormatValueObjectValues.Percent, Val = "20" });
iconSetB3F3.AppendChild(new ConditionalFormatValueObject()
{ Type = ConditionalFormatValueObjectValues.Percent, Val = "40" });
iconSetB3F3.AppendChild(new ConditionalFormatValueObject()
{ Type = ConditionalFormatValueObjectValues.Percent, Val = "60" });
iconSetB3F3.AppendChild(new ConditionalFormatValueObject()
{ Type = ConditionalFormatValueObjectValues.Percent, Val = "80" });
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 AddDataConditionalFormattingIconSet(WorkbookPart workbookPart, WorksheetPart worksheetPart)
{
SheetData sheetData = new SheetData();
var sharedStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
sharedStringPart.SharedStringTable = new SharedStringTable();
sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Default Three Arrows")));
sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Custom ThreeTrafficLights2 Minimum:25% Maximum:75%")));
sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Default Five Rating")));
var row1 = new Row() { RowIndex = 1 };
var row2 = new Row() { RowIndex = 2 };
var row3 = new Row() { RowIndex = 3 };
sheetData.Append(row1);
sheetData.Append(row2);
sheetData.Append(row3);
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");
row1.Append(cellA1);
row2.Append(cellA2);
row3.Append(cellA3);
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") });
worksheetPart.Worksheet.Append(sheetData);
}