Search in sources :

Example 1 with MemFuncPtg

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

the class TestFormulaParser method testComplexExplicitRangeEncodings.

/**
     * Checks that the area-ref and explicit range operators get the right associativity
     * and that the {@link MemFuncPtg} / {@link MemAreaPtg} is added correctly
     */
@Test
public void testComplexExplicitRangeEncodings() {
    Ptg[] ptgs;
    ptgs = parseFormula("SUM(OFFSET(A1,0,0):B2:C3:D4:E5:OFFSET(F6,1,1):G7)");
    confirmTokenClasses(ptgs, // len 57
    MemFuncPtg.class, // [A1]
    RefPtg.class, // [0]
    IntPtg.class, // [0]
    IntPtg.class, // [OFFSET nArgs=3]
    FuncVarPtg.class, // [B2:C3]
    AreaPtg.class, RangePtg.class, // [D4:E5]
    AreaPtg.class, RangePtg.class, // [F6]
    RefPtg.class, // [1]
    IntPtg.class, // [1]
    IntPtg.class, // [OFFSET nArgs=3]
    FuncVarPtg.class, RangePtg.class, // [G7]
    RefPtg.class, RangePtg.class, // [sum ]
    AttrPtg.class);
    MemFuncPtg mf = (MemFuncPtg) ptgs[0];
    assertEquals(57, mf.getLenRefSubexpression());
    assertEquals("D4:E5", ((AreaPtgBase) ptgs[7]).toFormulaString());
    assertTrue(((AttrPtg) ptgs[16]).isSum());
    ptgs = parseFormula("SUM(A1:B2:C3:D4)");
    confirmTokenClasses(ptgs, // len 19
    MemAreaPtg.class, // [A1:B2]
    AreaPtg.class, // [C3:D4]
    AreaPtg.class, RangePtg.class, // [sum ]
    AttrPtg.class);
    MemAreaPtg ma = (MemAreaPtg) ptgs[0];
    assertEquals(19, ma.getLenRefSubexpression());
}
Also used : MemAreaPtg(org.apache.poi.ss.formula.ptg.MemAreaPtg) NumberPtg(org.apache.poi.ss.formula.ptg.NumberPtg) ArrayPtg(org.apache.poi.ss.formula.ptg.ArrayPtg) AttrPtg(org.apache.poi.ss.formula.ptg.AttrPtg) PercentPtg(org.apache.poi.ss.formula.ptg.PercentPtg) RangePtg(org.apache.poi.ss.formula.ptg.RangePtg) AddPtg(org.apache.poi.ss.formula.ptg.AddPtg) EqualPtg(org.apache.poi.ss.formula.ptg.EqualPtg) UnaryMinusPtg(org.apache.poi.ss.formula.ptg.UnaryMinusPtg) NameXPtg(org.apache.poi.ss.formula.ptg.NameXPtg) RefPtg(org.apache.poi.ss.formula.ptg.RefPtg) DividePtg(org.apache.poi.ss.formula.ptg.DividePtg) GreaterThanPtg(org.apache.poi.ss.formula.ptg.GreaterThanPtg) MultiplyPtg(org.apache.poi.ss.formula.ptg.MultiplyPtg) Ref3DPtg(org.apache.poi.ss.formula.ptg.Ref3DPtg) StringPtg(org.apache.poi.ss.formula.ptg.StringPtg) ErrPtg(org.apache.poi.ss.formula.ptg.ErrPtg) Ptg(org.apache.poi.ss.formula.ptg.Ptg) Area3DPtg(org.apache.poi.ss.formula.ptg.Area3DPtg) NamePtg(org.apache.poi.ss.formula.ptg.NamePtg) MemAreaPtg(org.apache.poi.ss.formula.ptg.MemAreaPtg) ConcatPtg(org.apache.poi.ss.formula.ptg.ConcatPtg) UnaryPlusPtg(org.apache.poi.ss.formula.ptg.UnaryPlusPtg) BoolPtg(org.apache.poi.ss.formula.ptg.BoolPtg) IntersectionPtg(org.apache.poi.ss.formula.ptg.IntersectionPtg) AbstractFunctionPtg(org.apache.poi.ss.formula.ptg.AbstractFunctionPtg) IntPtg(org.apache.poi.ss.formula.ptg.IntPtg) UnionPtg(org.apache.poi.ss.formula.ptg.UnionPtg) FuncVarPtg(org.apache.poi.ss.formula.ptg.FuncVarPtg) SubtractPtg(org.apache.poi.ss.formula.ptg.SubtractPtg) FuncPtg(org.apache.poi.ss.formula.ptg.FuncPtg) MissingArgPtg(org.apache.poi.ss.formula.ptg.MissingArgPtg) MemFuncPtg(org.apache.poi.ss.formula.ptg.MemFuncPtg) PowerPtg(org.apache.poi.ss.formula.ptg.PowerPtg) AreaPtg(org.apache.poi.ss.formula.ptg.AreaPtg) ParenthesisPtg(org.apache.poi.ss.formula.ptg.ParenthesisPtg) MemFuncPtg(org.apache.poi.ss.formula.ptg.MemFuncPtg) Test(org.junit.Test)

