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

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

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFCell
  6. getStringCellValue

Introduction

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

Prototype

public String getStringCellValue()

Source Link

Document

get the value of the cell as a string - for numeric cells we throw an exception.

Usage

From source file:com.zhu.action.CarAction.java

/**
 * excel ? /// ww w  . j av  a2 s  .  c  om
 * 
 * @param hcell
 * @return
 */
public boolean isBlankCell(HSSFCell hcell) {
    if (hcell == null)
        return true;
    hcell.setCellType(hcell.CELL_TYPE_STRING);
    String content = hcell.getStringCellValue().trim();
    if (content == null || "".equals(content)) // ?
    {
        return true;
    }
    return false;
}

From source file:com.zxy.commons.poi.excel.ExcelUtils.java

License:Apache License

/**
 * ?Excelsheet/*from w  w w .  ja  v a  2s.  c om*/
 * 
 * @param inputPath ???Excel
 * @return Excel?
 * @throws IOException IOException
 */
public static Map<String, Table<Integer, String, String>> readAll2table(String inputPath) throws IOException {
    Map<String, Table<Integer, String, String>> tables = Maps.newLinkedHashMap();
    FileInputStream inputStream = null;
    HSSFWorkbook wb = null;
    try {
        inputStream = new FileInputStream(inputPath);
        BufferedInputStream bufferedInputStream = new BufferedInputStream(inputStream);
        // HSSFWorkbook
        POIFSFileSystem fs = new POIFSFileSystem(bufferedInputStream);
        wb = new HSSFWorkbook(fs);
        List<String> columnNames = Lists.newLinkedList();
        for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
            Table<Integer, String, String> table = TreeBasedTable.create();
            HSSFSheet st = wb.getSheetAt(sheetIndex);
            String sheetName = st.getSheetName();
            for (int rowIndex = 0; rowIndex <= st.getLastRowNum(); rowIndex++) {
                HSSFRow row = st.getRow(rowIndex);
                for (int columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++) {
                    HSSFCell cell = row.getCell(columnIndex);
                    if (cell != null) {
                        if (rowIndex == 0) { // 
                            columnNames.add(cell.getStringCellValue());
                        } else {
                            String value = cell.getStringCellValue();
                            table.put(rowIndex, columnNames.get(columnIndex), value);
                        }
                    }
                }
            }
            tables.put(sheetName, table);
        }
        return tables;
    } finally {
        if (wb != null) {
            wb.close();
        }
        if (inputStream != null) {
            inputStream.close();
        }
    }
}

From source file:Compras.ComparaCotizacion.java

void importaDatos(Workbook wb, int col) {
     Sheet hoja = wb.getSheetAt(0);//from   ww  w  .j av  a  2s. c om
     Iterator rowIterator = hoja.rowIterator();
     List renglones = new ArrayList();
     while (rowIterator.hasNext()) {
         HSSFRow hssfRow = (HSSFRow) rowIterator.next();
         List celdas = new ArrayList();
         Iterator iterator = hssfRow.cellIterator();
         while (iterator.hasNext()) {
             HSSFCell hssfCell = (HSSFCell) iterator.next();
             celdas.add(hssfCell);
         }
         renglones.add(celdas);
     }
     for (int r = 8; r < renglones.size(); r++) {
         List aux = (List) renglones.get(r);
         HSSFCell auxCell = (HSSFCell) aux.get(0);
         HSSFCell auxCell1 = (HSSFCell) aux.get(1);
         int res = busca(auxCell.getNumericCellValue(), auxCell1.getNumericCellValue());
         if (res != -1) {
             for (int c = 0; c < aux.size(); c++) {
                 HSSFCell valor = (HSSFCell) aux.get(c);

                 switch (valor.getColumnIndex()) {
                 case 4:
                     switch (valor.getCellType()) {
                     case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
                         t_datos.setValueAt((int) valor.getNumericCellValue(), res, col);
                         break;
                     case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
                         t_datos.setValueAt(valor.getStringCellValue(), res, col);
                         break;
                     case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK:
                         t_datos.setValueAt("", res, col);
                         break;
                     default:
                         t_datos.setValueAt("", res, col);
                         break;
                     }
                     break;

                 case 7:
                     switch (valor.getCellType()) {
                     case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
                         t_datos.setValueAt(valor.getNumericCellValue(), res, col + 1);
                         break;
                     case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
                         t_datos.setValueAt(valor.getStringCellValue(), res, col + 1);
                         break;
                     default:
                         t_datos.setValueAt("", res, col + 1);
                         break;
                     }
                     if (t_datos.getValueAt(res, col + 1).toString().compareTo("") != 0) {
                         model.setCeldaEditable(res, col + 3, true);
                         model.setCeldaEditable(res, col + 4, true);
                     }
                     break;

                 case 10:
                     switch (valor.getCellType()) {
                     case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
                         t_datos.setValueAt((int) valor.getNumericCellValue(), res, col + 2);
                         break;
                     case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
                         t_datos.setValueAt(valor.getStringCellValue(), res, col + 2);
                         break;
                     default:
                         t_datos.setValueAt("", res, col + 2);
                         break;
                     }
                     break;
                 }
                 t_datos.setValueAt(false, res, col + 3);
                 t_datos.setValueAt(false, res, col + 4);
             }
         }
     }
 }

