List of usage examples for org.apache.poi.poifs.filesystem POIFSFileSystem POIFSFileSystem
public POIFSFileSystem(InputStream stream) throws IOException
From source file:org.egov.egf.web.actions.budget.BudgetLoadAction.java
License:Open Source License
private void prepareOutPutFileWithFinalStatus(List<BudgetUpload> budgetUploadList) { FileInputStream fsIP;//from w w w . ja va 2s.c o m try { fsIP = new FileInputStream(budgetInXls); Map<String, String> errorsMap = new HashMap<String, String>(); final POIFSFileSystem fs = new POIFSFileSystem(fsIP); final HSSFWorkbook wb = new HSSFWorkbook(fs); wb.getNumberOfSheets(); final HSSFSheet sheet = wb.getSheetAt(0); Map<String, String> finalStatusMap = new HashMap<String, String>(); HSSFRow row = sheet.getRow(3); HSSFCell cell = row.createCell(7); cell.setCellValue("Status"); for (BudgetUpload budget : budgetUploadList) finalStatusMap.put(budget.getFundCode() + "-" + budget.getFunctionCode() + "-" + budget.getDeptCode() + "-" + budget.getBudgetHead(), budget.getFinalStatus()); for (int i = DATA_STARTING_ROW_INDEX; i <= sheet.getLastRowNum(); i++) { HSSFRow finalStatusRow = sheet.getRow(i); HSSFCell finalStatusCell = finalStatusRow.createCell(7); finalStatusCell .setCellValue(finalStatusMap.get((getStrValue(sheet.getRow(i).getCell(FUNDCODE_CELL_INDEX)) + "-" + getStrValue(sheet.getRow(i).getCell(FUNCTIONCODE_CELL_INDEX)) + "-" + getStrValue(sheet.getRow(i).getCell(DEPARTMENTCODE_CELL_INDEX)) + "-" + getStrValue(sheet.getRow(i).getCell(GLCODE_CELL_INDEX))))); } FileOutputStream output_file = new FileOutputStream(budgetInXls); wb.write(output_file); output_file.close(); if (budgetInXlsFileName.contains("_budget_original_")) { budgetOutPutFileName = budgetInXlsFileName.split("_budget_original_")[0] + "_budget_output_" + timeStamp + "." + budgetInXlsFileName.split("\\.")[1]; } else if (budgetInXlsFileName.contains("_budget_output_")) { budgetOutPutFileName = budgetInXlsFileName.split("_budget_output_")[0] + "_budget_output_" + timeStamp + "." + budgetInXlsFileName.split("\\.")[1]; } else { if (budgetInXlsFileName.length() > 60) { throw new ValidationException(Arrays .asList(new ValidationError(getText("file.name.should.be.less.then.60.characters"), getText("file.name.should.be.less.then.60.characters")))); } else budgetOutPutFileName = budgetInXlsFileName.split("\\.")[0] + "_budget_output_" + timeStamp + "." + budgetInXlsFileName.split("\\.")[1]; } final FileStoreMapper outPutFileStore = fileStoreService.store(budgetInXls, budgetOutPutFileName, budgetInXlsContentType, FinancialConstants.MODULE_NAME_APPCONFIG); persistenceService.persist(outPutFileStore); outPutFileStoreId = outPutFileStore.getFileStoreId(); } catch (FileNotFoundException e) { throw new ValidationException(Arrays.asList(new ValidationError(e.getMessage(), e.getMessage()))); } catch (IOException e) { throw new ValidationException(Arrays.asList(new ValidationError(e.getMessage(), e.getMessage()))); } }
From source file:org.encuestame.business.search.IndexerFile.java
License:Apache License
/** * Parse spreadsheets documents.//w ww . j av a 2s .com * @param file * @return * @throws Exception */ public static HSSFWorkbook parseSpreadsheetsDocument(final File file) throws Exception { InputStream is = new FileInputStream(file); POIFSFileSystem fileSystem = new POIFSFileSystem(is); HSSFWorkbook workBook = new HSSFWorkbook(fileSystem); return workBook; }
From source file:org.encuestame.business.search.SearchUtils.java
License:Apache License
/** * Create Spreadsheets Document.//from w w w . jav a 2 s.c o m * @param file Spreadsheet {@link File}. * @param Long attachmentId. * @return {@link Document} * @throws FileNotFoundException */ public static Document createSpreadsheetsDocument(final File file) throws Exception { InputStream is = new FileInputStream(file); StringBuilder contents = new StringBuilder(); POIFSFileSystem fileSystem = new POIFSFileSystem(is); HSSFWorkbook workBook = new HSSFWorkbook(fileSystem); for (int i = 0; i < workBook.getNumberOfSheets(); i++) { HSSFSheet sheet = workBook.getSheetAt(i); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); // Display the row number log.debug(row.getRowNum()); Iterator<Cell> cells = row.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); // Display the cell number of the current Row switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: { log.debug(String.valueOf(cell.getNumericCellValue())); contents.append(String.valueOf(cell.getNumericCellValue())).append(" "); break; } case HSSFCell.CELL_TYPE_STRING: { HSSFRichTextString richTextString = cell.getRichStringCellValue(); log.debug(richTextString.toString()); contents.append(richTextString.toString()).append(" "); break; } case HSSFCell.CELL_TYPE_BOOLEAN: { contents.append(String.valueOf(cell.getBooleanCellValue())).append(" "); break; } } } } } Document doc = SearchUtils.addFields(file, contents.toString()); return doc; }
From source file:org.fenixedu.academic.ui.struts.action.administrativeOffice.scholarship.utl.report.ReportStudentsUTLCandidatesDA.java
License:Open Source License
public ActionForward showReport(final ActionMapping mapping, final ActionForm actionForm, final HttpServletRequest request, final HttpServletResponse response) throws IOException { ReportStudentsUTLCandidatesBean bean = getRenderedObject("bean"); if (bean == null || bean.getXlsFile() == null) { return prepare(mapping, actionForm, request, response); }/*from www . jav a 2 s . c o m*/ POIFSFileSystem fs = new POIFSFileSystem(bean.getXlsFile()); try (HSSFWorkbook wb = new HSSFWorkbook(fs)) { HSSFSheet sheet = wb.getSheetAt(0); if (sheet == null) { addErrorMessage(request, "error", "error.academicAdminOffice.scholarship.utl.report.invalid.spreadsheet", new String[0]); return prepare(mapping, actionForm, request, response); } ReportStudentsUTLCandidates report = null; if (bean.getForFirstYear()) { report = new ReportStudentsUTLCandidatesForFirstYear(bean.getExecutionYear(), sheet); } else { report = new ReportStudentsUTLCandidates(bean.getExecutionYear(), sheet); } request.setAttribute("report", report); List<StudentLine> correctStudentLines = new ArrayList<StudentLine>(); List<StudentLine> erroneousStudentLines = new ArrayList<StudentLine>(); erroneousStudentLines.addAll(report.getErroneousStudentLines()); for (StudentLine studentLine : report.getCorrectStudentLines()) { if (studentLine.isAbleToReadAllValues()) { correctStudentLines.add(studentLine); } else { erroneousStudentLines.add(studentLine); } } request.setAttribute("correctStudentLines", correctStudentLines); request.setAttribute("erroneousStudentLines", erroneousStudentLines); } return mapping.findForward("showReport"); }
From source file:org.frameworkset.web.servlet.view.document.AbstractExcelView.java
License:Apache License
/** * Creates the workbook from an existing XLS document. * @param url the URL of the Excel template without localization part nor extension * @param request current HTTP request//from w w w . j av a 2 s . co m * @return the HSSFWorkbook * @throws Exception in case of failure */ protected HSSFWorkbook getTemplateSource(String url, HttpServletRequest request) throws Exception { LocalizedResourceHelper helper = new LocalizedResourceHelper(getApplicationContext()); Locale userLocale = RequestContextUtils.getRequestContextLocal(request); Resource inputFile = helper.findLocalizedResource(url, EXTENSION, userLocale); // Create the Excel document from the source. if (logger.isDebugEnabled()) { logger.debug("Loading Excel workbook from " + inputFile); } POIFSFileSystem fs = new POIFSFileSystem(inputFile.getInputStream()); return new HSSFWorkbook(fs); }
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 ww . j a v 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
public String printListChildren(ChildrenSearchVO params) { List<ChildrenPrintVO> children = childrenDaoExt.searchChildrenForPrint(params); SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy"); String strDOBFrom = format.format(params.getDateOfBirthFrom()); String strDOBTo = format.format(params.getDateOfBirthTo()); String path = ""; String prefixFileName = ""; Commune commune = communeDao.get(params.getCommuneId()); prefixFileName = commune.getDistrict().getProvince().getProvinceId() + commune.getDistrict().getDistrictId() + commune.getCommuneId();//from w w w.j a v a 2 s .c om GraniteContext gc = GraniteContext.getCurrentInstance(); ServletContext sc = ((HttpGraniteContext) gc).getServletContext(); String reportDir = sc.getRealPath(config.getBaseReportDir()); long currentTime = System.currentTimeMillis(); String filePath = reportDir + "/" + prefixFileName + "_List_Children_Excel_" + currentTime; POIFSFileSystem fs; try { filePath += ".xls"; fs = new POIFSFileSystem(new FileInputStream(reportDir + "/excel/ListOfChildrenInCommune.xls")); HSSFWorkbook wb = new HSSFWorkbook(fs, true); HSSFSheet s = wb.getSheetAt(0); HSSFRow r = null; HSSFCell c = null; r = s.getRow(0); c = r.getCell(1); c.setCellValue(commune.getCommuneName()); c = r.getCell(2); c.setCellValue("(" + strDOBFrom + " - " + strDOBTo + ")"); 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); HSSFCellStyle cs2 = wb.createCellStyle(); cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs2.setBorderTop(HSSFCellStyle.BORDER_THIN); cs2.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs2.setBorderRight(HSSFCellStyle.BORDER_THIN); CreationHelper createHelper = wb.getCreationHelper(); cs2.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy")); int rownum = 3; for (rownum = 3; rownum < children.size() + 3; rownum++) { r = s.createRow(rownum); c = r.createCell(0); c.setCellStyle(cs1); c.setCellValue(rownum - 2); c = r.createCell(1); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getFullName()); c = r.createCell(2); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getDateOfBirth()); c = r.createCell(3); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).isGender() == true ? "N" : "Nam"); c = r.createCell(4); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getVillageName()); c = r.createCell(5); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getMotherName()); c = r.createCell(6); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getMotherBirthYear() != null ? children.get(rownum - 3).getMotherBirthYear() : 0); c = r.createCell(7); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getMotherMobile()); c = r.createCell(8); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getFatherName()); c = r.createCell(9); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getFatherBirthYear() != null ? children.get(rownum - 3).getFatherBirthYear() : 0); c = r.createCell(10); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getFatherMobile()); c = r.createCell(11); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getCaretakerName()); c = r.createCell(12); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getCaretakerMobile()); c = r.createCell(13); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getVGB()); c = r.createCell(14); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getBCG()); c = r.createCell(15); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getDPT_VGB_Hib1()); c = r.createCell(16); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getDPT_VGB_Hib2()); c = r.createCell(17); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getDPT_VGB_Hib3()); c = r.createCell(18); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getOPV1()); c = r.createCell(19); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getOPV2()); c = r.createCell(20); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getOPV3()); c = r.createCell(21); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getMeasles1()); } FileOutputStream fileOut = new FileOutputStream(filePath); wb.write(fileOut); fileOut.close(); path = "/reports/" + prefixFileName + "_List_Children_Excel_" + currentTime + ".xls"; } 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
public String printListVaccinatedInLocationReport(String type, String timeFrom, String timeTo, Commune commune, District district, Vaccination vaccine, List<ChildrenVaccinatedInLocationVO> statistics) { SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy"); String path = ""; String prefixFileName = ""; if (commune != null) prefixFileName = district.getDistrictId() + "_" + commune.getCommuneId(); else if (district != null) prefixFileName = 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 + "_DanhSachTreDenTiem_" + vaccine.getName() + "_" + currentTime;/*from w w w . ja va 2s . c o m*/ POIFSFileSystem fs; try { filePath += ".xls"; fs = new POIFSFileSystem( new FileInputStream(reportDir + "/excel/ListOfChildrenVaccinatedInLocation.xls")); HSSFWorkbook wb = new HSSFWorkbook(fs, true); HSSFSheet s = wb.getSheetAt(0); HSSFRow r = null; HSSFCell c = null; r = s.getRow(0); c = r.getCell(0); c.setCellValue("Danh sch tr n tim chng " + vaccine.getName() + " (bao gm c tr tim bnh vin/phng khm)"); r = s.getRow(1); c = r.getCell(1); c.setCellValue(district.getDistrictName()); if (commune != null) { c = r.getCell(2); c.setCellValue("X"); c = r.getCell(3); c.setCellValue(commune.getCommuneName()); c = r.getCell(4); c.setCellValue("(" + timeFrom + " - " + timeTo + ")"); } else { c = r.getCell(2); c.setCellValue("(" + timeFrom + " - " + timeTo + ")"); } 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); HSSFCellStyle cs2 = wb.createCellStyle(); cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs2.setBorderTop(HSSFCellStyle.BORDER_THIN); cs2.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs2.setBorderRight(HSSFCellStyle.BORDER_THIN); CreationHelper createHelper = wb.getCreationHelper(); cs2.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy")); int rownum = 3; for (rownum = 3; rownum < statistics.size() + 3; rownum++) { r = s.createRow(rownum); c = r.createCell(0); c.setCellStyle(cs1); c.setCellValue(rownum - 2); c = r.createCell(1); c.setCellStyle(cs); c.setCellValue(statistics.get(rownum - 3).getCommuneName()); c = r.createCell(2); c.setCellStyle(cs); c.setCellValue(statistics.get(rownum - 3).getVillageName()); c = r.createCell(3); c.setCellStyle(cs); c.setCellValue(statistics.get(rownum - 3).getChildCode()); c = r.createCell(4); c.setCellStyle(cs); c.setCellValue(statistics.get(rownum - 3).getFullName()); c = r.createCell(5); c.setCellStyle(cs1); c.setCellValue(statistics.get(rownum - 3).getGender() == true ? "N" : "Nam"); c = r.createCell(6); c.setCellStyle(cs2); c.setCellValue(statistics.get(rownum - 3).getDateOfBirth()); c = r.createCell(7); c.setCellStyle(cs); c.setCellValue(statistics.get(rownum - 3).getMotherName()); c = r.createCell(8); c.setCellStyle(cs2); c.setCellValue(statistics.get(rownum - 3).getDateOfImmunization()); c = r.createCell(9); c.setCellStyle(cs); String vaccinatedLocation = ""; if (statistics.get(rownum - 3).getOtherLocation() != null && statistics.get(rownum - 3).getOtherLocation() >= 1 && statistics.get(rownum - 3).getOtherLocation() <= 4) { if (statistics.get(rownum - 3).getOtherLocation() == 1) vaccinatedLocation = "Bnh vin TW"; else if (statistics.get(rownum - 3).getOtherLocation() == 2) vaccinatedLocation = "Bnh vin tnh"; else if (statistics.get(rownum - 3).getOtherLocation() == 3) vaccinatedLocation = "Bnh vin huyn"; else if (statistics.get(rownum - 3).getOtherLocation() == 4) vaccinatedLocation = "Phng khm/Bnh vin t nhn"; } else vaccinatedLocation = statistics.get(rownum - 3).getVaccinatedCommune(); c.setCellValue(vaccinatedLocation); } FileOutputStream fileOut = new FileOutputStream(filePath); wb.write(fileOut); fileOut.close(); path = "/reports/" + prefixFileName + "_DanhSachTreDenTiem_" + vaccine.getName() + "_" + currentTime + ".xls"; } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return path; }
From source file:org.hsh.bfr.db.imports.custom.LieferkettenImporterEFSA.java
License:Open Source License
public void mergeIDs() { System.err.println("Merging..."); try {// w ww . j a v a 2s. c o m //FileInputStream is = new FileInputStream("C:\\Users\\Armin\\Desktop\\AllKrisen\\EFSA\\mergeList.xls"); FileInputStream is = new FileInputStream(DBKernel.HSHDB_PATH + "mergeList.xls"); POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet mergeSheet = wb.getSheet("mergeList"); int numRows = mergeSheet.getLastRowNum() + 1; for (int i = 1; i < numRows; i++) { try { HSSFRow row = mergeSheet.getRow(i); if (row != null) { HSSFCell cell = row.getCell(0); if (cell != null) { Integer oldEfsaID = (int) cell.getNumericCellValue(); if (cell != null) { cell = row.getCell(1); Integer newEfsaID = (int) cell.getNumericCellValue(); DBKernel.mergeIDs(DBKernel.getDBConnection(), "Station", oldEfsaID, newEfsaID); } } } } catch (Exception e) { System.err.println(e.getMessage() + "\t" + i); } } } catch (Exception e) { } System.err.println("Merging...Fin!"); }
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.java 2 s . co m //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; }