/// <summary>
 /// 添加条件格式规则
 /// </summary>
 /// <param name="cell">单元格</param>
 /// <param name="cfrs">条件格式规则</param>
 public static void AddConditionalFormattingRules(this ICell cell, IConditionalFormattingRule[] cfrs)
 {
     CellRangeAddress[] regions =
     {
         new CellRangeAddress(cell.RowIndex, cell.RowIndex, cell.ColumnIndex, cell.ColumnIndex)
     };
     cell.Sheet.SheetConditionalFormatting.AddConditionalFormatting(regions, cfrs);
 }
 public void AddRule(IConditionalFormattingRule cfRule)
 {
     AddRule((HSSFConditionalFormattingRule)cfRule);
 }
 public void SetRule(int idx, IConditionalFormattingRule cfRule)
 {
     SetRule(idx, (HSSFConditionalFormattingRule)cfRule);
 }
Ejemplo n.º 4
0
 /**
  * Add a Conditional Formatting rule.
  * Excel allows to create up to 3 Conditional Formatting rules.
  *
  * @param cfRule - Conditional Formatting rule
  */
 public void AddRule(IConditionalFormattingRule cfRule)
 {
     XSSFConditionalFormattingRule xRule = (XSSFConditionalFormattingRule)cfRule;
     _cf.AddNewCfRule().Set(xRule.GetCTCfRule());
 }
