/// <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); } }
public void TestDiscontinousReference() { Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("44167.xls"); HSSFWorkbook wb = new HSSFWorkbook(is1); NPOI.HSSF.Model.Workbook workbook = wb.Workbook; HSSFEvaluationWorkbook eb = HSSFEvaluationWorkbook.Create(wb); Assert.AreEqual(1, wb.NumberOfNames); String sheetName = "Tabelle1"; String rawRefA = "$C$10:$C$14"; String rawRefB = "$C$16:$C$18"; String refA = sheetName + "!" + rawRefA; String refB = sheetName + "!" + rawRefB; String ref1 = refA + "," + refB; // Check the low level record NameRecord nr = workbook.GetNameRecord(0); Assert.IsNotNull(nr); Assert.AreEqual("test", nr.NameText); Ptg[] def = nr.NameDefinition; Assert.AreEqual(4, def.Length); MemFuncPtg ptgA = (MemFuncPtg)def[0]; Area3DPtg ptgB = (Area3DPtg)def[1]; Area3DPtg ptgC = (Area3DPtg)def[2]; UnionPtg ptgD = (UnionPtg)def[3]; Assert.AreEqual("", ptgA.ToFormulaString()); Assert.AreEqual(refA, ptgB.ToFormulaString(eb)); Assert.AreEqual(refB, ptgC.ToFormulaString(eb)); Assert.AreEqual(",", ptgD.ToFormulaString()); Assert.AreEqual(ref1, NPOI.HSSF.Model.HSSFFormulaParser.ToFormulaString(wb, nr.NameDefinition)); // Check the high level definition int idx = wb.GetNameIndex("test"); Assert.AreEqual(0, idx); NPOI.SS.UserModel.Name aNamedCell = wb.GetNameAt(idx); // Should have 2 references Assert.AreEqual(ref1, aNamedCell.RefersToFormula); // Check the parsing of the reference into cells Assert.IsFalse(AreaReference.IsContiguous(aNamedCell.RefersToFormula)); AreaReference[] arefs = AreaReference.GenerateContiguous(aNamedCell.RefersToFormula); Assert.AreEqual(2, arefs.Length); Assert.AreEqual(refA, arefs[0].FormatAsString()); Assert.AreEqual(refB, arefs[1].FormatAsString()); for (int i = 0; i < arefs.Length; i++) { AreaReference ar = arefs[i]; ConfirmResolveCellRef(wb, ar.FirstCell); ConfirmResolveCellRef(wb, ar.LastCell); } }
private static ValueEval EvaluateFormula(Ptg[] ptgs) { HSSFWorkbook wb = new HSSFWorkbook(); wb.CreateSheet().CreateRow(0).CreateCell(0); IEvaluationWorkbook ewb = HSSFEvaluationWorkbook.Create(wb); OperationEvaluationContext ec = new OperationEvaluationContext(null, ewb, 0, 0, 0, null); return(new WorkbookEvaluator(null, null, null).EvaluateFormula(ec, ptgs)); }
private OperationEvaluationContext CreateContext() { HSSFWorkbook wb = new HSSFWorkbook(); wb.CreateSheet(); HSSFEvaluationWorkbook workbook = HSSFEvaluationWorkbook.Create(wb); WorkbookEvaluator workbookEvaluator = new WorkbookEvaluator(workbook, new IStabilityClassifier1(), null); OperationEvaluationContext ctx = new OperationEvaluationContext(workbookEvaluator, workbook, 0, 0, 0, null); return(ctx); }
private static IEvaluationWorkbook CreateEvaluationWorkbook(IWorkbook wb) { if (wb is HSSFWorkbook) { return(HSSFEvaluationWorkbook.Create((HSSFWorkbook)wb)); } // TODO rearrange POI build to allow this // if (wb is XSSFWorkbook) { // return XSSFEvaluationWorkbook.Create((XSSFWorkbook) wb); // } throw new ArgumentException("Unexpected workbook type (" + wb.GetType().Name + ")"); }
public void TestHSSFFailsForOver65536() { IFormulaParsingWorkbook workbook = HSSFEvaluationWorkbook.Create(new HSSFWorkbook()); try { FormulaParser.Parse("Sheet1!1:65537", workbook, FormulaType.Cell, 0); Assert.Fail("Expected exception"); } catch (FormulaParseException) { } }
public void TestToFormulaString() { Ref3DPtg target = new Ref3DPtg("A1", (short)0); HSSFWorkbook wb = CreateWorkbookWithSheet("my sheet"); HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.Create(wb); Assert.AreEqual("'my sheet'!A1", target.ToFormulaString(book)); wb.SetSheetName(0, "ProfitAndLoss"); Assert.AreEqual("ProfitAndLoss!A1", target.ToFormulaString(book)); wb.SetSheetName(0, "profit+loss"); Assert.AreEqual("'profit+loss'!A1", target.ToFormulaString(book)); }
public void TestToFormulaString() { Area3DPtg target = new Area3DPtg("A1:B1", (short)0); string sheetName = "my sheet"; HSSFWorkbook wb = CreateWorkbookWithSheet(sheetName); HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.Create(wb); Assert.AreEqual("'my sheet'!A1:B1", target.ToFormulaString(book)); wb.SetSheetName(0, "Sheet1"); Assert.AreEqual("Sheet1!A1:B1", target.ToFormulaString(book)); wb.SetSheetName(0, "C64"); Assert.AreEqual("'C64'!A1:B1", target.ToFormulaString(book)); }
public void TestMacroFunction() { // testNames.xls contains a VB function called 'myFunc' HSSFWorkbook w = HSSFTestDataSamples.OpenSampleWorkbook("testNames.xls"); HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.Create(w); Ptg[] ptg = HSSFFormulaParser.Parse("myFunc()", w); // the name Gets encoded as the first arg NamePtg tname = (NamePtg)ptg[0]; Assert.AreEqual("myFunc", tname.ToFormulaString(book)); AbstractFunctionPtg tfunc = (AbstractFunctionPtg)ptg[1]; Assert.IsTrue(tfunc.IsExternalFunction); }
private OperationEvaluationContext CreateContext() { HSSFWorkbook wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet(); IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell(0); cell.SetCellValue("123.43"); cell = row.CreateCell(1); cell.SetCellValue("8"); cell = row.CreateCell(2); cell.SetCellValue("-8"); HSSFEvaluationWorkbook workbook = HSSFEvaluationWorkbook.Create(wb); WorkbookEvaluator workbookEvaluator = new WorkbookEvaluator(workbook, new IStabilityClassifier1(), null); OperationEvaluationContext ctx = new OperationEvaluationContext(workbookEvaluator, workbook, 0, 0, 0, null); return(ctx); }
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)"); }
private static IEvaluationWorkbook CreateEvaluationWorkbook(IWorkbook wb) { if (wb is HSSFWorkbook) { return(HSSFEvaluationWorkbook.Create((HSSFWorkbook)wb)); } else { try { // TODO: check if this is Java 9 compatible ... Type evalWB = Type.GetType("NPOI.XSSF.UserModel.XSSFEvaluationWorkbook"); Type xssfWB = Type.GetType("NPOI.XSSF.UserMode.XSSFWorkbook"); MethodInfo createM = evalWB.GetMethod("create", new Type[] { xssfWB }); return((IEvaluationWorkbook)createM.Invoke(null, new object[] { wb })); } catch (Exception e) { throw new ArgumentException("Unexpected workbook type (" + wb.GetType().Name + ") - check for poi-ooxml and poi-ooxml schemas jar in the classpath", e); } } }
/** * 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)); }
private static IFormulaParsingWorkbook CreateParsingWorkbook(HSSFWorkbook book) { return(HSSFEvaluationWorkbook.Create(book)); }
public void TestHSSFPassCase() { IFormulaParsingWorkbook workbook = HSSFEvaluationWorkbook.Create(new HSSFWorkbook()); FormulaParser.Parse("Sheet1!1:65536", workbook, FormulaType.Cell, 0); }
public static WorkbookEvaluator CreateEvaluator(HSSFWorkbook wb, EvaluationListener listener) { return(new WorkbookEvaluator(HSSFEvaluationWorkbook.Create(wb), listener, null, null)); }
public void MultiSheetReferencesHSSFandXSSF() { IWorkbook[] wbs = new IWorkbook[] { HSSFTestDataSamples.OpenSampleWorkbook("55906-MultiSheetRefs.xls"), XSSFTestDataSamples.OpenSampleWorkbook("55906-MultiSheetRefs.xlsx") }; foreach (IWorkbook wb in wbs) { ISheet s1 = wb.GetSheetAt(0); Ptg[] ptgs; // Check the contents ICell sumF = s1.GetRow(2).GetCell(0); Assert.IsNotNull(sumF); Assert.AreEqual("SUM(Sheet1:Sheet3!A1)", sumF.CellFormula); ICell avgF = s1.GetRow(2).GetCell(1); Assert.IsNotNull(avgF); Assert.AreEqual("AVERAGE(Sheet1:Sheet3!A1)", avgF.CellFormula); ICell countAF = s1.GetRow(2).GetCell(2); Assert.IsNotNull(countAF); Assert.AreEqual("COUNTA(Sheet1:Sheet3!C1)", countAF.CellFormula); ICell maxF = s1.GetRow(4).GetCell(1); Assert.IsNotNull(maxF); Assert.AreEqual("MAX(Sheet1:Sheet3!A$1)", maxF.CellFormula); ICell sumFA = s1.GetRow(2).GetCell(7); Assert.IsNotNull(sumFA); Assert.AreEqual("SUM(Sheet1:Sheet3!A1:B2)", sumFA.CellFormula); ICell avgFA = s1.GetRow(2).GetCell(8); Assert.IsNotNull(avgFA); Assert.AreEqual("AVERAGE(Sheet1:Sheet3!A1:B2)", avgFA.CellFormula); ICell maxFA = s1.GetRow(4).GetCell(8); Assert.IsNotNull(maxFA); Assert.AreEqual("MAX(Sheet1:Sheet3!A$1:B$2)", maxFA.CellFormula); ICell countFA = s1.GetRow(5).GetCell(8); Assert.IsNotNull(countFA); Assert.AreEqual("COUNT(Sheet1:Sheet3!$A$1:$B$2)", countFA.CellFormula); // Create a formula Parser IFormulaParsingWorkbook fpb = null; if (wb is HSSFWorkbook) { fpb = HSSFEvaluationWorkbook.Create((HSSFWorkbook)wb); } else { fpb = XSSFEvaluationWorkbook.Create((XSSFWorkbook)wb); } // Check things parse as expected: // SUM to one cell over 3 workbooks, relative reference ptgs = Parse(fpb, "SUM(Sheet1:Sheet3!A1)"); Assert.AreEqual(2, ptgs.Length); if (wb is HSSFWorkbook) { Assert.AreEqual(typeof(Ref3DPtg), ptgs[0].GetType()); } else { Assert.AreEqual(typeof(Ref3DPxg), ptgs[0].GetType()); } Assert.AreEqual("Sheet1:Sheet3!A1", ToFormulaString(ptgs[0], fpb)); Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType()); Assert.AreEqual("SUM", ToFormulaString(ptgs[1], fpb)); // MAX to one cell over 3 workbooks, absolute row reference ptgs = Parse(fpb, "MAX(Sheet1:Sheet3!A$1)"); Assert.AreEqual(2, ptgs.Length); if (wb is HSSFWorkbook) { Assert.AreEqual(typeof(Ref3DPtg), ptgs[0].GetType()); } else { Assert.AreEqual(typeof(Ref3DPxg), ptgs[0].GetType()); } Assert.AreEqual("Sheet1:Sheet3!A$1", ToFormulaString(ptgs[0], fpb)); Assert.AreEqual(typeof(FuncVarPtg), ptgs[1].GetType()); Assert.AreEqual("MAX", ToFormulaString(ptgs[1], fpb)); // MIN to one cell over 3 workbooks, absolute reference ptgs = Parse(fpb, "MIN(Sheet1:Sheet3!$A$1)"); Assert.AreEqual(2, ptgs.Length); if (wb is HSSFWorkbook) { Assert.AreEqual(typeof(Ref3DPtg), ptgs[0].GetType()); } else { Assert.AreEqual(typeof(Ref3DPxg), ptgs[0].GetType()); } Assert.AreEqual("Sheet1:Sheet3!$A$1", ToFormulaString(ptgs[0], fpb)); Assert.AreEqual(typeof(FuncVarPtg), ptgs[1].GetType()); Assert.AreEqual("MIN", ToFormulaString(ptgs[1], fpb)); // SUM to a range of cells over 3 workbooks ptgs = Parse(fpb, "SUM(Sheet1:Sheet3!A1:B2)"); Assert.AreEqual(2, ptgs.Length); if (wb is HSSFWorkbook) { Assert.AreEqual(typeof(Area3DPtg), ptgs[0].GetType()); } else { Assert.AreEqual(typeof(Area3DPxg), ptgs[0].GetType()); } Assert.AreEqual(ToFormulaString(ptgs[0], fpb), "Sheet1:Sheet3!A1:B2"); Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType()); Assert.AreEqual("SUM", ToFormulaString(ptgs[1], fpb)); // MIN to a range of cells over 3 workbooks, absolute reference ptgs = Parse(fpb, "MIN(Sheet1:Sheet3!$A$1:$B$2)"); Assert.AreEqual(2, ptgs.Length); if (wb is HSSFWorkbook) { Assert.AreEqual(typeof(Area3DPtg), ptgs[0].GetType()); } else { Assert.AreEqual(typeof(Area3DPxg), ptgs[0].GetType()); } Assert.AreEqual(ToFormulaString(ptgs[0], fpb), "Sheet1:Sheet3!$A$1:$B$2"); Assert.AreEqual(typeof(FuncVarPtg), ptgs[1].GetType()); Assert.AreEqual("MIN", ToFormulaString(ptgs[1], fpb)); // Check we can round-trip - try to Set a new one to a new single cell ICell newF = s1.GetRow(0).CreateCell(10, CellType.Formula); newF.CellFormula = (/*setter*/ "SUM(Sheet2:Sheet3!A1)"); Assert.AreEqual("SUM(Sheet2:Sheet3!A1)", newF.CellFormula); // Check we can round-trip - try to Set a new one to a cell range newF = s1.GetRow(0).CreateCell(11, CellType.Formula); newF.CellFormula = (/*setter*/ "MIN(Sheet1:Sheet2!A1:B2)"); Assert.AreEqual("MIN(Sheet1:Sheet2!A1:B2)", newF.CellFormula); } }
public void TestFormulas() { FormulaRecord[] fRecs = mockListen.GetFormulaRecords(); // Check our formula records Assert.AreEqual(6, fRecs.Length); InternalWorkbook stubWB = listener.GetStubWorkbook(); Assert.IsNotNull(stubWB); HSSFWorkbook stubHSSF = listener.GetStubHSSFWorkbook(); Assert.IsNotNull(stubHSSF); // Check these stubs have the right stuff on them Assert.AreEqual("Sheet1", stubWB.GetSheetName(0)); Assert.AreEqual("S2", stubWB.GetSheetName(1)); Assert.AreEqual("Sh3", stubWB.GetSheetName(2)); // Check we can Get the formula without breaking for (int i = 0; i < fRecs.Length; i++) { HSSFFormulaParser.ToFormulaString(stubHSSF, fRecs[i].ParsedExpression); } // Peer into just one formula, and check that // all the ptgs give back the right things Ptg[] ptgs = fRecs[0].ParsedExpression; Assert.AreEqual(1, ptgs.Length); Assert.IsTrue(ptgs[0] is Ref3DPtg); Ref3DPtg ptg = (Ref3DPtg)ptgs[0]; HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.Create(stubHSSF); Assert.AreEqual("Sheet1!A1", ptg.ToFormulaString(book)); // Now check we Get the right formula back for // a few sample ones FormulaRecord fr; // Sheet 1 A2 is on same sheet fr = fRecs[0]; Assert.AreEqual(1, fr.Row); Assert.AreEqual(0, fr.Column); Assert.AreEqual("Sheet1!A1", HSSFFormulaParser.ToFormulaString(stubHSSF, fr.ParsedExpression)); // Sheet 1 A5 is to another sheet fr = fRecs[3]; Assert.AreEqual(4, fr.Row); Assert.AreEqual(0, fr.Column); Assert.AreEqual("'S2'!A1", HSSFFormulaParser.ToFormulaString(stubHSSF, fr.ParsedExpression)); // Sheet 1 A7 is to another sheet, range fr = fRecs[5]; Assert.AreEqual(6, fr.Row); Assert.AreEqual(0, fr.Column); Assert.AreEqual("SUM(Sh3!A1:A4)", HSSFFormulaParser.ToFormulaString(stubHSSF, fr.ParsedExpression)); // Now, load via Usermodel and re-check HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("3dFormulas.xls"); Assert.AreEqual("Sheet1!A1", wb.GetSheetAt(0).GetRow(1).GetCell(0).CellFormula); Assert.AreEqual("SUM(Sh3!A1:A4)", wb.GetSheetAt(0).GetRow(6).GetCell(0).CellFormula); }