Example usage for org.apache.poi.ss.usermodel Cell getNumericCellValue

List of usage examples for org.apache.poi.ss.usermodel Cell getNumericCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getNumericCellValue

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Cell getNumericCellValue.

Prototype

double getNumericCellValue();

Source Link

Document

Get the value of the cell as a number.

Usage

From source file:businessCharts.readExcell.java

public String[] queryByRowKey(String[] keyStringArray, String colName, String resultColName) throws Exception {
    String[] resultStringArray = new String[keyStringArray.length];
    initExcel("excel.xlsx");
    XSSFSheet spreadsheet = workbook.getSheetAt(0);

    Iterator<Row> rowIterator = spreadsheet.iterator();
    XSSFRow row = (XSSFRow) rowIterator.next();
    int getcellNo = getColNo(colName, row);
    int getReturncellKey = getColNo(resultColName, row);
    System.out.println(/*from   w w  w . j  a va  2  s . c o m*/
            "Inside queryByRowKey" + " CellNocolname" + getcellNo + "ReturnCellColName" + getReturncellKey);

    for (int i = 0; i < keyStringArray.length; i++) {
        System.out.println("resultstring array elements" + keyStringArray[i]);
        rowIterator = spreadsheet.iterator();
        row = (XSSFRow) rowIterator.next();
        int flag = 0;
        while (rowIterator.hasNext()) {
            row = (XSSFRow) rowIterator.next();
            Cell cell = row.getCell(getcellNo);
            Cell returnCell = row.getCell(getReturncellKey);
            //    System.out.println("Date is " + returnCell.getDateCellValue());
            //   try{
            if (cell.getNumericCellValue() == Double.parseDouble(keyStringArray[i])) {
                flag = 1;
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                //    System.out.println("Date is " + sdf.format(returnCell.getDateCellValue()));
                try {
                    if (returnCell.getDateCellValue() != null)
                        resultStringArray[i] = sdf.format(returnCell.getDateCellValue());
                } catch (Exception ex) {
                    resultStringArray[i] = "No Deadline Available";
                }
            }
            //  }
            // catch(Exception ex){
            //      continue;
            //}
        }
        if (flag == 0) {
            resultStringArray[i] = "Bug Not Available/Resolved";
            flag = 0;
        } else
            flag = 0;
    }

    return resultStringArray;
}

From source file:cherry.goods.excel.ExcelReader.java

License:Apache License

/**
 * ??????/*from w  w w . ja  v a 2s.c  om*/
 * 
 * @param cell ??
 * @return ?
 */
private String getNumericCellValueAsString(Cell cell) {
    BigDecimal value = BigDecimal.valueOf(cell.getNumericCellValue());
    try {
        return value.toBigIntegerExact().toString();
    } catch (ArithmeticException ex) {
        return value.toPlainString();
    }
}

From source file:cherry.parser.worksheet.CellUtil.java

License:Apache License

public static String getCellValueAsString(Cell cell) {
    switch (cell.getCellType()) {
    case CELL_TYPE_STRING:
        return cell.getStringCellValue();
    case CELL_TYPE_NUMERIC:
        return String.valueOf((int) cell.getNumericCellValue());
    case CELL_TYPE_FORMULA:
        switch (cell.getCachedFormulaResultType()) {
        case CELL_TYPE_STRING:
            return cell.getStringCellValue();
        case CELL_TYPE_NUMERIC:
            return String.valueOf((int) cell.getNumericCellValue());
        default://from ww  w  .  j  a  v a2  s .  co m
            return null;
        }
    default:
        return null;
    }
}

From source file:clases.excel.EXCELreader.java

