Examples with XSSFEvaluationWorkbook org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook used on opensource projects

Example 1 with XSSFEvaluationWorkbook

use of org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook 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 XSSFEvaluationWorkbook

use of org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook in project poi by apache.

the class TestFormulaParser method testParseExternalReferencesWithQuotedSheetName.

// bug 60219: FormulaParser can't parse external references when sheet name is quoted
@Test
public void testParseExternalReferencesWithQuotedSheetName() throws Exception {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFEvaluationWorkbook fpwb = XSSFEvaluationWorkbook.create(wb);
    Ptg[] ptgs = FormulaParser.parse("'[1]Sheet 1'!A1", fpwb, FormulaType.CELL, -1);
    // org.apache.poi.ss.formula.ptg.Ref3DPxg [ [workbook=1] sheet=Sheet 1 ! A1]
    assertEquals("Ptgs length", 1, ptgs.length);
    assertTrue("Ptg class", ptgs[0] instanceof Ref3DPxg);
    Ref3DPxg pxg = (Ref3DPxg) ptgs[0];
    assertEquals("External workbook number", 1, pxg.getExternalWorkbookNumber());
    assertEquals("Sheet name", "Sheet 1", pxg.getSheetName());
    assertEquals("Row", 0, pxg.getRow());
    assertEquals("Column", 0, pxg.getColumn());
    wb.close();
}

Example 3 with XSSFEvaluationWorkbook

use of org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook in project poi by apache.

the class TestFormulaParser method testParserErrors.

@Test
public void testParserErrors() throws Exception {
    XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("testNames.xlsm");
    try {
        XSSFEvaluationWorkbook workbook = XSSFEvaluationWorkbook.create(wb);
        parseExpectedException("(");
        parseExpectedException(")");
        parseExpectedException("+");
        parseExpectedException("42+");
        parseExpectedException("IF()");
        //no closing paren
        parseExpectedException("IF(");
        //no closing paren
        parseExpectedException("myFunc(", workbook);
    } finally {
        wb.close();
    }
}

Example 4 with XSSFEvaluationWorkbook

use of org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook in project poi by apache.

the class XSSFRowShifter method shiftFormula.

/**
     * Shift a formula using the supplied FormulaShifter
     *
     * @param row     the row of the cell this formula belongs to. Used to get a reference to the parent workbook.
     * @param formula the formula to shift
     * @param shifter the FormulaShifter object that operates on the parsed formula tokens
     * @return the shifted formula if the formula was changed,
     *         <code>null</code> if the formula wasn't modified
     */
private static String shiftFormula(Row row, String formula, FormulaShifter shifter) {
    Sheet sheet = row.getSheet();
    Workbook wb = sheet.getWorkbook();
    int sheetIndex = wb.getSheetIndex(sheet);
    final int rowIndex = row.getRowNum();
    XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook) wb);
    try {
        Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex, rowIndex);
        String shiftedFmla = null;
        if (shifter.adjustFormula(ptgs, sheetIndex)) {
            shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
        }
        return shiftedFmla;
    } catch (FormulaParseException fpe) {
        // Log, but don't change, rather than breaking
        logger.log(POILogger.WARN, "Error shifting formula on row ", row.getRowNum(), fpe);
        return formula;
    }
}

Example 5 with XSSFEvaluationWorkbook

use of org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook in project poi by apache.

the class XSSFRowShifter method updateNamedRanges.

/**
     * Updated named ranges
     */
public void updateNamedRanges(FormulaShifter shifter) {
    Workbook wb = sheet.getWorkbook();
    XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook) wb);
    for (Name name : wb.getAllNames()) {
        String formula = name.getRefersToFormula();
        int sheetIndex = name.getSheetIndex();
        //don't care, named ranges are not allowed to include structured references
        final int rowIndex = -1;
        Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.NAMEDRANGE, sheetIndex, rowIndex);
        if (shifter.adjustFormula(ptgs, sheetIndex)) {
            String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
            name.setRefersToFormula(shiftedFmla);
        }
    }
}