Example usage for org.apache.poi.ss.usermodel DateUtil getJavaDate

List of usage examples for org.apache.poi.ss.usermodel DateUtil getJavaDate

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. DateUtil
  6. getJavaDate

Introduction

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

Prototype

public static Date getJavaDate(double date)

Source Link

Document

Given an Excel date with using 1900 date windowing, and converts it to a java.util.Date.

Usage

From source file:org.dbunit.dataset.excel.MyXlsTable.java

License:Open Source License

protected Object getDateValue(Cell cell) {
    logger.debug("getDateValue(cell={}) - start", cell);

    double numericValue = cell.getNumericCellValue();
    Date date = DateUtil.getJavaDate(numericValue);
    // Add the timezone offset again because it was subtracted automatically by Apache-POI (we need UTC)
    //        long tzOffset = TimeZone.getDefault().getOffset(date.getTime());
    //        date = new Date(date.getTime() + tzOffset);
    return date;/*from  www . ja  v  a  2s .  c om*/

    //TODO use a calendar for XLS Date objects when it is supported better by POI
    //        HSSFCellStyle style = cell.getCellStyle();
    //        HSSFDataFormatter formatter = new HSSFDataFormatter();
    //        Format f = formatter.createFormat(cell);
    //      String formatted = fomatter.formatCellValue(cell);
    //System.out.println("###"+formatted);
    //        Date dateValue = cell.getDateCellValue();
}

From source file:org.eclipse.emfforms.internal.spreadsheet.core.converter.EMFFormsSpreadsheetSingleAttributeConverter.java

License:Open Source License

private Object readCellValue(Cell cell, final EDataType attributeType) {
    if (isByte(attributeType.getInstanceClass())) {
        return Double.valueOf(cell.getNumericCellValue()).byteValue();
    } else if (isShort(attributeType.getInstanceClass())) {
        return Double.valueOf(cell.getNumericCellValue()).shortValue();
    } else if (isInteger(attributeType.getInstanceClass())) {
        return Double.valueOf(cell.getNumericCellValue()).intValue();
    } else if (isLong(attributeType.getInstanceClass())) {
        return Double.valueOf(cell.getNumericCellValue()).longValue();
    } else if (isFloat(attributeType.getInstanceClass())) {
        return Double.valueOf(cell.getNumericCellValue()).floatValue();
    } else if (isDouble(attributeType.getInstanceClass())) {
        return cell.getNumericCellValue();
    } else if (isBigInteger(attributeType.getInstanceClass())) {
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            return BigInteger.valueOf((long) cell.getNumericCellValue());
        }//  ww w.  ja va  2  s  .  c  o  m
        return new BigInteger(cell.getStringCellValue());
    } else if (isBigDecimal(attributeType.getInstanceClass())) {
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            return BigDecimal.valueOf(cell.getNumericCellValue()).stripTrailingZeros();
        }
        final String value = cell.getStringCellValue();
        return new BigDecimal(value).stripTrailingZeros();
    } else if (isBoolean(attributeType.getInstanceClass())) {
        return cell.getBooleanCellValue();
    } else if (isDate(attributeType.getInstanceClass())) {
        return DateUtil.getJavaDate(cell.getNumericCellValue());
    } else if (isXMLDate(attributeType.getInstanceClass())) {
        final Calendar targetCal = DateUtil.getJavaCalendarUTC(cell.getNumericCellValue(), false);
        if (targetCal == null) {
            return null;
        }
        final XMLGregorianCalendar cal = new XMLCalendar(targetCal.getTime(), XMLCalendar.DATE);
        cal.setTimezone(DatatypeConstants.FIELD_UNDEFINED);
        cal.setHour(DatatypeConstants.FIELD_UNDEFINED);
        cal.setMinute(DatatypeConstants.FIELD_UNDEFINED);
        cal.setSecond(DatatypeConstants.FIELD_UNDEFINED);
        cal.setMillisecond(DatatypeConstants.FIELD_UNDEFINED);
        return cal;
    } else {
        return EcoreUtil.createFromString(attributeType, cell.getStringCellValue());
    }
}

From source file:org.esn.esobase.data.GoogleDocsService.java