Example 2 with MemFuncPtg

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

the class TestAreaReference method testDiscontinousReference.

public void testDiscontinousReference() throws Exception {
    InputStream is = HSSFTestDataSamples.openSampleFileStream("44167.xls");
    HSSFWorkbook wb = new HSSFWorkbook(is);
    InternalWorkbook workbook = TestHSSFWorkbook.getInternalWorkbook(wb);
    HSSFEvaluationWorkbook eb = HSSFEvaluationWorkbook.create(wb);
    assertEquals(1, wb.getNumberOfNames());
    String sheetName = "Tabelle1";
    String rawRefA = "$C$10:$C$14";
    String rawRefB = "$C$16:$C$18";
    String refA = sheetName + "!" + rawRefA;
    String refB = sheetName + "!" + rawRefB;
    String ref = refA + "," + refB;
    // Check the low level record
    NameRecord nr = workbook.getNameRecord(0);
    assertNotNull(nr);
    assertEquals("test", nr.getNameText());
    Ptg[] def = nr.getNameDefinition();
    assertEquals(4, def.length);
    MemFuncPtg ptgA = (MemFuncPtg) def[0];
    Area3DPtg ptgB = (Area3DPtg) def[1];
    Area3DPtg ptgC = (Area3DPtg) def[2];
    UnionPtg ptgD = (UnionPtg) def[3];
    assertEquals("", ptgA.toFormulaString());
    assertEquals(refA, ptgB.toFormulaString(eb));
    assertEquals(refB, ptgC.toFormulaString(eb));
    assertEquals(",", ptgD.toFormulaString());
    assertEquals(ref, HSSFFormulaParser.toFormulaString(wb, nr.getNameDefinition()));
    // Check the high level definition
    int idx = wb.getNameIndex("test");
    assertEquals(0, idx);
    HSSFName aNamedCell = wb.getNameAt(idx);
    // Should have 2 references
    assertEquals(ref, aNamedCell.getRefersToFormula());
    // Check the parsing of the reference into cells
    assertFalse(AreaReference.isContiguous(aNamedCell.getRefersToFormula()));
    AreaReference[] arefs = AreaReference.generateContiguous(aNamedCell.getRefersToFormula());
    assertEquals(2, arefs.length);
    assertEquals(refA, arefs[0].formatAsString());
    assertEquals(refB, arefs[1].formatAsString());
    for (AreaReference ar : arefs) {
        confirmResolveCellRef(wb, ar.getFirstCell());
        confirmResolveCellRef(wb, ar.getLastCell());
    }
}
Also used : Ptg(org.apache.poi.ss.formula.ptg.Ptg) UnionPtg(org.apache.poi.ss.formula.ptg.UnionPtg) Area3DPtg(org.apache.poi.ss.formula.ptg.Area3DPtg) MemFuncPtg(org.apache.poi.ss.formula.ptg.MemFuncPtg) AreaReference(org.apache.poi.ss.util.AreaReference) MemFuncPtg(org.apache.poi.ss.formula.ptg.MemFuncPtg) InputStream(java.io.InputStream) TestHSSFWorkbook(org.apache.poi.hssf.usermodel.TestHSSFWorkbook) HSSFWorkbook(org.apache.poi.hssf.usermodel.HSSFWorkbook) HSSFEvaluationWorkbook(org.apache.poi.hssf.usermodel.HSSFEvaluationWorkbook) InternalWorkbook(org.apache.poi.hssf.model.InternalWorkbook) HSSFName(org.apache.poi.hssf.usermodel.HSSFName) NameRecord(org.apache.poi.hssf.record.NameRecord) Area3DPtg(org.apache.poi.ss.formula.ptg.Area3DPtg) UnionPtg(org.apache.poi.ss.formula.ptg.UnionPtg)

