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

Example 1 with StringPtg

use of org.apache.poi.ss.formula.ptg.StringPtg 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 StringPtg

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

the class TestFormulaParser method testMacroFunction.

@Test
public void testMacroFunction() throws IOException {
    // testNames.xls contains a VB function called 'myFunc'
    final String testFile = "testNames.xls";
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook(testFile);
    try {
        HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.create(wb);
        //Expected ptg stack: [NamePtg(myFunc), StringPtg(arg), (additional operands go here...), FunctionPtg(myFunc)]
        Ptg[] ptg = FormulaParser.parse("myFunc(\"arg\")", book, FormulaType.CELL, -1);
        assertEquals(3, ptg.length);
        // the name gets encoded as the first operand on the stack
        NamePtg tname = (NamePtg) ptg[0];
        assertEquals("myFunc", tname.toFormulaString(book));
        // 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
        //FuncVarPtg
        AbstractFunctionPtg tfunc = (AbstractFunctionPtg) ptg[2];
        assertTrue(tfunc.isExternalFunction());
        // confirm formula parsing is case-insensitive
        FormulaParser.parse("mYfUnC(\"arg\")", book, 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()", book, FormulaType.CELL, -1);
        FormulaParser.parse("myFunc(\"arg\", 0, TRUE)", book, 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\")", book, FormulaType.CELL, -1);
        // Verify that myFunc and yourFunc were successfully added to Workbook names
        HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb);
        try {
            // HSSFWorkbook/EXCEL97-specific side-effects user-defined function names must be added to Workbook's defined names in order to be saved.
            assertNotNull(wb2.getName("myFunc"));
            assertEqualsIgnoreCase("myFunc", wb2.getName("myFunc").getNameName());
            assertNotNull(wb2.getName("yourFunc"));
            assertEqualsIgnoreCase("yourFunc", wb2.getName("yourFunc").getNameName());
        // 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 = HSSFTestDataSamples.getSampleFile(testFile);
                final File reSavedFile = new File(fileIn.getParentFile(), fileIn.getName().replace(".xls", "-saved.xls"));
                FileOutputStream fos = new FileOutputStream(reSavedFile);
                wb2.write(fos);
                fos.close();
                */
        } finally {
            wb2.close();
        }
    } finally {
        wb.close();
    }
}

Example 3 with StringPtg

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

the class TestFormulaParser method confirmStringParse.

/**
     * To aid readability the parameters have been encoded with single quotes instead of double
     * quotes.  This method converts single quotes to double quotes before performing the parse
     * and result check.
     */
private static void confirmStringParse(String singleQuotedValue) {
    // formula: internal quotes become double double, surround with double quotes
    String formula = '"' + singleQuotedValue.replaceAll("'", "\"\"") + '"';
    String expectedValue = singleQuotedValue.replace('\'', '"');
    StringPtg sp = (StringPtg) parseSingleToken(formula, StringPtg.class);
    assertEquals(expectedValue, sp.getValue());
}

Example 4 with StringPtg

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

the class FormulaParser method parseNonRange.

/**
     * Parses simple factors that are not primitive ranges or range components
     * i.e. '!', ':'(and equiv '...') do not appear
     * Examples
     * <pre>
     *   my.named...range.
     *   foo.bar(123.456, "abc")
     *   123.456
     *   "abc"
     *   true
     * </pre>
     */
private ParseNode parseNonRange(int savePointer) {
    resetPointer(savePointer);
    if (Character.isDigit(look)) {
        return new ParseNode(parseNumber());
    }
    if (look == '"') {
        return new ParseNode(new StringPtg(parseStringLiteral()));
    }
    // from now on we can only be dealing with non-quoted identifiers
    // which will either be named ranges or functions
    String name = parseAsName();
    if (look == '(') {
        return function(name);
    }
    if (look == '[') {
        return parseStructuredReference(name);
    }
    if (name.equalsIgnoreCase("TRUE") || name.equalsIgnoreCase("FALSE")) {
        return new ParseNode(BoolPtg.valueOf(name.equalsIgnoreCase("TRUE")));
    }
    if (_book == null) {
        // Only test cases omit the book (expecting it not to be needed)
        throw new IllegalStateException("Need book to evaluate name '" + name + "'");
    }
    EvaluationName evalName = _book.getName(name, _sheetIndex);
    if (evalName == null) {
        throw new FormulaParseException("Specified named range '" + name + "' does not exist in the current workbook.");
    }
    if (evalName.isRange()) {
        return new ParseNode(evalName.createPtg());
    }
    // TODO - what about NameX ?
    throw new FormulaParseException("Specified name '" + name + "' is not a range as expected.");
}

Example 5 with StringPtg

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

the class FormulaParser method parseSimpleFactor.

/**
     * factors (without ^ or % )
     */
private ParseNode parseSimpleFactor() {
    SkipWhite();
    switch(look) {
        case '#':
            return new ParseNode(ErrPtg.valueOf(parseErrorLiteral()));
        case '-':
            Match('-');
            return parseUnary(false);
        case '+':
            Match('+');
            return parseUnary(true);
        case '(':
            Match('(');
            ParseNode inside = unionExpression();
            Match(')');
            return new ParseNode(ParenthesisPtg.instance, inside);
        case '"':
            return new ParseNode(new StringPtg(parseStringLiteral()));
        case '{':
            Match('{');
            ParseNode arrayNode = parseArray();
            Match('}');
            return arrayNode;
    }
    // see https://support.office.com/en-us/article/Define-and-use-names-in-formulas-4d0f13ac-53b7-422e-afd2-abd7ff379c64?ui=en-US&rs=en-US&ad=US#bmsyntax_rules_for_names
    if (IsAlpha(look) || Character.isDigit(look) || look == '\'' || look == '[' || look == '_' || look == '\\') {
        return parseRangeExpression();
    }
    if (look == '.') {
        return new ParseNode(parseNumber());
    }
    throw expected("cell ref or constant literal");
}