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:file.open.util.parse.XlsParser.java

License:Open Source License

public String[] splitLine() throws Exception {
    if (m_iCurrentRow == m_iNbRows) {
        return null;
    }//  ww w  .  j ava2 s  .co  m

    HSSFRow row = m_sheet.getRow(m_iCurrentRow);
    if (row == null) {
        return null;
    } else {
        int cellIndex = 0;
        int noOfCells = row.getPhysicalNumberOfCells();
        String[] values = new String[noOfCells];
        short firstCellNum = row.getFirstCellNum();
        short lastCellNum = row.getLastCellNum();

        if (firstCellNum >= 0 && lastCellNum >= 0) {
            for (short iCurrent = firstCellNum; iCurrent < lastCellNum; iCurrent++) {
                HSSFCell cell = (HSSFCell) row.getCell((int) iCurrent);
                if (cell == null) {
                    values[iCurrent] = "";
                    cellIndex++;
                    continue;
                } else {
                    switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        double value = cell.getNumericCellValue();
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            if (HSSFDateUtil.isValidExcelDate(value)) {
                                Date date = HSSFDateUtil.getJavaDate(value);
                                SimpleDateFormat dateFormat = new SimpleDateFormat(JAVA_TOSTRING);
                                values[iCurrent] = dateFormat.format(date);
                            } else {
                                throw new Exception("Invalid Date value found at row number " + row.getRowNum()
                                        + " and column number " + cell.getNumericCellValue());
                            }
                        } else {
                            values[iCurrent] = value + "";
                        }
                        break;

                    case HSSFCell.CELL_TYPE_STRING:
                        values[iCurrent] = cell.getStringCellValue();
                        break;

                    case HSSFCell.CELL_TYPE_BLANK:
                        values[iCurrent] = null;
                        break;

                    default:
                        values[iCurrent] = null;
                    }
                }
            }
        }
        m_iCurrentRow++;
        return values;
    }

}

From source file:fitlibrary.runner.SpreadsheetRunner.java

License:Open Source License

@SuppressWarnings("deprecation")
private String value(HSSFCell cell) {
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_BLANK:
        return "";
    case HSSFCell.CELL_TYPE_BOOLEAN:
        return "" + cell.getBooleanCellValue();
    case HSSFCell.CELL_TYPE_ERROR:
        return "ERROR";
    case HSSFCell.CELL_TYPE_FORMULA:
        if (Double.isNaN(cell.getNumericCellValue())) {
            try {
                return "" + cell.getBooleanCellValue();
            } catch (NumberFormatException ex) {
                return cell.getStringCellValue();
            }/* w w  w .  ja va 2  s . c o m*/
        }
        return number(cell);
    case HSSFCell.CELL_TYPE_NUMERIC:
        return number(cell);
    case HSSFCell.CELL_TYPE_STRING:
        return cell.getStringCellValue();
    }
    return "UNKNOWN";
}

From source file:fitlibrary.runner.SpreadsheetRunner.java

License:Open Source License

private String number(HSSFCell cell) {
    double value = cell.getNumericCellValue();
    if (((int) value) == value)
        return "" + ((int) value);
    return "" + value;
}

From source file:fr.ens.transcriptome.aozan.io.CasavaDesignXLSReader.java

License:Open Source License

/**
 * Parse the content of a cell.//from w ww  .java 2 s . c  o  m
 * @param cell cell to parse
 * @return a String with the cell content
 */
private static String parseCell(final HSSFCell cell) {

    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        final double doubleValue = cell.getNumericCellValue();

        if (DoubleMath.isMathematicalInteger(doubleValue)) {
            return Long.toString((long) doubleValue);
        }
    }

    return cell.toString();
}

From source file:fr.univrouen.poste.services.ExcelParser.java

License:Apache License

public List<List<String>> getCells(InputStream xslFileInput) {

    List<List<String>> cellVectorHolder = new Vector<List<String>>();

    try {//www .j  a  v a  2s  .c  o  m

        POIFSFileSystem fileSystem = new POIFSFileSystem(xslFileInput);
        HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
        HSSFSheet sheet = workBook.getSheetAt(0);
        Iterator<Row> rowIter = sheet.rowIterator();

        while (rowIter.hasNext()) {
            HSSFRow myRow = (HSSFRow) rowIter.next();
            List<String> cellStoreVector = new Vector<String>();
            // take care of blank cell !
            // @see http://stackoverflow.com/questions/4929646/how-to-get-an-excel-blank-cell-value-in-apache-poi
            int max = myRow.getLastCellNum();
            for (int i = 0; i < max; i++) {
                HSSFCell myCell = (HSSFCell) myRow.getCell(i, Row.CREATE_NULL_AS_BLANK);
                if (Cell.CELL_TYPE_STRING == myCell.getCellType())
                    cellStoreVector.add(myCell.getStringCellValue());
                else if ((Cell.CELL_TYPE_NUMERIC == myCell.getCellType()))
                    cellStoreVector.add(Long.toString(new Double(myCell.getNumericCellValue()).longValue()));
                else if ((Cell.CELL_TYPE_BLANK == myCell.getCellType()))
                    cellStoreVector.add("");
                else {
                    logger.debug("This cell is not numeric or string ... : " + myCell + " \n ... cellType : "
                            + myCell.getCellType());
                    cellStoreVector.add("");
                }
            }
            cellVectorHolder.add(cellStoreVector);
        }
    } catch (Exception e) {
        logger.error("Error during parsing the XSL File", e);
        throw new RuntimeException("Error during parsing the XSL File", e);
    }

    return cellVectorHolder;
}

