void GenerateConditionalFormatting(IXlSheet sheet, int firstDataRowIndex)
{
// Create an instance of the XlConditionalFormatting class to define a new rule.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rule should be applied (B4:B38).
formatting.Ranges.Add(XlCellRange.FromLTRB(1, firstDataRowIndex, 1, sheet.CurrentRowIndex - 2));
// Create the rule to compare values in the "Sales" column using data bars.
XlCondFmtRuleDataBar rule1 = new XlCondFmtRuleDataBar();
// Specify the color of data bars.
rule1.FillColor = XlColor.FromTheme(XlThemeColor.Accent1, 0.4);
// Set the solid fill type.
rule1.GradientFill = false;
formatting.Rules.Add(rule1);
// Add the specified rule to the worksheet collection of conditional formatting rules.
sheet.ConditionalFormattings.Add(formatting);
// Create an instance of the XlConditionalFormatting class to define new rules.
formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rules should be applied (C4:C38).
formatting.Ranges.Add(XlCellRange.FromLTRB(2, firstDataRowIndex, 2, sheet.CurrentRowIndex - 2));
// Create the rule to identify negative values in the "Sales vs Target" column.
XlCondFmtRuleCellIs rule2 = new XlCondFmtRuleCellIs();
// Specify the relational operator to be used in the conditional formatting rule.
rule2.Operator = XlCondFmtOperator.LessThan;
// Set the threshold value.
rule2.Value = 0;
// Specify formatting options to be applied to cells if the condition is true.
// Set the font color to dark red.
rule2.Formatting = new XlFont()
{
Color = Color.DarkRed
};
formatting.Rules.Add(rule2);
// Create the rule to identify top five values in the "Sales vs Target" column.
XlCondFmtRuleTop10 rule3 = new XlCondFmtRuleTop10();
rule3.Rank = 5;
// Specify formatting options to be applied to cells if the condition is true.
// Set the font color to dark green.
rule3.Formatting = new XlFont()
{
Color = Color.DarkGreen
};
formatting.Rules.Add(rule3);
// Add the specified rules to the worksheet collection of conditional formatting rules.
sheet.ConditionalFormattings.Add(formatting);
// Create an instance of the XlConditionalFormatting class to define a new rule.
formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rules should be applied (D4:D38).
formatting.Ranges.Add(XlCellRange.FromLTRB(3, firstDataRowIndex, 3, sheet.CurrentRowIndex - 2));
// Create the rule to compare values in the "Profit" column using data bars.
XlCondFmtRuleDataBar rule4 = new XlCondFmtRuleDataBar();
// Specify the color of data bars.
rule4.FillColor = Color.FromArgb(99, 195, 132);
// Specify the positive bar border color.
rule4.BorderColor = Color.FromArgb(99, 195, 132);
// Specify the negative bar fill color.
rule4.NegativeFillColor = Color.FromArgb(255, 85, 90);
// Specify the negative bar border color.
rule4.NegativeBorderColor = Color.FromArgb(255, 85, 90);
// Specify the solid fill type.
rule4.GradientFill = false;
formatting.Rules.Add(rule4);
// Add the specified rule to the worksheet collection of conditional formatting rules.
sheet.ConditionalFormattings.Add(formatting);
// Create an instance of the XlConditionalFormatting class to define a new rule.
formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rules should be applied (E4:E38).
formatting.Ranges.Add(XlCellRange.FromLTRB(4, firstDataRowIndex, 4, sheet.CurrentRowIndex - 2));
// Create the rule to apply a specific icon from the three traffic lights icon set to each cell in the "Market Share" column based on its value.
XlCondFmtRuleIconSet rule5 = new XlCondFmtRuleIconSet();
rule5.IconSetType = XlCondFmtIconSetType.TrafficLights3;
formatting.Rules.Add(rule5);
// Add the specified rule to the worksheet collection of conditional formatting rules.
sheet.ConditionalFormattings.Add(formatting);
}
static void CellIs(Stream stream, XlDocumentFormat documentFormat)
{
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat, new XlFormulaParser());
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
// Generate data for the document.
for (int i = 0; i < 11; i++)
{
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = i + 1;
}
using (IXlCell cell = row.CreateCell()) {
cell.Value = 12 - i;
}
}
}
#region #CellIsRule
// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rules should be applied (A1:A11).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 10));
// Create the rule to highlight cells whose values are less than 5.
XlCondFmtRuleCellIs rule = new XlCondFmtRuleCellIs();
rule.Operator = XlCondFmtOperator.LessThan;
rule.Value = 5;
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Bad;
formatting.Rules.Add(rule);
// Create the rule to highlight cells whose values are between 5 and 8.
rule = new XlCondFmtRuleCellIs();
rule.Operator = XlCondFmtOperator.Between;
rule.Value = 5;
rule.SecondValue = 8;
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Neutral;
formatting.Rules.Add(rule);
// Create the rule to highlight cells whose values are greater than 8.
rule = new XlCondFmtRuleCellIs();
rule.Operator = XlCondFmtOperator.GreaterThan;
rule.Value = 8;
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Good;
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
// Create an instance of the XlConditionalFormatting class.
formatting = new XlConditionalFormatting();
// Specify the cell range to which the conditional formatting rule should be applied (B1:B11).
formatting.Ranges.Add(XlCellRange.FromLTRB(1, 0, 1, 10));
// Create the rule to highlight cells whose values are greater than a value calculated by a formula.
rule = new XlCondFmtRuleCellIs();
rule.Operator = XlCondFmtOperator.GreaterThan;
rule.Value = "=$A1+3";
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Bad;
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
#endregion #CellIsRule
}
}
}