static void AddExpressionConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #ExpressionConditionalFormatting
// Create the rule to identify values that are above the average in cells F2 through F15.
ExpressionConditionalFormatting cfRule =
worksheet.ConditionalFormattings.AddExpressionConditionalFormatting(worksheet["$F$2:$F$15"], ConditionalFormattingExpressionCondition.GreaterThan, "=AVERAGE($F$2:$F$15)");
// Specify formatting options to be applied to cells if the condition is true.
// Set the background color to yellow.
cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xFA, 0xF7, 0xAA);
// Set the font color to red.
cfRule.Formatting.Font.Color = Color.Red;
#endregion #ExpressionConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "Identify book prices that are greater than the average price.";
}
finally
{
workbook.EndUpdate();
}
}
public static void ApplyTemperatureConditionalFormatting(Worksheet sheet)
{
ConditionalFormattingCollection conditionalFormattings = sheet.ConditionalFormattings;
ConditionalFormattingValue minPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.MinMax);
ConditionalFormattingValue midPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Percentile, "50");
ConditionalFormattingValue maxPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.MinMax);
conditionalFormattings.AddColorScale3ConditionalFormatting(sheet.Range["$C$4:$C$60"], minPoint, Color.FromArgb(255, 0x65, 0x92, 0xAF), midPoint, Color.FromArgb(255, 0xF2, 0xA1, 0x6A), maxPoint, Color.FromArgb(255, 0xFF, 0xD5, 0x55));
ExpressionConditionalFormatting cfRule = conditionalFormattings.AddExpressionConditionalFormatting(sheet.Range["$C$4:$C$60"], ConditionalFormattingExpressionCondition.GreaterThan, "40");
cfRule.Formatting.Font.Color = Color.White;
}
static void AddExpressionConditionalFormatting(IWorkbook workbook)
{
#region #ExpressionConditionalFormatting
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
ExpressionConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddExpressionConditionalFormatting(worksheet["$G$5:$G$18"], ConditionalFormattingExpressionCondition.GreaterThan, "=AVERAGE($G$5:$G$18)");
cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xFA, 0xF7, 0xAA);
cfRule.Formatting.Font.Color = Color.Red;
worksheet["B2"].Value = "In the report below identify price values that are above the average.";
worksheet.Visible = true;
#endregion #ExpressionConditionalFormatting
}
public static void ApplyExportsYearlyChangeConditionalFormatting(Worksheet sheet)
{
ConditionalFormattingCollection conditionalFormattings = sheet.ConditionalFormattings;
ExpressionConditionalFormatting cfRule =
conditionalFormattings.AddExpressionConditionalFormatting(sheet["Table[Exports 1Y Chg]"], ConditionalFormattingExpressionCondition.GreaterThan, "0");
cfRule.Formatting.Font.Color = Color.Green;
ExpressionConditionalFormatting cfRule2 =
conditionalFormattings.AddExpressionConditionalFormatting(sheet["Table[Exports 1Y Chg]"], ConditionalFormattingExpressionCondition.LessThan, "0");
cfRule2.Formatting.Font.Color = Color.Red;
}
