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

Example 1 with FormulaParsingWorkbook

use of org.apache.poi.ss.formula.FormulaParsingWorkbook in project poi by apache.

the class Indirect method evaluateIndirect.

private static ValueEval evaluateIndirect(final OperationEvaluationContext ec, String text, boolean isA1style) {
    // Search backwards for '!' because sheet names can contain '!'
    int plingPos = text.lastIndexOf('!');
    String workbookName;
    String sheetName;
    // whitespace around this gets trimmed OK
    String refText;
    if (plingPos < 0) {
        workbookName = null;
        sheetName = null;
        refText = text;
    } else {
        String[] parts = parseWorkbookAndSheetName(text.subSequence(0, plingPos));
        if (parts == null) {
            return ErrorEval.REF_INVALID;
        }
        workbookName = parts[0];
        sheetName = parts[1];
        refText = text.substring(plingPos + 1);
    }
    if (Table.isStructuredReference.matcher(refText).matches()) {
        // The argument is structured reference
        Area3DPxg areaPtg = null;
        try {
            areaPtg = FormulaParser.parseStructuredReference(refText, (FormulaParsingWorkbook) ec.getWorkbook(), ec.getRowIndex());
        } catch (FormulaParseException e) {
            return ErrorEval.REF_INVALID;
        }
        return ec.getArea3DEval(areaPtg);
    } else {
        // The argument is regular reference
        String refStrPart1;
        String refStrPart2;
        int colonPos = refText.indexOf(':');
        if (colonPos < 0) {
            refStrPart1 = refText.trim();
            refStrPart2 = null;
        } else {
            refStrPart1 = refText.substring(0, colonPos).trim();
            refStrPart2 = refText.substring(colonPos + 1).trim();
        }
        return ec.getDynamicReference(workbookName, sheetName, refStrPart1, refStrPart2, isA1style);
    }
}

Example 2 with FormulaParsingWorkbook

use of org.apache.poi.ss.formula.FormulaParsingWorkbook in project poi by apache.

the class TestXSSFFormulaParser method multiSheetReferencesHSSFandXSSF.

/**
     * A handful of functions (such as SUM, COUNTA, MIN) support
     *  multi-sheet references (eg Sheet1:Sheet3!A1 = Cell A1 from
     *  Sheets 1 through Sheet 3) and multi-sheet area references 
     *  (eg Sheet1:Sheet3!A1:B2 = Cells A1 through B2 from Sheets
     *   1 through Sheet 3).
     * This test, based on common test files for HSSF and XSSF, checks
     *  that we can read and parse these kinds of references 
     * (but not evaluate - that's elsewhere in the test suite)
     */
