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

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

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. DateUtil
  6. 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.vertec.daoimpl.AttendanceDAOImpl.java

public List<Object> readexcel(String path) {
    //        String data = "";
    List<Object> table = new ArrayList<Object>();
    try {/*  ww  w .  java  2  s.  co  m*/
        // Get the workbook instance for XLSX file
        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(path));

        // Get first sheet from the workbook
        XSSFSheet sheet = wb.getSheetAt(0);

        Row row;
        Cell cell;

        // Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();

        while (rowIterator.hasNext()) {
            row = rowIterator.next();

            // For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();
            List<String> rows = new ArrayList<String>();
            while (cellIterator.hasNext()) {
                cell = cellIterator.next();

                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    rows.add(cell.getRichStringCellValue().getString());
                    //                            data+=cell.getRichStringCellValue().getString();
                    //                            System.out.print(cell.getRichStringCellValue().getString());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {

                        rows.add(cell.getDateCellValue() + "");
                        //                                data+=cell.getDateCellValue();
                        //                                System.out.print(cell.getDateCellValue());
                    } else {
                        rows.add(cell.getNumericCellValue() + "");
                        //                                data+=cell.getNumericCellValue();
                        //                                System.out.print(cell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    rows.add(cell.getBooleanCellValue() + "");
                    //                            data+=cell.getBooleanCellValue();
                    //                            System.out.print(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    rows.add(cell.getCellFormula() + "");
                    //                            data+=cell.getCellFormula();
                    //                            System.out.print(cell.getCellFormula());
                    break;
                default:
                    //                            System.out.print("");
                }
                //                    data += "-";
                //                    System.out.print(" - ");
            }
            table.add(rows);
            //                data += ";;;";
            //                System.out.println(";;;");
        }
    } catch (Exception e) {
        System.err.println("Exception :" + e.getMessage());
    }
    return table;
}

From source file:com.vertec.daoimpl.AttendanceDAOImpl.java

public String readexcel2(String path) {
    try {/*from w  ww . jav  a  2 s  .  co  m*/
        // Get the workbook instance for XLSX file
        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(path));

        // Get first sheet from the workbook
        XSSFSheet sheet = wb.getSheetAt(0);

        Row row;
        Cell cell;

        // Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();

        while (rowIterator.hasNext()) {
            row = rowIterator.next();

            // For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {
                cell = cellIterator.next();

                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    System.out.print(cell.getRichStringCellValue().getString());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        System.out.print(cell.getDateCellValue());
                    } else {
                        System.out.print(cell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.print(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    System.out.print(cell.getCellFormula());
                    break;
                default:
                    System.out.print("");
                }
                System.out.print(" - ");
            }
            System.out.println(";;;");
        }
    } catch (Exception e) {
        System.err.println("Exception :" + e.getMessage());
    }
    return null;
}

From source file:com.wabacus.system.dataimport.filetype.XlsFileProcessor.java

License:Open Source License

private Object getCellValue(Cell cell) {
    if (cell == null)
        return null;
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        return cell.getRichStringCellValue().getString();
    case Cell.CELL_TYPE_BOOLEAN:
        return String.valueOf(cell.getBooleanCellValue());
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue();
        } else {/*w w w . j a  v a  2  s. c  o m*/
            return String.valueOf(cell.getNumericCellValue());
            /*double d=cell.getNumericCellValue();
            if(d-(int)d<Double.MIN_VALUE)
            { // ?int  
                return (int)d;
            }else
            { 
                return cell.getNumericCellValue();
            }*/
        }
    case Cell.CELL_TYPE_BLANK:
        return "";
    default:
        return null;
    }
}

From source file:com.xn.interfacetest.service.impl.TestCaseServiceImpl.java

License:Open Source License

/**
 * ?Cell?/*from   www .j av  a  2 s . c  o  m*/
 * @param cell
 * @return
 */
private Object getCellFormatValue(Cell cell) {
    if (null == cell) {
        return "";
    }
    DataFormatter formatter = new DataFormatter();
    switch (cell.getCellTypeEnum()) {
    case STRING:
        return cell.getRichStringCellValue().getString();
    case NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue();
        } else {
            return Math.round(cell.getNumericCellValue());
        }
    case BOOLEAN:
        return cell.getBooleanCellValue();
    case FORMULA:
        return cell.getCellFormula();
    case BLANK:
        return "";
    default:
        return "";
    }

}

From source file:com.yyl.common.utils.excel.ExcelTools.java

/**
 * ?excel//from  www.  j a v  a  2  s.c o m
 * @param cell
 * @return
 */
