Example usage for org.apache.poi.hssf.usermodel HSSFCell getNumericCellValue

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFCell getNumericCellValue.

Prototype

public double getNumericCellValue() 

Source Link

Document

Get the value of the cell as a number.

Usage

From source file:org.orbeon.oxf.util.XLSUtils.java

License:Open Source License

public static void copyCell(HSSFWorkbook workbook, HSSFCell destination, HSSFCell source) {

    // Copy cell content
    destination.setCellType(source.getCellType());
    switch (source.getCellType()) {
    case HSSFCell.CELL_TYPE_BOOLEAN:
        destination.setCellValue(source.getBooleanCellValue());
        break;// w w  w  . ja v a  2  s  . c o m
    case HSSFCell.CELL_TYPE_FORMULA:
    case HSSFCell.CELL_TYPE_STRING:
        destination.setCellValue(source.getStringCellValue());
        break;
    case HSSFCell.CELL_TYPE_NUMERIC:
        destination.setCellValue(source.getNumericCellValue());
        break;
    }

    // Copy cell style
    HSSFCellStyle sourceCellStyle = source.getCellStyle();
    HSSFCellStyle destinationCellStyle = workbook.createCellStyle();
    destinationCellStyle.setAlignment(sourceCellStyle.getAlignment());
    destinationCellStyle.setBorderBottom(sourceCellStyle.getBorderBottom());
    destinationCellStyle.setBorderLeft(sourceCellStyle.getBorderLeft());
    destinationCellStyle.setBorderRight(sourceCellStyle.getBorderRight());
    destinationCellStyle.setBorderTop(sourceCellStyle.getBorderTop());
    destinationCellStyle.setBottomBorderColor(sourceCellStyle.getBottomBorderColor());
    destinationCellStyle.setDataFormat(sourceCellStyle.getDataFormat());
    destinationCellStyle.setFillBackgroundColor(sourceCellStyle.getFillForegroundColor());
    destinationCellStyle.setFillForegroundColor(sourceCellStyle.getFillForegroundColor());
    destinationCellStyle.setFillPattern(sourceCellStyle.getFillPattern());
    destinationCellStyle.setFont(workbook.getFontAt(sourceCellStyle.getFontIndex()));
    destinationCellStyle.setHidden(sourceCellStyle.getHidden());
    destinationCellStyle.setIndention(sourceCellStyle.getIndention());
    destinationCellStyle.setLeftBorderColor(sourceCellStyle.getLeftBorderColor());
    destinationCellStyle.setLocked(sourceCellStyle.getLocked());
    destinationCellStyle.setRightBorderColor(sourceCellStyle.getRightBorderColor());
    destinationCellStyle.setRotation(sourceCellStyle.getRotation());
    destinationCellStyle.setTopBorderColor(sourceCellStyle.getTopBorderColor());
    destinationCellStyle.setVerticalAlignment(sourceCellStyle.getVerticalAlignment());
    destinationCellStyle.setWrapText(sourceCellStyle.getWrapText());
    destination.setCellStyle(destinationCellStyle);
}

---

From source file:org.projectforge.excel.ExcelImport.java

License:Open Source License

/**
 * convert the cell-value to the type in the bean.
 * //from w  ww .j  a va2 s.co m
 * @param cell the cell containing an arbitrary value
 * @param destClazz the target class
 * @return a String, Boolean, Date or BigDecimal
 */
private Object toNativeType(final HSSFCell cell, final Class<?> destClazz) {
    if (cell == null) {
        return null;
    }
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_NUMERIC:
        log.debug("using numeric");
        if (Date.class.isAssignableFrom(destClazz)) {
            return cell.getDateCellValue();
        }
        String strVal = String.valueOf(cell.getNumericCellValue());
        strVal = strVal.replaceAll("\\.0*$", "");
        return ConvertUtils.convert(strVal, destClazz);
    case HSSFCell.CELL_TYPE_BOOLEAN:
        log.debug("using boolean");
        return Boolean.valueOf(cell.getBooleanCellValue());
    case HSSFCell.CELL_TYPE_STRING:
        log.debug("using string");
        strVal = StringUtils.trimToNull(cell.getStringCellValue());
        return ConvertUtils.convert(strVal, destClazz);
    case HSSFCell.CELL_TYPE_BLANK:
        return null;
    case HSSFCell.CELL_TYPE_FORMULA:
        return new Formula(cell.getCellFormula());
    default:
        return StringUtils.trimToNull(cell.getStringCellValue());
    }
}

