Example usage for org.apache.poi.ss.usermodel Cell 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.cms.utils.ExcelReader.java

public static List importExcel(File file, int iSheet, int iBeginRow, int iFromCol, int iToCol, int rowBack)
        throws FileNotFoundException {
    List lst = new ArrayList();
    FileInputStream flieInput = new FileInputStream(file);
    HSSFWorkbook workbook;//  ww w.ja  v a  2 s . co m
    try {
        workbook = new HSSFWorkbook(flieInput);
        HSSFSheet worksheet = workbook.getSheetAt(iSheet);
        int irowBack = 0;
        for (int i = iBeginRow; i <= worksheet.getLastRowNum(); i++) {
            Object[] obj = new Object[iToCol - iFromCol + 1];
            Row row = worksheet.getRow(i);
            if (row != null) {
                int iCount = 0;
                int check = 0;
                for (int j = iFromCol; j <= iToCol; j++) {
                    Cell cell = row.getCell(j);
                    if (cell != null) {
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            obj[iCount] = cell.getStringCellValue().trim();
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                Date date = cell.getDateCellValue();
                                obj[iCount] = DateTimeUtils.convertDateToString(date, "dd/MM/yyyy");
                            } else {
                                Double doubleValue = (Double) cell.getNumericCellValue();
                                //String.format("%.0f", doubleValue);
                                List<String> lstValue = DataUtil.splitDot(String.valueOf(doubleValue));
                                if (lstValue.get(1).matches("[0]+")) {
                                    obj[iCount] = lstValue.get(0);
                                } else {
                                    obj[iCount] = String.format("%.2f", doubleValue).trim();
                                }
                            }

                            break;
                        case Cell.CELL_TYPE_BLANK:
                            check++;
                            break;
                        }
                    } else {
                        obj[iCount] = null;
                    }
                    iCount += 1;
                }
                if (check != (iToCol - iFromCol + 1)) {
                    lst.add(obj);
                }

            } else {
                irowBack += 1;
            }
            if (irowBack == rowBack) {
                break;
            }
        }
    } catch (IOException ex) {
        lst = null;
    }
    return lst;
}

---

From source file:com.cn.util.Units.java

/**
 * ?Excel?/*from  w  w w .  j  a  v a  2s  .  c om*/
 *
 * @param cell
 * @return
 */
public static String getCellValue(Cell cell) {
    String result = null;
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        result = String.valueOf(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_NUMERIC:
        result = String.valueOf(cell.getNumericCellValue());
        break;
    case Cell.CELL_TYPE_STRING:
        result = cell.getStringCellValue();
        break;
    default:
        result = cell.getStringCellValue();
        break;
    }
    return result.trim();
}

---

From source file:com.cn.util.Units.java

/**
 * ?Cell?/*ww  w.  ja va 2  s .  c  o m*/
 *
 * @param cell
 * @return
 */
public static String getCellFormatValue(Cell cell) {
    String cellvalue = "";
    if (cell != null) {
        // ?CellType
        switch (cell.getCellType()) {
        // ?CellTypeNUMERIC
        case Cell.CELL_TYPE_NUMERIC:
        case Cell.CELL_TYPE_FORMULA: {
            // ?cell?Date
            if (DateUtil.isCellDateFormatted(cell)) {
                // DateData?

                //1?data?2011-10-12 0:00:00
                //cellvalue = cell.getDateCellValue().toLocaleString();
                //2?data??2011-10-12
                Date date = cell.getDateCellValue();
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                cellvalue = sdf.format(date);
            } // 
            else {
                // ??Cell
                cellvalue = String.valueOf(cell.getNumericCellValue());
            }
            break;
        }
        // ?CellTypeSTRIN
        case Cell.CELL_TYPE_STRING:
            // ??Cell
            cellvalue = cell.getRichStringCellValue().getString();
            break;
        // Cell
        default:
            cellvalue = " ";
        }
    } else {
        cellvalue = "";
    }
    return cellvalue;
}

---

From source file:com.consensus.qa.framework.ExcelOperations.java

