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:gov.nih.nci.evs.browser.utils.ExcelUtil.java

License:Open Source License

public static String getHSSFHeader(String file, int sheet) {
    StringBuffer buf = new StringBuffer();
    try {/*from  w ww  . j  av  a2s .com*/
        FileInputStream fis = new FileInputStream(new File(file));
        //Get the workbook instance for XLS file
        HSSFWorkbook workbook = new HSSFWorkbook(fis);
        try {
            fis.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        //Get first sheet from the workbook
        HSSFSheet hSSFSheet = workbook.getSheetAt(sheet);
        HSSFRow row = hSSFSheet.getRow(0);

        int cells = row.getPhysicalNumberOfCells();
        for (int c = 0; c < cells; c++) {
            HSSFCell cell = row.getCell(c);
            String value = null;

            switch (cell.getCellType()) {

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

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

            case HSSFCell.CELL_TYPE_STRING:
                value = cell.getStringCellValue();
                break;

            default:
            }
            buf.append(value);
            if (c < cells - 1) {
                buf.append("|");
            }
        }
    } catch (Exception ex) {
        ex.printStackTrace();
    }
    return buf.toString();
}

---

From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java

License:Open Source License

private void table(final HSSFSheet sheet, int startIndex, int col, String code, boolean cdisc) {
    resolvedValueSetList = new ArrayList();

    if (sheet == null) {
        return;// w  ww. j  a va2 s .c  o  m
    }
    if (sheet.getDrawingPatriarch() != null) {
        final List<HSSFShape> shapes = sheet.getDrawingPatriarch().getChildren();
        for (int i = 0; i < shapes.size(); ++i) {
            if (shapes.get(i) instanceof HSSFPicture) {
                try {
                    // Gain access to private field anchor.
                    final HSSFShape pic = shapes.get(i);
                    final Field f = HSSFShape.class.getDeclaredField("anchor");
                    f.setAccessible(true);
                    final HSSFClientAnchor anchor = (HSSFClientAnchor) f.get(pic);
                    // Store picture cell row, column and picture data.
                    if (!pix.containsKey(anchor.getRow1())) {
                        pix.put(anchor.getRow1(), new HashMap<Short, List<HSSFPictureData>>());
                    }
                    if (!pix.get(anchor.getRow1()).containsKey(anchor.getCol1())) {
                        pix.get(anchor.getRow1()).put(anchor.getCol1(), new ArrayList<HSSFPictureData>());
                    }
                    pix.get(anchor.getRow1()).get(anchor.getCol1())
                            .add(book.getAllPictures().get(((HSSFPicture) pic).getPictureIndex()));
                } catch (final Exception e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }

    out.append("<table id=\"" + "rvs_table" + "\" width=\"915\" class=\"mt\">\n");
    tr(sheet.getRow(0));
    StringBuffer buf = new StringBuffer();
    tr(sheet.getRow(0), buf);
    String t = buf.toString();
    resolvedValueSetList.add(t);
    int rows = sheet.getPhysicalNumberOfRows();

    for (int i = startIndex; i <= rows; i++) {
        HSSFRow row = sheet.getRow(i);
        if (row != null) {
            if (col != -1) {
                HSSFCell cell = row.getCell(col);
                if (cell != null) {
                    String value = null;
                    switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_FORMULA:
                        value = cell.getCellFormula();
                        break;

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

                    case HSSFCell.CELL_TYPE_STRING:
                        value = cell.getStringCellValue();
                        break;

                    default:
                    }
                    if ((cdisc && i == startIndex) || (value != null && value.compareTo(code) == 0)) {
                        tr(row);
                        buf = new StringBuffer();
                        tr(row, buf);
                        t = buf.toString();
                        resolvedValueSetList.add(t);
                    }
                }
            } else {
                tr(row);
                buf = new StringBuffer();
                tr(row, buf);
                t = buf.toString();
                resolvedValueSetList.add(t);
            }
        }
    }
    out.append("</table>\n");
    resolvedValueSetIterator = resolvedValueSetList.listIterator();
}

---

From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java

License:Open Source License

/**
 * (Each Excel sheet cell becomes an HTML table cell) Generates an HTML
 * table cell which has the same font styles, alignments, colours and
 * borders as the Excel cell./*from www  . j  a  v  a2s. c o  m*/
 *
 * @param cell
 *            The Excel cell.
 */

private void td(final HSSFCell cell) {
    int colspan = 1;
    if (colIndex == mergeStart) {
        // First cell in the merging region - set colspan.
        colspan = mergeEnd - mergeStart + 1;
    } else if (colIndex == mergeEnd) {
        // Last cell in the merging region - no more skipped cells.
        mergeStart = -1;
        mergeEnd = -1;
        return;
    } else if (mergeStart != -1 && mergeEnd != -1 && colIndex > mergeStart && colIndex < mergeEnd) {
        // Within the merging region - skip the cell.
        return;
    }
    //KLO 05022018
    //out.append("<td ");

    out.append("<td height=\"15\" ");
    if (colspan > 1) {
        out.append("colspan='").append(colspan).append("' ");
    }
    if (cell == null) {
        out.append("/>\n");
        return;
    }

    out.append("style='");
    final HSSFCellStyle style = cell.getCellStyle();
    // Text alignment
    switch (style.getAlignment()) {
    case CellStyle.ALIGN_LEFT:
        out.append("text-align: left; ");
        break;
    case CellStyle.ALIGN_RIGHT:
        out.append("text-align: right; ");
        break;
    case CellStyle.ALIGN_CENTER:
        out.append("text-align: center; ");
        break;
    default:
        break;
    }

    // Font style, size and weight
    final HSSFFont font = style.getFont(book);
    if (font == null)
        return;
    if (font.getBoldweight() == HSSFFont.BOLDWEIGHT_BOLD) {
        out.append("font-weight: bold; ");
    }
    if (font.getItalic()) {
        out.append("font-style: italic; ");
    }
    if (font.getUnderline() != HSSFFont.U_NONE) {
        out.append("text-decoration: underline; ");
    }
    out.append("font-size: ").append(Math.floor(font.getFontHeightInPoints() * 0.8)).append("pt; ");
    // Cell background and font colours
    final short[] backRGB = style.getFillForegroundColorColor().getTriplet();
    final HSSFColor foreColor = palette.getColor(font.getColor());
    if (foreColor != null) {
        final short[] foreRGB = foreColor.getTriplet();
        if (foreRGB[0] != 0 || foreRGB[1] != 0 || foreRGB[2] != 0) {
            out.append("color: rgb(").append(foreRGB[0]).append(',').append(foreRGB[1]).append(',')
                    .append(foreRGB[2]).append(");");
        }
    }
    if (backRGB[0] != 0 || backRGB[1] != 0 || backRGB[2] != 0) {
        out.append("background-color: rgb(").append(backRGB[0]).append(',').append(backRGB[1]).append(',')
                .append(backRGB[2]).append(");");
    }
    // Border
    if (style.getBorderTop() != HSSFCellStyle.BORDER_NONE) {
        out.append("border-top-style: solid; ");
    }
    if (style.getBorderRight() != HSSFCellStyle.BORDER_NONE) {
        out.append("border-right-style: solid; ");
    }
    if (style.getBorderBottom() != HSSFCellStyle.BORDER_NONE) {
        out.append("border-bottom-style: solid; ");
    }
    if (style.getBorderLeft() != HSSFCellStyle.BORDER_NONE) {
        out.append("border-left-style: solid; ");
    }
    out.append("'>");
    String val = "";
    try {
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            val = cell.getStringCellValue();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            // POI does not distinguish between integer and double, thus:
            final double original = cell.getNumericCellValue(), rounded = Math.round(original);
            if (Math.abs(rounded - original) < 0.00000000000000001) {
                val = String.valueOf((int) rounded);
            } else {
                val = String.valueOf(original);
            }
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            final CellValue cv = evaluator.evaluate(cell);
            if (cv == null)
                return;
            switch (cv.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                out.append(cv.getBooleanValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                out.append(cv.getNumberValue());
                break;
            case Cell.CELL_TYPE_STRING:
                out.append(cv.getStringValue());
                break;
            case Cell.CELL_TYPE_BLANK:
                break;
            case Cell.CELL_TYPE_ERROR:
                break;
            default:
                break;
            }
            break;
        default:
            // Neither string or number? Could be a date.
            try {
                val = sdf.format(cell.getDateCellValue());
            } catch (final Exception e1) {
            }
        }
    } catch (final Exception e) {
        val = e.getMessage();
    }
    if ("null".equals(val)) {
        val = "";
    }
    if (pix.containsKey(rowIndex)) {
        if (pix.get(rowIndex).containsKey(colIndex)) {
            for (final HSSFPictureData pic : pix.get(rowIndex).get(colIndex)) {
                out.append("<img alt='Image in Excel sheet' src='data:");
                out.append(pic.getMimeType());
                out.append(";base64,");
                try {
                    out.append(new String(Base64.encodeBase64(pic.getData()), "US-ASCII"));
                } catch (final UnsupportedEncodingException e) {
                    throw new RuntimeException(e);
                }
                out.append("'/>");
            }
        }
    }

    if (isCode(val) && this.URL != null) {
        val = getHyperLink(val);
    }

    out.append(val);
    out.append("</td>\n");
}

---

From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java

License:Open Source License

private void td(final HSSFCell cell, StringBuffer buf) {
    int colspan = 1;
    if (colIndex == mergeStart) {
        // First cell in the merging region - set colspan.
        colspan = mergeEnd - mergeStart + 1;
    } else if (colIndex == mergeEnd) {
        // Last cell in the merging region - no more skipped cells.
        mergeStart = -1;/*from w w  w.j  a  v  a 2 s .  c  o  m*/
        mergeEnd = -1;
        return;
    } else if (mergeStart != -1 && mergeEnd != -1 && colIndex > mergeStart && colIndex < mergeEnd) {
        // Within the merging region - skip the cell.
        return;
    }

    //KLO 05022018
    //buf.append("<td ");

    buf.append("<td height=\"15\" ");

    if (colspan > 1) {
        buf.append("colspan='").append(colspan).append("' ");
    }
    if (cell == null) {
        buf.append("/>");
        return;
    }
    buf.append("style='");
    final HSSFCellStyle style = cell.getCellStyle();
    // Text alignment
    switch (style.getAlignment()) {
    case CellStyle.ALIGN_LEFT:
        buf.append("text-align: left; ");
        break;
    case CellStyle.ALIGN_RIGHT:
        buf.append("text-align: right; ");
        break;
    case CellStyle.ALIGN_CENTER:
        buf.append("text-align: center; ");
        break;
    default:
        break;
    }
    // Font style, size and weight
    final HSSFFont font = style.getFont(book);
    if (font == null)
        return;
    if (font.getBoldweight() == HSSFFont.BOLDWEIGHT_BOLD) {
        buf.append("font-weight: bold; ");
    }
    if (font.getItalic()) {
        buf.append("font-style: italic; ");
    }
    if (font.getUnderline() != HSSFFont.U_NONE) {
        buf.append("text-decoration: underline; ");
    }
    buf.append("font-size: ").append(Math.floor(font.getFontHeightInPoints() * 0.8)).append("pt; ");

    // Cell background and font colours
    final short[] backRGB = style.getFillForegroundColorColor().getTriplet();
    final HSSFColor foreColor = palette.getColor(font.getColor());
    if (foreColor != null) {
        final short[] foreRGB = foreColor.getTriplet();
        if (foreRGB[0] != 0 || foreRGB[1] != 0 || foreRGB[2] != 0) {
            buf.append("color: rgb(").append(foreRGB[0]).append(',').append(foreRGB[1]).append(',')
                    .append(foreRGB[2]).append(");");

        }
    }
    if (backRGB[0] != 0 || backRGB[1] != 0 || backRGB[2] != 0) {
        buf.append("background-color: rgb(").append(backRGB[0]).append(',').append(backRGB[1]).append(',')
                .append(backRGB[2]).append(");");

    }
    // Border
    if (style.getBorderTop() != HSSFCellStyle.BORDER_NONE) {
        buf.append("border-top-style: solid; ");
    }
    if (style.getBorderRight() != HSSFCellStyle.BORDER_NONE) {
        buf.append("border-right-style: solid; ");
    }
    if (style.getBorderBottom() != HSSFCellStyle.BORDER_NONE) {
        buf.append("border-bottom-style: solid; ");
    }
    if (style.getBorderLeft() != HSSFCellStyle.BORDER_NONE) {
        buf.append("border-left-style: solid; ");
    }
    buf.append("'>");
    String val = "";
    try {
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            val = cell.getStringCellValue();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            // POI does not distinguish between integer and double, thus:
            final double original = cell.getNumericCellValue(), rounded = Math.round(original);
            if (Math.abs(rounded - original) < 0.00000000000000001) {
                val = String.valueOf((int) rounded);
            } else {
                val = String.valueOf(original);
            }
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            final CellValue cv = evaluator.evaluate(cell);
            if (cv == null)
                return;
            switch (cv.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                buf.append(cv.getBooleanValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                buf.append(cv.getNumberValue());
                break;
            case Cell.CELL_TYPE_STRING:
                buf.append(cv.getStringValue());
                break;
            case Cell.CELL_TYPE_BLANK:
                break;
            case Cell.CELL_TYPE_ERROR:
                break;
            default:
                break;
            }
            break;
        default:
            // Neither string or number? Could be a date.
            try {
                val = sdf.format(cell.getDateCellValue());
            } catch (final Exception e1) {
            }
        }
    } catch (final Exception e) {
        val = e.getMessage();
    }
    if ("null".equals(val)) {
        val = "";
    }
    if (pix.containsKey(rowIndex)) {
        if (pix.get(rowIndex).containsKey(colIndex)) {
            for (final HSSFPictureData pic : pix.get(rowIndex).get(colIndex)) {
                buf.append("<img alt='Image in Excel sheet' src='data:");
                buf.append(pic.getMimeType());
                buf.append(";base64,");

                try {
                    buf.append(new String(Base64.encodeBase64(pic.getData()), "US-ASCII"));

                } catch (final UnsupportedEncodingException e) {
                    throw new RuntimeException(e);
                }
                buf.append("'/>");
            }
        }
    }

    if (isCode(val) && this.URL != null) {
        val = getHyperLink(val);
    }
    buf.append(val);
    buf.append("</td>");

}

---

From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java

License:Open Source License

private String getCellData(final HSSFCell cell) {
    if (cell == null)
        return null;
    int colspan = 1;
    if (colIndex == mergeStart) {
        // First cell in the merging region - set colspan.
        colspan = mergeEnd - mergeStart + 1;
    } else if (colIndex == mergeEnd) {
        // Last cell in the merging region - no more skipped cells.
        mergeStart = -1;/*from w w  w.j a  v a  2 s. c  o m*/
        mergeEnd = -1;
        return null;
    } else if (mergeStart != -1 && mergeEnd != -1 && colIndex > mergeStart && colIndex < mergeEnd) {
        // Within the merging region - skip the cell.
        return null;
    }
    //StringBuffer buf = new StringBuffer();
    String val = "";
    try {
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            val = cell.getStringCellValue();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            // POI does not distinguish between integer and double, thus:
            final double original = cell.getNumericCellValue(), rounded = Math.round(original);
            if (Math.abs(rounded - original) < 0.00000000000000001) {
                val = String.valueOf((int) rounded);
            } else {
                val = String.valueOf(original);
            }
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            final CellValue cv = evaluator.evaluate(cell);
            if (cv == null)
                return null;
            switch (cv.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                out.append(cv.getBooleanValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                out.append(cv.getNumberValue());
                break;
            case Cell.CELL_TYPE_STRING:
                out.append(cv.getStringValue());
                break;
            case Cell.CELL_TYPE_BLANK:
                break;
            case Cell.CELL_TYPE_ERROR:
                break;
            default:
                break;
            }
            break;
        default:
            // Neither string or number? Could be a date.
            try {
                val = sdf.format(cell.getDateCellValue());
            } catch (final Exception e1) {
            }
        }
    } catch (final Exception e) {
        val = e.getMessage();
    }
    if ("null".equals(val)) {
        val = "";
    }
    return val;
}

---

From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.ExcelUtility.java

License:BSD License

public static int getInt(HSSFSheet sheet, int row, short col) {
    HSSFRow hssfRow = getRow(sheet, row);

    if (hssfRow == null) {
        return 0;
    }/*ww w  . j  ava2s  . c  o m*/

    HSSFCell cell = getRow(sheet, row).getCell(col);

    if (isNull(cell)) {
        return 0;
    }
    return (int) Math.round(cell.getNumericCellValue());
}

---

From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.ExcelUtility.java

License:BSD License

public static Integer getInteger(HSSFSheet sheet, int row, short col) {
    HSSFRow hssfRow = getRow(sheet, row);

    if (hssfRow == null) {
        return null;
    }//w w  w .  j av  a  2  s  .  c o  m

    HSSFCell cell = getRow(sheet, row).getCell(col);

    if (isNull(cell)) {
        return null;
    }
    return new Integer((int) Math.round(cell.getNumericCellValue()));
}

---

From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.ExcelUtility.java

License:BSD License

public static Long getLong(HSSFSheet sheet, int row, short col) {
    HSSFRow hssfRow = getRow(sheet, row);

    if (hssfRow == null) {
        return null;
    }//from www  .j  av  a 2s  . c  o  m

    HSSFCell cell = getRow(sheet, row).getCell(col);

    if (isNull(cell)) {
        return null;
    }
    long round = Math.round(cell.getNumericCellValue());
    return new Long(round);
}

---

From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.ExcelUtility.java

License:BSD License

public static Double getDouble(HSSFSheet sheet, int row, short col) {
    HSSFRow hssfRow = getRow(sheet, row);

    if (hssfRow == null) {
        return null;
    }/* ww w .j  ava  2  s  .c  o  m*/

    HSSFCell cell = getRow(sheet, row).getCell(col);

    if (isNull(cell)) {
        return null;
    }
    return new Double(cell.getNumericCellValue());
}

---

From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.ExcelUtility.java

License:BSD License

public static Float getFloat(HSSFSheet sheet, int row, short col) {
    HSSFRow hssfRow = getRow(sheet, row);

    if (hssfRow == null) {
        return null;
    }/*from  w w  w . j a  v  a 2s.  c  o  m*/

    HSSFCell cell = getRow(sheet, row).getCell(col);

    if (isNull(cell)) {
        return null;
    }
    return new Float(cell.getNumericCellValue());
}

---

• «
• 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
• »