From source file:database_lab1.ExcelToMySQL.java

public void ExcelFileReader(String filename) throws IOException, SQLException {
    FileInputStream fis = null;/*from   w w w .ja  va2 s .  co  m*/
    Scanner sc = new Scanner(System.in);
    try {
        fis = new FileInputStream(filename);
        String queryValue = (new File(filename).getName().replaceAll("(.xls)", ""));
        System.out.print("Please input the schema name:");
        String schemaName = sc.next();
        System.out.print("Please input the database username:");
        String userName = sc.next();
        System.out.print("Please input the database password:");
        String password = sc.next();
        HSSFWorkbook workbook = new HSSFWorkbook(fis);
        HSSFSheet sheet = workbook.getSheetAt(0);
        Iterator rowIter = sheet.rowIterator();
        //variable that will use to find the first row
        int firstrow = 0, maxColumn = 0;
        //iterator for the row values 
        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;

                //determines if cell value is string or numaric
                if (myCell.getCellType() == 1)
                    cellvalue = myCell.getStringCellValue();
                else
                    cellvalue = (int) myCell.getNumericCellValue() + "";
                //adds the value in the vector
                cellStoreVector.addElement(cellvalue);
                //prints out the cell valuse
                System.out.print(cellvalue + "  ");

            }
            System.out.println();
            //if the row is not the first row then,
            //insert the data in database
            if (firstrow != 0) {
                dbConnection db = new dbConnection("jdbc:mysql://localhost:3306/" + schemaName, userName,
                        password);
                //sql comment
                String insert = "INSERT INTO " + queryValue + " VALUES (?,?,?,?,?,?);";
                PreparedStatement ps = db.getConnection().prepareStatement(insert);//createStatement().executeUpdate(insert);
                for (int i = 1; i <= cellStoreVector.size(); i++) {
                    ps.setString(i, cellStoreVector.get(i - 1));
                }
                //executing the sql command
                ps.execute();
            }
            firstrow++;
        }
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (fis != null) {
            fis.close();
        }
    }
}

From source file:database_lab1.studentExcel.java

public void studentExcelFile(String filename) throws IOException, SQLException {
    FileInputStream fis = null;//from w  w  w .  ja  va  2  s.  c  o 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.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;/*from www .  ja v a 2  s  .c o  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 void add2Cell(HSSFCell c, String value) {
    if (c != null) {
        String ts = c.getStringCellValue();
        if (ts != null && value != null && ts.indexOf(value) < 0)
            c.setCellValue(ts + "\n" + value);
        //if (ts.length() > 5000) System.err.println(ts.length());
    }//from www .j a  va  2 s.  c  o m
}

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  w ww .  j a  v a 2  s.  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;/*from  w  ww . j  av a2  s  .  c o  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 Date manageDate(PreparedStatement ps, PreparedStatement psUpdate, int lfdCol, HSSFCell cell)
        throws SQLException {
    Date result = null;//from  w  w  w . j av a 2 s . c o m
    if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
        if (cell.getStringCellValue().trim().length() > 0) {
            DateFormat formater = new SimpleDateFormat("yyyy-MM-dd"); // 2012-06-01   hh:mm:ss
            java.util.Date parsedUtilDate;
            try {
                parsedUtilDate = formater.parse(cell.getStringCellValue());
                result = new java.sql.Date(parsedUtilDate.getTime());
            } catch (ParseException e) {
                e.printStackTrace();
            }
            if (result != null) {
                if (ps != null)
                    ps.setDate(lfdCol, result);
                if (psUpdate != null)
                    psUpdate.setDate(lfdCol, result);
                return result;
            }
        }
    } else {
        result = new Date(cell.getDateCellValue().getTime());
        if (ps != null)
            ps.setDate(lfdCol, result);
        if (psUpdate != null)
            psUpdate.setDate(lfdCol, result);
        return result;
    }
    if (ps != null)
        ps.setNull(lfdCol, java.sql.Types.DATE);
    if (psUpdate != null)
        psUpdate.setNull(lfdCol, java.sql.Types.DATE);
    return result;
}