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

List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getNumericCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFCell
  6. 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:database_lab1.studentExcel.java

public void studentExcelFile(String filename) throws IOException, SQLException {
    FileInputStream fis = null;/*w  ww  .j  a v a 2 s  .  co m*/
    try {
        fis = new FileInputStream(filename);
        HSSFWorkbook workbook = new HSSFWorkbook(fis);
        HSSFSheet sheet = workbook.getSheetAt(0);
        Iterator rowIter = sheet.rowIterator();
        int firstrow = 0;
        while (rowIter.hasNext()) {
            HSSFRow myRow = (HSSFRow) rowIter.next();
            Iterator cellIter = myRow.cellIterator();
            Vector<String> cellStoreVector = new Vector<String>();
            while (cellIter.hasNext()) {
                HSSFCell myCell = (HSSFCell) cellIter.next();
                String cellvalue;
                if (myCell.getCellType() == 1)
                    cellvalue = myCell.getStringCellValue();
                else
                    cellvalue = (int) myCell.getNumericCellValue() + "";
                cellStoreVector.addElement(cellvalue);
                System.out.print(cellvalue + "  ");

            }
            System.out.println();
            if (firstrow != 0) {
                dbConnection db = new dbConnection("jdbc:mysql://localhost:3306/database_lab1", "root",
                        "password");
                String insert = "INSERT INTO student  VALUES (?,?,?,?,?,?);";
                PreparedStatement ps = db.getConnection().prepareStatement(insert);//createStatement().executeUpdate(insert);
                ps.setString(1, cellStoreVector.get(0));
                ps.setString(2, cellStoreVector.get(1));
                ps.setString(3, cellStoreVector.get(2));
                ps.setString(4, cellStoreVector.get(3));
                ps.setString(5, cellStoreVector.get(4));
                ps.setString(6, cellStoreVector.get(5));
                Boolean rs = ps.execute();
            }
            firstrow++;

        }
    } catch (IOException e) {

        e.printStackTrace();

    } finally {
        if (fis != null) {
            fis.close();
        }
    }
}

From source file:de.alpharogroup.export.excel.poi.ExportExcelExtensions.java

License:Open Source License

/**
 * Exportiert die bergebene excel-Datei in eine Liste mit zweidimensionalen Arrays fr jeweils
 * ein sheet in der excel-Datei./*ww w.  jav  a2 s  .  c  o  m*/
 *
 * @param excelSheet
 *            Die excel-Datei.
 * @return Gibt eine Liste mit zweidimensionalen Arrays fr jeweils ein sheet in der excel-Datei
 *         zurck.
 * @throws IOException
 *             Fals ein Fehler beim Lesen aufgetreten ist.
 * @throws FileNotFoundException
 *             Fals die excel-Datei nicht gefunden wurde.
 */
public static List<String[][]> exportWorkbook(final File excelSheet) throws IOException, FileNotFoundException {
    final POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet));
    final HSSFWorkbook wb = new HSSFWorkbook(fs);

    final int numberOfSheets = wb.getNumberOfSheets();
    final List<String[][]> sheetList = new ArrayList<>();
    for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) {
        HSSFSheet sheet = null;
        sheet = wb.getSheetAt(sheetNumber);
        final int rows = sheet.getLastRowNum();

        final int columns = sheet.getRow(0).getLastCellNum();
        String[][] excelSheetInTDArray = null;
        excelSheetInTDArray = new String[rows + 1][columns];
        for (int i = 0; i <= rows; i++) {
            final HSSFRow row = sheet.getRow(i);
            if (null != row) {
                for (int j = 0; j < columns; j++) {
                    final HSSFCell cell = row.getCell(j);
                    if (null == cell) {
                        excelSheetInTDArray[i][j] = "";
                    } else {
                        final int cellType = cell.getCellType();
                        if (cellType == Cell.CELL_TYPE_BLANK) {
                            excelSheetInTDArray[i][j] = "";
                        } else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
                            excelSheetInTDArray[i][j] = Boolean.toString(cell.getBooleanCellValue());
                        } else if (cellType == Cell.CELL_TYPE_ERROR) {
                            excelSheetInTDArray[i][j] = "";
                        } else if (cellType == Cell.CELL_TYPE_FORMULA) {
                            excelSheetInTDArray[i][j] = cell.getCellFormula();
                        } else if (cellType == Cell.CELL_TYPE_NUMERIC) {
                            excelSheetInTDArray[i][j] = Double.toString(cell.getNumericCellValue());
                        } else if (cellType == Cell.CELL_TYPE_STRING) {
                            excelSheetInTDArray[i][j] = cell.getRichStringCellValue().getString();
                        }
                    }
                }
            }
        }
        sheetList.add(excelSheetInTDArray);
    }
    wb.close();
    return sheetList;
}

