static void AddIconSetConditionalFormatting(IWorkbook workbook) { #region #IconSetConditionalFormatting Worksheet worksheet = workbook.Worksheets["cfBooks"]; workbook.Worksheets.ActiveWorksheet = worksheet; ConditionalFormattingIconSetValue minPoint = worksheet.ConditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Formula, "=MIN($F$5:$F$18)", ConditionalFormattingValueOperator.GreaterOrEqual); ConditionalFormattingIconSetValue midPoint = worksheet.ConditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Number, "0", ConditionalFormattingValueOperator.GreaterOrEqual); ConditionalFormattingIconSetValue maxPoint = worksheet.ConditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Number, "0.01", ConditionalFormattingValueOperator.GreaterOrEqual); IconSetConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddIconSetConditionalFormatting(worksheet.Range["$F$5:$F$18"], IconSetType.Arrows3, new ConditionalFormattingIconSetValue[] { minPoint, midPoint, maxPoint }); cfRule.IsCustom = true; ConditionalFormattingCustomIcon cfCustomIcon = new ConditionalFormattingCustomIcon(); cfCustomIcon.IconSet = IconSetType.TrafficLights13; cfCustomIcon.IconIndex = 1; cfRule.SetCustomIcon(1, cfCustomIcon); cfRule.ShowValue = false; worksheet["B2"].Value = "In the report below identify upward and downward cost trends."; worksheet.Visible = true; #endregion #IconSetConditionalFormatting }
static void AddIconSetConditionalFormatting(IWorkbook workbook) { workbook.Calculate(); workbook.BeginUpdate(); try { Worksheet worksheet = workbook.Worksheets["cfBooks"]; workbook.Worksheets.ActiveWorksheet = worksheet; #region #IconSetConditionalFormatting ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings; // Set the first threshold to the lowest value in the range of cells using the MIN() formula. ConditionalFormattingIconSetValue minPoint = conditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Formula, "=MIN($E$2:$E$15)", ConditionalFormattingValueOperator.GreaterOrEqual); // Set the second threshold to 0. ConditionalFormattingIconSetValue midPoint = conditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Number, "0", ConditionalFormattingValueOperator.GreaterOrEqual); // Set the third threshold to 0.01. ConditionalFormattingIconSetValue maxPoint = conditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Number, "0.01", ConditionalFormattingValueOperator.GreaterOrEqual); // Create the rule to apply a specific icon from the three arrow icon set to each cell in the range E2:E15 based on its value. IconSetConditionalFormatting cfRule = conditionalFormattings.AddIconSetConditionalFormatting(worksheet.Range["$E$2:$E$15"], IconSetType.Arrows3, new ConditionalFormattingIconSetValue[] { minPoint, midPoint, maxPoint }); // Specify the custom icon to be displayed if the second condition is true. // To do this, set the IconSetConditionalFormatting.IsCustom property to true, which is false by default. cfRule.IsCustom = true; // Initialize the ConditionalFormattingCustomIcon object. ConditionalFormattingCustomIcon cfCustomIcon = new ConditionalFormattingCustomIcon(); // Specify the icon set where you wish to get the icon. cfCustomIcon.IconSet = IconSetType.TrafficLights13; // Specify the index of the desired icon in the set. cfCustomIcon.IconIndex = 1; // Add the custom icon at the specified position in the initial icon set. cfRule.SetCustomIcon(1, cfCustomIcon); // Hide values of cells to which the rule is applied. cfRule.ShowValue = false; #endregion #IconSetConditionalFormatting // Add an explanation to the created rule. CellRange ruleExplanation = worksheet.Range["A17:G18"]; ruleExplanation.Value = "Identify upward and downward cost trends."; } finally { workbook.EndUpdate(); } }