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:com.hust.zsuper.DealWithPatent.WorkhseetToMySQL.java

License:Open Source License

private String getStringValue(ExcelType type, Cell cell) {
    switch (type) {
    case DATE://from   w ww. j  av  a 2  s  .  c  o m
        return "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm").format(cell.getDateCellValue()) + "'";
    case NUMERIC:
        return String.valueOf(cell.getNumericCellValue());
    case BOOLEAN:
        return String.valueOf(cell.getBooleanCellValue());
    case STRING:
        return "'" + cell.getStringCellValue().replaceAll("'", "\\\\'") + "'";
    default:
        return null;
    }
}

From source file:com.ibm.db2j.GExcel.java

License:Open Source License

/**
 * looks for the column definition and initializes the following attributes :
 * /*from   w w w .j  av  a2s .c o m*/
 * - numberOfColumns
 * - columnIndexes
 * - columnNames
 *
 * If a column which contains no values is ignored.
 * 
 * If firstRowIsMetaData is true, the column names will be extract from the first row of the spreadsheet.
 * Else, they will be automatically generated : COLUMN1, COLUMN2...
 * 
 * @param sheet
 */
private void findColumns(Sheet sheet) {
    numberOfColumns = 0;

    columnIndexes = new ArrayList<Integer>();
    columnNames = new ArrayList<String>();

    Row firstRow = sheet.getRow(firstRowIndex);

    int columnLabelIndex = 1;

    if (firstRowIsMetaData) {
        //For each column
        for (int i = firstColumnIndex; i <= lastColumnIndex; ++i) {
            //Get the first cell in the column
            Cell cell = firstRow.getCell(i, Row.CREATE_NULL_AS_BLANK);

            columnIndexes.add(cell.getColumnIndex());

            int cellType = cell.getCellType();
            if (Cell.CELL_TYPE_FORMULA == cellType) {
                cellType = cell.getCachedFormulaResultType();
                //                System.out.println("cell type is now getCachedFormulaResultType() = " + cellType );
            }

            //Build the column names depending on it's type
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                //                case Cell.CELL_TYPE_FORMULA: // DO NOT USE: getCellFormula() !!!

                //                   System.out.println("cell type string" );

                // Note: Javadoc on method getStringCellValue() states:
                // "get the value of the cell as a string - for numeric cells we throw an exception. For blank cells we return an empty string. 
                // For formulaCells that are not string Formulas, we throw an exception"

                ++numberOfColumns;
                columnNames.add(cell.getStringCellValue().replaceAll("[\\ ]", "_")); // Note we should not have to do this in future... once defect is fixed
                break;

            case Cell.CELL_TYPE_NUMERIC:

                //                   System.out.println("cell type numeric " + 
                //                         ( DateUtil.isCellDateFormatted( cell ) ? "date: " + cell.getDateCellValue().toString() : "num: " + cell.getNumericCellValue() ) );

                ++numberOfColumns;
                columnNames.add(DateUtil.isCellDateFormatted(cell) ? cell.getDateCellValue().toString()
                        : "" + cell.getNumericCellValue());
                break;

            case Cell.CELL_TYPE_BOOLEAN:

                //                   System.out.println("cell type boolean" );

                ++numberOfColumns;
                columnNames.add("" + cell.getBooleanCellValue());
                break;

            default:

                //                   System.out.println("cell type default" );

                ++numberOfColumns;
                columnNames.add(DEFAULT_COLUMN_LABEL + "" + columnLabelIndex);
                break;
            }

            columnLabelIndex++;
        }
    } else {
        //For each column
        for (int i = firstColumnIndex; i <= lastColumnIndex; ++i) {
            //Get the first cell in the column
            Cell cell = firstRow.getCell(i, Row.CREATE_NULL_AS_BLANK);

            columnIndexes.add(cell.getColumnIndex());
            columnNames.add(DEFAULT_COLUMN_LABEL + "" + columnLabelIndex++);
        }
    }
}

From source file:com.ifeng.vdn.ip.repository.service.impl.AliDataFactoryTest.java

License:Apache License

