Examples with HSSFRow org.apache.poi.hssf.usermodel.HSSFRow used on opensource projects

Example 21 with HSSFRow

use of org.apache.poi.hssf.usermodel.HSSFRow in project poi by apache.

the class TestMirr method testEvaluateInSheet.

public void testEvaluateInSheet() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFRow row = sheet.createRow(0);
    row.createCell(0).setCellValue(-7500d);
    row.createCell(1).setCellValue(3000d);
    row.createCell(2).setCellValue(5000d);
    row.createCell(3).setCellValue(1200d);
    row.createCell(4).setCellValue(4000d);
    row.createCell(5).setCellValue(0.05d);
    row.createCell(6).setCellValue(0.08d);
    HSSFCell cell = row.createCell(7);
    cell.setCellFormula("MIRR(A1:E1, F1, G1)");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    fe.clearAllCachedResultValues();
    fe.evaluateFormulaCellEnum(cell);
    double res = cell.getNumericCellValue();
    assertEquals(0.18736225093, res, 0.00000001);
}

Example 22 with HSSFRow

use of org.apache.poi.hssf.usermodel.HSSFRow in project poi by apache.

the class TestEvaluationCache method testBlankCellChangedToValueCell_bug46053.

/**
	 * Make sure that when blank cells are changed to value/formula cells, any dependent formulas
	 * have their cached results cleared.
	 */
public void testBlankCellChangedToValueCell_bug46053() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFRow row = sheet.createRow(0);
    HSSFCell cellA1 = row.createCell(0);
    HSSFCell cellB1 = row.createCell(1);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    cellA1.setCellFormula("B1+2.2");
    cellB1.setCellValue(1.5);
    fe.notifyUpdateCell(cellA1);
    fe.notifyUpdateCell(cellB1);
    CellValue cv;
    cv = fe.evaluate(cellA1);
    assertEquals(3.7, cv.getNumberValue(), 0.0);
    cellB1.setCellType(CellType.BLANK);
    fe.notifyUpdateCell(cellB1);
    // B1 was used to evaluate A1
    cv = fe.evaluate(cellA1);
    assertEquals(2.2, cv.getNumberValue(), 0.0);
    // changing B1, so A1 cached result should be cleared
    cellB1.setCellValue(0.4);
    fe.notifyUpdateCell(cellB1);
    cv = fe.evaluate(cellA1);
    if (cv.getNumberValue() == 2.2) {
        // looks like left-over cached result from before change to B1
        throw new AssertionFailedError("Identified bug 46053");
    }
    assertEquals(2.6, cv.getNumberValue(), 0.0);
}

Example 23 with HSSFRow

use of org.apache.poi.hssf.usermodel.HSSFRow in project poi by apache.

the class BaseTestFunctionsFromSpreadsheet method processFunctionGroup.

private static void processFunctionGroup(List<Object[]> data, HSSFSheet sheet, final int startRowIndex, String testFocusFunctionName, String filename) {
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet.getWorkbook());
    String currentGroupComment = "";
    final int maxRows = sheet.getLastRowNum() + 1;
    for (int rowIndex = startRowIndex; rowIndex < maxRows; rowIndex++) {
        HSSFRow r = sheet.getRow(rowIndex);
        if (r == null) {
            continue;
        }
        String newMarkerValue = getCellTextValue(r, SS.COLUMN_INDEX_MARKER, "marker");
        if (SS.TEST_CASES_END_MARKER.equalsIgnoreCase(newMarkerValue)) {
            // normal exit point
            return;
        }
        if (SS.SKIP_CURRENT_TEST_CASE_MARKER.equalsIgnoreCase(newMarkerValue)) {
            // currently disabled test case row
            continue;
        }
        if (newMarkerValue != null) {
            currentGroupComment = newMarkerValue;
        }
        HSSFCell evalCell = r.getCell(SS.COLUMN_INDEX_EVALUATION);
        if (evalCell == null || evalCell.getCellTypeEnum() != CellType.FORMULA) {
            continue;
        }
        String rowComment = getCellTextValue(r, SS.COLUMN_ROW_COMMENT, "row comment");
        String testName = (currentGroupComment + '\n' + rowComment).replace("null", "").trim().replace("\n", " - ");
        if ("".equals(testName)) {
            testName = evalCell.getCellFormula();
        }
        data.add(new Object[] { testName, filename, sheet, rowIndex, evaluator });
    }
    fail("Missing end marker '" + SS.TEST_CASES_END_MARKER + "' on sheet '" + sheet.getSheetName() + "'");
}