private void prepareList(String path) {

    excelString = "";
    listaEXCEL = new ArrayList<>();
    int sw = 0;//0 no,1 si, 2 ready
    XSSFWorkbook workbook;/*w w  w. j  av a 2s .c  om*/

    try {
        //Create Workbook instance holding reference to .xlsx file
        workbook = new XSSFWorkbook(new FileInputStream(new File(path)));

        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);

        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            String[] linea = new String[parametros];
            Row row = rowIterator.next();
            //For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();

            //  if (rowcont2 ==16) {
            //                excelString += ++rowcont + "***";
            int rowcont = 0;
            while (cellIterator.hasNext()) {

                Cell cell = cellIterator.next();

                //JOptionPane.showMessageDialog(null, "Tipo *" + cell.getCellType() + "*");
                //Check the cell type and format accordingly
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    //leyendo tabla
                    if (sw == 2) {
                        Double d = cell.getNumericCellValue();//String.format("%d", d.intValue())
                        excelString += ((d % 1 != 0) ? d.toString() : d.toString()) + "\t";
                        linea[rowcont++] = ((d % 1 != 0) ? d.toString() : d.toString());
                    }
                    break;

                case Cell.CELL_TYPE_STRING:
                    // JOptionPane.showMessageDialog(null, "String *" + cell.getStringCellValue().trim() + "*");
                    if (sw == 0) {
                        if (cell.getStringCellValue().trim().equals(init_detalle)) {
                            sw = 1;
                        }
                    }
                    //leyendo tabla
                    if (sw == 2) {
                        excelString += cell.getStringCellValue() + "\t";
                        linea[rowcont++] = cell.getStringCellValue();
                    }
                    break;

                case Cell.CELL_TYPE_BLANK:
                    //si estaba leyendo tabla, pero encuentra vacio
                    //  termina de leer la tabla
                    if (rowcont == 0 && sw == 2) {
                        sw = 4;
                    }
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    //leyendo tabla
                    if (sw == 2) {
                        Double d = cell.getNumericCellValue();
                        excelString += ((d % 1 != 0) ? d.toString() : d.toString()) + "\t";
                        linea[rowcont++] = ((d % 1 != 0) ? d.toString() : d.toString());
                    }
                    break;
                }
                //si ya es 1 termina el ciclo, para iniciar el detalle
                //  en la siguiente linea
                if (sw == 1) {
                    sw = 2;
                    break;
                }
                //si ya no leera mas tabla, termina de leer la row
                if (sw == 4) {
                    break;
                }
            }
            //guarda si esta leyendo tabla
            if (sw == 2 && linea[0] != null) {
                listaEXCEL.add(linea);
                excelString += "\n";
            }
            //termino de leer la tabla, cierra todo
            if (sw == 4) {
                break;
            }

            //   }
        }
        int as = 0;
    } catch (Exception e) {
        e.printStackTrace();
    }

}

From source file:cn.afterturn.easypoi.excel.imports.CellValueService.java

License:Apache License

/**
 * ??/*from  ww w  . ja  va2s .  c  om*/
 *
 * @param cell
 * @param entity
 * @return
 */
private Object getCellValue(String classFullName, Cell cell, ExcelImportEntity entity) {
    if (cell == null) {
        return "";
    }
    Object result = null;
    if ("class java.util.Date".equals(classFullName) || "class java.sql.Date".equals(classFullName)
            || ("class java.sql.Time").equals(classFullName)
            || ("class java.time.Instant").equals(classFullName)
            || ("class java.time.LocalDate").equals(classFullName)
            || ("class java.time.LocalDateTime").equals(classFullName)
            || ("class java.sql.Timestamp").equals(classFullName)) {
        //FIX: ?yyyyMMdd cell.getDateCellValue() ?
        if (CellType.NUMERIC == cell.getCellType() && DateUtil.isCellDateFormatted(cell)) {
            result = DateUtil.getJavaDate(cell.getNumericCellValue());
        } else {
            String val = "";
            try {
                val = cell.getStringCellValue();
            } catch (Exception e) {
                cell.setCellType(CellType.STRING);
                val = cell.getStringCellValue();
            }

            result = getDateData(entity, val);
            if (result == null) {
                return null;
            }
        }
        if (("class java.time.Instant").equals(classFullName)) {
            result = ((Date) result).toInstant();
        } else if (("class java.time.LocalDate").equals(classFullName)) {
            result = ((Date) result).toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
        } else if (("class java.time.LocalDateTime").equals(classFullName)) {
            result = ((Date) result).toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
        } else if (("class java.sql.Date").equals(classFullName)) {
            result = new java.sql.Date(((Date) result).getTime());
        } else if (("class java.sql.Time").equals(classFullName)) {
            result = new Time(((Date) result).getTime());
        } else if (("class java.sql.Timestamp").equals(classFullName)) {
            result = new Timestamp(((Date) result).getTime());
        }
    } else {
        switch (cell.getCellType()) {
        case STRING:
            result = cell.getRichStringCellValue() == null ? "" : cell.getRichStringCellValue().getString();
            break;
        case NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                if ("class java.lang.String".equals(classFullName)) {
                    result = formateDate(entity, cell.getDateCellValue());
                }
            } else {
                result = readNumericCell(cell);
            }
            break;
        case BOOLEAN:
            result = Boolean.toString(cell.getBooleanCellValue());
            break;
        case BLANK:
            break;
        case ERROR:
            break;
        case FORMULA:
            try {
                result = readNumericCell(cell);
            } catch (Exception e1) {
                try {
                    result = cell.getRichStringCellValue() == null ? ""
                            : cell.getRichStringCellValue().getString();
                } catch (Exception e2) {
                    throw new RuntimeException("???", e2);
                }
            }
            break;
        default:
            break;
        }
    }
    return result;
}

From source file:cn.afterturn.easypoi.excel.imports.CellValueService.java

License:Apache License

private Object readNumericCell(Cell cell) {
    Object result = null;/* ww  w. j a v a2  s. c  om*/
    double value = cell.getNumericCellValue();
    if (((int) value) == value) {
        result = (int) value;
    } else {
        result = value;
    }
    return result;
}

From source file:cn.afterturn.easypoi.util.PoiSheetUtil.java

