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:com.hauldata.dbpa.file.book.XlsxSourceSheet.java

License:Apache License

private Object fromXLSX(Cell cell) {
    if (cell == null) {
        return null;
    }//from   w  ww .j  a  v a  2  s  . c o m

    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        return null;
    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue();
    case Cell.CELL_TYPE_NUMERIC:
        double numericValue = cell.getNumericCellValue();
        return DateUtil.isCellDateFormatted(cell) ? DateUtil.getJavaDate(numericValue) : (Double) numericValue;
    case Cell.CELL_TYPE_STRING:
    default:
        return cell.getStringCellValue();
    }
}

---

From source file:com.hurence.logisland.processor.excel.ExcelExtract.java

License:Apache License

/**
 * Handle row content and transform it into a {@link Record}
 *
 * @param row the {@link Row}/*from w  w  w . j a v a 2  s .c  o m*/
 * @return the transformed {@link Record}
 */
private Record handleRow(Row row, List<String> header) {
    Record ret = new StandardRecord().setTime(new Date());
    int index = 0;
    for (Cell cell : row) {
        if (configuration.getFieldNames() != null && index >= configuration.getFieldNames().size()) {
            //we've reached the end of mapping. Go to next row.
            break;
        }
        if (configuration.getColumnsToSkip().contains(cell.getColumnIndex())) {
            //skip this cell.
            continue;
        }
        String fieldName = header != null ? header.get(cell.getColumnIndex())
                : configuration.getFieldNames().get(index++);
        Field field;
        // Alternatively, get the value and format it yourself
        switch (cell.getCellTypeEnum()) {
        case STRING:
            field = new Field(fieldName, FieldType.STRING, cell.getStringCellValue());
            break;
        case NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                field = new Field(fieldName, FieldType.LONG, cell.getDateCellValue().getTime());
            } else {
                field = new Field(fieldName, FieldType.DOUBLE, cell.getNumericCellValue());
            }
            break;
        case BOOLEAN:
            field = new Field(fieldName, FieldType.BOOLEAN, cell.getBooleanCellValue());
            break;
        case FORMULA:
            field = new Field(fieldName, FieldType.STRING, cell.getCellFormula());
            break;
        default:
            //blank or unknown
            field = new Field(fieldName, FieldType.NULL, null);
            break;
        }
        ret.setField(field);
    }
    return ret;
}

---

From source file:com.hust.zsuper.DealWithPatent.ExcelToMySQL.java

License:Open Source License

private static Object getCellValue(Cell cell) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        return String.valueOf(cell.getBooleanCellValue());
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue();
        } else {//from  w w  w.j a v a 2 s . co  m
            return String.valueOf(cell.getNumericCellValue());
        }
    case Cell.CELL_TYPE_STRING:
    default:
        return cell.getStringCellValue().trim();
    }
}

---

From source file:com.hust.zsuper.DealWithPatent.Utils.java

License:Open Source License

public static ExcelType excelTypeToMySql(Cell cell) {
    final FormulaEvaluator evaluator = cell.getRow().getSheet().getWorkbook().getCreationHelper()
            .createFormulaEvaluator();//ww  w.ja  va  2s .c  om
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
    case Cell.CELL_TYPE_STRING:
        return ExcelType.STRING;
    case Cell.CELL_TYPE_BOOLEAN:
        return ExcelType.BOOLEAN;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return ExcelType.DATE;
        } else {
            return ExcelType.NUMERIC;
        }
    default:
        return null;
    }
}

---

From source file:com.ibm.db2j.GExcel.java

License:Open Source License

/**
 * Put the next row in the dvd row given in parameter.
 * Return SCAN_COMPLETED if there is no more row in the spreadsheet, or GOT_ROW if a row was successfully put in the dvd row.
 * /*w  ww. ja  va 2  s .c o  m*/
 * Uses the attribute currentRow to save the previous row fetched.
 * 
 * @param sheet
 * @param dvdr
 * @param numberOfLogicalColumnsInvolved
 * @param columnIndexes
 * @return SCAN_COMPLETED or GOT_ROW
 * @throws SQLException
 */
