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

Example 1 with FormulaParseException

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

the class TestFormulaParser method testParseComplexName.

/**
     * In bug 47078, POI had trouble evaluating a defined name flagged as 'complex'.
     * POI should also be able to parse such defined names.
     */
@Test
public void testParseComplexName() throws IOException {
    // Mock up a spreadsheet to match the critical details of the sample
    HSSFWorkbook wb = new HSSFWorkbook();
    wb.createSheet("Sheet1");
    HSSFName definedName = wb.createName();
    definedName.setNameName("foo");
    definedName.setRefersToFormula("Sheet1!B2");
    // Set the complex flag - POI doesn't usually manipulate this flag
    NameRecord nameRec = TestHSSFName.getNameRecord(definedName);
    // 0x10 -> complex
    nameRec.setOptionFlag((short) 0x10);
    Ptg[] result;
    try {
        result = HSSFFormulaParser.parse("1+foo", wb);
    } catch (FormulaParseException e) {
        if (e.getMessage().equals("Specified name 'foo' is not a range as expected.")) {
            fail("Identified bug 47078c");
        }
        wb.close();
        throw e;
    }
    confirmTokenClasses(result, IntPtg.class, NamePtg.class, AddPtg.class);
    wb.close();
}

Example 2 with FormulaParseException

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

the class TestFormulaParser method confirmArgCountMsg.

private static void confirmArgCountMsg(String formula, String expectedMessage) throws IOException {
    HSSFWorkbook book = new HSSFWorkbook();
    try {
        HSSFFormulaParser.parse(formula, book);
        fail("Didn't get parse exception as expected");
    } catch (FormulaParseException e) {
        confirmParseException(e, expectedMessage);
    }
    book.close();
}

Example 3 with FormulaParseException

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

the class TestFormulaParser method testZeroRowRefs.

/**
     * Zero is not a valid row number so cell references like 'A0' are not valid.
     * Actually, they should be treated like defined names.
     * <br/>
     * In addition, leading zeros (on the row component) should be removed from cell
     * references during parsing.
     */
@Test
public void testZeroRowRefs() throws IOException {
    // bad because zero is not a valid row number
    String badCellRef = "B0";
    // this should get parsed as "B1"
    String leadingZeroCellRef = "B000001";
    HSSFWorkbook wb = new HSSFWorkbook();
    try {
        HSSFFormulaParser.parse(badCellRef, wb);
        fail("Identified bug 47312b - Shouldn't be able to parse cell ref '" + badCellRef + "'.");
    } catch (FormulaParseException e) {
        // expected during successful test
        confirmParseException(e, "Specified named range '" + badCellRef + "' does not exist in the current workbook.");
    }
    Ptg[] ptgs;
    try {
        ptgs = HSSFFormulaParser.parse(leadingZeroCellRef, wb);
        assertEquals("B1", ((RefPtg) ptgs[0]).toFormulaString());
    } catch (FormulaParseException e) {
        confirmParseException(e, "Specified named range '" + leadingZeroCellRef + "' does not exist in the current workbook.");
        // close but no cigar
        fail("Identified bug 47312c - '" + leadingZeroCellRef + "' should parse as 'B1'.");
    }
    // create a defined name called 'B0' and try again
    Name n = wb.createName();
    n.setNameName("B0");
    n.setRefersToFormula("1+1");
    ptgs = HSSFFormulaParser.parse("B0", wb);
    confirmTokenClasses(ptgs, NamePtg.class);
    wb.close();
}

Example 4 with FormulaParseException

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

the class TestFormulaParserEval method testEvaluateFormulaWithRowBeyond32768_Bug44539.

public void testEvaluateFormulaWithRowBeyond32768_Bug44539() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    wb.setSheetName(0, "Sheet1");
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = row.createCell(0);
    cell.setCellFormula("SUM(A32769:A32770)");
    // put some values in the cells to make the evaluation more interesting
    sheet.createRow(32768).createCell(0).setCellValue(31);
    sheet.createRow(32769).createCell(0).setCellValue(11);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue result;
    try {
        result = fe.evaluate(cell);
    } catch (FormulaParseException e) {
        if (!e.getMessage().equals("Found reference to named range \"A\", but that named range wasn't defined!")) {
            throw new AssertionFailedError("Identifed bug 44539");
        }
        throw e;
    }
    assertEquals(CellType.NUMERIC, result.getCellTypeEnum());
    assertEquals(42.0, result.getNumberValue(), 0.0);
}

Example 5 with FormulaParseException

use of org.apache.poi.ss.formula.FormulaParseException 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;
    }
}