Example 3 with MemFuncPtg

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

the class FormulaRenderer method toFormulaString.

/**
     * Static method to convert an array of {@link Ptg}s in RPN order
     * to a human readable string format in infix mode.
     * @param book  used for defined names and 3D references
     * @param ptgs  must not be <code>null</code>
     * @return a human readable String
     */
public static String toFormulaString(FormulaRenderingWorkbook book, Ptg[] ptgs) {
    if (ptgs == null || ptgs.length == 0) {
        throw new IllegalArgumentException("ptgs must not be null");
    }
    Stack<String> stack = new Stack<String>();
    for (Ptg ptg : ptgs) {
        // TODO - what about MemNoMemPtg?
        if (ptg instanceof MemAreaPtg || ptg instanceof MemFuncPtg || ptg instanceof MemErrPtg) {
            // TODO - put comment and throw exception in toFormulaString() of these classes
            continue;
        }
        if (ptg instanceof ParenthesisPtg) {
            String contents = stack.pop();
            stack.push("(" + contents + ")");
            continue;
        }
        if (ptg instanceof AttrPtg) {
            AttrPtg attrPtg = ((AttrPtg) ptg);
            if (attrPtg.isOptimizedIf() || attrPtg.isOptimizedChoose() || attrPtg.isSkip()) {
                continue;
            }
            if (attrPtg.isSpace()) {
                // POI currently doesn't render spaces in formulas
                continue;
            // but if it ever did, care must be taken:
            // tAttrSpace comes *before* the operand it applies to, which may be consistent
            // with how the formula text appears but is against the RPN ordering assumed here
            }
            if (attrPtg.isSemiVolatile()) {
                // similar to tAttrSpace - RPN is violated
                continue;
            }
            if (attrPtg.isSum()) {
                String[] operands = getOperands(stack, attrPtg.getNumberOfOperands());
                stack.push(attrPtg.toFormulaString(operands));
                continue;
            }
            throw new RuntimeException("Unexpected tAttr: " + attrPtg);
        }
        if (ptg instanceof WorkbookDependentFormula) {
            WorkbookDependentFormula optg = (WorkbookDependentFormula) ptg;
            stack.push(optg.toFormulaString(book));
            continue;
        }
        if (!(ptg instanceof OperationPtg)) {
            stack.push(ptg.toFormulaString());
            continue;
        }
        OperationPtg o = (OperationPtg) ptg;
        String[] operands = getOperands(stack, o.getNumberOfOperands());
        stack.push(o.toFormulaString(operands));
    }
    if (stack.isEmpty()) {
        // stack.push(). So this is either an internal error or impossible.
        throw new IllegalStateException("Stack underflow");
    }
    String result = stack.pop();
    if (!stack.isEmpty()) {
        // put anything on the stack
        throw new IllegalStateException("too much stuff left on the stack");
    }
    return result;
}
Also used : AttrPtg(org.apache.poi.ss.formula.ptg.AttrPtg) Ptg(org.apache.poi.ss.formula.ptg.Ptg) MemAreaPtg(org.apache.poi.ss.formula.ptg.MemAreaPtg) MemErrPtg(org.apache.poi.ss.formula.ptg.MemErrPtg) MemFuncPtg(org.apache.poi.ss.formula.ptg.MemFuncPtg) OperationPtg(org.apache.poi.ss.formula.ptg.OperationPtg) ParenthesisPtg(org.apache.poi.ss.formula.ptg.ParenthesisPtg) MemFuncPtg(org.apache.poi.ss.formula.ptg.MemFuncPtg) ParenthesisPtg(org.apache.poi.ss.formula.ptg.ParenthesisPtg) OperationPtg(org.apache.poi.ss.formula.ptg.OperationPtg) AttrPtg(org.apache.poi.ss.formula.ptg.AttrPtg) Stack(java.util.Stack) MemAreaPtg(org.apache.poi.ss.formula.ptg.MemAreaPtg) MemErrPtg(org.apache.poi.ss.formula.ptg.MemErrPtg)

