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.ocs.dynamo.importer.impl.BaseXlsImporter.java

License:Apache License

/**
 * Retrieves the value of a cell as a string. Returns <code>null</code> if the cell does not
 * contain a string//w  w  w .  j  av a2  s . c o  m
 * 
 * @param cell
 * @return
 */
protected String getStringValue(Cell cell) {
    if (cell != null
            && (Cell.CELL_TYPE_STRING == cell.getCellType() || cell.getCellType() == Cell.CELL_TYPE_BLANK)) {
        String value = cell.getStringCellValue();
        return value == null ? null : value.trim();
    } else if (cell != null && Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
        // if a number is entered in a field that is supposed to contain a
        // string, Excel goes insane. We have to compensate for this
        Double d = cell.getNumericCellValue();
        return d == null ? null : Long.toString(d.longValue());
    }
    return null;
}

From source file:com.opendoorlogistics.core.tables.io.PoiIO.java

License:Open Source License

private static String getTextValue(Cell cell, int treatAsCellType) {
    if (cell == null) {
        return null;
    }/*from www  .j  a  v  a  2s.c  o m*/
    switch (treatAsCellType) {
    case Cell.CELL_TYPE_STRING:
        return cell.getRichStringCellValue().getString();

    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            Date date = cell.getDateCellValue();
            if (date != null) {
                Calendar cal = Calendar.getInstance();
                cal.setTime(date);
                @SuppressWarnings("deprecation")
                int year = date.getYear();
                if (year == -1) {
                    // equivalent to 1899 which is the first data .. assume its a time
                    String s = ODL_TIME_FORMATTER.format(date);
                    return s;
                }
                //   System.out.println(year);
            }
            return cell.getDateCellValue().toString();
        } else {
            String ret = Double.toString(cell.getNumericCellValue());
            if (ret.endsWith(".0")) {
                ret = ret.substring(0, ret.length() - 2);
            }

            return ret;
        }

    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue() ? "T" : "F";

    case Cell.CELL_TYPE_FORMULA:
        return cell.getCellFormula();

    case Cell.CELL_TYPE_BLANK:
        return null;
    }
    return "";
}

From source file:com.opengamma.financial.security.equity.GICSCodeDescription.java

License:Open Source License

/**
 * Get the value of the Apache POI Cell as a String.  If the Cell type is numeric (always a double with POI),
 * the value is converted to an integer.  The GCIS file does not contain any floating point values so (at this time)
 * this is a valid operation//from ww w.ja va  2  s . c  o m
 * 
 * @param cell Apache POI Cell
 * @return String value
 */
static String getGICSCellValue(Cell cell) {
    if (cell == null) {
        return "";
    }
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_NUMERIC:
        return Integer.valueOf((int) cell.getNumericCellValue()).toString();
    case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();
    case Cell.CELL_TYPE_BOOLEAN:
        return Boolean.toString(cell.getBooleanCellValue());
    case Cell.CELL_TYPE_BLANK:
        return "";
    default:
        return "null";
    }
}

From source file:com.opengamma.integration.copier.sheet.reader.SimpleXlsSheetReader.java

License:Open Source License

private static String getCellAsString(Cell cell) {

    if (cell == null) {
        return "";
    }/*from w  w w. j a v a  2 s.  com*/
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_NUMERIC:
        //return Double.toString(cell.getNumericCellValue());
        return (new DecimalFormat("#.##")).format(cell.getNumericCellValue());
    case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();
    case Cell.CELL_TYPE_BOOLEAN:
        return Boolean.toString(cell.getBooleanCellValue());
    case Cell.CELL_TYPE_BLANK:
        return "";
    default:
        return null;
    }
}

From source file:com.openitech.db.model.ExcelDataSource.java

License:Apache License