public void SetCellValueToUsed(FileNames fileName, SheetName sheetName, String searchString)
        throws IOException {
    String filePath = FilePath(fileName);
    fileInput = new FileInputStream(new File(filePath));
    workBook = new XSSFWorkbook(fileInput);
    String sheet = sheetName.toString();
    int rowIndex = -1;

    try {//from   ww  w  .  j  a v a  2  s  .co  m
        if (sheetName.toString().contains("_")) {
            String[] Name = sheetName.toString().split("_");
            if (Name[0].contains("IMEI")) {
                sheet = Name[1] + " " + Name[0];
            } else
                sheet = Name[1];
        }
        worksheet = workBook.getSheet(sheet);
        System.out.println(worksheet.getSheetName());
        Iterator<Row> rowIterator = worksheet.rowIterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC && String
                        .valueOf(Double.valueOf(cell.getNumericCellValue()).longValue()).equals(searchString)) {
                    rowIndex = row.getRowNum();
                    break;
                } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    if (cell.getStringCellValue().equals(searchString))
                        rowIndex = row.getRowNum();
                    break;
                }
            }
        }
        if (rowIndex != -1) {
            boolean flag = false;
            Row searchStringRow = worksheet.getRow(rowIndex);
            System.out.println(searchStringRow.getPhysicalNumberOfCells());
            Iterator<Cell> statusChangeCell = searchStringRow.cellIterator();
            while (statusChangeCell.hasNext()) {
                Cell statusCell = statusChangeCell.next();
                if (statusCell.getStringCellValue().toLowerCase()
                        .equals(Status.INUSE.toString().toLowerCase())) {
                    statusCell.setCellValue(Status.USED.toString());
                    flag = true;
                }
            }
            if (flag == false)
                Log.error("FAILED TO FIND INUSE FIELD for " + searchString + " @FileName: " + filePath
                        + ", Sheet: " + sheet);
        }
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        WriteAndCloseFile(filePath, fileInput, workBook);
    }
}

---

From source file:com.consensus.qa.framework.ExcelOperations.java

public String GetIMEINumber(FileNames fileName, SheetName workSheet) throws IOException {
    String filePath = FilePath(fileName);
    String sheet = null;/*from w w  w . j ava 2 s .com*/
    String tempSheet = null;
    String imei = "";
    int statusColumn = -1;
    try {
        int numOfWorkSheets = 0;

        fileInput = new FileInputStream(new File(filePath));
        workBook = new XSSFWorkbook(fileInput);
        numOfWorkSheets = workBook.getNumberOfSheets();
        for (int count = 0; count < numOfWorkSheets; count++) {
            if (workSheet.toString().toLowerCase().contains(workBook.getSheetName(count).toLowerCase())) {
                sheet = workBook.getSheetName(count);
                break;
            }
        }
        worksheet = workBook.getSheet(sheet);
        if (sheet.equals("iPhone 5C")) {
            tempSheet = sheet;
            sheet = "iPhone 4S";
        }

        switch (sheet) {
        case "4G IMEI":
            for (int i = 0; i < worksheet.getPhysicalNumberOfRows(); i++) {
                row = worksheet.getRow(i);
                Cell cell = row.getCell(2);
                if (row.getCell(5).toString().equals(Status.UNUSED.toString())) {
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        imei = String.valueOf(cell.getNumericCellValue());
                    } else
                        imei = cell.getStringCellValue();
                    row.getCell(5).setCellValue(Status.INUSE.toString());
                    break;
                }
            }
            return imei;

        case "3G":
            for (int i = 0; i < worksheet.getPhysicalNumberOfRows(); i++) {
                row = worksheet.getRow(i);
                if (row.getCell(4).toString().equals(Status.UNUSED.toString())) {
                    imei = String.valueOf(row.getCell(2).getNumericCellValue());
                    worksheet.getRow(i).getCell(4).setCellValue(Status.INUSE.toString());
                    break;
                }
            }

        case "Jetpack":
            statusColumn = CreateStatusColumn(filePath, sheet);
            if (statusColumn == -1) {
                statusColumn = 3;
            }

            for (int i = 2; i < worksheet.getPhysicalNumberOfRows(); i++) {
                row = worksheet.getRow(i);
                Cell cell = row.getCell(1);
                if (row.getCell(statusColumn).toString().equals(Status.UNUSED.toString())) {
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        imei = String.valueOf((long) cell.getNumericCellValue());
                    } else
                        imei = cell.getStringCellValue();
                    row.getCell(statusColumn).setCellValue(Status.INUSE.toString());
                    break;
                }
            }

        case "iPhone 4S":
            if (tempSheet.contains("5C"))
                statusColumn = CreateStatusColumn(filePath, tempSheet);
            if (statusColumn == -1) {
                Cell cell = worksheet.getRow(0).createCell(4);
                cell.setCellValue(Status.STATUS.toString());
                statusColumn = 4;
            }
            for (int i = 1; i < worksheet.getPhysicalNumberOfRows(); i++) {
                row = worksheet.getRow(i);
                Cell cell = row.getCell(0);
                if (row.getCell(statusColumn).getCellType() == Cell.CELL_TYPE_BLANK) {
                    row.createCell(statusColumn).setCellValue(Status.UNUSED.toString());
                }
                if (row.getCell(statusColumn).toString().equals(Status.UNUSED.toString())) {
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        imei = String.valueOf((long) cell.getNumericCellValue());
                    } else
                        imei = cell.getStringCellValue();
                    if (imei != "" || imei != null)
                        row.getCell(statusColumn).setCellValue(Status.INUSE.toString());
                    break;
                }
            }
        }
    }

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

    finally {
        WriteAndCloseFile(filePath, fileInput, workBook);
    }

    return imei;
}

