static void AddColorScale3ConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #ColorScale3ConditionalFormatting
ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings;
// Set the minimum threshold to the lowest value in the range of cells using the MIN() formula.
ConditionalFormattingValue minPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Formula, "=MIN($C$2:$D$15)");
// Set the midpoint threshold to the 50th percentile.
ConditionalFormattingValue midPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Percentile, "50");
// Set the maximum threshold to the highest value in the range of cells using the MAX() formula.
ConditionalFormattingValue maxPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Number, "=MAX($C$2:$D$15)");
// Create the three-color scale rule to determine how values in cells C2 through D15 vary. Red represents the lower values, yellow represents the medium values and sky blue represents the higher values.
ColorScale3ConditionalFormatting cfRule = conditionalFormattings.AddColorScale3ConditionalFormatting(worksheet.Range["$C$2:$D$15"], minPoint, Color.Red, midPoint, Color.Yellow, maxPoint, Color.SkyBlue);
#endregion #ColorScale3ConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "Examine cost distribution using a gradation of three colors. Red represents the lower values, yellow represents the medium values and sky blue represents the higher values.";
}
finally
{
workbook.EndUpdate();
}
}
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 AddComplexRangeConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfReport"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #ComplexRangeConditionalFormatting
// Create a union range to which the rule will be applied.
CellRange complexRange = worksheet.Range.Union(worksheet["G3:G6"], worksheet["G9:G12"]);
ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings;
// Specify the automatic minimum value for the shortest bar.
ConditionalFormattingValue lowBound = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Auto);
// Specify the automatic maximum value for the longest bar.
ConditionalFormattingValue highBound = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Auto);
// Create the rule to compare yearly total values for different states.
DataBarConditionalFormatting cfRule = conditionalFormattings.AddDataBarConditionalFormatting(complexRange, lowBound, highBound, Color.FromArgb(0x29, 0x3E, 0x6A));
#endregion #ComplexRangeConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["B15"];
ruleExplanation.Value = "Compare values in the \"Yearly Total\" column using data bars.";
}
finally
{
workbook.EndUpdate();
}
}
public static void ApplyHumidityConditionalFormatting(Worksheet sheet)
{
ConditionalFormattingCollection conditionalFormattings = sheet.ConditionalFormattings;
ConditionalFormattingValue lowBound = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Auto);
ConditionalFormattingValue highBound = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Auto);
DataBarConditionalFormatting cfRule = conditionalFormattings.AddDataBarConditionalFormatting(sheet.Range["$E$4:$E$60"], lowBound, highBound, Color.FromArgb(255, 0xD6, 0xD6, 0xD6));
cfRule.GradientFill = false;
}
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 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
}
static void AddColorScale3ConditionalFormatting(IWorkbook workbook)
{
#region #ColorScale3ConditionalFormatting
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
ConditionalFormattingValue minPoint = worksheet.ConditionalFormattings.CreateValue(ConditionalFormattingValueType.Formula, "=MIN($E$5:$F$18)");
ConditionalFormattingValue midPoint = worksheet.ConditionalFormattings.CreateValue(ConditionalFormattingValueType.Percentile, "50");
ConditionalFormattingValue maxPoint = worksheet.ConditionalFormattings.CreateValue(ConditionalFormattingValueType.Number, "=MAX($E$5:$F$18)");
ColorScale3ConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddColorScale3ConditionalFormatting(worksheet.Range["$D$5:$E$18"], minPoint, Color.Red, midPoint, Color.Yellow, maxPoint, Color.SkyBlue);
worksheet["B2"].Value = "Examine cost distribution using a gradation of three colors. Red represents the lower values, yellow represents the medium values and sky blue represents the higher values.";
worksheet.Visible = true;
#endregion #ColorScale3ConditionalFormatting
}
public static void ApplyBalanceChangeConditionalFormatting(Worksheet sheet)
{
ConditionalFormattingCollection conditionalFormattings = sheet.ConditionalFormattings;
ConditionalFormattingValue lowBound1 = sheet.ConditionalFormattings.CreateValue(ConditionalFormattingValueType.Auto);
ConditionalFormattingValue highBound1 = sheet.ConditionalFormattings.CreateValue(ConditionalFormattingValueType.Auto);
DataBarConditionalFormatting cfRule1 = conditionalFormattings.AddDataBarConditionalFormatting(sheet["Table[Balance 1Y Chg]"], lowBound1, highBound1, Color.FromArgb(0x9E, 0xAD, 0xFF));
cfRule1.BorderColor = Color.FromArgb(0x9E, 0xAD, 0xFF);
cfRule1.NegativeBarColor = Color.FromArgb(0xFF, 0x9E, 0xE7);
cfRule1.NegativeBarBorderColor = Color.FromArgb(0xFF, 0x9E, 0xE7);
cfRule1.AxisPosition = ConditionalFormattingDataBarAxisPosition.Middle;
cfRule1.AxisColor = Color.Black;
}
static void AddDataBarConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #DataBarConditionalFormatting
ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings;
// Set the value corresponding to the shortest bar to the lowest value.
ConditionalFormattingValue lowBound1 = conditionalFormattings.CreateValue(ConditionalFormattingValueType.MinMax);
// Set the value corresponding to the longest bar to the highest value.
ConditionalFormattingValue highBound1 = conditionalFormattings.CreateValue(ConditionalFormattingValueType.MinMax);
// Create the rule to compare values in cells E2 through E15 using data bars.
DataBarConditionalFormatting cfRule1 = conditionalFormattings.AddDataBarConditionalFormatting(worksheet.Range["$E$2:$E$15"], lowBound1, highBound1, DXColor.Green);
// Set the positive bar border color to green.
cfRule1.BorderColor = DXColor.Green;
// Set the negative bar color to red.
cfRule1.NegativeBarColor = DXColor.Red;
// Set the negative bar border color to red.
cfRule1.NegativeBarBorderColor = DXColor.Red;
// Set the axis position to display the axis in the middle of the cell.
cfRule1.AxisPosition = ConditionalFormattingDataBarAxisPosition.Middle;
// Set the axis color to dark blue.
cfRule1.AxisColor = Color.DarkBlue;
// Set the value corresponding to the shortest bar to 0 percent.
ConditionalFormattingValue lowBound2 = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Percent, "0");
// Set the value corresponding to the longest bar to 100 percent.
ConditionalFormattingValue highBound2 = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Percent, "100");
// Create the rule to compare values in cells G2 through G15 using data bars.
DataBarConditionalFormatting cfRule2 = conditionalFormattings.AddDataBarConditionalFormatting(worksheet.Range["$G$2:$G$15"], lowBound2, highBound2, DXColor.SkyBlue);
// Set the data bar border color to sky blue.
cfRule2.BorderColor = DXColor.SkyBlue;
// Specify the solid fill type.
cfRule2.GradientFill = false;
// Hide values of cells to which the rule is applied.
cfRule2.ShowValue = false;
#endregion #DataBarConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "Compare values in the \"Cost Trend\" and \"Markup\" columns using data bars.";
}
finally
{
workbook.EndUpdate();
}
}
static void AddDataBarConditionalFormatting(IWorkbook workbook)
{
#region #DataBarConditionalFormatting
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
ConditionalFormattingValue lowBound1 = worksheet.ConditionalFormattings.CreateValue(ConditionalFormattingValueType.MinMax);
ConditionalFormattingValue highBound1 = worksheet.ConditionalFormattings.CreateValue(ConditionalFormattingValueType.MinMax);
DataBarConditionalFormatting cfRule1 = worksheet.ConditionalFormattings.AddDataBarConditionalFormatting(worksheet.Range["$F$5:$F$18"], lowBound1, highBound1, Color.Green);
cfRule1.BorderColor = Color.Green;
cfRule1.NegativeBarColor = Color.Red;
cfRule1.NegativeBarBorderColor = Color.Red;
cfRule1.AxisPosition = ConditionalFormattingDataBarAxisPosition.Middle;
cfRule1.AxisColor = Color.DarkBlue;
ConditionalFormattingValue lowBound2 = worksheet.ConditionalFormattings.CreateValue(ConditionalFormattingValueType.Percent, "0");
ConditionalFormattingValue highBound2 = worksheet.ConditionalFormattings.CreateValue(ConditionalFormattingValueType.Percent, "100");
DataBarConditionalFormatting cfRule2 = worksheet.ConditionalFormattings.AddDataBarConditionalFormatting(worksheet.Range["$H$5:$H$18"], lowBound2, highBound2, Color.SkyBlue);
cfRule2.BorderColor = Color.SkyBlue;
cfRule2.GradientFill = false;
cfRule2.ShowValue = false;
worksheet["B2"].Value = "Compare values in the \"Cost Trend\" and \"Markup\" columns using data bars.";
worksheet.Visible = true;
#endregion #DataBarConditionalFormatting
}