---

From source file:org.rti.zcore.dar.utils.PoiUtils.java

License:Apache License

/**
 * This utility is a version of HSSF.main that does not use deprecated methods.
 * It is helpful in figuring out what row a filed is on when outputting Excel files via POI.
 * @param pathExcelMaster/*from w ww. j a v a  2 s  .  c  o m*/
 */
public static void testExcelOutput(String pathExcelMaster) {

    try {
        //HSSF hssf = new HSSF(args[ 0 ]);

        System.out.println("Data dump:\n");
        //HSSFWorkbook wb = hssf.hssfworkbook;
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(pathExcelMaster));
        HSSFWorkbook wb = new HSSFWorkbook(fs);

        for (int k = 0; k < wb.getNumberOfSheets(); k++) {
            System.out.println("Sheet " + k);
            HSSFSheet sheet = wb.getSheetAt(k);
            int rows = sheet.getPhysicalNumberOfRows();

            for (int r = 0; r < rows; r++) {
                //HSSFRow row   = sheet.getPhysicalRowAt(r);
                HSSFRow row = sheet.getRow(r);
                if (row != null) {
                    int cells = row.getPhysicalNumberOfCells();
                    System.out.println("ROW " + row.getRowNum());
                    for (int c = 0; c < cells; c++) {
                        //HSSFCell cell  = row.getPhysicalCellAt(c);
                        HSSFCell cell = row.getCell(c);
                        String value = null;
                        if (cell != null) {
                            switch (cell.getCellType()) {

                            case HSSFCell.CELL_TYPE_FORMULA:
                                value = "FORMULA ";
                                value = "FORMULA " + cell.getCellFormula();
                                break;

                            case HSSFCell.CELL_TYPE_NUMERIC:
                                value = "NUMERIC value=" + cell.getNumericCellValue();
                                break;

                            case HSSFCell.CELL_TYPE_STRING:
                                //value = "STRING value=" + cell.getStringCellValue();
                                HSSFRichTextString str = cell.getRichStringCellValue();
                                value = "STRING value=" + str;
                                break;

                            default:
                            }
                            //System.out.println("CELL col=" + cell.getCellNum()  + " VALUE=" + value);
                            System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value);
                        }
                    }
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }

}

---

From source file:org.sakaiproject.search.component.adapter.contenthosting.XLContentDigester.java

License:Educational Community License

public void loadContent(Writer writer, ContentResource contentResource) {
    if (contentResource != null && contentResource.getContentLength() > maxDigestSize) {
        throw new RuntimeException(
                "Attempt to get too much content as a string on " + contentResource.getReference());
    }/* w  ww.ja  v a 2  s .c  o  m*/
    if (contentResource == null) {
        throw new RuntimeException("Null contentResource passed the loadContent");
    }

    InputStream contentStream = null;
    try {
        contentStream = contentResource.streamContent();

        POIFSFileSystem fs = new POIFSFileSystem(contentStream);
        HSSFWorkbook workbook = new HSSFWorkbook(fs);

        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            HSSFSheet sheet = workbook.getSheetAt(i);

            Iterator<Row> rows = sheet.rowIterator();
            while (rows.hasNext()) {
                HSSFRow row = (HSSFRow) rows.next();

                Iterator<Cell> cells = row.cellIterator();
                while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell) cells.next();
                    switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        String num = Double.toString(cell.getNumericCellValue()).trim();
                        if (num.length() > 0) {
                            writer.write(num + " ");
                        }
                        break;
                    case HSSFCell.CELL_TYPE_STRING:
                        String text = cell.getStringCellValue().trim();
                        if (text.length() > 0) {
                            writer.write(text + " ");
                        }
                        break;
                    }
                }
            }
        }

    } catch (Exception e) {
        throw new RuntimeException("Failed to read content for indexing ", e);
    } finally {
        if (contentStream != null) {
            try {
                contentStream.close();
            } catch (IOException e) {
                log.debug(e);
            }
        }
    }

}