Ejemplo n.º 5
0
 /**
  * Replaces an existing Conditional Formatting rule at position idx.
  * Excel allows to create up to 3 Conditional Formatting rules.
  * This method can be useful to modify existing  Conditional Formatting rules.
  *
  * @param idx position of the rule. Should be between 0 and 2.
  * @param cfRule - Conditional Formatting rule
  */
 public void SetRule(int idx, IConditionalFormattingRule cfRule)
 {
     XSSFConditionalFormattingRule xRule = (XSSFConditionalFormattingRule)cfRule;
     _cf.GetCfRuleArray(idx).Set(xRule.GetCTCfRule());
 }
 public int AddConditionalFormatting(CellRangeAddress[] regions,
         IConditionalFormattingRule rule1, IConditionalFormattingRule rule2)
 {
     return AddConditionalFormatting(regions,
             rule1 == null ? null : new XSSFConditionalFormattingRule[] {
             (XSSFConditionalFormattingRule)rule1,
             (XSSFConditionalFormattingRule)rule2
     });
 }
        public int AddConditionalFormatting(CellRangeAddress[] regions, IConditionalFormattingRule[] cfRules)
        {
            if (regions == null)
            {
                throw new ArgumentException("regions must not be null");
            }
            foreach (CellRangeAddress range in regions) range.Validate(SpreadsheetVersion.EXCEL2007);

            if (cfRules == null)
            {
                throw new ArgumentException("cfRules must not be null");
            }
            if (cfRules.Length == 0)
            {
                throw new ArgumentException("cfRules must not be empty");
            }
            if (cfRules.Length > 3)
            {
                throw new ArgumentException("Number of rules must not exceed 3");
            }
            XSSFConditionalFormattingRule[] hfRules;
            if (cfRules is XSSFConditionalFormattingRule[])
                hfRules = (XSSFConditionalFormattingRule[])cfRules;
            else
            {
                hfRules = new XSSFConditionalFormattingRule[cfRules.Length];
                Array.Copy(cfRules, 0, hfRules, 0, hfRules.Length);
            }

            CellRangeAddress[] mergeCellRanges = CellRangeUtil.MergeCellRanges(regions);
            CT_ConditionalFormatting cf = _sheet.GetCTWorksheet().AddNewConditionalFormatting();
            List<String> refs = new List<String>();
            foreach (CellRangeAddress a in mergeCellRanges) refs.Add(a.FormatAsString());
            cf.sqref = (refs);


            int priority = 1;
            foreach (CT_ConditionalFormatting c in _sheet.GetCTWorksheet().conditionalFormatting)
            {
                priority += c.sizeOfCfRuleArray();
            }

            foreach (IConditionalFormattingRule rule in cfRules)
            {
                XSSFConditionalFormattingRule xRule = (XSSFConditionalFormattingRule)rule;
                xRule.GetCTCfRule().priority = (priority++);
                cf.AddNewCfRule().Set(xRule.GetCTCfRule());
            }
            return _sheet.GetCTWorksheet().SizeOfConditionalFormattingArray() - 1;
        }
        void MakePercentageConditionalFormattingRules()
        {
            IConditionalFormattingRule rule1 = sheet.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.GreaterThanOrEqual, GreenBandString);
            IPatternFormatting fill1 = rule1.CreatePatternFormatting();
            fill1.FillBackgroundColor = IndexedColors.BrightGreen.Index;
            fill1.FillPattern = (short)FillPattern.SolidForeground;

            IConditionalFormattingRule rule2 = sheet.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.GreaterThanOrEqual, YellowBandString);
            IPatternFormatting fill2 = rule2.CreatePatternFormatting();
            fill2.FillBackgroundColor = IndexedColors.Yellow.Index;
            fill2.FillPattern = (short)FillPattern.SolidForeground;

            IConditionalFormattingRule rule3 = sheet.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.LessThan, YellowBandString);
            IPatternFormatting fill3 = rule3.CreatePatternFormatting();
            fill3.FillBackgroundColor = IndexedColors.Red.Index;
            fill3.FillPattern = (short)FillPattern.SolidForeground;

            PercentageFormattingRules = new IConditionalFormattingRule[] { rule1, rule2, rule3 };
        }
        /// <summary>
        /// Adds the conditional formatting.
        /// </summary>
        /// <param name="regions">The regions.</param>
        /// <param name="rule1">The rule1.</param>
        /// <param name="rule2">The rule2.</param>
        /// <returns></returns>
        public int AddConditionalFormatting(CellRangeAddress[] regions,
                IConditionalFormattingRule rule1,
                IConditionalFormattingRule rule2)
        {
            return AddConditionalFormatting(regions,
                    new HSSFConditionalFormattingRule[]
				{
						(HSSFConditionalFormattingRule)rule1, (HSSFConditionalFormattingRule)rule2
				});
        }
 /// <summary>
 /// Adds the conditional formatting.
 /// </summary>
 /// <param name="regions">The regions.</param>
 /// <param name="rule1">The rule1.</param>
 /// <returns></returns>
 public int AddConditionalFormatting(CellRangeAddress[] regions,
         IConditionalFormattingRule rule1)
 {
     return AddConditionalFormatting(regions, (HSSFConditionalFormattingRule)rule1);
 }
        /// <summary>
        /// Allows to Add a new Conditional Formatting Set to the sheet.
        /// </summary>
        /// <param name="regions">list of rectangular regions to apply conditional formatting rules</param>
        /// <param name="cfRules">Set of up to three conditional formatting rules</param>
        /// <returns>index of the newly Created Conditional Formatting object</returns>
        public int AddConditionalFormatting(CellRangeAddress[] regions, IConditionalFormattingRule[] cfRules)
        {
            if (regions == null)
            {
                throw new ArgumentException("regions must not be null");
            }
            if (cfRules == null)
            {
                throw new ArgumentException("cfRules must not be null");
            }
            if (cfRules.Length == 0)
            {
                throw new ArgumentException("cfRules must not be empty");
            }
            if (cfRules.Length > 3)
            {
                throw new ArgumentException("Number of rules must not exceed 3");
            }

            CFRuleRecord[] rules = new CFRuleRecord[cfRules.Length];
            for (int i = 0; i != cfRules.Length; i++)
            {
                rules[i] = ((HSSFConditionalFormattingRule)cfRules[i]).CfRuleRecord;
            }
            CFRecordsAggregate cfra = new CFRecordsAggregate(regions, rules);
            return _conditionalFormattingTable.Add(cfra);
        }