Example usage for org.apache.poi.ss.usermodel CellType NUMERIC

List of usage examples for org.apache.poi.ss.usermodel CellType NUMERIC

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. CellType
  6. NUMERIC

Introduction

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

Prototype

CellType NUMERIC

To view the source code for org.apache.poi.ss.usermodel CellType NUMERIC.

Click Source Link

Document

Numeric cell type (whole numbers, fractional numbers, dates)

Usage

From source file:com.centurylink.mdw.drools.Excel2007Parser.java

License:Apache License

@SuppressWarnings("deprecation")
private void processSheet(XSSFSheet sheet, List<? extends DataListener> listeners) {

    int mergedRegionCount = sheet.getNumMergedRegions();
    CellRangeAddress[] mergedRanges = new CellRangeAddress[mergedRegionCount];
    for (int i = 0; i < mergedRegionCount; i++) {
        mergedRanges[i] = sheet.getMergedRegion(i);
    }//from w  w w .j a  v  a2 s  . c om

    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        XSSFRow row = sheet.getRow(i);
        if (row != null) {
            newRow(listeners, i, row.getLastCellNum());
            for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
                XSSFCell cell = row.getCell(cellNum);
                if (cell != null) {
                    CellRangeAddress merged = getRangeIfMerged(cell, mergedRanges);

                    if (merged != null) {
                        XSSFRow topRow = sheet.getRow(merged.getFirstRow());
                        XSSFCell topLeft = topRow.getCell(merged.getFirstColumn());
                        String cellValue = topLeft.getCellType() == CellType.NUMERIC.getCode()
                                ? String.valueOf(topLeft.getNumericCellValue())
                                : topLeft.getStringCellValue();
                        newCell(listeners, i, cellNum, cellValue, topLeft.getColumnIndex());
                    } else {
                        String cellValue = null;
                        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN)
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                        else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                            cellValue = String.valueOf(cell.getNumericCellValue());
                        else
                            cellValue = cell.getStringCellValue();
                        newCell(listeners, i, cellNum, cellValue, DataListener.NON_MERGED);
                    }
                }
            }
        }
    }
    finishSheet(listeners);
}

From source file:com.cloudera.sa.ExcelRecordReader.java

License:Apache License

private Text getCellValue(Cell cell) {
    Text out = new Text();
    CellType cellType = cell.getCellTypeEnum();

    if (cellType == CellType.STRING) {
        out.set(cell.getStringCellValue());
    } else if (cellType == CellType.NUMERIC) {
        out.set(String.valueOf(cell.getNumericCellValue()));
    } else if (cellType == CellType.FORMULA) {
        out.set(cell.getCellFormula());//ww  w  . j a v a2s  . c  om
    } else if (cellType == CellType.ERROR) {
        out.set(String.valueOf(cell.getErrorCellValue()));
    } else if (cellType == CellType.BOOLEAN) {
        out.set(String.valueOf(cell.getBooleanCellValue()));
    } else {
        out.set("");
    }

    return out;
}

From source file:com.ebay.xcelite.writer.SheetWriterAbs.java

License:Apache License

protected void writeToCell(Cell cell, Object fieldValueObj, Class<?> dataType) {
    cell.setCellStyle(CellStylesBank.get(sheet.getNativeSheet().getWorkbook()).getBoldStyle());
    if (fieldValueObj == null) {
        cell.setCellType(CellType.BLANK);
        cell.setCellValue("");
    } else {//www  .  j  a va2s  . c o m
        Class<?> type = fieldValueObj.getClass();
        if (dataType != null) {
            type = dataType;
        }
        if (type == Date.class) {
            cell.setCellValue((Date) fieldValueObj);
        } else if (type == Boolean.class) {
            cell.setCellValue((Boolean) fieldValueObj);
        } else if (type == Double.class || type == double.class || type == Integer.class || type == int.class
                || type == Long.class || type == long.class || type == Float.class || type == float.class
                || type == Short.class || type == short.class) {
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(Double.valueOf(fieldValueObj.toString()));
        } else {
            cell.setCellType(CellType.STRING);
            cell.setCellValue(fieldValueObj.toString());
        }
    }
}

From source file:com.funtl.framework.smoke.core.commons.excel.ImportExcel.java

