Examples with FormulaRecordAggregate org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate used on opensource projects

Example 11 with FormulaRecordAggregate

use of org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate in project poi by apache.

the class TestFormulaEvaluatorBugs method getPtgs.

private Ptg[] getPtgs(HSSFCell cell) {
    assertEquals(CellType.FORMULA, cell.getCellTypeEnum());
    assertEquals(FormulaRecordAggregate.class, cell.getCellValueRecord().getClass());
    FormulaRecordAggregate agg = (FormulaRecordAggregate) cell.getCellValueRecord();
    FormulaRecord rec = agg.getFormulaRecord();
    return rec.getParsedExpression();
}

Example 12 with FormulaRecordAggregate

use of org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate in project poi by apache.

the class TestBugs method bugZipCodeFormulas.

/**
     * From the mailing list - ensure we can handle a formula
     *  containing a zip code, eg ="70164"
     */
@Test
public void bugZipCodeFormulas() throws Exception {
    HSSFWorkbook wb1 = new HSSFWorkbook();
    HSSFSheet s = wb1.createSheet();
    s.createRow(0);
    HSSFCell c1 = s.getRow(0).createCell(0);
    HSSFCell c2 = s.getRow(0).createCell(1);
    HSSFCell c3 = s.getRow(0).createCell(2);
    // As number and string
    c1.setCellFormula("70164");
    c2.setCellFormula("\"70164\"");
    c3.setCellFormula("\"90210\"");
    // Check the formulas
    assertEquals("70164", c1.getCellFormula());
    assertEquals("\"70164\"", c2.getCellFormula());
    // And check the values - blank
    confirmCachedValue(0.0, c1);
    confirmCachedValue(0.0, c2);
    confirmCachedValue(0.0, c3);
    // Try changing the cached value on one of the string
    //  formula cells, so we can see it updates properly
    c3.setCellValue(new HSSFRichTextString("test"));
    confirmCachedValue("test", c3);
    try {
        c3.getNumericCellValue();
        fail("exception should have been thrown");
    } catch (IllegalStateException e) {
        assertEquals("Cannot get a NUMERIC value from a STRING formula cell", e.getMessage());
    }
    // Now evaluate, they should all be changed
    HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb1);
    eval.evaluateFormulaCellEnum(c1);
    eval.evaluateFormulaCellEnum(c2);
    eval.evaluateFormulaCellEnum(c3);
    // Check that the cells now contain
    //  the correct values
    confirmCachedValue(70164.0, c1);
    confirmCachedValue("70164", c2);
    confirmCachedValue("90210", c3);
    // Write and read
    HSSFWorkbook wb2 = writeOutAndReadBack(wb1);
    wb1.close();
    HSSFSheet ns = wb2.getSheetAt(0);
    HSSFCell nc1 = ns.getRow(0).getCell(0);
    HSSFCell nc2 = ns.getRow(0).getCell(1);
    HSSFCell nc3 = ns.getRow(0).getCell(2);
    // Re-check
    confirmCachedValue(70164.0, nc1);
    confirmCachedValue("70164", nc2);
    confirmCachedValue("90210", nc3);
    int i = 0;
    for (Iterator<CellValueRecordInterface> it = ns.getSheet().getCellValueIterator(); it.hasNext(); i++) {
        CellValueRecordInterface cvr = it.next();
        if (cvr instanceof FormulaRecordAggregate) {
            FormulaRecordAggregate fr = (FormulaRecordAggregate) cvr;
            if (i == 0) {
                assertEquals(70164.0, fr.getFormulaRecord().getValue(), 0.0001);
                assertNull(fr.getStringRecord());
            } else if (i == 1) {
                assertEquals(0.0, fr.getFormulaRecord().getValue(), 0.0001);
                assertNotNull(fr.getStringRecord());
                assertEquals("70164", fr.getStringRecord().getString());
            } else {
                assertEquals(0.0, fr.getFormulaRecord().getValue(), 0.0001);
                assertNotNull(fr.getStringRecord());
                assertEquals("90210", fr.getStringRecord().getString());
            }
        }
    }
    assertEquals(3, i);
    wb2.close();
}

Example 13 with FormulaRecordAggregate

use of org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate in project poi by apache.

