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