List of usage examples for org.apache.poi.ss.usermodel CellStyle setAlignment
void setAlignment(HorizontalAlignment align);
From source file:reports.kePMSNotAchievedDIC.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, InvalidFormatException, SQLException { session = request.getSession();/*from w w w .j a va 2 s . c o m*/ dbConn conn = new dbConn(); pos = 0; // partner_id="4"; partner_id = session.getAttribute("partnerDIC").toString(); String getPartner = "SELECT partner_name FROM partner WHERE partner_id='" + partner_id + "'"; conn.rs = conn.st.executeQuery(getPartner); if (conn.rs.next() == true) { partnername = conn.rs.getString(1).trim().replace(" ", "_"); } // 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, cell5; cell0 = rw4.createCell(0); cell1 = rw4.createCell(1); cell2 = rw4.createCell(2); cell3 = rw4.createCell(3); cell4 = rw4.createCell(4); cell5 = rw4.createCell(5); cell0.setCellValue("COUNTY NAME"); cell1.setCellValue("DISTRICT NAME"); cell2.setCellValue("DIC NAME"); cell3.setCellValue("AGE BRACKET"); cell4.setCellValue("GENDER"); cell5.setCellValue("CLIENTS"); String getClients = "SELECT county.county_name,district.district_name,dic.dic_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 'NOT SELECTED'" + " 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) FROM personal_information " + "JOIN dic ON personal_information.dic_id=dic.dic_id " + " JOIN district ON district.district_id=personal_information.district_id " + "JOIN county ON county.county_id=district.county_id " + " WHERE personal_information.partner_id='" + partner_id + "' && personal_information.lessons_attended>0 && personal_information.completionyear=0 " + "GROUP BY dic.dic_name,district.district_name,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); pos++; XSSFRow rw4x = shet1.createRow(pos); rw4x.setHeightInPoints(25); rw4x.setRowStyle(style2); XSSFCell cell0x, cell1x, cell2x, cell3x, cell4x, cell5x; cell0x = rw4x.createCell(0); cell1x = rw4x.createCell(1); cell2x = rw4x.createCell(2); cell3x = rw4x.createCell(3); cell4x = rw4x.createCell(4); cell5x = rw4x.createCell(5); // OUTPUT SERVICES PROVIDED================================ cell0x.setCellValue(countyname); cell1x.setCellValue(districtname); cell2x.setCellValue(partnername); cell3x.setCellValue(agebracket); cell4x.setCellValue(gender); cell5x.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_PER_DIC_FOR_" + partnername.trim() + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); }
From source file:reports.kePMSServices.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException, InvalidFormatException { session = request.getSession();//from w w w.j a va 2 s.com dbConn conn = new dbConn(); incrementor = 0; pos = 0; 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("/ServicesAll.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/ServicesAll_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("/ServicesAll_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.setItalic(true); // font.setBoldweight((short)12); 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(11, 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, cellxx14, cellxx15; 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); // cellxx14=rheading2.createCell(13); // cellxx15=rheading2.createCell(14); cellxx1.setCellValue("COUNTY NAME"); cellxx2.setCellValue("PARTNER NAME"); cellxx3.setCellValue("DISTRICT NAME"); cellxx4.setCellValue("GENDER"); cellxx5.setCellValue("CONTRACEPTIVE METHOD"); cellxx6.setCellValue("REFERRED TO A SERVICE POINT"); cellxx7.setCellValue("GIVEN CONDOMS"); cellxx8.setCellValue("SCREENED FOR TB"); cellxx9.setCellValue("SCREENED FOR STIS"); cellxx10.setCellValue("TESTED PARTNER"); cellxx11.setCellValue("TESTED CHILDREN"); cellxx12.setCellValue("DISCLOSED STATUS"); // cellxx13.setCellValue("YEAR"); // cellxx14.setCellValue("MONTH"); 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); // cellxx14.setCellStyle(styleBorder); // cellxx15.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,COUNTY,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,partnername as partner,districtname as district,AGEBRACKET FROM ( " + "SELECT personal_information.client_id as client_id,county.county_name as COUNTY,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,partner.partner_name as partnername," + "district.district_name as districtname," // + " CASE " // + " WHEN services_provided.submission_month=1 THEN 'JAN' " // + " WHEN services_provided.submission_month=2 THEN 'FEB' " // + " WHEN services_provided.submission_month=3 THEN 'MAR' " // + " WHEN services_provided.submission_month=4 THEN 'APR' " // + " WHEN services_provided.submission_month=5 THEN 'MAY' " // + " WHEN services_provided.submission_month=6 THEN 'JUN' " // + " WHEN services_provided.submission_month=7 THEN 'JUL' " // + " WHEN services_provided.submission_month=8 THEN 'AUG' " // + " WHEN services_provided.submission_month=9 THEN 'SEPT' " // + " WHEN services_provided.submission_month=10 THEN 'OCT' " // + " WHEN services_provided.submission_month=11 THEN 'NOV' " // + " WHEN services_provided.submission_month=12 THEN 'DEC'" // + "ELSE 'NO MONTH' END AS MONTHNAME," + "CASE" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above'" + " ELSE 'NO DATE OF BIRTH' " + " END AS AGEBRACKET " + " FROM personal_information JOIN partner ON personal_information.partner_id=partner.partner_id " + "" + "" + " JOIN (district JOIN county ON county.county_id=district.county_id) ON district.district_id=personal_information.district_id " + " JOIN services_provided ON services_provided.client_id=personal_information.client_id" + " WHERE 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()) { countyname = conn.rs.getString(2); 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)); partner = conn.rs.getString(14); district = conn.rs.getString(15); // monthname=conn.rs.getString(16); agebracket = conn.rs.getString(16); 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, cellx14, cellx15; 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); // cellx14=data.createCell(13); // cellx15=data.createCell(14); cellx1.setCellValue(countyname); cellx2.setCellValue(partner); cellx3.setCellValue(district); cellx4.setCellValue(gender); cellx5.setCellValue(contraceptive_method); cellx6.setCellValue(rsp); cellx7.setCellValue(cds_given); cellx8.setCellValue(screened_tb); cellx9.setCellValue(screened_stis); cellx10.setCellValue(tested_partner); cellx11.setCellValue(tested_children); cellx12.setCellValue(disclosed_status); // cellx13.setCellValue(pepfaryear); cellx13.setCellValue(agebracket); // cellx15.setCellValue(); 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); // cellx14.setCellStyle(stylex); // cellx15.setCellStyle(stylex); pos++; } } 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_REPORT_FOR_PEPFAR_YEAR_" + pepfaryear + "(" + period + ").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.kmmpexcel.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {/*from w w w . jav a 2s.com*/ response.setContentType("text/html;charset=UTF-8"); session = request.getSession(); dbConn conn = new dbConn(); //get the existing data for the month, year and facility that is already on session String month = ""; String year = ""; String facil = "361"; String form = "kmmp"; String reportType = ""; if (request.getParameter("reportType") != null) { reportType = request.getParameter("reportType"); } String reportDuration = ""; if (request.getParameter("reportDuration") != null) { reportDuration = request.getParameter("reportDuration"); } //===================================================================================================== year = "2015"; month = "5"; String county = ""; String header = ""; String reporttype = ""; if (request.getParameter("year") != null) { year = request.getParameter("year"); } if (request.getParameter("facility") != null && reportType.equals("2")) { facil = request.getParameter("facility"); String getfacil = "select SubPartnerNom,CentreSanteId as mflcode from subpartnera where SubPartnerID='" + facil + "'"; conn.rs = conn.st.executeQuery(getfacil); while (conn.rs.next()) { header += " FACILITY : " + conn.rs.getString(1).toUpperCase() + " MFL CODE : " + conn.rs.getString(2) + " "; } } if (request.getParameter("county") != null && reportType.equals("2")) { county = request.getParameter("county"); String getcounty = "select County from county where CountyID='" + county + "'"; conn.rs = conn.st.executeQuery(getcounty); while (conn.rs.next()) { header += " COUNTY : " + conn.rs.getString(1).toUpperCase() + " "; } } if (request.getParameter("month") != null && reportDuration.equals("4")) { month = request.getParameter("month"); String getmonth = "select name as monthname from month where id='" + month + "'"; conn.rs = conn.st.executeQuery(getmonth); while (conn.rs.next()) { header += " MONTH : " + conn.rs.getString(1).toUpperCase() + " "; } } header += " YEAR : " + year + ""; String facilitywhere = ""; String yearwhere = ""; String monthwhere = ""; String countywhere = ""; String duration = ""; String semi_annual = ""; String quarter = ""; //================================================================================================== //XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX int yearcopy = Integer.parseInt(year); // reportType="2"; // year=2015; // reportDuration="3"; String yearmonth = "" + year; int prevYear = yearcopy - 1; int maxYearMonth = 0; int monthcopy = 0; // GET REPORT DURATION============================================ if (reportDuration.equals("1")) { yearmonth += "_AnnualReport"; duration = " " + form + ".yearmonth BETWEEN " + prevYear + "10 AND " + year + "09"; } else if (reportDuration.equals("2")) { semi_annual = request.getParameter("semi_annual"); // semi_annual="2"; if (semi_annual.equals("1")) { yearmonth = prevYear + "_Oct_" + year + "_Mar"; duration = " " + form + ".yearmonth BETWEEN " + prevYear + "10 AND " + year + "03"; } else { yearmonth += "_Apr_Sep"; duration = " " + form + ".yearmonth BETWEEN " + year + "04 AND " + year + "09"; } } else if (reportDuration.equals("3")) { String startMonth, endMonth; quarter = request.getParameter("quarter"); // quarter="3"; String getMonths = "SELECT months,name FROM quarter WHERE id='" + quarter + "'"; conn.rs = conn.st.executeQuery(getMonths); if (conn.rs.next() == true) { String months[] = conn.rs.getString(1).split(","); startMonth = months[0]; endMonth = months[2]; if (quarter.equals("1")) { duration = " " + form + ".yearmonth BETWEEN " + prevYear + "" + startMonth + " AND " + prevYear + "" + endMonth; yearmonth = prevYear + "_" + conn.rs.getString(2); } else { yearmonth = year + "_" + conn.rs.getString(2); duration = " " + form + ".yearmonth BETWEEN " + year + "" + startMonth + " AND " + year + "" + endMonth; } } } else if (reportDuration.equals("4")) { monthcopy = Integer.parseInt(request.getParameter("month")); // month=5; if (monthcopy >= 10) { yearmonth = prevYear + "_" + month; duration = " " + form + ".yearmonth=" + prevYear + "" + month; } else { duration = " " + form + ".yearmonth=" + year + "0" + month; yearmonth = year + "_(" + month + ")"; } } else { duration = ""; } //====================================================================== //================================================================================================== //XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX String getexistingdata = ""; if (!county.equals("")) { countywhere = " and countyid = '" + county + "'"; } if (!facil.equals("") && reportType.equalsIgnoreCase("2")) { facilitywhere = " and kmmp.SubPartnerID = '" + facil + "'"; } //String joinedwhwere=" where 1=1 "+yearwhere+" && "+duration; String joinedwhwere = " where 1=1 " + facilitywhere + " " + yearwhere + " && " + duration; //===================================================================================================== //______________________________________________________________________________________ // NOW CREATE THE WORKSHEETS //______________________________________________________________________________________ HSSFWorkbook wb = new HSSFWorkbook(); //______________________________________________________________________________________ //______________________________________________________________________________________ HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 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.GREY_25_PERCENT.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.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); HSSFSheet shet = wb.createSheet(form); //create headers for that worksheet HSSFRow rw = shet.createRow(0); rw.setHeightInPoints(25); HSSFCell cl0 = rw.createCell(0); cl0.setCellValue("KMMP HEALTH FACILITY REPORTING FORM"); cl0.setCellStyle(stylex); for (int a = 1; a < 4; a++) { HSSFCell clx = rw.createCell(a); clx.setCellValue(""); clx.setCellStyle(stylex); } HSSFRow rw1 = shet.createRow(1); rw1.setHeightInPoints(23); HSSFCell cl = rw1.createCell(0); cl.setCellValue(header); cl.setCellStyle(stylex); for (int a = 1; a < 4; a++) { HSSFCell clx = rw1.createCell(a); clx.setCellValue(""); clx.setCellStyle(stylex); } HSSFRow rw2 = shet.createRow(2); rw2.setHeightInPoints(23); HSSFCell cl3 = rw2.createCell(0); cl3.setCellValue("KMMP OUTPUT DATA"); cl3.setCellStyle(stylex); for (int a = 1; a < 4; a++) { HSSFCell clx = rw2.createCell(a); clx.setCellValue(""); clx.setCellStyle(stylex); } shet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3)); shet.addMergedRegion(new CellRangeAddress(1, 1, 0, 3)); shet.addMergedRegion(new CellRangeAddress(2, 2, 0, 3)); shet.addMergedRegion(new CellRangeAddress(3, 3, 1, 2)); shet.addMergedRegion(new CellRangeAddress(4, 4, 1, 2)); shet.addMergedRegion(new CellRangeAddress(5, 5, 1, 2)); shet.addMergedRegion(new CellRangeAddress(5, 7, 0, 0)); shet.addMergedRegion(new CellRangeAddress(6, 6, 1, 2)); shet.addMergedRegion(new CellRangeAddress(7, 7, 1, 2)); shet.addMergedRegion(new CellRangeAddress(8, 8, 1, 2)); shet.addMergedRegion(new CellRangeAddress(9, 11, 0, 0)); shet.addMergedRegion(new CellRangeAddress(9, 11, 1, 1)); shet.addMergedRegion(new CellRangeAddress(12, 12, 1, 2)); shet.addMergedRegion(new CellRangeAddress(13, 13, 1, 2)); shet.setColumnWidth(0, 2000); shet.setColumnWidth(1, 17000); shet.setColumnWidth(2, 5000); getexistingdata = "select sum(KMMP1) as KMMP1, sum(KMMP2) as KMMP2, sum(KMMP3a) as KMMP3a, sum(KMMP3b) as KMMP3b, avg(KMMP3c) as KMMP3c , sum(KMMP4) as KMMP4 , sum(KMMP5a) as KMMP5a, sum(KMMP5b) as KMMP5b, sum(KMMP5c) as KMMP5c, sum(HV0205) as HV0205, sum(HV0206) as HV0206 from kmmp join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on kmmp.SubPartnerID = subpartnera.SubPartnerID " + joinedwhwere + " "; System.out.println(getexistingdata); String formtype = "<b><font color='#4b8df8'>New Entry</font></b>"; String KMMP1 = ""; String KMMP2 = ""; String KMMP3a = ""; String KMMP3b = ""; String KMMP3c = ""; String KMMP4 = ""; String KMMP5a = ""; String KMMP5b = ""; String KMMP5c = ""; String HV0205 = ""; String HV0206 = ""; String distid = ""; if (session.getAttribute("subcountyid") != null) { distid = session.getAttribute("subcountyid").toString(); } int counter = 0; conn.rs = conn.st.executeQuery(getexistingdata); while (conn.rs.next()) { //now check if form was updated and if its one month after data entry //now load the column values here KMMP1 = conn.rs.getString("KMMP1"); if (KMMP1 == null) { KMMP1 = ""; } KMMP2 = conn.rs.getString("KMMP2"); if (KMMP2 == null) { KMMP2 = ""; } KMMP3a = conn.rs.getString("KMMP3a"); if (KMMP3a == null) { KMMP3a = ""; } KMMP3b = conn.rs.getString("KMMP3b"); if (KMMP3b == null) { KMMP3b = ""; } KMMP3c = conn.rs.getString("KMMP3c"); if (KMMP3c == null) { KMMP3c = ""; } KMMP4 = conn.rs.getString("KMMP4"); if (KMMP4 == null) { KMMP4 = ""; } KMMP5a = conn.rs.getString("KMMP5a"); if (KMMP5a == null) { KMMP5a = ""; } KMMP5b = conn.rs.getString("KMMP5b"); if (KMMP5b == null) { KMMP5b = ""; } KMMP5c = conn.rs.getString("KMMP5c"); if (KMMP5c == null) { KMMP5c = ""; } HV0205 = conn.rs.getString("HV0205"); if (HV0205 == null) { HV0205 = ""; } HV0206 = conn.rs.getString("HV0206"); if (HV0206 == null) { HV0206 = ""; } } String createdtable = ""; if (1 == 1) { if (1 == 1) { HSSFRow rw3 = shet.createRow(3); rw3.setHeightInPoints(23); HSSFCell cl4 = rw3.createCell(0); cl4.setCellValue("1"); cl4.setCellStyle(style2); HSSFCell cl41 = rw3.createCell(1); cl41.setCellValue("No of New HIV positive clients enrolled in KMMP Services (ANC and PN) "); cl41.setCellStyle(style2); HSSFCell cl42 = rw3.createCell(2); cl42.setCellValue(""); cl42.setCellStyle(style2); HSSFCell cl43 = rw3.createCell(3); cl43.setCellValue(KMMP1); cl43.setCellStyle(style2); } //================================================================================================ if (1 == 1) { HSSFRow rw3 = shet.createRow(4); rw3.setHeightInPoints(23); HSSFCell cl4 = rw3.createCell(0); cl4.setCellValue("2"); cl4.setCellStyle(style2); HSSFCell cl41 = rw3.createCell(1); cl41.setCellValue("No of New HIV negative clients enrolled in KMMP Services (ANC Only) "); cl41.setCellStyle(style2); HSSFCell cl42 = rw3.createCell(2); cl42.setCellValue(""); cl42.setCellStyle(style2); HSSFCell cl43 = rw3.createCell(3); cl43.setCellValue(KMMP2); cl43.setCellStyle(style2); } //================================================================================================ if (1 == 1) { HSSFRow rw3 = shet.createRow(5); rw3.setHeightInPoints(23); HSSFCell cl4 = rw3.createCell(0); cl4.setCellValue("3"); cl4.setCellStyle(style2); HSSFCell cl41 = rw3.createCell(1); cl41.setCellValue("a) No. of HIV-positive pregnant women enrolled in KMMP Services"); cl41.setCellStyle(style2); HSSFCell cl42 = rw3.createCell(2); cl42.setCellValue(""); cl42.setCellStyle(style2); HSSFCell cl43 = rw3.createCell(3); cl43.setCellValue(KMMP3a); cl43.setCellStyle(style2); } //================================================================================================ if (1 == 1) { HSSFRow rw3 = shet.createRow(6); rw3.setHeightInPoints(23); HSSFCell cl41 = rw3.createCell(1); cl41.setCellValue( " b) Total number of HIV-positive pregnant women in facility (New positive \n and Known Positive-MOH731)"); cl41.setCellStyle(style2); HSSFCell cl42 = rw3.createCell(2); cl42.setCellValue(""); cl42.setCellStyle(style2); HSSFCell cl43 = rw3.createCell(3); cl43.setCellValue(KMMP3b); cl43.setCellStyle(style2); } //================================================================================================ if (1 == 1) { HSSFRow rw3 = shet.createRow(7); rw3.setHeightInPoints(23); HSSFCell cl41 = rw3.createCell(1); cl41.setCellValue(" Percentage of new IV-positive pregnant women enrolled in KMMP Services"); cl41.setCellStyle(style2); HSSFCell cl42 = rw3.createCell(2); cl42.setCellValue(""); cl42.setCellStyle(style2); HSSFCell cl43 = rw3.createCell(3); cl43.setCellValue(KMMP3c.substring(0, 2) + "%"); cl43.setCellStyle(style2); } //================================================================================================ if (1 == 1) { HSSFRow rw3 = shet.createRow(8); rw3.setHeightInPoints(23); HSSFCell cl4 = rw3.createCell(0); cl4.setCellValue("4"); cl4.setCellStyle(style2); HSSFCell cl41 = rw3.createCell(1); cl41.setCellValue("No. of KMMP support group sessions held"); cl41.setCellStyle(style2); HSSFCell cl42 = rw3.createCell(2); cl42.setCellValue(""); cl42.setCellStyle(style2); HSSFCell cl43 = rw3.createCell(3); cl43.setCellValue(KMMP4); cl43.setCellStyle(style2); } //================================================================================================ if (1 == 1) { HSSFRow rw3 = shet.createRow(9); rw3.setHeightInPoints(23); HSSFCell cl4 = rw3.createCell(0); cl4.setCellValue(""); cl4.setCellStyle(style2); HSSFCell cl41 = rw3.createCell(1); cl41.setCellValue("Defaulter tracing"); cl41.setCellStyle(style2); HSSFCell cl42 = rw3.createCell(2); cl42.setCellValue("New Defaulted Clients"); cl42.setCellStyle(style2); HSSFCell cl43 = rw3.createCell(3); cl43.setCellValue(KMMP5a); cl43.setCellStyle(style2); } //================================================================================================ if (1 == 1) { HSSFRow rw3 = shet.createRow(10); rw3.setHeightInPoints(23); HSSFCell cl4 = rw3.createCell(1); cl4.setCellValue(""); cl4.setCellStyle(style2); HSSFCell cl42 = rw3.createCell(2); cl42.setCellValue("Clients Reached"); cl42.setCellStyle(style2); HSSFCell cl43 = rw3.createCell(3); cl43.setCellValue(KMMP5b); cl43.setCellStyle(style2); } //================================================================================================ if (1 == 1) { HSSFRow rw3 = shet.createRow(11); rw3.setHeightInPoints(23); HSSFCell cl4 = rw3.createCell(1); cl4.setCellValue(""); cl4.setCellStyle(style2); HSSFCell cl42 = rw3.createCell(2); cl42.setCellValue("Successfully resolved"); cl42.setCellStyle(style2); HSSFCell cl43 = rw3.createCell(3); cl43.setCellValue(KMMP5c); cl43.setCellStyle(style2); } //================================================================================================ if (1 == 1) { HSSFRow rw3 = shet.createRow(12); rw3.setHeightInPoints(23); HSSFCell cl4 = rw3.createCell(0); cl4.setCellValue(""); cl4.setCellStyle(style2); HSSFCell cl41 = rw3.createCell(1); cl41.setCellValue("MOH 731 HV02-05 Known positive status (at entry into ANC) "); cl41.setCellStyle(style2); HSSFCell cl42 = rw3.createCell(2); cl42.setCellValue(""); cl42.setCellStyle(style2); HSSFCell cl43 = rw3.createCell(3); cl43.setCellValue(HV0205); cl43.setCellStyle(style2); } //================================================================================================ if (1 == 1) { HSSFRow rw3 = shet.createRow(13); rw3.setHeightInPoints(23); HSSFCell cl4 = rw3.createCell(0); cl4.setCellValue(""); cl4.setCellStyle(style2); HSSFCell cl41 = rw3.createCell(1); cl41.setCellValue("MOH 731 HV02-06 Antenatal"); cl41.setCellStyle(style2); HSSFCell cl42 = rw3.createCell(2); cl42.setCellValue(""); cl42.setCellStyle(style2); HSSFCell cl43 = rw3.createCell(3); cl43.setCellValue(HV0206); cl43.setCellStyle(style2); } //================================================================================================ createdtable += header + "<br/><br/><br/><table class='mytable' border=\"1\" style=\"font-family:cambria; border-color: #e5e5e5;margin-bottom: 3px; width:500px;\"><tr class='form-actions'><th colspan='3'><b style=\"text-align:center;\"> KMMP OUTPUT DATA</b></th><th>Total</th></tr><tr><td><b> 1 </b></td><td colspan='2'>No of New HIV positive clients enrolled in KMMP Services (ANC and PN) </td><td>" + KMMP1 + "</td></tr>"; createdtable += "<tr><td>Successfully Resolved</td><td>" + KMMP5c + "</td></tr>"; createdtable += "<tr><td></td><td colspan='2'>MOH 731 HV02-05 Known positive status (at entry into ANC) :</td><td>" + HV0205 + "</td></tr>"; createdtable += "<tr><td></td><td colspan='2'>MOH 731 HV02-06 Antenatal:</td><td>" + HV0206 + "</td></tr></table> <div class='form-actions'></div>"; } //System.out.println(createdtable); if (conn.conn != null) { conn.conn.close(); } if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st != null) { conn.st.close(); } if (conn.st2 != null) { conn.st2.close(); } //############################################################################################################# IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); 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=kmmp" + yearmonth + "_Generated_On_" + createdOn + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); outStream.close(); //############################################################################################################# } catch (SQLException ex) { Logger.getLogger(kmmppdf.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.moh731_Facility.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();//from w ww . j a v a2s . com dbConn conn = new dbConn(); pmtcthv = 0; htchv = 0; arthv = 0; year = request.getParameter("year"); month = request.getParameter("month"); //-------------------------------------------------------------------------------- //-------------------------------------------------------------------------------- //added later to accomodate the years String subpartnerid = "SubPartnerID"; int monthint = 0; int yearint = 0; monthint = Integer.parseInt(month); yearint = Integer.parseInt(year); String subpartnera = "subpartnera"; if (yearint == 2015) { if (monthint == 10 || monthint == 11 || monthint == 12 || monthint == 1 || monthint == 2 || monthint == 3) { //here use a different subpartner id subpartnerid = "SP_ID"; subpartnera = "subpartnera2014"; } else { subpartnerid = "SubPartnerID"; subpartnera = "subpartnera"; } } else if (yearint <= 2014) { subpartnerid = "SP_ID"; subpartnera = "subpartnera2014"; } else if (yearint > 2015) { subpartnerid = "SubPartnerID"; subpartnera = "subpartnera"; } //--------------------------------------------------------------------------------------- //--------------------------------------------------------------------------------------- // year="2015"; // month="5"; counter = 0; monthName = ""; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet shet4 = wb.createSheet("HTC"); XSSFSheet shet1 = wb.createSheet("PMTCT"); XSSFSheet shet2 = wb.createSheet("Care and Treatment"); XSSFSheet shet3 = wb.createSheet("PEP"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); 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_LEFT); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.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_LEFT); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); for (int i = 0; i <= 3; i++) { shet1.setColumnWidth(i, 6000); } for (int i = 4; i <= 47; i++) { shet1.setColumnWidth(i, 4000); } for (int i = 0; i <= 3; i++) { shet2.setColumnWidth(i, 6000); } for (int i = 4; i <= 66; i++) { shet2.setColumnWidth(i, 3000); } for (int i = 0; i <= 3; i++) { shet3.setColumnWidth(i, 6000); } for (int i = 4; i <= 19; i++) { shet3.setColumnWidth(i, 3000); } for (int i = 0; i <= 3; i++) { shet4.setColumnWidth(i, 6000); } for (int i = 4; i <= 14; i++) { shet4.setColumnWidth(i, 3000); } headers = "COUNTY,SUB COUNTY,FACILITY NAME,MFL CODE,ART HIGH VOLUME,HTC HIGH VOLUME,PMTCT HIGH VOLUME"; String arrayHeader[] = headers.split(","); int headerno = 0; // XSSFRow rw0S1=shet1.createRow(0); XSSFRow rw1S1 = shet1.createRow(0); // XSSFRow rw0S2=shet2.createRow(0); XSSFRow rw1S2 = shet2.createRow(0); // XSSFRow rw0S3=shet3.createRow(0); XSSFRow rw1S3 = shet3.createRow(0); // XSSFRow rw0S3=shet3.createRow(0); XSSFRow rw1S4 = shet4.createRow(0); // LOOP THROUGH AND WRITE STATIC HEADERS TO THE ELEMENTS for (String headername : arrayHeader) { XSSFCell S1cell = rw1S1.createCell(headerno); S1cell.setCellValue(headername); S1cell.setCellStyle(stylex); XSSFCell S2cell = rw1S2.createCell(headerno); S2cell.setCellValue(headername); S2cell.setCellStyle(stylex); XSSFCell S3cell = rw1S3.createCell(headerno); S3cell.setCellValue(headername); S3cell.setCellStyle(stylex); XSSFCell S4cell = rw1S4.createCell(headerno); S4cell.setCellValue(headername); S4cell.setCellStyle(stylex); headerno++; } String getMonth = "SELECT name FROM month WHERE id='" + month + "'"; conn.rs = conn.st.executeQuery(getMonth); if (conn.rs.next() == true) { monthName = conn.rs.getString(1); } counterPMTCT = counterART = counterPEP = counterHTC = 6; //get the specifc indicator names and the respective section and a count of indicators in each section which dictates the number of columns in the whole excel String getVariables = "SELECT * FROM moh731_indicators ORDER BY id"; conn.rs1 = conn.st1.executeQuery(getVariables); while (conn.rs1.next()) { elementName = conn.rs1.getString("indicator") + " \n" + conn.rs1.getString("code"); if (conn.rs1.getString("section").equals("1. HIV Counselling and Testing")) { counterHTC++; XSSFCell S4cell = rw1S4.createCell(counterHTC); S4cell.setCellValue(elementName); S4cell.setCellStyle(stylex); } else if (conn.rs1.getString("section").equals("2. Prevention of Mother-to-Child Transmission")) { counterPMTCT++; XSSFCell S1cell = rw1S1.createCell(counterPMTCT); S1cell.setCellValue(elementName); S1cell.setCellStyle(stylex); } else if (conn.rs1.getString("section").equals("3. Care and Treatment")) { counterART++; XSSFCell S2cell = rw1S2.createCell(counterART); S2cell.setCellValue(elementName); S2cell.setCellStyle(stylex); } else if (conn.rs1.getString("section").equals("5. Post-Exposure Prophylaxis")) { counterPEP++; XSSFCell S3cell = rw1S3.createCell(counterPEP); S3cell.setCellValue(elementName); S3cell.setCellStyle(stylex); } else { } } XSSFCell S3cella = rw1S1.createCell(49); S3cella.setCellValue("Validation Run"); S3cella.setCellStyle(stylex); XSSFCell S3cell2a = rw1S2.createCell(68); S3cell2a.setCellValue("Validation Run"); S3cell2a.setCellStyle(stylex); XSSFCell S3cell3a = rw1S3.createCell(20); S3cell3a.setCellValue("Validation Run"); S3cell3a.setCellStyle(stylex); XSSFCell S4cell3a = rw1S4.createCell(22); S4cell3a.setCellValue("Validation Run"); S4cell3a.setCellStyle(stylex); // counter=1; System.out.println("pmtct : " + counterPMTCT + " art : " + counterART + " PEP : " + counterPEP + " HTC : " + counterHTC); // counterPMTCT=counterPMTCT-5; // counterART=counterART-5; // counterPEP=counterPEP-5; counterPMTCT1 = counterART1 = counterPEP1 = counterHTC1 = 0; String getData = "SELECT county.County,district.DistrictNom," + subpartnera + ".SubPartnerNom," + subpartnera + ".CentreSanteId," + " moh731.HV0201,moh731.HV0202,moh731.HV0203,moh731.HV0204,moh731.HV0205,moh731.HV0206,moh731.HV0207,moh731.HV0208,moh731.HV0209,moh731.HV0210,moh731.HV0211,moh731.HV0212,moh731.HV0213," + "moh731.HV0214,moh731.HV0215,moh731.HV0216,moh731.HV0217,moh731.HV0218,moh731.HV0219,moh731.HV0220,moh731.HV0221,moh731.HV0224,moh731.HV0225,moh731.HV0226,moh731.HV0227,moh731.HV0228,moh731.HV0229," + " moh731.HV0230,moh731.HV0231,moh731.HV0232,moh731.HV0233,moh731.HV0234,moh731.HV0235,moh731.HV0236,moh731.HV0237,moh731.HV0238,moh731.HV0239,moh731.HV0240,moh731.HV0241,moh731.HV0242," + " moh731.HV0243,moh731.HV0244,moh731.HV0301,moh731.HV0302,moh731.HV0303,moh731.HV0304,moh731.HV0305,moh731.HV0306,moh731.HV0307,moh731.HV0308,moh731.HV0309,moh731.HV0310,moh731.HV0311,moh731.HV0312,moh731.HV0313,moh731.HV0314," + " moh731.HV0315,moh731.HV0316,moh731.HV0317,moh731.HV0318,moh731.HV0319,moh731.HV0320,moh731.HV0321,moh731.HV0322,moh731.HV0323,moh731.HV0324,moh731.HV0325,moh731.HV0326,moh731.HV0327,moh731.HV0328," + " moh731.HV0329,moh731.HV0330,moh731.HV0331,moh731.HV0332,moh731.HV0333,moh731.HV0334,moh731.HV0335,moh731.HV0336,moh731.HV0337,moh731.HV0338,moh731.HV0339,moh731.HV0340,moh731.HV0341," + " moh731.HV0342,moh731.HV0343,moh731.HV0344,moh731.HV0345,moh731.HV0346,moh731.HV0347,moh731.HV0348,moh731.HV0349,moh731.HV0350,moh731.HV0351,moh731.HV0352,moh731.HV0353," + " moh731.HV0354,moh731.HV0355,moh731.HV0904,moh731.HV0905,moh731.HV0370,moh731.HV0371,moh731.HV0372,moh731.HV0373," + " moh731.HV0501,moh731.HV0502,moh731.HV0503,moh731.HV0504,moh731.HV0505,moh731.HV0506,moh731.HV0507,moh731.HV0508,moh731.HV0509,moh731.HV0510,moh731.HV0511,moh731.HV0512,moh731.HV0513,moh731.HV0514," + " moh731.HV0101,moh731.HV0102,moh731.HV0103,moh731.HV0105,moh731.HV0106,moh731.HV0107,moh731.HV0108,moh731.HV0109,moh731.HV0110,moh731.HV0111,moh731.HV0112,moh731.HV0113,moh731.HV0114,moh731.HV0115,moh731.HV0116 " + "," + subpartnera + ".PMTCT," + subpartnera + ".ART," + subpartnera + ".PEP," + subpartnera + ".HTC,isValidated ,IFNULL(ART_highvolume,0) as ART_highvolume, IFNULL(HTC_highvolume,0) as HTC_highvolume, IFNULL(PMTCT_highvolume,0) as PMTCT_highvolume " + " FROM moh731 JOIN " + subpartnera + " ON moh731.SubPartnerID=" + subpartnera + "." + subpartnerid + " " + "JOIN district ON " + subpartnera + ".DistrictID=district.DistrictID " + "JOIN county ON county.CountyID=district.CountyID " + " WHERE moh731.Mois='" + month + "' && moh731.Annee='" + year + "'" + " ORDER BY county.County,district.DistrictNom," + subpartnera + "." + subpartnerid + ""; conn.rs = conn.st.executeQuery(getData); int valuesstartrow = 7; System.out.println("|__" + getData); while (conn.rs.next()) { counter++; county = conn.rs.getString(1); district = conn.rs.getString(2); facilityname = conn.rs.getString(3); mflcode = conn.rs.getString(4); arthv = conn.rs.getInt("ART_highvolume"); htchv = conn.rs.getInt("HTC_highvolume"); pmtcthv = conn.rs.getInt("PMTCT_highvolume"); System.out.println("@@@@@@" + arthv + "_" + htchv + "_" + pmtcthv); //ART High Volume HTC High Volume PMTCT High Volume String basicDetails = county + "@" + district + "@" + facilityname + "@" + mflcode + "@" + arthv + "@" + htchv + "@" + pmtcthv; String arrayDetails[] = basicDetails.split("@"); if (conn.rs.getInt(subpartnera + ".PMTCT") == 1) { counterPMTCT1++; XSSFRow rw2S1 = shet1.createRow(counterPMTCT1); int facilno = 0; for (int b = 0; b < arrayDetails.length; b++) { XSSFCell S3cell = rw2S1.createCell(facilno); if (b < 3) { S3cell.setCellValue(arrayDetails[b]); } else { S3cell.setCellValue(Integer.parseInt(arrayDetails[b])); } S3cell.setCellStyle(stborder); facilno++; } int pos; for (int i = valuesstartrow; i <= counterPMTCT; i++) { XSSFCell S3cell = rw2S1.createCell(i); pos = i + 1; //System.out.println("cell no 1 : "+i+" value no : "+pos); S3cell.setCellValue(conn.rs.getInt(pos - 3)); S3cell.setCellStyle(stborder); } isValidated = conn.rs.getString("isValidated"); if (isValidated.equals("1")) { isValidated = "Yes"; } else { isValidated = "No"; } XSSFCell S3cell = rw2S1.createCell(49); S3cell.setCellValue(isValidated); S3cell.setCellStyle(stborder); } if (conn.rs.getInt(subpartnera + ".ART") == 1) { counterART1++; XSSFRow rw2S2 = shet2.createRow(counterART1); int facilno = 0; for (int b = 0; b < arrayDetails.length; b++) { XSSFCell S3cell = rw2S2.createCell(facilno); if (b < 3) { S3cell.setCellValue(arrayDetails[b]); } else { S3cell.setCellValue(Integer.parseInt(arrayDetails[b])); } S3cell.setCellStyle(stborder); facilno++; } int pos; for (int i = valuesstartrow; i <= counterART; i++) { XSSFCell S3cell = rw2S2.createCell(i); pos = i + 43; System.out.println("cell no 2 : " + i + " value no : " + pos); S3cell.setCellValue(conn.rs.getInt(pos - 3)); S3cell.setCellStyle(stborder); } isValidated = conn.rs.getString("isValidated"); if (isValidated.equals("1")) { isValidated = "Yes"; } else { isValidated = "No"; } XSSFCell S3cell = rw2S2.createCell(68); S3cell.setCellValue(isValidated); S3cell.setCellStyle(stborder); } if (conn.rs.getInt(subpartnera + ".PEP") == 1) { counterPEP1++; XSSFRow rw2S3 = shet3.createRow(counterPEP1); int facilno = 0; for (int b = 0; b < arrayDetails.length; b++) { XSSFCell S3cell = rw2S3.createCell(facilno); if (b < 3) { S3cell.setCellValue(arrayDetails[b]); } else { S3cell.setCellValue(Integer.parseInt(arrayDetails[b])); } S3cell.setCellStyle(stborder); facilno++; } int pos; for (int i = valuesstartrow; i <= counterPEP; i++) { XSSFCell S3cell = rw2S3.createCell(i); pos = i + 104; //System.out.println("cell no 3 : "+i+" value no : "+pos); S3cell.setCellValue(conn.rs.getInt(pos - 3)); S3cell.setCellStyle(stborder); } isValidated = conn.rs.getString("isValidated"); if (isValidated.equals("1")) { isValidated = "Yes"; } else { isValidated = "No"; } XSSFCell S3cell = rw2S3.createCell(20); S3cell.setCellValue(isValidated); S3cell.setCellStyle(stborder); } //HTC_____________________added 2016 if (conn.rs.getInt(subpartnera + ".HTC") == 1) { counterHTC1++; XSSFRow rw2S4 = shet4.createRow(counterHTC1); int facilno = 0; for (int b = 0; b < arrayDetails.length; b++) { XSSFCell S3cell = rw2S4.createCell(facilno); if (b < 3) { S3cell.setCellValue(arrayDetails[b]); } else { S3cell.setCellValue(Integer.parseInt(arrayDetails[b])); } S3cell.setCellStyle(stborder); facilno++; } String pos; int cellpos = 0; for (int i = 1; i <= 16; i++) { if (i != 4) { cellpos++; XSSFCell S4cell = rw2S4.createCell(cellpos + 6); pos = "0" + i; if (i >= 10) { pos = "" + i; } S4cell.setCellValue(conn.rs.getInt("moh731.HV01" + pos)); S4cell.setCellStyle(stborder); } } isValidated = conn.rs.getString("isValidated"); if (isValidated.equals("1")) { isValidated = "Yes"; } else { isValidated = "No"; } XSSFCell S4cell = rw2S4.createCell(22); S4cell.setCellValue(isValidated); S4cell.setCellStyle(stborder); } System.out.println("counter : " + counter); } if (conn.st != null) { conn.st.close(); } if (conn.st1 != null) { conn.st1.close(); } // if(conn.st2!=null){conn.st2.close();} if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } // if(conn.rs2!=null){conn.rs2.close();} if (conn.conn != null) { conn.conn.close(); } IdGenerator IG = new IdGenerator(); createdOn = IG.CreatedOn(); // 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=MOH731_RAW_DATA_REPORT_FOR_" + year.trim() + "(" + monthName.trim() + ")_CREATED_" + createdOn.trim() + ".xlsx"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:reports.moh731_subSection.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {/*w ww . j av a 2 s .co m*/ response.setContentType("text/html;charset=UTF-8"); //a page to get Report of all the servlets String year = "2015"; String month = "4"; String county = "1"; String form = "moh731"; if (request.getParameter("year") != null) { year = request.getParameter("year"); } if (request.getParameter("county") != null) { county = request.getParameter("county"); } if (request.getParameter("month") != null) { month = request.getParameter("month"); } if (request.getParameter("form") != null) { form = request.getParameter("form"); } String facilitywhere = ""; String countywhere = ""; String districtwhere = ""; String reporttype = ""; dbConn conn = new dbConn(); //an array to store haeder information. //the header information should appear only if a certain parameters are met //The parameters listed in here can be removed if the report type doesnt require certain parameters ArrayList Headerorgunits = new ArrayList(); Headerorgunits.add("COUNTY"); Headerorgunits.add("SUB-COUNTY"); Headerorgunits.add("FACILITY"); Headerorgunits.add("MFL CODE"); //An arralist to store a list of columns that will be selected from the database ArrayList dbcolumns = new ArrayList(); ArrayList labels = new ArrayList(); ArrayList tablename = new ArrayList(); ArrayList iscumulative = new ArrayList(); ArrayList ispercent = new ArrayList(); // ArrayList isactive=new ArrayList(); //An arralist to store a list of worksheets that will be selected from the sections ArrayList worksheets = new ArrayList(); //An arralist to store distinct worksheets. This will be derived from the the sections column ArrayList distinctsheets = new ArrayList(); String selectdistinctworksheet = "select distinct(section) from pivottable where form='" + form + "' and active='1'"; conn.rs = conn.st.executeQuery(selectdistinctworksheet); while (conn.rs.next()) { //add the name of distinct sections distinctsheets.add(conn.rs.getString(1).replace("/", "_")); } String getattribs = "select indicator,label,section,cumulative,percentage,active ,shortlabel from pivottable where form='" + form + "' order by tableid, section"; conn.rs = conn.st.executeQuery(getattribs); while (conn.rs.next()) { //add active indicators only if (conn.rs.getString("active").equals("1")) { System.out.println(conn.rs.getString("indicator") + ""); //add indicator dbcolumns.add(conn.rs.getString("indicator")); //add label if (form.equals("moh731")) { labels.add(conn.rs.getString("shortlabel") + " \n " + conn.rs.getString("label")); } else { labels.add(conn.rs.getString("label")); } //add worksheets worksheets.add(conn.rs.getString("section").replace("/", "_")); String perc = "0"; String cum = "0"; if (conn.rs.getString("cumulative") != null) { iscumulative.add(conn.rs.getString("cumulative")); } else { iscumulative.add(cum); } if (conn.rs.getString("percentage") != null) { ispercent.add(conn.rs.getString("percentage")); } else { ispercent.add(perc); } } //end of active } //end of worksheet //if String perfacilselect = "select Upper(County) as County , Upper(DistrictNom) as District , UPPER(SubPartnerNom) as facility ,CentreSanteId as mflcode , "; //-------------------------------------------------------------------------------------------- // PREPARE SELECT //-------------------------------------------------------------------------------------------- //prepare selects for (int a = 0; a < dbcolumns.size(); a++) { //if the indicator is a percent, get an avaerage if (ispercent.get(a).equals("1")) { perfacilselect += " AVG(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } else if (iscumulative.get(a).equals("1")) { perfacilselect += " " + dbcolumns.get(a) + " as " + dbcolumns.get(a); } else { perfacilselect += " SUM(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } //if the item is not the last, append a comma if (a < dbcolumns.size() - 1) { perfacilselect += " ,"; } } //------------------------------------------------------------------------------------ // FROM //------------------------------------------------------------------------------------ perfacilselect += " from " + form + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on " + form + ".SubPartnerID = subpartnera.SubPartnerID "; //------------------------------------------------------------------------------------------ // WHERE //------------------------------------------------------------------------------------------ //----------------------------------------------------------------------------------------- //GROUP BY //---------------------------------------------------------------------------------------- perfacilselect += " group by subpartnera.SubPartnerID"; System.out.println(perfacilselect); //______________________________________________________________________________________ // NOW CREATE THE WORKSHEETS //______________________________________________________________________________________ XSSFWorkbook wb = new XSSFWorkbook(); //______________________________________________________________________________________ //______________________________________________________________________________________ XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); 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.GREY_25_PERCENT.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_LEFT); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); for (int b = 0; b < distinctsheets.size(); b++) { XSSFSheet shet = wb.createSheet(distinctsheets.get(b).toString().toUpperCase()); //create headers for that worksheet XSSFRow rw = shet.createRow(1); int headercellpos = 0; //create the orgunit header eg COUNTY | SUBCOUNTY | FACILITY for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(Headerorgunits.get(e).toString()); cell0.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(e, 6000); } //create the indicators header eg HV0101 | HIV 09676 | TOTAL for (int c = 0; c < dbcolumns.size(); c++) { //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(b))) { shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(labels.get(c).toString()); cell0.setCellStyle(stylex); headercellpos++; } //end of comparing if } //end of for loop } conn.rs = conn.st.executeQuery(perfacilselect); String sectioncopy = ""; int sheetpos = 0; int rowpos = 2; while (conn.rs.next()) { //-----------------INSIDE THE DATA FORM--------------------------------- //if the section changes, change the position of the worksheet too //also, reset the position counter to begin from 2 again. XSSFSheet shet = null; // if(--!sectioncopy.equals(shet)){} for (int g = 0; g < distinctsheets.size(); g++) { shet = wb.getSheetAt(g); int colpos = 0; //the fourth cell should XSSFRow rw = shet.createRow(rowpos); for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getString(e + 1)); cell0.setCellStyle(style2); colpos++; } //_________________________________________________________________ //VALUES //_________________________________________________________________ //create the indicators values eg 90 | 45 | 356 for (int c = 0; c < dbcolumns.size(); c++) { //get the section of the current dbcolumn //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(g))) { XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getString(dbcolumns.get(c).toString())); cell0.setCellStyle(stborder); colpos++; } //end of comparing if } //end of for loop } rowpos++; } 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=" + form.toUpperCase() + "_REPORT.xlsx"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(allStaticReportsMonthly.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.multiplesitesreport.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods./*from www .j a va 2 s . c o m*/ * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try { dbConn conn = new dbConn(); wb = new HSSFWorkbook(); HSSFSheet shet2 = null; String year = ""; String site = ""; String period = ""; String cbo = ""; String startdate = "2015-01-01"; String enddate = "2015-03-30"; startdate = request.getParameter("startdate"); enddate = request.getParameter("enddate"); String getdistinctsites = "select distinct (site) as site, year,period ,cbo from backgroundinfor where ass_date between '" + startdate + "' and '" + enddate + "' order by site"; ArrayList siteids = new ArrayList(); ArrayList years = new ArrayList(); ArrayList periods = new ArrayList(); ArrayList cbos = new ArrayList(); conn.rs = conn.st.executeQuery(getdistinctsites); while (conn.rs.next()) { siteids.add(conn.rs.getString(1)); years.add(conn.rs.getString(2)); periods.add(conn.rs.getString("period")); cbos.add(conn.rs.getString("cbo")); } // year=request.getParameter("year"); //site=request.getParameter("sitecbo"); //period=request.getParameter("period"); //cbo=request.getParameter("staffcbo"); String sitename = ""; String cboname = ""; //begin a loop that will create as many reports as possible for (int u = 0; u < siteids.size(); u++) { conn.rs = conn.st.executeQuery("select cbo from cbo where cboid='" + cbos.get(u) + "'"); if (conn.rs.next()) { cboname = conn.rs.getString(1); } conn.rs = conn.st .executeQuery("select site_name from sites where site_id='" + siteids.get(u) + "'"); if (conn.rs.next()) { sitename = conn.rs.getString(1); } HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Cambria"); // font.setItalic(true); font.setBoldweight((short) 02); font.setColor(HSSFColor.BLACK.index); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setWrapText(true); style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); style.setAlignment(style.ALIGN_CENTER); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //%%%%%%%%%%%%%%%%HEADER FONTS AND COLORATION%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% HSSFFont font_header = wb.createFont(); font_header.setFontHeightInPoints((short) 10); font_header.setFontName("Eras Bold ITC"); // font.setItalic(true); font_header.setBoldweight((short) 05); font_header.setColor(HSSFColor.BLACK.index); CellStyle style_header = wb.createCellStyle(); style_header.setFont(font_header); style_header.setWrapText(true); style_header.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); style_header.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style_header.setAlignment(style_header.ALIGN_CENTER); // style_header.setBorderBottom(HSSFCellStyle.BORDER_THIN); // style_header.setBorderTop(HSSFCellStyle.BORDER_THIN); // style_header.setBorderRight(HSSFCellStyle.BORDER_THIN); // style_header.setBorderLeft(HSSFCellStyle.BORDER_THIN); //%%%%%%%%%%%%%%%%%%%%%%%%%DATA FONT%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% //font data HSSFFont datafont = wb.createFont(); datafont.setBoldweight((short) 03); datafont.setColor(HSSFColor.BLACK.index); datafont.setFontHeightInPoints((short) 10); datafont.setFontName("Cambria"); datafont.setItalic(true); //bold font HSSFFont bolfont = wb.createFont(); bolfont.setBoldweight((short) 05); bolfont.setColor(HSSFColor.BLACK.index); bolfont.setFontHeightInPoints((short) 12); bolfont.setFontName("Cambria"); //=======INNER DATA STYLING=========================== CellStyle innerdata_style = wb.createCellStyle(); innerdata_style.setFont(datafont); innerdata_style.setWrapText(true); innerdata_style.setAlignment(innerdata_style.ALIGN_CENTER); innerdata_style.setFillForegroundColor(HSSFColor.WHITE.index); innerdata_style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN); innerdata_style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle lastcellrighborder = wb.createCellStyle(); lastcellrighborder.setFont(datafont); lastcellrighborder.setWrapText(true); lastcellrighborder.setAlignment(lastcellrighborder.ALIGN_CENTER); lastcellrighborder.setFillForegroundColor(HSSFColor.WHITE.index); lastcellrighborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // lastcellrighborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); // lastcellrighborder.setBorderTop(HSSFCellStyle.BORDER_THIN); lastcellrighborder.setBorderRight(HSSFCellStyle.BORDER_THIN); // lastcellrighborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); lastcellrighborder.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle innerdata_style2 = wb.createCellStyle(); innerdata_style2.setFont(bolfont); innerdata_style2.setWrapText(true); innerdata_style2.setAlignment(innerdata_style.ALIGN_LEFT); innerdata_style2.setFillForegroundColor(HSSFColor.WHITE.index); innerdata_style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN); innerdata_style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //Code colors CellStyle lg = wb.createCellStyle(); lg.setFont(bolfont); lg.setWrapText(true); lg.setAlignment(lg.ALIGN_CENTER); lg.setFillForegroundColor(HSSFColor.GREEN.index); lg.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); lg.setBorderBottom(HSSFCellStyle.BORDER_THIN); lg.setBorderTop(HSSFCellStyle.BORDER_THIN); lg.setBorderRight(HSSFCellStyle.BORDER_THIN); lg.setBorderLeft(HSSFCellStyle.BORDER_THIN); lg.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle Y = wb.createCellStyle(); Y.setFont(bolfont); Y.setWrapText(true); Y.setAlignment(Y.ALIGN_CENTER); Y.setFillForegroundColor(HSSFColor.YELLOW.index); Y.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); Y.setBorderBottom(HSSFCellStyle.BORDER_THIN); Y.setBorderTop(HSSFCellStyle.BORDER_THIN); Y.setBorderRight(HSSFCellStyle.BORDER_THIN); Y.setBorderLeft(HSSFCellStyle.BORDER_THIN); Y.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle R = wb.createCellStyle(); R.setFont(bolfont); R.setWrapText(true); R.setAlignment(R.ALIGN_CENTER); R.setFillForegroundColor(HSSFColor.RED.index); R.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); R.setBorderBottom(HSSFCellStyle.BORDER_THIN); R.setBorderTop(HSSFCellStyle.BORDER_THIN); R.setBorderRight(HSSFCellStyle.BORDER_THIN); R.setBorderLeft(HSSFCellStyle.BORDER_THIN); R.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //=======INNER LEFT DATA STYLING=========================== CellStyle binnerdata_style2 = wb.createCellStyle(); binnerdata_style2.setFont(datafont); binnerdata_style2.setWrapText(true); binnerdata_style2.setAlignment(binnerdata_style2.ALIGN_LEFT); binnerdata_style2.setFillForegroundColor(HSSFColor.WHITE.index); binnerdata_style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); binnerdata_style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderTop(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderRight(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //create a header //=======================Domainname styles CellStyle dnamestyle = wb.createCellStyle(); dnamestyle.setFont(bolfont); dnamestyle.setWrapText(true); dnamestyle.setAlignment(dnamestyle.ALIGN_LEFT); dnamestyle.setFillForegroundColor(HSSFColor.WHITE.index); dnamestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); dnamestyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderTop(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderRight(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); dnamestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); shet2 = wb.createSheet(sitename.toUpperCase()); shet2.setColumnWidth(0, 10000); shet2.setColumnWidth(1, 5000); shet2.setColumnWidth(2, 5000); shet2.setColumnWidth(3, 5000); shet2.setColumnWidth(4, 8000); shet2.setColumnWidth(5, 8000); //create header one HSSFRow header = shet2.createRow(0); header.setHeightInPoints(30); HSSFCell cel1 = header.createCell(0); cel1.setCellValue("APHIAplus NURU YA BONDE"); cel1.setCellStyle(style); for (int b = 1; b <= 5; b++) { cel1 = header.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(style); } shet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); //create header two HSSFRow header2 = shet2.createRow(1); header2.setHeightInPoints(28); HSSFCell cel2 = null; for (int b = 1; b <= 5; b++) { cel2 = header2.createCell(b); cel2.setCellValue(""); cel2.setCellStyle(style); } cel2 = header2.createCell(0); cel2.setCellValue("OVC LIP SUPPORT SUPERVISION DASH BOARD"); cel2.setCellStyle(style); shet2.addMergedRegion(new CellRangeAddress(1, 1, 0, 5)); //cbo name //create header three HSSFRow header3 = shet2.createRow(2); HSSFCell cel3 = header3.createCell(0); cel3.setCellValue("Name of LIP/CBO"); cel3.setCellStyle(innerdata_style2); HSSFCell cel4 = header3.createCell(1); cel4.setCellValue("" + cboname); cel4.setCellStyle(innerdata_style); //blank cells for purpose of clear worksheet only for (int x = 2; x <= 3; x++) { HSSFCell cel = header3.createCell(x); cel.setCellValue(""); cel.setCellStyle(innerdata_style); } HSSFCell cel5 = header3.createCell(4); cel5.setCellValue("Site Visited:"); cel5.setCellStyle(innerdata_style2); HSSFCell cel6 = header3.createCell(5); cel6.setCellValue("" + sitename); cel6.setCellStyle(lastcellrighborder); //add the width of this column //create a blank row whose last cell has a border HSSFRow blankrw = shet2.createRow(3); for (int z = 0; z < 5; z++) { HSSFCell cl = blankrw.createCell(z); cl.setCellValue(""); cl.setCellStyle(innerdata_style); } HSSFCell cl = blankrw.createCell(5); cl.setCellValue(""); cl.setCellStyle(lastcellrighborder); //==========DATE OF VISIT String mywhere = "site='" + siteids.get(u) + "' and period='" + periods.get(u) + "' and year='" + years.get(u) + "' "; String supervisor = ""; String dateofvisit = ""; String strengths = ""; String constraints = ""; String loadbasicdetails = "select * from backgroundinfor join staff on backgroundinfor.supervisor=staff.staff_id where " + mywhere + " "; System.out.println(loadbasicdetails); conn.rs = conn.st.executeQuery(loadbasicdetails); while (conn.rs.next()) { supervisor = conn.rs.getString("fname") + " " + conn.rs.getString("mname"); dateofvisit = conn.rs.getString("ass_date"); strengths = conn.rs.getString("strengths"); constraints = conn.rs.getString("constraints"); } //================Create the second header================= //create header three HSSFRow header4 = shet2.createRow(4); HSSFCell cel = header4.createCell(0); cel.setCellValue("Date of Visit"); cel.setCellStyle(innerdata_style2); HSSFCell cell = header4.createCell(1); cell.setCellValue("" + dateofvisit); cell.setCellStyle(innerdata_style); //blank cells for purpose of clear worksheet only for (int x = 2; x <= 3; x++) { HSSFCell ceel = header4.createCell(x); ceel.setCellValue(""); ceel.setCellStyle(innerdata_style); } HSSFCell cell5 = header4.createCell(4); cell5.setCellValue("Supervision Team Lead:"); cell5.setCellStyle(innerdata_style2); HSSFCell cell6 = header4.createCell(5); cell6.setCellValue("" + supervisor); cell6.setCellStyle(lastcellrighborder); //another blank row HSSFRow blankrw2 = shet2.createRow(5); for (int z = 0; z < 5; z++) { HSSFCell cl2 = blankrw2.createCell(z); cl2.setCellValue(""); cl2.setCellStyle(innerdata_style); } HSSFCell cl2 = blankrw2.createCell(5); cl2.setCellValue(""); cl2.setCellStyle(lastcellrighborder); //create a header String theaderar[] = { "Assesment Domain", "LG", "Y", "R", "Comments/Action", "" }; HSSFRow theader = shet2.createRow(6); for (int x = 0; x < theaderar.length; x++) { HSSFCell tcel = theader.createCell(x); tcel.setCellValue(theaderar[x]); if (theaderar[x].equalsIgnoreCase("LG")) { tcel.setCellStyle(lg); } else if (theaderar[x].equalsIgnoreCase("Y")) { tcel.setCellStyle(Y); } else if (theaderar[x].equalsIgnoreCase("R")) { tcel.setCellStyle(R); } else { tcel.setCellStyle(style); } } shet2.addMergedRegion(new CellRangeAddress(6, 6, 4, 5)); //SECTION A HEADER HSSFRow seca = shet2.createRow(7); HSSFCell tcel1 = seca.createCell(0); tcel1.setCellValue("Section A: Data management and Reporting Systems"); tcel1.setCellStyle(style); for (int b = 1; b <= 5; b++) { cel1 = seca.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(style); } shet2.addMergedRegion(new CellRangeAddress(7, 7, 0, 5)); String gettables = "SELECT domain_name,domains.domain_id as domainid,section_name,domains.section_id as secid,value as domainvalue,aggregate_sum,period,year,site FROM domains join sections on domains.section_id=sections.section_id join domain_totals on domains.domain_id=domain_totals.domainid where " + mywhere + " order by domainid"; System.out.println(gettables); conn.rs = conn.st.executeQuery(gettables); int rwcount = 8; HSSFRow rwx = null; HSSFCell celx = null; String sectioncopy = ""; while (conn.rs.next()) { if (sectioncopy.equals("")) { sectioncopy = conn.rs.getString("section_name"); } //if the section has changed if (!sectioncopy.equals(conn.rs.getString("section_name"))) { //create a section header //``````````````````````````````INNER SECTION HEADERS`````````````````` //``````````````````````````````INNER SECTION HEADERS`````````````````` //``````````````````````````````INNER SECTION HEADERS`````````````````` HSSFRow secb = shet2.createRow(rwcount); HSSFCell t = secb.createCell(0); t.setCellValue("Section " + conn.rs.getString("section_name")); t.setCellStyle(style); //for purpose of merging for (int b = 1; b <= 5; b++) { cel1 = secb.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(style); } //equalize copy and current value sectioncopy = conn.rs.getString("section_name"); shet2.addMergedRegion(new CellRangeAddress(rwcount, rwcount, 0, 5)); //increment rowcount to skip the current row rwcount++; } String valu[] = { conn.rs.getString("domain_name"), "", "", "", "", "" }; rwx = shet2.createRow(rwcount); for (int t = 0; t < valu.length; t++) { celx = rwx.createCell(t); celx.setCellValue("" + valu[t]); celx.setCellStyle(dnamestyle); } shet2.addMergedRegion(new CellRangeAddress(rwcount, rwcount, 4, 5)); //get the value of percentange achievement per domian //multiply by 100 //round off float domainvalue = conn.rs.getFloat("domainvalue"); domainvalue = domainvalue * 100; //BigDecimal bd=new BigDecimal(domainvalue).setScale(0,RoundingMode.HALF_EVEN); //domainval=bd.doubleValue(); domainvalue = Math.round(domainvalue); //determine the cell to print data on if (domainvalue >= 75) { celx = rwx.createCell(1); celx.setCellValue("" + domainvalue + "%"); celx.setCellStyle(lg); } else if (domainvalue >= 60 && domainvalue < 75) { celx = rwx.createCell(2); celx.setCellValue("" + domainvalue + "%"); celx.setCellStyle(Y); } else if (domainvalue < 60) { celx = rwx.createCell(3); celx.setCellValue("" + domainvalue + "%"); celx.setCellStyle(R); } rwcount++; } ///=========================end of while loop //====================STRENGTHS=========================== HSSFRow secb = shet2.createRow(rwcount); //for purpose of merging for (int b = 1; b <= 5; b++) { cel1 = secb.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(style); } HSSFCell t = secb.createCell(0); t.setCellValue("What has worked well and key areas of strengths observed"); t.setCellStyle(style); shet2.addMergedRegion(new CellRangeAddress(rwcount, rwcount, 0, 5)); rwcount++; HSSFRow str = shet2.createRow(rwcount); for (int b = 1; b <= 5; b++) { cel1 = str.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(dnamestyle); } HSSFCell t1 = str.createCell(0); t1.setCellValue("" + strengths); t1.setCellStyle(dnamestyle); shet2.addMergedRegion(new CellRangeAddress(rwcount, rwcount, 0, 5)); //for purpose of merging str.setHeightInPoints(60); rwcount++; //=======Contraints HSSFRow sec3 = shet2.createRow(rwcount); //for purpose of merging for (int b = 1; b <= 5; b++) { cel1 = sec3.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(style); } HSSFCell t2 = sec3.createCell(0); t2.setCellValue("Critical consraints affecting quality programming and data management"); t2.setCellStyle(style); shet2.addMergedRegion(new CellRangeAddress(rwcount, rwcount, 0, 5)); rwcount++; HSSFRow str2 = shet2.createRow(rwcount); for (int b = 1; b <= 5; b++) { cel1 = str2.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(dnamestyle); } HSSFCell t4 = str2.createCell(0); t4.setCellValue("" + constraints); t4.setCellStyle(dnamestyle); shet2.addMergedRegion(new CellRangeAddress(rwcount, rwcount, 0, 5)); str2.setHeightInPoints(50); rwcount++; //a line of codes String codes[] = { "LG - Meets Expectations (>=75%); ", " Y- Needs Improvement (60%- 74%);", "R - Needs Urgent Attention (<=59%);" }; HSSFRow rwl = shet2.createRow(rwcount); HSSFCell ce = rwl.createCell(0); ce.setCellValue("CODES"); ce.setCellStyle(dnamestyle); for (int b = 0; b < codes.length; b++) { ce = rwl.createCell(b + 1); ce.setCellValue("" + codes[b]); if (b == 0) { ce.setCellStyle(lg); } else if (b == 1) { ce.setCellStyle(Y); } else { ce.setCellStyle(R); } } ce = rwl.createCell(4); ce.setCellValue(""); ce.setCellStyle(dnamestyle); ce = rwl.createCell(5); ce.setCellValue(""); ce.setCellStyle(dnamestyle); shet2.addMergedRegion(new CellRangeAddress(rwcount, rwcount, 4, 5)); } //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=OVC_LIP_REPORT_Multiple_SITES_" + startdate + "_" + enddate + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(basicreports.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.new711report.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {//from w w w. j a v a 2 s . c om response.setContentType("text/html;charset=UTF-8"); //a page to get Report of all the servlets String year = "2016"; String month = "4"; String county = ""; String form = "moh711_new"; if (request.getParameter("year") != null) { year = request.getParameter("year"); } if (request.getParameter("county") != null) { county = request.getParameter("county"); } if (request.getParameter("month") != null) { month = request.getParameter("month"); } if (request.getParameter("form") != null) { form = request.getParameter("form"); } String pivotform = form; if (form.equalsIgnoreCase("MOH 731")) { form = "MOH731"; } if (form.equalsIgnoreCase("MOH 711 (New)")) { form = "moh711_new"; } String facilitywhere = ""; String yearwhere = ""; String monthwhere = ""; String countywhere = ""; String districtwhere = ""; String reporttype = ""; if (!year.equals("")) { yearwhere = " and Annee = '" + year + "'"; } if (!county.equals("")) { countywhere = " and countyid = '" + county + "'"; } if (!month.equals("")) { monthwhere = " and Mois = '" + month + "'"; } dbConn conn = new dbConn(); //an array to store haeder information. //the header information should appear only if a certain parameters are met //The parameters listed in here can be removed if the report type doesnt require certain parameters ArrayList Headerorgunits = new ArrayList(); Headerorgunits.add("COUNTY"); Headerorgunits.add("SUB-COUNTY"); Headerorgunits.add("FACILITY"); Headerorgunits.add("MFL CODE"); //An arralist to store a list of columns that will be selected from the database ArrayList dbcolumns = new ArrayList(); ArrayList labels = new ArrayList(); ArrayList tablename = new ArrayList(); ArrayList iscumulative = new ArrayList(); ArrayList ispercent = new ArrayList(); // ArrayList isactive=new ArrayList(); //An arralist to store a list of worksheets that will be selected from the sections and the respective service area to determine the facilities whose data will appear in that sheet ArrayList worksheets = new ArrayList(); //An arralist to store distinct worksheets. This will be derived from the the sections column ArrayList distinctsheets = new ArrayList(); ArrayList distinctservicearea = new ArrayList(); String selectdistinctworksheet = "select section,servicearea from pivottable where form='" + form.replace("_", "") + "' and active='1' group by section order by order_per_form"; conn.rs = conn.st.executeQuery(selectdistinctworksheet); while (conn.rs.next()) { //add the name of distinct sections distinctsheets.add(conn.rs.getString(1).replace("/", "_")); String servicearea = " 2=2 "; if (conn.rs.getString(2) != null) { servicearea = " " + conn.rs.getString(2) + "=1"; } distinctservicearea.add(servicearea); } String getattribs = "select indicator,label,section,cumulative,percentage,active ,shortlabel from pivottable where form='" + form.replace("_", "") + "' order by order_per_form, section"; conn.rs = conn.st.executeQuery(getattribs); while (conn.rs.next()) { //add active indicators only if (conn.rs.getString("active").equals("1")) { System.out.println(conn.rs.getString("indicator") + ""); //add indicator dbcolumns.add(conn.rs.getString("indicator")); //add label if (form.equals("moh731")) { labels.add(conn.rs.getString("shortlabel") + " \n " + conn.rs.getString("label")); } else { labels.add(conn.rs.getString("label")); } //add worksheets worksheets.add(conn.rs.getString("section").replace("/", "_")); String perc = "0"; String cum = "0"; if (conn.rs.getString("cumulative") != null) { iscumulative.add(conn.rs.getString("cumulative")); } else { iscumulative.add(cum); } if (conn.rs.getString("percentage") != null) { ispercent.add(conn.rs.getString("percentage")); } else { ispercent.add(perc); } } //end of active } //end of pivot table active //if String perfacilselect = "select Upper(County) as County , Upper(DistrictNom) as District , UPPER(SubPartnerNom) as facility ,CentreSanteId as mflcode , district.CountyID as countyid , "; //-------------------------------------------------------------------------------------------- // PREPARE SELECT //-------------------------------------------------------------------------------------------- //prepare selects for (int a = 0; a < dbcolumns.size(); a++) { //if the indicator is a percent, get an avaerage if (ispercent.get(a).equals("1")) { perfacilselect += " AVG(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } else if (iscumulative.get(a).equals("1")) { perfacilselect += " " + dbcolumns.get(a) + " as " + dbcolumns.get(a); } else { perfacilselect += " SUM(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } //if the item is not the last, append a comma //if(a<dbcolumns.size()-1){ perfacilselect += " ,"; // } } //------------------------------------------------------------------------------------ // FROM //------------------------------------------------------------------------------------ perfacilselect += " isValidated as Form_Validated from " + form + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on " + form + ".SubPartnerID = subpartnera.SubPartnerID "; //------------------------------------------------------------------------------------------ // WHERE //------------------------------------------------------------------------------------------ perfacilselect += " where 1=1 " + monthwhere + yearwhere; //----------------------------------------------------------------------------------------- //GROUP BY //---------------------------------------------------------------------------------------- perfacilselect += " group by subpartnera.SubPartnerID"; //System.out.println(perfacilselect); //______________________________________________________________________________________ // NOW CREATE THE WORKSHEETS //______________________________________________________________________________________ XSSFWorkbook wb = new XSSFWorkbook(); //______________________________________________________________________________________ //______________________________________________________________________________________ XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); 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.GREY_25_PERCENT.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_LEFT); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); for (int b = 0; b < distinctsheets.size(); b++) { XSSFSheet shet = wb.createSheet(distinctsheets.get(b).toString().toUpperCase()); //create headers for that worksheet XSSFRow rw = shet.createRow(1); int headercellpos = 0; //create the orgunit header eg COUNTY | SUBCOUNTY | FACILITY for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(Headerorgunits.get(e).toString()); cell0.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(e, 6000); } //create the indicators header eg HV0101 | HIV 09676 | TOTAL for (int c = 0; c < dbcolumns.size(); c++) { //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(b))) { shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(labels.get(c).toString()); cell0.setCellStyle(stylex); headercellpos++; } //end of comparing if } //end of for loop //create is validated header shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue("Form Validated ?"); cell0.setCellStyle(stylex); headercellpos++; } String sectioncopy = ""; int sheetpos = 0; int rowpos = 2; //-----------------INSIDE THE DATA FORM--------------------------------- //if the section changes, change the position of the worksheet too //also, reset the position counter to begin from 2 again. XSSFSheet shet = null; // if(--!sectioncopy.equals(shet)){} //create the org unit data values e.g BARINGO | BARINGO CENTRAL |KABARNET DISTRICT HOSPITAL | MFL CODE for (int g = 0; g < distinctsheets.size(); g++) { shet = wb.getSheetAt(g); int colpos = 0; String finalquery = perfacilselect.replace("1=1", distinctservicearea.get(g).toString()); System.out.println("" + finalquery); conn.rs = conn.st.executeQuery(finalquery); while (conn.rs.next()) { //the fourth cell should XSSFRow rw = shet.createRow(rowpos); for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(colpos); //for mfl code, last header, print integers if (Headerorgunits.get(e).toString().equals("MFL CODE")) { cell0.setCellValue(conn.rs.getInt(e + 1)); } else { cell0.setCellValue(conn.rs.getString(e + 1)); } cell0.setCellStyle(style2); colpos++; } //_________________________________________________________________ //VALUES //_________________________________________________________________ //create the indicators values eg 90 | 45 | 356 for (int c = 0; c < dbcolumns.size(); c++) { //get the section of the current dbcolumn //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(g))) { XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getInt(dbcolumns.get(c).toString())); cell0.setCellStyle(stborder); colpos++; } //end of comparing if } //end of for loop String isvalidated = "Yes"; if (conn.rs.getString("Form_Validated").equals("0")) { isvalidated = "No"; } XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(isvalidated); cell0.setCellStyle(stborder); colpos++; rowpos++; colpos = 0; } rowpos = 2; } IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); System.out.println("" + form.toUpperCase().trim() + "_REPORT_FOR_" + year.trim() + "(" + month.trim() + ")_CREATED_" + createdOn.trim() + ".xlsx"); 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=" + form.toUpperCase().trim() + "_REPORT_FOR_" + year.trim() + "(" + month.trim() + ")_CREATED_" + createdOn.trim() + ".xlsx"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(allStaticReportsMonthly.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.notReached.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException, InvalidFormatException { session = request.getSession();/*from w w w . j a v a2 s . c o m*/ dbConn conn = new dbConn(); position = 1; String reportHeader[] = ("COUNTY NAME ,PARTNER NAME,DISTRICT NAME, DIC NAME, GROUP NAME,CLIENT FULL NAME ," + " CCC NO. , MOBILE NUMBER , GENDER , DATE OF BIRTH , MARITAL STATUS , EMPLOYMENT STATUS ," + "EDUCATION LEVEL , ART STATUS , SERVICE PROVIDER NAME , HEALTH FACILITY, LESSONS ATTENDED,AGE BRACKET, Knowledge of HIV Status," + "Partner HIV Testing,Child HIV Testing,Discordance,HIV Disclosure,Risk Factor/Reduction,Condom Use," + "Alcohol and Substance Abuse,Adherence,STIs,Family Planning,PMTCT,TB").split(","); // COPY FILE TO BE WRITTEN TO Path original = Paths.get(getServletContext().getRealPath("/NOT_ACHIEVED_TEMPLATE.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/NOT_ACHIEVED_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("/NOT_ACHIEVED_TEMPLATE_1.xlsm"); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb1; OPCPackage pkg = OPCPackage.open(allpath); wb1 = new XSSFWorkbook(pkg); SXSSFWorkbook wb = new SXSSFWorkbook(wb1, 100); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ // HSSFWorkbook wb=new HSSFWorkbook(); Sheet shet1 = wb.getSheet("Sheet1"); Font 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); Font font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); CellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); for (int i = 0; i <= reportHeader.length; i++) { shet1.setColumnWidth(i, 4000); } CellStyle 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); CellStyle 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); Font fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); Cell cell; Row rw0 = shet1.createRow(0); rw0.setHeightInPoints(30); rw0.setRowStyle(style2); for (int i = 0; i <= (reportHeader.length - 1); i++) { cell = rw0.createCell(i); cell.setCellValue(reportHeader[i]); cell.setCellStyle(stylex); } String getClients = "SELECT county.county_name,partner.partner_name,district.district_name,dic.dic_name," + "groups.group_name,personal_information.fname,personal_information.mname,personal_information.lname," + "personal_information.ccc_no,personal_information.mobile_no, personal_information.gender," + "personal_information.dob,marital_status.name,employment_status.name,education_levels.name," + "art_status.name," + "service_provider.fname,service_provider.mname,service_provider.lname,health_facility.hf_name, " + "personal_information.lessons_attended," + "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,s1,s2,s3,s4,s5,s6,s7,s8,s9,s10,s11,s12,s13 " + " FROM personal_information " + " LEFT JOIN groups ON personal_information.group_id=groups.group_id " + " LEFT JOIN dic ON personal_information.dic_id=dic.dic_id " + " LEFT JOIN service_provider ON personal_information.provider_id=service_provider.provider_id " + " LEFT JOIN health_facility ON personal_information.hf_id=health_facility.hf_id " + " LEFT JOIN district ON personal_information.district_id=district.district_id " + " LEFT JOIN marital_status ON personal_information.marital_status=marital_status.id " + " LEFT JOIN employment_status ON personal_information.employment_status=employment_status.id " + " LEFT JOIN education_levels ON personal_information.education_level=education_levels.id " + " LEFT JOIN art_status ON personal_information.art_status=art_status.id " + " LEFT JOIN register ON personal_information.client_id=register.client_id " + " LEFT JOIN partner ON personal_information.partner_id=partner.partner_id " + " LEFT JOIN county ON district.county_id=county.county_id " + " WHERE (personal_information.completionmonth=0 || personal_information.completionyear=0) " + " ORDER BY partner.partner_name,county.county_name,district.district_name,dic.dic_name," + "groups.group_name"; System.out.println("query is : " + getClients); conn.rs = conn.st.executeQuery(getClients); while (conn.rs.next()) { // ADD THE DATA TO EXCEL HERE groupName = DICName = districtName = partnerName = countyName = agebracket = lessons_attended = year = ""; clientFname = clientMname = clientLname = ccc_no = mobile_no = gender = dob = marital_status = ""; location = employment_status = education_level = under_18 = ovc_children = hiv_year = art_status = ""; registration_date = approved_by = designation = approval_date = ""; SPFname = SPMname = SPLname = SPFullName = healthFacility = ""; s1 = s2 = s3 = s4 = s5 = s6 = s7 = s8 = s9 = s10 = s11 = s12 = s13 = ""; if (conn.rs.getString(1) != null) { countyName = conn.rs.getString(1); } if (conn.rs.getString(2) != null) { partnerName = conn.rs.getString(2); } if (conn.rs.getString(3) != null) { districtName = conn.rs.getString(3); } if (conn.rs.getString(4) != null) { DICName = conn.rs.getString(4); } else { DICName = "NO DIC"; } if (conn.rs.getString(5) != null) { groupName = conn.rs.getString(5); } else { groupName = "Individual"; } if (conn.rs.getString(6) != null) { clientFname = conn.rs.getString(6); } if (conn.rs.getString(7) != null) { clientMname = conn.rs.getString(7); } if (conn.rs.getString(8) != null) { clientLname = conn.rs.getString(8); } if (conn.rs.getString(9) != null) { ccc_no = conn.rs.getString(9); } if (conn.rs.getString(10) != null) { mobile_no = conn.rs.getString(10); } if (conn.rs.getString(11) != null) { gender = conn.rs.getString(11); } if (conn.rs.getString(12) != null) { dob = conn.rs.getString(12); } if (conn.rs.getString(13) != null) { marital_status = conn.rs.getString(13); } if (conn.rs.getString(14) != null) { employment_status = conn.rs.getString(14); } if (conn.rs.getString(15) != null) { education_level = conn.rs.getString(15); } if (conn.rs.getString(16) != null) { art_status = conn.rs.getString(16); } if (conn.rs.getString(17) != null) { SPFname = conn.rs.getString(17); } if (conn.rs.getString(18) != null) { SPMname = conn.rs.getString(18); } if (conn.rs.getString(19) != null) { SPLname = conn.rs.getString(19); } if (conn.rs.getString(20) != null) { healthFacility = conn.rs.getString(20); } if (conn.rs.getString(21) != null) { lessons_attended = conn.rs.getString(21); } if (conn.rs.getString(22) != null) { agebracket = conn.rs.getString(22); } if (conn.rs.getString(23) != null) { gender = conn.rs.getString(23); } if (conn.rs.getString(24) != null) { s1 = conn.rs.getString(24); } if (conn.rs.getString(25) != null) { s2 = conn.rs.getString(25); } if (conn.rs.getString(26) != null) { s3 = conn.rs.getString(26); } if (conn.rs.getString(27) != null) { s4 = conn.rs.getString(27); } if (conn.rs.getString(28) != null) { s5 = conn.rs.getString(28); } if (conn.rs.getString(29) != null) { s6 = conn.rs.getString(29); } if (conn.rs.getString(30) != null) { s7 = conn.rs.getString(30); } if (conn.rs.getString(31) != null) { s8 = conn.rs.getString(31); } if (conn.rs.getString(32) != null) { s9 = conn.rs.getString(32); } if (conn.rs.getString(33) != null) { s10 = conn.rs.getString(33); } if (conn.rs.getString(34) != null) { s11 = conn.rs.getString(34); } if (conn.rs.getString(35) != null) { s12 = conn.rs.getString(35); } if (conn.rs.getString(36) != null) { s13 = conn.rs.getString(36); } if (s1.equals("5")) { s1 = ""; } if (s1.equals("2")) { s1 = "0"; } if (s2.equals("5")) { s2 = ""; } if (s2.equals("2")) { s2 = "0"; } if (s3.equals("5")) { s3 = ""; } if (s3.equals("2")) { s3 = "0"; } if (s4.equals("5")) { s4 = ""; } if (s4.equals("2")) { s4 = "0"; } if (s5.equals("5")) { s5 = ""; } if (s5.equals("2")) { s5 = "0"; } if (s6.equals("5")) { s6 = ""; } if (s6.equals("2")) { s6 = "0"; } if (s7.equals("5")) { s7 = ""; } if (s7.equals("2")) { s7 = "0"; } if (s8.equals("5")) { s8 = ""; } if (s8.equals("2")) { s8 = "0"; } if (s9.equals("5")) { s9 = ""; } if (s9.equals("2")) { s9 = "0"; } if (s10.equals("5")) { s10 = ""; } if (s10.equals("2")) { s10 = "0"; } if (s11.equals("5")) { s11 = ""; } if (s11.equals("2")) { s11 = "0"; } if (s12.equals("5")) { s12 = ""; } if (s12.equals("2")) { s12 = "0"; } if (s13.equals("5")) { s13 = ""; } if (s13.equals("2")) { s13 = "0"; } if (clientMname.equals(clientLname)) { clientMname = ""; } if (SPMname.equals(SPLname)) { SPMname = ""; } SPFullName = SPFname + " " + SPMname + " " + SPLname; clientFullName = clientFname + " " + clientMname + " " + clientLname; String rawData[] = (countyName + "," + partnerName + "," + districtName + "," + DICName + "," + groupName + "," + clientFullName + "," + ccc_no + "," + mobile_no + "," + gender + "," + dob + "," + marital_status + "," + employment_status + "," + education_level + "," + art_status + "," + SPFullName + "," + healthFacility + "," + lessons_attended + "," + agebracket + "," + s1 + "," + s2 + "," + s3 + "," + s4 + "," + s5 + "," + s6 + "," + s7 + "," + s8 + "," + s9 + "," + s10 + "," + s11 + "," + s12 + "," + s13).split(","); Row rw1 = shet1.createRow(position); rw1.setHeightInPoints(25); rw1.setRowStyle(style2); for (int i = 0; i <= (reportHeader.length - 1); i++) { cell = rw1.createCell(i); cell.setCellStyle(styleBorder); } for (int i = 0; i <= (rawData.length - 1); i++) { cell = rw1.getCell(i); cell.setCellValue(rawData[i]); } position++; System.out.println("at position : " + position); } IdGenerator CRT = new IdGenerator(); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_CLIENTS_NOT_REACHED_REPORT_CREATED_ON_" + CRT.timestamp() + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); }
From source file:reports.nutritionexcel.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {/*from w w w .j a va 2s . c o m*/ response.setContentType("text/html;charset=UTF-8"); session = request.getSession(); dbConn conn = new dbConn(); //get the existing data for the month, year and facility that is already on session String month = ""; String year = ""; String facil = ""; String form = "nutrition"; //===================================================================================================== year = "2015"; month = "5"; String county = ""; String header = ""; String reportType = ""; if (request.getParameter("reportType") != null) { reportType = request.getParameter("reportType"); } String reportDuration = ""; if (request.getParameter("reportDuration") != null) { reportDuration = request.getParameter("reportDuration"); } if (request.getParameter("year") != null) { year = request.getParameter("year"); } if (request.getParameter("facility") != null && reportType.equals("2")) { facil = request.getParameter("facility"); String getfacil = "select SubPartnerNom,CentreSanteId as mflcode from subpartnera where SubPartnerID='" + facil + "'"; conn.rs = conn.st.executeQuery(getfacil); while (conn.rs.next()) { header += " FACILITY : " + conn.rs.getString(1).toUpperCase() + " MFL CODE : " + conn.rs.getString(2) + " "; } } if (request.getParameter("county") != null && reportType.equals("2")) { county = request.getParameter("county"); String getcounty = "select County from county where CountyID='" + county + "'"; conn.rs = conn.st.executeQuery(getcounty); while (conn.rs.next()) { header += " COUNTY : " + conn.rs.getString(1).toUpperCase() + " "; } } if (request.getParameter("month") != null && reportDuration.equals("4")) { month = request.getParameter("month"); String getmonth = "select name as monthname from month where id='" + month + "'"; conn.rs = conn.st.executeQuery(getmonth); while (conn.rs.next()) { header += " MONTH : " + conn.rs.getString(1).toUpperCase() + " "; } } header += " YEAR : " + year + ""; String facilitywhere = ""; String yearwhere = ""; String monthwhere = ""; String countywhere = ""; String duration = ""; String semi_annual = ""; String quarter = ""; //================================================================================================== //XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX int yearcopy = Integer.parseInt(year); // reportType="2"; // year=2015; // reportDuration="3"; String yearmonth = "" + year; int prevYear = yearcopy - 1; int maxYearMonth = 0; int monthcopy = 0; // GET REPORT DURATION============================================ if (reportDuration.equals("1")) { yearmonth += "_AnnualReport"; duration = " " + form + ".yearmonth BETWEEN " + prevYear + "10 AND " + year + "09"; } else if (reportDuration.equals("2")) { semi_annual = request.getParameter("semi_annual"); // semi_annual="2"; if (semi_annual.equals("1")) { yearmonth = prevYear + "_Oct_" + year + "_Mar"; duration = " " + form + ".yearmonth BETWEEN " + prevYear + "10 AND " + year + "03"; } else { yearmonth += "_Apr_Sep"; duration = " " + form + ".yearmonth BETWEEN " + year + "04 AND " + year + "09"; } } else if (reportDuration.equals("3")) { String startMonth, endMonth; quarter = request.getParameter("quarter"); // quarter="3"; String getMonths = "SELECT months,name FROM quarter WHERE id='" + quarter + "'"; conn.rs = conn.st.executeQuery(getMonths); if (conn.rs.next() == true) { String months[] = conn.rs.getString(1).split(","); startMonth = months[0]; endMonth = months[2]; if (quarter.equals("1")) { duration = " " + form + ".yearmonth BETWEEN " + prevYear + "" + startMonth + " AND " + prevYear + "" + endMonth; yearmonth = prevYear + "_" + conn.rs.getString(2); } else { yearmonth = year + "_" + conn.rs.getString(2); duration = " " + form + ".yearmonth BETWEEN " + year + "" + startMonth + " AND " + year + "" + endMonth; } } } else if (reportDuration.equals("4")) { monthcopy = Integer.parseInt(request.getParameter("month")); // month=5; if (monthcopy >= 10) { yearmonth = prevYear + "_" + month; duration = " " + form + ".yearmonth=" + prevYear + "" + month; } else { duration = " " + form + ".yearmonth=" + year + "0" + month; yearmonth = year + "_(" + month + ")"; } } else { duration = ""; } //====================================================================== //XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX String getexistingdata = ""; if (!county.equals("")) { countywhere = " and countyid = '" + county + "'"; } if (!facil.equals("") && reportType.equalsIgnoreCase("2")) { facilitywhere = " and " + form + ".SubPartnerID = '" + facil + "'"; } //String joinedwhwere=" where 1=1 "+yearwhere+" && "+duration; String joinedwhwere = " where 1=1 " + facilitywhere + " " + yearwhere + " && " + duration; //===================================================================================================== //===================================================================================================== //===================================================================================================== //______________________________________________________________________________________ // NOW CREATE THE WORKSHEETS //______________________________________________________________________________________ HSSFWorkbook wb = new HSSFWorkbook(); //______________________________________________________________________________________ //______________________________________________________________________________________ HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 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.GREY_25_PERCENT.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); stylex.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); fontx.setColor((short) 0000); fontx.setBoldweight((short) 07); stylex.setFont(fontx); stylex.setWrapText(true); HSSFSheet shet = wb.createSheet(form); //create headers for that worksheet HSSFRow rw = shet.createRow(0); rw.setHeightInPoints(25); HSSFCell cl0 = rw.createCell(0); cl0.setCellValue("3.1.9: Nutrition"); cl0.setCellStyle(stylex); for (int a = 1; a <= 6; a++) { HSSFCell clx = rw.createCell(a); clx.setCellValue(""); clx.setCellStyle(stylex); } HSSFRow rw1 = shet.createRow(1); rw1.setHeightInPoints(23); HSSFCell cl = rw1.createCell(0); cl.setCellValue(header); cl.setCellStyle(stylex); for (int a = 1; a <= 6; a++) { HSSFCell clx = rw1.createCell(a); clx.setCellValue(""); clx.setCellStyle(stylex); } HSSFRow rw2 = shet.createRow(2); rw2.setHeightInPoints(23); HSSFCell cl3 = rw2.createCell(0); cl3.setCellValue("3.1.9.2 Population Based Nutrition Service Delivery"); cl3.setCellStyle(stylex); HSSFCell cl3a = rw2.createCell(1); cl3a.setCellValue(""); cl3a.setCellStyle(stylex); HSSFCell cl31 = rw2.createCell(2); cl31.setCellValue( "Number of people trained in child health care and nutrition through USG-supported health area programs"); cl31.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx = rw2.createCell(a); clx.setCellValue(""); clx.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(2, 11, 0, 1)); shet.addMergedRegion(new CellRangeAddress(2, 2, 2, 5)); shet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6)); shet.addMergedRegion(new CellRangeAddress(1, 1, 0, 6)); shet.addMergedRegion(new CellRangeAddress(12, 20, 0, 0)); shet.setColumnWidth(0, 3000); shet.setColumnWidth(1, 3000); shet.setColumnWidth(2, 16000); shet.setColumnWidth(3, 6900); shet.setColumnWidth(4, 3000); shet.setColumnWidth(5, 3000); getexistingdata = "select sum(MCHCCNtrTM) as MCHCCNtrTM, MCHCCNtrTMC, sum(MCHCCNtrTF) as MCHCCNtrTF, MCHCCNtrTFC, sum(MCHCCNtrTT) as MCHCCNtrTT, MCHCCNtrTTC, sum(MCHNtrnCHWTrain) as MCHNtrnCHWTrain, sum(MCHNutChRch) as MCHNutChRch, sum(MCHNtrnWasted) as MCHNtrnWasted, sum(MCHNtrnUnderweight) as MCHNtrnUnderweight, sum(MCHChild5D) as MCHChild5D, sum(MCHNtrnHealthFacility) as MCHNtrnHealthFacility, sum(MCHVaccVitA) as MCHVaccVitA, sum(MCHNtrnFoodOVC) as MCHNtrnFoodOVC, sum(MCHNtrnFoodPLHIV) as MCHNtrnFoodPLHIV, sum(MCHNtrnFood) as MCHNtrnFood, sum(C51DCM) as C51DCM, sum(C51DCF) as C51DCF, sum(C51DC) as C51DC, sum(C51DAM) as C51DAM, sum(C51DAF) as C51DAF, sum(C51DA) as C51DA, sum(C51DP) as C51DP, sum(C51DMT) as C51DMT, sum(C51DFT) as C51DFT, sum(C51DT) as C51DT from " + form + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on " + form + ".SubPartnerID = subpartnera.SubPartnerID " + joinedwhwere + " "; //System.out.println(getexistingdata); String MCHCCNtrTM = ""; String MCHCCNtrTF = ""; String MCHCCNtrTT = ""; String MCHCCNtrTMC = "0"; String MCHCCNtrTFC = "0"; String MCHCCNtrTTC = "0"; String MCHCCNtrTMCH = "0"; String MCHCCNtrTFCH = "0"; String MCHCCNtrTTCH = "0"; String MCHNtrnCHWTrain = ""; String MCHNutChRch = ""; String MCHNtrnWasted = ""; String MCHNtrnUnderweight = ""; String MCHChild5D = ""; String MCHNtrnHealthFacility = ""; String MCHVaccVitA = ""; String MCHNtrnFoodOVC = ""; String MCHNtrnFoodPLHIV = ""; String MCHNtrnFood = ""; String C51DCM = ""; String C51DCF = ""; String C51DC = ""; String C51DAM = ""; String C51DAF = ""; String C51DA = ""; String C51DP = ""; String C51DMT = ""; String C51DFT = ""; String C51DT = ""; String distid = ""; if (session.getAttribute("subcountyid") != null) { distid = session.getAttribute("subcountyid").toString(); } int counter = 0; conn.rs = conn.st.executeQuery(getexistingdata); while (conn.rs.next()) { //now check if form was updated and if its one month after data entry //now load the column values here MCHCCNtrTM = conn.rs.getString("MCHCCNtrTM"); if (MCHCCNtrTM == null) { MCHCCNtrTM = ""; } MCHCCNtrTF = conn.rs.getString("MCHCCNtrTF"); if (MCHCCNtrTF == null) { MCHCCNtrTF = ""; } MCHCCNtrTT = conn.rs.getString("MCHCCNtrTT"); if (MCHCCNtrTT == null) { MCHCCNtrTT = ""; } MCHCCNtrTMC = conn.rs.getString("MCHCCNtrTMC"); if (MCHCCNtrTMC == null) { MCHCCNtrTMC = "0"; } MCHCCNtrTFC = conn.rs.getString("MCHCCNtrTFC"); if (MCHCCNtrTFC == null) { MCHCCNtrTFC = "0"; } MCHCCNtrTTC = conn.rs.getString("MCHCCNtrTTC"); if (MCHCCNtrTTC == null) { MCHCCNtrTTC = "0"; } MCHNtrnCHWTrain = conn.rs.getString("MCHNtrnCHWTrain"); if (MCHNtrnCHWTrain == null) { MCHNtrnCHWTrain = ""; } MCHNutChRch = conn.rs.getString("MCHNutChRch"); if (MCHNutChRch == null) { MCHNutChRch = ""; } MCHNtrnWasted = conn.rs.getString("MCHNtrnWasted"); if (MCHNtrnWasted == null) { MCHNtrnWasted = ""; } MCHNtrnUnderweight = conn.rs.getString("MCHNtrnUnderweight"); if (MCHNtrnUnderweight == null) { MCHNtrnUnderweight = ""; } MCHChild5D = conn.rs.getString("MCHChild5D"); if (MCHChild5D == null) { MCHChild5D = ""; } MCHNtrnHealthFacility = conn.rs.getString("MCHNtrnHealthFacility"); if (MCHNtrnHealthFacility == null) { MCHNtrnHealthFacility = ""; } MCHVaccVitA = conn.rs.getString("MCHVaccVitA"); if (MCHVaccVitA == null) { MCHVaccVitA = ""; } MCHNtrnFoodOVC = conn.rs.getString("MCHNtrnFoodOVC"); if (MCHNtrnFoodOVC == null) { MCHNtrnFoodOVC = ""; } MCHNtrnFoodPLHIV = conn.rs.getString("MCHNtrnFoodPLHIV"); if (MCHNtrnFoodPLHIV == null) { MCHNtrnFoodPLHIV = ""; } MCHNtrnFood = conn.rs.getString("MCHNtrnFood"); if (MCHNtrnFood == null) { MCHNtrnFood = ""; } C51DCM = conn.rs.getString("C51DCM"); if (C51DCM == null) { C51DCM = ""; } C51DCF = conn.rs.getString("C51DCF"); if (C51DCF == null) { C51DCF = ""; } C51DC = conn.rs.getString("C51DC"); if (C51DC == null) { C51DC = ""; } C51DAM = conn.rs.getString("C51DAM"); if (C51DAM == null) { C51DAM = ""; } C51DAF = conn.rs.getString("C51DAF"); if (C51DAF == null) { C51DAF = ""; } C51DA = conn.rs.getString("C51DA"); if (C51DA == null) { C51DA = ""; } C51DP = conn.rs.getString("C51DP"); if (C51DP == null) { C51DP = ""; } C51DMT = conn.rs.getString("C51DMT"); if (C51DMT == null) { C51DMT = ""; } C51DFT = conn.rs.getString("C51DFT"); if (C51DFT == null) { C51DFT = ""; } C51DT = conn.rs.getString("C51DT"); if (C51DT == null) { C51DT = ""; } } String createdtable = ""; if (1 == 1) { int r = 3; HSSFCell cl3d = rw2.createCell(6); cl3d.setCellValue(MCHCCNtrTTC); cl3d.setCellStyle(style2); if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(2); clx.setCellValue("Number of Men"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(MCHCCNtrTM); clx1.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); } r++; } //================================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(2); clx.setCellValue("Number of Women"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(MCHCCNtrTF); clx1.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); } r++; } if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(2); clx.setCellValue( "Total Number of people trained in child health care and nutrition through USG-supported health area programs"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(MCHCCNtrTT); clx1.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); } r++; } //============================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(2); clx.setCellValue("Number of Community health workers trained in child health and/or nutrition"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(MCHNtrnCHWTrain); clx1.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); } r++; } //=========================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(2); clx.setCellValue("Number of children reached by USG-supported nutrition programs"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(MCHNutChRch); clx1.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); } r++; } //=========================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(2); clx.setCellValue( "Total number of children under five who are wasted (with weight for height Z score < - 2)"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(MCHNtrnWasted); clx1.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); } r++; } if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(2); clx.setCellValue( "Total number of children under five who are underweight (with weight for age Z score < - 2) (see Indicator"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(MCHNtrnUnderweight); clx1.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); } r++; } //=========================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(2); clx.setCellValue("Total number of children under five years"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(MCHChild5D); clx1.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); } r++; } //=========================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(2); clx.setCellValue( "Number of health facilities with established capacity to manage acute under-nutrition"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(MCHNtrnHealthFacility); clx1.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); } r++; } //=========================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue("HIV and Nutrition"); clx0.setCellStyle(stylex); HSSFCell clx1a = rwx.createCell(1); clx1a.setCellValue(""); clx1a.setCellStyle(stylex); HSSFCell clx = rwx.createCell(2); clx.setCellValue( "Number of children under 5 years of age who received Vitamin A from USG-supported programs"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(MCHVaccVitA); clx1.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); //shet.addMergedRegion(new CellRangeAddress(2,11,0,0)); } r++; } //=========================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(stylex); HSSFCell clx1a = rwx.createCell(1); clx1a.setCellValue("C2.3.D:"); clx1a.setCellStyle(stylex); HSSFCell clx = rwx.createCell(2); clx.setCellValue( "Number of HIV positive clinically malnourished clients who received therapeutic and/or supplementary food < 18"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(MCHNtrnFoodOVC); clx1.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); shet.addMergedRegion(new CellRangeAddress(r, r + 2, 1, 1)); } r++; } //=========================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(stylex); HSSFCell clx1a = rwx.createCell(1); clx1a.setCellValue(""); clx1a.setCellStyle(stylex); HSSFCell clx = rwx.createCell(2); clx.setCellValue( "Number of HIV positive clinically malnourished clients who received therapeutic and/or supplementary food 18+ (PLHIV)"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(MCHNtrnFoodPLHIV); clx1.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); // shet.addMergedRegion(new CellRangeAddress(r,r+3,1,1)); } r++; } //=========================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(stylex); HSSFCell clx1a = rwx.createCell(1); clx1a.setCellValue(""); clx1a.setCellStyle(stylex); HSSFCell clx = rwx.createCell(2); clx.setCellValue( "Number of HIV positive clinically malnourished clients who received therapeutic and/or supplementary food - Total"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(MCHNtrnFood); clx1.setCellStyle(style2); for (int a = 3; a <= 5; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); // shet.addMergedRegion(new CellRangeAddress(r,r+3,1,1)); } r++; } //=========================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(stylex); HSSFCell clx1a = rwx.createCell(1); clx1a.setCellValue(""); clx1a.setCellStyle(stylex); HSSFCell clx = rwx.createCell(2); clx.setCellValue(""); clx.setCellStyle(style2); HSSFCell clx3 = rwx.createCell(3); clx3.setCellValue(""); clx3.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(6); clx1.setCellValue(""); clx1.setCellStyle(style2); String haeade[] = { "Male", "Female", "Total" }; for (int a = 4; a <= 6; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(haeade[a - 4]); clx2.setCellStyle(style2); //shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); // shet.addMergedRegion(new CellRangeAddress(r,r+3,1,1)); } shet.addMergedRegion(new CellRangeAddress(r, r, 2, 3)); r++; } //=========================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(stylex); HSSFCell clx1a = rwx.createCell(1); clx1a.setCellValue("C5.1.D:"); clx1a.setCellStyle(stylex); HSSFCell clx1b = rwx.createCell(3); clx1b.setCellValue("< 18"); clx1b.setCellStyle(style2); HSSFCell clx = rwx.createCell(2); clx.setCellValue( "Number of eligible clients who received food and / or other nutrition Services"); clx.setCellStyle(style2); String haeade[] = { C51DCM, C51DCF, C51DC }; for (int a = 0; a < haeade.length; a++) { HSSFCell clx2 = rwx.createCell(a + 4); clx2.setCellValue(haeade[a]); clx2.setCellStyle(style2); //shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); // shet.addMergedRegion(new CellRangeAddress(r,r+3,1,1)); } //shet.addMergedRegion(new CellRangeAddress(r,r,2,3)); shet.addMergedRegion(new CellRangeAddress(r, r + 3, 1, 1)); shet.addMergedRegion(new CellRangeAddress(r, r + 3, 2, 2)); r++; } //=========================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(stylex); HSSFCell clx1a = rwx.createCell(1); clx1a.setCellValue(""); clx1a.setCellStyle(stylex); HSSFCell clx1b = rwx.createCell(3); clx1b.setCellValue(">=18"); clx1b.setCellStyle(style2); HSSFCell clx = rwx.createCell(2); clx.setCellValue(""); clx.setCellStyle(style2); String haeade[] = { C51DAM, C51DAF, C51DA }; for (int a = 0; a < haeade.length; a++) { HSSFCell clx2 = rwx.createCell(a + 4); clx2.setCellValue(haeade[a]); clx2.setCellStyle(style2); //shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); // shet.addMergedRegion(new CellRangeAddress(r,r+3,1,1)); } //shet.addMergedRegion(new CellRangeAddress(r,r,2,3)); r++; } //=========================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(stylex); HSSFCell clx1a = rwx.createCell(1); clx1a.setCellValue(""); clx1a.setCellStyle(stylex); HSSFCell clx1b = rwx.createCell(3); clx1b.setCellValue("Pregnant/Lactating (PMTCT 1.5)"); clx1b.setCellStyle(style2); HSSFCell clx = rwx.createCell(2); clx.setCellValue(""); clx.setCellStyle(style2); String haeade[] = { "", "", C51DP }; for (int a = 0; a < haeade.length; a++) { HSSFCell clx2 = rwx.createCell(a + 4); clx2.setCellValue(haeade[a]); clx2.setCellStyle(style2); //shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); // shet.addMergedRegion(new CellRangeAddress(r,r+3,1,1)); } shet.addMergedRegion(new CellRangeAddress(r, r, 4, 5)); r++; } //=========================================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(stylex); HSSFCell clx1a = rwx.createCell(1); clx1a.setCellValue(""); clx1a.setCellStyle(stylex); HSSFCell clx1b = rwx.createCell(3); clx1b.setCellValue("Total"); clx1b.setCellStyle(style2); HSSFCell clx = rwx.createCell(2); clx.setCellValue(""); clx.setCellStyle(style2); String haeade[] = { C51DMT, C51DFT, C51DT }; for (int a = 0; a < haeade.length; a++) { HSSFCell clx2 = rwx.createCell(a + 4); clx2.setCellValue(haeade[a]); clx2.setCellStyle(style2); //shet.addMergedRegion(new CellRangeAddress(r, r, 2, 5)); // shet.addMergedRegion(new CellRangeAddress(r,r+3,1,1)); } //shet.addMergedRegion(new CellRangeAddress(r,r,2,3)); r++; } //=========================================================================================================== createdtable += header + "<br/><table border='1' style='border-color: #e5e5e5;margin-bottom: 3px;font-size:11;'><tr class='form-actions'>" + "<th rowspan='10' colspan='2'><b style='text-align:center;'>3.1.9.2 <br/> population-based Nutrition Service Delivery</b></th>" + "<td colspan='4'><b>Number of People trained in child health care and nutrition through USG-supported health area programs</b></td><td><b>" + MCHCCNtrTTC + "</b></td></tr>"; createdtable += "<tr><td colspan='4' style='text-align:left;'>No of Men </td><td>" + MCHCCNtrTM + "</td></tr>"; createdtable += "<tr><td colspan='4' style='text-align:left;'>No of Women </td><td>" + MCHCCNtrTF + "</td></tr>"; createdtable += "<tr><td colspan='4'><b>Total Number of people trained in child health care and nutrition through USG-supported health area programs</b></td><td>" + MCHCCNtrTT + "</td></tr>"; createdtable += "<tr><td colspan='4'><b>Number of Community health workers trained in child health and/or nutrition</b></td><td>" + MCHNtrnCHWTrain + "</td></tr>"; createdtable += "<tr><td colspan='4'><b>Number of children reached by USG-supported nutrition programs</b></td><td>" + MCHNutChRch + "</td></tr>"; createdtable += "<tr><td colspan='4'>Total number of children under five who are wasted (with weight for height Z score < - 2)</td><td>" + MCHNtrnWasted + "</td></tr>"; createdtable += "<tr><td colspan='4'>Total number of children under five who are underweight (with weight for age Z score < - 2) (SEE Indicator </td><td>" + MCHNtrnUnderweight + "</td></tr>"; createdtable += "<tr><td colspan='4'>Total number of children under five years</td><td>" + MCHChild5D + "</td></tr>"; createdtable += "<tr><td colspan='4'>Number of health facilities with established capacity to manage acute under-nutrition</td><td>" + MCHNtrnHealthFacility + "</td></tr>"; createdtable += "<tr><td colspan='1' rowspan='9'>HIV and Nutrition</td><td></td> <td colspan='4'> <b> Number of children under 5 years of age who received Vitamin A from USG-supported programs </b> </td><td>" + MCHVaccVitA + "</td></tr>"; createdtable += "<tr><td rowspan='3' colspan='1'> <b> C2.3.D </b> </td> <td colspan='4'>Number of HIV positive clinically malnourished clients who received therapeutic and/or supplementary food < 18 </td><td>" + MCHNtrnFoodOVC + "</td></tr>"; createdtable += "<tr><td colspan='4'>Number of HIV positive clinically malnourished clients who received therapeutic and/or supplementary food 18+ (PLHIV)</td><td>" + MCHNtrnFoodPLHIV + "</td></tr>"; createdtable += "<tr><td colspan='4'>Number of HIV positive clinically malnourished clients who received therapeutic and/or supplementary food -<b> Total</b></td><td>" + MCHNtrnFood + "</td></tr>"; createdtable += "<tr> <td></td><td></td><td></td><td><b>Male</b></td><td><b>Female</b></td><td><b>Total</b></td></tr>"; createdtable += "<tr><td rowspan='4' colspan='1'> <b>C5.1.D </b> </td> <td colspan='1' rowspan='4'>Number of eligible clients who received food and / or other nutrition Services</td><td> <b> less Than 18 </b> </td> <td>" + C51DCM + "</td><td>" + C51DCF + "</td><td>" + C51DC + "</td></tr>"; createdtable += "<tr><td> <b> >=18 </b> </td> <td>" + C51DAM + "</td><td>" + C51DAF + "</td><td>" + C51DA + "</td></tr>"; createdtable += "<tr><td colspan='3'> <b> Pregnant/Lactating (PMTCT 1.5)</b> </td><td>" + C51DP + "</td></tr>"; createdtable += "<tr><td> <b> Total </b> </td> <td> " + C51DMT + " </td> <td> " + C51DFT + " </td> <td> " + C51DT + " </td></tr></table>"; } System.out.println(createdtable); if (conn.conn != null) { conn.conn.close(); } if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st != null) { conn.st.close(); } if (conn.st2 != null) { conn.st2.close(); } IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); 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=" + form + yearmonth + "_Generated_On_" + createdOn + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); outStream.close(); } catch (SQLException ex) { Logger.getLogger(Vmmcpdf.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.OverallCharts.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods./*from w ww . ja v a 2 s . c om*/ * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try { dbConn conn = new dbConn(); wb = new HSSFWorkbook(); HSSFSheet shet2 = null; String year = ""; String site = ""; String period = ""; String cbo = ""; String startdate = "2015-01-01"; String enddate = "2015-03-30"; // startdate=request.getParameter("startdate"); //enddate=request.getParameter("enddate"); String getdistinctsites = "SELECT county.county_id as countyid,county_name FROM ovc_lip.backgroundinfor join (sites join (district join county on district.county_id=county.county_id) on sites.districtid=district.district_id) on backgroundinfor.site=sites.site_id where ass_date between '" + startdate + "' and '" + enddate + "' group by county_name "; ArrayList countyids = new ArrayList(); ArrayList countynames = new ArrayList(); countyids.add("1000"); countynames.add("OVERALL COUNTIES REPORT"); //ArrayList years=new ArrayList(); //ArrayList periods=new ArrayList(); //ArrayList cbos=new ArrayList(); conn.rs = conn.st.executeQuery(getdistinctsites); while (conn.rs.next()) { countyids.add(conn.rs.getString(1)); countynames.add(conn.rs.getString(2).toUpperCase() + " COUNTY"); } String sitename = ""; String cboname = ""; //begin a loop that will create as many reports as possible for (int u = 0; u < countyids.size(); u++) { HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Cambria"); // font.setItalic(true); font.setBoldweight((short) 02); font.setColor(HSSFColor.BLACK.index); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setWrapText(true); style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); style.setAlignment(style.ALIGN_CENTER); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //%%%%%%%%%%%%%%%%HEADER FONTS AND COLORATION%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% HSSFFont font_header = wb.createFont(); font_header.setFontHeightInPoints((short) 10); font_header.setFontName("Eras Bold ITC"); // font.setItalic(true); font_header.setBoldweight((short) 05); font_header.setColor(HSSFColor.BLACK.index); CellStyle style_header = wb.createCellStyle(); style_header.setFont(font_header); style_header.setWrapText(true); style_header.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); style_header.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style_header.setAlignment(style_header.ALIGN_CENTER); // style_header.setBorderBottom(HSSFCellStyle.BORDER_THIN); // style_header.setBorderTop(HSSFCellStyle.BORDER_THIN); // style_header.setBorderRight(HSSFCellStyle.BORDER_THIN); // style_header.setBorderLeft(HSSFCellStyle.BORDER_THIN); //%%%%%%%%%%%%%%%%%%%%%%%%%DATA FONT%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% //font data HSSFFont datafont = wb.createFont(); datafont.setBoldweight((short) 03); datafont.setColor(HSSFColor.BLACK.index); datafont.setFontHeightInPoints((short) 10); datafont.setFontName("Cambria"); datafont.setItalic(true); //bold font HSSFFont bolfont = wb.createFont(); bolfont.setBoldweight((short) 05); bolfont.setColor(HSSFColor.BLACK.index); bolfont.setFontHeightInPoints((short) 12); bolfont.setFontName("Cambria"); //=========================ROW STYLE=============================== HSSFCellStyle rowstyle = wb.createCellStyle(); rowstyle.setWrapText(true); //=======INNER DATA STYLING=========================== CellStyle innerdata_style = wb.createCellStyle(); innerdata_style.setFont(datafont); innerdata_style.setWrapText(true); innerdata_style.setAlignment(innerdata_style.ALIGN_CENTER); innerdata_style.setFillForegroundColor(HSSFColor.WHITE.index); innerdata_style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN); innerdata_style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle lastcellrighborder = wb.createCellStyle(); lastcellrighborder.setFont(datafont); lastcellrighborder.setWrapText(true); lastcellrighborder.setAlignment(lastcellrighborder.ALIGN_CENTER); lastcellrighborder.setFillForegroundColor(HSSFColor.WHITE.index); lastcellrighborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // lastcellrighborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); // lastcellrighborder.setBorderTop(HSSFCellStyle.BORDER_THIN); lastcellrighborder.setBorderRight(HSSFCellStyle.BORDER_THIN); // lastcellrighborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); lastcellrighborder.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle innerdata_style2 = wb.createCellStyle(); innerdata_style2.setFont(bolfont); innerdata_style2.setWrapText(true); innerdata_style2.setAlignment(innerdata_style.ALIGN_LEFT); innerdata_style2.setFillForegroundColor(HSSFColor.WHITE.index); innerdata_style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN); innerdata_style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle orangestyle = wb.createCellStyle(); orangestyle.setFont(bolfont); orangestyle.setWrapText(true); orangestyle.setAlignment(orangestyle.ALIGN_CENTER); orangestyle.setFillForegroundColor(HSSFColor.ORANGE.index); orangestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN); orangestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //Code colors CellStyle lg = wb.createCellStyle(); lg.setFont(bolfont); lg.setWrapText(true); lg.setAlignment(lg.ALIGN_CENTER); lg.setFillForegroundColor(HSSFColor.GREEN.index); lg.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); lg.setBorderBottom(HSSFCellStyle.BORDER_THIN); lg.setBorderTop(HSSFCellStyle.BORDER_THIN); lg.setBorderRight(HSSFCellStyle.BORDER_THIN); lg.setBorderLeft(HSSFCellStyle.BORDER_THIN); lg.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle Y = wb.createCellStyle(); Y.setFont(bolfont); Y.setWrapText(true); Y.setAlignment(Y.ALIGN_CENTER); Y.setFillForegroundColor(HSSFColor.WHITE.index); Y.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); Y.setBorderBottom(HSSFCellStyle.BORDER_THIN); Y.setBorderTop(HSSFCellStyle.BORDER_THIN); Y.setBorderRight(HSSFCellStyle.BORDER_THIN); Y.setBorderLeft(HSSFCellStyle.BORDER_THIN); Y.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle R = wb.createCellStyle(); R.setFont(bolfont); R.setWrapText(true); R.setAlignment(R.ALIGN_CENTER); R.setFillForegroundColor(HSSFColor.RED.index); R.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); R.setBorderBottom(HSSFCellStyle.BORDER_THIN); R.setBorderTop(HSSFCellStyle.BORDER_THIN); R.setBorderRight(HSSFCellStyle.BORDER_THIN); R.setBorderLeft(HSSFCellStyle.BORDER_THIN); R.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //=======INNER LEFT DATA STYLING=========================== CellStyle binnerdata_style2 = wb.createCellStyle(); binnerdata_style2.setFont(datafont); binnerdata_style2.setWrapText(true); binnerdata_style2.setAlignment(binnerdata_style2.ALIGN_LEFT); binnerdata_style2.setFillForegroundColor(HSSFColor.WHITE.index); binnerdata_style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); binnerdata_style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderTop(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderRight(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //create a header //=======================Domainname styles CellStyle dnamestyle = wb.createCellStyle(); dnamestyle.setFont(bolfont); dnamestyle.setWrapText(true); dnamestyle.setAlignment(dnamestyle.ALIGN_LEFT); dnamestyle.setFillForegroundColor(HSSFColor.WHITE.index); dnamestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); dnamestyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderTop(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderRight(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); dnamestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); shet2 = wb.createSheet(countynames.get(u).toString().toUpperCase()); shet2.setColumnWidth(0, 12000); shet2.setColumnWidth(1, 12000); shet2.setColumnWidth(2, 4000); shet2.setColumnWidth(3, 10000); shet2.setColumnWidth(4, 5000); shet2.setColumnWidth(5, 5000); shet2.setColumnWidth(6, 5000); shet2.setColumnWidth(7, 5000); shet2.setColumnWidth(8, 5000); shet2.setColumnWidth(9, 5000); shet2.setColumnWidth(10, 5000); shet2.setColumnWidth(11, 5000); shet2.setColumnWidth(12, 5000); shet2.setColumnWidth(13, 5000); //11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 //11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 //11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 //11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 //String gettables= "SELECT avg(aggregate_sum) as aggregate_sum FROM ovc_lip.domain_totals join (sites join cbo on sites.cbo_id=cbo.cboid) on domain_totals.site=sites.site_id join ( domains join sections on domains.section_id=sections.section_id ) on domain_totals.domainid=domains.domain_id "+mywhere+" group by cbo.cboid,domainid order by cbo,domainid"; // String gettables= "SELECT avg(value) as domainvalue,domain_totals.domainid as domainid,domain_name, section_name,cbo,avg(aggregate_sum) as aggregate_sum FROM ovc_lip.domain_totals join (sites join cbo on sites.cbo_id=cbo.cboid) on domain_totals.site=sites.site_id join ( domains join sections on domains.section_id=sections.section_id ) on domain_totals.domainid=domains.domain_id "+mywhere+" group by cbo.cboid,domainid order by cbo,domainid"; String gettables = "SELECT avg(value) as domainvalue,domain_totals.domainid as domainid,domain_name,section_name ,avg(aggregate_sum) as aggregate_sum , domains.section_id as secid FROM ovc_lip.domain_totals join (sites join (district join county on district.county_id=county.county_id) on sites.districtid=district.district_id) on domain_totals.site=sites.site_id join (domains join sections on domains.section_id=sections.section_id) on domain_totals.domainid=domains.domain_id where county.county_id='" + countyids.get(u) + "' and date between '" + startdate + "' and '" + enddate + "' group by domain_totals.domainid,county_name order by domainid"; //if its the first county, themn skip the county part if (countyids.get(u).toString().equalsIgnoreCase("1000")) { gettables = "SELECT avg(value) as domainvalue,domain_totals.domainid as domainid,domain_name,section_name ,avg(aggregate_sum) as aggregate_sum , domains.section_id as secid FROM ovc_lip.domain_totals join (domains join sections on domains.section_id=sections.section_id) on domain_totals.domainid=domains.domain_id where date between '" + startdate + "' and '" + enddate + "' group by domain_totals.domainid order by domainid"; } System.out.println(gettables); conn.rs = conn.st.executeQuery(gettables); int rwcount = 0; int rowcountcopy = 0; String tableheaders[] = { "Section", "Domain", "% Overall Achievement", "Column chart" }; HSSFRow rwx = null; int monitorrows = 0; int secAcopy = 0; int secBcopy = 0; HSSFCell celx = null; int noofcols = 3; boolean isrow1 = true; while (conn.rs.next()) { //if the section has changed monitorrows++; String domainid = conn.rs.getString("domainid"); float domainvalue = conn.rs.getFloat("domainvalue"); domainvalue = domainvalue * 100; domainvalue = Math.round(domainvalue); float totalsum = conn.rs.getFloat("aggregate_sum"); int dmn = (int) domainvalue; totalsum = Math.round(totalsum); //determine the cell to print data on int ttlsm = (int) totalsum; int hearderheight = 40; //if its the first row in each if (isrow1) { isrow1 = false; rwx = shet2.createRow(rwcount); HSSFCell headercel = rwx.createCell(0); headercel.setCellValue(countynames.get(u).toString().toUpperCase()); headercel.setCellStyle(style); rwx.setHeightInPoints(hearderheight); //create a blank HSSFCell cel = null; for (int b = 1; b < tableheaders.length; b++) { cel = rwx.createCell(b); cel.setCellValue(""); cel.setCellStyle(style); } //now merge the header cell shet2.addMergedRegion(new CellRangeAddress(rwcount, rwcount, 0, tableheaders.length - 1)); rwcount++; //now create the header part HSSFRow headerrw = shet2.createRow(rwcount); rwx.setHeightInPoints(hearderheight); for (int b = 0; b < tableheaders.length; b++) { HSSFCell cel1 = headerrw.createCell(b); cel1.setCellValue(tableheaders[b]); cel1.setCellStyle(style); } rwcount++; } //create the section part HSSFRow rw = shet2.createRow(rwcount); rw.setHeightInPoints(25); //column one --- section HSSFCell seccell = rw.createCell(0); seccell.setCellValue(conn.rs.getString("section_name")); seccell.setCellStyle(dnamestyle); HSSFCell domcell = rw.createCell(1); domcell.setCellValue(conn.rs.getString("domain_name")); domcell.setCellStyle(dnamestyle); //values only HSSFCell domval = rw.createCell(2); domval.setCellValue(dmn); domval.setCellStyle(dnamestyle); HSSFCell blank = rw.createCell(3); blank.setCellValue(""); blank.setCellStyle(dnamestyle); //now, draw the chart HSSFPatriarch patriarch = shet2.createDrawingPatriarch(); HSSFTextbox textbox1 = patriarch.createTextbox( new HSSFClientAnchor(0, 0, (dmn * 10), 255, (short) 3, rwcount, (short) 3, rwcount)); textbox1.setString(new HSSFRichTextString("" + dmn)); textbox1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //green 18,174,55 //red 250 32 32 //yellow 248 255 9 if (dmn >= 75) { textbox1.setFillColor(18, 174, 55); } else if (dmn > 59 && dmn < 75) { textbox1.setFillColor(248, 255, 9); } else { textbox1.setFillColor(250, 32, 32); } rwcount++; if (monitorrows == 4) { shet2.addMergedRegion(new CellRangeAddress(rwcount - 4, rwcount - 1, 0, 0)); } if (monitorrows == 12) { shet2.addMergedRegion(new CellRangeAddress(rwcount - 8, rwcount - 1, 0, 0)); HSSFRow lastrw = shet2.createRow(rwcount); lastrw.setHeightInPoints(25); //now create a row with average HSSFCell avcell0 = lastrw.createCell(0); avcell0.setCellValue("Average"); avcell0.setCellStyle(dnamestyle); HSSFCell avcell = lastrw.createCell(1); avcell.setCellValue("Average"); avcell.setCellStyle(dnamestyle); HSSFCell avcell1 = lastrw.createCell(2); avcell1.setCellValue(ttlsm); avcell1.setCellStyle(dnamestyle); HSSFCell blank1 = lastrw.createCell(3); blank1.setCellValue(""); blank1.setCellStyle(dnamestyle); HSSFTextbox textbox = patriarch.createTextbox(new HSSFClientAnchor(0, 0, (dmn * (10)), 255, (short) 3, rwcount, (short) 3, rwcount)); textbox.setString(new HSSFRichTextString("" + ttlsm)); if (dmn >= 75) { textbox.setFillColor(18, 174, 55); } else if (dmn > 59 && dmn < 75) { textbox.setFillColor(248, 255, 9); } else { textbox.setFillColor(250, 32, 32); } textbox.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); isrow1 = true; monitorrows = 0; //dont print anything rwcount++; //last blank cell HSSFRow blankrow = shet2.createRow(rwcount); blankrow.setHeightInPoints(30); for (int b = 0; b < tableheaders.length; b++) { HSSFCell cel1 = blankrow.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(innerdata_style); } rwcount++; } } ///=========================end of while loop } //end of each loop //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=OVC_COUNTY_CHARTS_FROM_" + startdate + "_TO_" + enddate + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(resultspercbo.class.getName()).log(Level.SEVERE, null, ex); } }