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);
}
