/**
* 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;
}
