Ejemplo n.º 1
0
 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();
     }
 }
Ejemplo n.º 2
0
 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
 }