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

Example 26 with HSSFRow

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

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

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

the class TestIsBlank method test3DArea.

public void test3DArea() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet();
    wb.setSheetName(0, "Sheet1");
    wb.createSheet();
    wb.setSheetName(1, "Sheet2");
    HSSFRow row = sheet1.createRow(0);
    HSSFCell cell = row.createCell(0);
    cell.setCellFormula("isblank(Sheet2!A1:A1)");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue result = fe.evaluate(cell);
    assertEquals(CellType.BOOLEAN, result.getCellTypeEnum());
    assertEquals(true, result.getBooleanValue());
    cell.setCellFormula("isblank(D7:D7)");
    result = fe.evaluate(cell);
    assertEquals(CellType.BOOLEAN, result.getCellTypeEnum());
    assertEquals(true, result.getBooleanValue());
}

Example 29 with HSSFRow

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

the class NumberComparingSpreadsheetGenerator method writeDataRow.

/**
	 * Fills a spreadsheet row with one comparison example. The two numeric values are written to
	 * columns C and D. Columns (F, G and H) respectively get formulas ("v0<v1", "v0=v1", "v0>v1"),
	 * which will be evaluated by Excel. Column D gets the expected comparison result. Column I
	 * gets a formula to check that Excel's comparison results match that predicted in column D.
	 *
	 * @param v0 the first value to be compared
	 * @param v1 the second value to be compared
	 * @param expRes expected comparison result (-1, 0, or +1)
	 */
static void writeDataRow(HSSFSheet sheet, int rowIx, double v0, double v1, int expRes) {
    HSSFRow row = sheet.createRow(rowIx);
    int rowNum = rowIx + 1;
    row.createCell(0).setCellValue(formatDoubleAsHex(v0));
    row.createCell(1).setCellValue(formatDoubleAsHex(v1));
    row.createCell(2).setCellValue(v0);
    row.createCell(3).setCellValue(v1);
    row.createCell(4).setCellValue(expRes < 0 ? "LT" : expRes > 0 ? "GT" : "EQ");
    row.createCell(5).setCellFormula("C" + rowNum + "<" + "D" + rowNum);
    row.createCell(6).setCellFormula("C" + rowNum + "=" + "D" + rowNum);
    row.createCell(7).setCellFormula("C" + rowNum + ">" + "D" + rowNum);
    // TODO - bug elsewhere in POI - something wrong with encoding of NOT() function
    String frm = "if(or(" + "and(E#='LT', F#      , G#=FALSE, H#=FALSE)," + "and(E#='EQ', F#=FALSE, G#      , H#=FALSE)," + "and(E#='GT', F#=FALSE, G#=FALSE, H#      )" + "), 'OK', 'error')";
    row.createCell(8).setCellFormula(frm.replaceAll("#", String.valueOf(rowNum)).replace('\'', '"'));
}

Example 30 with HSSFRow

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

the class ExcelUtil method readExcelCell03.

private static Object[][] readExcelCell03(Workbook wb, String sheetName, int rowNum, int columnNum) throws Exception {
    Object[][] result = null;
    int cellType = 0;
    HSSFRow row = null;
    HSSFCell cell = null;
    result = new Object[rowNum][columnNum];
    HSSFSheet sheet = (HSSFSheet) wb.getSheet(sheetName);
    // 开始循环遍历单元格，取出数据放入result的二维数组中
    for (int i = START_ROW; i < rowNum; i++) {
        row = sheet.getRow(i);
        cellType = -1;
        Object cellValue = null;
        // 确保此行有数据
        if (row == null) {
            result[i] = null;
            continue;
        }
        boolean rowEmptyFlg = true;
        for (int j = 0; j < columnNum; j++) {
            cell = row.getCell(j);
            if (cell != null) {
                rowEmptyFlg = false;
                // 判断单元格内数据类型，
                try {
                    // 數字型必須要先檢測，他既不會走if也不會走catch
                    cellType = cell.getCellType();
                    if (DateUtil.isCellDateFormatted(cell)) {
                        // 日期格式需要这样来判断，下面的方法判断不了
                        cellType = CELL_TYPE_DATE;
                    }
                } catch (IllegalStateException e) {
                    cellType = cell.getCellType();
                }
                if (cellType == CELL_TYPE_NULL) {
                    // 空值型
                    result[i][j] = null;
                    continue;
                } else if (cellType == CELL_TYPE_NUM) {
                    // 数值型，避免科学计数法
                    DecimalFormat df = new DecimalFormat("0");
                    cellValue = df.format(cell.getNumericCellValue());
                } else if (cellType == CEll_TYPE_STRING) {
                    // 字符串型
                    cellValue = cell.getStringCellValue();
                } else if (cellType == CELL_TYPE_BOOLEAN) {
                    // boolean型
                    cellValue = new Boolean(cell.getBooleanCellValue());
                } else if (cellType == CELL_TYPE_DATE) {
                    // 日期类型
                    double value = cell.getNumericCellValue();
                    cellValue = DateUtil.getJavaDate(value);
                } else if (cellType == CEll_TYPE_EXPRESSION) {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                    if ("NaN".equals(cellValue)) {
                        cellValue = String.valueOf(cell.getRichStringCellValue());
                    }
                }
                result[i][j] = cellValue;
            } else {
                result[i][j] = null;
            }
        }
        // 如何该行每一列都没有数据，则该行为空
        if (rowEmptyFlg) {
            result[i] = null;
        }
    }
    return result;
}