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

Introduction

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

Prototype

public static boolean isCellDateFormatted(Cell cell) 

Source Link

Document

Check if a cell contains a date Since dates are stored internally in Excel as double values we infer it is a date if it is formatted as such.

Usage

From source file:net.pcal.sqlsheet.XlsResultSetMetaData.java

License:Apache License

public XlsResultSetMetaData(Sheet sheet, XlsResultSet resultset, int firstSheetRowOffset) throws SQLException {

    if (sheet == null)
        throw new IllegalArgumentException();
    this.resultset = resultset;
    Row row = sheet.getRow(firstSheetRowOffset - 1);
    if (row == null) {
        throw new SQLException("No header row in sheet");
    }/*  w ww .  j a  va 2s. c  o m*/
    formatter = new DataFormatter();
    columnNames = new ArrayList<String>();
    for (short c = 0; c < row.getLastCellNum(); c++) {
        Cell cell = row.getCell(c);
        String columnName = formatter.formatCellValue(cell);

        // Is it unique in the column name set
        int suffix;
        while (columnNames.contains(columnName)) {
            suffix = 1;
            columnName += "_" + suffix;
        }

        columnNames.add(columnName);
    }

    // Data Type profiling on the whole excel file
    int currentRowNumber = resultset.getRow();

    // A double map to back the relation between the column Id and the count of type
    Map<Integer, Map<Integer, Integer>> columnTypeScan = new HashMap<Integer, Map<Integer, Integer>>();
    while (resultset.next()) {
        int typeCode;
        for (int columnId = 1; columnId <= getColumnCount(); columnId++) {

            Cell cell = resultset.getCell(columnId);
            if (cell != null) {

                int excelCellType = cell.getCellType();
                switch (excelCellType) {
                case Cell.CELL_TYPE_BOOLEAN:
                    typeCode = Types.VARCHAR;
                    break;
                case Cell.CELL_TYPE_STRING:
                    typeCode = Types.VARCHAR;
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        typeCode = Types.DATE;
                    } else {
                        typeCode = Types.DOUBLE;
                    }
                    break;
                case Cell.CELL_TYPE_BLANK:
                    typeCode = Types.NULL;
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    try {
                        cell.getStringCellValue();
                        typeCode = Types.VARCHAR;
                    } catch (Exception e) {
                        cell.getNumericCellValue();
                        typeCode = Types.DOUBLE;
                    }
                    break;
                case Cell.CELL_TYPE_ERROR:
                    throw new RuntimeException("The ExcelType ( ERROR ) is not supported - Cell ("
                            + resultset.getRow() + "," + columnId + ")");

                default:
                    throw new RuntimeException("The ExcelType (" + excelCellType + ") is not supported - Cell ("
                            + resultset.getRow() + "," + columnId + ")");
                }
            } else {
                typeCode = Types.NULL;
            }
            Map<Integer, Integer> columnIdTypeMap = columnTypeScan.get(columnId);
            if (columnIdTypeMap == null) {
                columnIdTypeMap = new HashMap<Integer, Integer>();
                columnIdTypeMap.put(typeCode, 1);
                columnTypeScan.put(columnId, columnIdTypeMap);
            } else {
                Integer columnIdType = columnIdTypeMap.get(typeCode);
                if (columnIdType == null) {
                    columnIdTypeMap.put(typeCode, 1);
                } else {
                    int count = columnIdTypeMap.get(typeCode) + 1;
                    columnIdTypeMap.put(typeCode, count);
                }
            }

        }
        // Retrieve only one type
        for (Integer columnId : columnTypeScan.keySet()) {

            Integer numberOfVarchar = 0;
            Integer numberOfDouble = 0;
            Integer numberOfDate = 0;

            for (Map.Entry<Integer, Integer> columnIdTypeMap : columnTypeScan.get(columnId).entrySet()) {
                if (columnIdTypeMap.getKey() == Types.VARCHAR) {
                    numberOfVarchar = columnIdTypeMap.getValue();
                } else if (columnIdTypeMap.getKey() == Types.DOUBLE) {
                    numberOfDouble = columnIdTypeMap.getValue();
                } else if (columnIdTypeMap.getKey() == Types.DATE) {
                    numberOfDate = columnIdTypeMap.getValue();
                }
            }
            Integer finalColumnType = null;
            if (numberOfVarchar != 0) {
                finalColumnType = Types.VARCHAR;
            } else {
                if (numberOfDouble != 0 && numberOfDate == 0) {
                    finalColumnType = Types.DOUBLE;
                }
                if (numberOfDouble == 0 && numberOfDate != 0) {
                    finalColumnType = Types.DATE;
                }
            }
            if (finalColumnType == null) {
                finalColumnType = Types.VARCHAR;
            }
            columnTypeMap.put(columnId, finalColumnType);
        }

    }

    // Go back to the current row
    resultset.absolute(currentRowNumber);

}

---

From source file:net.sf.ahtutils.report.util.DataUtil.java

