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:com.inet.web.service.mail.utils.ImportUtil.java

License:Open Source License

/**
 * get String//  w  w  w. jav  a 2 s. c om
 * @param rowData
 * @param index
 * @return
 */
private static String getString(HSSFRow rowData, int index) {
    HSSFCell cell = rowData.getCell(index);
    if (cell == null) {
        return StringService.EMPTY_STRING;
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
        return cell.getStringCellValue();
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
        return String.valueOf((new Double(cell.getNumericCellValue())).longValue());
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
        return StringService.EMPTY_STRING;
    } else {
        return StringService.EMPTY_STRING;
    }
}

---

From source file:com.inet.web.service.mail.utils.ImportUtil.java

License:Open Source License

/**
 * get double//w  w w .j  a v a  2  s .  c o m
 * @param rowData
 * @param index
 * @param def
 * @return
 */
@SuppressWarnings("unused")
private static double getDouble(HSSFRow rowData, int index, int def) {
    HSSFCell cell = rowData.getCell(index);

    if (cell == null) {
        return def;
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
        return cell.getNumericCellValue();
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
        return Double.valueOf(cell.getNumericCellValue());
    } else {
        return 0;
    }
}

---

From source file:com.inet.web.service.mail.utils.ImportUtil.java

License:Open Source License

/**
 * get Long//  w w w  .j a v a 2  s  .  c o m
 * @param rowData
 * @param index
 * @return
 */
private static long getLong(HSSFRow rowData, int index) {
    HSSFCell cell = rowData.getCell(index);

    if (cell == null) {
        return 0L;
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
        return (new Double(cell.getNumericCellValue())).longValue();
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
        return (new Double(cell.getStringCellValue())).longValue();
    } else {
        return 0L;
    }
}

---

From source file:com.isotrol.impe3.idx.oc.extractors.ExtractorMsExcel.java

License:Open Source License

/**
 * Extracts the text from the Excel table content.<p>
 * //from ww w  .  ja v  a2s.  com
 * @param in the document input stream
 * @return the extracted text
 * @throws IOException if something goes wring
 * @deprecated
 */