private static String getCellValue(Cell cell) {
    String cellValue = "";
    DecimalFormat df = new DecimalFormat("#");
    if (cell == null || cell.equals("") || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
        System.out.println(cellValue);
        return cellValue;
    }
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        cellValue = cell.getRichStringCellValue().getString().trim();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            cellValue = cell.getDateCellValue().toString();
        } else {
            cellValue = df.format(cell.getNumericCellValue()).toString();
        }
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
        break;
    default:
        cellValue = "";
    }
    return cellValue;
}

From source file:csv.impl.ExcelReader.java

License:Open Source License

/**
 * Returns the value of the specified cell.
 * If the cell contained//from  ww  w .jav a  2  s  .c  o  m
 * a formula, the formula is evaluated before returning the row.
 * @param cell cell object
 * @return value of cell
 */
public Object getValue(Cell cell) {
    if (cell == null)
        return null;

    int cellType = cell.getCellType();
    if (cellType == Cell.CELL_TYPE_FORMULA && !isEvaluateFormulas()) {
        cellType = cell.getCachedFormulaResultType();
    }

    switch (cellType) {
    case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue();
        } else {
            return cell.getNumericCellValue();
        }
    case Cell.CELL_TYPE_BLANK:
        return null;
    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue();
    case Cell.CELL_TYPE_FORMULA:
        return evaluateCellValue(cell);
    case Cell.CELL_TYPE_ERROR:
        return cell.getErrorCellValue();
    }
    return null;
}

From source file:csv.impl.ExcelReader.java

License:Open Source License

/**
 * Returns the evaluated cell content.//w ww.  j  av  a2s .c om
 * This assumes the cell contains a formula.
 * @param cell cell to evaluate
 * @return cell value
 */
public Object evaluateCellValue(Cell cell) {
    FormulaEvaluator evaluator = getFormulaEvaluator();
    CellValue value = evaluator.evaluate(cell);
    switch (value.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        return value.getStringValue();
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return DateUtil.getJavaDate(value.getNumberValue());
        } else {
            return value.getNumberValue();
        }
    case Cell.CELL_TYPE_BLANK:
        return null;
    case Cell.CELL_TYPE_BOOLEAN:
        return value.getBooleanValue();
    case Cell.CELL_TYPE_ERROR:
        return value.getErrorValue();
    default:
        System.out.println("type=" + cell.getCellType());
    }
    return cell.getCellFormula();
}

From source file:db.pj.util.excel.ApplicationImporter.java