public static Object getCellValue(Cell cell) {
    Object value = new Object();

    // Prevent a NullPointerException
    if (cell != null) {
        if (cell.getHyperlink() != null) {
            Workbook workbook = new XSSFWorkbook();
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            Hyperlink link = cell.getHyperlink();
            String address = link.getAddress();
            if (logger.isTraceEnabled()) {
                logger.trace("Found a Hyperlink to " + cell.getHyperlink().getAddress() + " in cell "
                        + cell.getRowIndex() + "," + cell.getColumnIndex());
            }/*from www .  ja  va  2s.c  o m*/
            cell = evaluator.evaluateInCell(cell);
        }
        // Depending on the cell type, the value is read using Apache POI methods

        switch (cell.getCellType()) {

        // String are easy to handle
        case Cell.CELL_TYPE_STRING:
            logger.trace("Found string " + cell.getStringCellValue());
            value = cell.getStringCellValue();
            break;

        // Since date formatted cells are also of the numeric type, this needs to be processed
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                Date date = cell.getDateCellValue();
                DateFormat df = SimpleDateFormat.getDateInstance();
                logger.trace("Found date " + df.format(date));
                value = date;
            } else {
                logger.trace("Found general number " + cell.getNumericCellValue());
                value = cell.getNumericCellValue();
            }
            break;
        }
    } else {
        logger.trace("Found cell with NULL value");
    }
    return value;
}

---

From source file:net.sf.taverna.t2.activities.spreadsheet.ExcelSpreadsheetReader.java

License:Open Source License

private String getCellValue(Cell cell, DataFormatter dataFormatter) {
    String value = null;/* ww  w.j a  v a2 s  .co  m*/
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        value = Boolean.toString(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            //            value = cell.getDateCellValue().toString();
            value = dataFormatter.formatCellValue(cell);
        } else {
            value = Double.toString(cell.getNumericCellValue());
        }
        break;
    case Cell.CELL_TYPE_STRING:
        value = cell.getStringCellValue();
        break;
    case Cell.CELL_TYPE_FORMULA:
        switch (cell.getCachedFormulaResultType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            value = Boolean.toString(cell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                value = cell.getDateCellValue().toString();
            } else {
                value = Double.toString(cell.getNumericCellValue());
            }
            break;
        case Cell.CELL_TYPE_STRING:
            value = cell.getStringCellValue();
            break;
        default:
            break;
        }
    default:
        break;
    }
    // value = dataFormatter.formatCellValue(cell);
    // if ("".equals(value)) value = null;
    return value;
}

---

From source file:net.sourceforge.squirrel_sql.plugins.dataimport.importer.excel.ExcelFileImporter.java

License:Open Source License

public Date getDate(int column) throws IOException, UnsupportedFormatException {
    checkPointer();//from ww  w  .  j  a v a2 s  .  c  om
    Cell cell = sheet.getRow(pointer).getCell(column);
    if (DateUtil.isCellDateFormatted(cell)) {
        throw new UnsupportedFormatException();
    }
    return DateUtil.getJavaDate(cell.getNumericCellValue());
}

---

From source file:org.argrr.extractor.excel.SpreadSheetTab.java

License:Open Source License

public SpreadSheetTab(XSSFSheet sheet) {
    this.sheet = sheet;
    columnNames = new ArrayList<String>();
    lines = new ArrayList<HashMap<String, String>>();

    //iterate throw the first line in order to have columns names
    Iterator<Row> rowIterator = sheet.iterator();
    Row curRow = rowIterator.next();//  w w  w  .  ja v  a  2s.  com
    for (int cn = 0; cn < curRow.getLastCellNum(); cn++) {

        Cell cell = curRow.getCell(cn, Row.CREATE_NULL_AS_BLANK);
        columnNames.add(cell.getStringCellValue());
    }

    //Iterate through each other rows in order to have datas
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        HashMap<String, String> curLine = new HashMap<String, String>();

        //For each row, iterate through all the columns
        for (int id = 0; id < columnNames.size(); id++) {

            //add empty cells names if there are more cols in values than header def
            if (id >= this.columnNames.size())
                this.columnNames.add("");

            Cell cell = row.getCell(id, Row.CREATE_NULL_AS_BLANK);
            if (id == 0 && cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                break;
            }

            String cellVal = "";
            //Check the cell type and format accordingly
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
                    cellVal = sdf.format(cell.getDateCellValue());
                } else {
                    cellVal = Integer.valueOf(Double.valueOf(cell.getNumericCellValue()).intValue()).toString();
                }
                break;
            case Cell.CELL_TYPE_STRING:
                cellVal = cell.getStringCellValue();
                break;
            }
            curLine.put(this.getColumnName(id), cellVal);
        }
        if (curLine.size() > 0)
            lines.add(curLine);
    }
}

---

From source file:org.azkfw.datasource.excel.ExcelDatasourceBuilder.java

License:Apache License