Example 4 with MemFuncPtg

use of org.apache.poi.ss.formula.ptg.MemFuncPtg 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));
}
Also used : AttrPtg(org.apache.poi.ss.formula.ptg.AttrPtg) Ptg(org.apache.poi.ss.formula.ptg.Ptg) UnionPtg(org.apache.poi.ss.formula.ptg.UnionPtg) ValueOperatorPtg(org.apache.poi.ss.formula.ptg.ValueOperatorPtg) FuncVarPtg(org.apache.poi.ss.formula.ptg.FuncVarPtg) RangePtg(org.apache.poi.ss.formula.ptg.RangePtg) MemAreaPtg(org.apache.poi.ss.formula.ptg.MemAreaPtg) ControlPtg(org.apache.poi.ss.formula.ptg.ControlPtg) IntersectionPtg(org.apache.poi.ss.formula.ptg.IntersectionPtg) AbstractFunctionPtg(org.apache.poi.ss.formula.ptg.AbstractFunctionPtg) MemFuncPtg(org.apache.poi.ss.formula.ptg.MemFuncPtg) MemFuncPtg(org.apache.poi.ss.formula.ptg.MemFuncPtg) ValueOperatorPtg(org.apache.poi.ss.formula.ptg.ValueOperatorPtg) ControlPtg(org.apache.poi.ss.formula.ptg.ControlPtg) MemAreaPtg(org.apache.poi.ss.formula.ptg.MemAreaPtg) IntersectionPtg(org.apache.poi.ss.formula.ptg.IntersectionPtg) UnionPtg(org.apache.poi.ss.formula.ptg.UnionPtg) AbstractFunctionPtg(org.apache.poi.ss.formula.ptg.AbstractFunctionPtg)

Example 5 with MemFuncPtg

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

the class TestFormulaParser method testExplicitRangeWithTwoSheetNames.