protected String extractTableContent(InputStream in) throws IOException {

    HSSFWorkbook excelWb = new HSSFWorkbook(in);
    StringBuffer result = new StringBuffer(4096);

    int numberOfSheets = excelWb.getNumberOfSheets();

    for (int i = 0; i < numberOfSheets; i++) {
        HSSFSheet sheet = excelWb.getSheetAt(i);
        int numberOfRows = sheet.getPhysicalNumberOfRows();
        if (numberOfRows > 0) {

            if (excelWb.getSheetName(i) != null && !excelWb.getSheetName(i).trim().equals("")) {
                // append sheet name to content
                if (i > 0) {
                    result.append("\n\n");
                }
                result.append(excelWb.getSheetName(i).trim());
                result.append(":\n\n");
            }

            Iterator<?> rowIt = sheet.rowIterator();
            while (rowIt.hasNext()) {
                HSSFRow row = (HSSFRow) rowIt.next();
                if (row != null) {
                    boolean hasContent = false;
                    Iterator<?> it = row.cellIterator();
                    while (it.hasNext()) {
                        HSSFCell cell = (HSSFCell) it.next();
                        String text = null;
                        try {
                            switch (cell.getCellType()) {
                            case HSSFCell.CELL_TYPE_BLANK:
                            case HSSFCell.CELL_TYPE_ERROR:
                                // ignore all blank or error cells
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                text = Double.toString(cell.getNumericCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN:
                                text = Boolean.toString(cell.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_STRING:
                            default:
                                text = cell.getStringCellValue();
                                break;
                            }
                        } catch (Exception e) {
                            // ignore this cell
                        }
                        if ((text != null) && (text.length() != 0)) {
                            result.append(text.trim());
                            result.append(' ');
                            hasContent = true;
                        }
                    }
                    if (hasContent) {
                        // append a newline at the end of each row that has content
                        result.append('\n');
                    }
                }
            }
        }
    }

    return result.toString();
}

---

From source file:com.jitendrasinghnz.excelreadutility.ExcelReadStringArrayXSL.java

License:Open Source License

public String convertCellToString(HSSFCell cell) {
    int type;/*from  w w  w  .jav  a2 s  .  co m*/
    if (cell == null) {
        type = Cell.CELL_TYPE_BLANK;
    } else {
        type = cell.getCellType();
    }
    Object result;
    switch (type) {
    case Cell.CELL_TYPE_STRING:
        result = cell.getStringCellValue();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        result = cell.getNumericCellValue();
        break;
    case Cell.CELL_TYPE_BLANK:
        result = "";
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        result = cell.getBooleanCellValue();
        break;
    case Cell.CELL_TYPE_ERROR:
        result = cell.getErrorCellValue();
        break;
    case Cell.CELL_TYPE_FORMULA:
        result = cell.getCellFormula();
        break;

    default:
        throw new RuntimeException("There are no support fot the cell type");
    }
    return result.toString();
}

---

From source file:com.krawler.esp.fileparser.excel.MsExcelParser.java

License:Open Source License

public String extractText(String filepath) throws Exception {

    InputStream input = new BufferedInputStream(new FileInputStream(filepath));
    String resultText = "";
    HSSFWorkbook wb = new HSSFWorkbook(input);
    if (wb == null) {
        return resultText;
    }// ww w . j  a v a2s  .c  o  m

    HSSFSheet sheet;
    HSSFRow row;
    HSSFCell cell;
    int sNum = 0;
    int rNum = 0;
    int cNum = 0;

    sNum = wb.getNumberOfSheets();

    for (int i = 0; i < sNum; i++) {
        if ((sheet = wb.getSheetAt(i)) == null) {
            continue;
        }
        rNum = sheet.getLastRowNum();

        for (int j = 0; j <= rNum; j++) {
            if ((row = sheet.getRow(j)) == null) {
                continue;
            }
            cNum = row.getLastCellNum();

            for (int k = 0; k < cNum; k++) {
                try {
                    if ((cell = row.getCell((short) k)) != null) {
                        /*
                         * if(HSSFDateUtil.isCellDateFormatted(cell) ==
                         * true) { resultText +=
                         * cell.getDateCellValue().toString() + " "; } else
                         */
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                            resultText += "           ";

                        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            resultText += cell.getRichStringCellValue().toString() + " ";
                        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            Double d = new Double(cell.getNumericCellValue());
                            resultText += d.toString() + " ";
                        }

                        /*
                         * else if(cell.getCellType() ==
                         * HSSFCell.CELL_TYPE_FORMULA){ resultText +=
                         * cell.getCellFormula() + " "; }
                         */
                    }
                } catch (Exception ex) {

                }
            }
            resultText += "\n";
        }
    }
    if (input != null) {
        input.close();
    }
    return resultText;
}

---

From source file:com.krawler.esp.fileparser.excel.MsExcelParser.java

License:Open Source License

public String getFormatedJSON(String filepath) throws Exception {
    InputStream input = new BufferedInputStream(new FileInputStream(filepath));
    JSONObject jobj = new JSONObject();
    HSSFWorkbook wb = new HSSFWorkbook(input);
    if (wb == null) {
        return ("\"data\":[]");
    }/*  w w  w  .jav  a  2  s  . c  om*/
    HSSFSheet sheet;
    HSSFRow row;
    HSSFCell cell;
    int sNum = 0;
    int rNum = 0;
    int cNum = 0;

    sNum = wb.getNumberOfSheets();

    for (int i = 0; i < sNum; i++) {
        if ((sheet = wb.getSheetAt(i)) == null) {
            continue;
        }
        rNum = sheet.getLastRowNum();

        for (int j = 0; j <= rNum; j++) {
            if ((row = sheet.getRow(j)) == null) {
                continue;
            }
            cNum = row.getLastCellNum();
            JSONObject temp = new JSONObject();
            for (int k = 0; k < cNum; k++) {
                try {
                    if ((cell = row.getCell((short) k)) != null) {

                        if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                            temp.put("cell" + cell.getCellNum(), "");

                        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            temp.put("cell" + cell.getCellNum(), cell.getRichStringCellValue().toString());
                        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            Double d = new Double(cell.getNumericCellValue());
                            temp.put("cell" + cell.getCellNum(), d.toString());
                        }
                    }

                } catch (Exception ex) {

                }
            }
            jobj.append("data", temp);
            temp = null;
        }
    }
    if (input != null) {
        input.close();
    }
    return jobj.toString();
}

---

From source file:com.krawler.esp.fileparser.excelparser.MsExcelParser.java

License:Open Source License

public String extractText(String filepath) throws Exception {

    InputStream input = new BufferedInputStream(new FileInputStream(filepath));
    String resultText = "";
    HSSFWorkbook wb = new HSSFWorkbook(input);
    if (wb == null) {
        return resultText;
    }//from   w w w.j a  v a2  s . c o m

    HSSFSheet sheet;
    HSSFRow row;
    HSSFCell cell;
    int sNum = 0;
    int rNum = 0;
    int cNum = 0;

    sNum = wb.getNumberOfSheets();

    for (int i = 0; i < sNum; i++) {
        if ((sheet = wb.getSheetAt(i)) == null) {
            continue;
        }
        rNum = sheet.getLastRowNum();

        for (int j = 0; j <= rNum; j++) {
            if ((row = sheet.getRow(j)) == null) {
                continue;
            }
            cNum = row.getLastCellNum();

            for (int k = 0; k < cNum; k++) {
                try {
                    if ((cell = row.getCell((short) k)) != null) {
                        /*
                         * if(HSSFDateUtil.isCellDateFormatted(cell) ==
                         * true) { resultText +=
                         * cell.getDateCellValue().toString() + " "; } else
                         */
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                            resultText += "           ";

                        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            resultText += cell.getRichStringCellValue().toString() + " ";
                        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            Double d = new Double(cell.getNumericCellValue());
                            resultText += d.toString() + " ";
                        }

                        /*
                         * else if(cell.getCellType() ==
                         * HSSFCell.CELL_TYPE_FORMULA){ resultText +=
                         * cell.getCellFormula() + " "; }
                         */
                    }
                } catch (Exception ex) {
                }
            }
            resultText += "\n";
        }
    }
    if (input != null) {
        input.close();
    }
    return resultText;
}