From source file:gatebass.utils.exel.POIExcelReader.java

/**
 * 41 This method is used to display the Excel content to command line. 42 *
 *
 * @param xlsPath/*from  w w w.  ja  v  a 2 s  .  c  om*/
 */
@SuppressWarnings("unchecked")
public void displayFromExcel(String xlsPath) {
    //        end_row = 2242;
    InputStream inputStream = null;

    try {
        inputStream = new FileInputStream(xlsPath);
    } catch (FileNotFoundException e) {
        System.out.println("File not found in the specified path.");
        e.printStackTrace();
    }

    POIFSFileSystem fileSystem = null;
    int dd = 0;

    try {
        fileSystem = new POIFSFileSystem(inputStream);

        HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
        HSSFSheet sheet = workBook.getSheetAt(0);
        Iterator rows = sheet.rowIterator();
        List<Individuals> individualses = new ArrayList<>();
        while (rows.hasNext()) {
            HSSFRow row = (HSSFRow) rows.next();
            //                if (row.getRowNum() >= end_row) {
            //                    break;
            //                }

            if (row.getRowNum() <= start_row) {
                continue;
            }

            dd = row.getRowNum();
            //                if (row.getRowNum() == 0
            //                        || row.getRowNum() < 195 || row.getRowNum() > 250
            //                        ) {
            //                    continue;
            //                }
            Individuals individuals = null;

            // display row number in the console.
            //                System.out.println("Row No.: " + row.getRowNum());
            // once get a row its time to iterate through cells.
            Iterator cells = row.cellIterator();
            while (cells.hasNext()) {
                HSSFCell cell = (HSSFCell) cells.next();

                //                    System.out.println("Cell No.: " + cell.getCellNum());

                /*
                 * Now we will get the cell type and display the values
                 * accordingly.
                 */
                switch (cell.getCellNum()) {
                case 0:
                    individuals = new Individuals();
                    //                            individuals = new Individuals(Integer.parseInt(cell.getRichStringCellValue().getString()));
                    break;
                case 1:
                    try {
                        individuals.setCard_id(((long) cell.getNumericCellValue()) + "");
                    } catch (Exception e) {
                    }
                    try {
                        individuals.setCard_id(cell.getRichStringCellValue().getString());
                    } catch (Exception e) {
                    }
                    break;
                //                        case 2:
                //                            if (!cell.getRichStringCellValue().getString().isEmpty()) {
                //                            }
                //                            break;
                case 3:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        individuals.setFirst_name(cell.getRichStringCellValue().getString());
                    }
                    break;
                case 4:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        individuals.setLast_name(cell.getRichStringCellValue().getString());
                    }
                    break;
                case 6:
                    try {
                        individuals.setNational_id(((long) cell.getNumericCellValue()) + "");
                    } catch (Exception e) {
                    }
                    try {
                        individuals.setNational_id(cell.getRichStringCellValue().getString());
                    } catch (Exception e) {
                    }

                    break;
                case 10:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        individuals.setFirst_name_ENG(cell.getRichStringCellValue().getString());
                    }
                    break;
                case 16:
                    try {
                        individuals.setPostal_code(((long) cell.getNumericCellValue()) + "");
                    } catch (Exception e) {
                    }
                    try {
                        individuals.setPostal_code(cell.getRichStringCellValue().getString());
                    } catch (Exception e) {
                    }
                    break;
                case 17:
                    try {
                        individuals.setId_number(((long) cell.getNumericCellValue()) + "");
                    } catch (Exception e) {
                    }
                    try {
                        individuals.setId_number(cell.getRichStringCellValue().getString());
                    } catch (Exception e) {
                    }
                    break;
                case 18:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        individuals.setBirth_day(databaseHelper.historyDao.getFirst("date",
                                cell.getRichStringCellValue().getString().substring(2)));
                    }
                    break;
                case 19:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        individuals.setFather_first_name(cell.getRichStringCellValue().getString());
                    }
                    break;
                case 20:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        individuals.setBirth_state(cell.getRichStringCellValue().getString());
                    }
                    break;
                case 21:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        individuals.setIssued(cell.getRichStringCellValue().getString());
                    }
                    break;
                case 22:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        individuals.setStreet_address(cell.getRichStringCellValue().getString());
                    }
                    break;
                case 23:
                    String ss = cell.getRichStringCellValue().getString();
                    individuals.setVeteran_status(BEDONE_KART);
                    if (!ss.isEmpty()) {
                        if (ss.contains("?")) {
                            individuals.setVeteran_status(MOAF);
                        } else if (ss.contains("")) {
                            individuals.setVeteran_status(PAYAN_KHEDMAT);
                        }
                    }
                    break;
                case 25:
                    try {
                        individuals.setMobile(((long) cell.getNumericCellValue()) + "");
                    } catch (Exception e) {
                    }
                    try {
                        individuals.setMobile(cell.getRichStringCellValue().getString());
                    } catch (Exception e) {
                    }

                    break;
                case 26:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        individuals.setAcademic_degree(cell.getRichStringCellValue().getString());
                    }
                    break;
                case 27:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        individuals.setField_of_study(cell.getRichStringCellValue().getString());
                    }
                    break;
                case 28:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        individuals.setReligion(cell.getRichStringCellValue().getString());
                    }
                    break;
                case 34:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        individuals.setHave_soe_pishine(true);
                    }
                    break;
                case 35:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        individuals.setComments(cell.getRichStringCellValue().getString());
                    }
                    break;
                }

                switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC: {
                    // cell type numeric.
                    //                            System.out.println("Numeric value: " + cell.getNumericCellValue());
                    break;
                }

                case HSSFCell.CELL_TYPE_STRING: {
                    // cell type string.
                    HSSFRichTextString richTextString = cell.getRichStringCellValue();
                    //                            System.out.println("String value: " + richTextString.getString());
                    break;
                }

                default: {
                    // types other than String and Numeric.
                    //                            System.out.println("Type not supported.");
                    break;
                }
                }
            }
            String split = FileSystems.getDefault().getSeparator();

            individuals.setFilesPatch(
                    "data" + split + "1394" + split + dd / 50 + split + individuals.getNational_id() + split);
            File imageFile = new File(
                    "d://test//Images-Personal-Gatepass//" + individuals.getCard_id() + ".jpg");

            if (imageFile.exists()) {
                individuals.setPicture_address(individuals.getNational_id() + "-pic");
                copyImageFile(imageFile.getAbsolutePath(), server + individuals.getFilesPatch(),
                        individuals.getPicture_address());
                individuals.setPicture_address(
                        individuals.getPicture_address() + getFileExtension(imageFile.getAbsolutePath()));
            }
            individualses.add(individuals);
            //                databaseHelper.individualsDao.createOrUpdate(individuals, dd);
        }
        databaseHelper.individualsDao.insertList(individualses);

    } catch (Exception e) {
        e.printStackTrace();
    }

}