private Date getDateFromCell(CellEntry cell) {
    Date result = null;/*from   w ww  . ja  va 2  s. c  o  m*/
    if (cell != null && cell.getCell() != null && cell.getCell().getNumericValue() != null) {
        result = DateUtil.getJavaDate(cell.getCell().getNumericValue().doubleValue());
        Date now = new Date();
        if (result.after(now)) {
            result = null;
        }
    }
    return result;
}

From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java

License:Apache License

private static Object _readCell(Cell cell) {
    if (cell == null) {
        return null;
    }/* ww w  .ja v a 2  s.co m*/
    int cellType = cell.getCellType();
    Object value = null;
    switch (cellType) {
    case Cell.CELL_TYPE_BLANK:
        value = null;
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        boolean bool = cell.getBooleanCellValue();
        value = bool;
        break;
    case Cell.CELL_TYPE_ERROR:
        // cell.getErrorCellValue();
        ExcelReadException e = new ExcelReadException("Cell type error");
        e.setRowIndex(cell.getRowIndex());
        e.setColIndex(cell.getColumnIndex());
        e.setCode(ExcelReadException.CODE_OF_CELL_ERROR);
        throw e;
    case Cell.CELL_TYPE_FORMULA:
        value = cell.getCellFormula();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        Object inputValue = null;//
        double doubleVal = cell.getNumericCellValue();
        if (DateUtil.isCellDateFormatted(cell)) {
            inputValue = DateUtil.getJavaDate(doubleVal);
        } else {
            long longVal = Math.round(cell.getNumericCellValue());
            if (Double.parseDouble(longVal + ".0") == doubleVal) {
                inputValue = longVal;
            } else {
                inputValue = doubleVal;
            }
        }
        value = inputValue;
        break;
    case Cell.CELL_TYPE_STRING:
        value = cell.getStringCellValue();
        break;
    default:
        throw new RuntimeException("unsupport cell type " + cellType);
    }
    return value;
}

From source file:org.jreserve.gui.excel.dataimport.DataEntryTableReader.java

License:Open Source License

protected MonthDate createDate(double value) {
    return mdf.toMonthDate(DateUtil.getJavaDate(value));
}

From source file:org.lazulite.boot.autoconfigure.core.utils.excel.ImportExcel.java

License:Apache License

/**
 * ??/*from  w w  w  . j  ava 2 s.  co  m*/
 *
 * @param cls    
 * @param groups 
 */