@Override
public List<String> importExcel() {
    // get workbook
    FileInputStream inputStream = null;
    XSSFWorkbook workbook = null;/*from  w w w .j ava 2s .  c  o m*/
    List<String> result = new ArrayList<String>();
    result.add("");
    try {
        inputStream = new FileInputStream(file);
        workbook = new XSSFWorkbook(inputStream);
    } catch (Exception e) {
        result.set(0, "");
        return result;
    }

    int success = 0, fail = 0; // number of tuples
    UserDao userDao = DaoFactory.getUserDao();
    LicenseDao licenseDao = DaoFactory.getLicenseDao();

    XSSFSheet sheet = workbook.getSheetAt(0);
    XSSFRow row = null;
    XSSFCell cell = null;

    int rows = sheet.getPhysicalNumberOfRows();
    for (int i = 1; i < rows; i++) {
        row = sheet.getRow(i);
        if (row == null)
            continue;

        int cells = row.getPhysicalNumberOfCells();
        String[] values = new String[cells];

        // read data to an array of strings
        for (short j = 0; j < cells; j++) {
            cell = row.getCell(j);
            if (cell == null)
                values[j] = null;
            else {
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        double d = cell.getNumericCellValue();
                        Date date = new Date(DateUtil.getJavaDate(d).getTime());
                        values[j] = date.toString();
                    } else {
                        values[j] = (int) (cell.getNumericCellValue()) + "";
                    }
                    break;
                default:
                    values[j] = cell.getStringCellValue();
                    break;
                }
            }
        }

        License license = null;
        User user = userDao.queryUserByIC(Integer.parseInt(values[0]), values[1]);
        if (user != null)
            license = licenseDao.queryLicenseByUserID(user.getUserID());

        if (user == null) {
            user = new User();
            user.setUserID(IDGenerator.generateUserID(values[0] + values[1]));
            user.setUserPwd(StringUtil.MD5(user.getUserID()));
            user.setUserName(values[2]);
            user.setUserGender(XMLRepertory.genderStr2Num(values[3]));
            user.setUserIctype(Integer.parseInt(values[0]));
            user.setUserIcno(values[1]);
            user.setUserBdate(MyDateUtil.str2Date(values[4]));
            user.setUserPhone("00000000");
            user.setUserNation(values[5]);
            user.setUserAddr(values[6]);
            user.setUserHead("user/default.png");
            user.setUserCensor(values[9]);
            user.setUserHealth(Integer.parseInt(values[7]));
            user.setUserPdate(MyDateUtil.str2Date(values[8]));
            // check health and age
            boolean ok = (user.getUserHealth() == 0) && (user.getAge(user.getUserPdate()) >= 18)
                    && (user.getAge(user.getUserPdate()) <= 70);
            user.setUserState(ok ? 1 : 0);
            boolean re = userDao.addUser(user);
            if (re)
                success++;
            else {
                fail++;
                String ic = (user.getUserIctype() == 1 ? "" : "") + user.getUserIcno();
                result.add("" + ic + "");
            }
        } else if (license == null) {
            user.setUserCensor(values[9]);
            user.setUserHealth(Integer.parseInt(values[7]));
            user.setUserPdate(MyDateUtil.str2Date(values[8]));
            // check health and age
            boolean ok = (user.getUserHealth() == 0) && (user.getAge(user.getUserPdate()) >= 18)
                    && (user.getAge(user.getUserPdate()) <= 70);
            user.setUserState(ok ? 1 : 0);
            boolean re = userDao.updateUser(user);
            if (re)
                success++;
            else {
                fail++;
                String ic = (user.getUserIctype() == 1 ? "" : "") + user.getUserIcno();
                result.add("" + ic + "");
            }
        } else {
            // user has a license
            if (license.getLicnValid() == 0) {
                boolean nolimit = license.getLicnVinfo() == 12 && license.getLicnLimit() == null;
                boolean outlimit = license.getLicnLimit() != null
                        && (license.getLicnLimit().getTime() - MyDateUtil.str2Date(values[8]).getTime() <= 0);
                if (nolimit || outlimit) {
                    user.setUserCensor(values[9]);
                    user.setUserHealth(Integer.parseInt(values[7]));
                    user.setUserPdate(MyDateUtil.str2Date(values[8]));
                    // check health and age
                    boolean ok = (user.getUserHealth() == 0) && (user.getAge(user.getUserPdate()) >= 18)
                            && (user.getAge(user.getUserPdate()) <= 70);
                    user.setUserState(ok ? 1 : 0);
                    boolean re = userDao.updateUser(user);
                    if (re)
                        success++;
                    else {
                        fail++;
                        String ic = (user.getUserIctype() == 1 ? "" : "")
                                + user.getUserIcno();
                        result.add("" + ic + "");
                    }
                } else {
                    fail++;
                    String ic = (user.getUserIctype() == 1 ? "" : "")
                            + user.getUserIcno();
                    result.add(ic + "");
                }
            } else {
                fail++;
                String ic = (user.getUserIctype() == 1 ? "" : "") + user.getUserIcno();
                result.add(ic + "");
            }
        }

    }

    result.set(0, "" + success + "" + fail + "");
    return result;
}

From source file:db.pj.util.excel.LicenseImporter.java

License:Open Source License

@Override
public List<String> importExcel() {
    // get workbook
    FileInputStream inputStream = null;
    XSSFWorkbook workbook = null;// ww w.j a va2 s  . c om
    List<String> result = new ArrayList<String>();
    result.add("");
    try {
        inputStream = new FileInputStream(file);
        workbook = new XSSFWorkbook(inputStream);
    } catch (Exception e) {
        result.set(0, "");
        return result;
    }

    int success = 0, fail = 0; // number of tuples
    UserDao userDao = DaoFactory.getUserDao();
    LicenseDao licenseDao = DaoFactory.getLicenseDao();
    AdministrationDao administrationDao = DaoFactory.getAdministrationDao();

    XSSFSheet sheet = workbook.getSheetAt(0);
    XSSFRow row = null;
    XSSFCell cell = null;

    int rows = sheet.getPhysicalNumberOfRows();
    for (int i = 1; i < rows; i++) {
        row = sheet.getRow(i);
        if (row == null)
            continue;

        int cells = row.getPhysicalNumberOfCells();
        String[] values = new String[cells];

        // read data to an array of strings
        for (short j = 0; j < cells; j++) {
            cell = row.getCell(j);
            if (cell == null)
                values[j] = null;
            else {
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        double d = cell.getNumericCellValue();
                        Date date = new Date(DateUtil.getJavaDate(d).getTime());
                        values[j] = date.toString();
                    } else {
                        values[j] = (int) (cell.getNumericCellValue()) + "";
                    }
                    break;
                default:
                    values[j] = cell.getStringCellValue();
                    break;
                }
            }
        }

        User user = new User();
        user.setUserID(IDGenerator.generateUserID(values[5] + values[6]));
        user.setUserPwd(StringUtil.MD5(user.getUserID()));
        user.setUserName(values[0]);
        user.setUserGender(XMLRepertory.genderStr2Num(values[1]));
        user.setUserIctype(Integer.parseInt(values[5]));
        user.setUserIcno(values[6]);
        user.setUserBdate(MyDateUtil.str2Date(values[2]));
        user.setUserPhone("00000000");
        user.setUserNation(values[3]);
        user.setUserAddr(values[4]);
        user.setUserHead("user/default.png");
        user.setUserHealth(0);
        user.setUserState(5);
        user.setUserPdate(new Date(System.currentTimeMillis()));

        License license = new License();
        license.setLicnID(values[11]);
        license.setLicnType(values[8]);
        license.setLicnGdate(MyDateUtil.str2Date(values[7]));
        license.setLicnVdlen(Integer.parseInt(values[9]));
        // if point>=12, set invalid
        license.setLicnVinfo(Integer.parseInt(values[12]));
        license.setLicnValid((license.getLicnVinfo() < 12) ? 1 : 0);
        // license is not valid, reset health and state
        if (license.getLicnValid() == 0) {
            user.setUserHealth(-1);
            user.setUserState(0);
        }
        user.setLicense(license);
        license.setUser(user);
        // get administration and check whether it is valid
        Administration administration = administrationDao.queryAdministrationByID(values[10]);
        if (administration == null) {
            fail++;
            result.add("" + values[11] + "");
        } else {
            license.setAdministration(administration);
            boolean re1 = userDao.addUser(user);
            boolean re2 = licenseDao.addLicense(license);

            if (re1 || re2)
                success++;
            else {
                fail++;
                result.add("" + values[11] + "");
            }
        }
    }

    result.set(0, "" + success + "" + fail + "");
    return result;
}

