/** * A factory method allowing to create a conditional formatting rule * with a cell comparison operator<p/> * TODO - formulas Containing cell references are currently not Parsed properly * * @param comparisonOperation - a constant value from * <tt>{@link NPOI.hssf.record.CFRuleRecord.ComparisonOperator}</tt>: <p> * <ul> * <li>BETWEEN</li> * <li>NOT_BETWEEN</li> * <li>EQUAL</li> * <li>NOT_EQUAL</li> * <li>GT</li> * <li>LT</li> * <li>GE</li> * <li>LE</li> * </ul> * </p> * @param formula1 - formula for the valued, Compared with the cell * @param formula2 - second formula (only used with * {@link NPOI.ss.usermodel.ComparisonOperator#BETWEEN}) and * {@link NPOI.ss.usermodel.ComparisonOperator#NOT_BETWEEN} operations) */ public IConditionalFormattingRule CreateConditionalFormattingRule( ComparisonOperator comparisonOperation, String formula1, String formula2) { XSSFConditionalFormattingRule rule = new XSSFConditionalFormattingRule(_sheet); CT_CfRule cfRule = rule.GetCTCfRule(); cfRule.AddFormula(formula1); if (formula2 != null) cfRule.AddFormula(formula2); cfRule.type = (ST_CfType.cellIs); ST_ConditionalFormattingOperator operator1; switch (comparisonOperation) { case ComparisonOperator.BETWEEN: operator1 = ST_ConditionalFormattingOperator.between; break; case ComparisonOperator.NOT_BETWEEN: operator1 = ST_ConditionalFormattingOperator.notBetween; break; case ComparisonOperator.LT: operator1 = ST_ConditionalFormattingOperator.lessThan; break; case ComparisonOperator.LE: operator1 = ST_ConditionalFormattingOperator.lessThanOrEqual; break; case ComparisonOperator.GT: operator1 = ST_ConditionalFormattingOperator.greaterThan; break; case ComparisonOperator.GE: operator1 = ST_ConditionalFormattingOperator.greaterThanOrEqual; break; case ComparisonOperator.EQUAL: operator1 = ST_ConditionalFormattingOperator.equal; break; case ComparisonOperator.NOT_EQUAL: operator1 = ST_ConditionalFormattingOperator.notEqual; break; default: throw new ArgumentException("Unknown comparison operator: " + comparisonOperation); } cfRule.@operator = (operator1); return rule; }
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); }
/** * A factory method allowing to create a conditional formatting rule with a formula.<br> * * @param formula - formula for the valued, Compared with the cell */ public IConditionalFormattingRule CreateConditionalFormattingRule(String formula) { XSSFConditionalFormattingRule rule = new XSSFConditionalFormattingRule(_sheet); CT_CfRule cfRule = rule.GetCTCfRule(); cfRule.AddFormula(formula); cfRule.type = ST_CfType.expression; return(rule); }
public IConditionalFormattingRule CreateConditionalFormattingRule(string formula) { XSSFConditionalFormattingRule conditionalFormattingRule = new XSSFConditionalFormattingRule(this._sheet); CT_CfRule ctCfRule = conditionalFormattingRule.GetCTCfRule(); ctCfRule.AddFormula(formula); ctCfRule.type = ST_CfType.expression; return((IConditionalFormattingRule)conditionalFormattingRule); }
/** * Create a Color Scale / Color Gradient conditional formatting rule. * <p>The thresholds and colours for it will be created, but will be * empty and require configuring with * {@link XSSFConditionalFormattingRule#getColorScaleFormatting()} * then * {@link XSSFColorScaleFormatting#getThresholds()} * and * {@link XSSFColorScaleFormatting#getColors()} */ public IConditionalFormattingRule CreateConditionalFormattingColorScaleRule() { XSSFConditionalFormattingRule rule = new XSSFConditionalFormattingRule(_sheet); // Have it setup, with suitable defaults rule.CreateColorScaleFormatting(); // All done! return(rule); }
/** * Create a Databar conditional formatting rule. * <p>The thresholds and colour for it will be created, but will be * empty and require configuring with * {@link XSSFConditionalFormattingRule#getDataBarFormatting()} * then * {@link XSSFDataBarFormatting#getMinThreshold()} * and * {@link XSSFDataBarFormatting#getMaxThreshold()} */ public XSSFConditionalFormattingRule CreateConditionalFormattingRule(XSSFColor color) { XSSFConditionalFormattingRule rule = new XSSFConditionalFormattingRule(_sheet); // Have it setup, with suitable defaults rule.CreateDataBarFormatting(color); // All done! return(rule); }
/** * A factory method allowing the creation of conditional formatting * rules using an Icon Set / Multi-State formatting. * The thresholds for it will be created, but will be empty * and require configuring with * {@link XSSFConditionalFormattingRule#getMultiStateFormatting()} * then * {@link XSSFIconMultiStateFormatting#getThresholds()} */ public IConditionalFormattingRule CreateConditionalFormattingRule(IconSet iconSet) { XSSFConditionalFormattingRule rule = new XSSFConditionalFormattingRule(_sheet); // Have it setup, with suitable defaults rule.CreateMultiStateFormatting(iconSet); // All done! return(rule); }
public IConditionalFormattingRule CreateConditionalFormattingRule(ComparisonOperator comparisonOperation, string formula1, string formula2) { XSSFConditionalFormattingRule conditionalFormattingRule = new XSSFConditionalFormattingRule(this._sheet); CT_CfRule ctCfRule = conditionalFormattingRule.GetCTCfRule(); ctCfRule.AddFormula(formula1); if (formula2 != null) { ctCfRule.AddFormula(formula2); } ctCfRule.type = ST_CfType.cellIs; ST_ConditionalFormattingOperator formattingOperator; switch (comparisonOperation) { case ComparisonOperator.BETWEEN: formattingOperator = ST_ConditionalFormattingOperator.between; break; case ComparisonOperator.NOT_BETWEEN: formattingOperator = ST_ConditionalFormattingOperator.notBetween; break; case ComparisonOperator.EQUAL: formattingOperator = ST_ConditionalFormattingOperator.equal; break; case ComparisonOperator.NOT_EQUAL: formattingOperator = ST_ConditionalFormattingOperator.notEqual; break; case ComparisonOperator.GT: formattingOperator = ST_ConditionalFormattingOperator.greaterThan; break; case ComparisonOperator.LT: formattingOperator = ST_ConditionalFormattingOperator.lessThan; break; case ComparisonOperator.GE: formattingOperator = ST_ConditionalFormattingOperator.greaterThanOrEqual; break; case ComparisonOperator.LE: formattingOperator = ST_ConditionalFormattingOperator.lessThanOrEqual; break; default: throw new ArgumentException("Unknown comparison operator: " + (object)comparisonOperation); } ctCfRule.@operator = formattingOperator; return((IConditionalFormattingRule)conditionalFormattingRule); }
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"); } CellRangeAddress[] mergeCellRanges = CellRangeUtil.MergeCellRanges(regions); CT_ConditionalFormatting cf = _sheet.GetCTWorksheet().AddNewConditionalFormatting(); string refs = string.Empty; foreach (CellRangeAddress a in mergeCellRanges) { if (refs.Length == 0) { refs = a.FormatAsString(); } else { refs += " " + 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); }
/** * A factory method allowing to create a conditional formatting rule * with a cell comparison operator<p/> * TODO - formulas Containing cell references are currently not Parsed properly * * @param comparisonOperation - a constant value from * <tt>{@link NPOI.hssf.record.CFRuleRecord.ComparisonOperator}</tt>: <p> * <ul> * <li>BETWEEN</li> * <li>NOT_BETWEEN</li> * <li>EQUAL</li> * <li>NOT_EQUAL</li> * <li>GT</li> * <li>LT</li> * <li>GE</li> * <li>LE</li> * </ul> * </p> * @param formula1 - formula for the valued, Compared with the cell * @param formula2 - second formula (only used with * {@link NPOI.ss.usermodel.ComparisonOperator#BETWEEN}) and * {@link NPOI.ss.usermodel.ComparisonOperator#NOT_BETWEEN} operations) */ public IConditionalFormattingRule CreateConditionalFormattingRule( ComparisonOperator comparisonOperation, String formula1, String formula2) { XSSFConditionalFormattingRule rule = new XSSFConditionalFormattingRule(_sheet); CT_CfRule cfRule = rule.GetCTCfRule(); cfRule.AddFormula(formula1); if (formula2 != null) { cfRule.AddFormula(formula2); } cfRule.type = (ST_CfType.cellIs); ST_ConditionalFormattingOperator operator1; switch (comparisonOperation) { case ComparisonOperator.Between: operator1 = ST_ConditionalFormattingOperator.between; break; case ComparisonOperator.NotBetween: operator1 = ST_ConditionalFormattingOperator.notBetween; break; case ComparisonOperator.LessThan: operator1 = ST_ConditionalFormattingOperator.lessThan; break; case ComparisonOperator.LessThanOrEqual: operator1 = ST_ConditionalFormattingOperator.lessThanOrEqual; break; case ComparisonOperator.GreaterThan: operator1 = ST_ConditionalFormattingOperator.greaterThan; break; case ComparisonOperator.GreaterThanOrEqual: operator1 = ST_ConditionalFormattingOperator.greaterThanOrEqual; break; case ComparisonOperator.Equal: operator1 = ST_ConditionalFormattingOperator.equal; break; case ComparisonOperator.NotEqual: operator1 = ST_ConditionalFormattingOperator.notEqual; break; default: throw new ArgumentException("Unknown comparison operator: " + comparisonOperation); } cfRule.@operator = (operator1); return(rule); }
public int AddConditionalFormatting(CellRangeAddress[] regions, IConditionalFormattingRule[] cfRules) { if (regions == null) { throw new ArgumentException("regions must not be null"); } foreach (CellRangeAddressBase region in regions) { region.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"); } if (!(cfRules is XSSFConditionalFormattingRule[])) { XSSFConditionalFormattingRule[] conditionalFormattingRuleArray = new XSSFConditionalFormattingRule[cfRules.Length]; Array.Copy((Array)cfRules, 0, (Array)conditionalFormattingRuleArray, 0, conditionalFormattingRuleArray.Length); } CellRangeAddress[] cellRangeAddressArray = CellRangeUtil.MergeCellRanges(regions); CT_ConditionalFormatting conditionalFormatting1 = this._sheet.GetCTWorksheet().AddNewConditionalFormatting(); List <string> stringList = new List <string>(); foreach (CellRangeAddress cellRangeAddress in cellRangeAddressArray) { stringList.Add(cellRangeAddress.FormatAsString()); } conditionalFormatting1.sqref = stringList; int num = 1; foreach (CT_ConditionalFormatting conditionalFormatting2 in this._sheet.GetCTWorksheet().conditionalFormatting) { num += conditionalFormatting2.sizeOfCfRuleArray(); } foreach (XSSFConditionalFormattingRule cfRule in cfRules) { cfRule.GetCTCfRule().priority = num++; conditionalFormatting1.AddNewCfRule().Set(cfRule.GetCTCfRule()); } return(this._sheet.GetCTWorksheet().SizeOfConditionalFormattingArray() - 1); }
public int AddConditionalFormatting(CellRangeAddress[] regions, IConditionalFormattingRule rule1) { CellRangeAddress[] regions1 = regions; XSSFConditionalFormattingRule[] conditionalFormattingRuleArray; if (rule1 != null) { conditionalFormattingRuleArray = new XSSFConditionalFormattingRule[1] { (XSSFConditionalFormattingRule)rule1 } } ; else { conditionalFormattingRuleArray = (XSSFConditionalFormattingRule[])null; } return(this.AddConditionalFormatting(regions1, (IConditionalFormattingRule[])conditionalFormattingRuleArray)); }
/** * 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()); }
/** * 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[] 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; }
/** * A factory method allowing to create a conditional formatting rule with a formula.<br> * * @param formula - formula for the valued, Compared with the cell */ public IConditionalFormattingRule CreateConditionalFormattingRule(String formula) { XSSFConditionalFormattingRule rule = new XSSFConditionalFormattingRule(_sheet); CT_CfRule cfRule = rule.GetCTCfRule(); cfRule.AddFormula(formula); cfRule.type = ST_CfType.expression; return rule; }