public <E> List<E> getDataList(Class<E> cls, int... groups)
        throws InstantiationException, IllegalAccessException {
    List<Object[]> annotationList = Lists.newArrayList();
    // Get annotation field
    Field[] fs = cls.getDeclaredFields();
    for (Field f : fs) {
        ExcelField ef = f.getAnnotation(ExcelField.class);
        if (ef != null && (ef.type() == 0 || ef.type() == 2)) {
            if (groups != null && groups.length > 0) {
                boolean inGroup = false;
                for (int g : groups) {
                    if (inGroup) {
                        break;
                    }
                    for (int efg : ef.groups()) {
                        if (g == efg) {
                            inGroup = true;
                            annotationList.add(new Object[] { ef, f });
                            break;
                        }
                    }
                }
            } else {
                annotationList.add(new Object[] { ef, f });
            }
        }
    }
    // Get annotation method
    Method[] ms = cls.getDeclaredMethods();
    for (Method m : ms) {
        ExcelField ef = m.getAnnotation(ExcelField.class);
        if (ef != null && (ef.type() == 0 || ef.type() == 2)) {
            if (groups != null && groups.length > 0) {
                boolean inGroup = false;
                for (int g : groups) {
                    if (inGroup) {
                        break;
                    }
                    for (int efg : ef.groups()) {
                        if (g == efg) {
                            inGroup = true;
                            annotationList.add(new Object[] { ef, m });
                            break;
                        }
                    }
                }
            } else {
                annotationList.add(new Object[] { ef, m });
            }
        }
    }
    // Field sorting
    Collections.sort(annotationList, new Comparator<Object[]>() {
        public int compare(Object[] o1, Object[] o2) {
            return new Integer(((ExcelField) o1[0]).sort()).compareTo(new Integer(((ExcelField) o2[0]).sort()));
        }

        ;
    });
    //log.debug("Import column count:"+annotationList.size());
    // Get excel data
    List<E> dataList = Lists.newArrayList();
    for (int i = this.getDataRowNum(); i < this.getLastDataRowNum(); i++) {
        E e = (E) cls.newInstance();
        int column = 0;
        Row row = this.getRow(i);
        StringBuilder sb = new StringBuilder();
        for (Object[] os : annotationList) {
            Object val = this.getCellValue(row, column++);
            if (val != null) {
                ExcelField ef = (ExcelField) os[0];

                // Get param type and type cast
                Class<?> valType = Class.class;
                if (os[1] instanceof Field) {
                    valType = ((Field) os[1]).getType();
                } else if (os[1] instanceof Method) {
                    Method method = ((Method) os[1]);
                    if ("get".equals(method.getName().substring(0, 3))) {
                        valType = method.getReturnType();
                    } else if ("set".equals(method.getName().substring(0, 3))) {
                        valType = ((Method) os[1]).getParameterTypes()[0];
                    }
                }
                //log.debug("Import value type: ["+i+","+column+"] " + valType);
                try {
                    if (valType == String.class) {
                        String s = String.valueOf(val.toString());
                        if (StringUtils.endsWith(s, ".0")) {
                            val = StringUtils.substringBefore(s, ".0");
                        } else {
                            val = String.valueOf(val.toString());
                        }
                    } else if (valType == Integer.class) {
                        val = Double.valueOf(val.toString()).intValue();
                    } else if (valType == Long.class) {
                        val = Double.valueOf(val.toString()).longValue();
                    } else if (valType == Double.class) {
                        val = Double.valueOf(val.toString());
                    } else if (valType == Float.class) {
                        val = Float.valueOf(val.toString());
                    } else if (valType == Date.class) {
                        val = DateUtil.getJavaDate((Double) val);
                    } else {
                        if (ef.fieldType() != Class.class) {
                            val = ef.fieldType().getMethod("getValue", String.class).invoke(null,
                                    val.toString());
                        } else {
                            val = Class
                                    .forName(this.getClass().getName().replaceAll(
                                            this.getClass().getSimpleName(),
                                            "fieldtype." + valType.getSimpleName() + "Type"))
                                    .getMethod("getValue", String.class).invoke(null, val.toString());
                        }
                    }
                } catch (Exception ex) {
                    log.info("Get cell value [" + i + "," + column + "] error: " + ex.toString());
                    val = null;
                }
                // set entity value
                if (os[1] instanceof Field) {
                    ReflectUtils.invokeSetter(e, ((Field) os[1]).getName(), val);
                } else if (os[1] instanceof Method) {
                    String mthodName = ((Method) os[1]).getName();
                    if ("get".equals(mthodName.substring(0, 3))) {
                        mthodName = "set" + StringUtils.substringAfter(mthodName, "get");
                    }
                    ReflectUtils.invokeMethod(e, mthodName, new Class[] { valType }, new Object[] { val });
                }
            }
            sb.append(val + ", ");
        }
        dataList.add(e);
        log.debug("Read success: [" + i + "] " + sb.toString());
    }
    return dataList;
}

From source file:org.meveo.service.catalog.impl.PricePlanMatrixService.java

License:Open Source License

private Date getCellAsDate(Cell cell) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_ERROR:
    case Cell.CELL_TYPE_BLANK:
    case Cell.CELL_TYPE_FORMULA:
        return null;
    case Cell.CELL_TYPE_NUMERIC:
        return DateUtil.getJavaDate(cell.getNumericCellValue());
    default://w w w.  j  av  a  2 s .c o m
        try {
            return cell.getDateCellValue();
        } catch (Exception e) {
            try {
                return sdf.parse(cell.getStringCellValue());
            } catch (ParseException e1) {
                return null;
            }
        }
    }
}

From source file:org.simplesite.commons.utils.excel.ImportExcel.java

License:Open Source License

/**
 * ??//from  w ww.  j  a va 2  s .c o m
 * @param cls 
 * @param groups 
 */
