static void AddColorScale2ConditionalFormatting(IWorkbook workbook) { workbook.Calculate(); workbook.BeginUpdate(); try { Worksheet worksheet = workbook.Worksheets["cfBooks"]; workbook.Worksheets.ActiveWorksheet = worksheet; #region #ColorScale2ConditionalFormatting ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings; // Set the minimum threshold to the lowest value in the range of cells. ConditionalFormattingValue minPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.MinMax); // Set the maximum threshold to the highest value in the range of cells. ConditionalFormattingValue maxPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.MinMax); // Create the two-color scale rule to differentiate low and high values in cells C2 through D15. Blue represents the lower values and yellow represents the higher values. ColorScale2ConditionalFormatting cfRule = conditionalFormattings.AddColorScale2ConditionalFormatting(worksheet.Range["$C$2:$D$15"], minPoint, Color.FromArgb(255, 0x9D, 0xE9, 0xFA), maxPoint, Color.FromArgb(255, 0xFF, 0xF6, 0xA9)); #endregion #ColorScale2ConditionalFormatting // Add an explanation to the created rule. CellRange ruleExplanation = worksheet.Range["A17:G18"]; ruleExplanation.Value = "Examine cost distribution using a gradation of two colors. Blue represents the lower values and yellow represents the higher values."; } finally { workbook.EndUpdate(); } }
static void AddColorScale2ConditionalFormatting_Extremum(IWorkbook workbook) { #region #ColorScale2ConditionalFormatting_Extremum Worksheet worksheet = workbook.Worksheets["cfBooks"]; workbook.Worksheets.ActiveWorksheet = worksheet; ConditionalFormattingValue minPoint = worksheet.ConditionalFormattings.CreateValue(ConditionalFormattingValueType.MinMax); ConditionalFormattingValue maxPoint = worksheet.ConditionalFormattings.CreateValue(ConditionalFormattingValueType.MinMax); ColorScale2ConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddColorScale2ConditionalFormatting(worksheet.Range["$D$5:$E$18"], minPoint, Color.FromArgb(255, 0x9D, 0xE9, 0xFA), maxPoint, Color.FromArgb(255, 0xFF, 0xF6, 0xA9)); worksheet["B2"].Value = "Examine cost distribution using a gradation of two colors. Blue represents the lower values and yellow represents the higher values."; worksheet.Visible = true; #endregion #ColorScale2ConditionalFormatting_Extremum }