List of usage examples for org.apache.poi.ss.usermodel DateUtil getJavaDate
public static Date getJavaDate(double date)
From source file:db.pj.util.excel.ApplicationImporter.java
@Override public List<String> importExcel() { // get workbook FileInputStream inputStream = null; XSSFWorkbook workbook = null;//ww w .ja v a 2 s. 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;/*from ww w .j av a 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(); 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;/*www . j av a 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 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; }
From source file:de.ks.idnadrev.expimp.xls.sheet.LocalDateTimeValueParser.java
License:Apache License
@Override public LocalDateTime getValue() { Double dVal = Double.valueOf(builder.toString()); Date date = DateUtil.getJavaDate(dVal); return new Timestamp(date.getTime()).toLocalDateTime(); }
From source file:de.ks.idnadrev.expimp.xls.sheet.LocalDateValueParser.java
License:Apache License
@Override public LocalDate getValue() { Double dVal = Double.valueOf(builder.toString()); Date date = DateUtil.getJavaDate(dVal); return new Timestamp(date.getTime()).toLocalDateTime().toLocalDate(); }
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 ww.jav a 2s . c om*/ return cell.getNumericCellValue(); case Cell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue(); default: return cell.getStringCellValue(); } }
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 av a2s . co m Cell cell = sheet.getRow(pointer).getCell(column); if (DateUtil.isCellDateFormatted(cell)) { throw new UnsupportedFormatException(); } return DateUtil.getJavaDate(cell.getNumericCellValue()); }
From source file:net.unit8.axebomber.parser.CellImpl.java
License:Apache License
public String toString() { switch (cell.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: return cell.getStringCellValue(); case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellInternalDateFormatted(cell)) { Date d = DateUtil.getJavaDate(cell.getNumericCellValue()); return dateFormat.format(d); } else {/* ww w . j a v a2 s . c o m*/ BigDecimal d = new BigDecimal(cell.getNumericCellValue()); return d.toString(); } case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); } return ""; }
From source file:org.apache.metamodel.excel.XlsxSheetToRowsHandler.java
License:Apache License
private String createValue() { if (_value.length() == 0) { return null; }//w ww .j av a 2 s. c om switch (_dataType) { case BOOL: char first = _value.charAt(0); return first == '0' ? "false" : "true"; case ERROR: logger.warn("Error-cell occurred: {}", _value); return _value.toString(); case FORMULA: return _value.toString(); case INLINESTR: XSSFRichTextString rtsi = new XSSFRichTextString(_value.toString()); return rtsi.toString(); case SSTINDEX: String sstIndex = _value.toString(); int idx = Integer.parseInt(sstIndex); XSSFRichTextString rtss = new XSSFRichTextString(_sharedStringTable.getEntryAt(idx)); return rtss.toString(); case NUMBER: final String numberString = _value.toString(); if (_formatString != null) { DataFormatter formatter = getDataFormatter(); if (HSSFDateUtil.isADateFormat(_formatIndex, _formatString)) { Date date = DateUtil.getJavaDate(Double.parseDouble(numberString)); return DateUtils.createDateFormat().format(date); } return formatter.formatRawCellContents(Double.parseDouble(numberString), _formatIndex, _formatString); } else { if (numberString.endsWith(".0")) { // xlsx only stores doubles, so integers get ".0" appended // to them return numberString.substring(0, numberString.length() - 2); } return numberString; } default: logger.error("Unsupported data type: {}", _dataType); return ""; } }
From source file:org.bbreak.excella.core.util.PoiUtil.java
License:Open Source License
/** * double??Date???//from w ww . j av a 2 s. c om * * @param excelDate double? * @return Date? */ public static Date getJavaDate(double excelDate) { return DateUtil.getJavaDate(excelDate); }