@Test
public void importDataFromExcel() {
    Workbook wb = null;//from w  w w  .j a  va  2  s  .c  om
    String input = "src/test/resources/data/ip_18_Alibaba.xlsx";

    try {

        List<IPModel> ipList = new ArrayList<IPModel>();

        wb = WorkbookFactory.create(new FileInputStream(input));
        Sheet sheet = wb.getSheetAt(0);

        int totalRows = sheet.getPhysicalNumberOfRows();

        Cell ipCell = null;
        Cell totalCell = null;
        Cell locCell = null;

        String location = "";
        int total;
        String ipString = "";

        String[] items = null;

        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);

            ipCell = row.getCell(1);
            totalCell = row.getCell(2);
            locCell = row.getCell(3);

            try {
                String country = "";
                String area = "";
                String region = "";
                String city = "";
                String isp = "";
                IPModel model = new IPModel();

                ipString = ipCell.getStringCellValue();
                total = (int) totalCell.getNumericCellValue();
                location = locCell.getStringCellValue();

                items = location.split(" ");
                if (items != null) {
                    if (items[0] != null) {
                        country = items[0];
                    }
                    if (items[1] != null) {
                        area = items[1];
                    }
                    if (items[2] != null) {
                        region = items[2];
                    }
                    if (items[3] != null) {
                        city = items[3];
                    }
                    if (items[4] != null) {
                        isp = items[4];
                    }

                    model.setIp(ipString);
                    model.setCountry(country.trim());
                    model.setArea(area.trim());
                    model.setRegion(region.trim());
                    model.setCity(city.trim());
                    model.setIsp(isp.trim());
                    model.setTotal(total);

                    ipList.add(model);
                }

                log.info("IP: {}, Total{}, location: {}", ipString, total, location);

                if ((i % 1000) == 0) {
                    aliDataFactory.importData(ipList);
                    ipList = new ArrayList<IPModel>();
                }

            } catch (Exception e) {
                e.getMessage();
                //log.error(e.getMessage(), e);

            }

        }

        if (ipList.size() > 0) {
            aliDataFactory.importData(ipList);
        }

    } catch (InvalidFormatException e) {
        e.printStackTrace();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

}

From source file:com.impetus.kvapps.runner.UserBroker.java

License:Apache License

private String extractCellData(Row row, int iCurrent) throws Exception {
    Cell cell = (Cell) row.getCell(iCurrent);
    if (cell == null) {
        return "";
    } else {//w  w w.  j a va  2 s .  c  o  m
        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);
                    return dateFormat.format(date);
                } else {
                    throw new Exception("Invalid Date value found at row number " + row.getRowNum()
                            + " and column number " + cell.getColumnIndex());
                }
            } else {
                return value + "";
            }
        case HSSFCell.CELL_TYPE_STRING:
            return cell.getStringCellValue();
        case HSSFCell.CELL_TYPE_BLANK:
            return null;
        default:
            return null;
        }
    }
}

From source file:com.inspiracode.nowgroup.scspro.xl.source.ExcelFile.java

License:Open Source License

private String getCellAsString(Cell cell) {
    if (cell == null)
        return "";

    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_NUMERIC:
        int intValue = (int) Math.ceil(cell.getNumericCellValue());
        return String.valueOf(intValue);
    case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();
    default://from   w ww  .ja v a 2s .  co  m
        return "";
    }
}

From source file:com.jaredrummler.android.devices.Main.java

License:Apache License

private static String getStringCellValue(Cell cell) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue() ? "true" : "false";
    case Cell.CELL_TYPE_NUMERIC:
        return Double.toString(cell.getNumericCellValue());
    case Cell.CELL_TYPE_STRING:
    default://from  w  ww.j  a  v a2s. co  m
        return cell.getStringCellValue();
    }
}

From source file:com.jmc.jfxxlsdiff.util.POIXlsUtil.java