@Override
public boolean loadData(boolean reload, int oldRow) {
    boolean result = false;

    if (isDataLoaded && !reload) {
        return false;
    }//  w ww  . j  av a2s .c  o m
    if (sourceFile != null) {
        try {
            Workbook workBook = WorkbookFactory.create(new FileInputStream(sourceFile));
            //        HSSFWorkbook workBook = new HSSFWorkbook(new FileInputStream(sourceFile));
            Sheet sheet = workBook.getSheetAt(0);
            DataFormatter dataFormatter = new DataFormatter(Locale.GERMANY);
            FormulaEvaluator formulaEvaluator = workBook.getCreationHelper().createFormulaEvaluator();

            int lastRowNum = sheet.getLastRowNum();

            boolean isFirstLineHeader = true;

            //count = sheet. - (isFirstLineHeader ? 1 : 0);
            int tempCount = 0;
            for (int j = 0; j <= lastRowNum; j++) {
                //zane se z 0
                Row row = row = sheet.getRow(j);
                if (row == null) {
                    continue;
                }

                // display row number in the console.
                System.out.println("Row No.: " + row.getRowNum());
                if (isFirstLineHeader && row.getRowNum() == 0) {
                    populateHeaders(row);
                    continue;
                }
                tempCount++;

                Map<String, DataColumn> values;
                if (rowValues.containsKey(row.getRowNum())) {
                    values = rowValues.get(row.getRowNum());
                } else {
                    values = new HashMap<String, DataColumn>();
                    rowValues.put(row.getRowNum(), values);
                }

                // once get a row its time to iterate through cells.
                int lastCellNum = row.getLastCellNum();
                for (int i = 0; i <= lastCellNum; i++) {
                    DataColumn dataColumn = new DataColumn();
                    Cell cell = row.getCell(i);
                    if (cell == null) {
                        continue;
                    }
                    System.out.println("Cell No.: " + cell.getColumnIndex());
                    System.out.println("Value: " + dataFormatter.formatCellValue(cell));
                    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                        dataColumn = new DataColumn(dataFormatter.formatCellValue(cell, formulaEvaluator));
                    } else {
                        dataColumn = new DataColumn(dataFormatter.formatCellValue(cell));
                    }

                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC: {
                        // cell type numeric.
                        System.out.println("Numeric value: " + cell.getNumericCellValue());
                        dataColumn = new DataColumn(dataFormatter.formatCellValue(cell));
                        break;
                    }
                    case Cell.CELL_TYPE_STRING:
                        // cell type string.
                        System.out.println("String value: " + cell.getStringCellValue());
                        dataColumn = new DataColumn(dataFormatter.formatCellValue(cell));
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        // cell type string.
                        System.out.println("String value: " + cell.getBooleanCellValue());
                        dataColumn.setValue(cell.getBooleanCellValue(), Boolean.class);
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        // cell type string.
                        System.out.println(
                                "Formula value: " + dataFormatter.formatCellValue(cell, formulaEvaluator));
                        dataColumn = new DataColumn(dataFormatter.formatCellValue(cell, formulaEvaluator));
                        break;
                    default:
                        dataColumn.setValue(cell.getStringCellValue(), String.class);
                        break;
                    }

                    values.put(getColumnName(cell.getColumnIndex()).toUpperCase(), dataColumn);

                }
            }

            count = tempCount;

            isDataLoaded = true;
            //se postavim na staro vrstico ali 1
            if (oldRow > 0) {
                absolute(oldRow);
            } else {
                first();
            }

            result = true;
        } catch (Exception ex) {
            Logger.getLogger(ExcelDataSource.class.getName()).log(Level.SEVERE, null, ex);
            result = false;
        }
    }

    return result;
}

From source file:com.ostrichemulators.semtool.poi.main.POIReader.java

License:Open Source License

/**
 * Always return a non-null string (will be "" for null cells).
 *
 * @param cell/*from   www. j a v  a2 s.  c o  m*/
 * @return
 */
