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 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();
}
}
public static void ApplyTopImportsConditionalFormatting(Worksheet sheet)
{
ConditionalFormattingCollection conditionalFormattings = sheet.ConditionalFormattings;
RankConditionalFormatting cfRule2 = conditionalFormattings.AddRankConditionalFormatting(sheet["Table[Imports]"], ConditionalFormattingRankCondition.TopByRank, 5);
cfRule2.Formatting.Fill.BackgroundColor = Color.FromArgb(0xFC, 0xB3, 0xB3);
}
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();
}
}
public static void ApplyAsiaCountriesConditionalFormatting(Worksheet sheet)
{
ConditionalFormattingCollection conditionalFormattings = sheet.ConditionalFormattings;
FormulaExpressionConditionalFormatting cfRule = conditionalFormattings.AddFormulaExpressionConditionalFormatting(sheet["Table[Country]"], "=$B6=\"Asia\"");
cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xBC, 0xDA, 0xF7);
}
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 ApplyBalanceTrendConditionalFormatting(Worksheet sheet)
{
ConditionalFormattingCollection conditionalFormattings = sheet.ConditionalFormattings;
ConditionalFormattingIconSetValue minPoint = sheet.ConditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Formula, "=MIN($F$6:$F$22)", ConditionalFormattingValueOperator.GreaterOrEqual);
ConditionalFormattingIconSetValue midPoint = sheet.ConditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Number, "0", ConditionalFormattingValueOperator.GreaterOrEqual);
ConditionalFormattingIconSetValue maxPoint = sheet.ConditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Number, "0.0001", ConditionalFormattingValueOperator.GreaterOrEqual);
IconSetConditionalFormatting cfRule = conditionalFormattings.AddIconSetConditionalFormatting(sheet["Table[Balance]"], IconSetType.Triangles3, new ConditionalFormattingIconSetValue[] { minPoint, midPoint, maxPoint });
}
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 ApplyPressureConditionalFormatting(Worksheet sheet)
{
ConditionalFormattingCollection conditionalFormattings = sheet.ConditionalFormattings;
ConditionalFormattingIconSetValue minPoint = conditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Number, "-1", ConditionalFormattingValueOperator.GreaterOrEqual);
ConditionalFormattingIconSetValue midPoint = conditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Number, "0", ConditionalFormattingValueOperator.GreaterOrEqual);
ConditionalFormattingIconSetValue maxPoint = conditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Number, "1", ConditionalFormattingValueOperator.GreaterOrEqual);
IconSetConditionalFormatting cfRule = conditionalFormattings.AddIconSetConditionalFormatting(sheet.Range["$I$4:$I$60"], IconSetType.Triangles3, new ConditionalFormattingIconSetValue[] { minPoint, midPoint, maxPoint });
cfRule.ShowValue = false;
}
public void ApplyNotExistsArticleFormatingforImportLog(Worksheet sheet, Range LogRange, Worksheet LogSheet)
{
try
{
string stRange = LogRange.GetReferenceA1();
ConditionalFormattingCollection conditionalFormattings = sheet.ConditionalFormattings;
FormulaExpressionConditionalFormatting cfRule =
conditionalFormattings.AddFormulaExpressionConditionalFormatting(sheet.GetDataRange(), "=$A1=VLOOKUP($A1,'Import des Logs'!$A:$A,1,0)");
cfRule.Formatting.Fill.BackgroundColor = Color.Red;
}
catch (Exception ex) { }
}
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;
}
public static void ApplyExportsYearlyChangeConditionalFormatting(Worksheet sheet)
{
ConditionalFormattingCollection conditionalFormattings = sheet.ConditionalFormattings;
ExpressionConditionalFormatting cfRule =
conditionalFormattings.AddExpressionConditionalFormatting(sheet["Table[Exports 1Y Chg]"], ConditionalFormattingExpressionCondition.GreaterThan, "0");
cfRule.Formatting.Font.Color = Color.Green;
ExpressionConditionalFormatting cfRule2 =
conditionalFormattings.AddExpressionConditionalFormatting(sheet["Table[Exports 1Y Chg]"], ConditionalFormattingExpressionCondition.LessThan, "0");
cfRule2.Formatting.Font.Color = Color.Red;
}
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 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
}
static void AddIconSetConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #IconSetConditionalFormatting
ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings;
// Set the first threshold to the lowest value in the range of cells using the MIN() formula.
ConditionalFormattingIconSetValue minPoint = conditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Formula, "=MIN($E$2:$E$15)", ConditionalFormattingValueOperator.GreaterOrEqual);
// Set the second threshold to 0.
ConditionalFormattingIconSetValue midPoint = conditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Number, "0", ConditionalFormattingValueOperator.GreaterOrEqual);
// Set the third threshold to 0.01.
ConditionalFormattingIconSetValue maxPoint = conditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Number, "0.01", ConditionalFormattingValueOperator.GreaterOrEqual);
// Create the rule to apply a specific icon from the three arrow icon set to each cell in the range E2:E15 based on its value.
IconSetConditionalFormatting cfRule = conditionalFormattings.AddIconSetConditionalFormatting(worksheet.Range["$E$2:$E$15"], IconSetType.Arrows3, new ConditionalFormattingIconSetValue[] { minPoint, midPoint, maxPoint });
// Specify the custom icon to be displayed if the second condition is true.
// To do this, set the IconSetConditionalFormatting.IsCustom property to true, which is false by default.
cfRule.IsCustom = true;
// Initialize the ConditionalFormattingCustomIcon object.
ConditionalFormattingCustomIcon cfCustomIcon = new ConditionalFormattingCustomIcon();
// Specify the icon set where you wish to get the icon.
cfCustomIcon.IconSet = IconSetType.TrafficLights13;
// Specify the index of the desired icon in the set.
cfCustomIcon.IconIndex = 1;
// Add the custom icon at the specified position in the initial icon set.
cfRule.SetCustomIcon(1, cfCustomIcon);
// Hide values of cells to which the rule is applied.
cfRule.ShowValue = false;
#endregion #IconSetConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "Identify upward and downward cost trends.";
}
finally
{
workbook.EndUpdate();
}
}