From source file:gatebass.utils.exel.POIExcelReader.java

@SuppressWarnings("unchecked")
public void displayFromExcel2(String xlsPath) {
    InputStream inputStream = null;

    try {//ww w.ja  va2  s .  co  m
        inputStream = new FileInputStream(xlsPath);
    } catch (FileNotFoundException e) {
        System.out.println("File not found in the specified path.");
        e.printStackTrace();
    }

    POIFSFileSystem fileSystem = null;
    int dd = 0;

    try {
        fileSystem = new POIFSFileSystem(inputStream);

        HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
        HSSFSheet sheet = workBook.getSheetAt(0);
        Iterator rows = sheet.rowIterator();
        List<Cars> carses = new ArrayList<>();
        while (rows.hasNext()) {
            HSSFRow row = (HSSFRow) rows.next();
            //                if (row.getRowNum() >= end_row) {
            //                    break;
            //                }

            if (row.getRowNum() <= start_row) {
                continue;
            }

            dd = row.getRowNum();
            //                if (row.getRowNum() == 0
            //                        || row.getRowNum() < 195 || row.getRowNum() > 250
            //                        ) {
            //                    continue;
            //                }
            Cars cars = null;

            // display row number in the console.
            //                System.out.println("Row No.: " + row.getRowNum());
            // once get a row its time to iterate through cells.
            Iterator cells = row.cellIterator();
            while (cells.hasNext()) {
                HSSFCell cell = (HSSFCell) cells.next();

                //                    System.out.println("Cell No.: " + cell.getCellNum());

                /*
                 * Now we will get the cell type and display the values
                 * accordingly.
                 */
                switch (cell.getCellNum()) {
                case 0:
                    cars = new Cars();
                    //                            individuals = new Individuals(Integer.parseInt(cell.getRichStringCellValue().getString()));
                    break;
                case 1:
                    try {
                        cars.setCard_id(((long) cell.getNumericCellValue()) + "");
                    } catch (Exception e) {
                    }
                    try {
                        cars.setCard_id(cell.getRichStringCellValue().getString());
                    } catch (Exception e) {
                    }
                    cars.setShasi_number(cars.getCard_id());
                    break;
                //                        case 2:
                //                            if (!cell.getRichStringCellValue().getString().isEmpty()) {
                //                            }
                //                            break;
                case 3:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        cars.setCar_name(cell.getRichStringCellValue().getString());
                    }
                    break;
                //                        case 4:
                //                            if (!cell.getRichStringCellValue().getString().isEmpty()) {
                //                                cars.setLast_name(cell.getRichStringCellValue().getString());
                //                            }
                //                            break;
                //                        case 5:
                //                            if (!cell.getRichStringCellValue().getString().isEmpty()) {
                //                                cars.set(cell.getRichStringCellValue().getString());
                //                            }
                //                            break;
                case 13:
                    if (!cell.getRichStringCellValue().getString().isEmpty()) {
                        cars.setComments(cell.getRichStringCellValue().getString());
                    }
                    break;
                }

                switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC: {
                    // cell type numeric.
                    //                            System.out.println("Numeric value: " + cell.getNumericCellValue());
                    break;
                }

                case HSSFCell.CELL_TYPE_STRING: {
                    // cell type string.
                    HSSFRichTextString richTextString = cell.getRichStringCellValue();
                    //                            System.out.println("String value: " + richTextString.getString());
                    break;
                }

                default: {
                    // types other than String and Numeric.
                    //                            System.out.println("Type not supported.");
                    break;
                }
                }
            }
            String split = FileSystems.getDefault().getSeparator();

            cars.setFilesPatch(
                    "data" + split + "1394" + split + dd / 50 + split + cars.getShasi_number() + "_c" + split);
            carses.add(cars);
            //                databaseHelper.individualsDao.createOrUpdate(individuals, dd);
        }
        databaseHelper.carDao.insertList(carses);

    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:gda.hrpd.data.ExcelWorkbook.java