License:Apache License

/**
 * ??/*w  w w  . j a  v  a  2  s .c o m*/
 *
 * @param row    ?
 * @param column ???
 * @return ?
 */
@SuppressWarnings("deprecation")
public Object getCellValue(Row row, int column) {
    Object val = "";
    try {
        Cell cell = row.getCell(column);
        if (cell != null) {
            if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                val = cell.getNumericCellValue();
            } else if (cell.getCellTypeEnum() == CellType.STRING) {
                val = cell.getStringCellValue();
            } else if (cell.getCellTypeEnum() == CellType.FORMULA) {
                val = cell.getCellFormula();
            } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
                val = cell.getBooleanCellValue();
            } else if (cell.getCellTypeEnum() == CellType.ERROR) {
                val = cell.getErrorCellValue();
            }
        }
    } catch (Exception e) {
        return val;
    }
    return val;
}

From source file:com.haulmont.cuba.gui.export.ExcelExporter.java

License:Apache License

protected void formatValueCell(HSSFCell cell, @Nullable Object cellValue,
        @Nullable MetaPropertyPath metaPropertyPath, int sizersIndex, int notificationRequired, int level,
        @Nullable Integer groupChildCount) {
    if (cellValue == null) {
        return;/*  w ww. j a  va2s  .  co  m*/
    }

    String childCountValue = "";
    if (groupChildCount != null) {
        childCountValue = " (" + groupChildCount + ")";
    }

    if (cellValue instanceof IdProxy) {
        cellValue = ((IdProxy) cellValue).get();
    }

    if (cellValue instanceof Number) {
        Number n = (Number) cellValue;
        final Datatype datatype = Datatypes.getNN(n.getClass());
        String str;
        if (sizersIndex == 0) {
            str = createSpaceString(level) + datatype.format(n);
            cell.setCellValue(str);
        } else {
            try {
                str = datatype.format(n);
                Number result = (Number) datatype.parse(str);
                if (result != null) {
                    if (n instanceof Integer || n instanceof Long || n instanceof Byte || n instanceof Short) {
                        cell.setCellValue(result.longValue());
                        cell.setCellStyle(integerFormatCellStyle);
                    } else {
                        cell.setCellValue(result.doubleValue());
                        cell.setCellStyle(doubleFormatCellStyle);
                    }
                }
            } catch (ParseException e) {
                throw new RuntimeException("Unable to parse numeric value", e);
            }
            cell.setCellType(CellType.NUMERIC);
        }
        if (sizers[sizersIndex].isNotificationRequired(notificationRequired)) {
            sizers[sizersIndex].notifyCellValue(str, stdFont);
        }
    } else if (cellValue instanceof Date) {
        Class javaClass = null;
        boolean supportTimezones = false;
        TimeZone timeZone = userSessionSource.getUserSession().getTimeZone();
        if (metaPropertyPath != null) {
            MetaProperty metaProperty = metaPropertyPath.getMetaProperty();
            if (metaProperty.getRange().isDatatype()) {
                javaClass = metaProperty.getRange().asDatatype().getJavaClass();
            }
            Boolean ignoreUserTimeZone = metadataTools.getMetaAnnotationValue(metaProperty,
                    IgnoreUserTimeZone.class);
            supportTimezones = timeZone != null && Objects.equals(Date.class, javaClass)
                    && !Boolean.TRUE.equals(ignoreUserTimeZone);
        }
        Date date = (Date) cellValue;
        if (supportTimezones) {
            TimeZone currentTimeZone = LocaleUtil.getUserTimeZone();
            try {
                LocaleUtil.setUserTimeZone(timeZone);
                cell.setCellValue(date);
            } finally {
                if (Objects.equals(currentTimeZone, TimeZone.getDefault())) {
                    LocaleUtil.resetUserTimeZone();
                } else {
                    LocaleUtil.setUserTimeZone(currentTimeZone);
                }
            }
        } else {
            cell.setCellValue(date);
        }

        if (Objects.equals(java.sql.Time.class, javaClass)) {
            cell.setCellStyle(timeFormatCellStyle);
        } else if (Objects.equals(java.sql.Date.class, javaClass)) {
            cell.setCellStyle(dateFormatCellStyle);
        } else {
            cell.setCellStyle(dateTimeFormatCellStyle);
        }
        if (sizers[sizersIndex].isNotificationRequired(notificationRequired)) {
            String str = Datatypes.getNN(Date.class).format(date);
            sizers[sizersIndex].notifyCellValue(str, stdFont);
        }
    } else if (cellValue instanceof Boolean) {
        String str = "";
        if (sizersIndex == 0) {
            str += createSpaceString(level);
        }
        str += ((Boolean) cellValue) ? trueStr : falseStr;
        cell.setCellValue(new HSSFRichTextString(str));
        if (sizers[sizersIndex].isNotificationRequired(notificationRequired)) {
            sizers[sizersIndex].notifyCellValue(str, stdFont);
        }
    } else if (cellValue instanceof EnumClass) {
        String nameKey = cellValue.getClass().getSimpleName() + "." + cellValue.toString();
        final String message = sizersIndex == 0
                ? createSpaceString(level) + messages.getMessage(cellValue.getClass(), nameKey)
                : messages.getMessage(cellValue.getClass(), nameKey);

        cell.setCellValue(message + childCountValue);
        if (sizers[sizersIndex].isNotificationRequired(notificationRequired)) {
            sizers[sizersIndex].notifyCellValue(message, stdFont);
        }
    } else if (cellValue instanceof Entity) {
        Entity entityVal = (Entity) cellValue;
        String instanceName = entityVal.getInstanceName();
        String str = sizersIndex == 0 ? createSpaceString(level) + instanceName : instanceName;
        str = str + childCountValue;
        cell.setCellValue(new HSSFRichTextString(str));
        if (sizers[sizersIndex].isNotificationRequired(notificationRequired)) {
            sizers[sizersIndex].notifyCellValue(str, stdFont);
        }
    } else if (cellValue instanceof Collection) {
        String str = "";
        cell.setCellValue(new HSSFRichTextString(str));
        if (sizers[sizersIndex].isNotificationRequired(notificationRequired)) {
            sizers[sizersIndex].notifyCellValue(str, stdFont);
        }
    } else {
        String strValue = cellValue == null ? "" : cellValue.toString();
        String str = sizersIndex == 0 ? createSpaceString(level) + strValue : strValue;
        str = str + childCountValue;
        cell.setCellValue(new HSSFRichTextString(str));
        if (sizers[sizersIndex].isNotificationRequired(notificationRequired)) {
            sizers[sizersIndex].notifyCellValue(str, stdFont);
        }
    }
}

