List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheet
@Override
public XSSFSheet getSheet(String name)
From source file:org.xframium.page.keyWord.provider.ExcelKeyWordProvider.java
License:Open Source License
private void readElements(InputStream inputStream) { List<MatrixTest> testList = new ArrayList<MatrixTest>(10); XSSFWorkbook workbook = null; try {/*w w w . jav a 2s. c o m*/ workbook = new XSSFWorkbook(inputStream); XSSFSheet sheet = workbook.getSheet("Model"); // // Extract the Tests // for (int i = 1; i <= sheet.getLastRowNum(); i++) { XSSFRow currentRow = sheet.getRow(i); String pageName = getCellValue(currentRow.getCell(0)); if (pageName.toLowerCase().equals("name")) continue; String className = getCellValue(currentRow.getCell(1)); try { Class useClass = KeyWordPage.class; if (className != null && !className.isEmpty()) useClass = (Class<Page>) Class.forName(className); if (log.isDebugEnabled()) log.debug("Creating page as " + useClass.getSimpleName() + " for " + pageName); KeyWordDriver.instance().addPage(pageName, useClass); } catch (Exception e) { log.error("Error creating instance of [" + className + "]"); } } sheet = workbook.getSheet("Tests"); // // Extract the Tests // for (int i = 1; i <= sheet.getLastRowNum(); i++) { XSSFRow currentRow = sheet.getRow(i); List<String> testDefinition = new ArrayList<String>(10); for (int j = 0; j < currentRow.getLastCellNum(); j++) testDefinition.add(getCellValue(currentRow.getCell(j))); MatrixTest currentTest = new MatrixTest(testDefinition.toArray(new String[0])); if (currentTest.getName() != null && !currentTest.getName().isEmpty() && currentTest.isActive()) testList.add(currentTest); } for (MatrixTest currentTest : testList) { List<String[]> stepList = new ArrayList<String[]>(20); sheet = workbook.getSheet(currentTest.getName()); if (sheet != null) { for (int i = 1; i <= sheet.getLastRowNum(); i++) { XSSFRow currentRow = sheet.getRow(i); List<String> stepDefinition = new ArrayList<String>(10); for (int j = 0; j < currentRow.getLastCellNum(); j++) stepDefinition.add(getCellValue(currentRow.getCell(j))); stepList.add(stepDefinition.toArray(new String[0])); } } currentTest.setStepDefinition((String[][]) stepList.toArray(new String[0][0])); } for (MatrixTest currentTest : testList) { if (currentTest.getType().equals("function")) KeyWordDriver.instance().addFunction(currentTest.createTest()); else KeyWordDriver.instance().addTest(currentTest.createTest()); } } catch (Exception e) { log.fatal("Error reading Excel Element File", e); } finally { try { workbook.close(); } catch (Exception e) { } } }
From source file:reports.achievedReport.java
public String getAchievedReport(int passedYear, String passedPath, String dates) throws InvalidFormatException, IOException, SQLException { pepfaryear = passedYear;/*from w w w . j ava 2 s .c o m*/ full_date = dates; dbConn conn = new dbConn(); pos = 0; incrementor = 0; prevyear = pepfaryear - 1; String enddate = pepfaryear + "09"; String startdate = prevyear + "10"; start = Integer.parseInt(startdate); end = Integer.parseInt(enddate); System.out.println("start date : " + start + " end date : " + end); String allpath = passedPath; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb; OPCPackage pkg = OPCPackage.open(allpath); wb = new XSSFWorkbook(pkg); // HSSFWorkbook wb=new HSSFWorkbook(); XSSFSheet shet1 = wb.getSheet("sheet0"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 4000); shet1.setColumnWidth(1, 4000); shet1.setColumnWidth(2, 4000); shet1.setColumnWidth(3, 4000); shet1.setColumnWidth(4, 4000); XSSFRow rw4 = shet1.createRow(0); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); XSSFCell cell0, cell1, cell2, cell3, cell4; cell0 = rw4.createCell(0); cell1 = rw4.createCell(1); cell2 = rw4.createCell(2); cell3 = rw4.createCell(3); cell4 = rw4.createCell(4); cell0.setCellValue("PARTNER NAME"); cell1.setCellValue("AGE BRACKET"); cell2.setCellValue("GENDER"); cell3.setCellValue("MONTH"); cell4.setCellValue("ACHIEVED"); String getClients = "SELECT partner.partner_name," + "CASE " + "when personal_information.completionmonth =01 THEN '" + pepfaryear + "-01(JAN)' " + "when personal_information.completionmonth =02 THEN '" + pepfaryear + "-02 (FEB)' " + "when personal_information.completionmonth =03 THEN '" + pepfaryear + "-03 (MAR)' " + "when personal_information.completionmonth=04 THEN '" + pepfaryear + "-04 (APR)' " + "when personal_information.completionmonth=05 THEN '" + pepfaryear + "-05 (MAY)' " + "when personal_information.completionmonth=06 THEN '" + pepfaryear + "-06 (JUN)' " + "when personal_information.completionmonth=07 THEN '" + pepfaryear + "-07 (JUL)' " + "when personal_information.completionmonth=08 THEN '" + pepfaryear + "-08 (AUG)' " + "when personal_information.completionmonth=09 THEN '" + pepfaryear + "-09 (SEPT)' " + "when personal_information.completionmonth=10 THEN '" + prevyear + "-10 (OCT)' " + "when personal_information.completionmonth=11 THEN '" + prevyear + "-11 (NOV)'" + "when personal_information.completionmonth=12 THEN '" + prevyear + "-12 (DEC)'" + "END AS MONTHS,personal_information.completionyear," + "CASE" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above'" + " ELSE 'NO DATE OF BIRTH'" + " END AS AGEBRACKET," + "CASE " + "when personal_information.gender LIKE 'Female' THEN 'F' " + "when personal_information.gender LIKE 'Male' THEN 'M' " + "ELSE 'NO SEX' " + "END AS SEX" + ",completionmonth,COUNT(personal_information.client_id) FROM personal_information " + "JOIN partner ON personal_information.partner_id=partner.partner_id " + " WHERE personal_information.completionmonth>0 && personal_information.completionyear>0 GROUP BY partner.partner_name,SEX,personal_information.completionyear,MONTHS,AGEBRACKET ORDER BY personal_information.partner_id"; conn.rs = conn.st.executeQuery(getClients); while (conn.rs.next()) { partnername = clientid = gender = ""; age = 0; partnername = conn.rs.getString(1); month = conn.rs.getString(2); year = conn.rs.getInt(3); agebracket = conn.rs.getString(4); gender = conn.rs.getString(5); String dkey = year + "" + conn.rs.getString(6); datekey = Integer.parseInt(dkey); achieved = conn.rs.getInt(7); incrementor += achieved; System.out.println( "date key : " + datekey + "startdate : " + start + " end date : " + end + " year : " + year); if (datekey >= start && datekey <= end && year >= 2014) { pos++; XSSFRow rw4x = shet1.createRow(pos); rw4x.setHeightInPoints(25); rw4x.setRowStyle(style2); XSSFCell cell0x, cell1x, cell2x, cell3x, cell4x; cell0x = rw4x.createCell(0); cell1x = rw4x.createCell(1); cell2x = rw4x.createCell(2); cell3x = rw4x.createCell(3); cell4x = rw4x.createCell(4); // OUTPUT SERVICES PROVIDED================================ cell0x.setCellValue(partnername); cell1x.setCellValue(agebracket); cell2x.setCellValue(gender); cell3x.setCellValue(month); cell4x.setCellValue(achieved); System.out.println(pos + "-----partner :" + partnername + " age bracket :" + agebracket + " gender :" + gender + " completion month : " + month); } } for (int i = 0; i < myalphabet.length; i++) { try { System.out.println("at position : " + myalphabet[i]); String current_drive = myalphabet[i]; File f3 = new File(current_drive + ":\\APHIAPLUS\\PWPDBCONNECTION"); // CREATE A DIRECTORY AND THE FILE TO HOLD DATA if (f3.exists() && f3.isDirectory()) { path = current_drive + ":\\APHIAPLUS\\PWPDBCONNECTION\\DATA\\REPORTS"; new File(path).mkdirs(); filePath = path + "\\PWP_ATTACHED_REPORT" + full_date + ".xlsm"; } //select the last timestamp which a backup was picked from..... } finally { } } FileOutputStream fileOut = new FileOutputStream(filePath); wb.write(fileOut); if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } //if(incrementor==0){ // filePath="noreport"; //} //else{ //// url="no url to the report"; //} return filePath; }
From source file:reports.completed13Messages.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException, InvalidFormatException { session = request.getSession();//from w w w .j a v a2 s . co m dbConn conn = new dbConn(); position = 1; String reportHeader[] = ("COUNTY NAME ,PARTNER NAME,DISTRICT NAME, DIC NAME, GROUP NAME,CLIENT FULL NAME ," + " CCC NO. , MOBILE NUMBER , GENDER , DATE OF BIRTH , MARITAL STATUS , EMPLOYMENT STATUS ," + "EDUCATION LEVEL , ART STATUS , SERVICE PROVIDER NAME , HEALTH FACILITY, YEAR, MONTH,AGE BRACKET") .split(","); // COPY FILE TO BE WRITTEN TO Path original = Paths.get(getServletContext().getRealPath("/ALL_13_MESSAGES.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/ALL_13_MESSAGES_1.xlsm")); //new file System.out.println("origin : " + original + " destination : " + destination); try { Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING); System.out.println("file copied----------------"); } catch (IOException x) { //catch all for IO problems System.out.println("fine not copied"); } String allpath = getServletContext().getRealPath("/ALL_13_MESSAGES_1.xlsm"); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb; OPCPackage pkg = OPCPackage.open(allpath); wb = new XSSFWorkbook(pkg); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ // HSSFWorkbook wb=new HSSFWorkbook(); XSSFSheet shet1 = wb.getSheet("Sheet1"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); for (int i = 0; i <= reportHeader.length; i++) { shet1.setColumnWidth(i, 4000); } XSSFCellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); XSSFCell cell; XSSFRow rw0 = shet1.createRow(0); rw0.setHeightInPoints(30); rw0.setRowStyle(style2); for (int i = 0; i <= (reportHeader.length - 1); i++) { cell = rw0.createCell(i); cell.setCellValue(reportHeader[i]); cell.setCellStyle(stylex); } String getClients = "SELECT county.county_name,partner.partner_name,district.district_name,dic.dic_name," + "groups.group_name,personal_information.fname,personal_information.mname,personal_information.lname," + "personal_information.ccc_no,personal_information.mobile_no, personal_information.gender," + "personal_information.dob,marital_status.name,employment_status.name,education_levels.name," + "art_status.name," + "service_provider.fname,service_provider.mname,service_provider.lname,health_facility.hf_name, " + "CASE " + "when personal_information.completionmonth =01 THEN 'JAN' " + "when personal_information.completionmonth =02 THEN 'FEB' " + "when personal_information.completionmonth =03 THEN 'MAR' " + "when personal_information.completionmonth=04 THEN 'APR' " + "when personal_information.completionmonth=05 THEN 'MAY' " + "when personal_information.completionmonth=06 THEN 'JUN' " + "when personal_information.completionmonth=07 THEN 'JUL' " + "when personal_information.completionmonth=08 THEN 'AUG' " + "when personal_information.completionmonth=09 THEN 'SEPT' " + "when personal_information.completionmonth=10 THEN 'OCT' " + "when personal_information.completionmonth=11 THEN 'NOV'" + "when personal_information.completionmonth=12 THEN 'DEC'" + "END AS MONTHS,personal_information.completionyear," + "CASE" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above'" + " ELSE 'NO DATE OF BIRTH'" + " END AS AGEBRACKET," + "CASE " + "when personal_information.gender LIKE 'Female' THEN 'F' " + "when personal_information.gender LIKE 'Male' THEN 'M' " + "ELSE 'NO SEX' " + "END AS SEX " + " FROM personal_information " + " LEFT JOIN groups ON personal_information.group_id=groups.group_id " + " LEFT JOIN dic ON personal_information.dic_id=dic.dic_id " + " LEFT JOIN service_provider ON personal_information.provider_id=service_provider.provider_id " + " LEFT JOIN health_facility ON personal_information.hf_id=health_facility.hf_id " + " LEFT JOIN district ON personal_information.district_id=district.district_id " + " LEFT JOIN marital_status ON personal_information.marital_status=marital_status.id " + " LEFT JOIN employment_status ON personal_information.employment_status=employment_status.id " + " LEFT JOIN education_levels ON personal_information.education_level=education_levels.id " + " LEFT JOIN art_status ON personal_information.art_status=art_status.id " + " LEFT JOIN partner ON personal_information.partner_id=partner.partner_id " + " LEFT JOIN county ON district.county_id=county.county_id " + " WHERE personal_information.lessons_attended=13 " + " ORDER BY partner.partner_name,county.county_name,district.district_name,dic.dic_name," + "groups.group_name,personal_information.completionyear,MONTHS "; conn.rs = conn.st.executeQuery(getClients); while (conn.rs.next()) { // ADD THE DATA TO EXCEL HERE groupName = DICName = districtName = partnerName = countyName = agebracket = month = year = ""; clientFname = clientMname = clientLname = ccc_no = mobile_no = gender = dob = marital_status = ""; location = employment_status = education_level = under_18 = ovc_children = hiv_year = art_status = ""; registration_date = approved_by = designation = approval_date = ""; SPFname = SPMname = SPLname = SPFullName = healthFacility = ""; if (conn.rs.getString(1) != null) { countyName = conn.rs.getString(1); } if (conn.rs.getString(2) != null) { partnerName = conn.rs.getString(2); } if (conn.rs.getString(3) != null) { districtName = conn.rs.getString(3); } if (conn.rs.getString(4) != null) { DICName = conn.rs.getString(4); } else { DICName = "NO DIC"; } if (conn.rs.getString(5) != null) { groupName = conn.rs.getString(5); } else { groupName = "Individual"; } if (conn.rs.getString(6) != null) { clientFname = conn.rs.getString(6); } if (conn.rs.getString(7) != null) { clientMname = conn.rs.getString(7); } if (conn.rs.getString(8) != null) { clientLname = conn.rs.getString(8); } if (conn.rs.getString(9) != null) { ccc_no = conn.rs.getString(9); } if (conn.rs.getString(10) != null) { mobile_no = conn.rs.getString(10); } if (conn.rs.getString(11) != null) { gender = conn.rs.getString(11); } if (conn.rs.getString(12) != null) { dob = conn.rs.getString(12); } if (conn.rs.getString(13) != null) { marital_status = conn.rs.getString(13); } if (conn.rs.getString(14) != null) { employment_status = conn.rs.getString(14); } if (conn.rs.getString(15) != null) { education_level = conn.rs.getString(15); } if (conn.rs.getString(16) != null) { art_status = conn.rs.getString(16); } if (conn.rs.getString(17) != null) { SPFname = conn.rs.getString(17); } if (conn.rs.getString(18) != null) { SPMname = conn.rs.getString(18); } if (conn.rs.getString(19) != null) { SPLname = conn.rs.getString(19); } if (conn.rs.getString(20) != null) { healthFacility = conn.rs.getString(20); } if (conn.rs.getString(21) != null) { month = conn.rs.getString(21); } if (conn.rs.getString(22) != null) { year = conn.rs.getString(22); } if (conn.rs.getString(23) != null) { agebracket = conn.rs.getString(23); } if (conn.rs.getString(24) != null) { gender = conn.rs.getString(24); } if (clientMname.equals(clientLname)) { clientMname = ""; } if (SPMname.equals(SPLname)) { SPMname = ""; } SPFullName = SPFname + " " + SPMname + " " + SPLname; clientFullName = clientFname + " " + clientMname + " " + clientLname; String rawData[] = (countyName + "," + partnerName + "," + districtName + "," + DICName + "," + groupName + "," + clientFullName + "," + ccc_no + "," + mobile_no + "," + gender + "," + dob + "," + marital_status + "," + employment_status + "," + education_level + "," + art_status + "," + SPFullName + "," + healthFacility + "," + year + "," + month + "," + agebracket) .split(","); XSSFRow rw1 = shet1.createRow(position); rw1.setHeightInPoints(25); rw1.setRowStyle(style2); for (int i = 0; i <= (rawData.length - 1); i++) { cell = rw1.createCell(i); cell.setCellValue(rawData[i]); cell.setCellStyle(styleBorder); } position++; } IdGenerator CRT = new IdGenerator(); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_ATTENDED_13_SESSIONS_REPORT_CREATED_ON_" + CRT.timestamp() + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); }
From source file:reports.enrollmentReport.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException, InvalidFormatException { session = request.getSession();/*w w w . ja va 2 s. co m*/ dbConn conn = new dbConn(); int m1 = 0, m2 = 0; String d1 = "", d2 = "", y1 = "", y2 = ""; // pepfaryear=2015; pepfaryear = Integer.parseInt(request.getParameter("year")); prevYear = pepfaryear - 1; // COPY FILE TO BE WRITTEN TO Path original = Paths.get(getServletContext().getRealPath("/ENROLLMENT_TEMPLATE.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/ENROLLMENT_TEMPLATE_2.xlsm")); //new file System.out.println("origin : " + original + " destination : " + destination); try { Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING); System.out.println("file copied----------------"); } catch (IOException x) { //catch all for IO problems System.out.println("fine not copied"); } String allpath = getServletContext().getRealPath("/ENROLLMENT_TEMPLATE_2.xlsm"); XSSFWorkbook wb; OPCPackage pkg = OPCPackage.open(allpath); wb = new XSSFWorkbook(pkg); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFSheet shet1 = wb.getSheet("Sheet1"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 11); font.setFontName("Arial Black"); // font.setItalic(true); // font.setBoldweight((short)12); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(XSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); // font2.setFontHeightInPoints((short)15); font2.setFontName("Arial Black"); // font.setItalic(true); // font2.setBoldweight((short)18); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(XSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(XSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(XSSFCellStyle.BORDER_THIN); stborder.setBorderRight(XSSFCellStyle.BORDER_THIN); // stborder.setFillForegroundColor(HSSFColor.ORANGE.index); // stborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stborder.setAlignment(XSSFCellStyle.ALIGN_CENTER); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 6000); shet1.setColumnWidth(1, 6000); shet1.setColumnWidth(2, 6000); shet1.setColumnWidth(3, 6000); shet1.setColumnWidth(4, 6000); shet1.setColumnWidth(5, 7000); // shet1.setColumnWidth(20, 2000); XSSFCellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(XSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(XSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(XSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(XSSFCellStyle.BORDER_THIN); styleBorder.setFillForegroundColor(HSSFColor.ORANGE.index); styleBorder.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); styleBorder.setAlignment(XSSFCellStyle.ALIGN_CENTER); // CREATE HEADING 2 XSSFRow rheading2 = shet1.createRow(0); rheading2.setHeightInPoints(25); XSSFCell cellxx1, cellxx2, cellxx3, cellxx4, cellxx5, cellxx6, cellxx7, cellxx8; cellxx1 = rheading2.createCell(0); cellxx2 = rheading2.createCell(1); cellxx3 = rheading2.createCell(2); cellxx4 = rheading2.createCell(3); cellxx5 = rheading2.createCell(4); cellxx6 = rheading2.createCell(5); cellxx7 = rheading2.createCell(6); cellxx8 = rheading2.createCell(7); cellxx1.setCellValue("COUNTY NAME"); cellxx2.setCellValue("PARTNER NAME"); cellxx3.setCellValue("DISTRICT NAME"); cellxx4.setCellValue("YEAR"); cellxx5.setCellValue("MONTH"); cellxx6.setCellValue("TOTAL ENROLLED"); cellxx7.setCellValue("GENDER"); cellxx8.setCellValue("AGE BRACKET"); cellxx1.setCellStyle(styleBorder); cellxx2.setCellStyle(styleBorder); cellxx3.setCellStyle(styleBorder); cellxx4.setCellStyle(styleBorder); cellxx5.setCellStyle(styleBorder); cellxx6.setCellStyle(styleBorder); cellxx7.setCellStyle(styleBorder); cellxx8.setCellStyle(styleBorder); pos = 1; XSSFCellStyle stylex = wb.createCellStyle(); //stylex.setFillForegroundColor(HSSFColor.LIME.index); //stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(XSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(XSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(XSSFCellStyle.BORDER_THIN); stylex.setBorderRight(XSSFCellStyle.BORDER_THIN); stylex.setAlignment(XSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); String getData = "SELECT county.county_name,partner.partner_name,district.district_name, " + "extract(YEAR FROM registration_date) AS YEAR, " + "CASE " + "WHEN extract(MONTH FROM registration_date)=1 THEN 'JAN' " + "WHEN extract(MONTH FROM registration_date)=2 THEN 'FEB' " + "WHEN extract(MONTH FROM registration_date)=3 THEN 'MAR' " + "WHEN extract(MONTH FROM registration_date)=4 THEN 'APR' " + "WHEN extract(MONTH FROM registration_date)=5 THEN 'MAY' " + "WHEN extract(MONTH FROM registration_date)=6 THEN 'JUN' " + "WHEN extract(MONTH FROM registration_date)=7 THEN 'JUL' " + "WHEN extract(MONTH FROM registration_date)=8 THEN 'AUG' " + "WHEN extract(MONTH FROM registration_date)=9 THEN 'SEPT' " + "WHEN extract(MONTH FROM registration_date)=10 THEN 'OCT' " + "WHEN extract(MONTH FROM registration_date)=11 THEN 'NOV' " + "WHEN extract(MONTH FROM registration_date)=12 THEN 'DEC' " + "ELSE 'NO REGISTRATION DATE' " + "END AS MONTH " + ",COUNT(personal_information.client_id) AS REGISTERED," + "CASE " + "WHEN personal_information.gender ='Female' THEN 'F' " + "WHEN personal_information.gender ='Male' THEN 'M' " + "ELSE 'NO GENDER' " + "END AS GENDER, " + "CASE" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above'" + " ELSE 'NO DATE OF BIRTH'" + " END AS AGEBRACKET " + "FROM personal_information " + "JOIN partner ON partner.partner_id=personal_information.partner_id " + "JOIN district ON district.district_id=personal_information.district_id " + "JOIN county ON county.county_id=district.county_id WHERE registration_date BETWEEN '" + prevYear + "-10-01' AND '" + pepfaryear + "-09-30' " + "GROUP BY county.county_name,district.district_name,partner.partner_name,YEAR, MONTH,AGEBRACKET,personal_information.gender " + "ORDER BY county.county_name,district.district_name,partner.partner_name,YEAR, MONTH"; conn.rs = conn.st.executeQuery(getData); while (conn.rs.next()) { countyname = conn.rs.getString(1); partnername = conn.rs.getString(2); districtname = conn.rs.getString(3); year = conn.rs.getInt(4); month = conn.rs.getString(5); achieved = conn.rs.getInt(6); gender = conn.rs.getString(7); agebracket = conn.rs.getString(8); // CREATE ROW AND ADD DATA TO THE DATA CELLS====================== XSSFRow data = shet1.createRow(pos); data.setHeightInPoints(25); XSSFCell cellx1, cellx2, cellx3, cellx4, cellx5, cellx6, cellx7, cellx8; cellx1 = data.createCell(0); cellx2 = data.createCell(1); cellx3 = data.createCell(2); cellx4 = data.createCell(3); cellx5 = data.createCell(4); cellx6 = data.createCell(5); cellx7 = data.createCell(6); cellx8 = data.createCell(7); cellx1.setCellValue(countyname); cellx2.setCellValue(partnername); cellx3.setCellValue(districtname); cellx4.setCellValue(year); cellx5.setCellValue(month); cellx6.setCellValue(achieved); cellx7.setCellValue(gender); cellx8.setCellValue(agebracket); cellx1.setCellStyle(stylex); cellx2.setCellStyle(stylex); cellx3.setCellStyle(stylex); cellx4.setCellStyle(stylex); cellx5.setCellStyle(stylex); cellx6.setCellStyle(stylex); cellx7.setCellStyle(stylex); cellx8.setCellStyle(stylex); // System.out.println("county : "+countyname+" partner : "+partnername+" ahieved:"+achieved+" month: "+month+" quarter: "+quarter); pos++; } System.out.println("report generated : " + pos); // write it as an excel attachment IdGenerator IG = new IdGenerator(); if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } if (pos == 1) { session.setAttribute("noEnrollments", "<font color=\"red\">No enrollments found in pepfar year </font> <font color=\"black\"><b>" + pepfaryear + "</b></font>"); pkg.close(); response.sendRedirect("enrollments.jsp"); } else { ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_new_enrollment_report_created_on_" + IG.timestamp() + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); } }
From source file:reports.kePMS.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException, InvalidFormatException { session = request.getSession();//from www . j a v a 2s .c o m dbConn conn = new dbConn(); pos = 0; pepfaryear = 2015; // pepfaryear=Integer.parseInt(request.getParameter("year")); prevyear = pepfaryear - 1; String enddate = pepfaryear + "09"; String startdate = prevyear + "10"; start = Integer.parseInt(startdate); end = Integer.parseInt(enddate); System.out.println("start date : " + start + " end date : " + end); // COPY FILE TO BE WRITTEN TO Path original = Paths.get(getServletContext().getRealPath("/TEMPLATE.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/TEMPLATE_1.xlsm")); //new file System.out.println("origin : " + original + " destination : " + destination); try { Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING); System.out.println("file copied----------------"); } catch (IOException x) { //catch all for IO problems System.out.println("fine not copied"); } String allpath = getServletContext().getRealPath("/TEMPLATE_1.xlsm"); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb; OPCPackage pkg = OPCPackage.open(allpath); wb = new XSSFWorkbook(pkg); // HSSFWorkbook wb=new HSSFWorkbook(); XSSFSheet shet1 = wb.getSheet("sheet0"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 4000); shet1.setColumnWidth(1, 4000); shet1.setColumnWidth(2, 4000); shet1.setColumnWidth(3, 4000); shet1.setColumnWidth(4, 4000); XSSFRow rw4 = shet1.createRow(0); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); XSSFCell cell0, cell1, cell2, cell3, cell4; cell0 = rw4.createCell(0); cell1 = rw4.createCell(1); cell2 = rw4.createCell(2); cell3 = rw4.createCell(3); cell4 = rw4.createCell(4); cell0.setCellValue("PARTNER NAME"); cell1.setCellValue("AGE BRACKET"); cell2.setCellValue("GENDER"); cell3.setCellValue("MONTH"); cell4.setCellValue("ACHIEVED"); String getClients = "SELECT partner.partner_name," + "CASE " + "when clients.completionmonth =01 THEN '" + pepfaryear + "-01(JAN)' " + "when clients.completionmonth =02 THEN '" + pepfaryear + "-02 (FEB)' " + "when clients.completionmonth =03 THEN '" + pepfaryear + "-03 (MAR)' " + "when clients.completionmonth=04 THEN '" + pepfaryear + "-04 (APR)' " + "when clients.completionmonth=05 THEN '" + pepfaryear + "-05 (MAY)' " + "when clients.completionmonth=06 THEN '" + pepfaryear + "-06 (JUN)' " + "when clients.completionmonth=07 THEN '" + pepfaryear + "-07 (JUL)' " + "when clients.completionmonth=08 THEN '" + pepfaryear + "-08 (AUG)' " + "when clients.completionmonth=09 THEN '" + pepfaryear + "-09 (SEPT)' " + "when clients.completionmonth=10 THEN '" + prevyear + "-10 (OCT)' " + "when clients.completionmonth=11 THEN '" + prevyear + "-11 (NOV)'" + "when clients.completionmonth=12 THEn '" + prevyear + "-12 (DEC)'" + "END AS MONTHS,clients.completionyear," + "CASE" + " WHEN clients.age BETWEEN 0 AND 14 THEN '0-14'" + " WHEN clients.age BETWEEN 15 AND 19 THEN '15-19'" + " WHEN clients.age BETWEEN 20 AND 24 THEN '20-24'" + " WHEN clients.age >24 THEN '>25'" + " ELSE 'NOT SELECTED'" + " END AS AGEBRACKET," + "CASE " + "when clients.gender LIKE 'Female' THEN 'F' " + "when clients.gender LIKE 'Male' THEN 'M' " + "ELSE 'NO SEX' " + "END AS SEX" + ",completionmonth,COUNT(clients.client_id) FROM clients " + "JOIN partner ON clients.partner_id=partner.partner_id " + " WHERE clients.completionmonth>0 && clients.completionyear>0 GROUP BY partner.partner_name,SEX,MONTHS,AGEBRACKET ORDER BY clients.partner_id"; conn.rs = conn.st.executeQuery(getClients); while (conn.rs.next()) { partnername = clientid = gender = ""; age = 0; partnername = conn.rs.getString(1); month = conn.rs.getString(2); year = conn.rs.getInt(3); agebracket = conn.rs.getString(4); gender = conn.rs.getString(5); String dkey = year + "" + conn.rs.getString(6); datekey = Integer.parseInt(dkey); achieved = conn.rs.getInt(7); if (datekey >= start && datekey <= end && year >= 2014) { pos++; XSSFRow rw4x = shet1.createRow(pos); rw4x.setHeightInPoints(25); rw4x.setRowStyle(style2); XSSFCell cell0x, cell1x, cell2x, cell3x, cell4x; cell0x = rw4x.createCell(0); cell1x = rw4x.createCell(1); cell2x = rw4x.createCell(2); cell3x = rw4x.createCell(3); cell4x = rw4x.createCell(4); // OUTPUT SERVICES PROVIDED================================ cell0x.setCellValue(partnername); cell1x.setCellValue(agebracket); cell2x.setCellValue(gender); cell3x.setCellValue(month); cell4x.setCellValue(achieved); System.out.println(pos + "-----partner :" + partnername + " age bracket :" + agebracket + " gender :" + gender + " completion month : " + month); } } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_kePMS_REPORT_FOR_PEPFAR_YEAR_" + pepfaryear + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); }
From source file:reports.kePMSCounty.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException, InvalidFormatException { session = request.getSession();/*from www. ja v a2s .c om*/ dbConn conn = new dbConn(); pos = 0; // pepfaryear=2015; pepfaryear = Integer.parseInt(session.getAttribute("PepfarYear").toString()); prevyear = pepfaryear - 1; String enddate = pepfaryear + "09"; String startdate = prevyear + "10"; start = Integer.parseInt(startdate); end = Integer.parseInt(enddate); System.out.println("start date : " + start + " end date : " + end); // COPY FILE TO BE WRITTEN TO Path original = Paths.get(getServletContext().getRealPath("/perCounty.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/perCounty_1.xlsm")); //new file System.out.println("origin : " + original + " destination : " + destination); try { Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING); System.out.println("file copied----------------"); } catch (IOException x) { //catch all for IO problems System.out.println("fine not copied"); } String allpath = getServletContext().getRealPath("/perCounty_1.xlsm"); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb; OPCPackage pkg = OPCPackage.open(allpath); wb = new XSSFWorkbook(pkg); // HSSFWorkbook wb=new HSSFWorkbook(); XSSFSheet shet1 = wb.getSheet("sheet1"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 4000); shet1.setColumnWidth(1, 4000); shet1.setColumnWidth(2, 4000); shet1.setColumnWidth(3, 4000); shet1.setColumnWidth(4, 4000); XSSFRow rw4 = shet1.createRow(0); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); XSSFCell cell0, cell1, cell2, cell3, cell4, cell5, cell6; cell0 = rw4.createCell(0); cell1 = rw4.createCell(1); cell2 = rw4.createCell(2); cell3 = rw4.createCell(3); cell4 = rw4.createCell(4); cell5 = rw4.createCell(5); cell6 = rw4.createCell(6); cell0.setCellValue("DISTRICT NAME"); cell1.setCellValue("AGE BRACKET"); cell2.setCellValue("GENDER"); cell3.setCellValue("MONTH"); cell4.setCellValue("ACHIEVED"); cell5.setCellValue("COUNTY"); cell6.setCellValue(""); String getClients2 = "SELECT county.county_name,district.district_name," + "CASE " + "when personal_information.completionmonth =01 THEN '" + pepfaryear + "-01(JAN)' " + "when personal_information.completionmonth =02 THEN '" + pepfaryear + "-02 (FEB)' " + "when personal_information.completionmonth =03 THEN '" + pepfaryear + "-03 (MAR)' " + "when personal_information.completionmonth=04 THEN '" + pepfaryear + "-04 (APR)' " + "when personal_information.completionmonth=05 THEN '" + pepfaryear + "-05 (MAY)' " + "when personal_information.completionmonth=06 THEN '" + pepfaryear + "-06 (JUN)' " + "when personal_information.completionmonth=07 THEN '" + pepfaryear + "-07 (JUL)' " + "when personal_information.completionmonth=08 THEN '" + pepfaryear + "-08 (AUG)' " + "when personal_information.completionmonth=09 THEN '" + pepfaryear + "-09 (SEPT)' " + "when personal_information.completionmonth=10 THEN '" + prevyear + "-10 (OCT)' " + "when personal_information.completionmonth=11 THEN '" + prevyear + "-11 (NOV)'" + "when personal_information.completionmonth=12 THEN '" + prevyear + "-12 (DEC)'" + "END AS MONTHS,personal_information.completionyear,personal_information.completionmonth," + "personal_information.client_id,personal_information.gender FROM personal_information " + " JOIN district ON district.district_id=personal_information.district_id " + "JOIN county ON county.county_id=district.county_id " + " WHERE personal_information.completionmonth>0 && personal_information.completionyear>0"; conn.rs = conn.st.executeQuery(getClients2); while (conn.rs.next()) { county = conn.rs.getString(1); district = conn.rs.getString(2); month = conn.rs.getString(3); year = conn.rs.getInt(4); gender = conn.rs.getString(7); if (gender.equalsIgnoreCase("female")) { gender = "F"; } else { gender = "M"; } String dkey = year + "" + conn.rs.getString(5); datekey = Integer.parseInt(dkey); // achieved=conn.rs.getInt(6); if (datekey >= start && datekey <= end && year >= 2014) { System.out.println("date key : " + datekey); pos++; XSSFRow rw4x = shet1.createRow(pos); rw4x.setHeightInPoints(25); rw4x.setRowStyle(style2); XSSFCell cell0x, cell1x, cell2x, cell3x, cell4x, cell5x, cell6x; cell0x = rw4x.createCell(0); cell1x = rw4x.createCell(1); cell2x = rw4x.createCell(2); cell3x = rw4x.createCell(3); cell4x = rw4x.createCell(4); cell5x = rw4x.createCell(5); cell6x = rw4x.createCell(6); // OUTPUT SERVICES PROVIDED================================ cell0x.setCellValue(district); cell1x.setCellValue(agebracket); cell2x.setCellValue(gender); cell3x.setCellValue(month); cell4x.setCellValue(conn.rs.getString(6)); cell5x.setCellValue(county); cell6x.setCellValue(""); } } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } IdGenerator IGR = new IdGenerator(); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_kePMS_REPORT_PER_COUNTY_FOR_PEPFAR_YEAR_" + pepfaryear + "_CREATED_ON_" + IGR.timestamp() + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); }
From source file:reports.kePMSDIC.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException, InvalidFormatException { session = request.getSession();/*w ww . j a v a2 s .c o m*/ dbConn conn = new dbConn(); incrementor = 0; partner_id = session.getAttribute("partnerDIC").toString(); pos = 0; pepfaryear = Integer.parseInt(session.getAttribute("PepfarYear").toString()); // pepfaryear=Integer.parseInt(request.getParameter("year")); prevyear = pepfaryear - 1; String enddate = pepfaryear + "09"; String startdate = prevyear + "10"; start = Integer.parseInt(startdate); end = Integer.parseInt(enddate); System.out.println(" partner id : " + partner_id + " pepfaryear : " + pepfaryear); Path original = Paths.get(getServletContext().getRealPath("/AchievedDIC.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/AchievedDIC_1.xlsm")); //new file System.out.println("origin : " + original + " destination : " + destination); try { Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING); System.out.println("file copied----------------"); } catch (IOException x) { //catch all for IO problems System.out.println("fine not copied"); } String allpath = getServletContext().getRealPath("/AchievedDIC_1.xlsm"); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb; OPCPackage pkg = OPCPackage.open(allpath); wb = new XSSFWorkbook(pkg); // HSSFWorkbook wb=new HSSFWorkbook(); XSSFSheet shet1 = wb.getSheet("sheet1"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 4000); shet1.setColumnWidth(1, 4000); shet1.setColumnWidth(2, 4000); shet1.setColumnWidth(3, 4000); shet1.setColumnWidth(4, 4000); XSSFRow rw4 = shet1.createRow(0); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); XSSFCell cell0, cell1, cell2, cell3, cell4; cell0 = rw4.createCell(0); cell1 = rw4.createCell(1); cell2 = rw4.createCell(2); cell3 = rw4.createCell(3); cell4 = rw4.createCell(4); cell0.setCellValue("DIC NAME"); cell1.setCellValue("AGE BRACKET"); cell2.setCellValue("GENDER"); cell3.setCellValue("MONTH"); cell4.setCellValue("ACHIEVED"); String getData = "SELECT count(personal_information.client_id),dic.dic_name," + "CASE " + "when personal_information.completionmonth =01 THEN '" + pepfaryear + "-01(JAN)' " + "when personal_information.completionmonth =02 THEN '" + pepfaryear + "-02 (FEB)' " + "when personal_information.completionmonth =03 THEN '" + pepfaryear + "-03 (MAR)' " + "when personal_information.completionmonth=04 THEN '" + pepfaryear + "-04 (APR)' " + "when personal_information.completionmonth=05 THEN '" + pepfaryear + "-05 (MAY)' " + "when personal_information.completionmonth=06 THEN '" + pepfaryear + "-06 (JUN)' " + "when personal_information.completionmonth=07 THEN '" + pepfaryear + "-07 (JUL)' " + "when personal_information.completionmonth=08 THEN '" + pepfaryear + "-08 (AUG)' " + "when personal_information.completionmonth=09 THEN '" + pepfaryear + "-09 (SEPT)' " + "when personal_information.completionmonth=10 THEN '" + prevyear + "-10 (OCT)' " + "when personal_information.completionmonth=11 THEN '" + prevyear + "-11 (NOV)'" + "when personal_information.completionmonth=12 THEN '" + prevyear + "-12 (DEC)'" + "END AS MONTHS," + "CASE" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above'" + " ELSE 'NO DATE OF BIRTH'" + " END AS AGEBRACKET," + "CASE " + "when personal_information.gender LIKE 'Female' THEN 'F' " + "when personal_information.gender LIKE 'Male' THEN 'M' " + "ELSE 'NO SEX' " + "END AS SEX,personal_information.completionyear,personal_information.completionmonth" + " FROM personal_information " + "LEFT JOIN dic ON dic.dic_id=personal_information.dic_id" + " WHERE personal_information.partner_id='" + partner_id + "' " + "GROUP BY dic.dic_name,SEX,AGEBRACKET,personal_information.completionyear,MONTHS ORDER BY dic.dic_name"; conn.rs = conn.st.executeQuery(getData); while (conn.rs.next()) { dic_name = age = gender = county = ""; achieved = 0; achieved = conn.rs.getInt(1); incrementor += achieved; dic_name = conn.rs.getString(2); if (dic_name == null) { dic_name = "NO DIC"; } month = conn.rs.getString(3); age = conn.rs.getString(4); gender = conn.rs.getString(5); String dkey = conn.rs.getString(6) + "" + conn.rs.getString(7); datekey = Integer.parseInt(dkey); System.out.println("date key : " + datekey + " start : " + start + " end : " + end); if (datekey >= start && datekey <= end) { pos++; XSSFRow rw4x = shet1.createRow(pos); rw4x.setHeightInPoints(25); rw4x.setRowStyle(style2); XSSFCell cell0x, cell1x, cell2x, cell3x, cell4x; cell0x = rw4x.createCell(0); cell1x = rw4x.createCell(1); cell2x = rw4x.createCell(2); cell3x = rw4x.createCell(3); cell4x = rw4x.createCell(4); // OUTPUT SERVICES PROVIDED================================ cell0x.setCellValue(dic_name); cell1x.setCellValue(age); cell2x.setCellValue(gender); cell3x.setCellValue(month); cell4x.setCellValue(achieved); System.out.println("entered to fetch data============="); } } String getPartner = "SELECT partner_name FROM partner WHERE partner_id='" + partner_id + "'"; conn.rs = conn.st.executeQuery(getPartner); if (conn.rs.next() == true) { partner_name = conn.rs.getString(1).trim().replace(" ", "_"); } session.removeAttribute("PepfarYear"); session.removeAttribute("partnerDIC"); if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } System.out.println("===============END IS HERE=============="); if (incrementor > 0) { IdGenerator CRT = new IdGenerator(); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_kePMS_REPORT_FOR_PEPFAR_YEAR_" + pepfaryear + "_PER_DIC_FOR_" + partner_name + "_CREATED_ON_" + CRT.timestamp() + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); } else { session.setAttribute("kePMSError", "<font color=\"red\"><b>NO DATA WITHIN THE SELECTED PARAMETERS.</b></font>"); response.sendRedirect("kePMS.jsp"); } }
From source file:reports.kePMSDICServices.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException, InvalidFormatException { session = request.getSession();/*from w ww. j a va 2s .c o m*/ dbConn conn = new dbConn(); incrementor = 0; pos = 0; partner_id = session.getAttribute("partnerDIC").toString(); pepfaryear = Integer.parseInt(session.getAttribute("PepfarYear").toString()); prevyear = pepfaryear - 1; String[] periods = session.getAttribute("period").toString().split("-"); if (session.getAttribute("period").toString().equals("10-12")) { period = "OCT-DEC"; } else if (session.getAttribute("period").toString().equals("01-03")) { period = "JAN-MARCH"; } else if (session.getAttribute("period").toString().equals("04-06")) { period = "APRIL-JUNE"; } else if (session.getAttribute("period").toString().equals("07-09")) { period = "JULY-SEPT"; } else { } startdate = periods[0]; enddate = periods[1]; if (session.getAttribute("period").toString().equals("10-12")) { pepfaryear = pepfaryear - 1; } start = Integer.parseInt(startdate); end = Integer.parseInt(enddate); Path original = Paths.get(getServletContext().getRealPath("/ServicesDIC.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/ServicesDIC_1.xlsm")); //new file System.out.println("origin : " + original + " destination : " + destination); try { Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING); System.out.println("file copied----------------"); } catch (IOException x) { //catch all for IO problems System.out.println("fine not copied"); } String allpath = getServletContext().getRealPath("/ServicesDIC_1.xlsm"); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb; OPCPackage pkg = OPCPackage.open(allpath); wb = new XSSFWorkbook(pkg); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFSheet shet1 = wb.getSheet("sheet1"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); // font2.setFontHeightInPoints((short)15); font2.setFontName("Arial Black"); // font.setItalic(true); // font2.setBoldweight((short)18); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); // stborder.setFillForegroundColor(HSSFColor.ORANGE.index); // stborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 6000); shet1.setColumnWidth(1, 6000); shet1.setColumnWidth(2, 6000); shet1.setColumnWidth(3, 6000); shet1.setColumnWidth(4, 6000); shet1.setColumnWidth(5, 6000); shet1.setColumnWidth(6, 6000); shet1.setColumnWidth(7, 6000); shet1.setColumnWidth(8, 6000); shet1.setColumnWidth(9, 6000); // shet1.setColumnWidth(20, 2000); XSSFCellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorder.setFillForegroundColor(HSSFColor.ORANGE.index); styleBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // CREATE HEADING 2 XSSFRow rheading2 = shet1.createRow(0); rheading2.setHeightInPoints(25); XSSFCell cellxx1, cellxx2, cellxx3, cellxx4, cellxx5, cellxx6, cellxx7, cellxx8, cellxx9, cellxx10, cellxx11; cellxx1 = rheading2.createCell(0); cellxx2 = rheading2.createCell(1); cellxx3 = rheading2.createCell(2); cellxx4 = rheading2.createCell(3); cellxx5 = rheading2.createCell(4); cellxx6 = rheading2.createCell(5); cellxx7 = rheading2.createCell(6); cellxx8 = rheading2.createCell(7); cellxx9 = rheading2.createCell(8); cellxx10 = rheading2.createCell(9); cellxx11 = rheading2.createCell(10); cellxx1.setCellValue("DIC NAME"); cellxx2.setCellValue("GENDER"); cellxx3.setCellValue("CONTRACEPTIVE METHOD"); cellxx4.setCellValue("REFERRED TO A SERVICE POINT"); cellxx5.setCellValue("GIVEN CONDOMS"); cellxx6.setCellValue("SCREENED FOR TB"); cellxx7.setCellValue("SCREENED FOR STIS"); cellxx8.setCellValue("TESTED PARTNER"); cellxx9.setCellValue("TESTED CHILDREN"); cellxx10.setCellValue("DISCLOSED STATUS"); cellxx11.setCellValue("AGE BRACKET"); cellxx1.setCellStyle(styleBorder); cellxx2.setCellStyle(styleBorder); cellxx3.setCellStyle(styleBorder); cellxx4.setCellStyle(styleBorder); cellxx5.setCellStyle(styleBorder); cellxx6.setCellStyle(styleBorder); cellxx7.setCellStyle(styleBorder); cellxx8.setCellStyle(styleBorder); cellxx9.setCellStyle(styleBorder); cellxx10.setCellStyle(styleBorder); cellxx11.setCellStyle(styleBorder); pos = 1; XSSFCellStyle stylex = wb.createCellStyle(); //stylex.setFillForegroundColor(HSSFColor.LIME.index); //stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); String getServices = "SELECT client_id,DIC,GENDER, bit_or(cm) AS CONTRACEPTIVE_METHOD,bit_or(sp) AS REFERRED_TO_SERVICE_POINT, " + "SUM(cg) AS CONDOMS_GIVEN ,bit_or(st) AS SCREENED_TB ,bit_or(ss) AS SCREENED_STIS,bit_or(tp) TESTED_PARTNER, " + "bit_or(tc) AS TESTED_CHILDREN,bit_or(ds) as DISCLOSED_STATUS,year AS pepfaryear,month as pepfarmonth,AGEBRACKET FROM ( " + "SELECT personal_information.client_id as client_id,dic.dic_name as DIC,personal_information.gender as GENDER, " + " CASE " + " WHEN services_provided.contraceptive_method= 'YES' THEN 1 " + " WHEN services_provided.contraceptive_method= 'NO' THEN 0 " + "ELSE 'NONE' " + "END AS cm, " + "CASE " + " WHEN services_provided.rsp LIKE 'YES' THEN 1 " + " WHEN services_provided.rsp LIKE 'NO' THEN 0 " + "ELSE 'NONE' " + "END AS sp, " + " services_provided.cds_given AS cg, " + "CASE " + " WHEN services_provided.screened_tb='YES' THEN 1 " + " WHEN services_provided.screened_tb='NO' THEN 0 " + "ELSE 'NONE' " + "END AS st, " + "CASE " + " WHEN services_provided.screened_stis='YES' THEN 1 " + " WHEN services_provided.screened_stis='NO' THEN 0 " + "ELSE 'NONE' " + "END AS ss, " + "CASE " + " WHEN services_provided.tested_partner='YES' THEN 1 " + " WHEN services_provided.tested_partner='NO' THEN 0 " + "ELSE 'NONE' " + "END AS tp, " + "CASE " + " WHEN services_provided.tested_children= 'YES' THEN 1 " + " WHEN services_provided.tested_children= 'NO' THEN 0 " + "ELSE 'NONE' " + "END AS tc, " + "CASE " + " WHEN services_provided.disclosed_status= 'YES' THEN 1 " + " WHEN services_provided.disclosed_status= 'NO' THEN 0 " + "ELSE 'NONE' " + "END AS ds,services_provided.submission_month AS month,services_provided.submission_year as year," + "CASE" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above'" + " ELSE 'NO DATE OF BIRTH' " + " END AS AGEBRACKET " + "FROM personal_information LEFT JOIN dic ON dic.dic_id=personal_information.dic_id " + " JOIN services_provided ON services_provided.client_id=personal_information.client_id" + " WHERE personal_information.partner_id='" + partner_id + "' && services_provided.submission_month>='" + startdate + "' && services_provided.submission_month<='" + enddate + "' " + "&& services_provided.submission_year='" + pepfaryear + "' order by personal_information.client_id ) as temptbl" + " WHERE (cm>0 || sp>0 || cg>0 || st>0 || ss>0" + " || tp>0 || tc>0 || ds>0) GROUP BY client_id ORDER BY client_id"; conn.rs = conn.st.executeQuery(getServices); while (conn.rs.next()) { dicname = conn.rs.getString(2); if (dicname == null) { dicname = "NO DIC"; } gender = conn.rs.getString(3); contraceptive_method = conn.rs.getInt(4); rsp = conn.rs.getInt(5); cds_given = conn.rs.getInt(6); screened_tb = conn.rs.getInt(7); screened_stis = conn.rs.getInt(8); tested_partner = conn.rs.getInt(9); tested_children = conn.rs.getInt(10); disclosed_status = conn.rs.getInt(11); datekey = Integer.parseInt(conn.rs.getInt(12) + "" + conn.rs.getInt(13)); agebracket = conn.rs.getString(14); if (contraceptive_method > 0 || rsp > 0 || cds_given > 0 || screened_tb > 0 || screened_stis > 0 || tested_partner > 0 || tested_children > 0 || disclosed_status > 0) { // CREATE ROW AND ADD DATA TO THE DATA CELLS====================== incrementor++; XSSFRow data = shet1.createRow(pos); data.setHeightInPoints(25); XSSFCell cellx1, cellx2, cellx3, cellx4, cellx5, cellx6, cellx7, cellx8, cellx9, cellx10, cellx11; cellx1 = data.createCell(0); cellx2 = data.createCell(1); cellx3 = data.createCell(2); cellx4 = data.createCell(3); cellx5 = data.createCell(4); cellx6 = data.createCell(5); cellx7 = data.createCell(6); cellx8 = data.createCell(7); cellx9 = data.createCell(8); cellx10 = data.createCell(9); cellx11 = data.createCell(10); cellx1.setCellValue(dicname); cellx2.setCellValue(gender); cellx3.setCellValue(contraceptive_method); cellx4.setCellValue(rsp); cellx5.setCellValue(cds_given); cellx6.setCellValue(screened_tb); cellx7.setCellValue(screened_stis); cellx8.setCellValue(tested_partner); cellx9.setCellValue(tested_children); cellx10.setCellValue(disclosed_status); cellx11.setCellValue(agebracket); cellx1.setCellStyle(stylex); cellx2.setCellStyle(stylex); cellx3.setCellStyle(stylex); cellx4.setCellStyle(stylex); cellx5.setCellStyle(stylex); cellx6.setCellStyle(stylex); cellx7.setCellStyle(stylex); cellx8.setCellStyle(stylex); cellx9.setCellStyle(stylex); cellx10.setCellStyle(stylex); cellx11.setCellStyle(stylex); pos++; } } String getPartner = "SELECT partner_name FROM partner WHERE partner_id='" + partner_id + "'"; conn.rs = conn.st.executeQuery(getPartner); if (conn.rs.next() == true) { partner_name = conn.rs.getString(1).trim().replace(" ", "_"); } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } if (incrementor > 0) { if (session.getAttribute("period").toString().equals("10-12")) { pepfaryear++; } // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_SERVICES_PROVIDED_PER_DIC_REPORT_FOR_pepfar_year_" + pepfaryear + "(" + period + ")_AND_PARTNER_" + partner_name + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); } else { session.setAttribute("kePMSError", "<font color=\"red\"><b>NO DATA WITHIN THE SELECTED PARAMETERS.</b></font>"); response.sendRedirect("kePMS.jsp"); } }
From source file:reports.kePMSGroup.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, InvalidFormatException, SQLException { session = request.getSession();/* w ww . j a v a 2s . com*/ dbConn conn = new dbConn(); incrementor = 0; partner_id = session.getAttribute("partnerDIC").toString(); pos = 0; pepfaryear = Integer.parseInt(session.getAttribute("PepfarYear").toString()); prevyear = pepfaryear - 1; String enddate = pepfaryear + "09"; String startdate = prevyear + "10"; start = Integer.parseInt(startdate); end = Integer.parseInt(enddate); System.out.println(" partner id : " + partner_id + " pepfaryear : " + pepfaryear); Path original = Paths.get(getServletContext().getRealPath("/AchievedGroup.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/AchievedGroup_1.xlsm")); //new file System.out.println("origin : " + original + " destination : " + destination); try { Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING); System.out.println("file copied----------------"); } catch (IOException x) { //catch all for IO problems System.out.println("fine not copied"); } String allpath = getServletContext().getRealPath("/AchievedGroup_1.xlsm"); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb; OPCPackage pkg = OPCPackage.open(allpath); wb = new XSSFWorkbook(pkg); // HSSFWorkbook wb=new HSSFWorkbook(); XSSFSheet shet1 = wb.getSheet("sheet1"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 4000); shet1.setColumnWidth(1, 4000); shet1.setColumnWidth(2, 4000); shet1.setColumnWidth(3, 4000); shet1.setColumnWidth(4, 4000); XSSFRow rw4 = shet1.createRow(0); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); XSSFCell cell0, cell1, cell2, cell3, cell4; cell0 = rw4.createCell(0); cell1 = rw4.createCell(1); cell2 = rw4.createCell(2); cell3 = rw4.createCell(3); cell4 = rw4.createCell(4); cell0.setCellValue("GROUP NAME"); cell1.setCellValue("AGE BRACKET"); cell2.setCellValue("GENDER"); cell3.setCellValue("MONTH"); cell4.setCellValue("ACHIEVED"); String getData = "SELECT count(personal_information.client_id),groups.group_name," + "CASE " + "when personal_information.completionmonth =01 THEN '" + pepfaryear + "-01(JAN)' " + "when personal_information.completionmonth =02 THEN '" + pepfaryear + "-02 (FEB)' " + "when personal_information.completionmonth =03 THEN '" + pepfaryear + "-03 (MAR)' " + "when personal_information.completionmonth=04 THEN '" + pepfaryear + "-04 (APR)' " + "when personal_information.completionmonth=05 THEN '" + pepfaryear + "-05 (MAY)' " + "when personal_information.completionmonth=06 THEN '" + pepfaryear + "-06 (JUN)' " + "when personal_information.completionmonth=07 THEN '" + pepfaryear + "-07 (JUL)' " + "when personal_information.completionmonth=08 THEN '" + pepfaryear + "-08 (AUG)' " + "when personal_information.completionmonth=09 THEN '" + pepfaryear + "-09 (SEPT)' " + "when personal_information.completionmonth=10 THEN '" + prevyear + "-10 (OCT)' " + "when personal_information.completionmonth=11 THEN '" + prevyear + "-11 (NOV)'" + "when personal_information.completionmonth=12 THEn '" + prevyear + "-12 (DEC)'" + "END AS MONTHS," + "CASE" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above'" + " ELSE 'NO DATE OF BIRTH'" + " END AS AGEBRACKET," + "CASE " + "when personal_information.gender LIKE 'Female' THEN 'F' " + "when personal_information.gender LIKE 'Male' THEN 'M' " + "ELSE 'NO SEX' " + "END AS SEX,personal_information.completionyear,personal_information.completionmonth" + " FROM personal_information " + "JOIN groups ON groups.group_id=personal_information.group_id" + " WHERE personal_information.group_id!='0' && personal_information.partner_id='" + partner_id + "' " + "GROUP BY groups.group_name,SEX,AGEBRACKET,personal_information.completionyear,MONTHS ORDER BY groups.group_name"; conn.rs = conn.st.executeQuery(getData); while (conn.rs.next()) { month = age = gender = group_name = ""; achieved = 0; achieved = conn.rs.getInt(1); group_name = conn.rs.getString(2); month = conn.rs.getString(3); age = conn.rs.getString(4); gender = conn.rs.getString(5); String dkey = conn.rs.getString(6) + "" + conn.rs.getString(7); datekey = Integer.parseInt(dkey); incrementor += achieved; System.out.println("date key : " + datekey + " start : " + start + " end : " + end); if (datekey >= start && datekey <= end) { pos++; XSSFRow rw4x = shet1.createRow(pos); rw4x.setHeightInPoints(25); rw4x.setRowStyle(style2); XSSFCell cell0x, cell1x, cell2x, cell3x, cell4x; cell0x = rw4x.createCell(0); cell1x = rw4x.createCell(1); cell2x = rw4x.createCell(2); cell3x = rw4x.createCell(3); cell4x = rw4x.createCell(4); // OUTPUT SERVICES PROVIDED================================ cell0x.setCellValue(group_name); cell1x.setCellValue(age); cell2x.setCellValue(gender); cell3x.setCellValue(month); cell4x.setCellValue(achieved); System.out.println("entered to fetch data=============" + achieved); } } String getDataIndividual = "SELECT count(personal_information.client_id)," + "CASE " + "when personal_information.completionmonth =01 THEN '" + pepfaryear + "-01(JAN)' " + "when personal_information.completionmonth =02 THEN '" + pepfaryear + "-02 (FEB)' " + "when personal_information.completionmonth =03 THEN '" + pepfaryear + "-03 (MAR)' " + "when personal_information.completionmonth=04 THEN '" + pepfaryear + "-04 (APR)' " + "when personal_information.completionmonth=05 THEN '" + pepfaryear + "-05 (MAY)' " + "when personal_information.completionmonth=06 THEN '" + pepfaryear + "-06 (JUN)' " + "when personal_information.completionmonth=07 THEN '" + pepfaryear + "-07 (JUL)' " + "when personal_information.completionmonth=08 THEN '" + pepfaryear + "-08 (AUG)' " + "when personal_information.completionmonth=09 THEN '" + pepfaryear + "-09 (SEPT)' " + "when personal_information.completionmonth=10 THEN '" + prevyear + "-10 (OCT)' " + "when personal_information.completionmonth=11 THEN '" + prevyear + "-11 (NOV)'" + "when personal_information.completionmonth=12 THEn '" + prevyear + "-12 (DEC)'" + "END AS MONTHS," + "CASE" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 14 THEN '0-14'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >24 THEN '>25'" + " ELSE 'NO DATE OF BIRTH'" + " END AS AGEBRACKET," + "CASE " + "when personal_information.gender LIKE 'Female' THEN 'F' " + "when personal_information.gender LIKE 'Male' THEN 'M' " + "ELSE 'NO SEX' " + "END AS SEX,personal_information.completionyear,personal_information.completionmonth" + " FROM personal_information " + " WHERE personal_information.group_id='0' && personal_information.partner_id='" + partner_id + "' " + "GROUP BY SEX,AGEBRACKET,personal_information.completionyear,MONTHS "; conn.rs = conn.st.executeQuery(getDataIndividual); while (conn.rs.next()) { month = age = gender = group_name = ""; achieved = 0; achieved = conn.rs.getInt(1); incrementor += achieved; group_name = "INDIVIDUAL"; month = conn.rs.getString(2); age = conn.rs.getString(3); gender = conn.rs.getString(4); String dkey = conn.rs.getString(5) + "" + conn.rs.getString(6); datekey = Integer.parseInt(dkey); System.out.println("date key : " + datekey + " start : " + start + " end : " + end); if (datekey >= start && datekey <= end) { pos++; XSSFRow rw4x = shet1.createRow(pos); rw4x.setHeightInPoints(25); rw4x.setRowStyle(style2); XSSFCell cell0x, cell1x, cell2x, cell3x, cell4x; cell0x = rw4x.createCell(0); cell1x = rw4x.createCell(1); cell2x = rw4x.createCell(2); cell3x = rw4x.createCell(3); cell4x = rw4x.createCell(4); // OUTPUT SERVICES PROVIDED================================ cell0x.setCellValue(group_name); cell1x.setCellValue(age); cell2x.setCellValue(gender); cell3x.setCellValue(month); cell4x.setCellValue(achieved); System.out.println("entered to fetch data=============" + achieved); } } String getPartner = "SELECT partner_name FROM partner WHERE partner_id='" + partner_id + "'"; conn.rs = conn.st.executeQuery(getPartner); if (conn.rs.next() == true) { partner_name = conn.rs.getString(1).trim().replace(" ", "_"); } session.removeAttribute("PepfarYear"); session.removeAttribute("partnerDIC"); System.out.println("incrementer : " + incrementor); if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } if (incrementor > 0) { System.out.println("===============END IS HERE=============="); IdGenerator CRT = new IdGenerator(); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_kePMS_REPORT_FOR_PEPFAR_YEAR_" + pepfaryear + "_PER_GROUP_FOR_" + partner_name + "_CREATED_ON_" + CRT.timestamp() + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); } else { session.setAttribute("kePMSError", "<font color=\"red\"><b>NO DATA WITHIN THE SELECTED PARAMETERS.</b></font>"); response.sendRedirect("kePMS.jsp"); } }
From source file:reports.kePMSGroupServices.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException, InvalidFormatException { session = request.getSession();//from ww w. ja va2s .c o m dbConn conn = new dbConn(); incrementor = 0; pos = 0; partner_id = session.getAttribute("partnerDIC").toString(); pepfaryear = Integer.parseInt(session.getAttribute("PepfarYear").toString()); prevyear = pepfaryear - 1; String[] periods = session.getAttribute("period").toString().split("-"); if (session.getAttribute("period").toString().equals("10-12")) { period = "OCT-DEC"; } else if (session.getAttribute("period").toString().equals("01-03")) { period = "JAN-MARCH"; } else if (session.getAttribute("period").toString().equals("04-06")) { period = "APRIL-JUNE"; } else if (session.getAttribute("period").toString().equals("07-09")) { period = "JULY-SEPT"; } else { } startdate = periods[0]; enddate = periods[1]; if (session.getAttribute("period").toString().equals("10-12")) { pepfaryear = pepfaryear - 1; } start = Integer.parseInt(startdate); end = Integer.parseInt(enddate); System.out.println("partner id : " + partner_id + " year : " + pepfaryear); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ Path original = Paths.get(getServletContext().getRealPath("/ServicesGroup.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/ServicesGroup_1.xlsm")); //new file System.out.println("origin : " + original + " destination : " + destination); try { Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING); System.out.println("file copied----------------"); } catch (IOException x) { //catch all for IO problems System.out.println("fine not copied"); } String allpath = getServletContext().getRealPath("/ServicesGroup_1.xlsm"); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb; OPCPackage pkg = OPCPackage.open(allpath); wb = new XSSFWorkbook(pkg); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFSheet shet1 = wb.getSheet("sheet1"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); // font2.setFontHeightInPoints((short)15); font2.setFontName("Arial Black"); // font.setItalic(true); // font2.setBoldweight((short)18); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); // stborder.setFillForegroundColor(HSSFColor.ORANGE.index); // stborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 6000); shet1.setColumnWidth(1, 6000); shet1.setColumnWidth(2, 6000); shet1.setColumnWidth(3, 6000); shet1.setColumnWidth(4, 6000); shet1.setColumnWidth(5, 6000); shet1.setColumnWidth(6, 6000); shet1.setColumnWidth(7, 6000); shet1.setColumnWidth(8, 6000); shet1.setColumnWidth(9, 6000); shet1.setColumnWidth(10, 6000); // shet1.setColumnWidth(20, 2000); XSSFCellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorder.setFillForegroundColor(HSSFColor.ORANGE.index); styleBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // CREATE HEADING 2 XSSFRow rheading2 = shet1.createRow(0); rheading2.setHeightInPoints(25); XSSFCell cellxx1, cellxx2, cellxx3, cellxx4, cellxx5, cellxx6, cellxx7, cellxx8, cellxx9, cellxx10, cellxx11, cellxx12, cellxx13; cellxx1 = rheading2.createCell(0); cellxx2 = rheading2.createCell(1); cellxx3 = rheading2.createCell(2); cellxx4 = rheading2.createCell(3); cellxx5 = rheading2.createCell(4); cellxx6 = rheading2.createCell(5); cellxx7 = rheading2.createCell(6); cellxx8 = rheading2.createCell(7); cellxx9 = rheading2.createCell(8); cellxx10 = rheading2.createCell(9); cellxx11 = rheading2.createCell(10); cellxx12 = rheading2.createCell(11); cellxx13 = rheading2.createCell(12); // cellxx1.setCellValue("GROUP NAME"); // cellxx2.setCellValue("GENDER"); // cellxx3.setCellValue("CONTRACEPTIVE METHOD"); // cellxx4.setCellValue("REFERRED TO A SERVICE POINT"); // cellxx5.setCellValue("GIVEN CONDOMS"); // cellxx6.setCellValue("SCREENED FOR TB"); // cellxx7.setCellValue("SCREENED FOR STIS"); // cellxx8.setCellValue("TESTED PARTNER"); // cellxx9.setCellValue("TESTED CHILDREN"); // cellxx10.setCellValue("DISCLOSED STATUS"); // cellxx11.setCellValue("PERIOD"); cellxx1.setCellValue("DISTRICT"); cellxx2.setCellValue("GROUP NAME"); cellxx3.setCellValue("GENDER"); cellxx4.setCellValue("CONTRACEPTIVE METHOD"); cellxx5.setCellValue("REFERRED TO A SERVICE POINT"); cellxx6.setCellValue("GIVEN CONDOMS"); cellxx7.setCellValue("SCREENED FOR TB"); cellxx8.setCellValue("SCREENED FOR STIS"); cellxx9.setCellValue("TESTED PARTNER"); cellxx10.setCellValue("TESTED CHILDREN"); cellxx11.setCellValue("DISCLOSED STATUS"); cellxx12.setCellValue("PERIOD"); cellxx13.setCellValue("AGE BRACKET"); cellxx1.setCellStyle(styleBorder); cellxx2.setCellStyle(styleBorder); cellxx3.setCellStyle(styleBorder); cellxx4.setCellStyle(styleBorder); cellxx5.setCellStyle(styleBorder); cellxx6.setCellStyle(styleBorder); cellxx7.setCellStyle(styleBorder); cellxx8.setCellStyle(styleBorder); cellxx9.setCellStyle(styleBorder); cellxx10.setCellStyle(styleBorder); cellxx11.setCellStyle(styleBorder); cellxx12.setCellStyle(styleBorder); cellxx13.setCellStyle(styleBorder); pos = 1; XSSFCellStyle stylex = wb.createCellStyle(); //stylex.setFillForegroundColor(HSSFColor.LIME.index); //stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); String getServices = "SELECT client_id,groupName,GENDER, bit_or(cm) AS CONTRACEPTIVE_METHOD,bit_or(sp) AS REFERRED_TO_SERVICE_POINT, " + "SUM(cg) AS CONDOMS_GIVEN ,bit_or(st) AS SCREENED_TB ,bit_or(ss) AS SCREENED_STIS,bit_or(tp) TESTED_PARTNER, " + "bit_or(tc) AS TESTED_CHILDREN,bit_or(ds) as DISCLOSED_STATUS,year AS pepfaryear,month as pepfarmonth, district_name AS district,AGEBRACKET FROM ( " + "SELECT personal_information.client_id as client_id,groups.group_name as groupName,personal_information.gender as GENDER, " + " CASE " + " WHEN services_provided.contraceptive_method= 'YES' THEN 1 " + " WHEN services_provided.contraceptive_method= 'NO' THEN 0 " + "ELSE 'NONE' " + "END AS cm, " + "CASE " + " WHEN services_provided.rsp LIKE 'YES' THEN 1 " + " WHEN services_provided.rsp LIKE 'NO' THEN 0 " + "ELSE 'NONE' " + "END AS sp, " + " services_provided.cds_given AS cg, " + "CASE " + " WHEN services_provided.screened_tb='YES' THEN 1 " + " WHEN services_provided.screened_tb='NO' THEN 0 " + "ELSE 'NONE' " + "END AS st, " + "CASE " + " WHEN services_provided.screened_stis='YES' THEN 1 " + " WHEN services_provided.screened_stis='NO' THEN 0 " + "ELSE 'NONE' " + "END AS ss, " + "CASE " + " WHEN services_provided.tested_partner='YES' THEN 1 " + " WHEN services_provided.tested_partner='NO' THEN 0 " + "ELSE 'NONE' " + "END AS tp, " + "CASE " + " WHEN services_provided.tested_children= 'YES' THEN 1 " + " WHEN services_provided.tested_children= 'NO' THEN 0 " + "ELSE 'NONE' " + "END AS tc, " + "CASE " + " WHEN services_provided.disclosed_status= 'YES' THEN 1 " + " WHEN services_provided.disclosed_status= 'NO' THEN 0 " + "ELSE 'NONE' " + "END AS ds,services_provided.submission_month AS month,services_provided.submission_year as year, district.district_name as district_name," + "CASE" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above'" + " ELSE 'NO DATE OF BIRTH' " + " END AS AGEBRACKET " + "FROM personal_information JOIN district ON personal_information.district_id=district.district_id " + " LEFT JOIN groups ON groups.group_id=personal_information.group_id " + " JOIN services_provided ON services_provided.client_id=personal_information.client_id" + " WHERE personal_information.partner_id='" + partner_id + "' && services_provided.submission_month>='" + startdate + "' && services_provided.submission_month<='" + enddate + "' " + "&& services_provided.submission_year='" + pepfaryear + "' order by personal_information.client_id ) as temptbl" + " WHERE cm>0 || sp>0 || cg>0 || st>0 || ss>0" + " || tp>0 || tc>0 || ds>0 GROUP BY client_id ORDER BY client_id"; conn.rs = conn.st.executeQuery(getServices); while (conn.rs.next()) { groupname = conn.rs.getString(2); if (groupname == null) { groupname = "INDIVIDUAL"; } gender = conn.rs.getString(3); contraceptive_method = conn.rs.getInt(4); rsp = conn.rs.getInt(5); cds_given = conn.rs.getInt(6); screened_tb = conn.rs.getInt(7); screened_stis = conn.rs.getInt(8); tested_partner = conn.rs.getInt(9); tested_children = conn.rs.getInt(10); disclosed_status = conn.rs.getInt(11); datekey = Integer.parseInt(conn.rs.getInt(12) + "" + conn.rs.getInt(13)); periodS = conn.rs.getInt(12) + "-" + conn.rs.getInt(13); district = conn.rs.getString(14); agebracket = conn.rs.getString(15); if (contraceptive_method > 0 || rsp > 0 || cds_given > 0 || screened_tb > 0 || screened_stis > 0 || tested_partner > 0 || tested_children > 0 || disclosed_status > 0) { // CREATE ROW AND ADD DATA TO THE DATA CELLS====================== incrementor++; XSSFRow data = shet1.createRow(pos); data.setHeightInPoints(25); XSSFCell cellx1, cellx2, cellx3, cellx4, cellx5, cellx6, cellx7, cellx8, cellx9, cellx10, cellx11, cellx12, cellx13; cellx1 = data.createCell(0); cellx2 = data.createCell(1); cellx3 = data.createCell(2); cellx4 = data.createCell(3); cellx5 = data.createCell(4); cellx6 = data.createCell(5); cellx7 = data.createCell(6); cellx8 = data.createCell(7); cellx9 = data.createCell(8); cellx10 = data.createCell(9); cellx11 = data.createCell(10); cellx12 = data.createCell(11); cellx13 = data.createCell(12); cellx1.setCellValue(district); cellx2.setCellValue(groupname); cellx3.setCellValue(gender); cellx4.setCellValue(contraceptive_method); cellx5.setCellValue(rsp); cellx6.setCellValue(cds_given); cellx7.setCellValue(screened_tb); cellx8.setCellValue(screened_stis); cellx9.setCellValue(tested_partner); cellx10.setCellValue(tested_children); cellx11.setCellValue(disclosed_status); cellx12.setCellValue(periodS); cellx13.setCellValue(agebracket); cellx1.setCellStyle(stylex); cellx2.setCellStyle(stylex); cellx3.setCellStyle(stylex); cellx4.setCellStyle(stylex); cellx5.setCellStyle(stylex); cellx6.setCellStyle(stylex); cellx7.setCellStyle(stylex); cellx8.setCellStyle(stylex); cellx9.setCellStyle(stylex); cellx10.setCellStyle(stylex); cellx11.setCellStyle(stylex); cellx12.setCellStyle(stylex); cellx13.setCellStyle(stylex); pos++; } } String getPartner = "SELECT partner_name FROM partner WHERE partner_id='" + partner_id + "'"; conn.rs = conn.st.executeQuery(getPartner); if (conn.rs.next() == true) { partner_name = conn.rs.getString(1).trim().replace(" ", "_"); } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } if (incrementor > 0) { // write it as an excel attachment if (session.getAttribute("period").toString().equals("10-12")) { pepfaryear++; } ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_SERVICES_PROVIDED_PER_GROUP_REPORT_FOR_pepfar_year_" + pepfaryear + "(" + period + ")_AND_PARTNER_" + partner_name + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); } else { session.setAttribute("kePMSError", "<font color=\"red\"><b>NO DATA WITHIN THE SELECTED PARAMETERS.</b></font>"); response.sendRedirect("kePMS.jsp"); } }