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();
}
/**
* Gets Conditional Formatting object at a particular index
*
* @param index
* of the Conditional Formatting object to fetch
* @return Conditional Formatting object
*/
public IConditionalFormatting GetConditionalFormattingAt(int index)
{
CheckIndex(index);
CT_ConditionalFormatting cf = _sheet.GetCTWorksheet().GetConditionalFormattingArray(index);
return(new XSSFConditionalFormatting(_sheet, cf));
}
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);
}
public CT_ConditionalFormatting AddNewConditionalFormatting()
{
if (null == conditionalFormattingField)
{
conditionalFormattingField = new List <CT_ConditionalFormatting>();
}
CT_ConditionalFormatting cf = new CT_ConditionalFormatting();
this.conditionalFormattingField.Add(cf);
return(cf);
}
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);
}
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);
}
/*package*/
internal XSSFConditionalFormatting(XSSFSheet sh, CT_ConditionalFormatting cf)
{
_cf = cf;
_sh = sh;
}
/*package*/
internal XSSFConditionalFormatting(XSSFSheet sh)
{
_cf = new CT_ConditionalFormatting();
_sh = sh;
}
public void UpdateConditionalFormatting(FormulaShifter Shifter)
{
IWorkbook workbook = this.sheet.Workbook;
int sheetIndex = workbook.GetSheetIndex((ISheet)this.sheet);
XSSFEvaluationWorkbook evaluationWorkbook = XSSFEvaluationWorkbook.Create(workbook);
List <CT_ConditionalFormatting> conditionalFormatting1 = this.sheet.GetCTWorksheet().conditionalFormatting;
for (int index1 = 0; conditionalFormatting1 != null && index1 < conditionalFormatting1.Count; ++index1)
{
CT_ConditionalFormatting conditionalFormatting2 = conditionalFormatting1[index1];
List <CellRangeAddress> cellRangeAddressList1 = new List <CellRangeAddress>();
foreach (object obj in conditionalFormatting2.sqref)
{
string str = obj.ToString();
char[] chArray = new char[1] {
' '
};
foreach (string reference in str.Split(chArray))
{
cellRangeAddressList1.Add(CellRangeAddress.ValueOf(reference));
}
}
bool flag = false;
List <CellRangeAddress> cellRangeAddressList2 = new List <CellRangeAddress>();
for (int index2 = 0; index2 < cellRangeAddressList1.Count; ++index2)
{
CellRangeAddress cra = cellRangeAddressList1[index2];
CellRangeAddress cellRangeAddress = XSSFRowShifter.ShiftRange(Shifter, cra, sheetIndex);
if (cellRangeAddress == null)
{
flag = true;
}
else
{
cellRangeAddressList2.Add(cellRangeAddress);
if (cellRangeAddress != cra)
{
flag = true;
}
}
}
if (flag)
{
if (cellRangeAddressList2.Count == 0)
{
conditionalFormatting1.RemoveAt(index1);
continue;
}
List <string> stringList = new List <string>();
foreach (CellRangeAddress cellRangeAddress in cellRangeAddressList2)
{
stringList.Add(cellRangeAddress.FormatAsString());
}
conditionalFormatting2.sqref = stringList;
}
foreach (CT_CfRule ctCfRule in conditionalFormatting2.cfRule)
{
List <string> formula = ctCfRule.formula;
for (int index2 = 0; index2 < formula.Count; ++index2)
{
Ptg[] ptgs = FormulaParser.Parse(formula[index2], (IFormulaParsingWorkbook)evaluationWorkbook, FormulaType.CELL, sheetIndex);
if (Shifter.AdjustFormula(ptgs, sheetIndex))
{
string formulaString = FormulaRenderer.ToFormulaString((IFormulaRenderingWorkbook)evaluationWorkbook, ptgs);
formula[index2] = formulaString;
}
}
}
}
}
public static CT_Worksheet Parse(XmlNode node, XmlNamespaceManager namespaceManager)
{
if (node == null)
{
return(null);
}
CT_Worksheet ctObj = new CT_Worksheet();
ctObj.cols = new List <CT_Cols>();
ctObj.conditionalFormatting = new List <CT_ConditionalFormatting>();
foreach (XmlNode childNode in node.ChildNodes)
{
if (childNode.LocalName == "sheetPr")
{
ctObj.sheetPr = CT_SheetPr.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "dimension")
{
ctObj.dimension = CT_SheetDimension.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "sheetViews")
{
ctObj.sheetViews = CT_SheetViews.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "sheetFormatPr")
{
ctObj.sheetFormatPr = CT_SheetFormatPr.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "sheetData")
{
ctObj.sheetData = CT_SheetData.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "sheetCalcPr")
{
ctObj.sheetCalcPr = CT_SheetCalcPr.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "sheetProtection")
{
ctObj.sheetProtection = CT_SheetProtection.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "protectedRanges")
{
ctObj.protectedRanges = CT_ProtectedRanges.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "scenarios")
{
ctObj.scenarios = CT_Scenarios.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "autoFilter")
{
ctObj.autoFilter = CT_AutoFilter.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "sortState")
{
ctObj.sortState = CT_SortState.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "dataConsolidate")
{
ctObj.dataConsolidate = CT_DataConsolidate.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "customSheetViews")
{
ctObj.customSheetViews = CT_CustomSheetViews.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "mergeCells")
{
ctObj.mergeCells = CT_MergeCells.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "phoneticPr")
{
ctObj.phoneticPr = CT_PhoneticPr.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "dataValidations")
{
ctObj.dataValidations = CT_DataValidations.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "hyperlinks")
{
ctObj.hyperlinks = CT_Hyperlinks.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "printOptions")
{
ctObj.printOptions = CT_PrintOptions.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "pageMargins")
{
ctObj.pageMargins = CT_PageMargins.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "pageSetup")
{
ctObj.pageSetup = CT_PageSetup.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "headerFooter")
{
ctObj.headerFooter = CT_HeaderFooter.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "rowBreaks")
{
ctObj.rowBreaks = CT_PageBreak.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "colBreaks")
{
ctObj.colBreaks = CT_PageBreak.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "customProperties")
{
ctObj.customProperties = CT_CustomProperties.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "cellWatches")
{
ctObj.cellWatches = CT_CellWatches.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "ignoredErrors")
{
ctObj.ignoredErrors = CT_IgnoredErrors.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "smartTags")
{
ctObj.smartTags = CT_CellSmartTags.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "drawing")
{
ctObj.drawing = CT_Drawing.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "legacyDrawing")
{
ctObj.legacyDrawing = CT_LegacyDrawing.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "legacyDrawingHF")
{
ctObj.legacyDrawingHF = CT_LegacyDrawing.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "picture")
{
ctObj.picture = CT_SheetBackgroundPicture.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "oleObjects")
{
ctObj.oleObjects = CT_OleObjects.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "controls")
{
ctObj.controls = CT_Controls.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "webPublishItems")
{
ctObj.webPublishItems = CT_WebPublishItems.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "tableParts")
{
ctObj.tableParts = CT_TableParts.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "extLst")
{
ctObj.extLst = CT_ExtensionList.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "cols")
{
ctObj.cols.Add(CT_Cols.Parse(childNode, namespaceManager));
}
else if (childNode.LocalName == "conditionalFormatting")
{
ctObj.conditionalFormatting.Add(CT_ConditionalFormatting.Parse(childNode, namespaceManager));
}
}
return(ctObj);
}
public void UpdateConditionalFormatting(FormulaShifter Shifter)
{
IWorkbook wb = sheet.Workbook;
int sheetIndex = wb.GetSheetIndex(sheet);
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);
CT_Worksheet ctWorksheet = sheet.GetCTWorksheet();
List <CT_ConditionalFormatting> conditionalFormattingArray = ctWorksheet.conditionalFormatting;
// iterate backwards due to possible calls to ctWorksheet.removeConditionalFormatting(j)
for (int j = conditionalFormattingArray.Count - 1; j >= 0; j--)
{
CT_ConditionalFormatting cf = conditionalFormattingArray[j];
List <CellRangeAddress> cellRanges = new List <CellRangeAddress>();
String[] regions = cf.sqref.ToString().Split(new char[] { ' ' });
for (int i = 0; i < regions.Length; i++)
{
cellRanges.Add(CellRangeAddress.ValueOf(regions[i]));
}
bool Changed = false;
List <CellRangeAddress> temp = new List <CellRangeAddress>();
for (int i = 0; i < cellRanges.Count; i++)
{
CellRangeAddress craOld = cellRanges[i];
CellRangeAddress craNew = ShiftRange(Shifter, craOld, sheetIndex);
if (craNew == null)
{
Changed = true;
continue;
}
temp.Add(craNew);
if (craNew != craOld)
{
Changed = true;
}
}
if (Changed)
{
int nRanges = temp.Count;
if (nRanges == 0)
{
conditionalFormattingArray.RemoveAt(j);
continue;
}
string refs = string.Empty;
foreach (CellRangeAddress a in temp)
{
if (refs.Length == 0)
{
refs = a.FormatAsString();
}
else
{
refs += " " + a.FormatAsString();
}
}
cf.sqref = refs;
}
foreach (CT_CfRule cfRule in cf.cfRule)
{
List <String> formulas = cfRule.formula;
for (int i = 0; i < formulas.Count; i++)
{
String formula = formulas[i];
Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.Cell, sheetIndex);
if (Shifter.AdjustFormula(ptgs, sheetIndex))
{
String ShiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs);
formulas[i] = ShiftedFmla;
}
}
}
}
}
public void UpdateConditionalFormatting(FormulaShifter Shifter)
{
IWorkbook wb = sheet.Workbook;
int sheetIndex = wb.GetSheetIndex(sheet);
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);
List <CT_ConditionalFormatting> cfList = sheet.GetCTWorksheet().conditionalFormatting;
for (int j = 0; cfList != null && j < cfList.Count; j++)
{
CT_ConditionalFormatting cf = cfList[j];
List <CellRangeAddress> cellRanges = new List <CellRangeAddress>();
foreach (Object stRef in cf.sqref)
{
String[] regions = stRef.ToString().Split(new char[] { ' ' });
for (int i = 0; i < regions.Length; i++)
{
cellRanges.Add(CellRangeAddress.ValueOf(regions[i]));
}
}
bool Changed = false;
List <CellRangeAddress> temp = new List <CellRangeAddress>();
for (int i = 0; i < cellRanges.Count; i++)
{
CellRangeAddress craOld = cellRanges[i];
CellRangeAddress craNew = ShiftRange(Shifter, craOld, sheetIndex);
if (craNew == null)
{
Changed = true;
continue;
}
temp.Add(craNew);
if (craNew != craOld)
{
Changed = true;
}
}
if (Changed)
{
int nRanges = temp.Count;
if (nRanges == 0)
{
cfList.RemoveAt(j);
continue;
}
List <String> refs = new List <String>();
foreach (CellRangeAddress a in temp)
{
refs.Add(a.FormatAsString());
}
cf.sqref = (refs);
}
foreach (CT_CfRule cfRule in cf.cfRule)
{
List <String> formulas = cfRule.formula;
for (int i = 0; i < formulas.Count; i++)
{
String formula = formulas[i];
Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.CELL, sheetIndex);
if (Shifter.AdjustFormula(ptgs, sheetIndex))
{
String ShiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs);
formulas[i] = ShiftedFmla;
}
}
}
}
}
