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.Range["$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.Range["$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();
}
}
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 AddExpressionConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #ExpressionConditionalFormatting
// Create the rule to identify values that are above the average in cells F2 through F15.
ExpressionConditionalFormatting cfRule =
worksheet.ConditionalFormattings.AddExpressionConditionalFormatting(worksheet.Range["$F$2:$F$15"], ConditionalFormattingExpressionCondition.GreaterThan, "=AVERAGE($F$2:$F$15)");
// Specify formatting options to be applied to cells if the condition is true.
// Set the background color to yellow.
cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xFA, 0xF7, 0xAA);
// Set the font color to red.
cfRule.Formatting.Font.Color = Color.Red;
#endregion #ExpressionConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "Identify book prices that are greater than the average price.";
}
finally
{
workbook.EndUpdate();
}
}
static void AddTimePeriodConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfTasks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #TimePeriodConditionalFormatting
// Create the rule to highlight today's dates in cells B2 through B6.
TimePeriodConditionalFormatting cfRule =
worksheet.ConditionalFormattings.AddTimePeriodConditionalFormatting(worksheet.Range["$B$2:$B$6"], ConditionalFormattingTimePeriod.Today);
// Specify formatting options to be applied to cells if the condition is true.
// Set the background color to pink.
cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xF2, 0xAE, 0xE3);
#endregion #TimePeriodConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A8:B9"];
ruleExplanation.Value = "Determine the today's task in the TO DO list.";
}
finally
{
workbook.EndUpdate();
}
}
static void AddTimePeriodConditionalFormatting(IWorkbook workbook)
{
#region #TimePeriodConditionalFormatting
workbook.Calculate();
Worksheet worksheet = workbook.Worksheets["cfTasks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
TimePeriodConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddTimePeriodConditionalFormatting(worksheet["$C$5:$C$9"], ConditionalFormattingTimePeriod.Today);
cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xF2, 0xAE, 0xE3);
worksheet["B2"].Value = "Determine the today's task in the TO DO list.";
worksheet.Visible = true;
#endregion #TimePeriodConditionalFormatting
}
void GenerateAnnuityPaymentsContent()
{
AddDefinedNamesForAnnuityPayments();
Sheet["I4"].FormulaInvariant = "=PMT(Interest_Rate_Per_Month,Scheduled_Number_Payments,-Loan_Amount)";
Sheet["I5"].FormulaInvariant = "=Loan_Years*Number_of_Payments_Per_Year";
Sheet["I6"].FormulaInvariant = "=ROUNDUP(Actual_Number_Payments,0)";
workbook.Calculate();
Sheet["I7"].FormulaInvariant = "=SUM(F12:F" + ActualLastRow + ")";
Sheet["I8"].FormulaInvariant = "=SUM($I$12:$I$" + ActualLastRow + ")";
if (ScheduledNumberOfPayments == 0)
{
return;
}
for (int i = 0; i < ActualNumberOfPayments; i++)
{
Sheet["B" + (i + 12).ToString()].Value = i + 1;
}
Sheet["C12:C" + ActualLastRow].FormulaInvariant = "=DATE(YEAR(Loan_Start),MONTH(Loan_Start)+(B12)*12/Number_of_Payments_Per_Year,DAY(Loan_Start))";
Sheet["D12"].Formula = "=Loan_Amount";
if (ScheduledNumberOfPayments > 1)
{
Sheet["D13:D" + ActualLastRow].Formula = "=J12";
}
Sheet["E12:E" + ActualLastRow].FormulaInvariant = "=IF(D12>0,IF(Scheduled_payment<D12, Scheduled_payment, D12),0)";
Sheet["F12:F" + ActualLastRow].FormulaInvariant = "=IF(Extra_Payments<>0, IF(Scheduled_payment<D12, G12-E12, 0), 0)";
Sheet["G12:G" + ActualLastRow].FormulaInvariant = "=H12+I12";
Sheet["H12:H" + ActualLastRow].FormulaInvariant = "=IF(J12>0,PPMT(Interest_Rate_Per_Month,B12,Actual_Number_Payments,-Loan_Amount),D12)";
Sheet["I12:I" + ActualLastRow].FormulaInvariant = "=IF(D12>0,IPMT(Interest_Rate_Per_Month,B12,Actual_Number_Payments,-Loan_Amount),0)";
Sheet["J12:J" + ActualLastRow].FormulaInvariant = "=IF(D12-PPMT(Interest_Rate_Per_Month,B12,Actual_Number_Payments,-Loan_Amount)>0,D12-PPMT(Interest_Rate_Per_Month,B12,Actual_Number_Payments,-Loan_Amount),0)";
Sheet["K12:K" + ActualLastRow].FormulaInvariant = "=SUM($I$12:$I12)";
workbook.Calculate();
}
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 SimpleSearchValue(IWorkbook workbook)
{
#region #SimpleSearch
workbook.LoadDocument("Documents\\ExpenseReport.xlsx");
workbook.Calculate();
Worksheet worksheet = workbook.Worksheets[0];
// Find and highlight cells containing the word "holiday".
IEnumerable <Cell> searchResult = worksheet.Search("holiday");
foreach (Cell cell in searchResult)
{
cell.Fill.BackgroundColor = System.Drawing.Color.LightGreen;
}
#endregion #SimpleSearch
// Add a note.
worksheet["E1"].Value = "Find the word \"holiday\" in the expense report";
}
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();
}
}
static void AddFormulaExpressionConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #FormulaExpressionConditionalFormatting
// Create the rule to shade alternate rows without applying a new style.
FormulaExpressionConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddFormulaExpressionConditionalFormatting(worksheet.Range["$A$2:$G$15"], "=MOD(ROW(),2)=1");
// Specify formatting options to be applied to cells if the condition is true.
// Set the background color to light blue.
cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xBC, 0xDA, 0xF7);
#endregion #FormulaExpressionConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "Shade alternate rows in light blue without applying a new style.";
}
finally
{
workbook.EndUpdate();
}
}
static void AddSpecialConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #SpecialConditionalFormatting
// Create the rule to identify unique values in cells A2 through A15.
SpecialConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddSpecialConditionalFormatting(worksheet["$A$2:$A$15"], ConditionalFormattingSpecialCondition.ContainUniqueValue);
// Specify formatting options to be applied to cells if the condition is true.
// Set the background color to yellow.
cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xFA, 0xF7, 0xAA);
#endregion #SpecialConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "In a list of authors quickly identify unique values.";
}
finally
{
workbook.EndUpdate();
}
}
static void AddTextConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #TextConditionalFormatting
// Create the rule to highlight values with the given text string in cells A2 through A15.
TextConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddTextConditionalFormatting(worksheet["$A$2:$A$15"], ConditionalFormattingTextCondition.Contains, "Bradbury");
// Specify formatting options to be applied to cells if the condition is true.
// Set the background color to pink.
cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xE1, 0x95, 0xC2);
#endregion #TextConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "Quickly find books written by Ray Bradbury.";
}
finally
{
workbook.EndUpdate();
}
}