License:Apache License

private static void cloneCell(Cell cNew, Cell cOld) {
    cNew.setCellComment(cOld.getCellComment());
    cNew.setCellStyle(cOld.getCellStyle());

    switch (cNew.getCellType()) {
    case BOOLEAN: {
        cNew.setCellValue(cOld.getBooleanCellValue());
        break;/*from ww w .  j av  a2 s.  c o m*/
    }
    case NUMERIC: {
        cNew.setCellValue(cOld.getNumericCellValue());
        break;
    }
    case STRING: {
        cNew.setCellValue(cOld.getStringCellValue());
        break;
    }
    case ERROR: {
        cNew.setCellValue(cOld.getErrorCellValue());
        break;
    }
    case FORMULA: {
        cNew.setCellFormula(cOld.getCellFormula());
        break;
    }
    default:
        cNew.setCellValue(cOld.getStringCellValue());
    }

}

From source file:cn.bzvs.excel.imports.ExcelImportServer.java

License:Apache License

/**
 * ?key,?????/*from  ww w  .  j a v  a  2 s .c om*/
 * 
 * @param cell
 * @return
 */
private String getKeyValue(Cell cell) {
    Object obj = null;
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        obj = cell.getStringCellValue();
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        obj = cell.getBooleanCellValue();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        obj = cell.getNumericCellValue();
        break;
    case Cell.CELL_TYPE_FORMULA:
        obj = cell.getCellFormula();
        break;
    default:
        cell.setCellType(Cell.CELL_TYPE_STRING);
        obj = cell.getStringCellValue();
    }
    return obj == null ? null : obj.toString().trim();
}

From source file:cn.edu.zjnu.acm.judge.util.excel.ExcelUtil.java

License:Apache License

private static JsonElement parseAsJsonElement(Cell cell, FormulaEvaluator evaluator) {
    switch (cell.getCellType()) {
    case NUMERIC:
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            return new JsonPrimitive(DateFormatterHolder.FORMATTER.format(cell.getDateCellValue().toInstant()));
        } else {//ww w.j  a va 2s .c  o m
            return new JsonPrimitive(cell.getNumericCellValue());
        }
    case STRING:
        return new JsonPrimitive(cell.getStringCellValue());
    case FORMULA:
        CellValue cellValue = evaluator.evaluate(cell);
        switch (cellValue.getCellType()) {
        case NUMERIC:
            return new JsonPrimitive(cellValue.getNumberValue());
        case STRING:
            return new JsonPrimitive(cellValue.getStringValue());
        case BLANK:
            return new JsonPrimitive("");
        case BOOLEAN:
            return new JsonPrimitive(cellValue.getBooleanValue());
        case ERROR:
        default:
            return null;
        }
    case BLANK:
        return new JsonPrimitive("");
    case BOOLEAN:
        return new JsonPrimitive(cell.getBooleanCellValue());
    case ERROR:
    default:
        return null;
    }
}

From source file:cn.mypandora.util.MyExcelUtil.java

License:Apache License

/**
 * @param workbook /*w  w w  . j  av a  2  s .co m*/
 * @param fieldNames ??
 * @param sheetName ???
 * @return
 */
private static List<Map<String, String>> execRead(Workbook workbook, String fieldNames, String... sheetName) {
    String[] strKey = fieldNames.split(",");
    List<Map<String, String>> listMap = new ArrayList<>();
    int i = 1;
    try {
        Sheet sheet;
        if (sheetName.length == 0) {
            sheet = workbook.getSheetAt(0);
        } else {
            sheet = workbook.getSheet(sheetName[0]);
        }
        while (true) {
            Row row = sheet.getRow(i);
            if (row == null) {
                break;
            }
            Map<String, String> map = new HashMap<String, String>();
            map.put("rowid", String.valueOf(row.getRowNum()));
            for (int keyIndex = 0; keyIndex < strKey.length; keyIndex++) {
                Cell cell;
                cell = row.getCell(keyIndex);
                String cellValue = "";
                if (cell != null) {
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC: {
                        // ?cell?Date
                        if (DateUtil.isCellDateFormatted(cell)) {
                            // Date?CellDate
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            cellValue = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                        }
                        // 
                        else {
                            // ??Cell
                            Integer num = new Integer((int) cell.getNumericCellValue());
                            cellValue = String.valueOf(num);
                        }
                        break;
                    }
                    case Cell.CELL_TYPE_STRING:
                        cellValue = cell.getRichStringCellValue().getString();
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        System.out.println(cell.getBooleanCellValue());
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        System.out.println(cell.getCellFormula());
                        break;
                    default:
                        cellValue = " ";
                    }
                }
                map.put(strKey[keyIndex], cellValue);
            }
            listMap.add(map);
            i++;
        }
    } catch (Exception e) {
        logger.debug("?" + i + "??");
        throw new RuntimeException(e);
    }
    return listMap;
}