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

Example 66 with HSSFSheet

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

the class TestFixed method setUp.

@Before
public void setUp() throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    try {
        HSSFSheet sheet = wb.createSheet("new sheet");
        cell11 = sheet.createRow(0).createCell(0);
        cell11.setCellType(CellType.FORMULA);
        evaluator = new HSSFFormulaEvaluator(wb);
    } finally {
        wb.close();
    }
}

Example 67 with HSSFSheet

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

the class TestIPMT method testFromFile.

/**
     *  from http://office.microsoft.com/en-001/excel-help/ipmt-HP005209145.aspx
     */
public void testFromFile() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("finance.xls");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    HSSFSheet example1 = wb.getSheet("IPMT");
    HSSFCell ex1cell1 = example1.getRow(6).getCell(0);
    fe.evaluate(ex1cell1);
    assertEquals(-22.41, ex1cell1.getNumericCellValue(), 0.1);
    HSSFCell ex1cell2 = example1.getRow(7).getCell(0);
    fe.evaluate(ex1cell2);
    assertEquals(-292.45, ex1cell2.getNumericCellValue(), 0.1);
}

Example 68 with HSSFSheet

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

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

Example 70 with HSSFSheet

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

the class RrdMetricsRetrieverTest method testMetricsXlsDataWithGauge.

@Test
public void testMetricsXlsDataWithGauge() throws Exception {
    String rrdFilename = TEST_DIR + "queryCount_Gauge" + RRD_FILE_EXTENSION;
    long endTime = new RrdFileBuilder().rrdFileName(rrdFilename).dsType(DsType.GAUGE).build();
    MetricsRetriever metricsRetriever = new RrdMetricsRetriever();
    OutputStream os = metricsRetriever.createXlsData("queryCount", rrdFilename, START_TIME, endTime);
    InputStream xls = new ByteArrayInputStream(((ByteArrayOutputStream) os).toByteArray());
    assertThat(xls, not(nullValue()));
    HSSFWorkbook wb = new HSSFWorkbook(xls);
    assertThat(wb.getNumberOfSheets(), equalTo(1));
    HSSFSheet sheet = wb.getSheet("Query Count");
    if (null != sheet) {
        assertThat(sheet, not(nullValue()));
        verifyWorksheet(sheet, "Query Count", 6, false);
    } else {
        fail();
    }
}