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);
        }
コード例 #2
0
        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
                }
            }
        }