List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getStringCellValue
public String getStringCellValue()
From source file:org.gaixie.micrite.enterprise.service.impl.DealWithEnterprise.java
@Override public int doJob(File src, Map<String, String> res) throws Exception { List<Dictionary> qualifications = dictionaryService.findALLDictionary(Enterprise.QUALIFICATION_TYPE); List<Dictionary> kinds = dictionaryService.findALLDictionary(Enterprise.KIND_TYPE); List<Dictionary> workTypes = dictionaryService.findALLDictionary(Enterprise.WORKTYPE_TYPE); // List<Dictionary> ranges=dictionaryService.findALLDictionary(11); // TODO Auto-generated method stub HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(src)); Enterprise enterprise = new Enterprise(); HSSFSheet sheet = wb.getSheetAt(0);//from www. j a v a2 s . co m String temp = null; for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); HSSFCell cell = row.getCell(4); try { temp = cell.getStringCellValue(); } catch (Exception e) { // TODO: handle exception e.printStackTrace(); } if (enterpriseService.existEnterprise(null, temp)) continue; cell = row.getCell(1); enterprise.setUnitName(cell.getStringCellValue()); cell = row.getCell(2); enterprise.setLegalPerson(cell.getStringCellValue()); cell = row.getCell(3); enterprise.setTelephone1(cell.getStringCellValue()); cell = row.getCell(4); enterprise.setLicense(cell.getStringCellValue()); cell = row.getCell(5); enterprise.setQualification(DictionaryUtil.getDictionary(cell.getStringCellValue(), qualifications)); cell = row.getCell(6); enterprise.setHandleMan(cell.getStringCellValue()); cell = row.getCell(7); enterprise.setTelephone2(cell.getStringCellValue()); cell = row.getCell(8); enterprise.setTelephone3(cell.getStringCellValue()); cell = row.getCell(9); enterprise.setCommission(cell.getStringCellValue()); cell = row.getCell(10); enterprise.setTelephone4(cell.getStringCellValue()); cell = row.getCell(11); enterprise.setKind(DictionaryUtil.getDictionary(cell.getStringCellValue(), kinds)); cell = row.getCell(12); if (cell.getStringCellValue() != null) { try { enterprise.setLicenseDate(df.parse(cell.getStringCellValue())); } catch (Exception e) { } } cell = row.getCell(13); if (cell.getStringCellValue() != null) { try { enterprise.setDateBegin(df.parse(cell.getStringCellValue()));// HH:mm:ss.S } catch (Exception e) { } } cell = row.getCell(14); if (cell.getStringCellValue() != null) { try { enterprise.setDateEnd(df.parse(cell.getStringCellValue())); } catch (Exception e) { } } cell = row.getCell(15); enterprise.setAddress(cell.getStringCellValue()); cell = row.getCell(16); if (cell.getStringCellValue() != null) { try { enterprise.setEditDate(df.parse(cell.getStringCellValue())); } catch (Exception e) { } } cell = row.getCell(17); enterprise.setWorkArea(cell.getStringCellValue()); cell = row.getCell(18); enterprise.setWorkRemark(cell.getStringCellValue()); cell = row.getCell(19); enterprise.setWorkType(DictionaryUtil.getDictionary(cell.getStringCellValue(), workTypes)); //?cell20 ?? //--------------------------------- //? enterprise.setStation(DictionaryUtil.getDefaultDictionary()); enterprise.setStatus(IEnterpriseService.STATUS_NORMAL); enterpriseService.add(enterprise); } return IDealWith.OK; }
From source file:org.hil.children.service.impl.ChildrenManagerImpl.java
License:Open Source License
public String printListVaccinationReport(String type, String timeFrom, String timeTo, Commune commune, District district, List<RegionVaccinationReportData> statistics) { String path = ""; String prefixFileName = ""; if (commune != null) prefixFileName = commune.getDistrict().getProvince().getProvinceId() + commune.getDistrict().getDistrictId() + commune.getCommuneId(); else//from w w w . j av a 2 s . c o m prefixFileName = district.getProvince().getProvinceId() + district.getDistrictId(); GraniteContext gc = GraniteContext.getCurrentInstance(); ServletContext sc = ((HttpGraniteContext) gc).getServletContext(); String reportDir = sc.getRealPath(config.getBaseReportDir()); long currentTime = System.currentTimeMillis(); String filePath = reportDir + "/" + prefixFileName + "_Report_" + currentTime; if (type.equalsIgnoreCase("pdf")) { JasperPrint reportPrint = createListVaccinationReportPrint(timeFrom, timeTo, commune, district, statistics); try { filePath += ".pdf"; JasperExportManager.exportReportToPdfFile(reportPrint, filePath); path = "/reports/" + prefixFileName + "_Report_" + currentTime + ".pdf"; log.debug("path to pdf report:" + path); } catch (Exception ex) { String connectMsg = "Could not create the report " + ex.getMessage() + " " + ex.getLocalizedMessage(); log.debug(connectMsg); } } else { filePath += ".xls"; POIFSFileSystem fs; String regionName = ""; String provinceName = ""; String districtName = ""; String communeName = ""; String timeData = ""; Short rId = 0; if (commune != null) { communeName = commune.getCommuneName(); provinceName = commune.getDistrict().getProvince().getProvinceName(); districtName = commune.getDistrict().getDistrictName(); rId = commune.getDistrict().getProvince().getRegionId(); } else if (district != null) { provinceName = district.getProvince().getProvinceName(); districtName = district.getDistrictName(); rId = district.getProvince().getRegionId(); } if (rId == 1) regionName = "Mi?n Bc"; else if (rId == 2) regionName = "Mi?n Trung"; else regionName = "Mi?n Nam"; if (timeFrom.equalsIgnoreCase(timeTo)) { timeData = timeTo; } else { timeData = timeFrom + " - " + timeTo; } try { fs = new POIFSFileSystem(new FileInputStream(reportDir + "/excel/TCMR_Report_Template.xls")); HSSFWorkbook wb = new HSSFWorkbook(fs, true); HSSFSheet s = wb.getSheetAt(0); HSSFRow r = null; HSSFCell c = null; r = s.getRow(4); c = r.getCell(1); c.setCellValue(c.getStringCellValue() + " " + regionName.toUpperCase()); r = s.getRow(5); c = r.getCell(1); c.setCellValue(c.getStringCellValue() + " " + provinceName.toUpperCase()); r = s.getRow(6); c = r.getCell(1); c.setCellValue(c.getStringCellValue() + " " + districtName.toUpperCase()); r = s.getRow(7); c = r.getCell(1); c.setCellValue(c.getStringCellValue() + " " + communeName.toUpperCase()); r = s.getRow(4); c = r.getCell(15); c.setCellValue(timeData); HSSFCellStyle cs = wb.createCellStyle(); cs.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs.setBorderTop(HSSFCellStyle.BORDER_THIN); cs.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle cs1 = wb.createCellStyle(); cs1.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs1.setBorderTop(HSSFCellStyle.BORDER_THIN); cs1.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs1.setBorderRight(HSSFCellStyle.BORDER_THIN); cs1.setAlignment(HSSFCellStyle.ALIGN_CENTER); int rownum = 13; for (rownum = 13; rownum < statistics.size() + 11; rownum++) { if (rownum < statistics.size() + 10) copyRow(wb, s, rownum, rownum + 1); r = s.getRow(rownum); r.setHeight((short) 270); c = r.getCell(1); c.setCellValue(rownum - 12); c = r.getCell(3); c.setCellValue(statistics.get(rownum - 13).getRegionName()); c = r.getCell(6); c.setCellValue(statistics.get(rownum - 13).getChildrenUnder1() == null ? 0 : statistics.get(rownum - 13).getChildrenUnder1()); c = r.getCell(8); c.setCellValue(statistics.get(rownum - 13).getBCG() + " (" + statistics.get(rownum - 13).geteBCG() + ")"); c = r.getCell(10); c.setCellValue(statistics.get(rownum - 13).getVGBL24() + " (" + statistics.get(rownum - 13).geteVGBL24() + ")"); c = r.getCell(11); c.setCellValue(statistics.get(rownum - 13).getVGBG24() + " (" + statistics.get(rownum - 13).geteVGBG24() + ")"); c = r.getCell(14); c.setCellValue(statistics.get(rownum - 13).getDPT_VGB_Hib1() + " (" + statistics.get(rownum - 13).geteDPT_VGB_Hib1() + ")"); c = r.getCell(16); c.setCellValue(statistics.get(rownum - 13).getDPT_VGB_Hib2() + " (" + statistics.get(rownum - 13).geteDPT_VGB_Hib2() + ")"); c = r.getCell(18); c.setCellValue(statistics.get(rownum - 13).getDPT_VGB_Hib3() + " (" + statistics.get(rownum - 13).geteDPT_VGB_Hib3() + ")"); c = r.getCell(19); c.setCellValue(statistics.get(rownum - 13).getOPV1() + " (" + statistics.get(rownum - 13).geteOPV1() + ")"); c = r.getCell(20); c.setCellValue(statistics.get(rownum - 13).getOPV2() + " (" + statistics.get(rownum - 13).geteOPV2() + ")"); c = r.getCell(21); c.setCellValue(statistics.get(rownum - 13).getOPV3() + " (" + statistics.get(rownum - 13).geteOPV3() + ")"); c = r.getCell(23); c.setCellValue(statistics.get(rownum - 13).getMeasles1() + " (" + statistics.get(rownum - 13).geteMeasles1() + ")"); c = r.getCell(25); c.setCellValue(statistics.get(rownum - 13).getAmountOfFinish()); c = r.getCell(27); c.setCellValue(statistics.get(rownum - 13).getProtectedTetanusCases() == null ? 0 : statistics.get(rownum - 13).getProtectedTetanusCases()); c = r.getCell(28); c.setCellValue(statistics.get(rownum - 13).getReactionNormalCases() == null ? 0 : statistics.get(rownum - 13).getReactionNormalCases()); c = r.getCell(30); c.setCellValue(statistics.get(rownum - 13).getReactionSeriousCases() == null ? 0 : statistics.get(rownum - 13).getReactionSeriousCases()); } if (statistics != null && statistics.size() > 0) { for (; rownum < statistics.size() + 13; rownum++) { r = s.getRow(rownum); c = r.getCell(6); c.setCellValue(statistics.get(rownum - 13).getChildrenUnder1() == null ? 0 : statistics.get(rownum - 13).getChildrenUnder1()); c = r.getCell(8); c.setCellValue(statistics.get(rownum - 13).getBCG() + "\n(" + statistics.get(rownum - 13).geteBCG() + ")"); c = r.getCell(10); c.setCellValue(statistics.get(rownum - 13).getVGBL24() + "\n(" + statistics.get(rownum - 13).geteVGBL24() + ")"); c = r.getCell(11); c.setCellValue(statistics.get(rownum - 13).getVGBG24() + "\n(" + statistics.get(rownum - 13).geteVGBG24() + ")"); c = r.getCell(14); c.setCellValue(statistics.get(rownum - 13).getDPT_VGB_Hib1() + "\n(" + statistics.get(rownum - 13).geteDPT_VGB_Hib1() + ")"); c = r.getCell(16); c.setCellValue(statistics.get(rownum - 13).getDPT_VGB_Hib2() + "\n(" + statistics.get(rownum - 13).geteDPT_VGB_Hib2() + ")"); c = r.getCell(18); c.setCellValue(statistics.get(rownum - 13).getDPT_VGB_Hib3() + "\n(" + statistics.get(rownum - 13).geteDPT_VGB_Hib3() + ")"); c = r.getCell(19); c.setCellValue(statistics.get(rownum - 13).getOPV1() + "\n(" + statistics.get(rownum - 13).geteOPV1() + ")"); c = r.getCell(20); c.setCellValue(statistics.get(rownum - 13).getOPV2() + "\n(" + statistics.get(rownum - 13).geteOPV2() + ")"); c = r.getCell(21); c.setCellValue(statistics.get(rownum - 13).getOPV3() + "\n(" + statistics.get(rownum - 13).geteOPV3() + ")"); c = r.getCell(23); c.setCellValue(statistics.get(rownum - 13).getMeasles1() + "\n(" + statistics.get(rownum - 13).geteMeasles1() + ")"); c = r.getCell(25); c.setCellValue(statistics.get(rownum - 13).getAmountOfFinish()); c = r.getCell(27); c.setCellValue(statistics.get(rownum - 13).getProtectedTetanusCases() == null ? 0 : statistics.get(rownum - 13).getProtectedTetanusCases()); c = r.getCell(28); c.setCellValue(statistics.get(rownum - 13).getReactionNormalCases() == null ? 0 : statistics.get(rownum - 13).getReactionNormalCases()); c = r.getCell(30); c.setCellValue(statistics.get(rownum - 13).getReactionSeriousCases() == null ? 0 : statistics.get(rownum - 13).getReactionSeriousCases()); r.setHeight((short) 500); } } else { for (; rownum < 16; rownum++) { r = s.getRow(rownum); c = r.getCell(6); c.setCellValue(""); c = r.getCell(8); c.setCellValue(""); c = r.getCell(10); c.setCellValue(""); c = r.getCell(11); c.setCellValue(""); c = r.getCell(14); c.setCellValue(""); c = r.getCell(16); c.setCellValue(""); c = r.getCell(18); c.setCellValue(""); c = r.getCell(19); c.setCellValue(""); c = r.getCell(20); c.setCellValue(""); c = r.getCell(21); c.setCellValue(""); c = r.getCell(23); c.setCellValue(""); c = r.getCell(25); c.setCellValue(""); c = r.getCell(27); c.setCellValue(""); c = r.getCell(28); c.setCellValue(""); c = r.getCell(30); c.setCellValue(""); } } FileOutputStream fileOut = new FileOutputStream(filePath); wb.write(fileOut); fileOut.close(); path = "/reports/" + prefixFileName + "_Report_" + currentTime + ".xls"; log.debug("Excel: " + path); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } return path; }
From source file:org.hil.children.service.impl.ChildrenManagerImpl.java
License:Open Source License
private static void copyRow(HSSFWorkbook workbook, HSSFSheet worksheet, int sourceRowNum, int destinationRowNum) { // Get the source / new row HSSFRow newRow = worksheet.getRow(destinationRowNum); HSSFRow sourceRow = worksheet.getRow(sourceRowNum); // If the row exist in destination, push down all rows by 1 else create a new row if (newRow != null) { worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1); } else {/* w ww . j a v a 2 s .c om*/ newRow = worksheet.createRow(destinationRowNum); } // Loop through source columns to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell HSSFCell oldCell = sourceRow.getCell(i); HSSFCell newCell = newRow.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null; continue; } // Copy style from old cell and apply to new cell HSSFCellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // If there is a cell comment, copy if (newCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } // If there is a cell hyperlink, copy if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } // Set the cell data type newCell.setCellType(oldCell.getCellType()); // Set the cell data value switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } } // If there are are any merged regions in the source row, copy to new row for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getFirstRow() - cellRangeAddress.getLastRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } }
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 w w. java2 s . c o 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.hlc.utility.excel.ExcelInputHandler.java
License:Apache License
/** * Import excel.//from w w w.ja va 2 s . c o m * * @param <T> the generic type * @param type the type * @param in the in * @return the list */ @SuppressWarnings("rawtypes") public <T> List<T> importExcel(Class<T> type, InputStream in) { Excel excelAnn = type.getAnnotation(Excel.class); if (excelAnn == null) { throw new ExcelException("The Class <" + type + "> did not Excel"); } List<T> list = new ArrayList<T>(); Map<String, Method> mapping = new LinkedHashMap<String, Method>(); Map<String, TypeHandler> converters = new HashMap<String, TypeHandler>(); try { // Step1 ?? Field fileds[] = type.getDeclaredFields(); for (int i = 0; i < fileds.length; i++) { Field field = fileds[i]; ExcelColumn column = field.getAnnotation(ExcelColumn.class); if (column != null) { Method setMethod = ReflectionUtils.setValueMethod(field, type); mapping.put(column.value(), setMethod); if (column.converter() != TypeHandler.class) { converters.put(setMethod.getName().toString(), column.converter().newInstance()); } else { converters.put(setMethod.getName().toString(), TypeHandlerFactory.getHandler(field.getType())); } } } T temp = null; HSSFWorkbook hssfWorkbook = new HSSFWorkbook(in); for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } // ?Sheet List<Method> methods = new ArrayList<Method>(); for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow hssfRow = hssfSheet.getRow(rowNum); if (hssfRow == null) { continue; } // ? if (rowNum == 0) { for (int cellNum = 0; cellNum < hssfRow.getLastCellNum(); cellNum++) { String title = hssfRow.getCell(cellNum).getStringCellValue(); Method me = mapping.get(title); if (me == null) { continue; } methods.add(me); } continue; } temp = type.newInstance(); for (int cellNum = 0; cellNum < methods.size(); cellNum++) { HSSFCell xh = hssfRow.getCell(cellNum); if (xh == null) { continue; } Method m = methods.get(cellNum); TypeHandler handler = converters.get(m.getName()); if (handler == null) { continue; } String value = xh.getStringCellValue(); if (StringUtils.isEmpty(value)) { continue; } Object val = null; try { val = handler.stringToType(value); } catch (Exception e) { throw new ExcelException("" + (numSheet + 1) + "" + (rowNum + 1) + "" + (cellNum + 1) + "" + value + "??"); } methods.get(cellNum).invoke(temp, val); } list.add(temp); } } } catch (Exception e) { throw new ExcelException("Excel processing error?", e); } return list; }
From source file:org.hsh.bfr.db.imports.custom.LieferkettenImporterEFSA.java
License:Open Source License
private HashMap<String, Integer> loadNodeIDs10000() { System.err.println("loadNodeIDs10000..."); nodeIds = new HashMap<String, Integer>(); try {/*w w w.ja va 2 s . c om*/ //FileInputStream is = new FileInputStream("C:\\Users\\Armin\\Desktop\\AllKrisen\\EFSA\\nodesids10000.xls"); FileInputStream is = new FileInputStream(DBKernel.HSHDB_PATH + "nodesids10000.xls"); POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet defaultSheet = wb.getSheet("default"); int numRows = defaultSheet.getLastRowNum() + 1; for (int i = 1; i < numRows; i++) { try { HSSFRow row = defaultSheet.getRow(i); if (row != null) { HSSFCell cell = row.getCell(0); Integer id = (int) cell.getNumericCellValue(); if (id > maxNodeID) maxNodeID = id; cell = row.getCell(1); String name = cell.getStringCellValue(); nodeIds.put(name, id); } } catch (Exception e) { System.err.println(e.getMessage() + "\t" + i); } } } catch (Exception e) { } System.err.println("loadNodeIDs10000...Fin!"); return nodeIds; }
From source file:org.jlibrary.core.search.extraction.ExcelExtractor.java
License:Open Source License
/** * Extracts the text from the Excel table content.<p> * //from w ww.j a va 2s .com * @param in the document input stream * @return the extracted text * @throws IOException if something goes wring */ protected String extractTableContent(InputStream in) throws IOException { HSSFWorkbook excelWb = new HSSFWorkbook(in); StringBuilder result = new StringBuilder(4096); int numberOfSheets = excelWb.getNumberOfSheets(); for (int i = 0; i < numberOfSheets; i++) { HSSFSheet sheet = excelWb.getSheetAt(i); int numberOfRows = sheet.getPhysicalNumberOfRows(); if (numberOfRows > 0) { if ((excelWb.getSheetName(i) != null) && !excelWb.getSheetName(i).equals("")) { // append sheet name to content if (i > 0) { result.append("\n\n"); } result.append(excelWb.getSheetName(i).trim()); result.append(":\n\n"); } Iterator rowIt = sheet.rowIterator(); while (rowIt.hasNext()) { HSSFRow row = (HSSFRow) rowIt.next(); if (row != null) { boolean hasContent = false; Iterator it = row.cellIterator(); while (it.hasNext()) { HSSFCell cell = (HSSFCell) it.next(); String text = null; try { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: case HSSFCell.CELL_TYPE_ERROR: // ignore all blank or error cells break; case HSSFCell.CELL_TYPE_NUMERIC: text = Double.toString(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BOOLEAN: text = Boolean.toString(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_STRING: default: text = cell.getStringCellValue(); break; } } catch (Exception e) { // ignore this cell } if ((text != null) && !text.equals("")) { result.append(text.trim()); result.append(' '); hasContent = true; } } if (hasContent) { // append a newline at the end of each row that has content result.append('\n'); } } } } } return result.toString(); }
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 www . j av a2 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.jxstar.report.util.ReportXlsUtil.java
/** * //from www . j av a2 s . c o m * @param mainSheet -- ? * @param subSheet -- ? * @param tempRow -- ????? * @return */ public static HSSFSheet appendSheet(HSSFSheet mainSheet, HSSFSheet subSheet, int tempRow) { if (mainSheet == null || subSheet == null) return null; //?? if (!isAllowOut(mainSheet)) return mainSheet; //? int endRowNum = mainSheet.getPhysicalNumberOfRows(); HSSFRow sourow = null, descrow = null; HSSFCell sourcell = null, descell = null, orgcell = null; int i = 0, offsetcnt = 0; //? copySheetImage(mainSheet.getWorkbook(), subSheet.getWorkbook()); //?? CellRangeAddress range = null; int mergedNum = subSheet.getNumMergedRegions(); for (i = 0; i < mergedNum; i++) { range = subSheet.getMergedRegion(i); range.setFirstRow(range.getFirstRow() + endRowNum); range.setLastRow(range.getLastRow() + endRowNum); mainSheet.addMergedRegion(range); } range = null; //int k = 0; //? mainSheet.setAlternativeExpression(subSheet.getAlternateExpression()); mainSheet.setAlternativeFormula(subSheet.getAlternateFormula()); mainSheet.setAutobreaks(subSheet.getAutobreaks()); mainSheet.setDialog(subSheet.getDialog()); mainSheet.setDisplayGuts(subSheet.getDisplayGuts()); mainSheet.setFitToPage(subSheet.getFitToPage()); for (java.util.Iterator<Row> iterow = subSheet.rowIterator(); iterow.hasNext();) { sourow = (HSSFRow) iterow.next(); offsetcnt = sourow.getRowNum() + endRowNum; descrow = mainSheet.createRow(offsetcnt); descrow.setHeight(sourow.getHeight()); descrow.setHeightInPoints(sourow.getHeightInPoints()); java.util.Iterator<Cell> iter = sourow.cellIterator(); while (iter.hasNext()) { sourcell = (HSSFCell) iter.next(); int column = sourcell.getColumnIndex(); descell = descrow.createCell(column); /** * ??????orgcell = mainSheet.getRow(row).getCell(column); * ?? * ??orgcell.getCellStyle()????sheet?? * This Style does not belong to the supplied Workbook. * ?descell.getCellStyle().cloneStyleFrom(sourcell.getCellStyle());???excel * HSSFCellStyle cs = mainSheet.getWorkbook().createCellStyle(); * cs.cloneStyleFrom(sourcell.getCellStyle()); * descell.setCellStyle(cs);//excel? * tempRow???? */ //???????? int row = sourcell.getRowIndex(); if (tempRow > 0 && row > tempRow) { row = tempRow; } orgcell = mainSheet.getRow(row).getCell(column); if (orgcell != null) { //orgcell.getCellType()???0 descell.setCellType(HSSFCell.CELL_TYPE_STRING); //??? descell.setCellStyle(orgcell.getCellStyle()); } else { _log.showWarn("module xls [{0}, {1}] cell is null!", row, column); } if (sourcell.getCellType() == HSSFCell.CELL_TYPE_STRING) descell.setCellValue(sourcell.getStringCellValue()); else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) descell.setCellValue(sourcell.getNumericCellValue()); } sourow = null; sourcell = null; descrow = null; orgcell = null; } return mainSheet; }
From source file:org.jxstar.report.util.XlsToHtml.java
/** * ??// w w w . j a v a 2 s . c o m * @param cell * @return */ private String getCellValue(HSSFCell cell) { String value = null; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { value = cell.getStringCellValue(); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { value = Double.toString(cell.getNumericCellValue()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { value = Boolean.toString(cell.getBooleanCellValue()); } else { value = ""; } if (value != null) { value = value.trim(); } else { value = ""; } return value; }