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; }