Examples with NameXPxg org.apache.poi.ss.formula.ptg.NameXPxg used on opensource projects

Example 1 with NameXPxg

use of org.apache.poi.ss.formula.ptg.NameXPxg in project poi by apache.

the class TestFormulaParser method testMacroFunction.

// copied from org.apache.poi.hssf.model.TestFormulaParser
@Test
public void testMacroFunction() throws Exception {
    // testNames.xlsm contains a VB function called 'myFunc'
    final 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);
        assertEquals(3, ptg.length);
        // the name gets encoded as the first operand on the stack
        NameXPxg tname = (NameXPxg) ptg[0];
        assertEquals("myFunc", tname.toFormulaString());
        // the function's arguments are pushed onto the stack from left-to-right as OperandPtgs
        StringPtg arg = (StringPtg) ptg[1];
        assertEquals("arg", arg.getValue());
        // 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];
        assertTrue(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
    /*
            final File fileIn = XSSFTestDataSamples.getSampleFile(testFile);
            final File reSavedFile = new File(fileIn.getParentFile(), fileIn.getName().replace(".xlsm", "-saved.xlsm"));
            final FileOutputStream fos = new FileOutputStream(reSavedFile);
            wb.write(fos);
            fos.close();
            */
    } finally {
        wb.close();
    }
}

Example 2 with NameXPxg

use of org.apache.poi.ss.formula.ptg.NameXPxg in project poi by apache.

the class FormulaParser method function.

/**
     * Note - Excel function names are 'case aware but not case sensitive'.  This method may end
     * up creating a defined name record in the workbook if the specified name is not an internal
     * Excel function, and has not been encountered before.
     * 
     * Side effect: creates workbook name if name is not recognized (name is probably a UDF)
     *
     * @param name case preserved function name (as it was entered/appeared in the formula).
     */
private ParseNode function(String name) {
    Ptg nameToken = null;
    if (!AbstractFunctionPtg.isBuiltInFunctionName(name)) {
        if (_book == null) {
            // Only test cases omit the book (expecting it not to be needed)
            throw new IllegalStateException("Need book to evaluate name '" + name + "'");
        }
        // Check to see if name is a named range in the workbook
        EvaluationName hName = _book.getName(name, _sheetIndex);
        if (hName != null) {
            if (!hName.isFunctionName()) {
                throw new FormulaParseException("Attempt to use name '" + name + "' as a function, but defined name in workbook does not refer to a function");
            }
            // calls to user-defined functions within the workbook
            // get a Name token which points to a defined name record
            nameToken = hName.createPtg();
        } else {
            // Check if name is an external names table
            nameToken = _book.getNameXPtg(name, null);
            if (nameToken == null) {
                // name is not an internal or external name
                if (log.check(POILogger.WARN)) {
                    log.log(POILogger.WARN, "FormulaParser.function: Name '" + name + "' is completely unknown in the current workbook.");
                }
                // name is probably the name of an unregistered User-Defined Function
                switch(_book.getSpreadsheetVersion()) {
                    case EXCEL97:
                        // HSSFWorkbooks require a name to be added to Workbook defined names table
                        addName(name);
                        hName = _book.getName(name, _sheetIndex);
                        nameToken = hName.createPtg();
                        break;
                    case EXCEL2007:
                        // XSSFWorkbooks store formula names as strings.
                        nameToken = new NameXPxg(name);
                        break;
                    default:
                        throw new IllegalStateException("Unexpected spreadsheet version: " + _book.getSpreadsheetVersion().name());
                }
            }
        }
    }
    Match('(');
    ParseNode[] args = Arguments();
    Match(')');
    return getFunction(name, nameToken, args);
}

Example 3 with NameXPxg

use of org.apache.poi.ss.formula.ptg.NameXPxg in project poi by apache.

the class BaseXSSFEvaluationWorkbook method getNameXPtg.

/**
     * Return an external name (named range, function, user-defined function) Pxg
     */
@Override
public NameXPxg getNameXPtg(String name, SheetIdentifier sheet) {
    // First, try to find it as a User Defined Function
    IndexedUDFFinder udfFinder = (IndexedUDFFinder) getUDFFinder();
    FreeRefFunction func = udfFinder.findFunction(name);
    if (func != null) {
        return new NameXPxg(null, name);
    }
    // Otherwise, try it as a named range
    if (sheet == null) {
        if (!_uBook.getNames(name).isEmpty()) {
            return new NameXPxg(null, name);
        }
        return null;
    }
    if (sheet._sheetIdentifier == null) {
        // Workbook + Named Range only
        int bookIndex = resolveBookIndex(sheet._bookName);
        return new NameXPxg(bookIndex, null, name);
    }
    // Use the sheetname and process
    String sheetName = sheet._sheetIdentifier.getName();
    if (sheet._bookName != null) {
        int bookIndex = resolveBookIndex(sheet._bookName);
        return new NameXPxg(bookIndex, sheetName, name);
    } else {
        return new NameXPxg(sheetName, name);
    }
}