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

Example 1 with FuncVarPtg

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

the class SheetRefEvaluator method isSubTotal.

/**
     * @return  whether cell at rowIndex and columnIndex is a subtotal
     * @see org.apache.poi.ss.formula.functions.Subtotal
     */
public boolean isSubTotal(int rowIndex, int columnIndex) {
    boolean subtotal = false;
    EvaluationCell cell = getSheet().getCell(rowIndex, columnIndex);
    if (cell != null && cell.getCellTypeEnum() == CellType.FORMULA) {
        EvaluationWorkbook wb = _bookEvaluator.getWorkbook();
        for (Ptg ptg : wb.getFormulaTokens(cell)) {
            if (ptg instanceof FuncVarPtg) {
                FuncVarPtg f = (FuncVarPtg) ptg;
                if ("SUBTOTAL".equals(f.getName())) {
                    subtotal = true;
                    break;
                }
            }
        }
    }
    return subtotal;
}

Example 2 with FuncVarPtg

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

the class TestFormulaParser method test57196_Formula.

@Test
public void test57196_Formula() throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    Ptg[] ptgs = HSSFFormulaParser.parse("DEC2HEX(HEX2DEC(O8)-O2+D2)", wb, FormulaType.CELL, -1);
    assertNotNull("Ptg array should not be null", ptgs);
    confirmTokenClasses(ptgs, // ??
    NameXPtg.class, // ??
    NameXPtg.class, // O8
    RefPtg.class, // HEX2DEC
    FuncVarPtg.class, // O2
    RefPtg.class, SubtractPtg.class, // D2
    RefPtg.class, AddPtg.class, // DEC2HEX
    FuncVarPtg.class);
    RefPtg o8 = (RefPtg) ptgs[2];
    FuncVarPtg hex2Dec = (FuncVarPtg) ptgs[3];
    RefPtg o2 = (RefPtg) ptgs[4];
    RefPtg d2 = (RefPtg) ptgs[6];
    FuncVarPtg dec2Hex = (FuncVarPtg) ptgs[8];
    assertEquals("O8", o8.toFormulaString());
    assertEquals(255, hex2Dec.getFunctionIndex());
    //assertEquals("", hex2Dec.toString());
    assertEquals("O2", o2.toFormulaString());
    assertEquals("D2", d2.toFormulaString());
    assertEquals(255, dec2Hex.getFunctionIndex());
    wb.close();
}

Example 3 with FuncVarPtg

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

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

the class TestOperandClassTransformer method testComplexIRR_bug45041.

public void testComplexIRR_bug45041() {
    String formula = "(1+IRR(SUMIF(A:A,ROW(INDIRECT(MIN(A:A)&\":\"&MAX(A:A))),B:B),0))^365-1";
    Ptg[] ptgs = parseFormula(formula);
    FuncVarPtg rowFunc = (FuncVarPtg) ptgs[10];
    FuncVarPtg sumifFunc = (FuncVarPtg) ptgs[12];
    assertEquals("ROW", rowFunc.getName());
    assertEquals("SUMIF", sumifFunc.getName());
    if (rowFunc.getPtgClass() == Ptg.CLASS_VALUE || sumifFunc.getPtgClass() == Ptg.CLASS_VALUE) {
        throw new AssertionFailedError("Identified bug 45041");
    }
    confirmTokenClass(ptgs, 1, Ptg.CLASS_REF);
    confirmTokenClass(ptgs, 2, Ptg.CLASS_REF);
    confirmFuncClass(ptgs, 3, "MIN", Ptg.CLASS_VALUE);
    confirmTokenClass(ptgs, 6, Ptg.CLASS_REF);
    confirmFuncClass(ptgs, 7, "MAX", Ptg.CLASS_VALUE);
    confirmFuncClass(ptgs, 9, "INDIRECT", Ptg.CLASS_REF);
    confirmFuncClass(ptgs, 10, "ROW", Ptg.CLASS_ARRAY);
    confirmTokenClass(ptgs, 11, Ptg.CLASS_REF);
    confirmFuncClass(ptgs, 12, "SUMIF", Ptg.CLASS_ARRAY);
    confirmFuncClass(ptgs, 14, "IRR", Ptg.CLASS_VALUE);
}

Example 5 with FuncVarPtg

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

the class OperandClassTransformer method transformNode.

/**
	 * @param callerForceArrayFlag <code>true</code> if one of the current node's parents is a
	 * function Ptg which has been changed from default 'V' to 'A' type (due to requirements on
	 * the function return value).
	 */
private void transformNode(ParseNode node, byte desiredOperandClass, boolean callerForceArrayFlag) {
    Ptg token = node.getToken();
    ParseNode[] children = node.getChildren();
    boolean isSimpleValueFunc = isSimpleValueFunction(token);
    if (isSimpleValueFunc) {
        boolean localForceArray = desiredOperandClass == Ptg.CLASS_ARRAY;
        for (int i = 0; i < children.length; i++) {
            transformNode(children[i], desiredOperandClass, localForceArray);
        }
        setSimpleValueFuncClass((AbstractFunctionPtg) token, desiredOperandClass, callerForceArrayFlag);
        return;
    }
    if (isSingleArgSum(token)) {
        // Need to process the argument of SUM with transformFunctionNode below
        // so make a dummy FuncVarPtg for that call.
        token = FuncVarPtg.SUM;
    // Note - the tAttrSum token (node.getToken()) is a base
    // token so does not need to have its operand class set
    }
    if (token instanceof ValueOperatorPtg || token instanceof ControlPtg || token instanceof MemFuncPtg || token instanceof MemAreaPtg || token instanceof UnionPtg || token instanceof IntersectionPtg) {
        // Value Operator Ptgs and Control are base tokens, so token will be unchanged
        // but any child nodes are processed according to desiredOperandClass and callerForceArrayFlag
        // As per OOO documentation Sec 3.2.4 "Token Class Transformation", "Step 1"
        // All direct operands of value operators that are initially 'R' type will
        // be converted to 'V' type.
        byte localDesiredOperandClass = desiredOperandClass == Ptg.CLASS_REF ? Ptg.CLASS_VALUE : desiredOperandClass;
        for (int i = 0; i < children.length; i++) {
            transformNode(children[i], localDesiredOperandClass, callerForceArrayFlag);
        }
        return;
    }
    if (token instanceof AbstractFunctionPtg) {
        transformFunctionNode((AbstractFunctionPtg) token, children, desiredOperandClass, callerForceArrayFlag);
        return;
    }
    if (children.length > 0) {
        if (token == RangePtg.instance) {
            // TODO is any token transformation required under the various ref operators?
            return;
        }
        throw new IllegalStateException("Node should not have any children");
    }
    if (token.isBaseToken()) {
        // nothing to do
        return;
    }
    token.setClass(transformClass(token.getPtgClass(), desiredOperandClass, callerForceArrayFlag));
}