static void AddAverageConditionalFormatting(IWorkbook workbook) { workbook.Calculate(); workbook.BeginUpdate(); try { Worksheet worksheet = workbook.Worksheets["cfBooks"]; workbook.Worksheets.ActiveWorksheet = worksheet; #region #AverageConditionalFormatting ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings; // Create the rule highlighting values that are above the average in cells C2 through C15. AverageConditionalFormatting cfRule1 = conditionalFormattings.AddAverageConditionalFormatting(worksheet["$C$2:$C$15"], ConditionalFormattingAverageCondition.AboveOrEqual); // Specify formatting options to be applied to cells if the condition is true. // Set the background color to yellow. cfRule1.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xFA, 0xF7, 0xAA); // Set the font color to red. cfRule1.Formatting.Font.Color = Color.Red; // Create the rule highlighting values that are one standard deviation below the mean in cells D2 through D15. AverageConditionalFormatting cfRule2 = conditionalFormattings.AddAverageConditionalFormatting(worksheet["$D$2:$D$15"], ConditionalFormattingAverageCondition.BelowOrEqual, 1); // Specify formatting options to be applied to cells if the conditions is true. // Set the background color to light-green. cfRule2.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0x9F, 0xFB, 0x69); // Set the font color to blue-violet. cfRule2.Formatting.Font.Color = Color.BlueViolet; #endregion #AverageConditionalFormatting // Add an explanation to the created rule. CellRange ruleExplanation = worksheet.Range["A17:G18"]; ruleExplanation.Value = "Determine cost values that are above the average in the first quarter and one standard deviation below the mean in the second quarter."; } finally { workbook.EndUpdate(); } }
static void AddAverageConditionalFormatting(IWorkbook workbook) { #region #AverageConditionalFormatting Worksheet worksheet = workbook.Worksheets["cfBooks"]; workbook.Worksheets.ActiveWorksheet = worksheet; ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings; AverageConditionalFormatting cfRule1 = conditionalFormattings.AddAverageConditionalFormatting(worksheet["$D$5:$D$18"], ConditionalFormattingAverageCondition.AboveOrEqual); cfRule1.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xFA, 0xF7, 0xAA); cfRule1.Formatting.Font.Color = Color.Red; AverageConditionalFormatting cfRule2 = conditionalFormattings.AddAverageConditionalFormatting(worksheet["$E$5:$E$18"], ConditionalFormattingAverageCondition.BelowOrEqual, 1); cfRule2.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0x9F, 0xFB, 0x69); cfRule2.Formatting.Font.Color = Color.BlueViolet; worksheet["B2"].Value = "In the report below determine cost values that are above the average in the first quarter and one standard deviation below the average in the second quarter."; worksheet.Visible = true; #endregion #AverageConditionalFormatting }