the class TestFormulaEvaluatorBugs method test44410.

/**
     * Bug 44410: SUM(C:C) is valid in excel, and means a sum
     *  of all the rows in Column C
     */
@Test
public void test44410() throws Exception {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("SingleLetterRanges.xls");
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(wb);
    // =index(C:C,2,1) -> 2
    HSSFRow rowIDX = sheet.getRow(3);
    // =sum(C:C) -> 6
    HSSFRow rowSUM = sheet.getRow(4);
    // =sum(C:D) -> 66
    HSSFRow rowSUM2D = sheet.getRow(5);
    // Test the sum
    HSSFCell cellSUM = rowSUM.getCell(0);
    FormulaRecordAggregate frec = (FormulaRecordAggregate) cellSUM.getCellValueRecord();
    Ptg[] ops = frec.getFormulaRecord().getParsedExpression();
    assertEquals(2, ops.length);
    assertEquals(AreaPtg.class, ops[0].getClass());
    assertEquals(FuncVarPtg.class, ops[1].getClass());
    // Actually stored as C1 to C65536
    // (last row is -1 === 65535)
    AreaPtg ptg = (AreaPtg) ops[0];
    assertEquals(2, ptg.getFirstColumn());
    assertEquals(2, ptg.getLastColumn());
    assertEquals(0, ptg.getFirstRow());
    assertEquals(65535, ptg.getLastRow());
    assertEquals("C:C", ptg.toFormulaString());
    // Will show as C:C, but won't know how many
    // rows it covers as we don't have the sheet
    // to hand when turning the Ptgs into a string
    assertEquals("SUM(C:C)", cellSUM.getCellFormula());
    // But the evaluator knows the sheet, so it
    // can do it properly
    assertEquals(6, eva.evaluate(cellSUM).getNumberValue(), 0);
    // Test the index
    // Again, the formula string will be right but
    // lacking row count, evaluated will be right
    HSSFCell cellIDX = rowIDX.getCell(0);
    assertEquals("INDEX(C:C,2,1)", cellIDX.getCellFormula());
    assertEquals(2, eva.evaluate(cellIDX).getNumberValue(), 0);
    // Across two colums
    HSSFCell cellSUM2D = rowSUM2D.getCell(0);
    assertEquals("SUM(C:D)", cellSUM2D.getCellFormula());
    assertEquals(66, eva.evaluate(cellSUM2D).getNumberValue(), 0);
    wb.close();
}

Example 14 with FormulaRecordAggregate

use of org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate in project poi by apache.

the class HSSFSheet method removeArrayFormula.

@Override
public CellRange<HSSFCell> removeArrayFormula(Cell cell) {
    if (cell.getSheet() != this) {
        throw new IllegalArgumentException("Specified cell does not belong to this sheet.");
    }
    CellValueRecordInterface rec = ((HSSFCell) cell).getCellValueRecord();
    if (!(rec instanceof FormulaRecordAggregate)) {
        String ref = new CellReference(cell).formatAsString();
        throw new IllegalArgumentException("Cell " + ref + " is not part of an array formula.");
    }
    FormulaRecordAggregate fra = (FormulaRecordAggregate) rec;
    CellRangeAddress range = fra.removeArrayFormula(cell.getRowIndex(), cell.getColumnIndex());
    CellRange<HSSFCell> result = getCellRange(range);
    // clear all cells in the range
    for (Cell c : result) {
        c.setCellType(CellType.BLANK);
    }
    return result;
}

Example 15 with FormulaRecordAggregate

use of org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate in project poi by apache.

the class HSSFCell method getBooleanCellValue.

/**
     * get the value of the cell as a boolean.  For strings, numbers, and errors, we throw an exception.
     * For blank cells we return a false.
     */
@Override
public boolean getBooleanCellValue() {
    switch(_cellType) {
        case BLANK:
            return false;
        case BOOLEAN:
            return ((BoolErrRecord) _record).getBooleanValue();
        case FORMULA:
            break;
        default:
            throw typeMismatch(CellType.BOOLEAN, _cellType, false);
    }
    FormulaRecord fr = ((FormulaRecordAggregate) _record).getFormulaRecord();
    checkFormulaCachedValueType(CellType.BOOLEAN, fr);
    return fr.getCachedBooleanValue();
}