From source file:com.jkoolcloud.tnt4j.streams.inputs.ExcelSXSSFRowStreamTest.java

License:Apache License

public static void printRow(Row row) {
    Iterator<Cell> cellIterator = row.cellIterator();
    System.out.print("|");
    while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();
        System.out.print(cell);/*from ww  w.  j  a  va2 s  .  c o m*/
        System.out.print("(");
        System.out.print(cell.getCellTypeEnum());
        if (cell.getCellTypeEnum().equals(CellType.NUMERIC) && DateUtil.isCellDateFormatted(cell)) {
            System.out.print("Date");
        }
        System.out.print(")");
        System.out.print("\t |");
    }
    System.out.println("|");
}

From source file:com.ksa.myanmarlottery.service.parser.ExcelFileParser.java

@Override
public List<Result> getResult(InputStream in) throws FileNotFoundException, IOException, ParseException {
    List<Prize> prizes = null;
    List<Result> resultList = new ArrayList<>();
    SimpleDateFormat format = new SimpleDateFormat("dd-MM-yyyy"); // 01-May-2017
    try {//from  w w w. j  a  va2  s  .c  o  m
        Workbook workbook = new XSSFWorkbook(in);
        Sheet datatypeSheet = workbook.getSheetAt(0);
        Iterator<Row> iterator = datatypeSheet.iterator();

        while (iterator.hasNext()) {

            Row currentRow = iterator.next();
            Cell cell0 = currentRow.getCell(0); // get first cell.

            if (cell0.getCellTypeEnum() == CellType.NUMERIC) {
                int numberic = (int) cell0.getNumericCellValue();
                log.info("Numberic - " + numberic);

                // check lottery type
                if (ConstantUtil.OLD_LOTTERY_TYPE == numberic || ConstantUtil.NEW_LOTTERY_TYPE == numberic) { // for lottery type result
                    Result result = new Result();
                    result.setType(numberic);
                    result.setNumberOfTimes((int) currentRow.getCell(1).getNumericCellValue());
                    //                        result.setResultFor(format.parse(currentRow.getCell(2).toString()));
                    result.setResultFor(currentRow.getCell(2).getDateCellValue());
                    result.setDataProvider(currentRow.getCell(3).getStringCellValue());
                    result.setCompanyName(currentRow.getCell(4).getStringCellValue());

                    prizes = new ArrayList<>();
                    result.setPrizes(prizes);
                    resultList.add(result);
                }

            } else if (cell0.getCellTypeEnum() == CellType.STRING) { // result data
                String character = cell0.getStringCellValue();
                log.info("character - " + character);

                // check validation for character.
                String value = charMap.get(character);
                if (value == null) {
                    throw new ParseException(
                            "Character is Not valid at Row: " + currentRow.getRowNum() + " > column:" + 0, 400);
                }
                Cell cell1 = currentRow.getCell(1);
                if (cell1.getCellTypeEnum() != CellType.NUMERIC) {
                    throw new ParseException(
                            "Should be Number at Row: " + currentRow.getRowNum() + " > column:" + 1, 400);
                }
                log.info("Cell Type " + cell1.getCellTypeEnum());
                int code = (int) cell1.getNumericCellValue();
                log.info("code - " + code + " Row:" + currentRow.getRowNum() + " > column:" + 1);
                String prizeTitle = currentRow.getCell(2).getStringCellValue();
                log.info("prizeTitle - " + prizeTitle);
                String prizeDesc = currentRow.getCell(4).getStringCellValue();
                log.info("prizeDesc - " + prizeDesc);
                prizes.add(new Prize(character, code, prizeTitle, prizeDesc));
            }
        }
        log.info("resultList size: " + resultList.size());
        for (Result r : resultList) {
            log.info("prizeList size: " + r.getPrizes().size());
        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
        throw e;
    } catch (IOException e) {
        e.printStackTrace();
        throw e;
    }
    return resultList;
}

From source file:com.ostrichemulators.semtool.poi.main.xlsxml.LoadingSheetXmlHandler.java

@Override
public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
    if (null != name) {
        switch (name) {
        case "row":
            rownum = Integer.parseInt(attributes.getValue("r")) - 1;
            currentrowdata.clear();/*from  w  ww .  ja v a 2s. c o m*/
            break;
        case "c": // c is a new cell
            String celltypestr = attributes.getValue("t");
            celltype = (formats.containsKey(celltypestr) ? formats.get(celltypestr) : CellType.BLANK);
            // dates don't always have a type attribute
            if (CellType.NUMERIC == celltype || null == celltypestr) {
                celltype = CellType.NUMERIC;

                // check if it's a date
                String styleidstr = attributes.getValue("s");
                int styleid = (null == styleidstr ? 0 : Integer.parseInt(styleidstr));

                XSSFCellStyle style = styles.getStyleAt(styleid);
                int formatIndex = style.getDataFormat();
                String formatString = style.getDataFormatString();
                isdate = DateUtil.isADateFormat(formatIndex, formatString);
            }

            String colname = attributes.getValue("r");
            colnum = getColNum(colname.substring(0, colname.lastIndexOf(Integer.toString(rownum + 1))));
            break;
        case "v": // new value for a cell
            setReading(true);
            resetContents();
            break;
        }
    }
}