From source file:de.alpharogroup.export.excel.poi.ExportExcelExtensions.java

License:Open Source License

/**
 * Exportiert die bergebene excel-Datei in eine geschachtelte Liste mit Listen von sheets und
 * Listen von den Zeilen der sheets von der excel-Datei.
 *
 * @param excelSheet//from   w  w w. j  av a  2  s  .  co m
 *            Die excel-Datei.
 * @return Gibt eine Liste mit Listen von den sheets in der excel-Datei zurck. Die Listen mit
 *         den sheets beinhalten weitere Listen mit String die jeweils eine Zeile
 *         reprsentieren.
 * @throws IOException
 *             Fals ein Fehler beim Lesen aufgetreten ist.
 * @throws FileNotFoundException
 *             Fals die excel-Datei nicht gefunden wurde.
 */
public static List<List<List<String>>> exportWorkbookAsStringList(final File excelSheet)
        throws IOException, FileNotFoundException {
    final POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet));
    final HSSFWorkbook wb = new HSSFWorkbook(fs);
    final int numberOfSheets = wb.getNumberOfSheets();
    final List<List<List<String>>> sl = new ArrayList<>();
    for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) {
        HSSFSheet sheet = null;
        sheet = wb.getSheetAt(sheetNumber);
        final int rows = sheet.getLastRowNum();
        final int columns = sheet.getRow(0).getLastCellNum();
        final List<List<String>> excelSheetList = new ArrayList<>();
        for (int i = 0; i <= rows; i++) {
            final HSSFRow row = sheet.getRow(i);
            if (null != row) {
                final List<String> reihe = new ArrayList<>();
                for (int j = 0; j < columns; j++) {
                    final HSSFCell cell = row.getCell(j);
                    if (null == cell) {
                        reihe.add("");
                    } else {
                        final int cellType = cell.getCellType();
                        if (cellType == Cell.CELL_TYPE_BLANK) {
                            reihe.add("");
                        } else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
                            reihe.add(Boolean.toString(cell.getBooleanCellValue()));
                        } else if (cellType == Cell.CELL_TYPE_ERROR) {
                            reihe.add("");
                        } else if (cellType == Cell.CELL_TYPE_FORMULA) {
                            reihe.add(cell.getCellFormula());
                        } else if (cellType == Cell.CELL_TYPE_NUMERIC) {
                            reihe.add(Double.toString(cell.getNumericCellValue()));
                        } else if (cellType == Cell.CELL_TYPE_STRING) {
                            reihe.add(cell.getRichStringCellValue().getString());
                        }
                    }
                }
                excelSheetList.add(reihe);
            }
        }
        sl.add(excelSheetList);
    }
    wb.close();
    return sl;
}

From source file:de.bezier.data.XlsReader.java

License:Creative Commons License

/**
*   Return an float value from a specific cell of the current sheet.
*
*   @param   rowNum   Row (vertically) to read from. First row is 0.
*   @param   cellNum Cell (horizontal) in the row to read from. Starts at 0.
*   @return   float The float value of that cell.
*//*  ww  w. j a v  a 2s  .  co  m*/

