public void TestConvertSharedFormulasOperandClasses_bug45123() { ILittleEndianInput in1 = TestcaseRecordInputStream.CreateLittleEndian(SHARED_FORMULA_WITH_REF_ARRAYS_DATA); int encodedLen = in1.ReadUShort(); Ptg[] sharedFormula = Ptg.ReadTokens(encodedLen, in1); SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL97); Ptg[] ConvertedFormula = sf.ConvertSharedFormulas(sharedFormula, 100, 200); RefPtg refPtg = (RefPtg)ConvertedFormula[1]; Assert.AreEqual("$C101", refPtg.ToFormulaString()); if (refPtg.PtgClass == Ptg.CLASS_REF) { throw new AssertionException("Identified bug 45123"); } ConfirmOperandClasses(sharedFormula, ConvertedFormula); }
/// <summary> /// Creates a non shared formula from the shared formula counterpart /// </summary> /// <param name="si">Shared Group Index</param> /// <returns>non shared formula created for the given shared formula and this cell</returns> private String ConvertSharedFormula(int si) { 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); XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(sheet.Workbook); SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL2007); Ptg[] ptgs = FormulaParser.Parse(sharedFormula, fpb, FormulaType.Cell, sheetIndex); Ptg[] fmla = sf.ConvertSharedFormulas(ptgs, RowIndex - ref1.FirstRow, ColumnIndex - ref1.FirstColumn); return FormulaRenderer.ToFormulaString(fpb, fmla); }
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); 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); 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)"); }
/** * @return the equivalent {@link Ptg} array that the formula would have, were it not shared. */ public Ptg[] GetFormulaTokens(FormulaRecord formula) { int formulaRow = formula.Row; int formulaColumn = formula.Column; //Sanity checks if (!IsInRange(formulaRow, formulaColumn)) { throw new Exception("Shared Formula Conversion: Coding Error"); } SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL97); return sf.ConvertSharedFormulas(field_7_parsed_expr.Tokens, formulaRow, formulaColumn); //return ConvertSharedFormulas(field_7_parsed_expr.Tokens, formulaRow, formulaColumn); }