From source file:de.jlo.talendcomp.excel.SpreadsheetInput.java

License:Apache License

private String getStringCellValue(Cell cell, int originalColumnIndex) throws Exception {
    String value = null;// w ww  . j a  v  a 2 s  .co m
    if (cell != null) {
        CellType cellType = cell.getCellTypeEnum();
        if (cellType == CellType.FORMULA) {
            try {
                value = getDataFormatter().formatCellValue(cell, getFormulaEvaluator());
            } catch (Exception e) {
                if (useCachedValuesForFailedEvaluations) {
                    cellType = cell.getCachedFormulaResultTypeEnum();
                    if (cellType == CellType.STRING) {
                        if (returnURLInsteadOfName) {
                            Hyperlink link = cell.getHyperlink();
                            if (link != null) {
                                if (concatenateLabelUrl) {
                                    String url = link.getAddress();
                                    if (url == null) {
                                        url = "";
                                    }
                                    String label = link.getLabel();
                                    if (label == null) {
                                        label = "";
                                    }
                                    value = label + "|" + url;
                                } else {
                                    value = link.getAddress();
                                }
                            } else {
                                value = cell.getStringCellValue();
                            }
                        } else {
                            value = cell.getStringCellValue();
                        }
                    } else if (cellType == CellType.NUMERIC) {
                        if (DateUtil.isCellDateFormatted(cell)) {
                            if (defaultDateFormat != null) {
                                Date d = cell.getDateCellValue();
                                if (d != null) {
                                    value = defaultDateFormat.format(d);
                                }
                            } else {
                                value = getDataFormatter().formatCellValue(cell);
                            }
                        } else {
                            if (overrideExcelNumberFormat) {
                                value = getNumberFormat(originalColumnIndex).format(cell.getNumericCellValue());
                            } else {
                                value = getDataFormatter().formatCellValue(cell);
                            }
                        }
                    } else if (cellType == CellType.BOOLEAN) {
                        value = cell.getBooleanCellValue() ? "true" : "false";
                    }
                } else {
                    throw e;
                }
            }
        } else if (cellType == CellType.STRING) {
            if (returnURLInsteadOfName) {
                Hyperlink link = cell.getHyperlink();
                if (link != null) {
                    if (concatenateLabelUrl) {
                        String url = link.getAddress();
                        if (url == null) {
                            url = "";
                        }
                        String label = link.getLabel();
                        if (label == null) {
                            label = "";
                        }
                        value = label + "|" + url;
                    } else {
                        value = link.getAddress();
                    }
                } else {
                    value = cell.getStringCellValue();
                }
            } else {
                value = cell.getStringCellValue();
            }
        } else if (cellType == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                value = getDataFormatter().formatCellValue(cell);
            } else {
                if (overrideExcelNumberFormat) {
                    value = getNumberFormat(originalColumnIndex).format(cell.getNumericCellValue());
                } else {
                    value = getDataFormatter().formatCellValue(cell);
                }
            }
        } else if (cellType == CellType.BOOLEAN) {
            value = cell.getBooleanCellValue() ? "true" : "false";
        } else if (cellType == CellType.BLANK) {
            value = null;
        }
    }
    return value;
}

