private ValueEval GetLocalNameXEval(NameXPxg nameXPxg) { // Look up the sheet, if present int sIdx = -1; if (nameXPxg.SheetName != null) { sIdx = _workbook.GetSheetIndex(nameXPxg.SheetName); } // Is it a name or a function? string name = nameXPxg.NameName; IEvaluationName evalName = _workbook.GetName(name, sIdx); if (evalName != null) { // Process it as a name return(new ExternalNameEval(evalName)); } else { // Must be an external function return(new FunctionNameEval(name)); } }
// copied from org.apache.poi.hssf.model.TestFormulaParser public void TestMacroFunction() { // testNames.xlsm contains a VB function called 'myFunc' String testFile = "testNames.xlsm"; XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook(testFile); try { XSSFEvaluationWorkbook workbook = XSSFEvaluationWorkbook.Create(wb); //Expected ptg stack: [NamePtg(myFunc), StringPtg(arg), (additional operands would go here...), FunctionPtg(myFunc)] Ptg[] ptg = FormulaParser.Parse("myFunc(\"arg\")", workbook, FormulaType.Cell, -1); Assert.AreEqual(3, ptg.Length); // the name gets encoded as the first operand on the stack NameXPxg tname = (NameXPxg)ptg[0]; Assert.AreEqual("myFunc", tname.ToFormulaString()); // the function's arguments are pushed onto the stack from left-to-right as OperandPtgs StringPtg arg = (StringPtg)ptg[1]; Assert.AreEqual("arg", arg.Value); // The external FunctionPtg is the last Ptg added to the stack // During formula evaluation, this Ptg pops off the the appropriate number of // arguments (getNumberOfOperands()) and pushes the result on the stack AbstractFunctionPtg tfunc = (AbstractFunctionPtg)ptg[2]; Assert.IsTrue(tfunc.IsExternalFunction); // confirm formula parsing is case-insensitive FormulaParser.Parse("mYfUnC(\"arg\")", workbook, FormulaType.Cell, -1); // confirm formula parsing doesn't care about argument count or type // this should only throw an error when evaluating the formula. FormulaParser.Parse("myFunc()", workbook, FormulaType.Cell, -1); FormulaParser.Parse("myFunc(\"arg\", 0, TRUE)", workbook, FormulaType.Cell, -1); // A completely unknown formula name (not saved in workbook) should still be parseable and renderable // but will throw an NotImplementedFunctionException or return a #NAME? error value if evaluated. FormulaParser.Parse("yourFunc(\"arg\")", workbook, FormulaType.Cell, -1); // Make sure workbook can be written and read XSSFTestDataSamples.WriteOutAndReadBack(wb).Close(); // Manually check to make sure file isn't corrupted // TODO: develop a process for occasionally manually reviewing workbooks // to verify workbooks are not corrupted /* * FileInfo fileIn = XSSFTestDataSamples.GetSampleFile(testFile); * FileInfo reSavedFile = new FileInfo(fileIn.FullName.Replace(".xlsm", "-saved.xlsm")); * FileStream fos = new FileStream(reSavedFile.FullName, FileMode.Create, FileAccess.ReadWrite); * wb.Write(fos); * fos.Close(); */ } finally { wb.Close(); } }
public ValueEval GetNameXEval(NameXPxg nameXPxg) { ExternalSheet externSheet = _workbook.GetExternalSheet(nameXPxg.SheetName, null, nameXPxg.ExternalWorkbookNumber); if (externSheet == null || externSheet.WorkbookName == null) { // External reference to our own workbook's name return(GetLocalNameXEval(nameXPxg)); } // Look it up for the external workbook string workbookName = externSheet.WorkbookName; ExternalName externName = _workbook.GetExternalName( nameXPxg.NameName, nameXPxg.SheetName, nameXPxg.ExternalWorkbookNumber ); return(GetExternalNameXEval(externName, workbookName)); }