---

From source file:com.cordys.coe.ac.fileconnector.utils.ExcelRead.java

License:Apache License

/**
 * Read records from Excel file//from  w  w w.  j a va 2s  .  c om
 *
 * @param vcConfig The validator configuration object.
 * @param bUseTupleOld
 * @param filename Name of the Excel file.
 * @param doc Document conatins the request.
 * @param iResponsenode The record XML structure root node, or zero, if only validation is needed.
 * @param sheetno Sheet index of the Excel file.
 * @param startrow row index from which data to be read.
 * @param endrow   row index upto which data to be read.
 * @param startcolumn column index from which data to be read.
 * @param endcolumn column index upto which data to be read.
 */
public static void readall(ValidatorConfig vcConfig, Boolean bUseTupleOld, String filename, Document doc,
        int iResponsenode, int sheetno, int startrow, int endrow, int startcolumn, int endcolumn)
        throws FileException {

    Workbook book = null;
    Sheet sheet;
    Cell cell;
    Row row;
    FileInputStream fileinp = null;
    String sRecordName = vcConfig.mConfigMap.get("excel").lRecordList.get(0).sRecordName;
    try {
        int iRow, iCol, sheetindex, noofsheets;
        File file = new File(filename);
        fileinp = new FileInputStream(filename);
        if (file.exists()) {
            if (file.getName().substring(file.getName().lastIndexOf(".") + 1).equalsIgnoreCase("xls")) {
                book = (Workbook) new HSSFWorkbook(fileinp);
            } else if (file.getName().substring(file.getName().lastIndexOf(".") + 1).equalsIgnoreCase("xlsx")) {
                book = new XSSFWorkbook(fileinp);
            } else {
                //ERROR
                fileinp.close();
            }
        } else {
            //ERROR
            fileinp.close();
        }

        if (sheetno != -1) {
            sheetindex = sheetno;
            noofsheets = sheetindex + 1;
        } else {
            sheetindex = 0;
            noofsheets = book.getNumberOfSheets();
        }
        for (; sheetindex < noofsheets; sheetindex++) {
            sheet = book.getSheetAt(sheetindex);

            if (endrow == -1) {
                endrow = sheet.getLastRowNum();
                if (startrow == -1) {
                    startrow = 0;
                }
            } else {
                endrow = startrow + endrow - 1;
                if (endrow > sheet.getLastRowNum()) {
                    endrow = sheet.getLastRowNum();
                }
            }

            if (endcolumn == -1) {
                endcolumn = 30;
                if (startcolumn == -1) {
                    startcolumn = 0;
                }
            }
            for (int i = startrow; i <= endrow; i++) {

                row = sheet.getRow(i);

                if (row == null) {
                    int iTup = doc.createElement("tuple", iResponsenode);

                    if (bUseTupleOld) {
                        iTup = doc.createElement("old", iTup);
                    }
                    iRow = doc.createElement(sRecordName, iTup);
                    //Node.setAttribute(iRow, "id", "" + i);
                    ListIterator fieldslist = vcConfig.mConfigMap.get("excel").lRecordList.get(0).lFieldList
                            .listIterator();
                    while (fieldslist.hasNext()) {
                        FieldType excelfields = (FieldType) fieldslist.next();
                        String sColumnName = excelfields.sFieldName;

                        iCol = doc.createTextElement(sColumnName, "", iRow);
                    }
                    continue;
                }
                int iTup = doc.createElement("tuple", iResponsenode);
                if (bUseTupleOld) {
                    iTup = doc.createElement("old", iTup);
                }
                iRow = doc.createElement(sRecordName, iTup);
                ListIterator fieldslist = vcConfig.mConfigMap.get("excel").lRecordList.get(0).lFieldList
                        .listIterator();
                while (fieldslist.hasNext()) {
                    FieldType excelfields = (FieldType) fieldslist.next();
                    int iColumnIndex = Integer.parseInt(excelfields.sColumnIndex);
                    cell = row.getCell(iColumnIndex);
                    String sColumnName = excelfields.sFieldName;
                    if (cell == null) {
                        iCol = doc.createTextElement(sColumnName, "", iRow);
                        continue;
                    }
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_BLANK:
                        iCol = doc.createTextElement(sColumnName, "", iRow);
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        iCol = doc.createTextElement(sColumnName, "" + cell.getBooleanCellValue(), iRow);

                        break;
                    case Cell.CELL_TYPE_ERROR:
                        iCol = doc.createTextElement(sColumnName, "", iRow);
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        iCol = doc.createTextElement(sColumnName, "" + cell.getCellFormula(), iRow);

                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            SimpleDateFormat simpledateformat = new SimpleDateFormat(
                                    "yyyy-MM-dd 'T' HH:mm:ss.S");
                            iCol = doc.createTextElement(sColumnName,
                                    "" + simpledateformat.format(cell.getDateCellValue()), iRow);

                        } else {
                            iCol = doc.createTextElement(sColumnName, "" + cell.getNumericCellValue(), iRow);
                        }
                        break;
                    case Cell.CELL_TYPE_STRING:
                        iCol = doc.createTextElement(sColumnName, "" + cell.getStringCellValue(), iRow);
                        break;
                    default:
                        System.out.println("default");
                    }
                }
            }
        }
    } catch (FileNotFoundException e) {
        throw new FileException(e, LogMessages.FILE_NOT_FOUND);
    } catch (IOException e) {
        throw new FileException(e, LogMessages.IOEXCEPTION_WHILE_READING_FILE, filename);
    } finally {
        try {
            fileinp.close();
        } catch (IOException ex) {
            Logger.getLogger(ExcelRead.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

---

From source file:com.creditcloud.carinsurance.CarInsuranceFeeServiceBean.java

/**
 * ??//from   w  ww. jav  a 2 s. co m
 *
 * @param feeFileExcel
 */
public void bacthUpdateCarInsuranceFeeSatatus(File feeFileExcel) {
    try {
        FileInputStream file = new FileInputStream(feeFileExcel);
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();
        String insuranceNum = "";
        int currentPeriod = 0;
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    currentPeriod = (int) cell.getNumericCellValue();
                    System.out.print((int) cell.getNumericCellValue() + "\t");
                    break;
                case Cell.CELL_TYPE_STRING:
                    insuranceNum = cell.getStringCellValue().trim();
                    System.out.print(cell.getStringCellValue() + "\t");
                    break;
                }
            }
            CarInsuranceFee fee = carInsuranceFeeDAO.findByInSuranceNumAndCurrentPeriod(insuranceNum,
                    currentPeriod);
            //?
            if (fee != null) {
                updateCarInsuranceFeeSatatus(fee.getId(), CarInsuranceStatus.CLEARED);
            }
            System.out.println("");
        }
        file.close();
    } catch (Exception e) {
        e.printStackTrace();
    }

}