private int createNextRow(Sheet sheet, DataValueDescriptor[] dvdr) {
    boolean gotData = false;

    /*
     * Find the next row to return.
     * 
     * currentRow should currently point to the last row returned.
     * If that's null, then start from first row.
     * Else, search for the next non-empty row (until we hit the end of the prescribed range).
     */
    if (currentRow == null)
        currentRow = sheet.getRow(firstRowIndex + (firstRowIsMetaData ? 1 : 0));
    else {
        int nextRowIndex = currentRow.getRowNum() + 1;
        currentRow = null;

        if (stopScanOnFirstEmptyRow) {
            currentRow = sheet.getRow(nextRowIndex);
        } else {
            while (currentRow == null && nextRowIndex <= lastRowIndex) {
                currentRow = sheet.getRow(nextRowIndex);
                nextRowIndex++;
            }
        }
    }

    /*
     * If we've run out of spreadsheet (currentRow == null) or gone out of the prescribed range,
     * then scan complete - return that.
     */
    if (currentRow == null || currentRow.getRowNum() > lastRowIndex) {
        return SCAN_COMPLETED;
    }

    /*
     * Get the offset of the first column in the spreadsheet.
     * Note: this is used when iterating below, so that we can correctly relate 
     * the actual column in the spreadsheet to the correct 'column' in the 
     * DataValueDescriptor [] representing the row.
     */
    int columnOffset = firstColumnIndex;

    //Figure out how many columns there are
    int numberOfColumns = lastColumnIndex - firstColumnIndex + 1;

    for (int i = 0; i < numberOfColumns; i++) {
        /*
         * Note: i is used to refer to the index of the DataValueDescriptor which represents
         * the actual spreadsheet column (at i + columnOffset) in the DataValueDescriptor[] 
         * representing this row. 
         */

        Cell cell = currentRow.getCell(i + columnOffset);

        if (cell == null) {
            dvdr[i].setToNull();
        } else {
            try {
                int cellValueType = cell.getCellType();

                if (cellValueType == Cell.CELL_TYPE_FORMULA)
                    cellValueType = cell.getCachedFormulaResultType();

                switch (cellValueType) {

                case Cell.CELL_TYPE_STRING:
                    dvdr[i].setValue(cell.getStringCellValue());
                    break;

                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell))
                        dvdr[i].setValue(new java.sql.Date(cell.getDateCellValue().getTime()));
                    else {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        dvdr[i].setValue(cell.getStringCellValue());
                    }
                    break;

                case Cell.CELL_TYPE_BOOLEAN:
                    dvdr[i].setValue(cell.getBooleanCellValue());
                    break;

                default:
                    dvdr[i].setToNull();
                    break;
                }

                //If a cell has data that is not null - then flag that we actually have data to return
                if (!dvdr[i].isNull())
                    gotData = true;

            } catch (Exception e) {
                dvdr[i].setToNull();
                logger.logWarning(GDBMessages.DSWRAPPER_GEXCEL_MAP_LT_ERROR, "Excel cell [spreadsheet "
                        + sheet.getSheetName() + "; row " + cell.getRow().getRowNum() + "; column "
                        + cell.getColumnIndex() + "; value " + cell
                        + "] could not be mapped into the logical table because of the column logical type: "
                        + e);
            }
        }
    }

    if (!gotData && stopScanOnFirstEmptyRow) {
        logger.logInfo(
                "Ending GExcel table scan on first empty row (as no row limit was specified in the ending cell config constraint)");
        return SCAN_COMPLETED;
    }

    return GOT_ROW;
}

---

From source file:com.ibm.db2j.GExcel.java

License:Open Source License

/**
 * looks for the column definition and initializes the following attributes :
 * //from   ww w . jav a2  s.  com
 * - numberOfColumns
 * - columnIndexes
 * - columnNames
 *
 * If a column which contains no values is ignored.
 * 
 * If firstRowIsMetaData is true, the column names will be extract from the first row of the spreadsheet.
 * Else, they will be automatically generated : COLUMN1, COLUMN2...
 * 
 * @param sheet
 */