@Test
public void multiSheetReferencesHSSFandXSSF() throws IOException {
    Workbook[] wbs = new Workbook[] { HSSFTestDataSamples.openSampleWorkbook("55906-MultiSheetRefs.xls"), XSSFTestDataSamples.openSampleWorkbook("55906-MultiSheetRefs.xlsx") };
    for (Workbook wb : wbs) {
        Sheet s1 = wb.getSheetAt(0);
        Ptg[] ptgs;
        // Check the contents
        Cell sumF = s1.getRow(2).getCell(0);
        assertNotNull(sumF);
        assertEquals("SUM(Sheet1:Sheet3!A1)", sumF.getCellFormula());
        Cell avgF = s1.getRow(2).getCell(1);
        assertNotNull(avgF);
        assertEquals("AVERAGE(Sheet1:Sheet3!A1)", avgF.getCellFormula());
        Cell countAF = s1.getRow(2).getCell(2);
        assertNotNull(countAF);
        assertEquals("COUNTA(Sheet1:Sheet3!C1)", countAF.getCellFormula());
        Cell maxF = s1.getRow(4).getCell(1);
        assertNotNull(maxF);
        assertEquals("MAX(Sheet1:Sheet3!A$1)", maxF.getCellFormula());
        Cell sumFA = s1.getRow(2).getCell(7);
        assertNotNull(sumFA);
        assertEquals("SUM(Sheet1:Sheet3!A1:B2)", sumFA.getCellFormula());
        Cell avgFA = s1.getRow(2).getCell(8);
        assertNotNull(avgFA);
        assertEquals("AVERAGE(Sheet1:Sheet3!A1:B2)", avgFA.getCellFormula());
        Cell maxFA = s1.getRow(4).getCell(8);
        assertNotNull(maxFA);
        assertEquals("MAX(Sheet1:Sheet3!A$1:B$2)", maxFA.getCellFormula());
        Cell countFA = s1.getRow(5).getCell(8);
        assertNotNull(countFA);
        assertEquals("COUNT(Sheet1:Sheet3!$A$1:$B$2)", countFA.getCellFormula());
        // Create a formula parser
        final FormulaParsingWorkbook fpb;
        if (wb instanceof HSSFWorkbook)
            fpb = HSSFEvaluationWorkbook.create((HSSFWorkbook) wb);
        else
            fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook) wb);
        // Check things parse as expected:
        // SUM to one cell over 3 workbooks, relative reference
        ptgs = parse(fpb, "SUM(Sheet1:Sheet3!A1)");
        assertEquals(2, ptgs.length);
        if (wb instanceof HSSFWorkbook) {
            assertEquals(Ref3DPtg.class, ptgs[0].getClass());
        } else {
            assertEquals(Ref3DPxg.class, ptgs[0].getClass());
        }
        assertEquals("Sheet1:Sheet3!A1", toFormulaString(ptgs[0], fpb));
        assertEquals(AttrPtg.class, ptgs[1].getClass());
        assertEquals("SUM", toFormulaString(ptgs[1], fpb));
        // MAX to one cell over 3 workbooks, absolute row reference
        ptgs = parse(fpb, "MAX(Sheet1:Sheet3!A$1)");
        assertEquals(2, ptgs.length);
        if (wb instanceof HSSFWorkbook) {
            assertEquals(Ref3DPtg.class, ptgs[0].getClass());
        } else {
            assertEquals(Ref3DPxg.class, ptgs[0].getClass());
        }
        assertEquals("Sheet1:Sheet3!A$1", toFormulaString(ptgs[0], fpb));
        assertEquals(FuncVarPtg.class, ptgs[1].getClass());
        assertEquals("MAX", toFormulaString(ptgs[1], fpb));
        // MIN to one cell over 3 workbooks, absolute reference
        ptgs = parse(fpb, "MIN(Sheet1:Sheet3!$A$1)");
        assertEquals(2, ptgs.length);
        if (wb instanceof HSSFWorkbook) {
            assertEquals(Ref3DPtg.class, ptgs[0].getClass());
        } else {
            assertEquals(Ref3DPxg.class, ptgs[0].getClass());
        }
        assertEquals("Sheet1:Sheet3!$A$1", toFormulaString(ptgs[0], fpb));
        assertEquals(FuncVarPtg.class, ptgs[1].getClass());
        assertEquals("MIN", toFormulaString(ptgs[1], fpb));
        // SUM to a range of cells over 3 workbooks
        ptgs = parse(fpb, "SUM(Sheet1:Sheet3!A1:B2)");
        assertEquals(2, ptgs.length);
        if (wb instanceof HSSFWorkbook) {
            assertEquals(Area3DPtg.class, ptgs[0].getClass());
        } else {
            assertEquals(Area3DPxg.class, ptgs[0].getClass());
        }
        assertEquals("Sheet1:Sheet3!A1:B2", toFormulaString(ptgs[0], fpb));
        assertEquals(AttrPtg.class, ptgs[1].getClass());
        assertEquals("SUM", toFormulaString(ptgs[1], fpb));
        // MIN to a range of cells over 3 workbooks, absolute reference
        ptgs = parse(fpb, "MIN(Sheet1:Sheet3!$A$1:$B$2)");
        assertEquals(2, ptgs.length);
        if (wb instanceof HSSFWorkbook) {
            assertEquals(Area3DPtg.class, ptgs[0].getClass());
        } else {
            assertEquals(Area3DPxg.class, ptgs[0].getClass());
        }
        assertEquals("Sheet1:Sheet3!$A$1:$B$2", toFormulaString(ptgs[0], fpb));
        assertEquals(FuncVarPtg.class, ptgs[1].getClass());
        assertEquals("MIN", toFormulaString(ptgs[1], fpb));
        // Check we can round-trip - try to set a new one to a new single cell
        Cell newF = s1.getRow(0).createCell(10, CellType.FORMULA);
        newF.setCellFormula("SUM(Sheet2:Sheet3!A1)");
        assertEquals("SUM(Sheet2:Sheet3!A1)", newF.getCellFormula());
        // Check we can round-trip - try to set a new one to a cell range
        newF = s1.getRow(0).createCell(11, CellType.FORMULA);
        newF.setCellFormula("MIN(Sheet1:Sheet2!A1:B2)");
        assertEquals("MIN(Sheet1:Sheet2!A1:B2)", newF.getCellFormula());
        wb.close();
    }
}