public <E> List<E> getDataList(Class<E> cls, int... groups)
        throws InstantiationException, IllegalAccessException {
    List<Object[]> annotationList = Lists.newArrayList();
    // Get annotation field
    Field[] fs = cls.getDeclaredFields();
    for (Field f : fs) {
        ExcelField ef = f.getAnnotation(ExcelField.class);
        if (ef != null && (ef.type() == 0 || ef.type() == 2)) {
            if (groups != null && groups.length > 0) {
                boolean inGroup = false;
                for (int g : groups) {
                    if (inGroup) {
                        break;
                    }
                    for (int efg : ef.groups()) {
                        if (g == efg) {
                            inGroup = true;
                            annotationList.add(new Object[] { ef, f });
                            break;
                        }
                    }
                }
            } else {
                annotationList.add(new Object[] { ef, f });
            }
        }
    }
    // Get annotation method
    Method[] ms = cls.getDeclaredMethods();
    for (Method m : ms) {
        ExcelField ef = m.getAnnotation(ExcelField.class);
        if (ef != null && (ef.type() == 0 || ef.type() == 2)) {
            if (groups != null && groups.length > 0) {
                boolean inGroup = false;
                for (int g : groups) {
                    if (inGroup) {
                        break;
                    }
                    for (int efg : ef.groups()) {
                        if (g == efg) {
                            inGroup = true;
                            annotationList.add(new Object[] { ef, m });
                            break;
                        }
                    }
                }
            } else {
                annotationList.add(new Object[] { ef, m });
            }
        }
    }
    // Field sorting
    Collections.sort(annotationList, new Comparator<Object[]>() {
        public int compare(Object[] o1, Object[] o2) {
            return new Integer(((ExcelField) o1[0]).sort()).compareTo(new Integer(((ExcelField) o2[0]).sort()));
        };
    });
    //log.debug("Import column count:"+annotationList.size());
    // Get excel data
    List<E> dataList = Lists.newArrayList();
    for (int i = this.getDataRowNum(); i < this.getLastDataRowNum(); i++) {
        E e = (E) cls.newInstance();
        int column = 0;
        Row row = this.getRow(i);
        StringBuilder sb = new StringBuilder();
        for (Object[] os : annotationList) {
            Object val = this.getCellValue(row, column++);
            if (val != null) {
                ExcelField ef = (ExcelField) os[0];
                // If is dict type, get dict value
                if (StringUtils.isNotBlank(ef.dictType())) {
                    //val = DictUtils.getDictValue(val.toString(), ef.dictType(), "");
                    val = val == null ? "" : val.toString();
                    //log.debug("Dictionary type value: ["+i+","+colunm+"] " + val);
                }
                // Get param type and type cast
                Class<?> valType = Class.class;
                if (os[1] instanceof Field) {
                    valType = ((Field) os[1]).getType();
                } else if (os[1] instanceof Method) {
                    Method method = ((Method) os[1]);
                    if ("get".equals(method.getName().substring(0, 3))) {
                        valType = method.getReturnType();
                    } else if ("set".equals(method.getName().substring(0, 3))) {
                        valType = ((Method) os[1]).getParameterTypes()[0];
                    }
                }
                //log.debug("Import value type: ["+i+","+column+"] " + valType);
                try {
                    if (valType == String.class) {
                        val = String.valueOf(val.toString());
                    } else if (valType == Integer.class) {
                        val = Double.valueOf(val.toString()).intValue();
                    } else if (valType == Long.class) {
                        val = Double.valueOf(val.toString()).longValue();
                    } else if (valType == Double.class) {
                        val = Double.valueOf(val.toString());
                    } else if (valType == Float.class) {
                        val = Float.valueOf(val.toString());
                    } else if (valType == Date.class) {
                        val = DateUtil.getJavaDate((Double) val);
                    } else {
                        if (ef.fieldType() != Class.class) {
                            val = ef.fieldType().getMethod("getValue", String.class).invoke(null,
                                    val.toString());
                        } else {
                            val = Class
                                    .forName(this.getClass().getName().replaceAll(
                                            this.getClass().getSimpleName(),
                                            "fieldtype." + valType.getSimpleName() + "Type"))
                                    .getMethod("getValue", String.class).invoke(null, val.toString());
                        }
                    }
                } catch (Exception ex) {
                    log.info("Get cell value [" + i + "," + column + "] error: " + ex.toString());
                    val = null;
                }
                // set entity value
                if (os[1] instanceof Field) {
                    Reflections.invokeSetter(e, ((Field) os[1]).getName(), val);
                } else if (os[1] instanceof Method) {
                    String mthodName = ((Method) os[1]).getName();
                    if ("get".equals(mthodName.substring(0, 3))) {
                        mthodName = "set" + StringUtils.substringAfter(mthodName, "get");
                    }
                    Reflections.invokeMethod(e, mthodName, new Class[] { valType }, new Object[] { val });
                }
            }
            sb.append(val + ", ");
        }
        dataList.add(e);
        log.debug("Read success: [" + i + "] " + sb.toString());
    }
    return dataList;
}