private String toStringFromCell(final Cell aCell) { // ???
    String string = "";

    if (null != aCell) {
        switch (aCell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            string = Boolean.toString(aCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            string = aCell.getCellFormula();
            // string = cell.getStringCellValue();(
            break;
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(aCell)) {
                java.util.Date dt = aCell.getDateCellValue();
                string = (new SimpleDateFormat("yyyy/MM/dd HH:mm:ss")).format(dt);
            } else {
                string = Double.toString(aCell.getNumericCellValue());
            }//  ww  w .  j  a  v a 2s  .  c  o m
            break;
        case Cell.CELL_TYPE_STRING: {
            string = aCell.getStringCellValue();
            break;
        }
        case Cell.CELL_TYPE_ERROR: {
            break;
        }
        }
    }
    return string;
}

---

From source file:org.azkfw.datasource.excel.ExcelDatasourceFactory.java

License:Apache License

private static String toStringFromCell(final Cell aCell) { // ???
    String string = "";

    if (null != aCell) {
        switch (aCell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            string = Boolean.toString(aCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            string = aCell.getCellFormula();
            // string = cell.getStringCellValue();(
            break;
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(aCell)) {
                java.util.Date dt = aCell.getDateCellValue();
                string = (new SimpleDateFormat("yyyy/MM/dd HH:mm:ss")).format(dt);
            } else {
                string = Double.toString(aCell.getNumericCellValue());
            }//from  w  w w.  jav a 2  s.co m
            break;
        case Cell.CELL_TYPE_STRING: {
            string = aCell.getStringCellValue();
            break;
        }
        case Cell.CELL_TYPE_ERROR: {
            break;
        }
        }
    }
    return string;
}

---

From source file:org.beangle.commons.transfer.excel.ExcelItemReader.java

License:Open Source License

/**
 * <p>//from  w w w .  ja v a  2 s.  c  o m
 * getCellValue.
 * </p>
 * 
 * @see ?cell??
 * @param cell a {@link org.apache.poi.hssf.usermodel.HSSFCell} object.
 * @return a {@link java.lang.Object} object.
 */
public static Object getCellValue(HSSFCell cell) {
    if ((cell == null))
        return null;
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_BLANK:
        return null;
    case HSSFCell.CELL_TYPE_STRING:
        return Strings.trim(cell.getRichStringCellValue().getString());
    case HSSFCell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue();
        } else {
            return numberFormat.format(cell.getNumericCellValue());
        }
    case HSSFCell.CELL_TYPE_BOOLEAN:
        return (cell.getBooleanCellValue()) ? Boolean.TRUE : Boolean.FALSE;
    default:
        // cannot handle HSSFCell.CELL_TYPE_ERROR,HSSFCell.CELL_TYPE_FORMULA
        return null;
    }
}

---

From source file:org.beangle.model.transfer.excel.ExcelItemReader.java

License:Open Source License

/**
 * @see ?cell??//from  www . j  a  va2s.co  m
 * @param cell
 * @param objClass
 * @return
 */
public static Object getCellValue(HSSFCell cell) {
    if ((cell == null))
        return null;
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_BLANK:
        return null;
    case HSSFCell.CELL_TYPE_STRING:
        return StringUtils.trim(cell.getRichStringCellValue().getString());
    case HSSFCell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue();
        } else {
            return numberFormat.format(cell.getNumericCellValue());
        }
    case HSSFCell.CELL_TYPE_BOOLEAN:
        return (cell.getBooleanCellValue()) ? Boolean.TRUE : Boolean.FALSE;
    default:
        // cannot handle HSSFCell.CELL_TYPE_ERROR,HSSFCell.CELL_TYPE_FORMULA
        return null;
    }
}

---

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

License:Open Source License

public Object getValue(int row, String column) throws DataSetException {
    if (logger.isDebugEnabled())
        logger.debug("getValue(row={}, columnName={}) - start", Integer.toString(row), column);

    assertValidRowIndex(row);//from  w w  w .  j  a  va2  s.  c om

    int columnIndex = getColumnIndex(column);
    Row rowObject = _sheet.getRow(row + 1);
    if (rowObject == null) {
        return null;
    }
    Cell cell = rowObject.getCell(columnIndex);
    if (cell == null) {
        return null;
    }

    int type = cell.getCellType();
    switch (type) {
    case Cell.CELL_TYPE_NUMERIC:
        CellStyle style = cell.getCellStyle();
        if (DateUtil.isCellDateFormatted(cell)) {
            return getDateValue(cell);
        } else if (XlsDataSetWriter.DATE_FORMAT_AS_NUMBER_DBUNIT.equals(style.getDataFormatString())) {
            // The special dbunit date format
            return getDateValueFromJavaNumber(cell);
        } else {
            return getNumericValue(cell);
        }

    case Cell.CELL_TYPE_STRING:
        return cell.getRichStringCellValue().getString();

    case Cell.CELL_TYPE_FORMULA:
        throw new DataTypeException("Formula not supported at row=" + row + ", column=" + column);

    case Cell.CELL_TYPE_BLANK:
        return null;

    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE;

    case Cell.CELL_TYPE_ERROR:
        throw new DataTypeException("Error at row=" + row + ", column=" + column);

    default:
        throw new DataTypeException("Unsupported type at row=" + row + ", column=" + column);
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• »