@Test
public void testExplicitRangeWithTwoSheetNames() throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    wb.createSheet("Sheet1");
    Ptg[] ptgs = HSSFFormulaParser.parse("Sheet1!F1:Sheet1!G2", wb);
    confirmTokenClasses(ptgs, MemFuncPtg.class, Ref3DPtg.class, Ref3DPtg.class, RangePtg.class);
    MemFuncPtg mf;
    mf = (MemFuncPtg) ptgs[0];
    assertEquals(15, mf.getLenRefSubexpression());
    wb.close();
}
Also used : NumberPtg(org.apache.poi.ss.formula.ptg.NumberPtg) ArrayPtg(org.apache.poi.ss.formula.ptg.ArrayPtg) AttrPtg(org.apache.poi.ss.formula.ptg.AttrPtg) PercentPtg(org.apache.poi.ss.formula.ptg.PercentPtg) RangePtg(org.apache.poi.ss.formula.ptg.RangePtg) AddPtg(org.apache.poi.ss.formula.ptg.AddPtg) EqualPtg(org.apache.poi.ss.formula.ptg.EqualPtg) UnaryMinusPtg(org.apache.poi.ss.formula.ptg.UnaryMinusPtg) NameXPtg(org.apache.poi.ss.formula.ptg.NameXPtg) RefPtg(org.apache.poi.ss.formula.ptg.RefPtg) DividePtg(org.apache.poi.ss.formula.ptg.DividePtg) GreaterThanPtg(org.apache.poi.ss.formula.ptg.GreaterThanPtg) MultiplyPtg(org.apache.poi.ss.formula.ptg.MultiplyPtg) Ref3DPtg(org.apache.poi.ss.formula.ptg.Ref3DPtg) StringPtg(org.apache.poi.ss.formula.ptg.StringPtg) ErrPtg(org.apache.poi.ss.formula.ptg.ErrPtg) Ptg(org.apache.poi.ss.formula.ptg.Ptg) Area3DPtg(org.apache.poi.ss.formula.ptg.Area3DPtg) NamePtg(org.apache.poi.ss.formula.ptg.NamePtg) MemAreaPtg(org.apache.poi.ss.formula.ptg.MemAreaPtg) ConcatPtg(org.apache.poi.ss.formula.ptg.ConcatPtg) UnaryPlusPtg(org.apache.poi.ss.formula.ptg.UnaryPlusPtg) BoolPtg(org.apache.poi.ss.formula.ptg.BoolPtg) IntersectionPtg(org.apache.poi.ss.formula.ptg.IntersectionPtg) AbstractFunctionPtg(org.apache.poi.ss.formula.ptg.AbstractFunctionPtg) IntPtg(org.apache.poi.ss.formula.ptg.IntPtg) UnionPtg(org.apache.poi.ss.formula.ptg.UnionPtg) FuncVarPtg(org.apache.poi.ss.formula.ptg.FuncVarPtg) SubtractPtg(org.apache.poi.ss.formula.ptg.SubtractPtg) FuncPtg(org.apache.poi.ss.formula.ptg.FuncPtg) MissingArgPtg(org.apache.poi.ss.formula.ptg.MissingArgPtg) MemFuncPtg(org.apache.poi.ss.formula.ptg.MemFuncPtg) PowerPtg(org.apache.poi.ss.formula.ptg.PowerPtg) AreaPtg(org.apache.poi.ss.formula.ptg.AreaPtg) ParenthesisPtg(org.apache.poi.ss.formula.ptg.ParenthesisPtg) MemFuncPtg(org.apache.poi.ss.formula.ptg.MemFuncPtg) HSSFWorkbook(org.apache.poi.hssf.usermodel.HSSFWorkbook) Test(org.junit.Test)

Aggregations

MemFuncPtg (org.apache.poi.ss.formula.ptg.MemFuncPtg)9 Ptg (org.apache.poi.ss.formula.ptg.Ptg)9 UnionPtg (org.apache.poi.ss.formula.ptg.UnionPtg)8 Area3DPtg (org.apache.poi.ss.formula.ptg.Area3DPtg)7 AttrPtg (org.apache.poi.ss.formula.ptg.AttrPtg)7 MemAreaPtg (org.apache.poi.ss.formula.ptg.MemAreaPtg)7 AbstractFunctionPtg (org.apache.poi.ss.formula.ptg.AbstractFunctionPtg)6 FuncVarPtg (org.apache.poi.ss.formula.ptg.FuncVarPtg)6 IntersectionPtg (org.apache.poi.ss.formula.ptg.IntersectionPtg)6 ParenthesisPtg (org.apache.poi.ss.formula.ptg.ParenthesisPtg)6 RangePtg (org.apache.poi.ss.formula.ptg.RangePtg)6 HSSFWorkbook (org.apache.poi.hssf.usermodel.HSSFWorkbook)5 AddPtg (org.apache.poi.ss.formula.ptg.AddPtg)5 AreaPtg (org.apache.poi.ss.formula.ptg.AreaPtg)5 ArrayPtg (org.apache.poi.ss.formula.ptg.ArrayPtg)5 BoolPtg (org.apache.poi.ss.formula.ptg.BoolPtg)5 ConcatPtg (org.apache.poi.ss.formula.ptg.ConcatPtg)5 DividePtg (org.apache.poi.ss.formula.ptg.DividePtg)5 EqualPtg (org.apache.poi.ss.formula.ptg.EqualPtg)5 ErrPtg (org.apache.poi.ss.formula.ptg.ErrPtg)5