From source file:de.jlo.talendcomp.excel.SpreadsheetInput.java

License:Apache License

private Double getDoubleCellValue(Cell cell) throws Exception {
    Double value = null;/*from w w  w.  j  av  a  2  s  .  c om*/
    if (cell != null) {
        CellType cellType = cell.getCellTypeEnum();
        if (cellType == CellType.FORMULA) {
            try {
                String s = getDataFormatter().formatCellValue(cell, getFormulaEvaluator());
                if (s != null && s.trim().isEmpty() == false) {
                    Number n = getNumberFormat(cell.getColumnIndex()).parse(s.trim());
                    value = n.doubleValue();
                }
            } catch (Exception e) {
                if (useCachedValuesForFailedEvaluations) {
                    cellType = cell.getCachedFormulaResultTypeEnum();
                    if (cellType == CellType.STRING) {
                        String s = cell.getStringCellValue();
                        if (s != null && s.trim().isEmpty() == false) {
                            Number n = getNumberFormat(cell.getColumnIndex()).parse(s.trim());
                            value = n.doubleValue();
                        }
                    } else if (cellType == CellType.NUMERIC) {
                        value = cell.getNumericCellValue();
                    }
                } else {
                    throw e;
                }
            }
        } else if (cellType == CellType.STRING) {
            String s = cell.getStringCellValue();
            if (s != null && s.trim().isEmpty() == false) {
                Number n = getNumberFormat(cell.getColumnIndex()).parse(s.trim());
                value = n.doubleValue();
            }
        } else if (cellType == CellType.NUMERIC) {
            value = cell.getNumericCellValue();
        }
    }
    return value;
}