public static Object getCellValue(Cell cell) {
    Object cv = null;/*from ww w  .java 2s .  c  o  m*/

    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK: {
        break;
    }
    case Cell.CELL_TYPE_BOOLEAN: {
        cv = cell.getBooleanCellValue();
        break;
    }
    case Cell.CELL_TYPE_ERROR: {
        cv = cell.getErrorCellValue();
        break;
    }
    case Cell.CELL_TYPE_FORMULA: {
        cv = getFormulaValue(cell);
        break;
    }
    case Cell.CELL_TYPE_NUMERIC: {
        if (DateUtil.isCellDateFormatted(cell)) {
            // format in form of M/D/YY
            //Calendar cal = Calendar.getInstance();
            //cal.setTime( DateUtil.getJavaDate( d ) );
            //cv = cal.getTime();
            cv = cell.getDateCellValue();
        } else {
            cv = cell.getNumericCellValue();
        }
        break;
    }
    case Cell.CELL_TYPE_STRING: {
        cv = cell.getStringCellValue();
        break;
    }
    default: {
        logger.log(Level.WARNING, "Unexpected cell type = {0}", cell.getCellType());
        break;
    }
    }

    return cv;
}

From source file:com.joalgoca.validatorLayout.layoutDefinition.XLSXDocumentLayout.java

@Override
public ResponseValidator validateDocument(InputStream inputStream) {
    ResponseValidator response;/*from  w  ww.jav a2 s . co  m*/
    StringBuilder stringBuilder = new StringBuilder();
    if (isReadyToValidate() && inputStream != null) {
        HashMap rowsType = new HashMap();
        for (int i = 0; i < documentValidator.getListRowValidator().size(); i++) {
            rowsType.put(documentValidator.getListRowValidator().get(i).getName(), i);
        }
        try {
            int rownum = 0;
            int wrong = 0;
            int right = 0;
            int skip = 0;
            OPCPackage pkg = OPCPackage.open(inputStream);
            XSSFWorkbook workBook = new XSSFWorkbook(pkg);
            FormulaEvaluator evaluator = workBook.getCreationHelper().createFormulaEvaluator();
            XSSFSheet xssfSheet = workBook.getSheetAt(0);
            for (Row row : xssfSheet) {
                String rowType = row.getCell(0).getStringCellValue();
                if (rowsType.containsKey(rowType)) {
                    RowValidator rowValidator = documentValidator.getListRowValidator()
                            .get((int) rowsType.get(rowType));
                    int columnNum = rowValidator.getListItemValidator().size();
                    if (row.getLastCellNum() == columnNum) {
                        String[] values = new String[columnNum];
                        int i = 0;
                        for (Cell cell : row) {
                            switch (evaluator.evaluateInCell(cell).getCellType()) {
                            case Cell.CELL_TYPE_NUMERIC:
                                values[i] = cell.getNumericCellValue() + "";
                                break;
                            case Cell.CELL_TYPE_STRING:
                                values[i] = cell.getStringCellValue();
                                break;
                            case Cell.CELL_TYPE_FORMULA:
                                values[i] = "";
                                break;
                            case Cell.CELL_TYPE_BLANK:
                                values[i] = "";
                                break;
                            }
                            i++;
                        }
                        ResponseValidator responseValidator = rowValidator.validate(values);
                        if (!responseValidator.isSuccess()) {
                            wrong++;
                            stringBuilder.append("{\"row\":").append(rownum).append(",\"message\":")
                                    .append(responseValidator.getMessage()).append(",");
                        } else
                            right++;
                    } else {
                        wrong++;
                        stringBuilder.append("{\"row\":").append(rownum)
                                .append(",\"success\":false,\"message\":\"Line wrong size\"},").toString();
                    }
                } else {
                    skip++;
                    stringBuilder.append("{\"row\":").append(rownum)
                            .append(",\"success\":false,\"message\":\"Unknow row type\"},").toString();
                }
                rownum++;

            }

            response = new ResponseValidator(wrong == 0,
                    "{\"skip\":" + skip + ",\"wrong\":" + wrong + ",\"right\":" + right + ",\"count\":" + rownum
                            + ",\"errorMessages\":["
                            + (stringBuilder.toString().length() > 0
                                    ? stringBuilder.substring(0, stringBuilder.toString().length() - 1)
                                    : "")
                            + "]}");
        } catch (Exception ex) {
            Logger.getLogger(FlatFixedDocumentLayout.class.getName()).log(Level.SEVERE, null, ex);
            response = new ResponseValidator(false, stringBuilder.append("\"success\":false,\"message\":\"")
                    .append(ex.getMessage()).append("\"}").toString());
        } finally {
            try {
                inputStream.close();
            } catch (IOException ex) {
                response = new ResponseValidator(false, stringBuilder.append("\"success\":false,\"message\":\"")
                        .append(ex.getMessage()).append("\"}").toString());
            }
        }
    } else {
        response = new ResponseValidator(false,
                stringBuilder.append("\"success\":false,\"message\":\"No configuration loaded\"}").toString());
    }
    return response;
}