---

From source file:org.sakaiproject.tool.gradebook.ui.SpreadsheetUploadBean.java

License:Educational Community License

private String fromHSSFRowtoCSV(HSSFRow row) {
    StringBuffer csvRow = new StringBuffer();
    int l = row.getLastCellNum();
    for (int i = 0; i < l; i++) {
        HSSFCell cell = row.getCell((short) i);
        String cellValue = "";
        if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
            cellValue = "";
        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
            cellValue = "\"" + cell.getStringCellValue() + "\"";
        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
            double value = cell.getNumericCellValue();
            cellValue = getNumberFormat().format(value);
            cellValue = "\"" + cellValue + "\"";
        }//from   w ww  . ja v a 2s.  c o  m

        csvRow.append(cellValue);

        if (i < l) {
            csvRow.append(getCsvDelimiter().toCharArray()[0]);
        }
    }
    return csvRow.toString();

}

---

From source file:org.seasar.dbflute.helper.io.xls.DfTableXlsReader.java

License:Apache License

protected Object extractCellValue(DfDataTable table, int columnIndex, HSSFRow row, HSSFCell cell) {
    if (cell == null) {
        return isEmptyStringTarget(table, columnIndex) ? "" : null;
    }//from w  w w.j  a  va 2s.  c  o  m
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_NUMERIC:
        if (isCellDateFormatted(cell)) {
            return DfTypeUtil.toTimestamp(cell.getDateCellValue());
        }
        final double numericCellValue = cell.getNumericCellValue();
        if (isInt(numericCellValue)) {
            return new BigDecimal((int) numericCellValue);
        }
        return new BigDecimal(Double.toString(numericCellValue));
    case HSSFCell.CELL_TYPE_STRING:
        return processRichStringCellValue(table, columnIndex, row, cell);
    case HSSFCell.CELL_TYPE_BOOLEAN:
        boolean b = cell.getBooleanCellValue();
        return Boolean.valueOf(b);
    default:
        return isEmptyStringTarget(table, columnIndex) ? "" : null;
    }
}

---

From source file:org.seasar.dbflute.helper.io.xls.DfXlsReader.java

License:Apache License

public Object getValue(int columnIndex, HSSFCell cell, DfDataTable table) {
    if (cell == null) {
        if (isEmptyStringTarget(columnIndex, table)) {
            return "\"\""; // for preventing trimming later
        } else {//  w w w . ja v a2 s .  co m
            return null;
        }
    }
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_NUMERIC:
        if (isCellDateFormatted(cell)) {
            return DfTypeUtil.toTimestamp(cell.getDateCellValue());
        }
        final double numericCellValue = cell.getNumericCellValue();
        if (isInt(numericCellValue)) {
            return new BigDecimal((int) numericCellValue);
        }
        return new BigDecimal(Double.toString(numericCellValue));
    case HSSFCell.CELL_TYPE_STRING:
        String s = cell.getRichStringCellValue().getString();
        if (s != null) {
            if (isNotTrimTarget(cell, table)) {
                if (s.length() != s.trim().length()) {
                    s = "\"" + s + "\""; // for preventing trimming later
                }
            } else {
                s = Srl.rtrim(s);
            }
        }
        if ("".equals(s)) {
            s = null;
        }
        if (isEmptyStringTarget(columnIndex, table) && s == null) {
            s = "\"\""; // for preventing trimming later
        }
        if (isCellBase64Formatted(cell)) {
            return DfTypeUtil.decodeAsBase64(s);
        }
        return s;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        boolean b = cell.getBooleanCellValue();
        return Boolean.valueOf(b);
    default:
        if (isEmptyStringTarget(columnIndex, table)) {
            return "\"\"";
        } else {
            return null;
        }
    }
}