private void findColumns(Sheet sheet) {
    numberOfColumns = 0;

    columnIndexes = new ArrayList<Integer>();
    columnNames = new ArrayList<String>();

    Row firstRow = sheet.getRow(firstRowIndex);

    int columnLabelIndex = 1;

    if (firstRowIsMetaData) {
        //For each column
        for (int i = firstColumnIndex; i <= lastColumnIndex; ++i) {
            //Get the first cell in the column
            Cell cell = firstRow.getCell(i, Row.CREATE_NULL_AS_BLANK);

            columnIndexes.add(cell.getColumnIndex());

            int cellType = cell.getCellType();
            if (Cell.CELL_TYPE_FORMULA == cellType) {
                cellType = cell.getCachedFormulaResultType();
                //                System.out.println("cell type is now getCachedFormulaResultType() = " + cellType );
            }

            //Build the column names depending on it's type
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                //                case Cell.CELL_TYPE_FORMULA: // DO NOT USE: getCellFormula() !!!

                //                   System.out.println("cell type string" );

                // Note: Javadoc on method getStringCellValue() states:
                // "get the value of the cell as a string - for numeric cells we throw an exception. For blank cells we return an empty string. 
                // For formulaCells that are not string Formulas, we throw an exception"

                ++numberOfColumns;
                columnNames.add(cell.getStringCellValue().replaceAll("[\\ ]", "_")); // Note we should not have to do this in future... once defect is fixed
                break;

            case Cell.CELL_TYPE_NUMERIC:

                //                   System.out.println("cell type numeric " + 
                //                         ( DateUtil.isCellDateFormatted( cell ) ? "date: " + cell.getDateCellValue().toString() : "num: " + cell.getNumericCellValue() ) );

                ++numberOfColumns;
                columnNames.add(DateUtil.isCellDateFormatted(cell) ? cell.getDateCellValue().toString()
                        : "" + cell.getNumericCellValue());
                break;

            case Cell.CELL_TYPE_BOOLEAN:

                //                   System.out.println("cell type boolean" );

                ++numberOfColumns;
                columnNames.add("" + cell.getBooleanCellValue());
                break;

            default:

                //                   System.out.println("cell type default" );

                ++numberOfColumns;
                columnNames.add(DEFAULT_COLUMN_LABEL + "" + columnLabelIndex);
                break;
            }

            columnLabelIndex++;
        }
    } else {
        //For each column
        for (int i = firstColumnIndex; i <= lastColumnIndex; ++i) {
            //Get the first cell in the column
            Cell cell = firstRow.getCell(i, Row.CREATE_NULL_AS_BLANK);

            columnIndexes.add(cell.getColumnIndex());
            columnNames.add(DEFAULT_COLUMN_LABEL + "" + columnLabelIndex++);
        }
    }
}

---

From source file:com.ibm.db2j.GExcel.java

License:Open Source License

private boolean checkSheetConsistencySubMethod(Cell cell, int index, boolean isConsistent) {
    if (index < columnTypes.length) {
        boolean isADate = (cell.getCellType() == Cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(cell));

        if (columnTypes[index] == CELL_WITH_NO_TYPE) {
            if (isADate) {
                // Specific case where the cell is a date
                columnTypes[index] = DATE_TYPE;
            } else {
                columnTypes[index] = cell.getCellType();
            }//from ww w .j a va 2  s.  c o  m
        } else if (isADate && columnTypes[index] != DATE_TYPE) {
            // Specific case where the cell is a date
            return false;
        } else if (!isADate && (columnTypes[index] != cell.getCellType())) {
            return false;
        }

    }
    return isConsistent;
}

---

From source file:com.infovity.iep.loader.util.SupplierLoaderUtil.java

