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
}