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:it.dontesta.liferay.example.portal.client.ImportUsersToLiferay.java

License:Open Source License

/**
 * Read users to import from excel file.
 * //from ww  w . j  a v a2  s .  co m
 * @return Returns a list of users who are ready for import.
 */
private static List<UserToImport> getUsersToImportFromExcel() {
    String fileName = (System.getProperty("fileToImport") != null) ? System.getProperty("fileToImport")
            : FILE_TO_IMPORT_USERS;
    InputStream inp = null;
    List<UserToImport> usersList = new ArrayList<UserToImport>();
    UserToImport user = null;
    boolean readyForImport = true;

    try {
        inp = new FileInputStream(fileName);
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);

        for (Row row : sheet) {
            LOGGER.debug("Processing row index {}...", row.getRowNum());
            if (row.getRowNum() == 0) {
                LOGGER.debug("First row is the header. Skip this row");
                continue;
            } else {
                user = new UserToImport();
            }
            for (Cell cell : row) {
                LOGGER.debug("Processing cell index {}...", cell.getColumnIndex());
                switch (cell.getColumnIndex()) {
                case 0:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setTitle(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 1:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setScreenName(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    } else {
                        LOGGER.warn("The username attribute is not null. Row skipped");
                        LOGGER.warn("Processing cell index {}...[FAILED]", cell.getColumnIndex());
                        break;

                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 2:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setEmail(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    } else {
                        LOGGER.warn("The email attribute is not null. Row skipped");
                        LOGGER.warn("Processing cell index {}...[FAILED]", cell.getColumnIndex());
                        break;
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 3:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setFirstName(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 4:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setMiddleName(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 5:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setLastName(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 6:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setGender(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 7:
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        if (DateUtil.isCellDateFormatted(cell)) {
                            Calendar calBirthDate = Calendar.getInstance();
                            calBirthDate.setTime(cell.getDateCellValue());

                            user.setBirthDate(calBirthDate);
                            LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                    cell.getDateCellValue());
                        } else {
                            LOGGER.warn("Value cell index {} {}", cell.getColumnIndex(),
                                    cell.getNumericCellValue());
                            LOGGER.warn("Value cell index {} not contain a date type format",
                                    cell.getColumnIndex());
                        }
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 8:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setJobTitle(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    break;
                case 9:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setSiteName(cell.getStringCellValue().split(","));
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                Arrays.toString(cell.getStringCellValue().split(",")));
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 10:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setRoleName(cell.getStringCellValue().split(","));
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                Arrays.toString(cell.getStringCellValue().split(",")));
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 11:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setLanguageId(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 12:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setTimeZoneId(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 13:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setAccountId(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());

                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                default:
                    break;
                }
            }
            if (user.getScreenName() == null) {
                LOGGER.warn("The username attribute can not be null for rowId {}", row.getRowNum());
                readyForImport = false;
            }
            if (user.getEmail() == null) {
                LOGGER.warn("The email attribute can not be null for rowId {}", row.getRowNum());
                readyForImport = false;
            }
            if (readyForImport) {
                LOGGER.info("Add user object {} to user list...", user.toString());
                usersList.add(user);
            }
        }
    } catch (FileNotFoundException e) {
        LOGGER.error(e.getMessage());
    } catch (IllegalStateException e) {
        LOGGER.error(e.getMessage());
    } catch (InvalidFormatException e) {
        LOGGER.error(e.getMessage());
    } catch (IOException e) {
        LOGGER.error(e.getMessage());
    }

    return usersList;
}

---

From source file:javacommon.excel.ExcelReader.java

/**
 * ???/* www .  j  a v  a  2  s.com*/
 *
 * @param c ?
 * @return
 */
private String getCellStringValue(Cell c) {
    if (c == null) {
        return "";
    }
    String value = null;
    NumberFormat nf = NumberFormat.getInstance();
    nf.setGroupingUsed(false);
    nf.setMaximumFractionDigits(12);
    switch (c.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        value = String.valueOf(c.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(c)) {
            return DateFormatUtils.ISO_DATE_FORMAT.format(c.getDateCellValue());
        } else if ("@".equals(c.getCellStyle().getDataFormatString())) {
            value = nf.format(c.getNumericCellValue());
        } else if ("General".equals(c.getCellStyle().getDataFormatString())) {
            value = nf.format(c.getNumericCellValue());
        } else if (ArrayUtils.contains(ExcelConstants.DATE_PATTERNS, c.getCellStyle().getDataFormatString())) {
            value = DateFormatUtils.format(HSSFDateUtil.getJavaDate(c.getNumericCellValue()),
                    c.getCellStyle().getDataFormatString());
        } else {
            value = nf.format(c.getNumericCellValue());
        }
        break;
    case Cell.CELL_TYPE_STRING:
        value = c.getStringCellValue();
        break;
    case Cell.CELL_TYPE_FORMULA:
        value = c.getCellFormula();
        break;
    }
    return value == null ? "" : value.trim();
}

---

From source file:javacommon.excel.ExcelReader.java

/**
 * ???/*w  ww .j av a  2s . co m*/
 *
 * @param c ?
 * @return
 */
private String getCellStringFormatValue(Cell c) {
    if (c == null) {
        return "";
    }
    String value = null;
    NumberFormat nf = NumberFormat.getInstance();
    nf.setGroupingUsed(false);
    nf.setMaximumFractionDigits(12);
    switch (c.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        value = String.valueOf(c.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(c)) {
            return DateFormatUtils.ISO_DATE_FORMAT.format(c.getDateCellValue());
        } else if ("@".equals(c.getCellStyle().getDataFormatString())) {
            value = nf.format(c.getNumericCellValue());
        } else if ("General".equals(c.getCellStyle().getDataFormatString())) {
            value = nf.format(c.getNumericCellValue());
        } else if (ArrayUtils.contains(ExcelConstants.DATE_PATTERNS, c.getCellStyle().getDataFormatString())) {
            value = DateFormatUtils.format(HSSFDateUtil.getJavaDate(c.getNumericCellValue()),
                    c.getCellStyle().getDataFormatString());
        } else {
            value = nf.format(c.getNumericCellValue());
        }
        break;
    case Cell.CELL_TYPE_STRING:
        value = c.getStringCellValue();
        break;
    case Cell.CELL_TYPE_FORMULA:
        return c.getCellFormula();
    }
    return value == null ? "" : value.trim();
}

---

From source file:javacommon.excel.ExcelReader.java

/**
 * ???/*from  w  w  w  .  jav  a  2  s  . c o  m*/
 *
 * @param c ?
 * @return
 */
private Object getCellValue(Cell c) {
    if (c == null) {
        return null;
    }
    switch (c.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        return null;
    case Cell.CELL_TYPE_BOOLEAN:
        return c.getBooleanCellValue();
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(c)) {
            return c.getDateCellValue();
        }
        return c.getNumericCellValue();
    case Cell.CELL_TYPE_STRING:
        return c.getStringCellValue();
    case Cell.CELL_TYPE_FORMULA:
        return c.getCellFormula();
    }
    return null;
}

---

From source file:joinery.impl.Serialization.java

License:Open Source License

private static final Object readCell(final Cell cell) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return DateUtil.getJavaDate(cell.getNumericCellValue());
        }//from w w w  .j av  a 2s.c  om
        return cell.getNumericCellValue();
    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue();
    default:
        return cell.getStringCellValue();
    }
}

---

From source file:magicware.scm.redmine.tools.util.ExcelUtils.java

License:Apache License

public static String getCellContent(Cell cell, FormulaEvaluator evaluator) {

    String result = null;/*from  w  w w. ja v a 2 s  . c o m*/

    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        result = cell.getRichStringCellValue().getString();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            result = Constants.DATE_FORMAT.format(cell.getDateCellValue());
        } else {
            result = String.valueOf(Double.valueOf(cell.getNumericCellValue()).intValue());
        }
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        result = String.valueOf(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_FORMULA:
        switch (evaluator.evaluateFormulaCell(cell)) {
        case Cell.CELL_TYPE_BOOLEAN:
            result = String.valueOf(cell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                result = Constants.DATE_FORMAT.format(cell.getDateCellValue());
            } else {
                result = String.valueOf(Double.valueOf(cell.getNumericCellValue()).intValue());
            }
            break;
        case Cell.CELL_TYPE_STRING:
            result = String.valueOf(cell.getStringCellValue());
            break;
        case Cell.CELL_TYPE_BLANK:
            break;
        case Cell.CELL_TYPE_ERROR:
            result = String.valueOf(cell.getErrorCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            break;
        }
        break;
    default:
        break;
    }
    return result;
}

---

From source file:main.KeywordList.java

private String getCellDate(Cell cell) {
    switch (cell.getCellType()) {
    case CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return ForcastUi.dateToString(cell.getDateCellValue());
        } else {//w w  w . j ava2  s. c  om
            return "01/01/2000";
        }
    case CELL_TYPE_STRING:
        return cell.getStringCellValue();
    case CELL_TYPE_FORMULA:
        return cell.getCellFormula();
    case CELL_TYPE_BLANK:
        return "";
    case CELL_TYPE_BOOLEAN:
        return Boolean.toString(cell.getBooleanCellValue());
    case CELL_TYPE_ERROR:
        return Byte.toString(cell.getErrorCellValue());
    default:
        return "01/01/2000";
    }
}

---

From source file:math.page.KnapsackTest.java

License:Apache License

public static void test3() throws InvalidFormatException, IOException {
    String path = "d:" + File.separator + "price.xlsx";
    File file = new File(path);
    Workbook workbook = WorkbookFactory.create(file);
    Sheet sheet = workbook.getSheetAt(0);
    List<Knapsack> bags = new ArrayList<Knapsack>();
    try {// w ww.ja  v a  2  s.com
        for (int row = 1; row <= sheet.getLastRowNum(); row++) {
            Row row2 = sheet.getRow(row);
            Cell cell0 = row2.getCell(0);
            Cell cell1 = row2.getCell(1);
            // 
            // System.out.print(cell.toString() + "  ");
            // System.out.println(cell0.getCellComment().toString());

            // Integer integer = Double.valueOf(cell0.getNumericCellValue())
            // .intValue();
            Integer integer = null;
            switch (cell0.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell0)) {

                } else {
                    cell0.setCellType(Cell.CELL_TYPE_STRING);
                    String temp = cell0.getStringCellValue();
                    // ??????????Double
                    if (temp.indexOf(".") > -1) {
                        integer = Double.valueOf(temp).intValue();
                    } else {
                        integer = Integer.valueOf(temp).intValue();
                    }
                }
                break;
            case Cell.CELL_TYPE_STRING:
                integer = Integer.valueOf(cell0.getStringCellValue()).intValue();
                break;
            default:
                break;
            }
            Knapsack knapsack = new Knapsack(integer, integer);
            knapsack.setNo(Double.valueOf(cell1.getNumericCellValue()).intValue());

            bags.add(knapsack);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    List<Total> list = test4();

    write(list, bags);

}

---

From source file:math.page.KnapsackTest.java

License:Apache License

public static List<Total> test4() throws InvalidFormatException, IOException {
    String path = "d:" + File.separator + "total.xlsx";
    File file = new File(path);
    // Workbook workbook = Workbook.getWorkbook(file);
    Workbook workbook = WorkbookFactory.create(file);
    // Sheet sheet = workbook.getSheet(0);
    Sheet sheet = workbook.getSheetAt(0);
    List<Total> bags = new ArrayList<Total>();
    try {//from   ww w  .  j  a v a  2  s. c o  m

        for (int row = 1; row <= sheet.getLastRowNum(); row++) {
            // Cell[] cells = sheet.getRow(row);
            // System.out.println(cells[0].getContents());
            Row row2 = sheet.getRow(row);
            Cell cell0 = row2.getCell(0);
            Cell cell1 = row2.getCell(1);
            Integer integer = null;
            switch (cell0.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell0)) {

                } else {
                    cell0.setCellType(Cell.CELL_TYPE_STRING);
                    String temp = cell0.getStringCellValue();
                    // ??????????Double
                    if (temp.indexOf(".") > -1) {
                        integer = Double.valueOf(temp).intValue();
                    } else {
                        integer = Integer.valueOf(temp).intValue();
                    }
                }
                break;
            case Cell.CELL_TYPE_STRING:
                integer = Integer.valueOf(cell0.getStringCellValue()).intValue();
                break;
            default:
                break;
            }

            Total total = new Total();
            total.setNo(Double.valueOf(cell1.getNumericCellValue()).intValue());
            total.setTotal(integer);
            bags.add(total);
        }
        Arrays.sort(bags.toArray());
    } catch (Exception e) {
        e.printStackTrace();
    }

    return bags;
}

---

From source file:midas.sheeco.exceptions.SpreadsheetViolation.java

License:Apache License

private static Object getCellValue(final Cell cell) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue();
        }/*from w  w  w.ja  v  a 2  s  . com*/
        return cell.getNumericCellValue();
    case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();
    case Cell.CELL_TYPE_BLANK:
        return null;
    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue();
    case Cell.CELL_TYPE_ERROR:
        return cell.getErrorCellValue();
    case Cell.CELL_TYPE_FORMULA:
        return cell.getCellFormula();
    }
    throw new UnsupportedOperationException("CellType " + cell.getCellType() + " is invalid");
}

---

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