/// <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)); }
private void SetFormula(string formula, FormulaType formulaType) { IWorkbook wb = _row.Sheet.Workbook; if (formula == null) { ((XSSFWorkbook)wb).OnDeleteFormula(this); if (_cell.IsSetF()) { _cell.unsetF(); } return; } IFormulaParsingWorkbook fpb = XSSFEvaluationWorkbook.Create(wb); //validate through the FormulaParser FormulaParser.Parse(formula, fpb, formulaType, wb.GetSheetIndex(this.Sheet)); CT_CellFormula f = new CT_CellFormula(); f.Value = formula; _cell.f = (f); if (_cell.IsSetV()) { _cell.unsetV(); } }
public Ptg[] GetFormulaTokens(IEvaluationCell EvalCell) { XSSFCell cell = ((XSSFEvaluationCell)EvalCell).GetXSSFCell(); XSSFEvaluationWorkbook frBook = XSSFEvaluationWorkbook.Create(_uBook); return(FormulaParser.Parse(cell.CellFormula, frBook, FormulaType.Cell, _uBook.GetSheetIndex(cell.Sheet))); }
public void FormulaReferencesOtherWorkbook() { // Use a test file with the external linked table in place XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("ref-56737.xlsx"); XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb); Ptg[] ptgs; // Reference to a single cell in a different workbook ptgs = Parse(fpb, "[1]Uses!$A$1"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual(typeof(Ref3DPxg), ptgs[0].GetType()); Assert.AreEqual(1, ((Ref3DPxg)ptgs[0]).ExternalWorkbookNumber); Assert.AreEqual("Uses", ((Ref3DPxg)ptgs[0]).SheetName); Assert.AreEqual("$A$1", ((Ref3DPxg)ptgs[0]).Format2DRefAsString()); Assert.AreEqual("[1]Uses!$A$1", ((Ref3DPxg)ptgs[0]).ToFormulaString()); // Reference to a sheet-scoped named range in a different workbook ptgs = Parse(fpb, "[1]Defines!NR_To_A1"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual(typeof(NameXPxg), ptgs[0].GetType()); Assert.AreEqual(1, ((NameXPxg)ptgs[0]).ExternalWorkbookNumber); Assert.AreEqual("Defines", ((NameXPxg)ptgs[0]).SheetName); Assert.AreEqual("NR_To_A1", ((NameXPxg)ptgs[0]).NameName); Assert.AreEqual("[1]Defines!NR_To_A1", ((NameXPxg)ptgs[0]).ToFormulaString()); // Reference to a global named range in a different workbook ptgs = Parse(fpb, "[1]!NR_Global_B2"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual(typeof(NameXPxg), ptgs[0].GetType()); Assert.AreEqual(1, ((NameXPxg)ptgs[0]).ExternalWorkbookNumber); Assert.AreEqual(null, ((NameXPxg)ptgs[0]).SheetName); Assert.AreEqual("NR_Global_B2", ((NameXPxg)ptgs[0]).NameName); Assert.AreEqual("[1]!NR_Global_B2", ((NameXPxg)ptgs[0]).ToFormulaString()); }
public void TestBuiltInFormulas() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb); Ptg[] ptgs; ptgs = Parse(fpb, "LOG10"); Assert.AreEqual(1, ptgs.Length); Assert.IsTrue((ptgs[0] is RefPtg)); ptgs = Parse(fpb, "LOG10(100)"); Assert.AreEqual(2, ptgs.Length); Assert.IsTrue(ptgs[0] is IntPtg); Assert.IsTrue(ptgs[1] is FuncPtg); }
public void FormaulReferncesSameWorkbook() { // Use a test file with "other workbook" style references // to itself XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("56737.xlsx"); XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb); Ptg[] ptgs; // Reference to a named range in our own workbook, as if it // were defined in a different workbook ptgs = Parse(fpb, "[0]!NR_Global_B2"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual(typeof(NameXPxg), ptgs[0].GetType()); Assert.AreEqual(0, ((NameXPxg)ptgs[0]).ExternalWorkbookNumber); Assert.AreEqual(null, ((NameXPxg)ptgs[0]).SheetName); Assert.AreEqual("NR_Global_B2", ((NameXPxg)ptgs[0]).NameName); Assert.AreEqual("[0]!NR_Global_B2", ((NameXPxg)ptgs[0]).ToFormulaString()); }
public void FormulaReferencesOtherSheets() { // Use a test file with the named ranges in place XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("56737.xlsx"); XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb); Ptg[] ptgs; // Reference to a single cell in a different sheet ptgs = Parse(fpb, "Uses!A1"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual(typeof(Ref3DPxg), ptgs[0].GetType()); Assert.AreEqual(-1, ((Ref3DPxg)ptgs[0]).ExternalWorkbookNumber); Assert.AreEqual("A1", ((Ref3DPxg)ptgs[0]).Format2DRefAsString()); Assert.AreEqual("Uses!A1", ((Ref3DPxg)ptgs[0]).ToFormulaString()); // Reference to a single cell in a different sheet, which needs quoting ptgs = Parse(fpb, "'Testing 47100'!A1"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual(typeof(Ref3DPxg), ptgs[0].GetType()); Assert.AreEqual(-1, ((Ref3DPxg)ptgs[0]).ExternalWorkbookNumber); Assert.AreEqual("Testing 47100", ((Ref3DPxg)ptgs[0]).SheetName); Assert.AreEqual("A1", ((Ref3DPxg)ptgs[0]).Format2DRefAsString()); Assert.AreEqual("'Testing 47100'!A1", ((Ref3DPxg)ptgs[0]).ToFormulaString()); // Reference to a sheet scoped named range from another sheet ptgs = Parse(fpb, "Defines!NR_To_A1"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual(typeof(NameXPxg), ptgs[0].GetType()); Assert.AreEqual(-1, ((NameXPxg)ptgs[0]).ExternalWorkbookNumber); Assert.AreEqual("Defines", ((NameXPxg)ptgs[0]).SheetName); Assert.AreEqual("NR_To_A1", ((NameXPxg)ptgs[0]).NameName); Assert.AreEqual("Defines!NR_To_A1", ((NameXPxg)ptgs[0]).ToFormulaString()); // Reference to a workbook scoped named range ptgs = Parse(fpb, "NR_Global_B2"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual(typeof(NamePtg), ptgs[0].GetType()); Assert.AreEqual("NR_Global_B2", ((NamePtg)ptgs[0]).ToFormulaString(fpb)); }
public void BasicParse() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb); Ptg[] ptgs; ptgs = Parse(fpb, "ABC10"); Assert.AreEqual(1, ptgs.Length); Assert.IsTrue(ptgs[0] is RefPtg); ptgs = Parse(fpb, "A500000"); Assert.AreEqual(1, ptgs.Length); Assert.IsTrue(ptgs[0] is RefPtg); ptgs = Parse(fpb, "ABC500000"); Assert.AreEqual(1, ptgs.Length); Assert.IsTrue(ptgs[0] is RefPtg); //highest allowed rows and column (XFD and 0x100000) ptgs = Parse(fpb, "XFD1048576"); Assert.AreEqual(1, ptgs.Length); Assert.IsTrue(ptgs[0] is RefPtg); //column greater than XFD try { ptgs = Parse(fpb, "XFE10"); Assert.Fail("expected exception"); } catch (FormulaParseException e) { Assert.AreEqual("Specified named range 'XFE10' does not exist in the current workbook.", e.Message); } //row greater than 0x100000 try { ptgs = Parse(fpb, "XFD1048577"); Assert.Fail("expected exception"); } catch (FormulaParseException e) { Assert.AreEqual("Specified named range 'XFD1048577' does not exist in the current workbook.", e.Message); } // Formula referencing one cell ptgs = Parse(fpb, "ISEVEN(A1)"); Assert.AreEqual(3, ptgs.Length); Assert.AreEqual(typeof(NameXPxg), ptgs[0].GetType()); Assert.AreEqual(typeof(RefPtg), ptgs[1].GetType()); Assert.AreEqual(typeof(FuncVarPtg), ptgs[2].GetType()); Assert.AreEqual("ISEVEN", ptgs[0].ToFormulaString()); Assert.AreEqual("A1", ptgs[1].ToFormulaString()); Assert.AreEqual("#external#", ptgs[2].ToFormulaString()); // Formula referencing an area ptgs = Parse(fpb, "SUM(A1:B3)"); Assert.AreEqual(2, ptgs.Length); Assert.AreEqual(typeof(AreaPtg), ptgs[0].GetType()); Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType()); Assert.AreEqual("A1:B3", ptgs[0].ToFormulaString()); Assert.AreEqual("SUM", ptgs[1].ToFormulaString()); // Formula referencing one cell in a different sheet ptgs = Parse(fpb, "SUM(Sheet1!A1)"); Assert.AreEqual(2, ptgs.Length); Assert.AreEqual(typeof(Ref3DPxg), ptgs[0].GetType()); Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType()); Assert.AreEqual("Sheet1!A1", ptgs[0].ToFormulaString()); Assert.AreEqual("SUM", ptgs[1].ToFormulaString()); // Formula referencing an area in a different sheet ptgs = Parse(fpb, "SUM(Sheet1!A1:B3)"); Assert.AreEqual(2, ptgs.Length); Assert.AreEqual(typeof(Area3DPxg), ptgs[0].GetType()); Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType()); Assert.AreEqual("Sheet1!A1:B3", ptgs[0].ToFormulaString()); Assert.AreEqual("SUM", ptgs[1].ToFormulaString()); }
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); } }
private XSSFFormulaEvaluator(XSSFWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder) { _bookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.Create(workbook), stabilityClassifier, udfFinder); _book = workbook; }
/** * @param stabilityClassifier used to optimise caching performance. Pass <code>null</code> * for the (conservative) assumption that any cell may have its defInition Changed After * Evaluation begins. * @deprecated (Sep 2009) (reduce overloading) use {@link #Create(XSSFWorkbook, Npoi.Core.ss.formula.IStabilityClassifier, Npoi.Core.ss.formula.udf.UDFFinder)} */ public XSSFFormulaEvaluator(XSSFWorkbook workbook, IStabilityClassifier stabilityClassifier) { _bookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.Create(workbook), stabilityClassifier, null); _book = workbook; }