public void WriteConditionalFormatting(Sheet sheet, out CT_ConditionalFormatting[] cf) { List <CT_ConditionalFormatting> cflist = new List <CT_ConditionalFormatting>(); foreach (var cfrCollection in sheet.ConditionalFormatting) { if (cfrCollection.Ranges != null && cfrCollection.Ranges.Count > 0) { List <CT_CfRule> ct_cfrule_list = new List <CT_CfRule>(); foreach (var ruleobj in cfrCollection) { CT_CfRule cfRule = null; WriteConditionalFormattingRule(ruleobj, cfrCollection.GetFirstCellStringValue(), out cfRule); ct_cfrule_list.Add(cfRule); } var tempCf = new CT_ConditionalFormatting { cfRule = ct_cfrule_list.ToArray(), sqref = cfrCollection.Ranges.ToArray() }; cflist.Add(tempCf); } } cf = cflist.ToArray(); }
public CT_CfRule SetDxfStyle(CT_CfRule ct_cfrule, CellStyle style) { if (style != null) { ct_cfrule.dxfId = RegisterDxfStyle(style); ct_cfrule.dxfIdSpecified = true; } return(ct_cfrule); }
/** * 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); }
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); }
/** * 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); }
internal XSSFConditionalFormattingRule(XSSFSheet sh, CT_CfRule cfRule) { this._cfRule = cfRule; this._sh = sh; }
internal XSSFConditionalFormattingRule(XSSFSheet sh) { this._cfRule = new CT_CfRule(); this._sh = sh; }
public void WriteConditionalFormattingRule(CFRule rule, string firstCell, out CT_CfRule cfRule) { ST_CfType st_cftype = (ST_CfType)rule.Type; CT_ColorScale ct_colorscale = null; CT_DataBar ct_databar = null; CT_IconSet ct_iconset = null; if (rule.Type == CFType.ColorScale) { WriteConditionalFormattingColorScale(rule.ColorScale, out ct_colorscale); } if (rule.Type == CFType.DataBar) { WriteConditionalFormattingDataBar(rule.DataBar, out ct_databar); } if (rule.Type == CFType.IconSet) { WriteConditionalFormattingIconSet(rule.IconSet, out ct_iconset); } if (CFRule.RequiresDxf(rule.Type)) { List <string> formulas = new List <string>(); if (!string.IsNullOrEmpty(rule.Formula1)) { formulas.Add(rule.Formula1); } if (!string.IsNullOrEmpty(rule.Formula2)) { formulas.Add(rule.Formula2); } if (!string.IsNullOrEmpty(rule.Formula3)) { formulas.Add(rule.Formula3); } cfRule = new CT_CfRule { typeSpecified = true, type = st_cftype, priority = rule.Priority, formula = formulas.ToArray(), operatorSpecified = rule.Operator.HasValue, @operator = rule.Operator.HasValue ? (ST_ConditionalFormattingOperator)rule.Operator.Value : ST_ConditionalFormattingOperator.beginsWith, text = rule.Text, }; cfRule = SetDxfStyle(cfRule, rule.Style); switch (rule.Type) { case CFType.Top10: cfRule.percent = rule.IsPercent; cfRule.bottom = rule.IsBottom; cfRule.rankSpecified = rule.Rank.HasValue; cfRule.rank = (uint)rule.Rank.Value; break; case CFType.AboveAverage: cfRule.aboveAverage = rule.IsAboveAverage; cfRule.equalAverage = rule.IsEqualAverage; cfRule.stdDevSpecified = rule.IsStdDev; cfRule.stdDev = rule.StdDev; break; case CFType.TimePeriod: cfRule.timePeriodSpecified = true; cfRule.timePeriod = (ST_TimePeriod)rule.TimePeriod; break; } return; } cfRule = new CT_CfRule { typeSpecified = true, type = st_cftype, colorScale = ct_colorscale, dataBar = ct_databar, iconSet = ct_iconset, priority = rule.Priority }; }
/*package*/ public XSSFConditionalFormattingRule(XSSFSheet sh) { _cfRule = new CT_CfRule(); _sh = sh; }
public XSSFConditionFilterData(CT_CfRule cfRule) { _cfRule = cfRule; }
private CFRule GetConditionalFormattingRule(CT_CfRule ct_cfrule) { CFRule rule = new CFRule((CFType)ct_cfrule.type, ct_cfrule.priority); if (ct_cfrule.formula != null) { string for1 = null, for2 = null, for3 = null; if (ct_cfrule.formula.Length > 0) { for1 = ct_cfrule.formula[0]; } if (ct_cfrule.formula.Length > 1) { for2 = ct_cfrule.formula[1]; } if (ct_cfrule.formula.Length > 2) { for3 = ct_cfrule.formula[2]; } rule.Formula1 = for1; rule.Formula2 = for2; rule.Formula3 = for3; } switch (ct_cfrule.type) { case ST_CfType.iconSet: rule.IconSet = new IconSet { IconSetType = (IconSetType)ct_cfrule.iconSet.iconSet, ShowValue = ct_cfrule.iconSet.showValue, CFVOList = ConvertCFVOList(ct_cfrule.iconSet.cfvo) }; break; case ST_CfType.dataBar: rule.DataBar = new DataBar { CFVOList = ConvertCFVOList(ct_cfrule.dataBar.cfvo), Color = ConvertColor(ct_cfrule.dataBar.color) }; break; case ST_CfType.colorScale: rule.ColorScale = new ColorScale { CFVOList = ConvertCFVOList(ct_cfrule.colorScale.cfvo), Colors = ConvertColorList(ct_cfrule.colorScale.color) }; break; case ST_CfType.timePeriod: rule.TimePeriod = (TimePeriod)ct_cfrule.timePeriod; break; case ST_CfType.top10: rule.IsPercent = ct_cfrule.percent; rule.IsBottom = ct_cfrule.bottom; rule.Rank = (int)ct_cfrule.rank; break; case ST_CfType.aboveAverage: rule.IsAboveAverage = ct_cfrule.aboveAverage; rule.IsEqualAverage = ct_cfrule.equalAverage; rule.IsStdDev = ct_cfrule.stdDevSpecified; rule.StdDev = ct_cfrule.stdDev; break; case ST_CfType.containsText: case ST_CfType.notContainsText: case ST_CfType.beginsWith: case ST_CfType.endsWith: case ST_CfType.cellIs: rule.Text = ct_cfrule.text; rule.Operator = (CFOperator)ct_cfrule.@operator; break; } if (ct_cfrule.dxfIdSpecified) { rule.Style = GetDxfStyle(ct_cfrule.dxfId); } return(rule); }