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

Example 1 with FuncPtg

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

the class FormulaViewer method listFormula.

private void listFormula(FormulaRecord record) {
    String sep = "~";
    Ptg[] tokens = record.getParsedExpression();
    Ptg token;
    int numptgs = tokens.length;
    String numArg;
    token = tokens[numptgs - 1];
    if (token instanceof FuncPtg) {
        numArg = String.valueOf(numptgs - 1);
    } else {
        numArg = String.valueOf(-1);
    }
    StringBuilder buf = new StringBuilder();
    if (token instanceof ExpPtg)
        return;
    buf.append(token.toFormulaString());
    buf.append(sep);
    switch(token.getPtgClass()) {
        case Ptg.CLASS_REF:
            buf.append("REF");
            break;
        case Ptg.CLASS_VALUE:
            buf.append("VALUE");
            break;
        case Ptg.CLASS_ARRAY:
            buf.append("ARRAY");
            break;
        default:
            throwInvalidRVAToken(token);
    }
    buf.append(sep);
    if (numptgs > 1) {
        token = tokens[numptgs - 2];
        switch(token.getPtgClass()) {
            case Ptg.CLASS_REF:
                buf.append("REF");
                break;
            case Ptg.CLASS_VALUE:
                buf.append("VALUE");
                break;
            case Ptg.CLASS_ARRAY:
                buf.append("ARRAY");
                break;
            default:
                throwInvalidRVAToken(token);
        }
    } else {
        buf.append("VALUE");
    }
    buf.append(sep);
    buf.append(numArg);
    System.out.println(buf);
}

Example 2 with FuncPtg

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

the class TestFormulaParserIf method testIfSingleCondition.

public void testIfSingleCondition() {
    Ptg[] ptgs = parseFormula("IF(1=1,10)");
    assertEquals(7, ptgs.length);
    assertTrue("IF Attr set correctly", (ptgs[3] instanceof AttrPtg));
    AttrPtg ifFunc = (AttrPtg) ptgs[3];
    assertTrue("It is not an if", ifFunc.isOptimizedIf());
    assertTrue("Single Value is not an IntPtg", (ptgs[4] instanceof IntPtg));
    IntPtg intPtg = (IntPtg) ptgs[4];
    assertEquals("Result", (short) 10, intPtg.getValue());
    assertTrue("Ptg is not a Variable Function", (ptgs[6] instanceof FuncVarPtg));
    FuncVarPtg funcPtg = (FuncVarPtg) ptgs[6];
    assertEquals("Arguments", 2, funcPtg.getNumberOfOperands());
}

Example 3 with FuncPtg

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

the class TestFormulaEvaluatorBugs method test55747_55324.