License:Open Source License

/**
 * gets value from the specified cell and return it as String.
 * //from   www .  j ava 2  s.  c  om
 * @param cell
 * @return value from cell as a String
 */
public String getCellValue(HSSFCell cell) {
    // If the cell is null return an empty string
    if (cell == null) {
        return "";
    }

    String value = null;
    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
        value = cell.getRichStringCellValue().toString();
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
        value = String.valueOf(cell.getNumericCellValue());
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
        value = "        ";
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
        value = String.valueOf(cell.getBooleanCellValue());
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_ERROR) {
        value = String.valueOf(cell.getErrorCellValue());
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
        value = cell.getCellFormula();
    }
    return value;
}

From source file:gov.nih.nci.cabig.caaers.dataimport.AgentSpecificTermsImporter.java

License:BSD License

private String getCellData(String sheetname, int rowNum, HSSFCell cell) {
    if (cell == null)
        return "";
    int cellType = cell.getCellType();
    if (cellType == 0) {
        if (cell.getNumericCellValue() == 0)
            return "";
        return (Integer.toString((int) cell.getNumericCellValue())).trim();
    } else if (cellType == 1) {
        return cell.getStringCellValue().trim();
    } else {/*from w ww . j  a  v a  2s  .c  o  m*/
        return "";
    }
}

From source file:gov.nih.nci.cabig.caaers.tools.ExcelProcessor.java

License:BSD License

private String getCellData(String sheetname, int rowNum, HSSFCell cell) {
    if (cell == null)
        throw new CaaersSystemException("Invalid data or Blank cell at following location: \n Sheet: "
                + sheetname + "\n Row: " + (rowNum + 1));

    int cellType = cell.getCellType();
    if (cellType == 0) {
        if (cell.getNumericCellValue() == 0)
            throw new CaaersSystemException("Invalid data or Blank cell at following location: \n Sheet: "
                    + sheetname + "\n Row: " + (rowNum + 1) + "\n Cell: " + ((cell.getCellNum()) + 1));
        return (Integer.toString((int) cell.getNumericCellValue())).trim();
    } else if (cellType == 1) {
        if (cell.getStringCellValue().equals(""))
            throw new CaaersSystemException("Invalid data or Blank cell at following location: \n Sheet: "
                    + sheetname + "\n Row: " + (rowNum + 1) + "\n Cell: " + ((cell.getCellNum()) + 1));
        return cell.getStringCellValue().trim();
    } else {//from  w  ww  .  j  av a  2s.  com
        throw new CaaersSystemException("Invalid data or Blank cell at following location: \n Sheet: "
                + sheetname + "\n Row: " + (rowNum + 1) + "\n Cell: " + ((cell.getCellNum()) + 1));
    }

}