Beispiel #1
0
        static void Top10(Stream stream, XlDocumentFormat documentFormat)
        {
            // Create an exporter instance.
            IXlExporter exporter = XlExport.CreateExporter(documentFormat);

            // 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 < 10; i++)
                    {
                        using (IXlRow row = sheet.CreateRow()) {
                            for (int j = 0; j < 4; j++)
                            {
                                using (IXlCell cell = row.CreateCell()) {
                                    cell.Value = cell.ColumnIndex * 4 + cell.RowIndex + 1;
                                }
                            }
                        }
                    }
                    #region #TopAndBottomRules
                    // 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:D10).
                    formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 3, 9));
                    // Create the rule to identify bottom 10 values in the cell range.
                    XlCondFmtRuleTop10 rule = new XlCondFmtRuleTop10();
                    rule.Bottom = true;
                    // 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 identify top 10 values in the cell range.
                    rule = new XlCondFmtRuleTop10();
                    // 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);
                    #endregion #TopAndBottomRules
                }
            }
        }
        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);
        }