---

From source file:org.seasar.extension.dataset.impl.XlsReader.java

License:Apache License

/**
 * ????//from   w w w.j  a va 2s.co m
 * 
 * @param cell
 *            
 * @return ?
 */
public Object getValue(HSSFCell cell) {
    if (cell == null) {
        return null;
    }
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_NUMERIC:
        if (isCellDateFormatted(cell)) {
            return TimestampConversionUtil.toTimestamp(cell.getDateCellValue());
        }
        final double numericCellValue = cell.getNumericCellValue();
        if (isInt(numericCellValue)) {
            return new BigDecimal((int) numericCellValue);
        }
        return new BigDecimal(Double.toString(numericCellValue));
    case HSSFCell.CELL_TYPE_STRING:
        String s = cell.getRichStringCellValue().getString();
        if (s != null) {
            s = StringUtil.rtrim(s);
            if (!trimString && s.length() > 1 && s.startsWith("\"") && s.endsWith("\"")) {
                s = s.substring(1, s.length() - 1);
            }
        }
        if ("".equals(s)) {
            s = null;
        }
        if (isCellBase64Formatted(cell)) {
            return Base64Util.decode(s);
        }
        return s;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        boolean b = cell.getBooleanCellValue();
        return Boolean.valueOf(b);
    default:
        return null;
    }
}

---

From source file:org.seasar.fisshplate.core.element.ElTest.java

License:Apache License

() throws Exception{
    HSSFWorkbook template = getTemplate("/ElTest.xls");
    WorkbookWrapper workbook = new WorkbookWrapper(template);

    Map<String, Object> data = new HashMap<String, Object>();
    data.put("code", "01234");
    data.put("num", new Integer(-1234));

    FPContext context = new FPContext(template.getSheetAt(0),data);

    CellWrapper cell0 = workbook.getSheetAt(0).getRow(0).getCell(0);//${code}
    CellWrapper cell1 = workbook.getSheetAt(0).getRow(0).getCell(1);//${num}

    el = new El(new GenericCell(cell0));
    el.merge(context);//from ww  w.  ja va  2  s. co m
    el = new El(new GenericCell(cell1));
    el.merge(context);

    HSSFCell actual = template.getSheetAt(0).getRow(0).getCell(0);
    assertEquals("celltype",HSSFCell.CELL_TYPE_STRING, actual.getCellType());
    assertEquals("value", "01234", actual.getRichStringCellValue().getString());

    actual = template.getSheetAt(0).getRow(0).getCell(1);
    assertEquals("celltype",HSSFCell.CELL_TYPE_NUMERIC, actual.getCellType());
    assertEquals("value",-1234D, actual.getNumericCellValue(),0D);
}

---

From source file:org.sns.tool.data.DataGeneratorSources.java

License:Open Source License

protected void readCitiesAndPopulations(final HSSFSheet sheet) {
    String currentState = "UNKNOWN";
    int rowNum = 1;
    while (true) {
        final HSSFRow row = sheet.getRow(rowNum);
        if (row == null)
            break;

        final HSSFCell nameCell = row.getCell((short) 0);
        final String name = nameCell.getStringCellValue();
        if (name == null || name.trim().length() == 0)
            break;

        final HSSFCell zipCodeCell = row.getCell((short) 1);
        if (zipCodeCell == null || zipCodeCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
            // states are on a line by themselves so save the current state and move on
            currentState = name;//from   w ww . j a va2s . c  om
            rowNum++;
            continue;
        }

        final int zipCode = (int) zipCodeCell.getNumericCellValue();
        final int population = (int) row.getCell((short) 2).getNumericCellValue();
        final City city = new City(name, currentState, zipCode, population);

        largestCities.add(city);
        rowNum++;
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• »