Example 24 with HSSFRow

use of org.apache.poi.hssf.usermodel.HSSFRow in project poi by apache.

the class BaseTestFunctionsFromSpreadsheet method processFunctionRow.

@Test
public void processFunctionRow() throws Exception {
    HSSFRow r = sheet.getRow(formulasRowIdx);
    HSSFCell evalCell = r.getCell(SS.COLUMN_INDEX_EVALUATION);
    HSSFCell expectedCell = r.getCell(SS.COLUMN_INDEX_EXPECTED_RESULT);
    CellReference cr = new CellReference(sheet.getSheetName(), formulasRowIdx, evalCell.getColumnIndex(), false, false);
    String msg = String.format(Locale.ROOT, "In %s %s {=%s} '%s'", filename, cr.formatAsString(), evalCell.getCellFormula(), testName);
    CellValue actualValue = evaluator.evaluate(evalCell);
    assertNotNull(msg + " - Bad setup data expected value is null", expectedCell);
    assertNotNull(msg + " - actual value was null", actualValue);
    if (expectedCell.getCellTypeEnum() == CellType.ERROR) {
        int expectedErrorCode = expectedCell.getErrorCellValue();
        assertEquals(msg, CellType.ERROR, actualValue.getCellTypeEnum());
        assertEquals(msg, ErrorEval.getText(expectedErrorCode), actualValue.formatAsString());
        assertEquals(msg, expectedErrorCode, actualValue.getErrorValue());
        assertEquals(msg, ErrorEval.getText(expectedErrorCode), ErrorEval.getText(actualValue.getErrorValue()));
        return;
    }
    // unexpected error
    assertNotEquals(msg, CellType.ERROR, actualValue.getCellTypeEnum());
    assertNotEquals(msg, formatValue(expectedCell), ErrorEval.getText(actualValue.getErrorValue()));
    // wrong type error
    assertEquals(msg, expectedCell.getCellTypeEnum(), actualValue.getCellTypeEnum());
    final CellType expectedCellType = expectedCell.getCellTypeEnum();
    switch(expectedCellType) {
        case BOOLEAN:
            assertEquals(msg, expectedCell.getBooleanCellValue(), actualValue.getBooleanValue());
            break;
        case // will never be used, since we will call method after formula evaluation
        FORMULA:
            fail("Cannot expect formula as result of formula evaluation: " + msg);
        case NUMERIC:
            assertEquals(expectedCell.getNumericCellValue(), actualValue.getNumberValue(), 0.0);
            break;
        case STRING:
            assertEquals(msg, expectedCell.getRichStringCellValue().getString(), actualValue.getStringValue());
            break;
        default:
            fail("Unexpected cell type: " + expectedCellType);
    }
}

Example 25 with HSSFRow

use of org.apache.poi.hssf.usermodel.HSSFRow in project poi by apache.

the class TestYearFracCalculatorFromSpreadsheet method testAll.

@Test
public void testAll() throws Exception {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("yearfracExamples.xls");
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFFormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator(wb);
    int nSuccess = 0;
    Iterator<Row> rowIterator = sheet.rowIterator();
    while (rowIterator.hasNext()) {
        HSSFRow row = (HSSFRow) rowIterator.next();
        HSSFCell cell = row.getCell(SS.YEARFRAC_FORMULA_COLUMN);
        if (cell == null || cell.getCellTypeEnum() != CellType.FORMULA) {
            continue;
        }
        processRow(row, cell, formulaEvaluator);
        nSuccess++;
    }
    assertTrue("No test sample cases found", nSuccess > 0);
    wb.close();
}