Search in sources :

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());
}
Also used : HSSFCell(org.apache.poi.hssf.usermodel.HSSFCell)

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() + "'");
}
Also used : HSSFFormulaEvaluator(org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator) HSSFCell(org.apache.poi.hssf.usermodel.HSSFCell) HSSFRow(org.apache.poi.hssf.usermodel.HSSFRow)

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);
}
Also used : HSSFFormulaEvaluator(org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator) HSSFCell(org.apache.poi.hssf.usermodel.HSSFCell) HSSFSheet(org.apache.poi.hssf.usermodel.HSSFSheet) HSSFWorkbook(org.apache.poi.hssf.usermodel.HSSFWorkbook)

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());
}
Also used : HSSFFormulaEvaluator(org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator) EmptyStackException(java.util.EmptyStackException) HSSFCell(org.apache.poi.hssf.usermodel.HSSFCell) HSSFSheet(org.apache.poi.hssf.usermodel.HSSFSheet) CellValue(org.apache.poi.ss.usermodel.CellValue) AssertionFailedError(junit.framework.AssertionFailedError) HSSFWorkbook(org.apache.poi.hssf.usermodel.HSSFWorkbook)

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);
    }
}
Also used : CellType(org.apache.poi.ss.usermodel.CellType) HSSFCell(org.apache.poi.hssf.usermodel.HSSFCell) HSSFRow(org.apache.poi.hssf.usermodel.HSSFRow) CellValue(org.apache.poi.ss.usermodel.CellValue) CellReference(org.apache.poi.hssf.util.CellReference) Test(org.junit.Test)

Aggregations

HSSFCell (org.apache.poi.hssf.usermodel.HSSFCell)150 HSSFRow (org.apache.poi.hssf.usermodel.HSSFRow)99 HSSFWorkbook (org.apache.poi.hssf.usermodel.HSSFWorkbook)95 HSSFSheet (org.apache.poi.hssf.usermodel.HSSFSheet)93 HSSFCellStyle (org.apache.poi.hssf.usermodel.HSSFCellStyle)32 HSSFFormulaEvaluator (org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator)30 HSSFRichTextString (org.apache.poi.hssf.usermodel.HSSFRichTextString)29 FileOutputStream (java.io.FileOutputStream)26 Test (org.junit.Test)25 IOException (java.io.IOException)19 HSSFFont (org.apache.poi.hssf.usermodel.HSSFFont)16 File (java.io.File)14 CellValue (org.apache.poi.ss.usermodel.CellValue)13 ArrayList (java.util.ArrayList)11 AssertionFailedError (junit.framework.AssertionFailedError)10 HashMap (java.util.HashMap)9 CellRangeAddress (org.apache.poi.ss.util.CellRangeAddress)9 OutputStream (java.io.OutputStream)8 Map (java.util.Map)8 FileInputStream (java.io.FileInputStream)7