public float getFloat(int rowNum, int cellNum) {
    HSSFCell cell = getCell(rowNum, cellNum);

    if (cell == null)
        papplet.println("ERR, getFloat(): cell is null");

    if (!isCellType(cell, HSSFCell.CELL_TYPE_NUMERIC))
        papplet.println("ERR, getFloat(): wrong celltype");

    float i;
    try {
        i = (float) (cell.getNumericCellValue());
        return i;
    } catch (NumberFormatException nfe) {
        if (showWarnings)
            papplet.println(
                    "ERR, getFloat(" + rowNum + "," + cellNum + "): you are reading a text cell as float.");
    }
    i = Float.parseFloat(cell.getRichStringCellValue().toString().trim());
    return i;
}

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.LieferkettenImporterEFSA.java

License:Open Source License

public void mergeIDs() {
    System.err.println("Merging...");
    try (HSSFWorkbook wb = new HSSFWorkbook(
            new POIFSFileSystem(new FileInputStream(DBKernel.HSHDB_PATH + "mergeList.xls")))) {
        //FileInputStream is = new FileInputStream("C:\\Users\\Armin\\Desktop\\AllKrisen\\EFSA\\mergeList.xls");
        HSSFSheet mergeSheet = wb.getSheet("mergeList");
        int numRows = mergeSheet.getLastRowNum() + 1;
        for (int i = 1; i < numRows; i++) {
            try {
                HSSFRow row = mergeSheet.getRow(i);
                if (row != null) {
                    HSSFCell cell = row.getCell(0);
                    if (cell != null) {
                        Integer oldEfsaID = (int) cell.getNumericCellValue();
                        if (cell != null) {
                            cell = row.getCell(1);
                            Integer newEfsaID = (int) cell.getNumericCellValue();
                            DBKernel.mergeIDs(DBKernel.getDBConnection(), "Station", oldEfsaID, newEfsaID);
                        }//  ww  w .j  a  va 2s . c  o m
                    }
                }
            } catch (Exception e) {
                System.err.println(e.getMessage() + "\t" + i);
            }
        }
    } catch (Exception e) {
    }
    System.err.println("Merging...Fin!");
}

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.LieferkettenImporterEFSA.java

License:Open Source License

private HashMap<String, Integer> loadNodeIDs10000() {
    System.err.println("loadNodeIDs10000...");

    nodeIds = new HashMap<String, Integer>();
    try (HSSFWorkbook wb = new HSSFWorkbook(
            new POIFSFileSystem(new FileInputStream(DBKernel.HSHDB_PATH + "nodesids10000.xls")))) {
        //FileInputStream is = new FileInputStream("C:\\Users\\Armin\\Desktop\\AllKrisen\\EFSA\\nodesids10000.xls");
        HSSFSheet defaultSheet = wb.getSheet("default");
        int numRows = defaultSheet.getLastRowNum() + 1;
        for (int i = 1; i < numRows; i++) {
            try {
                HSSFRow row = defaultSheet.getRow(i);
                if (row != null) {
                    HSSFCell cell = row.getCell(0);
                    Integer id = (int) cell.getNumericCellValue();
                    if (id > maxNodeID)
                        maxNodeID = id;// ww w.j a v  a  2 s  .  co  m
                    cell = row.getCell(1);
                    String name = cell.getStringCellValue();
                    nodeIds.put(name, id);
                }
            } catch (Exception e) {
                System.err.println(e.getMessage() + "\t" + i);
            }
        }
    } catch (Exception e) {
    }

    System.err.println("loadNodeIDs10000...Fin!");
    return nodeIds;
}

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.LieferkettenImporterEFSA.java

License:Open Source License