From source file:utilities.SmapSheetXMLHandler.java

License:Apache License

@Override
public void endElement(String uri, String localName, String qName) throws SAXException {

    if (uri != null && !uri.equals(NS_SPREADSHEETML)) {
        return;//w  ww .  j  a  v a2s . c om
    }

    String thisStr = null;

    // v => contents of a cell
    if (isTextTag(localName)) {
        vIsOpen = false;

        // Process the value contents as required, now we have it all
        switch (nextDataType) {
        case BOOLEAN:
            char first = value.charAt(0);
            thisStr = first == '0' ? "FALSE" : "TRUE";
            break;

        case ERROR:
            thisStr = "ERROR:" + value;
            break;

        case FORMULA:
            if (formulasNotResults) {
                thisStr = formula.toString();
            } else {
                String fv = value.toString();

                if (this.formatString != null) {
                    try {
                        // Try to use the value as a formattable number
                        double d = Double.parseDouble(fv);
                        thisStr = formatter.formatRawCellContents(d, this.formatIndex, this.formatString);
                    } catch (NumberFormatException e) {
                        // Formula is a String result not a Numeric one
                        thisStr = fv;
                    }
                } else {
                    // No formatting applied, just do raw value in all cases
                    thisStr = fv;
                }
            }
            break;

        case INLINE_STRING:
            // TODO: Can these ever have formatting on them?
            XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
            thisStr = rtsi.toString();
            break;

        case SST_STRING:
            String sstIndex = value.toString();
            try {
                int idx = Integer.parseInt(sstIndex);
                RichTextString rtss = sharedStringsTable.getItemAt(idx);
                thisStr = rtss.toString();
            } catch (NumberFormatException ex) {
                logger.log(POILogger.ERROR, "Failed to parse SST index '" + sstIndex, ex);
            }
            break;

        case NUMBER:
            String n = value.toString();
            if (this.formatString != null && n.length() > 0) {
                // smap Check for date
                boolean isDate = false;
                double d = Double.parseDouble(n);
                if (DateUtil.isValidExcelDate(d)) {
                    ExcelNumberFormat nf = ExcelNumberFormat.from(style);
                    if (nf != null) {
                        isDate = DateUtil.isADateFormat(nf);
                    }
                }
                if (isDate) {
                    try {
                        Date dv = DateUtil.getJavaDate(d);
                        thisStr = sdf.format(dv);
                    } catch (Exception e) {

                    }
                } else {
                    thisStr = formatter.formatRawCellContents(Double.parseDouble(n), this.formatIndex,
                            this.formatString);
                }
            } else
                thisStr = n;
            break;

        default:
            thisStr = "(TODO: Unexpected type: " + nextDataType + ")";
            break;
        }

        // Do we have a comment for this cell?
        checkForEmptyCellComments(EmptyCellCommentsCheckType.CELL);
        XSSFComment comment = comments != null ? comments.findCellComment(new CellAddress(cellRef)) : null;

        // Output

        output.cell(cellRef, thisStr, comment, nextDataType, formatString);
    } else if ("f".equals(localName)) {
        fIsOpen = false;
    } else if ("is".equals(localName)) {
        isIsOpen = false;
    } else if ("row".equals(localName)) {
        // Handle any "missing" cells which had comments attached
        checkForEmptyCellComments(EmptyCellCommentsCheckType.END_OF_ROW);

        // Finish up the row
        output.endRow(rowNum);

        // some sheets do not have rowNum set in the XML, Excel can read them so we should try to read them as well
        nextRowNum = rowNum + 1;
    } else if ("sheetData".equals(localName)) {
        // Handle any "missing" cells which had comments attached
        checkForEmptyCellComments(EmptyCellCommentsCheckType.END_OF_SHEET_DATA);

        // indicate that this sheet is now done
        output.endSheet();
    } else if ("oddHeader".equals(localName) || "evenHeader".equals(localName)
            || "firstHeader".equals(localName)) {
        hfIsOpen = false;
        output.headerFooter(headerFooter.toString(), true, localName);
    } else if ("oddFooter".equals(localName) || "evenFooter".equals(localName)
            || "firstFooter".equals(localName)) {
        hfIsOpen = false;
        output.headerFooter(headerFooter.toString(), false, localName);
    }
}