From source file:db.pj.util.excel.PenaltyImporter.java

@Override
public List<String> importExcel() {
    // get workbook
    FileInputStream inputStream = null;
    XSSFWorkbook workbook = null;//from   w w w . j a va2 s. co  m
    List<String> result = new ArrayList<String>();
    result.add("");
    try {
        inputStream = new FileInputStream(file);
        workbook = new XSSFWorkbook(inputStream);
    } catch (Exception e) {
        result.set(0, "");
        return result;
    }

    int success = 0, fail = 0; // number of tuples
    LicenseDao licenseDao = DaoFactory.getLicenseDao();
    PenaltyDao penaltyDao = DaoFactory.getPenaltyDao();

    XSSFSheet sheet = workbook.getSheetAt(0);
    XSSFRow row = null;
    XSSFCell cell = null;

    int rows = sheet.getPhysicalNumberOfRows();
    int cells = sheet.getRow(0).getPhysicalNumberOfCells();
    for (int i = 1; i < rows; i++) {
        row = sheet.getRow(i);
        if (row == null)
            continue;

        String[] values = new String[cells];

        // read data to an array of strings
        for (short j = 0; j < cells; j++) {
            cell = row.getCell(j);
            if (cell == null)
                values[j] = null;
            else {
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        double d = cell.getNumericCellValue();
                        Date date = new Date(DateUtil.getJavaDate(d).getTime());
                        values[j] = date.toString();
                    } else {
                        values[j] = (int) (cell.getNumericCellValue()) + "";
                    }
                    break;
                default:
                    values[j] = cell.getStringCellValue();
                    break;
                }
            }
        }

        License license = licenseDao.queryLicenseByUserID(values[0]);

        if (license == null) {
            fail++;
            result.add("" + values[0] + "," + values[1] + "," + values[2]
                    + "");
        } else {

            Penalty penalty = new Penalty();
            penalty.setPnltID(IDGenerator.generateUUID());
            penalty.setPnltType(Integer.parseInt(values[3]));
            penalty.setPnltTime(MyDateUtil.str2Date(values[1]));
            penalty.setPnltInfo(values[2]);
            // set penalty point and limit
            if (penalty.getPnltType() == 5) {
                penalty.setPnltPoint(Integer.parseInt(values[5]));
                license.addPnltPoint(penalty.getPnltPoint());
            } else {
                penalty.setPnltPoint(0);
                license.setLicnValid(0);
                if (penalty.getPnltType() == 3)
                    license.setLicnLimit(MyDateUtil.getLimitDate(penalty.getPnltTime(), 100));
                else
                    license.setLicnLimit(
                            MyDateUtil.getLimitDate(penalty.getPnltTime(), Integer.parseInt(values[4])));
            }
            boolean re1 = licenseDao.updateLicense(license);
            penalty.setLicense(license);
            boolean re2 = penaltyDao.addPenalty(penalty);

            if (re1 && re2)
                success++;
            else {
                fail++;
                result.add("" + values[0] + "," + values[1] + "," + values[2]
                        + "");
            }
        }

    }

    result.set(0, "" + success + "" + fail + "");
    return result;
}