private static String getString(Cell cell) {
    if (null == cell) {
        return "";
    }

    switch (cell.getCellType()) {
    case NUMERIC:
        return Double.toString(cell.getNumericCellValue());
    case BOOLEAN:
        return Boolean.toString(cell.getBooleanCellValue());
    case FORMULA:
        return cell.getCellFormula();
    default:
        return cell.getStringCellValue();
    }
}

From source file:com.pdf.GetPdf.java

public static void addXls(Document document, String url, String type) throws IOException, DocumentException {
    Iterator<Row> rowIterator;
    int colNo;// w  w w  .j  a v  a 2 s .  c o m
    if (type.equals("xls")) {
        HSSFWorkbook excelWorkbook = new HSSFWorkbook(new URL(url).openStream());
        HSSFSheet my_worksheet = excelWorkbook.getSheetAt(0);
        rowIterator = my_worksheet.iterator();
        colNo = my_worksheet.getRow(0).getLastCellNum();
    } else {
        XSSFWorkbook excelWorkbook1 = new XSSFWorkbook(new URL(url).openStream());
        XSSFSheet my_worksheet = excelWorkbook1.getSheetAt(0);
        rowIterator = my_worksheet.iterator();
        colNo = my_worksheet.getRow(0).getLastCellNum();
    }
    PdfPTable my_table = new PdfPTable(colNo);
    PdfPCell table_cell = null;
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next(); //Read Rows from Excel document       
        Iterator<Cell> cellIterator = row.cellIterator();//Read every column for every row that is READ
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next(); //Fetch CELL
            if (cell.getCellType() == (Cell.CELL_TYPE_NUMERIC)) {
                table_cell = new PdfPCell(new Phrase(new Double(cell.getNumericCellValue()).toString()));
                System.out.println(cell.getNumericCellValue());
                my_table.addCell(table_cell);
            } else if (cell.getCellType() == (Cell.CELL_TYPE_STRING)) {
                table_cell = new PdfPCell(new Phrase(cell.getStringCellValue()));
                System.out.println(cell.getStringCellValue());
                my_table.addCell(table_cell);
            } else if (cell.getCellType() == (Cell.CELL_TYPE_FORMULA)) {
                table_cell = new PdfPCell(new Phrase(cell.getCellFormula()));
                my_table.addCell(table_cell);
            } else if (cell.getCellType() == (Cell.CELL_TYPE_BLANK)) {
                table_cell = new PdfPCell(new Phrase(""));
                my_table.addCell(table_cell);
            } else {
                table_cell = new PdfPCell(new Phrase(""));
                my_table.addCell(table_cell);
            }
        }
    }
    document.add(my_table);
}

From source file:com.pe.nisira.movil.view.action.RegistroPaleta.java

public void getExcelTablePaletaList(List sheetData) {
    try {//  ww w.  j  ava 2  s.co  m
        if (sheetData.size() > 0) {
            List<Paleta> lista = new ArrayList<Paleta>();
            Paleta paleta = new Paleta();
            for (int i = 1; i < sheetData.size(); i++) {
                List list = (List) sheetData.get(i);
                paleta = new Paleta();
                for (int j = 0; j < list.size(); j++) {
                    Cell cell = (Cell) list.get(j);
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        System.out.print(cell.getNumericCellValue());
                    } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        System.out.print(cell.getRichStringCellValue());
                    } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                        System.out.print(cell.getBooleanCellValue());
                    }
                    switch (j) {
                    case 0:
                        paleta.setIdempresa(cell.getRichStringCellValue().getString());
                        break;
                    case 1:
                        paleta.setIdregistropaleta(cell.getRichStringCellValue().getString());
                        break;
                    case 2:
                        paleta.setIdproducto(cell.getRichStringCellValue().getString());
                        break;
                    case 3:
                        paleta.setNropaleta(cell.getRichStringCellValue().getString());
                        break;
                    case 4:
                        paleta.setDescproducto(cell.getRichStringCellValue().getString());
                        break;
                    case 5:
                        paleta.setIdmedida(cell.getRichStringCellValue().getString());
                        break;
                    case 6:
                        paleta.setCantidad(cell.getRichStringCellValue().getString());
                        break;
                    case 7:
                        paleta.setPeso(Double.parseDouble(cell.getRichStringCellValue().getString()));
                        break;
                    case 8:
                        paleta.setIdlotep(cell.getRichStringCellValue().getString());
                        break;
                    case 9:
                        paleta.setIdcliente(cell.getRichStringCellValue().getString());
                        break;
                    case 10:
                        paleta.setIdclieprov(cell.getRichStringCellValue().getString());
                        break;
                    case 11:
                        paleta.setIdenvase(cell.getRichStringCellValue().getString());
                        break;
                    case 12:
                        paleta.setDescenvase(cell.getRichStringCellValue().getString());
                        break;
                    case 13:
                        paleta.setCerrado(cell.getRichStringCellValue().getString());
                        break;
                    case 14:
                        paleta.setNromanual(cell.getRichStringCellValue().getString());
                        break;
                    }
                    if (j < list.size() - 1) {
                        System.out.print(", ");
                    }
                }
                lista.add(paleta);
                System.out.println("");
            }
            System.out.println("Terminado ...");
            this.listPaleta = lista;
            RequestContext.getCurrentInstance().update("datos:tbl");
        }
    } catch (Exception ex) {

    }
}

From source file:com.photon.phresco.eshop.utils.ServiceUtil.java

License:Apache License

public static String getValue(Cell cell) {
    if (cell == null) {
        return null;
    }//w  w  w . j a  v  a 2  s .  com

    if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
        return cell.getStringCellValue();
    }

    if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
        return String.valueOf(cell.getNumericCellValue());
    }

    if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
        return String.valueOf(cell.getBooleanCellValue());
    }

    if (Cell.CELL_TYPE_BLANK == cell.getCellType()) {
        return null;
    }

    return null;
}

From source file:com.photon.phresco.framework.commons.FrameworkUtil.java

License:Apache License

private void updateIndex(float totalPass, float totalFail, float totalNotApplicable, float totalBlocked,
        Row next1, int totalTestCases, String operation) {
    Cell successCell = next1.getCell(3);
    int pass = (int) totalPass;
    successCell.setCellValue(pass);/*  w w w  . j  ava  2 s  . c  o m*/

    Cell failureCell = next1.getCell(4);
    int fail = (int) totalFail;
    failureCell.setCellValue(fail);

    Cell notAppCell = next1.getCell(5);
    int notApp = (int) totalNotApplicable;
    notAppCell.setCellValue(notApp);

    Cell blockedCell = next1.getCell(7);
    int blocked = (int) totalBlocked;
    blockedCell.setCellValue(blocked);
    double numericCellValue;
    Cell cell = next1.getCell(8);
    if (StringUtils.isNotEmpty(operation) && operation.equalsIgnoreCase("delete")) {
        numericCellValue = totalTestCases;
    } else {
        numericCellValue = cell.getNumericCellValue();
    }
    Cell notExeCell = next1.getCell(6);
    int notExe = (int) (numericCellValue - (pass + fail + notApp + blocked));
    notExeCell.setCellValue(notExe);

    Cell testCovrgeCell = next1.getCell(9);
    int total = 0;
    if (StringUtils.isNotEmpty(operation) && operation.equalsIgnoreCase("delete")) {
        total = totalTestCases;
        Cell totalCell = next1.getCell(8);
        totalCell.setCellValue(total);
    } else {
        total = (int) cell.getNumericCellValue();
    }
    float notExetd = notExe;
    float testCovrge = (float) ((total - notExetd) / total) * 100;
    testCovrgeCell.setCellValue(Math.round(testCovrge));
}