public static ArrayList<String[]> getDataFromFile(File inputFile, String sheetName) {
    ArrayList<String[]> data = new ArrayList<String[]>();
    File selectedFile = null;/*from   ww w.  j a  v  a  2s  . c  o m*/
    FileInputStream fis = null;
    ;
    XSSFWorkbook workbook = null;
    //inputFile = new File("C:\\Users\\INFOVITY-USER-029\\Desktop\\DataLoader\\Consolidated Supplier Data Capture Template v4.0.xlsx");
    boolean sheetFound = false;
    XSSFSheet sheet = null;
    try {

        int sheetIndex = -1;
        fis = new FileInputStream(inputFile);
        workbook = new XSSFWorkbook(fis);

        int noOfSheets = workbook.getNumberOfSheets();
        for (int i = 0; i < noOfSheets; i++) {
            sheet = workbook.getSheetAt(i);
            if (sheet.getSheetName().equals(sheetName)) {
                sheetFound = true;
                sheetIndex = i;
                selectedFile = inputFile;
                break;
            }
        }
        XSSFWorkbook myWorkBook;

        try {
            myWorkBook = new XSSFWorkbook(selectedFile);
            // Return first sheet from the XLSX workbook
            // XSSFSheet mySheet = myWorkBook.getSheetAt(0);
            // Get iterator to all the rows in current sheet
            Iterator<Row> rowIterator = sheet.iterator();
            String query;
            String[] values = null;
            // Traversing over each row of XLSX file
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                if (rowHasData(row) && (row.getRowNum() >= 9)) {
                    int endColumn = row.getLastCellNum();
                    int startColumn = row.getFirstCellNum();
                    // For each row, iterate through each columns
                    values = new String[endColumn + 2];
                    for (int i = startColumn; i < endColumn; i++) {
                        String cellValue;
                        Cell cell = row.getCell(i);
                        values[0] = Integer.toString(row.getRowNum() + 1);
                        if (cell != null) {
                            if (cell.getCellType() == cell.CELL_TYPE_NUMERIC
                                    && DateUtil.isCellDateFormatted(cell)) {
                                DateFormat df = new SimpleDateFormat("yyyy/MM/dd");
                                Date cellDateValue = cell.getDateCellValue();
                                cellValue = df.format(cellDateValue);
                            } else {
                                cell.setCellType(cell.CELL_TYPE_STRING);
                                cellValue = cell.getStringCellValue().replaceAll("'", "");
                            }
                            if (!"".equals(cellValue) && cellValue != null) {
                                values[i + 1] = cellValue;
                            } else if (cellValue.isEmpty() || "".equals(cellValue)) {
                                values[i + 1] = "";
                            }
                        } else {
                            values[i + 1] = "";
                        }
                    }
                    data.add(values);
                }

            }
        } catch (InvalidFormatException ex) {
            Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex);
        }
    } catch (IOException ex) {
        Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex);
    } finally {
        try {
            fis.close();
            workbook.close();
        } catch (IOException ex) {
            Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex);
        }
    }

    return data;
}

---

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 w ww  .ja v  a2 s .  co 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.jmc.jfxxlsdiff.util.POIXlsUtil.java

public static Object getCellValue(Cell cell) {
    Object cv = null;/*w  w w.ja  v a 2 s.co  m*/

    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK: {
        break;
    }
    case Cell.CELL_TYPE_BOOLEAN: {
        cv = cell.getBooleanCellValue();
        break;
    }
    case Cell.CELL_TYPE_ERROR: {
        cv = cell.getErrorCellValue();
        break;
    }
    case Cell.CELL_TYPE_FORMULA: {
        cv = getFormulaValue(cell);
        break;
    }
    case Cell.CELL_TYPE_NUMERIC: {
        if (DateUtil.isCellDateFormatted(cell)) {
            // format in form of M/D/YY
            //Calendar cal = Calendar.getInstance();
            //cal.setTime( DateUtil.getJavaDate( d ) );
            //cv = cal.getTime();
            cv = cell.getDateCellValue();
        } else {
            cv = cell.getNumericCellValue();
        }
        break;
    }
    case Cell.CELL_TYPE_STRING: {
        cv = cell.getStringCellValue();
        break;
    }
    default: {
        logger.log(Level.WARNING, "Unexpected cell type = {0}", cell.getCellType());
        break;
    }
    }

    return cv;
}

---

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