@SuppressWarnings("resource")
@Test
public void test55747_55324() throws Exception {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFFormulaEvaluator ev = wb.getCreationHelper().createFormulaEvaluator();
    HSSFSheet ws = wb.createSheet();
    HSSFRow row = ws.createRow(0);
    HSSFCell cell;
    // Our test value
    cell = row.createCell(0);
    cell.setCellValue("abc");
    // Lots of IF cases
    cell = row.createCell(1);
    //if(expr,func,val)
    cell.setCellFormula("IF(A1<>\"\",MID(A1,1,2),\"X\")");
    cell = row.createCell(2);
    // if(expr,val,val)
    cell.setCellFormula("IF(A1<>\"\",\"A\",\"B\")");
    cell = row.createCell(3);
    //if(expr,val,func),
    cell.setCellFormula("IF(A1=\"\",\"X\",MID(A1,1,2))");
    cell = row.createCell(4);
    //if(expr,val,func),
    cell.setCellFormula("IF(A1<>\"\",\"X\",MID(A1,1,2))");
    cell = row.createCell(5);
    //if(exp,func,func)
    cell.setCellFormula("IF(A1=\"\",MID(A1,1,2),MID(A1,2,2))");
    cell = row.createCell(6);
    //if(exp,func,func)
    cell.setCellFormula("IF(A1<>\"\",MID(A1,1,2),MID(A1,2,2))");
    cell = row.createCell(7);
    //if(func_expr,val,val)
    cell.setCellFormula("IF(MID(A1,1,2)<>\"\",\"A\",\"B\")");
    // And some MID ones just to check
    row = ws.createRow(1);
    cell = row.createCell(1);
    cell.setCellFormula("MID(A1,1,2)");
    cell = row.createCell(2);
    cell.setCellFormula("MID(A1,2,2)");
    cell = row.createCell(3);
    cell.setCellFormula("MID(A1,2,1)");
    cell = row.createCell(4);
    cell.setCellFormula("MID(A1,3,1)");
    // Evaluate
    ev.evaluateAll();
    // Save and re-load
    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    ws = wb.getSheetAt(0);
    // Check the MID Ptgs in Row 2 have V RefPtgs for A1
    row = ws.getRow(1);
    for (int i = 1; i <= 4; i++) {
        cell = row.getCell(i);
        Ptg[] ptgs = getPtgs(cell);
        assertEquals(4, ptgs.length);
        assertEquals(FuncPtg.class, ptgs[3].getClass());
        assertEquals("MID", ((FuncPtg) ptgs[3]).getName());
        assertRefPtgA1('V', ptgs, 0);
    }
    // Now check the IF formulas
    row = ws.getRow(0);
    // H1, MID is used in the expression IF checks, so A1 should be V
    cell = row.getCell(CellReference.convertColStringToIndex("H"));
    assertRefPtgA1('V', getPtgs(cell), 0);
    // E1, MID is used in the FALSE route, so:
    //  A1 should be V in the IF check
    //  A1 should be R in the FALSE route
    cell = row.getCell(CellReference.convertColStringToIndex("E"));
    assertRefPtgA1('V', getPtgs(cell), 0);
    assertRefPtgA1('R', getPtgs(cell), 6);
    // Check that, for B1, D1, F1 and G1, the references to A1
    //  from all of IF check, True and False are V
    cell = row.getCell(CellReference.convertColStringToIndex("B"));
    assertRefPtgA1('V', getPtgs(cell), 0);
    //      assertRefPtgA1('V', getPtgs(cell), 4); // FIXME!
    cell = row.getCell(CellReference.convertColStringToIndex("D"));
    assertRefPtgA1('V', getPtgs(cell), 0);
    //      assertRefPtgA1('V', getPtgs(cell), 6); // FIXME!
    cell = row.getCell(CellReference.convertColStringToIndex("F"));
    assertRefPtgA1('V', getPtgs(cell), 0);
    //      assertRefPtgA1('V', getPtgs(cell), 4); // FIXME!
    //      assertRefPtgA1('V', getPtgs(cell), 9); // FIXME!
    cell = row.getCell(CellReference.convertColStringToIndex("G"));
    assertRefPtgA1('V', getPtgs(cell), 0);
    //      assertRefPtgA1('V', getPtgs(cell), 4); // FIXME!
    //      assertRefPtgA1('V', getPtgs(cell), 9); // FIXME!
    // Check our cached values were correctly evaluated
    cell = row.getCell(CellReference.convertColStringToIndex("A"));
    assertEquals("abc", cell.getStringCellValue());
    cell = row.getCell(CellReference.convertColStringToIndex("B"));
    assertEquals("ab", cell.getStringCellValue());
    cell = row.getCell(CellReference.convertColStringToIndex("C"));
    assertEquals("A", cell.getStringCellValue());
    cell = row.getCell(CellReference.convertColStringToIndex("D"));
    assertEquals("ab", cell.getStringCellValue());
    cell = row.getCell(CellReference.convertColStringToIndex("E"));
    assertEquals("X", cell.getStringCellValue());
    cell = row.getCell(CellReference.convertColStringToIndex("F"));
    assertEquals("bc", cell.getStringCellValue());
    cell = row.getCell(CellReference.convertColStringToIndex("G"));
    assertEquals("ab", cell.getStringCellValue());
    cell = row.getCell(CellReference.convertColStringToIndex("H"));
    assertEquals("A", cell.getStringCellValue());
    // Enable this to write out + check in Excel
    if (OUTPUT_TEST_FILES) {
        FileOutputStream out = new FileOutputStream("/tmp/test.xls");
        wb.write(out);
        out.close();
    }
}