private String getStrVal(HSSFCell cell, int maxChars) {
    String result = null;/*from ww  w.j  av  a2s.  c  o m*/
    try {
        if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
            result = cell.getStringCellValue();
            if (result.equals("."))
                result = null;
        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC
                || cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
            try {
                double dbl = cell.getNumericCellValue();
                if (Math.round(dbl) == dbl)
                    result = "" + ((int) dbl);
                else
                    result = "" + cell.getNumericCellValue();
            } catch (Exception e) {
                result = cell.getStringCellValue();
            }
        } else {
            result = cell.toString();
        }
        if (result != null) {
            if (result.equals("#N/A")) {
                result = null;
            } else if (result.length() > maxChars) {
                String msg = "string too long (" + result.length() + ") - shortened to " + maxChars
                        + " chars... '" + result + "' -> '" + result.substring(0, maxChars) + "'";
                System.err.println(msg);
                logMessages += msg + "\n";
                result = result.substring(0, maxChars);
            }
        }
    } catch (Exception e) {
    }
    return result;
}

From source file:de.bund.bfr.knime.openkrise.db.imports.GeneralXLSImporter.java

License:Open Source License

private Integer manageInteger(PreparedStatement ps, PreparedStatement psUpdate, int lfdCol, HSSFCell cell)
        throws SQLException {
    Integer result = null;/*  www. j  ava2 s  . co  m*/
    if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
        if (cell.getStringCellValue().trim().length() > 0) {
            result = new Integer(cell.getStringCellValue());
            if (ps != null)
                ps.setInt(lfdCol, result);
            if (psUpdate != null)
                psUpdate.setInt(lfdCol, result);
            return result;
        }
    } else {
        result = new Integer((int) cell.getNumericCellValue());
        if (ps != null)
            ps.setInt(lfdCol, result);
        if (psUpdate != null)
            psUpdate.setInt(lfdCol, result);
        return result;
    }
    if (ps != null)
        ps.setNull(lfdCol, java.sql.Types.INTEGER);
    if (psUpdate != null)
        psUpdate.setNull(lfdCol, java.sql.Types.INTEGER);
    return result;
}

From source file:de.bund.bfr.knime.openkrise.db.imports.GeneralXLSImporter.java

License:Open Source License

private Long manageBigInteger(PreparedStatement ps, PreparedStatement psUpdate, int lfdCol, HSSFCell cell)
        throws SQLException {
    Long result = null;//from w  ww .j  a v  a2s  .  co  m
    if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
        if (cell.getStringCellValue().trim().length() > 0) {
            result = new Long(cell.getStringCellValue());
            if (ps != null)
                ps.setLong(lfdCol, result);
            if (psUpdate != null)
                psUpdate.setLong(lfdCol, result);
            return result;
        }
    } else {
        result = new Long((long) cell.getNumericCellValue());
        if (ps != null)
            ps.setLong(lfdCol, result);
        if (psUpdate != null)
            psUpdate.setLong(lfdCol, result);
        return result;
    }
    if (ps != null)
        ps.setNull(lfdCol, java.sql.Types.BIGINT);
    if (psUpdate != null)
        psUpdate.setNull(lfdCol, java.sql.Types.BIGINT);
    return result;
}

From source file:de.bund.bfr.knime.openkrise.db.imports.GeneralXLSImporter.java

License:Open Source License

private Double manageDouble(PreparedStatement ps, PreparedStatement psUpdate, int lfdCol, HSSFCell cell)
        throws SQLException {
    Double dbl = null;//w  w  w  . j  av a 2 s . c om
    if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
        if (cell.getStringCellValue().trim().length() > 0
                && !cell.getStringCellValue().equalsIgnoreCase("null")) {
            try {
                dbl = Double.parseDouble(cell.getStringCellValue());
                ps.setDouble(lfdCol, dbl);
                psUpdate.setDouble(lfdCol, dbl);
                return dbl;
            } catch (Exception e) {
            }
        }
    } else {
        dbl = cell.getNumericCellValue();
        try {
            ps.setDouble(lfdCol, dbl);
            psUpdate.setDouble(lfdCol, dbl);
        } catch (Exception e) {
        }
        return dbl;
    }
    try {
        ps.setNull(lfdCol, java.sql.Types.DOUBLE);
        psUpdate.setNull(lfdCol, java.sql.Types.DOUBLE);
    } catch (Exception e) {
    }
    return dbl;
}