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

Example 61 with HSSFSheet

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

Example 62 with HSSFSheet

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

the class TestCountFuncs method testCountifExamples.

/**
     * Two COUNTIF examples taken from
     * http://office.microsoft.com/en-us/excel-help/countif-function-HP010069840.aspx?CTT=5&origin=HA010277524
     */
public void testCountifExamples() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("countifExamples.xls");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    HSSFSheet sheet1 = wb.getSheet("MSDN Example 1");
    for (int rowIx = 7; rowIx <= 12; rowIx++) {
        HSSFRow row = sheet1.getRow(rowIx - 1);
        // cell containing a formula with COUNTIF
        HSSFCell cellA = row.getCell(0);
        assertEquals(CellType.FORMULA, cellA.getCellTypeEnum());
        // cell with a reference value
        HSSFCell cellC = row.getCell(2);
        assertEquals(CellType.NUMERIC, cellC.getCellTypeEnum());
        CellValue cv = fe.evaluate(cellA);
        double actualValue = cv.getNumberValue();
        double expectedValue = cellC.getNumericCellValue();
        assertEquals("Problem with a formula at  " + new CellReference(cellA).formatAsString() + ": " + cellA.getCellFormula() + " :" + "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ", expectedValue, actualValue, 0.0001);
    }
    HSSFSheet sheet2 = wb.getSheet("MSDN Example 2");
    for (int rowIx = 9; rowIx <= 14; rowIx++) {
        HSSFRow row = sheet2.getRow(rowIx - 1);
        // cell containing a formula with COUNTIF
        HSSFCell cellA = row.getCell(0);
        assertEquals(CellType.FORMULA, cellA.getCellTypeEnum());
        // cell with a reference value
        HSSFCell cellC = row.getCell(2);
        assertEquals(CellType.NUMERIC, cellC.getCellTypeEnum());
        CellValue cv = fe.evaluate(cellA);
        double actualValue = cv.getNumberValue();
        double expectedValue = cellC.getNumericCellValue();
        assertEquals("Problem with a formula at " + new CellReference(cellA).formatAsString() + "[" + cellA.getCellFormula() + "]: " + "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ", expectedValue, actualValue, 0.0001);
    }
}

Example 63 with HSSFSheet

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

the class TestCountFuncs method testCountifBug51498.

/**
    * Bug #51498 - Check that CountIf behaves correctly for GTE, LTE
    *  and NEQ cases
    */
public void testCountifBug51498() throws Exception {
    final int REF_COL = 4;
    final int EVAL_COL = 3;
    HSSFWorkbook workbook = HSSFTestDataSamples.openSampleWorkbook("51498.xls");
    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    HSSFSheet sheet = workbook.getSheetAt(0);
    // numeric criteria
    for (int i = 0; i < 8; i++) {
        CellValue expected = evaluator.evaluate(sheet.getRow(i).getCell(REF_COL));
        CellValue actual = evaluator.evaluate(sheet.getRow(i).getCell(EVAL_COL));
        assertEquals(expected.formatAsString(), actual.formatAsString());
    }
    // boolean criteria
    for (int i = 0; i < 8; i++) {
        HSSFCell cellFmla = sheet.getRow(i).getCell(8);
        HSSFCell cellRef = sheet.getRow(i).getCell(9);
        double expectedValue = cellRef.getNumericCellValue();
        double actualValue = evaluator.evaluate(cellFmla).getNumberValue();
        assertEquals("Problem with a formula at " + new CellReference(cellFmla).formatAsString() + "[" + cellFmla.getCellFormula() + "] ", expectedValue, actualValue, 0.0001);
    }
    // string criteria
    for (int i = 1; i < 9; i++) {
        HSSFCell cellFmla = sheet.getRow(i).getCell(13);
        HSSFCell cellRef = sheet.getRow(i).getCell(14);
        double expectedValue = cellRef.getNumericCellValue();
        double actualValue = evaluator.evaluate(cellFmla).getNumberValue();
        assertEquals("Problem with a formula at " + new CellReference(cellFmla).formatAsString() + "[" + cellFmla.getCellFormula() + "] ", expectedValue, actualValue, 0.0001);
    }
}

Example 64 with HSSFSheet

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

the class TestCountFuncs method testCountFunctionFromSpreadsheet.

private static void testCountFunctionFromSpreadsheet(String FILE_NAME, int START_ROW_IX, int COL_IX_ACTUAL, int COL_IX_EXPECTED, String functionName) {
    int failureCount = 0;
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook(FILE_NAME);
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    int maxRow = sheet.getLastRowNum();
    for (int rowIx = START_ROW_IX; rowIx < maxRow; rowIx++) {
        HSSFRow row = sheet.getRow(rowIx);
        if (row == null) {
            continue;
        }
        HSSFCell cell = row.getCell(COL_IX_ACTUAL);
        CellValue cv = fe.evaluate(cell);
        double actualValue = cv.getNumberValue();
        double expectedValue = row.getCell(COL_IX_EXPECTED).getNumericCellValue();
        if (actualValue != expectedValue) {
            System.err.println("Problem with test case on row " + (rowIx + 1) + " " + "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ");
            failureCount++;
        }
    }
    if (failureCount > 0) {
        throw new AssertionFailedError(failureCount + " " + functionName + " evaluations failed. See stderr for more details");
    }
}

Example 65 with HSSFSheet

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

the class TestIndirect method createWBB.

private static HSSFWorkbook createWBB() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet("Sheet1");
    HSSFSheet sheet2 = wb.createSheet("Sheet2");
    HSSFSheet sheet3 = wb.createSheet("## Look here!");
    createDataRow(sheet1, 0, 400, 440, 480, 520);
    createDataRow(sheet1, 1, 420, 460, 500, 540);
    createDataRow(sheet2, 0, 50, 55, 60, 65);
    createDataRow(sheet2, 1, 51, 56, 61, 66);
    createDataRow(sheet3, 0, 42);
    return wb;
}