---

From source file:com.krawler.esp.fileparser.excelparser.MsExcelParser.java

License:Open Source License

public String getFormatedJSON(String filepath) throws Exception {
    InputStream input = new BufferedInputStream(new FileInputStream(filepath));
    JSONObject jobj = new JSONObject();
    HSSFWorkbook wb = new HSSFWorkbook(input);
    if (wb == null) {
        return ("\"data\":[]");
    }/*from  w  ww .  jav a 2  s  .c  om*/
    HSSFSheet sheet;
    HSSFRow row;
    HSSFCell cell;
    int sNum = 0;
    int rNum = 0;
    int cNum = 0;

    sNum = wb.getNumberOfSheets();

    for (int i = 0; i < sNum; i++) {
        if ((sheet = wb.getSheetAt(i)) == null) {
            continue;
        }
        rNum = sheet.getLastRowNum();

        for (int j = 0; j <= rNum; j++) {
            if ((row = sheet.getRow(j)) == null) {
                continue;
            }
            cNum = row.getLastCellNum();
            JSONObject temp = new JSONObject();
            for (int k = 0; k < cNum; k++) {
                try {
                    if ((cell = row.getCell((short) k)) != null) {

                        if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                            temp.put("cell" + cell.getCellNum(), "");

                        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            temp.put("cell" + cell.getCellNum(), cell.getRichStringCellValue().toString());
                        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            Double d = new Double(cell.getNumericCellValue());
                            temp.put("cell" + cell.getCellNum(), d.toString());
                        }
                    }

                } catch (Exception ex) {
                }
            }
            jobj.append("data", temp);
            temp = null;
        }
    }
    if (input != null) {
        input.close();
    }
    return jobj.toString();
}

---

From source file:com.krawler.spring.importFunctionality.ImportHandler.java

License:Open Source License

public JSONObject parseXLS(String filename, int sheetNo)
        throws FileNotFoundException, IOException, JSONException {
    JSONObject jobj = new JSONObject();
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
    HSSFSheet sheet = wb.getSheetAt(sheetNo);

    int startRow = 0;
    int maxRow = sheet.getLastRowNum();
    int maxCol = 0;
    int noOfRowsDisplayforSample = 20;
    if (noOfRowsDisplayforSample > sheet.getLastRowNum()) {
        noOfRowsDisplayforSample = sheet.getLastRowNum();
    }//  w  ww  .j  a va 2 s . c o m

    JSONArray jArr = new JSONArray();
    try {
        for (int i = 0; i <= noOfRowsDisplayforSample; i++) {
            HSSFRow row = sheet.getRow(i);
            JSONObject obj = new JSONObject();
            JSONObject jtemp1 = new JSONObject();
            if (row == null) {
                continue;
            }
            if (i == 0) {
                maxCol = row.getLastCellNum();
            }
            for (int cellcount = 0; cellcount < maxCol; cellcount++) {
                HSSFCell cell = row.getCell(cellcount);
                CellReference cref = new CellReference(i, cellcount);
                String colHeader = cref.getCellRefParts()[2];
                String val = null;
                if (cell != null) {
                    switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            val = Double.toString(cell.getNumericCellValue());
                            java.util.Date date1 = HSSFDateUtil.getJavaDate(Double.parseDouble(val));
                            DateFormat sdf = new SimpleDateFormat(df);
                            val = sdf.format(date1);
                        } else {
                            val = dfmt.format(cell.getNumericCellValue());
                        }
                        break;
                    case HSSFCell.CELL_TYPE_STRING:
                        val = cleanHTML(cell.getRichStringCellValue().getString());
                        break;
                    }
                }

                if (i == 0) { // List of Headers (Consider first row as Headers)
                    if (val != null) {
                        jtemp1 = new JSONObject();
                        jtemp1.put("header", val == null ? "" : val);
                        jtemp1.put("index", cellcount);
                        jobj.append("Header", jtemp1);
                    }
                }
                obj.put(colHeader, val);
            }
            //                    if(obj.length()>0){ //Don't show blank row in preview grid[SK]
            jArr.put(obj);
            //                    }
        }
    } catch (Exception ex) {
        Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex);
    }
    jobj.put("startrow", startRow);
    jobj.put("maxrow", maxRow);
    jobj.put("maxcol", maxCol);
    jobj.put("index", sheetNo);
    jobj.put("data", jArr);
    jobj.put("filename", filename);

    jobj.put("msg", "XLS has been successfully uploaded");
    jobj.put("lsuccess", true);
    jobj.put("valid", true);
    return jobj;
}

---

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