List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getDateCellValue
public Date getDateCellValue()
From source file:org.databene.platform.xls.XLSEntityExporterTest.java
License:Open Source License
private static void checkCells(HSSFRow row, Object... values) { if (ArrayUtil.isEmpty(values)) assertNull(row);//w w w . j a v a 2s.c om for (int i = 0; i < values.length; i++) { HSSFCell cell = row.getCell(i); Object expectedContent = values[i]; if (expectedContent == null) assertNull(cell); else if (expectedContent instanceof String) { assertEquals(HSSFCell.CELL_TYPE_STRING, cell.getCellType()); assertEquals(expectedContent, cell.getStringCellValue()); } else if (expectedContent instanceof Number) { assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cell.getCellType()); assertEquals(((Number) expectedContent).doubleValue(), cell.getNumericCellValue()); } else if (expectedContent instanceof Boolean) { assertEquals(HSSFCell.CELL_TYPE_BOOLEAN, cell.getCellType()); assertEquals(expectedContent, cell.getBooleanCellValue()); } else if (expectedContent instanceof Date) { assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cell.getCellType()); assertEquals(((Date) expectedContent).getTime() / 1000, cell.getDateCellValue().getTime() / 1000); // cut off milliseconds } else throw new RuntimeException("Type not supported: " + expectedContent.getClass()); } }
From source file:org.gageot.excel.core.StringCellMapper.java
License:Apache License
private String numericToString(HSSFCell cell) { double numericValue = cell.getNumericCellValue(); if (Double.isNaN(numericValue)) { return ""; }/*from w w w.j a v a2 s. co m*/ if (isDateFormat(cell)) { if (null == dateFormat) { dateFormat = new SimpleDateFormat("dd/MM/yyyy"); } return dateFormat.format(cell.getDateCellValue()); } // For text cells, Excel still tries to converts the content into // numerical value. For integer content, we want to convert // into a String value without fraction. // if (isTextFormat(cell) && (((long) numericValue) == numericValue)) { return Long.toString((long) numericValue); } return Double.toString(numericValue); }
From source file:org.hil.children.service.impl.ChildrenManagerImpl.java
License:Open Source License
public void importExcel() { String name = SecurityContextHolder.getContext().getAuthentication().getName(); log.debug("Start import..."); String fileToBeRead = "/home/hieu/DKTC-2011_Hieu.xls"; try {//from w ww . j av a 2 s. co m HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead)); HSSFSheet sheet = workbook.getSheetAt(1); Vaccination vvgb = vaccinationDao.get((long) 1); Vaccination vbcg = vaccinationDao.get((long) 2); Vaccination vdpt1 = vaccinationDao.get((long) 3); Vaccination vopv1 = vaccinationDao.get((long) 4); Vaccination vdpt2 = vaccinationDao.get((long) 5); Vaccination vopv2 = vaccinationDao.get((long) 6); Vaccination vdpt3 = vaccinationDao.get((long) 7); Vaccination vopv3 = vaccinationDao.get((long) 8); Vaccination vmeasles = vaccinationDao.get((long) 9); SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy"); for (int r = 1; r < sheet.getPhysicalNumberOfRows(); r++) { log.debug("--------------------------------------" + r); HSSFRow row = sheet.getRow(r); Children child = new Children(); Date bcg = null; Date opv1 = null; Date opv2 = null; Date opv3 = null; Date dpt1 = null; Date dpt2 = null; Date dpt3 = null; Date measles = null; Date helpb1 = null; Boolean helpb1ontime = null; Date finishedDate = null; String villageName = ""; long communeId = 0; for (int c = 0; c < 20; c++) { HSSFCell cell = row.getCell(c); // Type the content Date tmpDate = null; int tmpInt = 0; String tmpStr = ""; Boolean tmpboolean = null; if (cell != null) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { if (HSSFDateUtil.isCellDateFormatted(cell)) { tmpDate = cell.getDateCellValue(); } else tmpInt = (int) cell.getNumericCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { tmpStr = cell.getStringCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { tmpInt = (int) cell.getNumericCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { tmpboolean = cell.getBooleanCellValue(); } } if (c == 0) { communeId = (long) tmpInt; } else if (c == 1) { child.setFullName(tmpStr); } else if (c == 2) { if (tmpInt == 2) child.setGender(true); else child.setGender(false); } else if (c == 3) { // if (cell==null) // child.setDateOfBirth(null); // else // child.setDateOfBirth(tmpDate); if (tmpStr == null || tmpStr.equalsIgnoreCase("")) child.setDateOfBirth(null); else { child.setDateOfBirth(format.parse(tmpStr)); } } else if (c == 4) { child.setFatherName(tmpStr); } else if (c == 5) { child.setMotherName(tmpStr); } else if (c == 6) { //village villageName = tmpStr; } else if (c == 7) { // if (cell != null) // bcg = tmpDate; if (tmpStr == null || tmpStr.equalsIgnoreCase("")) bcg = null; else { bcg = format.parse(tmpStr); } } else if (c == 8) { // if (cell != null) // opv1 = tmpDate; if (tmpStr == null || tmpStr.equalsIgnoreCase("")) opv1 = null; else { opv1 = format.parse(tmpStr); } } else if (c == 9) { // if (cell != null) // opv2 = tmpDate; if (tmpStr == null || tmpStr.equalsIgnoreCase("")) opv2 = null; else { opv2 = format.parse(tmpStr); } } else if (c == 10) { // if (cell != null) // opv3 = tmpDate; if (tmpStr == null || tmpStr.equalsIgnoreCase("")) opv3 = null; else { opv3 = format.parse(tmpStr); } } else if (c == 11) { // if (cell != null) // dpt1 = tmpDate; if (tmpStr == null || tmpStr.equalsIgnoreCase("")) dpt1 = null; else { dpt1 = format.parse(tmpStr); } } else if (c == 12) { // if (cell != null) // dpt2 = tmpDate; if (tmpStr == null || tmpStr.equalsIgnoreCase("")) dpt2 = null; else { dpt2 = format.parse(tmpStr); } } else if (c == 13) { // if (cell != null) // dpt3 = tmpDate; if (tmpStr == null || tmpStr.equalsIgnoreCase("")) dpt3 = null; else { dpt3 = format.parse(tmpStr); } } else if (c == 14) { // if (cell != null) // measles = tmpDate; if (tmpStr == null || tmpStr.equalsIgnoreCase("")) measles = null; else { measles = format.parse(tmpStr); } } else if (c == 15) { // if (cell != null) { // helpb1 = tmpDate; // helpb1ontime = true; // } if (tmpStr == null || tmpStr.equalsIgnoreCase("")) helpb1 = null; else { helpb1 = format.parse(tmpStr); helpb1ontime = true; } } else if (c == 16 && helpb1 == null) { // if (cell != null) { // helpb1 = tmpDate; // helpb1ontime = false; // } if (tmpStr == null || tmpStr.equalsIgnoreCase("")) helpb1 = null; else { helpb1 = format.parse(tmpStr); helpb1ontime = false; } } else if (c == 19) { // if (cell != null) // finishedDate = tmpDate; if (tmpStr == null || tmpStr.equalsIgnoreCase("")) finishedDate = null; else { finishedDate = format.parse(tmpStr); } } } if (child.getDateOfBirth() != null) { log.debug(child.getFullName() + " - " + child.getDateOfBirth() + " - " + child.isGender()); Commune commune = communeDao.get(communeId); if (villageName.equalsIgnoreCase("")) child.setVillage(villageDao.findByCommune(commune).get(0)); else { List<Village> vl = villageDaoExt.findByCommuneIdAndVillageName(communeId, villageName); if (vl != null && vl.size() > 0) child.setVillage(vl.get(0)); else { child.setVillage(villageDao.findByCommune(commune).get(0)); child.setAddress(villageName); } } child.setCurrentCaretaker((short) 0); child.setCreationDate(new Date()); child.setAuthor(name); child.setBarcodeDate(null); child.setLocked(false); if (finishedDate != null) { child.setFinishedDate(finishedDate); } else if (bcg != null && dpt1 != null && dpt2 != null && dpt3 != null && opv1 != null && opv2 != null && opv3 != null && measles != null) { Date maxDate = dpt3.getTime() > opv3.getTime() ? dpt3 : opv3; maxDate = maxDate.getTime() > measles.getTime() ? maxDate : measles; child.setFinishedDate(maxDate); } else child.setFinishedDate(null); int year = child.getDateOfBirth().getYear() + 1900; if (child.getFullName() != null && !child.getFullName().equalsIgnoreCase("")) child.setFirstName(child.getFullName().substring(child.getFullName().lastIndexOf(" ") + 1)); if (child.getMotherName() != null && !child.getMotherName().equalsIgnoreCase("")) child.setMotherFirstName( child.getMotherName().substring(child.getMotherName().lastIndexOf(" ") + 1)); child.setChildCode(commune.getDistrict().getProvince().getProvinceId() + commune.getDistrict().getDistrictId() + commune.getCommuneId() + "-" + year); child = childrenDao.save(child); String code = childrenDaoExt.generateChildCode(child); child.setChildCode(code); log.debug("child code: " + code); child = childrenDao.save(child); ChildrenVaccinationHistory newVH = new ChildrenVaccinationHistory(); if (helpb1 != null) { log.debug("VGB " + helpb1); newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setReasonIfMissed(""); if (helpb1ontime != null) newVH.setOverdue(helpb1ontime); newVH.setVaccinated((short) 1); newVH.setVaccination(vvgb); newVH.setDateOfImmunization(helpb1); newVH.setVaccinatedLocation(child.getVillage().getCommune()); childrenVaccinationHistoryDao.save(newVH); } else { newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setOverdue(false); newVH.setReasonIfMissed(""); newVH.setVaccinated((short) 0); newVH.setVaccination(vvgb); childrenVaccinationHistoryDao.save(newVH); } if (bcg != null) { log.debug("BCG " + bcg); newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setReasonIfMissed(""); //newVH.setOverdue(false); newVH.setVaccinated((short) 1); newVH.setVaccination(vbcg); newVH.setDateOfImmunization(bcg); newVH.setVaccinatedLocation(child.getVillage().getCommune()); childrenVaccinationHistoryDao.save(newVH); } else { newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setOverdue(false); newVH.setReasonIfMissed(""); newVH.setVaccinated((short) 0); newVH.setVaccination(vbcg); childrenVaccinationHistoryDao.save(newVH); } if (dpt1 != null) { log.debug("DPT1 " + dpt1); newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setReasonIfMissed(""); //newVH.setOverdue(false); newVH.setVaccinated((short) 1); newVH.setVaccination(vdpt1); newVH.setDateOfImmunization(dpt1); newVH.setVaccinatedLocation(child.getVillage().getCommune()); childrenVaccinationHistoryDao.save(newVH); } else { newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setOverdue(false); newVH.setReasonIfMissed(""); newVH.setVaccinated((short) 0); newVH.setVaccination(vdpt1); childrenVaccinationHistoryDao.save(newVH); } if (opv1 != null) { log.debug("OPV1 " + opv1); newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setReasonIfMissed(""); //newVH.setOverdue(false); newVH.setVaccinated((short) 1); newVH.setVaccination(vopv1); newVH.setDateOfImmunization(opv1); newVH.setVaccinatedLocation(child.getVillage().getCommune()); childrenVaccinationHistoryDao.save(newVH); } else { newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setOverdue(false); newVH.setReasonIfMissed(""); newVH.setVaccinated((short) 0); newVH.setVaccination(vopv1); childrenVaccinationHistoryDao.save(newVH); } if (dpt2 != null) { log.debug("DPT2 " + dpt2); newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setReasonIfMissed(""); //newVH.setOverdue(false); newVH.setVaccinated((short) 1); newVH.setVaccination(vdpt2); newVH.setDateOfImmunization(dpt2); newVH.setVaccinatedLocation(child.getVillage().getCommune()); childrenVaccinationHistoryDao.save(newVH); } else { newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setOverdue(false); newVH.setReasonIfMissed(""); newVH.setVaccinated((short) 0); newVH.setVaccination(vdpt2); childrenVaccinationHistoryDao.save(newVH); } if (opv2 != null) { log.debug("OPV2 " + opv2); newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setReasonIfMissed(""); //newVH.setOverdue(false); newVH.setVaccinated((short) 1); newVH.setVaccination(vopv2); newVH.setDateOfImmunization(opv2); newVH.setVaccinatedLocation(child.getVillage().getCommune()); childrenVaccinationHistoryDao.save(newVH); } else { newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setOverdue(false); newVH.setReasonIfMissed(""); newVH.setVaccinated((short) 0); newVH.setVaccination(vopv2); childrenVaccinationHistoryDao.save(newVH); } if (dpt3 != null) { log.debug("DPT3 " + dpt3); newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setReasonIfMissed(""); //newVH.setOverdue(false); newVH.setVaccinated((short) 1); newVH.setVaccination(vdpt3); newVH.setDateOfImmunization(dpt3); newVH.setVaccinatedLocation(child.getVillage().getCommune()); childrenVaccinationHistoryDao.save(newVH); } else { newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setOverdue(false); newVH.setReasonIfMissed(""); newVH.setVaccinated((short) 0); newVH.setVaccination(vdpt3); childrenVaccinationHistoryDao.save(newVH); } if (opv3 != null) { log.debug("OPV3 " + opv1); newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setReasonIfMissed(""); //newVH.setOverdue(false); newVH.setVaccinated((short) 1); newVH.setVaccination(vopv3); newVH.setDateOfImmunization(opv3); newVH.setVaccinatedLocation(child.getVillage().getCommune()); childrenVaccinationHistoryDao.save(newVH); } else { newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setOverdue(false); newVH.setReasonIfMissed(""); newVH.setVaccinated((short) 0); newVH.setVaccination(vopv3); childrenVaccinationHistoryDao.save(newVH); } if (measles != null) { log.debug("Measles " + measles); newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setReasonIfMissed(""); //newVH.setOverdue(false); newVH.setVaccinated((short) 1); newVH.setVaccination(vmeasles); newVH.setDateOfImmunization(measles); newVH.setVaccinatedLocation(child.getVillage().getCommune()); childrenVaccinationHistoryDao.save(newVH); } else { newVH = new ChildrenVaccinationHistory(); newVH.setChild(child); newVH.setOverdue(false); newVH.setReasonIfMissed(""); newVH.setVaccinated((short) 0); newVH.setVaccination(vmeasles); childrenVaccinationHistoryDao.save(newVH); } log.debug("--------------------------------------"); } } } catch (Exception e) { } }
From source file:org.jtotus.database.FileSystemFromHex.java
License:Open Source License
public BigDecimal omxNordicFile(String fileName, DateTime calendar, int row) { BigDecimal result = null;/*from ww w .j a v a 2 s . c om*/ try { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(pathToDataBaseDir + fileName)); HSSFWorkbook workbook = new HSSFWorkbook(fs); HSSFSheet worksheet = workbook.getSheetAt(0); //HSSFRow row1 = worksheet.getRow(0); String correctTime = dateFormatter.print(calendar); Iterator rowIter = worksheet.rowIterator(); while (rowIter.hasNext()) { HSSFRow rows = (HSSFRow) rowIter.next(); HSSFCell cell = rows.getCell(0); String dateString = null; if (cell.getCellType() == Cell.CELL_TYPE_STRING) { dateString = cell.getStringCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { Date date = cell.getDateCellValue(); SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd"); dateString = format.format(date); // System.err.printf("File (%s) is corrucped ? type:%s\n", fileName, dateString); } else { System.err.printf("File (%s) is corrucped ? type:%d formula:%d\n", fileName, cell.getCellType(), Cell.CELL_TYPE_FORMULA); return null; } // help.debug("FileSystemFromHex","Searching:%s from:%s\n", correctTime, temp); if (correctTime.compareTo(dateString) == 0) { HSSFCell closingPrice = rows.getCell(row); if (closingPrice == null) return null; float floatTemp = (float) closingPrice.getNumericCellValue(); System.out.printf("FileSystemFromHex", "Closing price at:%d f:%.4f Time:%s\n", cell.getRowIndex(), floatTemp, correctTime); return new BigDecimal(floatTemp); } } } catch (IOException ex) { Logger.getLogger(FileSystemFromHex.class.getName()).log(Level.SEVERE, null, ex); } return result; }
From source file:org.mifos.application.importexport.xls.XlsClientsImporter.java
License:Open Source License
private Date getCellDateValue(final HSSFRow row, final XlsImportConstants xlsImportConstant) throws Exception { HSSFCell cell = null; try {// w ww .j a v a 2 s . c o m cell = row.getCell(xlsImportConstant.value(), HSSFRow.RETURN_BLANK_AS_NULL); return (cell == null) ? null : cell.getDateCellValue(); } catch (Exception ex) { String invalidDateString = (cell == null) ? "" : getCellStringValue(row, xlsImportConstant); throw new Exception(getMessage(XlsMessageConstants.INVALID_DATE, invalidDateString)); } }
From source file:org.openhealthtools.mdht.cda.generate.C32Generator.java
License:Open Source License
private static String getCellValueFromDate(HSSFCell cell) { if (cell != null) { Date date = cell.getDateCellValue(); return sdf.format(date); } else {/*w ww.j a va2s .c o m*/ return ""; } }
From source file:org.opensprout.osaf.util.ExcelUtils.java
License:Open Source License
public static Date getDateValue(HSSFRow row, int i) { HSSFCell c = row.getCell((short) i); if (c == null) return new Date(); return c.getDateCellValue(); }
From source file:org.openswing.swing.importdata.java.ImportFromExcel.java
License:Open Source License
public ArrayList importData(int cols, InputStream in) throws Throwable { // read existing workbook HSSFWorkbook wb = new HSSFWorkbook(new BufferedInputStream(in)); // retrieve existing sheet HSSFSheet s = wb.getSheetAt(0);//w w w . jav a2s . c om int i = 0; ArrayList rows = new ArrayList(); Object[] rowobj = null; HSSFRow row = null; boolean rowEmpty = true; HSSFCell cell = null; while (true) { rowEmpty = true; rowobj = new Object[cols]; row = s.getRow(i); if (row == null) break; for (short j = 0; j < cols; j++) { cell = row.getCell(j); if (cell != null) { if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) { rowobj[j] = new Double(cell.getNumericCellValue()); } else try { rowobj[j] = cell.getDateCellValue(); } catch (Exception ex) { rowobj[j] = cell.getStringCellValue(); } } if (rowobj[j] != null) rowEmpty = false; } if (rowEmpty) break; rows.add(rowobj); i++; } return rows; }
From source file:org.projectforge.excel.ExcelImport.java
License:Open Source License
/** * convert the cell-value to the type in the bean. * /*from ww w.j a v a2 s .co m*/ * @param cell the cell containing an arbitrary value * @param destClazz the target class * @return a String, Boolean, Date or BigDecimal */ private Object toNativeType(final HSSFCell cell, final Class<?> destClazz) { if (cell == null) { return null; } switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: log.debug("using numeric"); if (Date.class.isAssignableFrom(destClazz)) { return cell.getDateCellValue(); } String strVal = String.valueOf(cell.getNumericCellValue()); strVal = strVal.replaceAll("\\.0*$", ""); return ConvertUtils.convert(strVal, destClazz); case HSSFCell.CELL_TYPE_BOOLEAN: log.debug("using boolean"); return Boolean.valueOf(cell.getBooleanCellValue()); case HSSFCell.CELL_TYPE_STRING: log.debug("using string"); strVal = StringUtils.trimToNull(cell.getStringCellValue()); return ConvertUtils.convert(strVal, destClazz); case HSSFCell.CELL_TYPE_BLANK: return null; case HSSFCell.CELL_TYPE_FORMULA: return new Formula(cell.getCellFormula()); default: return StringUtils.trimToNull(cell.getStringCellValue()); } }
From source file:org.seasar.dbflute.helper.io.xls.DfTableXlsReader.java
License:Apache License
protected Object extractCellValue(DfDataTable table, int columnIndex, HSSFRow row, HSSFCell cell) { if (cell == null) { return isEmptyStringTarget(table, columnIndex) ? "" : null; }//from w w w.j av a 2 s . co m switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (isCellDateFormatted(cell)) { return DfTypeUtil.toTimestamp(cell.getDateCellValue()); } final double numericCellValue = cell.getNumericCellValue(); if (isInt(numericCellValue)) { return new BigDecimal((int) numericCellValue); } return new BigDecimal(Double.toString(numericCellValue)); case HSSFCell.CELL_TYPE_STRING: return processRichStringCellValue(table, columnIndex, row, cell); case HSSFCell.CELL_TYPE_BOOLEAN: boolean b = cell.getBooleanCellValue(); return Boolean.valueOf(b); default: return isEmptyStringTarget(table, columnIndex) ? "" : null; } }