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