---

From source file:com.ctb.importdata.ImportSFDataProcessor.java

public static void populateSFDataNumericColValue(SalesForceLicenseData sfld, Cell dataCell, Cell headerCell) {
    //TODO:: Set sf data object from numeric column value
    Double cellVal = dataCell.getNumericCellValue();
    if (headerCell.getStringCellValue().equals(Constants.CUSTOMER_ID))
        sfld.setCustomerId(cellVal.intValue());
    else if ((headerCell.getStringCellValue().equals(Constants.OAS_IMPLEMENTATION_ID)))
        sfld.setOasImplementationId(/*from w w  w  . ja v  a 2s . co m*/
                getStrValWithDesiredLen(cellVal.toString(), Constants.OAS_IMPLEMENTATION_ID_SIZE));
    else if ((headerCell.getStringCellValue().equals(Constants.IMPL_RECORD_TYPE)))
        sfld.setImplRecordType(getStrValWithDesiredLen(cellVal.toString(), Constants.IMPL_RECORD_TYPE_SIZE));
    else if ((headerCell.getStringCellValue().equals(Constants.CUSTOMER_ACCOUNT_NAME)))
        sfld.setCustomerAccountName(
                getStrValWithDesiredLen(cellVal.toString(), Constants.CUSTOMER_ACCOUNT_NAME_SIZE));
    else if ((headerCell.getStringCellValue().equals(Constants.ACCOUNT_STATE)))
        sfld.setAccountState(getStrValWithDesiredLen(cellVal.toString(), Constants.ACCOUNT_STATE_SIZE));// Need to restrict value to 2 characters only
    else if ((headerCell.getStringCellValue().equals(Constants.ORG_NODE_ID)))
        sfld.setOrgNodeId(cellVal.intValue());
    else if ((headerCell.getStringCellValue().equals(Constants.ORG_NODE_NAME)))
        sfld.setOrgNodeName(getStrValWithDesiredLen(cellVal.toString(), Constants.ORG_NODE_NAME_SIZE));
    else if ((headerCell.getStringCellValue().equals(Constants.CONTACT_PHONE)))
        sfld.setContactPhone(getStrValWithDesiredLen(cellVal.toString(), Constants.CONTACT_PHONE_SIZE));
    else if ((headerCell.getStringCellValue().equals(Constants.CONTACT)))
        sfld.setContact(getStrValWithDesiredLen(cellVal.toString(), Constants.CONTACT_SIZE));
    else if ((headerCell.getStringCellValue().equals(Constants.CONTACT_EMAIL)))
        sfld.setContactEmail(getStrValWithDesiredLen(cellVal.toString(), Constants.CONTACT_EMAIL_SIZE));
    else if ((headerCell.getStringCellValue().equals(Constants.CATEGORY_NAME)))
        sfld.setCategoryName(getStrValWithDesiredLen(cellVal.toString(), Constants.CATEGORY_NAME_SIZE));
    else if ((headerCell.getStringCellValue().equals(Constants.CATEGORY_LEVEL)))
        sfld.setCategoryLevel(cellVal.intValue());
    else if ((headerCell.getStringCellValue().equals(Constants.LICENSE_MODEL)))
        sfld.setLicenseModel(getStrValWithDesiredLen(cellVal.toString(), Constants.LICENSE_MODEL_SIZE));
    else if ((headerCell.getStringCellValue().equals(Constants.LICENSE_COUNT)))
        sfld.setLicenseCount(cellVal.intValue());
    else if ((headerCell.getStringCellValue().equals(Constants.ORDER_QUANTITY)))
        sfld.setOrderQuantity(cellVal.intValue());
    else if ((headerCell.getStringCellValue().equals(Constants.LICENSE_DISTRIBUTED_TO)))
        sfld.setLicenseDistributedTo(
                getStrValWithDesiredLen(cellVal.toString(), Constants.LICENSE_DISTRIBUTED_TO_SIZE));
    else if ((headerCell.getStringCellValue().equals(Constants.CREATED_DATE)))
        sfld.setCreatedDate(dataCell.getDateCellValue());
    else if ((headerCell.getStringCellValue().equals(Constants.INTERVAL_NAME)))
        sfld.setIntervalName(getStrValWithDesiredLen(cellVal.toString(), Constants.INTERVAL_NAME_SIZE));

}

