List of usage examples for org.apache.poi.ss.usermodel CellStyle setAlignment
void setAlignment(HorizontalAlignment align);
From source file:reports.IndvRawData.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();/*from w ww.j a v a2 s. c o m*/ dbConn conn = new dbConn(); group_ids = session.getAttribute("customInd").toString(); startDate = session.getAttribute("custstartDate").toString(); endDate = session.getAttribute("custendDate").toString(); System.out.println(" group _ids are : " + group_ids); i = 4; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet(); HSSFFont 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); HSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); HSSFCellStyle 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); shet1.setColumnWidth(0, 5000); shet1.setColumnWidth(1, 5000); shet1.setColumnWidth(2, 5000); shet1.setColumnWidth(3, 5500); shet1.setColumnWidth(4, 7000); shet1.setColumnWidth(5, 5300); shet1.setColumnWidth(6, 3000); shet1.setColumnWidth(7, 3200); shet1.setColumnWidth(8, 3200); shet1.setColumnWidth(9, 3200); shet1.setColumnWidth(10, 3800); shet1.setColumnWidth(11, 3000); shet1.setColumnWidth(12, 5300); shet1.setColumnWidth(13, 5000); shet1.setColumnWidth(14, 5300); shet1.setColumnWidth(15, 5000); shet1.setColumnWidth(16, 5200); shet1.setColumnWidth(17, 5200); shet1.setColumnWidth(18, 5200); shet1.setColumnWidth(19, 5800); shet1.setColumnWidth(20, 5000); shet1.setColumnWidth(21, 5300); shet1.setColumnWidth(22, 5300); shet1.setColumnWidth(23, 5000); shet1.setColumnWidth(24, 5200); shet1.setColumnWidth(25, 5200); // shet1.setColumnWidth(20, 2000); HSSFCellStyle 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); HSSFRow rw1 = shet1.createRow(1); HSSFCell cell; HSSFRow rw4 = shet1.createRow(0); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell0, cell1, cell2, cell3, cell4, cell5, cell6, cell7, cell8, cell9, cell10, cell11, cell12, cell13, cell14, cell15, cell16, cell17, cell18, cell19, cell20; HSSFCell cell21, cell22, cell23, cell24, cell25, cell26, cell27, cell28, cell29, cell30, cell31, cell32, cell33; 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); cell7 = rw4.createCell(7); cell8 = rw4.createCell(8); cell9 = rw4.createCell(9); cell10 = rw4.createCell(10); cell11 = rw4.createCell(11); cell12 = rw4.createCell(12); cell13 = rw4.createCell(13); cell14 = rw4.createCell(14); cell15 = rw4.createCell(15); cell16 = rw4.createCell(16); cell17 = rw4.createCell(17); cell18 = rw4.createCell(18); cell19 = rw4.createCell(19); cell20 = rw4.createCell(20); cell21 = rw4.createCell(21); cell22 = rw4.createCell(22); cell23 = rw4.createCell(23); cell24 = rw4.createCell(24); cell25 = rw4.createCell(25); cell0.setCellValue("COUNTY NAME"); cell1.setCellValue("PARTNER NAME"); cell2.setCellValue("NEAREST FACILITY"); cell3.setCellValue("GROUP NAME"); cell4.setCellValue("SERVICE PROVIDER"); cell5.setCellValue("FULL NAME"); cell6.setCellValue("AGE"); cell7.setCellValue("GENDER"); cell8.setCellValue("DATE OF BIRTH"); cell9.setCellValue("NATIONAL ID"); cell10.setCellValue("MOBILE NO"); cell11.setCellValue("CCC NO"); cell12.setCellValue("No. of Messages Attended"); cell13.setCellValue("Knowledge of HIV Status"); cell14.setCellValue("Partner HIV Testing"); cell15.setCellValue("Child HIV Testing"); cell16.setCellValue("Discordance"); cell17.setCellValue("HIV Disclosure"); cell18.setCellValue("Risk Factor/Reduction"); cell19.setCellValue("Condom Use"); cell20.setCellValue("Alcohol and Substance Abuse"); cell21.setCellValue("Adherence"); cell22.setCellValue("STIs"); cell23.setCellValue("Family Planning"); cell24.setCellValue("PMTCT"); cell25.setCellValue("TB"); HSSFCellStyle 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); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); cell0.setCellStyle(stylex); cell1.setCellStyle(stylex); cell2.setCellStyle(stylex); cell3.setCellStyle(stylex); cell4.setCellStyle(stylex); cell5.setCellStyle(stylex); cell6.setCellStyle(stylex); cell7.setCellStyle(stylex); cell8.setCellStyle(stylex); cell9.setCellStyle(stylex); cell10.setCellStyle(stylex); cell11.setCellStyle(stylex); cell12.setCellStyle(stylex); cell13.setCellStyle(stylex); cell14.setCellStyle(stylex); cell15.setCellStyle(stylex); cell16.setCellStyle(stylex); cell17.setCellStyle(stylex); cell18.setCellStyle(stylex); cell19.setCellStyle(stylex); cell20.setCellStyle(stylex); cell21.setCellStyle(stylex); cell22.setCellStyle(stylex); cell23.setCellStyle(stylex); cell24.setCellStyle(stylex); cell25.setCellStyle(stylex); i = 1; String[] groupIDS = group_ids.split(","); for (String group_id : groupIDS) { if (!group_id.equals("") && !group_id.equals(",")) { groupid = group_id; System.out.println("group id is : " + groupid); String getClients = "SELECT personal_information.client_id,personal_information.fname,personal_information.mname,personal_information.lname," + "DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )" + ",personal_information.gender,groups.group_name,district.district_name,partner.partner_name," + "service_provider.fname,service_provider.mname,service_provider.lname,personal_information.lessons_attended," + "personal_information.national_id,personal_information.ccc_no,personal_information.mobile_no,health_facility.hf_name,county.county_name " + " FROM personal_information" + " 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 county ON district.county_id=county.county_id " + "LEFT JOIN service_provider ON personal_information.provider_id=service_provider.provider_id " + "LEFT JOIN partner ON personal_information.partner_id=partner.partner_id " + "LEFT JOIN groups ON personal_information.group_id =groups.group_id" + " WHERE personal_information.group_id='" + groupid + "' ORDER BY partner.partner_name,district.district_name,personal_information.fname,personal_information.mname,personal_information.lname"; conn.rs = conn.st.executeQuery(getClients); while (conn.rs.next()) { county = district = hf = partner = groupname = serviceprovider = clientname = age = gender = groupings = year = providerid = ""; countyid = districtid = hfid = partnerid = groupid = serviceproviderid = clientid = ""; s1 = s2 = s3 = s4 = s5 = s6 = s7 = s8 = s9 = s10 = s11 = s12 = s13 = 0; cm = rsp = cd = tb = sti = testedpartner = testedchild = session_no = value = status; sess = val = cds = 0; hf_id = lessons_attended = national_id = ccc_no = mobile_no = dob = ""; added = 0; clientid = conn.rs.getString(1); if (!conn.rs.getString(3).equalsIgnoreCase(conn.rs.getString(4))) { clientname = conn.rs.getString(2) + " " + conn.rs.getString(3) + " " + conn.rs.getString(4); } else { clientname = conn.rs.getString(2) + " " + conn.rs.getString(4); } age = conn.rs.getString(5); gender = conn.rs.getString(6); groupname = conn.rs.getString(7); if (groupname == null) { groupname = "INDIVIDUAL"; } // districtname=conn.rs.getString(8); partner = conn.rs.getString(9); serviceprovider = conn.rs.getString(10) + " " + conn.rs.getString(11) + " " + conn.rs.getString(12); if (conn.rs.getString(11) != null && conn.rs.getString(12) != null) { if (conn.rs.getString(11).equals(conn.rs.getString(12))) { serviceprovider = conn.rs.getString(10) + " " + conn.rs.getString(12); } } lessons_attended = conn.rs.getString(13); national_id = conn.rs.getString(14); ccc_no = conn.rs.getString(15); mobile_no = conn.rs.getString(16); hf = conn.rs.getString(17); county = conn.rs.getString(18); String getAttended = "SELECT session_no,value FROM register2 WHERE client_id='" + clientid + "' && " + " STR_TO_DATE(register2.date,'%m/%d/%Y') BETWEEN STR_TO_DATE('" + startDate + "','%m/%d/%Y') AND STR_TO_DATE('" + endDate + "','%m/%d/%Y') "; System.out.println(getAttended); conn.rs1 = conn.st1.executeQuery(getAttended); while (conn.rs1.next()) { sess = conn.rs1.getInt(1); val = conn.rs1.getInt(2); if (sess == 1) { if (val == 1) { s1 = 1; added++; } else if (val == 2) { s1 = 0; } else { s1 = 2; } } if (sess == 2) { if (val == 1) { s2 = 1; added++; } else if (val == 2) { s2 = 0; } else { s2 = 2; } } if (sess == 3) { if (val == 1) { s3 = 1; added++; } else if (val == 2) { s3 = 0; } else { s3 = 2; } } if (sess == 4) { if (val == 1) { s4 = 1; added++; } else if (val == 2) { s4 = 0; } else { s4 = 2; } } if (sess == 5) { if (val == 1) { s5 = 1; added++; } else if (val == 2) { s5 = 0; } else { s5 = 2; } } if (sess == 6) { if (val == 1) { s6 = 1; added++; } else if (val == 2) { s6 = 0; } else { s6 = 2; } } if (sess == 7) { if (val == 1) { s7 = 1; added++; } else if (val == 2) { s7 = 0; } else { s7 = 2; } } if (sess == 8) { if (val == 1) { s8 = 1; added++; } else if (val == 2) { s8 = 0; } else { s8 = 2; } } if (sess == 9) { if (val == 1) { s9 = 1; added++; } else if (val == 2) { s9 = 0; } else { s9 = 2; } } if (sess == 10) { if (val == 1) { s10 = 1; added++; } else if (val == 2) { s10 = 0; } else { s10 = 2; } } if (sess == 11) { if (val == 1) { s11 = 1; added++; } else if (val == 2) { s11 = 0; } else { s11 = 2; } } if (sess == 12) { if (val == 1) { s12 = 1; added++; } else if (val == 2) { s12 = 0; } else { s12 = 2; } } if (sess == 13) { if (val == 1) { s13 = 1; added++; } else if (val == 2) { s13 = 0; } else { s13 = 2; } } } if (added > 10) { System.out.println("added id : " + clientid); } // OUTPUT ATTENDED-------------------------------- if (added > 0) { // OUTPUT SERVICES PROVIDED================================ HSSFRow rw4x = shet1.createRow(i); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell0x, cell1x, cell2x, cell3x, cell4x, cell5x, cell6x, cell7x, cell8x, cell9x, cell10x, cell11x, cell12x, cell13x, cell14x, cell15x, cell16x, cell17x, cell18x, cell19x, cell20x; HSSFCell cell21x, cell22x, cell23x, cell24x, cell25x, cell26x, cell27x, cell28x, cell29x, cell30x, cell31x, cell32x, cell33x; 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); cell7x = rw4x.createCell(7); cell8x = rw4x.createCell(8); cell9x = rw4x.createCell(9); cell10x = rw4x.createCell(10); cell11x = rw4x.createCell(11); cell12x = rw4x.createCell(12); cell13x = rw4x.createCell(13); cell14x = rw4x.createCell(14); cell15x = rw4x.createCell(15); cell16x = rw4x.createCell(16); cell17x = rw4x.createCell(17); cell18x = rw4x.createCell(18); cell19x = rw4x.createCell(19); cell20x = rw4x.createCell(20); cell21x = rw4x.createCell(21); cell22x = rw4x.createCell(22); cell23x = rw4x.createCell(23); cell24x = rw4x.createCell(24); cell25x = rw4x.createCell(25); cell26x = rw4x.createCell(26); cell27x = rw4x.createCell(27); cell28x = rw4x.createCell(28); cell29x = rw4x.createCell(29); cell30x = rw4x.createCell(30); cell31x = rw4x.createCell(31); cell32x = rw4x.createCell(32); cell33x = rw4x.createCell(33); cell0x.setCellValue(county); cell1x.setCellValue(partner); cell2x.setCellValue(hf); cell3x.setCellValue(groupname); cell4x.setCellValue(serviceprovider); cell5x.setCellValue(clientname); cell6x.setCellValue(age); cell7x.setCellValue(gender); cell8x.setCellValue(dob); cell9x.setCellValue(national_id); cell10x.setCellValue(mobile_no); cell11x.setCellValue(ccc_no); cell12x.setCellValue(added); if (s1 > 1) { cell13x.setCellValue(""); } else { cell13x.setCellValue(s1); } if (s2 > 1) { cell14x.setCellValue(""); } else { cell14x.setCellValue(s2); } if (s3 > 1) { cell15x.setCellValue(""); } else { cell15x.setCellValue(s3); } if (s4 > 1) { cell16x.setCellValue(""); } else { cell16x.setCellValue(s4); } if (s5 > 1) { cell17x.setCellValue(""); } else { cell17x.setCellValue(s5); } if (s6 > 1) { cell18x.setCellValue(""); } else { cell18x.setCellValue(s6); } if (s7 > 1) { cell19x.setCellValue(""); } else { cell19x.setCellValue(s7); } if (s8 > 1) { cell20x.setCellValue(""); } else { cell20x.setCellValue(s8); } if (s9 > 1) { cell21x.setCellValue(""); } else { cell21x.setCellValue(s9); } if (s10 > 1) { cell22x.setCellValue(""); } else { cell22x.setCellValue(s10); } if (s11 > 1) { cell23x.setCellValue(""); } else { cell23x.setCellValue(s11); } if (s12 > 1) { cell24x.setCellValue(""); } else { cell24x.setCellValue(s12); } if (s13 > 1) { cell25x.setCellValue(""); } else { cell25x.setCellValue(s13); } cell0x.setCellStyle(styleBorder); cell1x.setCellStyle(styleBorder); cell2x.setCellStyle(styleBorder); cell3x.setCellStyle(styleBorder); cell4x.setCellStyle(styleBorder); cell5x.setCellStyle(styleBorder); cell6x.setCellStyle(styleBorder); cell7x.setCellStyle(styleBorder); cell8x.setCellStyle(styleBorder); cell9x.setCellStyle(styleBorder); cell10x.setCellStyle(styleBorder); cell11x.setCellStyle(styleBorder); cell12x.setCellStyle(styleBorder); cell13x.setCellStyle(styleBorder); cell14x.setCellStyle(styleBorder); cell15x.setCellStyle(styleBorder); cell16x.setCellStyle(styleBorder); cell17x.setCellStyle(styleBorder); cell18x.setCellStyle(styleBorder); cell19x.setCellStyle(styleBorder); cell20x.setCellStyle(styleBorder); cell21x.setCellStyle(styleBorder); cell22x.setCellStyle(styleBorder); cell23x.setCellStyle(styleBorder); cell24x.setCellStyle(styleBorder); cell25x.setCellStyle(styleBorder); i++; } System.out.println("here : " + i); } } } 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(); } // 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_Raw_Data.xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:reports.kePMS.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException, InvalidFormatException { session = request.getSession();//from w ww . ja v a 2 s .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 w w w.j a va2 s. c o m 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();/* ww w .j a v a 2 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 v a 2 s.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.kePMSFormated.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();/* w ww .j a va 2 s . c o m*/ dbConn conn = new dbConn(); pos = 0; pepfaryear = 2014; // 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); String allpath = getServletContext().getRealPath("/TEMPLATE.xlsm"); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb; wb = new HSSFWorkbook(); // HSSFWorkbook wb=new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet("sheet0"); HSSFFont 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); HSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); HSSFCellStyle 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); HSSFCellStyle 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); HSSFFont 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); HSSFRow rw4 = shet1.createRow(0); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell 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++; HSSFRow rw4x = shet1.createRow(pos); rw4x.setHeightInPoints(25); rw4x.setRowStyle(style2); HSSFCell 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.xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:reports.kePMSGroup.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, InvalidFormatException, SQLException { session = request.getSession();//from w ww.ja v a 2s. co m 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 w ww . j a va 2s. com*/ 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"); } }
From source file:reports.kePMSnew.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException, InvalidFormatException { session = request.getSession();//from w w w. j av a 2s . c o m dbConn conn = new dbConn(); pos = 0; incrementor = 0; // pepfaryear=2014; // pepfaryear=Integer.parseInt(request.getParameter("year")); 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("/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"); System.out.println("real path for template : " + allpath); // ^^^^^^^^^^^^^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); } } 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) { 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_ACHIEVED_REPORT_PER_PARTNER_FOR_PEPFAR_YEAR_" + pepfaryear + "_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.kePMSNotAchieved.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, InvalidFormatException, SQLException { session = request.getSession();/* ww w . j a v a 2s.c om*/ dbConn conn = new dbConn(); pos = 0; // COPY FILE TO BE WRITTEN TO Path original = Paths.get(getServletContext().getRealPath("/NotAchieved.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/NotAchieved_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("/NotAchieved_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("COUNTY NAME"); cell1.setCellValue("DISTRICT NAME"); cell2.setCellValue("PARTNER NAME"); cell3.setCellValue("GROUP NAME"); cell4.setCellValue("AGE BRACKET"); cell5.setCellValue("GENDER"); cell6.setCellValue("CLIENTS"); String getClients = "SELECT county.county_name,district.district_name,partner.partner_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 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" + ",COUNT(personal_information.client_id),groups.group_name as groupname FROM personal_information " + "JOIN partner ON personal_information.partner_id=partner.partner_id " + " JOIN district ON district.district_id=personal_information.district_id " + "JOIN county ON county.county_id=district.county_id " + "LEFT JOIN groups ON personal_information.group_id=groups.group_id " + " WHERE personal_information.lessons_attended>0 && personal_information.completionyear=0 " + "GROUP BY partner.partner_name,district.district_name,groupname,SEX,AGEBRACKET ORDER BY personal_information.partner_id"; conn.rs = conn.st.executeQuery(getClients); while (conn.rs.next()) { partnername = clientid = gender = ""; age = 0; countyname = conn.rs.getString(1); districtname = conn.rs.getString(2); partnername = conn.rs.getString(3); agebracket = conn.rs.getString(4); gender = conn.rs.getString(5); achieved = conn.rs.getInt(6); groupname = conn.rs.getString(7); if (groupname == null) { groupname = "INDIVIDUAL"; } 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(countyname); cell1x.setCellValue(districtname); cell2x.setCellValue(partnername); cell3x.setCellValue(groupname); cell4x.setCellValue(agebracket); cell5x.setCellValue(gender); cell6x.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_NOT_ACHIEVED.xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); }