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