---

From source file:com.cx.test.FromHowTo.java

License:Apache License

public static Object getCellValue(Cell cell, Class clazz) {
    String name = clazz.getSimpleName();
    if ("String".equals(name)) {
        return cell.getStringCellValue();
    }//from  ww w.j  av a2  s  . c om
    if ("Integer".equals(name)) {
        return cell.getNumericCellValue();
    }
    return null;
}

---

From source file:com.dataart.spreadsheetanalytics.engine.ConverterUtils.java

License:Apache License

/** Returns the new {@link CellValue} from provided {@link Cell}. */
public static ICellValue resolveCellValue(Cell c) {
    if (c == null) {
        return CellValue.BLANK;
    }// w w w  .j  av  a 2s. co  m

    switch (c.getCellType()) {
    case CELL_TYPE_NUMERIC: {
        return CellValue.from(c.getNumericCellValue());
    }
    case CELL_TYPE_STRING: {
        return CellValue.from(c.getStringCellValue());
    }
    case CELL_TYPE_BOOLEAN: {
        return CellValue.from(c.getBooleanCellValue());
    }
    case CELL_TYPE_ERROR: {
        return CellValue.from(forInt(c.getErrorCellValue()).getString());
    }
    case CELL_TYPE_BLANK: {
        return CellValue.BLANK;
    }
    case CELL_TYPE_FORMULA: {
        return CellValue.from(String.format("%s%s", FORMULA_PREFIX, c.getCellFormula()));
    }
    default: {
        throw new CalculationEngineException(
                String.format("Cell's type %s is not supported.", c.getCellType()));
    }
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »