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.avaya.plds.excel.ExcelRead.java

public List<String> getPoeticFeatureLoad(String value1, String value2, int sheetNo, int headers) {
    System.out.println(" Inside of  getPoeticFeatureLoad method ...");
    sheet = xssfWorkbook.getSheetAt(sheetNo);
    boolean read = false;
    List<String> dataList = new ArrayList<String>();
    rowIterator = sheet.iterator();/*from  ww  w  .  j av  a2  s  . c  o  m*/
    while (rowIterator.hasNext()) {
        StringBuilder builder = new StringBuilder();
        Row row = rowIterator.next();
        int rowNumber = row.getRowNum();
        if (row != null) {
            for (short i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {

                if (row.getCell(i) != null && (row.getCell(i).getCellType() == row.getCell(i).CELL_TYPE_STRING)
                        && row.getCell(i).getStringCellValue().contains(value1) && i == 0) {
                    read = true;
                    break;
                    //builder.append(value1).append("\t");
                }

                else if (row.getCell(i) != null
                        && (row.getCell(i).getCellType() == row.getCell(i).CELL_TYPE_STRING)
                        && row.getCell(i).getStringCellValue().contains(value2)) {
                    read = false;
                } else if (read) {
                    //   System.out.println("rowNumber "+ rowNumber);
                    maxCellIndex = (row.getLastCellNum() > maxCellIndex && rowNumber > 0) ? row.getLastCellNum()
                            : maxCellIndex;
                    //   System.out.println("maxCellIndex "+ maxCellIndex);
                    Cell cell = row.getCell(i);
                    if (cell != null) {
                        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                            //if(i >0)
                            builder.append(
                                    cell != null ? Double.valueOf(cell.getNumericCellValue()).longValue() : "")
                                    .append("\t");
                        } else {
                            //if(i >0 )
                            builder.append(cell != null ? cell.getStringCellValue() : "").append("\t");
                        }
                    } else {
                        //if(i >0)
                        builder.append("").append("\t");
                    }
                }
                if (headers == rowNumber) {
                    //if(i>0)
                    builder.append(row.getCell(i).getStringCellValue()).append("\t");
                }
            }
            if (!builder.toString().equals("") && !builder.toString().matches("^ null.*"))
                dataList.add(builder.toString().replaceFirst(",", ""));
        }

    }
    return dataList;

}

From source file:com.axelor.studio.service.data.validator.ValidatorService.java

License:Open Source License

public void addLog(String log, String sheetName, int rowNum) throws IOException {

    if (logFile == null) {
        logFile = File.createTempFile("ImportLog", ".xlsx");
        logBook = new XSSFWorkbook();
    }//  w  ww.j  ava  2s  .c  om

    XSSFSheet sheet = logBook.getSheet(sheetName);

    if (sheet == null) {
        sheet = logBook.createSheet(sheetName);
        XSSFRow titleRow = sheet.createRow(0);
        titleRow.createCell(0).setCellValue("Row Number");
        titleRow.createCell(1).setCellValue("Issues");
    }

    Iterator<Row> rowIterator = sheet.rowIterator();
    Row logRow = null;
    while (rowIterator.hasNext()) {
        Row sheetRow = rowIterator.next();
        Cell cell = sheetRow.getCell(0);
        if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC) {
            continue;
        }
        double value = cell.getNumericCellValue();
        if (value == rowNum + 1) {
            logRow = sheetRow;
            break;
        }
    }

    if (logRow == null) {
        logRow = sheet.createRow(sheet.getPhysicalNumberOfRows());
    }

    Cell cell = logRow.getCell(0);
    if (cell == null) {
        cell = logRow.createCell(0);
        cell.setCellValue(rowNum + 1);
    }
    cell = logRow.getCell(1);
    if (cell == null) {
        cell = logRow.createCell(1);
    }
    String oldValue = cell.getStringCellValue();
    if (oldValue == null) {
        cell.setCellValue(log);
    } else {
        cell.setCellValue(oldValue + "\n" + log);
    }

}

From source file:com.b2international.snowowl.datastore.server.importer.ExcelUtilities.java

License:Apache License

private static String extractAsString(final Cell cell, final boolean formatNumber) {
    String value = "";

    if (cell == null) {
        return value;
    }/*from  w ww .j a v a2  s. c  om*/

    FormulaEvaluator formulaEvaluator = cell.getSheet().getWorkbook().getCreationHelper()
            .createFormulaEvaluator();

    int type = cell.getCellType();

    switch (type) {
    case Cell.CELL_TYPE_NUMERIC:
        if (formatNumber) {
            value = convertToString(cell.getNumericCellValue());
        } else {
            value = convertToStringWithoutFormat(cell.getNumericCellValue());
        }
        break;
    case Cell.CELL_TYPE_STRING:
        value = cell.getStringCellValue();
        break;
    case Cell.CELL_TYPE_FORMULA:
        CellValue cellValue = formulaEvaluator.evaluate(cell);
        value = cellValue.getStringValue(); //type should be checked
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        value = Boolean.toString(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_BLANK:
        //do nothing, sctId is null
        break;
    default:
        LOGGER.log(Level.SEVERE, "Unsupported cell type:" + type + " for cell: " + cell);
        break;
    }
    return null == value ? "" : value;
}

From source file:com.b2international.snowowl.snomed.core.refset.automap.XlsParser.java

License:Apache License

/**
 * @param cell/*  w w  w .  ja  v a2 s  . c om*/
 * @return the textual representation of the cell or empty string if the cell is empty (null)
 */
private String getStringValue(Cell cell) {
    String value = "";
    if (cell != null) {
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            value = cell.getRichStringCellValue().getString();
            break;
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                value = cell.getDateCellValue().toString();
            } else {
                value = Integer.toString((int) cell.getNumericCellValue());
            }
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            value = Boolean.toString(cell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            value = cell.getCellFormula();
            break;
        }
    }
    return value;
}

From source file:com.b2international.snowowl.snomed.importer.net4j.SnomedSubsetImportUtil.java

License:Apache License

private String getStringValue(final Cell cell) {
    String value = "";

    //   empty cell
    if (cell == null) {
        return "";
    }//from www  .  jav  a  2s. c  o m

    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        value = cell.getRichStringCellValue().getString();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            value = cell.getDateCellValue().toString();
        } else {
            value = Integer.toString((int) cell.getNumericCellValue());
        }
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        value = Boolean.toString(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_FORMULA:
        value = cell.getCellFormula();
        break;
    }

    return value;
}

From source file:com.bayareasoftware.chartengine.ds.ExcelDataStream.java

License:Apache License

private Object getCellData(Cell cell, int rowNum, int type, int index) {
    evaluator.evaluate(cell);//from w  w  w  .j  ava2 s . c o m
    if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK || type == UNKNOWN) {
        return null;
    }
    //p("getting from row#" + rowNum + " col#" + cell.getCellNum());
    switch (type) {
    case STRING:
        return getCellString(cell);
    case DATE: {
        if (ExcelInference.isCellDateFormatted(cell)) {
            Date d = cell.getDateCellValue();
            if (d != null) {
                return d;
            }
        }
        // figure out date format
        Date ret = null;
        String dstr = getCellString(cell);
        if (dfmts[index - 1] != null) {
            ret = this.parseDate(dstr, dfmts[index - 1], -1, index);
        } else if (metadata.getColumnFormat(index) != null) {
            dfmts[index - 1] = DateUtil.createDateFormat(metadata.getColumnFormat(index));
            ret = this.parseDate(dstr, dfmts[index - 1], -1, index);
        } else if (dstr != null) {
            if (dateRecognizer == null) {
                dateRecognizer = new DateRecognizer();
            }
            dateRecognizer.reset();
            dateRecognizer.parse(dstr);
            if (!dateRecognizer.failed()) {
                dfmts[index - 1] = dateRecognizer.getSimpleDateFormat();
                ret = this.parseDate(dstr, dfmts[index - 1], -1, index);
            }
        }
        return ret;
    }
    case DOUBLE:
        return cell.getNumericCellValue();
    case INTEGER:
        double d = cell.getNumericCellValue();
        return new Double(d).intValue();
    case BOOLEAN:
        String s = cell.getRichStringCellValue().getString();
        return "true".equalsIgnoreCase(s);
    case IGNORE:
        return null;
    default:
        throw new AssertionError("unexpected type: " + DataType.toString(type));
    }
}

From source file:com.bayareasoftware.chartengine.ds.util.ExcelInference.java

License:Apache License

public static String getCellString(Cell cell, HSSFFormulaEvaluator eval, DateFormat dfmt) {
    if (cell == null) {
        return null;
    }//from  www .  j  a  v a  2s . co  m
    String ret = null;
    eval.evaluate(cell);
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_NUMERIC:
    case HSSFCell.CELL_TYPE_FORMULA: // ?
        if (isCellDateFormatted(cell)) {
            if (dfmt == null) {
                dfmt = new SimpleDateFormat("yyyy-MM-dd");
            }
            Date d = cell.getDateCellValue();
            if (d != null) {
                ret = dfmt.format(d);
            } else {
                ret = "";
            }
        } else {
            try {
                ret = "" + cell.getNumericCellValue();
            } catch (IllegalStateException ise) {
                int errVal = cell.getErrorCellValue();
                String formula = cell.getCellFormula();
                int cacheType = cell.getCachedFormulaResultType();
                throw new RuntimeException(ise.getMessage() + ": errVal=" + errVal + " formula='" + formula
                        + "' cacheType=" + cacheType);
            }
        }
        break;
    case HSSFCell.CELL_TYPE_BLANK:
        ret = null;
        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        ret = "" + cell.getBooleanCellValue();
        break;
    case HSSFCell.CELL_TYPE_STRING:
    default:
        ret = cell.getRichStringCellValue().getString();
    }
    return ret;
}

From source file:com.beyondb.io.ExcelControl.java

@Override
public Object[][] readTableContent() throws IOException, InvalidFormatException, Exception {
    try {/*  w w w.  j  ava 2s.  com*/
        //OPCPackage pkg = OPCPackage.open(file);
        //            InputStream m_InputStream = new FileInputStream(m_File);
        Sheet sheet = null;
        //            if (!m_InputStream.markSupported()) {
        //                m_InputStream = new PushbackInputStream(m_InputStream, 8);
        //            } 
        //            if (POIFSFileSystem.hasPOIFSHeader(m_InputStream)) {
        //                HSSFWorkbook hSSFWorkbook = new HSSFWorkbook(m_InputStream);
        //                 sheet  = (Sheet)hSSFWorkbook.getSheetAt(0);
        //            
        //             } else if (POIXMLDocument.hasOOXMLHeader(m_InputStream)) {
        //                XSSFWorkbook xSSFWorkbook = new XSSFWorkbook(OPCPackage.open(m_File));
        //               sheet  = (Sheet)xSSFWorkbook.getSheetAt(0);
        //             }
        //             else {
        //                throw new IllegalArgumentException("excel?poi??");
        //            }
        sheet = getSheet();
        if (sheet != null) {
            if (sheet.getLastRowNum() == 0) {
                throw new Exception("Excel");
            }
            //?
            m_RowNum = sheet.getLastRowNum() + 1;

            //                m_ColumnNum = sheet.getRow(0).getPhysicalNumberOfCells();
            m_ColumnNum = sheet.getRow(0).getLastCellNum();
            m_TableStr = new Object[m_RowNum][m_ColumnNum];

            for (int rindex = 0; rindex < m_RowNum; rindex++) {
                Row row = sheet.getRow(rindex);
                for (int cindex = 0; cindex < m_ColumnNum; cindex++) {
                    Cell cell = row.getCell(cindex);

                    if (cell == null) {
                        m_TableStr[rindex][cindex] = "";
                    } else {
                        String value = "";
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            //                          System.out.println(cell.getRichStringCellValue().getString());                          
                            value = cell.getRichStringCellValue().getString().replace("\n", "");
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                //                                System.out.println(cell.getDateCellValue());
                                value = cell.getDateCellValue().toString();
                            } else {

                                DecimalFormat df = new DecimalFormat("#");
                                value = String.valueOf(cell.getNumericCellValue());
                                double d = cell.getNumericCellValue();
                                int dInt = (int) d;
                                BigDecimal b1 = new BigDecimal(value);
                                BigDecimal b2 = new BigDecimal(Integer.toString(dInt));
                                double dPoint = b1.subtract(b2).doubleValue();
                                if (dPoint == 0) {
                                    //?
                                    value = df.format(cell.getNumericCellValue());
                                }
                            }
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            //                            System.out.println(cell.getBooleanCellValue());
                            value = cell.getBooleanCellValue() + "";
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            //                            System.out.println(cell.getCellFormula());
                            value = cell.getCellFormula();
                            break;
                        case Cell.CELL_TYPE_BLANK:
                            value = "";
                        default:
                            //                            System.out.println();
                            value = "";
                        }
                        m_TableStr[row.getRowNum()][cell.getColumnIndex()] = value;
                    }
                }
            }
        }

    } catch (IOException | InvalidFormatException e) {
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "excel??", e);
        throw e;

    } catch (Exception ex) {
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "excel??", ex);

        throw ex;
    } finally {
        m_InputStream.close();
    }

    return m_TableStr;
}

From source file:com.bizosys.dataservice.dao.ReadXLS.java

License:Apache License

@Override
protected List<String> populate() throws SQLException {

    checkCondition();/*w w w  .j a  v a  2s  .c o m*/

    Workbook workbook = getWorkbook();
    Sheet sheet = workbook.createSheet();

    ResultSetMetaData md = rs.getMetaData();
    int totalCol = md.getColumnCount();
    String[] cols = createLabels(md, totalCol);

    try {

        if (null != templateFile) {
            File templateFileObject = new File(templateFile);
            if (templateFileObject.exists()) {
                Workbook templateWorkbook = new HSSFWorkbook(new FileInputStream(templateFileObject));
                Sheet templatesheet = templateWorkbook.getSheetAt(0);
                Iterator<Row> rowIterator = templatesheet.iterator();

                while (rowIterator.hasNext()) {
                    Row templateRow = rowIterator.next();
                    Row row = sheet.createRow(startRowIndex++);

                    Iterator<Cell> cellIterator = templateRow.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell templateCell = cellIterator.next();
                        Cell cell = row.createCell(templateCell.getColumnIndex());
                        cell.setCellType(templateCell.getCellType());
                        switch (templateCell.getCellType()) {
                        case Cell.CELL_TYPE_BLANK:
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            cell.setCellValue(templateCell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_ERROR:
                            cell.setCellValue(templateCell.getErrorCellValue());
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            cell.setCellValue(templateCell.getCellFormula());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            cell.setCellValue(templateCell.getNumericCellValue());
                            break;
                        case Cell.CELL_TYPE_STRING:
                            cell.setCellValue(templateCell.getStringCellValue());
                            break;
                        }
                    }
                }
            } else {
                System.err.println("Can not read " + templateFileObject.getAbsolutePath());
            }

        }

        while (this.rs.next()) {
            createRecord(totalCol, cols, sheet);
        }
        workbook.write(out);
    } catch (IOException ex) {
        throw new SQLException(ex);
    }
    return null;
}

From source file:com.bizosys.dataservice.dao.WriteToXls.java

License:Apache License

public void write(List<Object[]> records) throws Exception {
    Workbook workbook = getWorkbook();/*from  w w w .jav a2 s . c  om*/
    Sheet sheet = workbook.createSheet();

    if (null != templateFile) {
        File templateFileObject = new File(templateFile);
        if (templateFileObject.exists()) {
            Workbook templateWorkbook = new HSSFWorkbook(new FileInputStream(templateFileObject));
            Sheet templatesheet = templateWorkbook.getSheetAt(0);
            Iterator<Row> rowIterator = templatesheet.iterator();

            while (rowIterator.hasNext()) {
                Row templateRow = rowIterator.next();
                Row row = sheet.createRow(startRowIndex++);

                Iterator<Cell> cellIterator = templateRow.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell templateCell = cellIterator.next();
                    Cell cell = row.createCell(templateCell.getColumnIndex());
                    cell.setCellType(templateCell.getCellType());
                    switch (templateCell.getCellType()) {
                    case Cell.CELL_TYPE_BLANK:
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        cell.setCellValue(templateCell.getBooleanCellValue());
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        cell.setCellValue(templateCell.getErrorCellValue());
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        cell.setCellValue(templateCell.getCellFormula());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        cell.setCellValue(templateCell.getNumericCellValue());
                        break;
                    case Cell.CELL_TYPE_STRING:
                        cell.setCellValue(templateCell.getStringCellValue());
                        break;
                    }
                }
            }
        } else {
            System.err.println("Can not read " + templateFileObject.getAbsolutePath());
        }
    }

    for (Object[] cols : records) {
        createRecord(cols, sheet);
    }
    workbook.write(out);

}