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

Example 31 with HSSFCell

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

the class TestWorkbookEvaluator method confirmFormula.

private static void confirmFormula(HSSFWorkbook wb, int sheetIndex, int rowIndex, int columnIndex, String expectedFormula) {
    HSSFCell cell = wb.getSheetAt(sheetIndex).getRow(rowIndex).getCell(columnIndex);
    assertEquals(expectedFormula, cell.getCellFormula());
}

Example 32 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell 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 33 with HSSFCell

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

the class TestMissingArgEval method testCountFuncs.

public void testCountFuncs() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFCell cell = sheet.createRow(0).createCell(0);
    // 4 missing args, C5 is blank 
    cell.setCellFormula("COUNT(C5,,,,)");
    assertEquals(4.0, fe.evaluate(cell).getNumberValue(), 0.0);
    // 2 missing args, C5 is blank 
    cell.setCellFormula("COUNTA(C5,,)");
    fe.clearAllCachedResultValues();
    assertEquals(2.0, fe.evaluate(cell).getNumberValue(), 0.0);
}

Example 34 with HSSFCell

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

the class TestMissingArgEval method testEvaluateMissingArgs.

public void testEvaluateMissingArgs() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFCell cell = sheet.createRow(0).createCell(0);
    cell.setCellFormula("if(true,)");
    fe.clearAllCachedResultValues();
    CellValue cv;
    try {
        cv = fe.evaluate(cell);
    } catch (EmptyStackException e) {
        throw new AssertionFailedError("Missing args evaluation not implemented (bug 43354");
    }
    // MissingArg -> BlankEval -> zero (as formula result)
    assertEquals(0.0, cv.getNumberValue(), 0.0);
    // MissingArg -> BlankEval -> empty string (in concatenation)
    cell.setCellFormula("\"abc\"&if(true,)");
    fe.clearAllCachedResultValues();
    assertEquals("abc", fe.evaluate(cell).getStringValue());
}

Example 35 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell 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);
    }
}