From source file:com.jogo.dao.RepositorioDao.java

@Override
public List importar() {

    //arry de usuario para armazenar os meus usuarios que pega do excel na folha 1
    List<Usuario> usuarios = new ArrayList<>();

    //CAPTURA OS DADOS DO USUARIO NO EXCEL
    try {/*from ww w  . ja v  a2  s  .c  o m*/
        //capturando o excel para meu wb
        wb = WorkbookFactory.create(new FileInputStream(patch));

        //CAPTURAR A PRIMEIRA FOLHA DO EXCEL 
        Sheet folha = wb.getSheetAt(0);

        //criO um iterator para interagir com as linhas
        Iterator filaIterator = folha.rowIterator();

        //ENQUANTO HOUVER LINHAS O ITERATOR ME TRAZ.
        while (filaIterator.hasNext()) {

            //CAPTURO A LINHA DO EXCEL
            Row linha = (Row) filaIterator.next();

            //CRIO UM INTERATOR PARA INTERAGIR COM AS COLUNAS
            Iterator colunaIterator = linha.cellIterator();

            //CRIOU A CLASSE DE USUARIO E ADD DENTRO DO MEU ARRAY
            Usuario user = new Usuario();
            usuarios.add(user);

            //ENAUQNTO HOUVER COLUNAS O INTERATOR ME TRAZ.
            while (colunaIterator.hasNext()) {

                //COM A LINHA E A COLUNA JA POSSO CRIAR UMA CELULA.
                Cell celula = (Cell) colunaIterator.next();

                //APOS CAPTURAR O VALOR NA CELULA, SETO PARA MINHA CLASSE USUARIO QUE CRIEI LOGO ACIMA.
                if (celula != null) {
                    //CAPTURO O TIPO DA CELULA, NESSE CASO E STRING E NUMERICO(INT)
                    switch (celula.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        //CONVERTENDO O VALOR PARA INTEIRO.
                        user.setPontuacao((int) Math.round(celula.getNumericCellValue()));
                        break;
                    case Cell.CELL_TYPE_STRING:
                        user.setNome(celula.getStringCellValue());
                        break;

                    }

                }

            }

        }

    } catch (IOException | InvalidFormatException | EncryptedDocumentException e) {
    }
    return usuarios;
}

From source file:com.krawler.esp.servlets.XLSDataExtractor.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();
    }/*ww w. j a v  a2 s.c  o m*/

    JSONArray jArr = new JSONArray();
    for (int i = 0; i <= noOfRowsDisplayforSample; i++) {
        Row row = sheet.getRow(i);
        JSONObject obj = new JSONObject();
        JSONObject jtemp1 = new JSONObject();
        if (row == null) {
            jArr.put(obj);
            continue;
        }
        if (maxCol < row.getLastCellNum())
            maxCol = row.getLastCellNum();
        for (int j = 0; j < row.getLastCellNum(); j++) {
            Cell cell = row.getCell(j);
            if (cell == null)
                continue;
            String colHeader = new CellReference(i, j).getCellRefParts()[2];
            String val = null;
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                val = Double.toString(cell.getNumericCellValue());
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    java.util.Date df = HSSFDateUtil.getJavaDate(Double.parseDouble(val));
                    String df_full = "yyyy-MM-dd";
                    DateFormat sdf = new SimpleDateFormat(df_full);
                    val = sdf.format(df);
                }
                break;
            case Cell.CELL_TYPE_STRING:
                val = cell.getRichStringCellValue().getString();
                break;
            }
            if (i == 0) { // List of Headers (Consider first row as Headers)
                jtemp1 = new JSONObject();
                jtemp1.put("header", val);
                jtemp1.put("index", j);
                jobj.append("Header", jtemp1);
            }
            obj.put(colHeader, val);
        }
        jArr.put(obj);
    }
    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", "Image has been successfully uploaded");
    jobj.put("lsuccess", true);
    jobj.put("valid", true);
    return jobj;
}