/// <summary> /// Creates a non shared formula from the shared formula counterpart /// </summary> /// <param name="si">Shared Group Index</param> /// <param name="fpb"></param> /// <returns>non shared formula created for the given shared formula and this cell</returns> private String ConvertSharedFormula(int si, XSSFEvaluationWorkbook fpb) { XSSFSheet sheet = (XSSFSheet)Sheet; CT_CellFormula f = sheet.GetSharedFormula(si); if (f == null) { throw new InvalidOperationException( "Master cell of a shared formula with sid=" + si + " was not found"); } String sharedFormula = f.Value; //Range of cells which the shared formula applies to String sharedFormulaRange = f.@ref; CellRangeAddress ref1 = CellRangeAddress.ValueOf(sharedFormulaRange); int sheetIndex = sheet.Workbook.GetSheetIndex(sheet); SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL2007); Ptg[] ptgs = FormulaParser.Parse(sharedFormula, fpb, FormulaType.Cell, sheetIndex, RowIndex); Ptg[] fmla = sf.ConvertSharedFormulas(ptgs, RowIndex - ref1.FirstRow, ColumnIndex - ref1.FirstColumn); return(FormulaRenderer.ToFormulaString(fpb, fmla)); }
public void TestArrayFormulas() { int rownum = 4; int colnum = 4; FormulaRecord fr = new FormulaRecord(); fr.Row = (rownum); fr.Column = ((short)colnum); FormulaRecordAggregate agg = new FormulaRecordAggregate(fr, null, SharedValueManager.CreateEmpty()); Ptg[] ptgsForCell = { new ExpPtg(rownum, colnum) }; agg.SetParsedExpression(ptgsForCell); String formula = "SUM(A1:A3*B1:B3)"; Ptg[] ptgs = HSSFFormulaParser.Parse(formula, null, FormulaType.Array, 0); agg.SetArrayFormula(new CellRangeAddress(rownum, rownum, colnum, colnum), ptgs); Assert.IsTrue(agg.IsPartOfArrayFormula); Assert.AreEqual("E5", agg.GetArrayFormulaRange().FormatAsString()); Ptg[] ptg = agg.FormulaTokens; String fmlaSer = FormulaRenderer.ToFormulaString(null, ptg); Assert.AreEqual(formula, fmlaSer); agg.RemoveArrayFormula(rownum, colnum); Assert.IsFalse(agg.IsPartOfArrayFormula); }
/// <summary> /// Shift a formula using the supplied FormulaShifter /// </summary> /// <param name="row">the row of the cell this formula belongs to. Used to get a reference to the parent workbook.</param> /// <param name="formula">the formula to shift</param> /// <param name="formulaShifter">the FormulaShifter object that operates on the parsed formula tokens</param> /// <returns>the shifted formula if the formula was changed, null if the formula wasn't modified</returns> public static String ShiftFormula(IRow row, String formula, FormulaShifter formulaShifter) { ISheet sheet = row.Sheet; IWorkbook wb = sheet.Workbook; int sheetIndex = wb.GetSheetIndex(sheet); int rowIndex = row.RowNum; HSSFEvaluationWorkbook fpb = HSSFEvaluationWorkbook.Create((HSSFWorkbook)wb); try { Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.Cell, sheetIndex, rowIndex); String shiftedFmla; if (formulaShifter.AdjustFormula(ptgs, sheetIndex)) { shiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs); } else { shiftedFmla = formula; } return(shiftedFmla); } catch (FormulaParseException fpe) { // Log, but don't change, rather than breaking log.Log(POILogger.ERROR, "Error shifting formula on row " + row.RowNum.ToString(), fpe); return(formula); } }
private void UpdateFormula(XSSFCell cell, IFormulaRenderingWorkbook frwb) { CT_CellFormula f = cell.GetCTCell().f; if (f == null) { return; } string formula = f.Value; if (formula == null || formula.Length <= 0) { return; } int sheetIndex = this._wb.GetSheetIndex(cell.Sheet); Ptg[] ptgs = FormulaParser.Parse(formula, (IFormulaParsingWorkbook)this._fpwb, FormulaType.CELL, sheetIndex); string formulaString = FormulaRenderer.ToFormulaString(frwb, ptgs); if (formula.Equals(formulaString)) { return; } f.Value = formulaString; }
public void TestRead() { String hex = "21 02 25 00 01 00 01 00 01 01 00 00 00 00 00 00 " + "17 00 65 00 00 01 00 02 C0 02 C0 65 00 00 01 00 " + "03 C0 03 C0 04 62 01 07 00"; byte[] data = HexRead.ReadFromString(hex); RecordInputStream in1 = TestcaseRecordInputStream.Create(data); ArrayRecord r1 = new ArrayRecord(in1); CellRangeAddress8Bit range = r1.Range; Assert.AreEqual(1, range.FirstColumn); Assert.AreEqual(1, range.LastColumn); Assert.AreEqual(1, range.FirstRow); Assert.AreEqual(1, range.LastRow); Ptg[] ptg = r1.FormulaTokens; Assert.AreEqual(FormulaRenderer.ToFormulaString(null, ptg), "MAX(C1:C2-D1:D2)"); //construct a new ArrayRecord with the same contents as r1 Ptg[] fmlaPtg = FormulaParser.Parse("MAX(C1:C2-D1:D2)", null, FormulaType.Array, 0); ArrayRecord r2 = new ArrayRecord(Formula.Create(fmlaPtg), new CellRangeAddress8Bit(1, 1, 1, 1)); byte[] ser = r2.Serialize(); //serialize and check that the data is the same as in r1 Assert.AreEqual(HexDump.ToHex(data), HexDump.ToHex(ser)); }
/** * Parse formula in the named range and re-assemble it back using the specified FormulaRenderingWorkbook. * * @param name the name to update * @param frwb the formula rendering workbbok that returns new sheet name */ private void UpdateName(IName name, IFormulaRenderingWorkbook frwb) { String formula = name.RefersToFormula; if (formula != null) { int sheetIndex = name.SheetIndex; Ptg[] ptgs = FormulaParser.Parse(formula, _fpwb, FormulaType.NamedRange, sheetIndex); String updatedFormula = FormulaRenderer.ToFormulaString(frwb, ptgs); if (!formula.Equals(updatedFormula)) { name.RefersToFormula = (updatedFormula); } } }
private static string CopyFormula(string formula, ISheet sheet, int targetIndex) { var workbookWrapper = XSSFEvaluationWorkbook.Create((XSSFWorkbook)sheet.Workbook); var ptgs = FormulaParser.Parse(formula, workbookWrapper, FormulaType.Cell, sheet.Workbook.GetSheetIndex(sheet)); foreach (var ptg in ptgs) { if (ptg is RefPtgBase refs && refs.IsRowRelative) { refs.Row = targetIndex; } } return(FormulaRenderer.ToFormulaString(workbookWrapper, ptgs)); }
private static string ShiftFormula(XSSFRow row, string formula, FormulaShifter Shifter) { ISheet sheet = row.Sheet; IWorkbook workbook = sheet.Workbook; int sheetIndex = workbook.GetSheetIndex(sheet); XSSFEvaluationWorkbook evaluationWorkbook = XSSFEvaluationWorkbook.Create(workbook); Ptg[] ptgs = FormulaParser.Parse(formula, (IFormulaParsingWorkbook)evaluationWorkbook, FormulaType.CELL, sheetIndex); string str = (string)null; if (Shifter.AdjustFormula(ptgs, sheetIndex)) { str = FormulaRenderer.ToFormulaString((IFormulaRenderingWorkbook)evaluationWorkbook, ptgs); } return(str); }
/** * Shift a formula using the supplied FormulaShifter * * @param row the row of the cell this formula belongs to. Used to get a reference to the parent workbook. * @param formula the formula to shift * @param Shifter the FormulaShifter object that operates on the Parsed formula tokens * @return the Shifted formula if the formula was Changed, * <code>null</code> if the formula wasn't modified */ private static String ShiftFormula(XSSFRow row, String formula, FormulaShifter Shifter) { ISheet sheet = row.Sheet; IWorkbook wb = sheet.Workbook; int sheetIndex = wb.GetSheetIndex(sheet); XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb); Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.Cell, sheetIndex); String ShiftedFmla = null; if (Shifter.AdjustFormula(ptgs, sheetIndex)) { ShiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs); } return(ShiftedFmla); }
private string ConvertSharedFormula(int si) { XSSFSheet sheet = (XSSFSheet)this.Sheet; CT_CellFormula sharedFormula = sheet.GetSharedFormula(si); if (sharedFormula == null) { throw new InvalidOperationException("Master cell of a shared formula with sid=" + (object)si + " was not found"); } string formula = sharedFormula.Value; CellRangeAddress cellRangeAddress = CellRangeAddress.ValueOf(sharedFormula.@ref); int sheetIndex = sheet.Workbook.GetSheetIndex((ISheet)sheet); XSSFEvaluationWorkbook evaluationWorkbook = XSSFEvaluationWorkbook.Create(sheet.Workbook); Ptg[] ptgs = new SharedFormula(SpreadsheetVersion.EXCEL2007).ConvertSharedFormulas(FormulaParser.Parse(formula, (IFormulaParsingWorkbook)evaluationWorkbook, FormulaType.CELL, sheetIndex), this.RowIndex - cellRangeAddress.FirstRow, this.ColumnIndex - cellRangeAddress.FirstColumn); return(FormulaRenderer.ToFormulaString((IFormulaRenderingWorkbook)evaluationWorkbook, ptgs)); }
public void UpdateNamedRanges(FormulaShifter shifter) { IWorkbook workbook = this.sheet.Workbook; XSSFEvaluationWorkbook evaluationWorkbook = XSSFEvaluationWorkbook.Create(workbook); for (int nameIndex = 0; nameIndex < workbook.NumberOfNames; ++nameIndex) { IName nameAt = workbook.GetNameAt(nameIndex); string refersToFormula = nameAt.RefersToFormula; int sheetIndex = nameAt.SheetIndex; Ptg[] ptgs = FormulaParser.Parse(refersToFormula, (IFormulaParsingWorkbook)evaluationWorkbook, FormulaType.NAMEDRANGE, sheetIndex); if (shifter.AdjustFormula(ptgs, sheetIndex)) { string formulaString = FormulaRenderer.ToFormulaString((IFormulaRenderingWorkbook)evaluationWorkbook, ptgs); nameAt.RefersToFormula = formulaString; } } }
private static FormulaValuePair toFormulaString(Ptg[] ptgs, IFormulaRenderingWorkbook book) { FormulaValuePair pair = new FormulaValuePair(); if (ptgs != null && ptgs.Length > 0) { String aString = FormulaRenderer.ToFormulaString(book, ptgs); if (ptgs.Length == 1 && ptgs[0].GetType() == typeof(NumberPtg)) { pair._value = aString; } else { pair._formula = aString; } } return(pair); }
/** * Updated named ranges */ public override void UpdateNamedRanges(FormulaShifter shifter) { IWorkbook wb = sheet.Workbook; XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb); foreach (IName name in wb.GetAllNames()) { String formula = name.RefersToFormula; int sheetIndex = name.SheetIndex; Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.NamedRange, sheetIndex, -1); if (shifter.AdjustFormula(ptgs, sheetIndex)) { String shiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs); name.RefersToFormula = shiftedFmla; } } }
private void UpdateName(IName name, IFormulaRenderingWorkbook frwb) { string refersToFormula = name.RefersToFormula; if (refersToFormula == null) { return; } int sheetIndex = name.SheetIndex; Ptg[] ptgs = FormulaParser.Parse(refersToFormula, (IFormulaParsingWorkbook)this._fpwb, FormulaType.NAMEDRANGE, sheetIndex); string formulaString = FormulaRenderer.ToFormulaString(frwb, ptgs); if (refersToFormula.Equals(formulaString)) { return; } name.RefersToFormula = formulaString; }
/** * Parse cell formula and re-assemble it back using the specified FormulaRenderingWorkbook. * * @param cell the cell to update * @param frwb the formula rendering workbbok that returns new sheet name */ private void UpdateFormula(XSSFCell cell, IFormulaRenderingWorkbook frwb) { CT_CellFormula f = cell.GetCTCell().f; if (f != null) { String formula = f.Value; if (formula != null && formula.Length > 0) { int sheetIndex = _wb.GetSheetIndex(cell.Sheet); Ptg[] ptgs = FormulaParser.Parse(formula, _fpwb, FormulaType.Cell, sheetIndex); String updatedFormula = FormulaRenderer.ToFormulaString(frwb, ptgs); if (!formula.Equals(updatedFormula)) { f.Value = (updatedFormula); } } } }
/** * Updated named ranges */ public void UpdateNamedRanges(FormulaShifter shifter) { IWorkbook wb = sheet.Workbook; XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb); for (int i = 0; i < wb.NumberOfNames; i++) { IName name = wb.GetNameAt(i); String formula = name.RefersToFormula; int sheetIndex = name.SheetIndex; Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.NamedRange, sheetIndex); if (shifter.AdjustFormula(ptgs, sheetIndex)) { String shiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs); name.RefersToFormula = shiftedFmla; } } }
/** * Parse formula in the named range and re-assemble it back using the specified FormulaRenderingWorkbook. * * @param name the name to update * @param frwb the formula rendering workbbok that returns new sheet name */ private void UpdateName(IName name, String oldName, String newName) { String formula = name.RefersToFormula; if (formula != null) { int sheetIndex = name.SheetIndex; Ptg[] ptgs = FormulaParser.Parse(formula, _fpwb, FormulaType.NamedRange, sheetIndex, -1); foreach (Ptg ptg in ptgs) { UpdatePtg(ptg, oldName, newName); } String updatedFormula = FormulaRenderer.ToFormulaString(_fpwb, ptgs); if (!formula.Equals(updatedFormula)) { name.RefersToFormula = (updatedFormula); } } }
public void TestConvertSharedFormulas() { IWorkbook wb = new HSSFWorkbook(); HSSFEvaluationWorkbook fpb = HSSFEvaluationWorkbook.Create(wb); Ptg[] sharedFormula, ConvertedFormula; SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL97); sharedFormula = FormulaParser.Parse("A2", fpb, FormulaType.Cell, -1, -1); ConvertedFormula = sf.ConvertSharedFormulas(sharedFormula, 0, 0); ConfirmOperandClasses(sharedFormula, ConvertedFormula); //conversion relative to [0,0] should return the original formula Assert.AreEqual(FormulaRenderer.ToFormulaString(fpb, ConvertedFormula), "A2"); ConvertedFormula = sf.ConvertSharedFormulas(sharedFormula, 1, 0); ConfirmOperandClasses(sharedFormula, ConvertedFormula); //one row down Assert.AreEqual(FormulaRenderer.ToFormulaString(fpb, ConvertedFormula), "A3"); ConvertedFormula = sf.ConvertSharedFormulas(sharedFormula, 1, 1); ConfirmOperandClasses(sharedFormula, ConvertedFormula); //one row down and one cell right Assert.AreEqual(FormulaRenderer.ToFormulaString(fpb, ConvertedFormula), "B3"); sharedFormula = FormulaParser.Parse("SUM(A1:C1)", fpb, FormulaType.Cell, -1, -1); ConvertedFormula = sf.ConvertSharedFormulas(sharedFormula, 0, 0); ConfirmOperandClasses(sharedFormula, ConvertedFormula); Assert.AreEqual(FormulaRenderer.ToFormulaString(fpb, ConvertedFormula), "SUM(A1:C1)"); ConvertedFormula = sf.ConvertSharedFormulas(sharedFormula, 1, 0); ConfirmOperandClasses(sharedFormula, ConvertedFormula); Assert.AreEqual(FormulaRenderer.ToFormulaString(fpb, ConvertedFormula), "SUM(A2:C2)"); ConvertedFormula = sf.ConvertSharedFormulas(sharedFormula, 1, 1); ConfirmOperandClasses(sharedFormula, ConvertedFormula); Assert.AreEqual(FormulaRenderer.ToFormulaString(fpb, ConvertedFormula), "SUM(B2:D2)"); }
/** * Parse cell formula and re-assemble it back using the specified FormulaRenderingWorkbook. * * @param cell the cell to update * @param frwb the formula rendering workbbok that returns new sheet name */ private void UpdateFormula(XSSFCell cell, String oldName, String newName) { CT_CellFormula f = cell.GetCTCell().f; if (f != null) { String formula = f.Value; if (formula != null && formula.Length > 0) { int sheetIndex = _wb.GetSheetIndex(cell.Sheet); Ptg[] ptgs = FormulaParser.Parse(formula, _fpwb, FormulaType.Cell, sheetIndex, -1); foreach (Ptg ptg in ptgs) { UpdatePtg(ptg, oldName, newName); } String updatedFormula = FormulaRenderer.ToFormulaString(_fpwb, ptgs); if (!formula.Equals(updatedFormula)) { f.Value = (updatedFormula); } } } }
/** * Shift a formula using the supplied FormulaShifter * * @param row the row of the cell this formula belongs to. Used to get a reference to the parent workbook. * @param formula the formula to shift * @param Shifter the FormulaShifter object that operates on the Parsed formula tokens * @return the Shifted formula if the formula was Changed, * <code>null</code> if the formula wasn't modified */ private static String ShiftFormula(XSSFRow row, String formula, FormulaShifter Shifter) { ISheet sheet = row.Sheet; IWorkbook wb = sheet.Workbook; int sheetIndex = wb.GetSheetIndex(sheet); XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb); try { Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.Cell, sheetIndex); String ShiftedFmla = null; if (Shifter.AdjustFormula(ptgs, sheetIndex)) { ShiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs); } return(ShiftedFmla); } catch (FormulaParseException fpe) { // Log, but don't change, rather than breaking Console.WriteLine("Error shifting formula on row {0}, {1}", row.RowNum, fpe); return(formula); } }
/** * Static method to convert an array of {@link Ptg}s in RPN order * to a human readable string format in infix mode. * @param book used for defined names and 3D references * @param ptgs must not be <c>null</c> * @return a human readable String */ public static String ToFormulaString(HSSFWorkbook book, Ptg[] ptgs) { return(FormulaRenderer.ToFormulaString(HSSFEvaluationWorkbook.Create(book), ptgs)); }
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; } } } } }
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 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; } } } } }