List of usage examples for org.apache.poi.ss.usermodel CellStyle setFont
void setFont(Font font);
From source file:reports.completedSessions.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();/* w w w. jav a 2 s . c om*/ dbConn conn = new dbConn(); String[] starter = request.getParameter("start_date").split("/"); String[] ender = request.getParameter("end_date").split("/"); String m1 = "", m2 = ""; String d1 = "", d2 = "", y1 = "", y2 = ""; start = request.getParameter("start_date"); end = request.getParameter("end_date"); m1 = starter[1]; m2 = ender[1]; d1 = starter[0]; d2 = ender[0]; y1 = starter[2]; y2 = ender[2]; startdate = y1 + "" + m1 + "" + d1; enddate = y2 + "" + m2 + "" + d2; System.out.println("start date : " + startdate + " end date : " + enddate); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet(); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); 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); HSSFFont 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); 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.setFillForegroundColor(HSSFColor.ORANGE.index); // stborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 9000); shet1.setColumnWidth(1, 9000); shet1.setColumnWidth(2, 9000); // shet1.setColumnWidth(3, 6000); // shet1.setColumnWidth(4, 6000); // shet1.setColumnWidth(20, 2000); HSSFCellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorder.setFillForegroundColor(HSSFColor.ORANGE.index); styleBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFRow rw1 = shet1.createRow(1); HSSFCell cell; cell = rw1.createCell(0); cell.setCellValue("PWP SESSIONS COMPLETION REPORT BETWEEN " + start + " AND " + end + ""); cell.setCellStyle(style); rw1.setHeightInPoints(30); // Merge the cells shet1.addMergedRegion(new CellRangeAddress(1, 1, 0, 2)); // CREATE HEADING 2 HSSFRow rheading2 = shet1.createRow(2); rheading2.setHeightInPoints(25); HSSFCell cellxx1, cellxx2, cellxx3, cellxx4, cellxx5, cellxx6; cellxx1 = rheading2.createCell(0); cellxx2 = rheading2.createCell(1); cellxx3 = rheading2.createCell(2); // cellxx4=rheading2.createCell(3); // cellxx5=rheading2.createCell(4); // cellxx6=rheading2.createCell(5); cellxx1.setCellValue("COUNTY NAME"); cellxx2.setCellValue("PARTNER NAME"); cellxx3.setCellValue("TOTAL COMPLETED"); // cellxx4.setCellValue("MONTH"); // cellxx5.setCellValue("GIVED SERVICES"); cellxx1.setCellStyle(styleBorder); cellxx2.setCellStyle(styleBorder); cellxx3.setCellStyle(styleBorder); // cellxx4.setCellStyle(styleBorder); // cellxx5.setCellStyle(styleBorder); pos = 3; HSSFCellStyle stylex = wb.createCellStyle(); //stylex.setFillForegroundColor(HSSFColor.LIME.index); //stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); String getCOUNTY = "SELECT * FROM county"; conn.rs = conn.st.executeQuery(getCOUNTY); while (conn.rs.next()) { countyname = conn.rs.getString(2); String getPartner = "SELECT * FROM partner"; conn.rs1 = conn.st1.executeQuery(getPartner); while (conn.rs1.next()) { partnername = conn.rs1.getString(2); achieved = comp1 = comp2 = 0; // DATA FOR THE PARTNER------------------------- String checkCompleted = "SELECT DISTINCT clients.client_id " + "FROM clients JOIN district ON clients.district_id=district.district_id" + " JOIN register2 ON clients.client_id=register2.client_id " + "WHERE district.county_id='" + conn.rs.getString(1) + "' && clients.partner_id='" + conn.rs1.getString(1) + "' && " + " register2.datekey<='" + startdate + "' && register2.value='1' GROUP BY register2.client_id HAVING SUM(value)=13"; conn.rs3 = conn.st3.executeQuery(checkCompleted); if (conn.rs3.next() == true) { conn.rs3.last(); comp1 = conn.rs3.getRow(); conn.rs3.beforeFirst(); } String checkCompleted2 = "SELECT DISTINCT clients.client_id " + "FROM clients JOIN district ON clients.district_id=district.district_id" + " JOIN register2 ON clients.client_id=register2.client_id " + "WHERE district.county_id='" + conn.rs.getString(1) + "' && clients.partner_id='" + conn.rs1.getString(1) + "' && " + " register2.datekey<='" + enddate + "' && register2.value='1' GROUP BY register2.client_id HAVING SUM(value)=13"; conn.rs3 = conn.st3.executeQuery(checkCompleted2); if (conn.rs3.next() == true) { conn.rs3.last(); comp2 = conn.rs3.getRow(); conn.rs3.beforeFirst(); } achieved = comp2 - comp1; System.out.println("county name " + partnername + " partner name " + partnername + " attended : " + achieved); // COMPLETED PER PARTNER HERE-------------------------------- if (achieved > 0) { // OUTPUT HERE TO EXCEL>>>>>>>>>>>>>>>>>>>>>>>>>>>> // CREATE ROW AND ADD DATA TO THE DATA CELLS====================== HSSFRow data = shet1.createRow(pos); data.setHeightInPoints(25); HSSFCell cellx1, cellx2, cellx3, cellx4, cellx5, cellx6; cellx1 = data.createCell(0); cellx2 = data.createCell(1); cellx3 = data.createCell(2); // cellx4=data.createCell(3); // cellx5=data.createCell(4); // cellxx6=rheading2.createCell(5); cellx1.setCellValue(countyname); cellx2.setCellValue(partnername); cellx3.setCellValue(achieved); // cellx4.setCellValue(month); // cellx5.setCellValue(achieved); cellx1.setCellStyle(stylex); cellx2.setCellStyle(stylex); cellx3.setCellStyle(stylex); // cellx4.setCellStyle(stylex); // cellx5.setCellStyle(stylex); System.out.println("county : " + countyname + " partner : " + partnername + " achieved:" + achieved + " month: " + month + " quarter: " + quarter); 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(); } // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_COMPLETION_REPORT.xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:reports.CompletionSummary.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { dbConn conn = new dbConn(); total = completed = comp_receiveService = incomp_receiveService = 0; county_name = partner_name = ""; String yeargt = request.getParameter("year"); year = Integer.parseInt(yeargt); prevyear = year - 1;//from www. j a v a 2 s.com sq1 = prevyear + "" + 1001; sq2 = prevyear + "" + 1231; sq3 = year + "" + 101; sq4 = year + "" + 331; sq5 = year + "" + 401; sq6 = year + "" + 631; sq7 = year + "" + 701; sq8 = year + "" + 931; q1s = Integer.parseInt(sq1); q1e = Integer.parseInt(sq2); q2s = Integer.parseInt(sq3); q2e = Integer.parseInt(sq4); q3s = Integer.parseInt(sq5); q3e = Integer.parseInt(sq6); q4s = Integer.parseInt(sq7); q4e = Integer.parseInt(sq8); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet(); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); // font.setItalic(true); // font.setBoldweight((short)12); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont 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); 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.setFillForegroundColor(HSSFColor.ORANGE.index); // stborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 2000); shet1.setColumnWidth(1, 3500); shet1.setColumnWidth(2, 5000); shet1.setColumnWidth(3, 5000); shet1.setColumnWidth(4, 5000); shet1.setColumnWidth(5, 5000); shet1.setColumnWidth(6, 5000); shet1.setColumnWidth(7, 5000); shet1.setColumnWidth(8, 5000); shet1.setColumnWidth(9, 5000); shet1.setColumnWidth(10, 5000); shet1.setColumnWidth(11, 5000); shet1.setColumnWidth(12, 5000); shet1.setColumnWidth(13, 5000); shet1.setColumnWidth(14, 5000); shet1.setColumnWidth(15, 5000); shet1.setColumnWidth(16, 5000); shet1.setColumnWidth(17, 5000); shet1.setColumnWidth(18, 5000); shet1.setColumnWidth(19, 5000); shet1.setColumnWidth(20, 5000); shet1.setColumnWidth(21, 5000); // shet1.setColumnWidth(20, 2000); HSSFCellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorder.setFillForegroundColor(HSSFColor.ORANGE.index); styleBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFRow rw1 = shet1.createRow(1); HSSFCell cell; cell = rw1.createCell(0); cell.setCellValue("PWP COMPLETION SUMMARY PER QUARTER"); cell.setCellStyle(style); rw1.setHeightInPoints(30); // Merge the cells shet1.addMergedRegion(new CellRangeAddress(1, 1, 0, 19)); // CREATE HEADING 2 HSSFRow rheading2 = shet1.createRow(2); rheading2.setHeightInPoints(25); HSSFCell cellxx1, cellxx2, cellxx3, cellxx4, cellxx5, cellxx6, cellxx7, cellxx8, cellxx9, cellxx10, cellxx11, cellxx12, cellxx13, cellxx14, cellxx15, cellxx16, cellxx17, cellxx18, cellxx19, cellxx20; 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); cellxx16 = rheading2.createCell(15); cellxx17 = rheading2.createCell(16); cellxx18 = rheading2.createCell(17); cellxx19 = rheading2.createCell(18); // int prevyear=year-1; cellxx3.setCellValue("OCT - DEC " + prevyear); cellxx7.setCellValue("JAN - MARCH " + year); cellxx11.setCellValue("APRIL - JUNE " + year); cellxx15.setCellValue("JULY - SEPT" + year); shet1.addMergedRegion(new CellRangeAddress(2, 2, 2, 5)); shet1.addMergedRegion(new CellRangeAddress(2, 2, 6, 9)); shet1.addMergedRegion(new CellRangeAddress(2, 2, 10, 13)); shet1.addMergedRegion(new CellRangeAddress(2, 2, 14, 17)); 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); cellxx16.setCellStyle(styleBorder); cellxx17.setCellStyle(styleBorder); cellxx18.setCellStyle(styleBorder); cellxx19.setCellStyle(styleBorder); HSSFRow rw4 = shet1.createRow(3); rw4.setHeightInPoints(75); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell1, cell2, cell3, cell4, cell5, cell6, cell7, cell8, cell9, cell10, cell11, cell12, cell13, cell14, cell15, cell16, cell17, cell18, cell19, cell20; HSSFCell cell21, cell22; cell1 = rw4.createCell(0); cell2 = rw4.createCell(1); cell3 = rw4.createCell(2); cell4 = rw4.createCell(3); cell5 = rw4.createCell(4); cell6 = rw4.createCell(5); cell7 = rw4.createCell(6); cell8 = rw4.createCell(7); cell9 = rw4.createCell(8); cell10 = rw4.createCell(9); cell11 = rw4.createCell(10); cell12 = rw4.createCell(11); cell13 = rw4.createCell(12); cell14 = rw4.createCell(13); cell15 = rw4.createCell(14); cell16 = rw4.createCell(15); cell17 = rw4.createCell(16); cell18 = rw4.createCell(17); cell19 = rw4.createCell(18); cell20 = rw4.createCell(19); cell21 = rw4.createCell(20); cell22 = rw4.createCell(21); cell1.setCellValue("COUNTY NAME"); cell2.setCellValue("PARTNER NAME"); cell3.setCellValue("TOTAL ENROLLED"); cell4.setCellValue("COMPLETED ALL SESSIONS"); cell5.setCellValue("COMPLETED ALL SESSIONS AND RECEIVED SERVICES"); cell6.setCellValue("DID NOT COMPLETE ALL SESSIONS BUT RECEIVED SERVICES"); cell7.setCellValue("TOTAL ENROLLED"); cell8.setCellValue("COMPLETED ALL SESSIONS"); cell9.setCellValue("COMPLETED ALL SESSIONS AND RECEIVED SERVICES"); cell10.setCellValue("DID NOT COMPLETE ALL SESSIONS BUT RECEIVED SERVICES"); cell11.setCellValue("TOTAL ENROLLED"); cell12.setCellValue("COMPLETED ALL SESSIONS"); cell13.setCellValue("COMPLETED ALL SESSIONS AND RECEIVED SERVICES"); cell14.setCellValue("DID NOT COMPLETE ALL SESSIONS BUT RECEIVED SERVICES"); cell15.setCellValue("TOTAL ENROLLED"); cell16.setCellValue("COMPLETED ALL SESSIONS"); cell17.setCellValue("COMPLETED ALL SESSIONS AND RECEIVED SERVICES"); cell18.setCellValue("DID NOT COMPLETE ALL SESSIONS BUT RECEIVED SERVICES"); cell19.setCellValue("TOTAL"); // cell20.setCellValue("Partner Tested"); // cell21.setCellValue("Children Tested"); // cell22.setCellValue("Disclosed Status"); pos = 4; HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); cell1.setCellStyle(stylex); cell2.setCellStyle(stylex); cell3.setCellStyle(stylex); cell4.setCellStyle(stylex); cell5.setCellStyle(stylex); cell6.setCellStyle(stylex); cell7.setCellStyle(stylex); cell8.setCellStyle(stylex); cell9.setCellStyle(stylex); cell10.setCellStyle(stylex); cell11.setCellStyle(stylex); cell12.setCellStyle(stylex); cell13.setCellStyle(stylex); cell14.setCellStyle(stylex); cell15.setCellStyle(stylex); cell16.setCellStyle(stylex); cell17.setCellStyle(stylex); cell18.setCellStyle(stylex); cell19.setCellStyle(stylex); //cell20.setCellStyle(stylex); //cell21.setCellStyle(stylex); //cell22.setCellStyle(stylex); String county_selector = "SELECT county_name,county_id FROM county"; conn.rs = conn.st.executeQuery(county_selector); while (conn.rs.next()) { // GET COUNTY NAME county_name = ""; county_name = conn.rs.getString(1); county_id = conn.rs.getString(2); String getpartners = "SELECT * FROM partner"; conn.rs0 = conn.st0.executeQuery(getpartners); while (conn.rs0.next()) { partner_id = conn.rs0.getString(1); partner_name = conn.rs0.getString(2); q1c = q1i = q2c = q2i = q3c = q3i = q4c = q4i = 0; q1comp = q2comp = q3comp = q4comp = 0; q1t = q2t = q3t = q4t = 0; total = 0; System.out.println("partner : " + partner_name); String distselector = "SELECT district_id FROM district WHERE county_id='" + county_id + "'"; conn.rs1 = conn.st1.executeQuery(distselector); while (conn.rs1.next()) { district_id = ""; district_id = conn.rs1.getString(1); String getq = "SELECT COUNT(client_id) FROM clients WHERE district_id='" + district_id + "' && partner_id='" + partner_id + "' && (timestamp BETWEEN '2013-10-01' AND '2013-12-31')"; conn.rs2 = conn.st2.executeQuery(getq); while (conn.rs2.next()) { q1t += conn.rs2.getInt(1); } String getq1 = "SELECT COUNT(client_id) FROM clients WHERE district_id='" + district_id + "' && partner_id='" + partner_id + "' && (timestamp BETWEEN '2014-1-01' AND '2014-3-31')"; conn.rs2 = conn.st2.executeQuery(getq1); while (conn.rs2.next()) { q2t += conn.rs2.getInt(1); } String getq2 = "SELECT COUNT(client_id) FROM clients WHERE district_id='" + district_id + "' && partner_id='" + partner_id + "' && (timestamp BETWEEN '2014-4-01' AND '2014-6-31')"; conn.rs2 = conn.st2.executeQuery(getq2); while (conn.rs2.next()) { q3t += conn.rs2.getInt(1); } String getq3 = "SELECT COUNT(client_id) FROM clients WHERE district_id='" + district_id + "' && partner_id='" + partner_id + "' && (timestamp BETWEEN '2014-7-01' AND '2014-9-30')"; conn.rs2 = conn.st2.executeQuery(getq3); while (conn.rs2.next()) { q4t += conn.rs2.getInt(1); } String getCompleted = "SELECT COUNT(DISTINCT clients.client_id) FROM clients JOIN register2 ON clients.client_id=register2.client_id " + "WHERE clients.partner_id='" + partner_id + "' && register2.month>9 && register2.month<=12 && clients.district_id='" + district_id + "' && register2.year='2014' && register2.value=1 GROUP BY register2.client_id HAVING SUM(register2.value)=13"; conn.rs2 = conn.st2.executeQuery(getCompleted); if (conn.rs2.next() == true) { conn.rs2.last(); q1comp += conn.rs2.getRow(); conn.rs2.beforeFirst(); } String getCompleted1 = "SELECT COUNT(DISTINCT clients.client_id) FROM clients JOIN register2 ON clients.client_id=register2.client_id " + "WHERE clients.partner_id='" + partner_id + "' && register2.month>0 && register2.month<=3 && clients.district_id='" + district_id + "' && register2.year='2014' && register2.value=1 GROUP BY register2.client_id HAVING SUM(register2.value)=13"; conn.rs2 = conn.st2.executeQuery(getCompleted1); if (conn.rs2.next() == true) { conn.rs2.last(); q2comp += conn.rs2.getRow(); conn.rs2.beforeFirst(); } String getCompleted2 = "SELECT COUNT(DISTINCT clients.client_id) FROM clients JOIN register2 ON clients.client_id=register2.client_id " + "WHERE clients.partner_id='" + partner_id + "' && register2.month>3 && register2.month<=6 && clients.district_id='" + district_id + "' && register2.year='2014' && register2.value=1 GROUP BY register2.client_id HAVING SUM(register2.value)=13"; conn.rs2 = conn.st2.executeQuery(getCompleted2); if (conn.rs2.next() == true) { conn.rs2.last(); q3comp += conn.rs2.getRow(); conn.rs2.beforeFirst(); } String getCompleted3 = "SELECT COUNT(DISTINCT clients.client_id) FROM clients JOIN register2 ON clients.client_id=register2.client_id " + "WHERE clients.partner_id='" + partner_id + "' && register2.month>6 && register2.month<=9 && clients.district_id='" + district_id + "' && register2.year='2014' && register2.value=1 GROUP BY register2.client_id HAVING SUM(register2.value)=13"; conn.rs2 = conn.st2.executeQuery(getCompleted3); if (conn.rs2.next() == true) { conn.rs2.last(); q4comp += conn.rs2.getRow(); conn.rs2.beforeFirst(); } // GET DATA FOR THE SERVICES GIVEN===================================================== } if (q1t > 0 || q2t > 0 || q3t > 0 || q4t > 0) { total = q1t + q2t + q3t + q4t; HSSFRow rwx = shet1.createRow(pos); rwx.setHeightInPoints(20); HSSFCell cellx1, cellx2, cellx3, cellx4, cellx5, cellx6, cellx7, cellx8, cellx9, cellx10; HSSFCell cellx11, cellx12, cellx13, cellx14, cellx15, cellx16, cellx17, cellx18, cellx19, cellx20, cellx21, cellx22; cellx1 = rwx.createCell(0); cellx2 = rwx.createCell(1); cellx3 = rwx.createCell(2); cellx4 = rwx.createCell(3); cellx5 = rwx.createCell(4); cellx6 = rwx.createCell(5); cellx7 = rwx.createCell(6); cellx8 = rwx.createCell(7); cellx9 = rwx.createCell(8); cellx10 = rwx.createCell(9); cellx11 = rwx.createCell(10); cellx12 = rwx.createCell(11); cellx13 = rwx.createCell(12); cellx14 = rwx.createCell(13); cellx15 = rwx.createCell(14); cellx16 = rwx.createCell(15); cellx17 = rwx.createCell(16); cellx18 = rwx.createCell(17); cellx19 = rwx.createCell(18); // cellx20=rwx.createCell(19); // cellx21=rwx.createCell(20); // cellx22=rwx.createCell(21); cellx1.setCellValue(county_name); cellx2.setCellValue(partner_name); cellx3.setCellValue(q1t); cellx4.setCellValue(q1comp); cellx5.setCellValue(q1c); cellx6.setCellValue(q1i); cellx7.setCellValue(q2t); cellx8.setCellValue(q2comp - q1comp); cellx9.setCellValue(q2c); cellx10.setCellValue(q2i); cellx11.setCellValue(q3t); cellx12.setCellValue(q3comp - q2comp); cellx13.setCellValue(q3c); cellx14.setCellValue(q3i); cellx15.setCellValue(q4t); cellx16.setCellValue(q4comp - q3comp); cellx17.setCellValue(q4c); cellx18.setCellValue(q4i); cellx19.setCellValue(total); cellx1.setCellStyle(stborder); cellx2.setCellStyle(stborder); cellx3.setCellStyle(stborder); cellx4.setCellStyle(stborder); cellx5.setCellStyle(stborder); cellx6.setCellStyle(stborder); cellx7.setCellStyle(stborder); cellx8.setCellStyle(stborder); cellx9.setCellStyle(stborder); cellx10.setCellStyle(stborder); cellx11.setCellStyle(stborder); cellx12.setCellStyle(stborder); cellx13.setCellStyle(stborder); cellx14.setCellStyle(stborder); cellx15.setCellStyle(stborder); cellx16.setCellStyle(stborder); cellx17.setCellStyle(stborder); cellx18.setCellStyle(stborder); cellx19.setCellStyle(stborder); pos++; System.out.println("here partner : " + partner_name); } } //END PARTNER SELECTION } //end of county---------------------- if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_Completion_Rate_Summary.xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:reports.countyreport.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.//from ww w. j ava 2s .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"); } // 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 < 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.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(countynames.get(u).toString().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(countynames.get(u).toString()); cel3.setCellStyle(orangestyle); HSSFCell cel4 = header3.createCell(1); cel4.setCellValue(""); cel4.setCellStyle(orangestyle); //blank cells for purpose of clear worksheet only for (int x = 2; x <= 3; x++) { HSSFCell cel = header3.createCell(x); cel.setCellValue(""); cel.setCellStyle(orangestyle); } HSSFCell cel5 = header3.createCell(4); cel5.setCellValue(""); cel5.setCellStyle(orangestyle); HSSFCell cel6 = header3.createCell(5); cel6.setCellValue(""); cel6.setCellStyle(orangestyle); //create a merged region shet2.addMergedRegion(new CellRangeAddress(2, 2, 0, 5)); //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 = " district.county_id='" + countyids.get(u).toString() + "' and ass_date between '" + startdate + "' and '" + enddate + "' "; //if the current countyid is 0, then the where code should not specify the county name if (countyids.get(u).toString().equals("1000")) { mywhere = " ass_date between '" + startdate + "' and '" + enddate + "' "; } String supervisor = ""; String dateofvisit = ""; String strengths = ""; String constraints = ""; // String loadbasicdetails="select * from backgroundinfor join staff on backgroundinfor.supervisor=staff.staff_id where "+mywhere+" "; String loadbasicdetails = "select strengths,constraints,county_name from 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 " + mywhere + " order by county_name"; //System.out.println("~~~~ "+loadbasicdetails); conn.rs = conn.st.executeQuery(loadbasicdetails); //if this is the overal query ArrayList countycomments = new ArrayList(); if (countyids.get(u).toString().equals("1000")) { while (conn.rs.next()) { // supervisor=conn.rs.getString("fname")+" "+conn.rs.getString("mname"); //dateofvisit=conn.rs.getString("ass_date"); if (!conn.rs.getString("strengths").equals("")) { //add the county header if it has not been added before only if (countycomments.contains(conn.rs.getString("county_name"))) { } else { countycomments.add(conn.rs.getString("county_name")); strengths += "________________________________________" + conn.rs.getString("county_name") + " County ________________________________________\n"; constraints += "________________________________________" + conn.rs.getString("county_name") + " County ________________________________________\n"; } } strengths += conn.rs.getString("strengths"); constraints += conn.rs.getString("constraints"); if (!conn.rs.getString("strengths").equals("")) { strengths += "\n"; constraints += "\n"; } } } else { 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"); if (!conn.rs.getString("strengths").equals("")) { strengths += "\n"; constraints += "\n"; } } } //end of while //================Create the second header================= //create header three HSSFRow header4 = shet2.createRow(4); HSSFCell cel = header4.createCell(0); cel.setCellValue(""); cel.setCellStyle(innerdata_style); HSSFCell cell = header4.createCell(1); cell.setCellValue(""); 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(""); cell5.setCellStyle(innerdata_style); HSSFCell cell6 = header4.createCell(5); cell6.setCellValue(""); 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"; String gettables = "SELECT domain_name,avg(value) as domainvalue,section_name ,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 domain_name,avg(value) as domainvalue,section_name ,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 = 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); //str.setRowStyle(rowstyle); 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 gen g = new gen(); int rwheight1 = g.countLines(strengths); rwheight1 = rwheight1 * 17; str.setHeightInPoints(rwheight1); 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); str2.setRowStyle(rowstyle); 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)); //count the number of lines then multiply by a certain fixed unit int rwheight = g.countLines(constraints); rwheight = rwheight * 17; str2.setHeightInPoints(rwheight); 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_COUNTY_REPORT_" + 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.enrollmentReport.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException, InvalidFormatException { session = request.getSession();// www . ja va2 s . c o m dbConn conn = new dbConn(); int m1 = 0, m2 = 0; String d1 = "", d2 = "", y1 = "", y2 = ""; // pepfaryear=2015; pepfaryear = Integer.parseInt(request.getParameter("year")); prevYear = pepfaryear - 1; // COPY FILE TO BE WRITTEN TO Path original = Paths.get(getServletContext().getRealPath("/ENROLLMENT_TEMPLATE.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/ENROLLMENT_TEMPLATE_2.xlsm")); //new file System.out.println("origin : " + original + " destination : " + destination); try { Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING); System.out.println("file copied----------------"); } catch (IOException x) { //catch all for IO problems System.out.println("fine not copied"); } String allpath = getServletContext().getRealPath("/ENROLLMENT_TEMPLATE_2.xlsm"); XSSFWorkbook wb; OPCPackage pkg = OPCPackage.open(allpath); wb = new XSSFWorkbook(pkg); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFSheet shet1 = wb.getSheet("Sheet1"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 11); font.setFontName("Arial Black"); // font.setItalic(true); // font.setBoldweight((short)12); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(XSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); // font2.setFontHeightInPoints((short)15); font2.setFontName("Arial Black"); // font.setItalic(true); // font2.setBoldweight((short)18); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(XSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(XSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(XSSFCellStyle.BORDER_THIN); stborder.setBorderRight(XSSFCellStyle.BORDER_THIN); // stborder.setFillForegroundColor(HSSFColor.ORANGE.index); // stborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stborder.setAlignment(XSSFCellStyle.ALIGN_CENTER); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 6000); shet1.setColumnWidth(1, 6000); shet1.setColumnWidth(2, 6000); shet1.setColumnWidth(3, 6000); shet1.setColumnWidth(4, 6000); shet1.setColumnWidth(5, 7000); // shet1.setColumnWidth(20, 2000); XSSFCellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(XSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(XSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(XSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(XSSFCellStyle.BORDER_THIN); styleBorder.setFillForegroundColor(HSSFColor.ORANGE.index); styleBorder.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); styleBorder.setAlignment(XSSFCellStyle.ALIGN_CENTER); // CREATE HEADING 2 XSSFRow rheading2 = shet1.createRow(0); rheading2.setHeightInPoints(25); XSSFCell cellxx1, cellxx2, cellxx3, cellxx4, cellxx5, cellxx6, cellxx7, cellxx8; cellxx1 = rheading2.createCell(0); cellxx2 = rheading2.createCell(1); cellxx3 = rheading2.createCell(2); cellxx4 = rheading2.createCell(3); cellxx5 = rheading2.createCell(4); cellxx6 = rheading2.createCell(5); cellxx7 = rheading2.createCell(6); cellxx8 = rheading2.createCell(7); cellxx1.setCellValue("COUNTY NAME"); cellxx2.setCellValue("PARTNER NAME"); cellxx3.setCellValue("DISTRICT NAME"); cellxx4.setCellValue("YEAR"); cellxx5.setCellValue("MONTH"); cellxx6.setCellValue("TOTAL ENROLLED"); cellxx7.setCellValue("GENDER"); cellxx8.setCellValue("AGE BRACKET"); cellxx1.setCellStyle(styleBorder); cellxx2.setCellStyle(styleBorder); cellxx3.setCellStyle(styleBorder); cellxx4.setCellStyle(styleBorder); cellxx5.setCellStyle(styleBorder); cellxx6.setCellStyle(styleBorder); cellxx7.setCellStyle(styleBorder); cellxx8.setCellStyle(styleBorder); pos = 1; XSSFCellStyle stylex = wb.createCellStyle(); //stylex.setFillForegroundColor(HSSFColor.LIME.index); //stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(XSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(XSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(XSSFCellStyle.BORDER_THIN); stylex.setBorderRight(XSSFCellStyle.BORDER_THIN); stylex.setAlignment(XSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); String getData = "SELECT county.county_name,partner.partner_name,district.district_name, " + "extract(YEAR FROM registration_date) AS YEAR, " + "CASE " + "WHEN extract(MONTH FROM registration_date)=1 THEN 'JAN' " + "WHEN extract(MONTH FROM registration_date)=2 THEN 'FEB' " + "WHEN extract(MONTH FROM registration_date)=3 THEN 'MAR' " + "WHEN extract(MONTH FROM registration_date)=4 THEN 'APR' " + "WHEN extract(MONTH FROM registration_date)=5 THEN 'MAY' " + "WHEN extract(MONTH FROM registration_date)=6 THEN 'JUN' " + "WHEN extract(MONTH FROM registration_date)=7 THEN 'JUL' " + "WHEN extract(MONTH FROM registration_date)=8 THEN 'AUG' " + "WHEN extract(MONTH FROM registration_date)=9 THEN 'SEPT' " + "WHEN extract(MONTH FROM registration_date)=10 THEN 'OCT' " + "WHEN extract(MONTH FROM registration_date)=11 THEN 'NOV' " + "WHEN extract(MONTH FROM registration_date)=12 THEN 'DEC' " + "ELSE 'NO REGISTRATION DATE' " + "END AS MONTH " + ",COUNT(personal_information.client_id) AS REGISTERED," + "CASE " + "WHEN personal_information.gender ='Female' THEN 'F' " + "WHEN personal_information.gender ='Male' THEN 'M' " + "ELSE 'NO GENDER' " + "END AS GENDER, " + "CASE" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above'" + " ELSE 'NO DATE OF BIRTH'" + " END AS AGEBRACKET " + "FROM personal_information " + "JOIN partner ON partner.partner_id=personal_information.partner_id " + "JOIN district ON district.district_id=personal_information.district_id " + "JOIN county ON county.county_id=district.county_id WHERE registration_date BETWEEN '" + prevYear + "-10-01' AND '" + pepfaryear + "-09-30' " + "GROUP BY county.county_name,district.district_name,partner.partner_name,YEAR, MONTH,AGEBRACKET,personal_information.gender " + "ORDER BY county.county_name,district.district_name,partner.partner_name,YEAR, MONTH"; conn.rs = conn.st.executeQuery(getData); while (conn.rs.next()) { countyname = conn.rs.getString(1); partnername = conn.rs.getString(2); districtname = conn.rs.getString(3); year = conn.rs.getInt(4); month = conn.rs.getString(5); achieved = conn.rs.getInt(6); gender = conn.rs.getString(7); agebracket = conn.rs.getString(8); // CREATE ROW AND ADD DATA TO THE DATA CELLS====================== XSSFRow data = shet1.createRow(pos); data.setHeightInPoints(25); XSSFCell cellx1, cellx2, cellx3, cellx4, cellx5, cellx6, cellx7, cellx8; cellx1 = data.createCell(0); cellx2 = data.createCell(1); cellx3 = data.createCell(2); cellx4 = data.createCell(3); cellx5 = data.createCell(4); cellx6 = data.createCell(5); cellx7 = data.createCell(6); cellx8 = data.createCell(7); cellx1.setCellValue(countyname); cellx2.setCellValue(partnername); cellx3.setCellValue(districtname); cellx4.setCellValue(year); cellx5.setCellValue(month); cellx6.setCellValue(achieved); cellx7.setCellValue(gender); cellx8.setCellValue(agebracket); cellx1.setCellStyle(stylex); cellx2.setCellStyle(stylex); cellx3.setCellStyle(stylex); cellx4.setCellStyle(stylex); cellx5.setCellStyle(stylex); cellx6.setCellStyle(stylex); cellx7.setCellStyle(stylex); cellx8.setCellStyle(stylex); // System.out.println("county : "+countyname+" partner : "+partnername+" ahieved:"+achieved+" month: "+month+" quarter: "+quarter); pos++; } System.out.println("report generated : " + pos); // write it as an excel attachment IdGenerator IG = new IdGenerator(); if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } if (pos == 1) { session.setAttribute("noEnrollments", "<font color=\"red\">No enrollments found in pepfar year </font> <font color=\"black\"><b>" + pepfaryear + "</b></font>"); pkg.close(); response.sendRedirect("enrollments.jsp"); } else { ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_new_enrollment_report_created_on_" + IG.timestamp() + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); } }
From source file:reports.excelstaticreports.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {//ww w .j a v 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 = ""; String county = ""; String form = "kmmp"; 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 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 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 , 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++; } 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 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++; } 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.genderexcel.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {//from w ww.j a va 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 = ""; String form = "gender"; //===================================================================================================== 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); 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("Prevention Sub Area 12: Gender"); cl0.setCellStyle(stylex); for (int a = 1; a <= 5; 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 <= 5; 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(""); cl3.setCellStyle(stylex); HSSFCell cl31 = rw2.createCell(1); cl31.setCellValue(""); cl31.setCellStyle(stylex); String head[] = { "AGE", "MALE", "FEMALE", "TOTAL" }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rw2.createCell(a + 2); clx.setCellValue(head[a]); clx.setCellStyle(stylex); } //shet.addMergedRegion(new CellRangeAddress(3,10,0,0)); shet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); shet.addMergedRegion(new CellRangeAddress(1, 1, 0, 5)); shet.addMergedRegion(new CellRangeAddress(2, 2, 1, 1)); shet.setColumnWidth(0, 2500); shet.setColumnWidth(1, 25000); shet.setColumnWidth(2, 5000); shet.setColumnWidth(3, 5000); shet.setColumnWidth(4, 5000); shet.setColumnWidth(5, 5000); getexistingdata = "select sum(P121DM0) as P121DM0, sum(P121DF0) as P121DF0, sum(P121DM10) as P121DM10, sum(P121DF10) as P121DF10, sum(P121DM15) as P121DM15, sum(P121DF15) as P121DF15, sum(P121DM20) as P121DM20, sum(P121DF20) as P121DF20, sum(P121DM25) as P121DM25, sum(P121DF25) as P121DF25, sum(P121DMT) as P121DMT, sum(P121DFT) as P121DFT, sum(P121DTT) as P121DTT, sum(P122DM0) as P122DM0, sum(P122DF0) as P122DF0, sum(P122DM15) as P122DM15, sum(P122DF15) as P122DF15, sum(P122DM25) as P122DM25, sum(P122DF25) as P122DF25, sum(P122DMT) as P122DMT, sum(P122DFT) as P122DFT, sum(P122DTT) as P122DTT, sum(P123DM0) as P123DM0, sum(P123DF0) as P123DF0, sum(P123DM15) as P123DM15, sum(P123DF15) as P123DF15, sum(P123DM25) as P123DM25, sum(P123DF25) as P123DF25, sum(P123DMT) as P123DMT, sum(P123DFT) as P123DFT, sum(P123DTT) as P123DTT, sum(P124DM0) as P124DM0, sum(P124DF0) as P124DF0, sum(P124DM15) as P124DM15, sum(P124DF15) as P124DF15, sum(P124DM25) as P124DM25, sum(P124DF25) as P124DF25, sum(P124DMT) as P124DMT, sum(P124DFT) as P124DFT, sum(P124DTT) as P124DTT, sum(GEND_GBV9M) as GEND_GBV9M, sum(GEND_GBV9F) as GEND_GBV9F, sum(GEND_GBV9) as GEND_GBV9, sum(GEND_GBV14M) as GEND_GBV14M, sum(GEND_GBV14F) as GEND_GBV14F, sum(GEND_GBV14) as GEND_GBV14, sum(GEND_GBV17M) as GEND_GBV17M, sum(GEND_GBV17F) as GEND_GBV17F, sum(GEND_GBV17) as GEND_GBV17, sum(GEND_GBV24M) as GEND_GBV24M, sum(GEND_GBV24F) as GEND_GBV24F, sum(GEND_GBV24) as GEND_GBV24, sum(GEND_GBV25M) as GEND_GBV25M, sum(GEND_GBV25F) as GEND_GBV25F, sum(GEND_GBV25) as GEND_GBV25, sum(GEND_GBVM) as GEND_GBVM, sum(GEND_GBVF) as GEND_GBVF, sum(GEND_GBV) as GEND_GBV, sum(P121D0) as P121D0, sum(P121D10) as P121D10, sum(P121D15) as P121D15, sum(P121D20) as P121D20, sum(P121D25) as P121D25, sum(P122D0) as P122D0, sum(P122D15) as P122D15, sum(P122D25) as P122D25, sum(P123D0) as P123D0, sum(P123D15) as P123D15, sum(P123D25) as P123D25, sum(P124D0) as P124D0, sum(P124D15) as P124D15, sum(P124D25) as P124D25 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 P121DM0 = ""; String P121DF0 = ""; String P121DM10 = ""; String P121DF10 = ""; String P121DM15 = ""; String P121DF15 = ""; String P121DM20 = ""; String P121DF20 = ""; String P121DM25 = ""; String P121DF25 = ""; String P121DMT = ""; String P121DFT = ""; String P121DTT = ""; String P122DM0 = ""; String P122DF0 = ""; String P122DM15 = ""; String P122DF15 = ""; String P122DM25 = ""; String P122DF25 = ""; String P122DMT = ""; String P122DFT = ""; String P122DTT = ""; String P123DM0 = ""; String P123DF0 = ""; String P123DM15 = ""; String P123DF15 = ""; String P123DM25 = ""; String P123DF25 = ""; String P123DMT = ""; String P123DFT = ""; String P123DTT = ""; String P124DM0 = ""; String P124DF0 = ""; String P124DM15 = ""; String P124DF15 = ""; String P124DM25 = ""; String P124DF25 = ""; String P124DMT = ""; String P124DFT = ""; String P124DTT = ""; String GEND_GBV9M = ""; String GEND_GBV9F = ""; String GEND_GBV9 = ""; String GEND_GBV14M = ""; String GEND_GBV14F = ""; String GEND_GBV14 = ""; String GEND_GBV17M = ""; String GEND_GBV17F = ""; String GEND_GBV17 = ""; String GEND_GBV24M = ""; String GEND_GBV24F = ""; String GEND_GBV24 = ""; String GEND_GBV25M = ""; String GEND_GBV25F = ""; String GEND_GBV25 = ""; String GEND_GBVM = ""; String GEND_GBVF = ""; String GEND_GBV = ""; String P121D0 = ""; String P121D10 = ""; String P121D15 = ""; String P121D20 = ""; String P121D25 = ""; String P122D0 = ""; String P122D15 = ""; String P122D25 = ""; String P123D0 = ""; String P123D15 = ""; String P123D25 = ""; String P124D0 = ""; String P124D15 = ""; String P124D25 = ""; 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 //====================================================================p122 P121DM0 = conn.rs.getString("P121DM0"); if (P121DM0 == null) { P121DM0 = ""; } P121DF0 = conn.rs.getString("P121DF0"); if (P121DF0 == null) { P121DF0 = ""; } P121DM10 = conn.rs.getString("P121DM10"); if (P121DM10 == null) { P121DM10 = ""; } P121DF10 = conn.rs.getString("P121DF10"); if (P121DF10 == null) { P121DF10 = ""; } P121DM15 = conn.rs.getString("P121DM15"); if (P121DM15 == null) { P121DM15 = ""; } P121DF15 = conn.rs.getString("P121DF15"); if (P121DF15 == null) { P121DF15 = ""; } P121DM20 = conn.rs.getString("P121DM20"); if (P121DM20 == null) { P121DM20 = ""; } P121DF20 = conn.rs.getString("P121DF20"); if (P121DF20 == null) { P121DF20 = ""; } P121DM25 = conn.rs.getString("P121DM25"); if (P121DM25 == null) { P121DM25 = ""; } P121DF25 = conn.rs.getString("P121DF25"); if (P121DF25 == null) { P121DF25 = ""; } P121DMT = conn.rs.getString("P121DMT"); if (P121DMT == null) { P121DMT = ""; } P121DFT = conn.rs.getString("P121DFT"); if (P121DFT == null) { P121DFT = ""; } P121DTT = conn.rs.getString("P121DTT"); if (P121DTT == null) { P121DTT = ""; } //====================================================================p122 P122DM0 = conn.rs.getString("P122DM0"); if (P122DM0 == null) { P122DM0 = ""; } P122DF0 = conn.rs.getString("P122DF0"); if (P122DF0 == null) { P122DF0 = ""; } P122DM15 = conn.rs.getString("P122DM15"); if (P122DM15 == null) { P122DM15 = ""; } P122DF15 = conn.rs.getString("P122DF15"); if (P122DF15 == null) { P122DF15 = ""; } P122DM25 = conn.rs.getString("P122DM25"); if (P122DM25 == null) { P122DM25 = ""; } P122DF25 = conn.rs.getString("P122DF25"); if (P122DF25 == null) { P122DF25 = ""; } P122DMT = conn.rs.getString("P122DMT"); if (P122DMT == null) { P122DMT = ""; } P122DFT = conn.rs.getString("P122DFT"); if (P122DFT == null) { P122DFT = ""; } P122DTT = conn.rs.getString("P122DTT"); if (P122DTT == null) { P122DTT = ""; } //====================================================================p123 P123DM0 = conn.rs.getString("P123DM0"); if (P123DM0 == null) { P123DM0 = ""; } P123DF0 = conn.rs.getString("P123DF0"); if (P123DF0 == null) { P123DF0 = ""; } P123DM15 = conn.rs.getString("P123DM15"); if (P123DM15 == null) { P123DM15 = ""; } P123DF15 = conn.rs.getString("P123DF15"); if (P123DF15 == null) { P123DF15 = ""; } P123DM25 = conn.rs.getString("P123DM25"); if (P123DM25 == null) { P123DM25 = ""; } P123DF25 = conn.rs.getString("P123DF25"); if (P123DF25 == null) { P123DF25 = ""; } P123DMT = conn.rs.getString("P123DMT"); if (P123DMT == null) { P123DMT = ""; } P123DFT = conn.rs.getString("P123DFT"); if (P123DFT == null) { P123DFT = ""; } P123DTT = conn.rs.getString("P123DTT"); if (P123DTT == null) { P123DTT = ""; } //====================================================================p124 P124DM0 = conn.rs.getString("P124DM0"); if (P124DM0 == null) { P124DM0 = ""; } P124DF0 = conn.rs.getString("P124DF0"); if (P124DF0 == null) { P124DF0 = ""; } P124DM15 = conn.rs.getString("P124DM15"); if (P124DM15 == null) { P124DM15 = ""; } P124DF15 = conn.rs.getString("P124DF15"); if (P124DF15 == null) { P124DF15 = ""; } P124DM25 = conn.rs.getString("P124DM25"); if (P124DM25 == null) { P124DM25 = ""; } P124DF25 = conn.rs.getString("P124DF25"); if (P124DF25 == null) { P124DF25 = ""; } P124DMT = conn.rs.getString("P124DMT"); if (P124DMT == null) { P124DMT = ""; } P124DFT = conn.rs.getString("P124DFT"); if (P124DFT == null) { P124DFT = ""; } P124DTT = conn.rs.getString("P124DTT"); if (P124DTT == null) { P124DTT = ""; } //=========================================================GEND_GBV GEND_GBV9M = conn.rs.getString("GEND_GBV9M"); if (GEND_GBV9M == null) { GEND_GBV9M = ""; } GEND_GBV9F = conn.rs.getString("GEND_GBV9F"); if (GEND_GBV9F == null) { GEND_GBV9F = ""; } GEND_GBV9 = conn.rs.getString("GEND_GBV9"); if (GEND_GBV9 == null) { GEND_GBV9 = ""; } //============ GEND_GBV14M = conn.rs.getString("GEND_GBV14M"); if (GEND_GBV14M == null) { GEND_GBV14M = ""; } GEND_GBV14F = conn.rs.getString("GEND_GBV14F"); if (GEND_GBV14F == null) { GEND_GBV14F = ""; } GEND_GBV14 = conn.rs.getString("GEND_GBV14"); if (GEND_GBV14 == null) { GEND_GBV14 = ""; } //======= //============ GEND_GBV17M = conn.rs.getString("GEND_GBV17M"); if (GEND_GBV17M == null) { GEND_GBV17M = ""; } GEND_GBV17F = conn.rs.getString("GEND_GBV17F"); if (GEND_GBV17F == null) { GEND_GBV17F = ""; } GEND_GBV17 = conn.rs.getString("GEND_GBV17"); if (GEND_GBV17 == null) { GEND_GBV17 = ""; } //======= //============ GEND_GBV24M = conn.rs.getString("GEND_GBV24M"); if (GEND_GBV24M == null) { GEND_GBV24M = ""; } GEND_GBV24F = conn.rs.getString("GEND_GBV24F"); if (GEND_GBV24F == null) { GEND_GBV24F = ""; } GEND_GBV24 = conn.rs.getString("GEND_GBV24"); if (GEND_GBV24 == null) { GEND_GBV24 = ""; } //======= //============ GEND_GBV25M = conn.rs.getString("GEND_GBV25M"); if (GEND_GBV25M == null) { GEND_GBV25M = ""; } GEND_GBV25F = conn.rs.getString("GEND_GBV25F"); if (GEND_GBV25F == null) { GEND_GBV25F = ""; } GEND_GBV25 = conn.rs.getString("GEND_GBV25"); if (GEND_GBV25 == null) { GEND_GBV25 = ""; } //======= //============ GEND_GBVM = conn.rs.getString("GEND_GBVM"); if (GEND_GBVM == null) { GEND_GBVM = ""; } GEND_GBVF = conn.rs.getString("GEND_GBVF"); if (GEND_GBVF == null) { GEND_GBVF = ""; } GEND_GBV = conn.rs.getString("GEND_GBV"); if (GEND_GBV == null) { GEND_GBV = ""; } //======= //added totals //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ P121D0 = conn.rs.getString("P121D0"); if (P121D0 == null) { P121D0 = ""; } P121D10 = conn.rs.getString("P121D10"); if (P121D10 == null) { P121D10 = ""; } P121D15 = conn.rs.getString("P121D15"); if (P121D15 == null) { P121D15 = ""; } P121D20 = conn.rs.getString("P121D20"); if (P121D20 == null) { P121D20 = ""; } P121D25 = conn.rs.getString("P121D25"); if (P121D25 == null) { P121D25 = ""; } P122D0 = conn.rs.getString("P122D0"); if (P122D0 == null) { P122D0 = ""; } P122D15 = conn.rs.getString("P122D15"); if (P122D15 == null) { P122D15 = ""; } P122D25 = conn.rs.getString("P122D25"); if (P122D25 == null) { P122D25 = ""; } P123D0 = conn.rs.getString("P123D0"); if (P123D0 == null) { P123D0 = ""; } P123D15 = conn.rs.getString("P123D15"); if (P123D15 == null) { P123D15 = ""; } P123D25 = conn.rs.getString("P123D25"); if (P123D25 == null) { P123D25 = ""; } P124D0 = conn.rs.getString("P124D0"); if (P124D0 == null) { P124D0 = ""; } P124D15 = conn.rs.getString("P124D15"); if (P124D15 == null) { P124D15 = ""; } P124D25 = conn.rs.getString("P124D25"); if (P124D25 == null) { P124D25 = ""; } } String createdtable = ""; if (1 == 1) { int r = 3; if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue("P12.1.D:"); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue( "GEND_NORM: Number of people completing an intervention pertaining to gender norms, that meets minimum criteria"); cl3x1.setCellStyle(style2); String head1[] = { "0-9", P121DM0, P121DF0, P121D0 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r + 5, 1, 1)); shet.addMergedRegion(new CellRangeAddress(r, r + 5, 0, 0)); r++; } //================================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "10-14", P121DM10, P121DF10, P121D10 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //================================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "15-19", P121DM15, P121DF15, P121D15 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //================================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "20-24", P121DM20, P121DF20, P121D20 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //================================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "25+", P121DM25, P121DF25, P121D25 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //================================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "Total", P121DMT, P121DFT, P121DTT }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //================================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue("P12.2.D:"); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue( "Gender Based Violence and Coercion: Number of people reached by an individual, small group or community?level intervention or service that explicitly addresses "); cl3x1.setCellStyle(style2); String head1[] = { "0-14", P122DM0, P122DF0, P122D0 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r + 3, 1, 1)); shet.addMergedRegion(new CellRangeAddress(r, r + 3, 0, 0)); r++; } //================================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "15-24", P122DM15, P122DF15, P122D15 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //=================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "25+", P122DM25, P122DF25, P122D25 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //=================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "Total", P122DMT, P122DFT, P122DTT }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //=================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue("P12.3.D:"); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue( "Women's Legal Rights and Protection Number of people reached by an individual, smallgroup, or community?level intervention or service that explicitly addresses the legal "); cl3x1.setCellStyle(style2); String head1[] = { "0-14", P123DM0, P123DF0, P123D0 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r + 3, 1, 1)); shet.addMergedRegion(new CellRangeAddress(r, r + 3, 0, 0)); r++; } //================================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "15-24", P123DM15, P123DF15, P123D15 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //=================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "25+", P123DM25, P123DF25, P123D25 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //=================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "TOTAL", P123DMT, P123DFT, P123DTT }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //=================================================================================== //=================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue("P12.4.D:"); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue( "Number of people reached by an individual, small group, or community?level intervention or service that explicitly aims to increase access to income and productive "); cl3x1.setCellStyle(style2); String head1[] = { "0-14", P124DM0, P124DF0, P124D0 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r + 3, 1, 1)); shet.addMergedRegion(new CellRangeAddress(r, r + 3, 0, 0)); r++; } //================================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "15-24", P124DM15, P124DF15, P124D15 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //=================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "25+", P124DM25, P124DF25, P124D25 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //=================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "TOTAL", P124DMT, P124DFT, P124DTT }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //=================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue("GEND GBV:"); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue("Number of people receiving post-GBV Care"); cl3x1.setCellStyle(style2); String head1[] = { "<10", GEND_GBV9M, GEND_GBV9F, GEND_GBV9 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r + 5, 1, 1)); shet.addMergedRegion(new CellRangeAddress(r, r + 5, 0, 0)); r++; } //=================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "10-14", GEND_GBV14M, GEND_GBV14F, GEND_GBV14 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //================================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "15-17", GEND_GBV17M, GEND_GBV17F, GEND_GBV17 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //================================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "18-24", GEND_GBV24M, GEND_GBV24F, GEND_GBV24 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //================================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "25+", GEND_GBV25M, GEND_GBV25F, GEND_GBV25 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //================================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "Total", GEND_GBVM, GEND_GBVF, GEND_GBV }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //================================================================================================ createdtable = header + "<br/><br/><table border='1' style='border-color: #e5e5e5;margin-bottom: 3px;font-size:10;font-family:cambria;'>" + "<tr class='form-actions'><th colspan='6'><b style='text-align:center;'> Prevention Sub Area 12:Gender</b></th></tr>"; createdtable += "<tr><td rowspan='7'><b> P12.1.D: </b></td><td rowspan='7'> GEND_NORM: Number of people completing an intervention pertaining to gender norms, that meets minimum</td><td class='form-actions'>AGE</td><td class='form-actions'>MALE</td><td style='width:80px;' class='form-actions'>FEMALE</td><td class='form-actions'>TOTAL</td></tr>"; createdtable += "<tr><td><b>0-9</b></td><td>" + P121DM0 + "</td><td>" + P121DF0 + "</td><td>" + P121D0 + "</td></tr>"; createdtable += "<tr><td><b>10-14</b></td><td>" + P121DM10 + "</td><td>" + P121DF10 + "</td><td>" + P121D10 + "</td></tr>"; createdtable += "<tr><td><b>15-19</b></td><td>" + P121DM15 + "</td><td>" + P121DF15 + "</td><td>" + P121D15 + "</td></tr>"; createdtable += "<tr><td><b>20-24</b></td><td>" + P121DM20 + "</td><td>" + P121DF20 + "</td><td>" + P121D20 + "</td></tr>"; createdtable += "<tr><td><b>25+ </b></b></td><td>" + P121DM25 + "</td><td>" + P121DF25 + "</td><td>" + P121D25 + "</td></tr>"; createdtable += "<tr><td><b>Total</b></td><td>" + P121DMT + "</td><td>" + P121DFT + "</td><td>" + P121DTT + "</td></tr>"; createdtable += "<tr><td rowspan='4'><b> P12.2.D: </b></td><td rowspan='4'>Gender Based Violence and Coercion: Number of people reached by an individual, small group or community?level intervention or service that explicitly addresses gender?based violence and coercion related to HIV/AIDS<td><b>0-14</b></td><td>" + P122DM0 + "</td><td>" + P122DF0 + "</td><td>" + P122D0 + "</td></tr>"; createdtable += "<tr><td><b>15-24</b></td><td>" + P122DM15 + "</td><td>" + P122DF15 + "</td><td>" + P122D15 + "</td></tr>"; createdtable += "<tr><td><b>25+</b></td><td>" + P122DM25 + "</td><td>" + P122DF25 + "</td><td>" + P122D25 + "</td></tr>"; createdtable += "<tr><td><b>Total</b></td><td>" + P122DMT + "</td><td>" + P122DFT + "</td><td>" + P122DTT + "</td></tr>"; createdtable += "<tr><td rowspan='4'><b> P12.3.D: </b></td><td rowspan='4'>Women's Legal Rights and Protection Number of people reached by an individual, small group, or community?level intervention or service that explicitly addresses the legal rights and protection of women and girls impacted by HIV/AIDS<td><b>0-14</b></td><td>" + P123DM0 + "</td><td>" + P123DF0 + "</td><td>" + P123D0 + "</td></tr>"; createdtable += "<tr><td><b>15-24</b></td><td>" + P123DM15 + "</td><td>" + P123DF15 + "</td><td>" + P123D15 + "</td></tr>"; createdtable += "<tr><td><b>25+</b></td><td>" + P123DM25 + "</td><td>" + P123DF25 + "</td><td>" + P123D25 + "</td></tr>"; createdtable += "<tr><td><b>Total</b></td><td>" + P123DMT + "</td><td>" + P123DFT + "</td><td>" + P123DTT + "</td></tr>"; createdtable += "<tr><td rowspan='4'><b> P12.4.D: </b></td><td rowspan='4'>Number of people reached by an individual, small group, or community?level intervention or service that explicitly aims to increase access to income and productive resources of women and girls impacted by HIV/AIDS M 0-15<td><b>0-14</b></td><td>" + P124DM0 + "</td><td>" + P124DF0 + "</td><td>" + P124D0 + "</td></tr>"; createdtable += "<tr><td><b>15-24</b></td><td>" + P124DM15 + "</td><td>" + P124DF15 + "</td><td> " + P124D15 + "</td></tr>"; createdtable += "<tr><td><b>25+</b></td><td>" + P124DM25 + "</td><td>" + P124DF25 + "</td><td>" + P124D25 + "</td></tr>"; createdtable += "<tr><td><b>Total</b></td><td>" + P124DMT + "</td><td>" + P124DFT + "</td><td>" + P124DTT + "</td></tr>"; createdtable += "<tr><td rowspan='6'><b> GEND_GBV </b></td><td rowspan='6'>Number of people receiving post-GBV Care<td><b> less than 10 </b> </td><td>" + GEND_GBV9M + "</td><td>" + GEND_GBV9F + "</td><td>" + GEND_GBV9 + "</td></tr>"; createdtable += "<tr><td><b>10-14</b></td><td>" + GEND_GBV14M + "</td><td>" + GEND_GBV14F + "</td><td>" + GEND_GBV14 + "</td></tr>"; createdtable += "<tr><td><b>15-17</b></td><td>" + GEND_GBV17M + "</td><td>" + GEND_GBV17F + "</td><td>" + GEND_GBV17 + "</td></tr>"; createdtable += "<tr><td><b>18-24</b></td><td>" + GEND_GBV24M + "</td><td>" + GEND_GBV24F + "</td><td>" + GEND_GBV24 + "</td></tr>"; createdtable += "<tr><td><b>25+</b></td><td>" + GEND_GBV25M + "</td><td>" + GEND_GBV25F + "</td><td>" + GEND_GBV25 + "</td></tr>"; createdtable += "<tr><td><b>Total</b></td><td>" + GEND_GBVM + "</td><td>" + GEND_GBVF + "</td><td>" + GEND_GBV + "</td></tr>"; createdtable += "<tr><td></td><td></td><td class='form-actions'>AGE</td><td class='form-actions'>MALE</td><td style='width:80px;' class='form-actions'>FEMALE</td><td class='form-actions'>TOTAL</td></tr>"; createdtable += "</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.getMonthlyCompletionRate.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();//from w w w . j a va 2 s .c o m dbConn conn = new dbConn(); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet(); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); // font.setItalic(true); // font.setBoldweight((short)12); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont 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); 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.setFillForegroundColor(HSSFColor.ORANGE.index); // stborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 4000); shet1.setColumnWidth(1, 1500); shet1.setColumnWidth(2, 2000); shet1.setColumnWidth(3, 2000); shet1.setColumnWidth(4, 2000); shet1.setColumnWidth(5, 2000); shet1.setColumnWidth(6, 2000); shet1.setColumnWidth(7, 2000); shet1.setColumnWidth(8, 2000); shet1.setColumnWidth(9, 2000); shet1.setColumnWidth(10, 2000); shet1.setColumnWidth(11, 2000); shet1.setColumnWidth(12, 2000); shet1.setColumnWidth(13, 2000); // shet1.setColumnWidth(20, 2000); HSSFCellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorder.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index); styleBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFRow rw1 = shet1.createRow(1); HSSFCell cell; cell = rw1.createCell(0); cell.setCellValue("kePMS Report"); cell.setCellStyle(style); rw1.setHeightInPoints(30); shet1.addMergedRegion(new CellRangeAddress(1, 1, 0, 13)); position = 2; String getPartners = "SELECT * FROM partner"; conn.rs = conn.st.executeQuery(getPartners); while (conn.rs.next()) { partnername = partnerid = ""; completedmale = completedfemale = 0; completedmale2 = completedfemale2 = 0; completedmale3 = completedfemale3 = 0; completedmale1 = completedfemale1 = 0; partnerid = conn.rs.getString(1); partnername = conn.rs.getString(2); HSSFRow rheading2 = shet1.createRow(position); rheading2.setHeightInPoints(25); shet1.addMergedRegion(new CellRangeAddress(position, position, 0, 13)); HSSFCell cellxx1 = rheading2.createCell(0); HSSFCell cellxx2 = rheading2.createCell(1); HSSFCell cellxx3 = rheading2.createCell(2); HSSFCell cellxx4 = rheading2.createCell(3); HSSFCell cellxx5 = rheading2.createCell(4); HSSFCell cellxx6 = rheading2.createCell(5); HSSFCell cellxx7 = rheading2.createCell(6); HSSFCell cellxx8 = rheading2.createCell(7); HSSFCell cellxx9 = rheading2.createCell(8); HSSFCell cellxx10 = rheading2.createCell(9); HSSFCell cellxx11 = rheading2.createCell(10); HSSFCell cellxx12 = rheading2.createCell(11); HSSFCell cellxx13 = rheading2.createCell(12); HSSFCell cellxx14 = rheading2.createCell(13); cellxx1.setCellValue("PARTNER : " + partnername); 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); position += 2; // HEADINGS=============================================== HSSFRow rheading = shet1.createRow(position); rheading2.setHeightInPoints(25); HSSFCell cellx1 = rheading.createCell(0); HSSFCell cellx2 = rheading.createCell(1); HSSFCell cellx3 = rheading.createCell(2); HSSFCell cellx4 = rheading.createCell(3); HSSFCell cellx5 = rheading.createCell(4); HSSFCell cellx6 = rheading.createCell(5); HSSFCell cellx7 = rheading.createCell(6); HSSFCell cellx8 = rheading.createCell(7); HSSFCell cellx9 = rheading.createCell(8); HSSFCell cellx10 = rheading.createCell(9); HSSFCell cellx11 = rheading.createCell(10); HSSFCell cellx12 = rheading.createCell(11); HSSFCell cellx13 = rheading.createCell(12); HSSFCell cellx14 = rheading.createCell(13); cellx1.setCellValue("AGE BRACKET "); cellx2.setCellValue("APRIL"); cellx3.setCellValue(""); cellx4.setCellValue("MAY"); cellx5.setCellValue(""); cellx6.setCellValue("JUNE"); cellx7.setCellValue(""); cellx8.setCellValue("JULY"); cellx9.setCellValue(""); cellx10.setCellValue("AUGUST"); cellx11.setCellValue(""); cellx12.setCellValue("SEPT"); cellx13.setCellValue(""); cellx1.setCellStyle(styleBorder); cellx2.setCellStyle(styleBorder); cellx3.setCellStyle(styleBorder); cellx4.setCellStyle(styleBorder); cellx5.setCellStyle(styleBorder); cellx6.setCellStyle(styleBorder); cellx7.setCellStyle(styleBorder); cellx8.setCellStyle(styleBorder); cellx9.setCellStyle(styleBorder); cellx10.setCellStyle(styleBorder); cellx11.setCellStyle(styleBorder); cellx12.setCellStyle(styleBorder); cellx13.setCellStyle(styleBorder); cellx14.setCellStyle(styleBorder); shet1.addMergedRegion(new CellRangeAddress(position, position, 1, 2)); shet1.addMergedRegion(new CellRangeAddress(position, position, 3, 4)); shet1.addMergedRegion(new CellRangeAddress(position, position, 5, 6)); shet1.addMergedRegion(new CellRangeAddress(position, position, 7, 8)); shet1.addMergedRegion(new CellRangeAddress(position, position, 9, 10)); shet1.addMergedRegion(new CellRangeAddress(position, position, 11, 12)); position++; HSSFRow rheading1 = shet1.createRow(position); rheading2.setHeightInPoints(25); HSSFCell cell1 = rheading1.createCell(0); HSSFCell cell2 = rheading1.createCell(1); HSSFCell cell3 = rheading1.createCell(2); HSSFCell cell4 = rheading1.createCell(3); HSSFCell cell5 = rheading1.createCell(4); HSSFCell cell6 = rheading1.createCell(5); HSSFCell cell7 = rheading1.createCell(6); HSSFCell cell8 = rheading1.createCell(7); HSSFCell cell9 = rheading1.createCell(8); HSSFCell cell10 = rheading1.createCell(9); HSSFCell cell11 = rheading1.createCell(10); HSSFCell cell12 = rheading1.createCell(11); HSSFCell cell13 = rheading1.createCell(12); HSSFCell cell14 = rheading1.createCell(13); cell1.setCellValue(""); cell2.setCellValue("M"); cell3.setCellValue("F"); cell4.setCellValue("M"); cell5.setCellValue("F"); cell6.setCellValue("M"); cell7.setCellValue("F"); cell8.setCellValue("M"); cell9.setCellValue("F"); cell10.setCellValue("M"); cell11.setCellValue("F"); cell12.setCellValue("M"); cell13.setCellValue("F"); cell1.setCellStyle(styleBorder); cell2.setCellStyle(styleBorder); cell3.setCellStyle(styleBorder); cell4.setCellStyle(styleBorder); cell5.setCellStyle(styleBorder); cell6.setCellStyle(styleBorder); cell7.setCellStyle(styleBorder); cell8.setCellStyle(styleBorder); cell9.setCellStyle(styleBorder); cell10.setCellStyle(styleBorder); cell11.setCellStyle(styleBorder); cell12.setCellStyle(styleBorder); cell13.setCellStyle(styleBorder); cell14.setCellStyle(styleBorder); position++; HSSFRow rheadingS1 = shet1.createRow(position); rheadingS1.setHeightInPoints(20); HSSFCell cellS1 = rheadingS1.createCell(0); cellS1.setCellValue("0-14"); position++; HSSFRow rheadingS2 = shet1.createRow(position); rheadingS2.setHeightInPoints(20); HSSFCell cellS2 = rheadingS2.createCell(0); cellS2.setCellValue("15-19"); position++; HSSFRow rheadingS3 = shet1.createRow(position); rheadingS3.setHeightInPoints(20); HSSFCell cellS3 = rheadingS3.createCell(0); cellS3.setCellValue("20-24"); position++; HSSFRow rheadingS4 = shet1.createRow(position); rheadingS4.setHeightInPoints(20); HSSFCell cellS4 = rheadingS4.createCell(0); cellS4.setCellValue(">=25"); position++; int cnt2 = 1; for (int i = 4; i <= 9; i++) { month = "0" + i; int j = i - 1; month2 = "0" + j; String getClient = "SELECT client_id,age,gender FROM clients WHERE partner_id='" + partnerid + "' && lessons_attended>2 && year='2014'"; conn.rs2 = conn.st2.executeQuery(getClient); while (conn.rs2.next()) { age = conn.rs2.getInt(2); gender = conn.rs2.getString(3); System.out.println("client id : " + conn.rs2.getString(1)); String counter = "SELECT SUM(register2.value) FROM register2 WHERE month<='" + month + "'&& register2.session_no='9' && register2.value='1' && client_id='" + conn.rs2.getString(1) + "'"; conn.rs1 = conn.st1.executeQuery(counter); if (conn.rs1.next() == true) { attended = conn.rs1.getInt(1); } String counter2 = "SELECT SUM(register2.value) FROM register2 WHERE month<='" + month2 + "' && register2.session_no='9' && register2.value='1' && client_id='" + conn.rs2.getString(1) + "'"; conn.rs1 = conn.st1.executeQuery(counter2); if (conn.rs1.next() == true) { attendedx = conn.rs1.getInt(1); } System.out.println("attended : " + attended); if (attended > 2) { if (gender.equalsIgnoreCase("female")) { if (age > 0 && age < 15) { completedfemale++; } else if (age > 14 && age < 20) { completedfemale1++; } else if (age > 19 && age < 25) { completedfemale2++; } else if (age > 24) { completedfemale3++; } } else { if (age > 0 && age < 15) { completedmale++; } else if (age > 14 && age < 20) { completedmale1++; } else if (age > 19 && age < 25) { completedmale2++; } else if (age > 24) { completedmale3++; } } } // ATTENDED PREVIOUSLY============================= if (attendedx == 13) { if (gender.equalsIgnoreCase("female")) { if (age > 0 && age < 15) { completedfemalex++; } else if (age > 14 && age < 20) { completedfemale1x++; } else if (age > 19 && age < 25) { completedfemale2x++; } else if (age > 24) { completedfemale3x++; } } else { if (age > 0 && age < 15) { completedmalex++; } else if (age > 14 && age < 20) { completedmale1x++; } else if (age > 19 && age < 25) { completedmale2x++; } else if (age > 24) { completedmale3x++; } } } } if (completedmale3 > 0 || completedmale2 > 0 || completedmale1 > 0 || completedmale > 0 || completedfemale3 > 0 || completedfemale2 > 0 || completedfemale1 > 0 || completedfemale > 0) { System.out.println(" here completed : " + partnername); } // if(partnerid.equals("")) // ADD DATA FOR EACH MONTH================================================= HSSFCell cellS11 = rheadingS1.createCell(cnt2); HSSFCell cellS12 = rheadingS1.createCell(cnt2 + 1); cellS11.setCellValue(completedmale - completedmalex); cellS12.setCellValue(completedfemale - completedfemale); HSSFCell cellS21 = rheadingS2.createCell(cnt2); HSSFCell cellS22 = rheadingS2.createCell(cnt2 + 1); cellS21.setCellValue(completedmale1 - completedmale1x); cellS22.setCellValue(completedfemale1 - completedfemale1x); HSSFCell cellS31 = rheadingS3.createCell(cnt2); HSSFCell cellS32 = rheadingS3.createCell(cnt2 + 1); cellS31.setCellValue(completedmale2 - completedmale2x); cellS32.setCellValue(completedfemale2 - completedfemale2x); HSSFCell cellS41 = rheadingS4.createCell(cnt2); HSSFCell cellS42 = rheadingS4.createCell(cnt2 + 1); cellS41.setCellValue(completedmale3 - completedmale3x); cellS42.setCellValue(completedfemale3 - completedfemale3x); completedfemale3 = completedmale3 = completedfemale2 = completedmale2 = 0; completedfemale1 = completedmale1 = completedfemale = completedmale = 0; completedfemale3x = completedmale3x = completedfemale2x = completedmale2x = 0; completedfemale1x = completedmale1x = completedfemalex = completedmalex = 0; cnt2 += 2; } // GET ANOTHER PARTNER======================================= System.out.println("partner name : " + partnername); } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_kePMS_Report.xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:reports.htctracker731.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { dbConn conn = new dbConn(); session = request.getSession();/*from w ww.jav a 2 s . com*/ year = Integer.parseInt(request.getParameter("year")); // year=2015; prevYear = year - 1; IdGenerator IG = new IdGenerator(); allMonths.clear(); allReports.clear(); duration = "WHERE (moh731.yearmonth BETWEEN " + prevYear + "" + 10 + " AND " + year + "09) AND ( subpartnera.HTC=1) AND ( HV0103 > 0)"; currentMonth = IG.CurrentMonth(); monthsData = ""; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet("MOH 731 HTC REPORTS TRACKER"); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); stborder.setWrapText(true); 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.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); HSSFCellStyle styleHeader = wb.createCellStyle(); styleHeader.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); styleHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontHeader = wb.createFont(); fontHeader.setColor(HSSFColor.DARK_BLUE.index); styleHeader.setFont(fontHeader); styleHeader.setWrapText(true); for (int i = 0; i <= 2; i++) { shet1.setColumnWidth(i, 8000); } HSSFRow rw1S1 = shet1.createRow(0); HSSFCell S1cell = rw1S1.createCell(0); S1cell.setCellValue("COUNTY NAME"); S1cell.setCellStyle(stylex); HSSFCell S1cellX = rw1S1.createCell(1); S1cellX.setCellValue("SUB COUNTY"); S1cellX.setCellStyle(stylex); S1cellX = rw1S1.createCell(2); S1cellX.setCellValue("HEALTH FACILITY"); S1cellX.setCellStyle(stylex); S1cellX = rw1S1.createCell(3); S1cellX.setCellValue("MFL CODE"); S1cellX.setCellStyle(stylex); S1cellX = rw1S1.createCell(4); S1cellX.setCellValue("EXPECTED REPORTS"); S1cellX.setCellStyle(stylex); counterHeader = 5; String getMaxandMin = "SELECT DISTINCT(month.name),month.id FROM moh731 JOIN month ON moh731.Mois=month.id JOIN subpartnera ON moh731.SubPartnerID=subpartnera.SubPartnerID " + duration + " " + " ORDER BY moh731.yearmonth"; conn.rs = conn.st.executeQuery(getMaxandMin); while (conn.rs.next()) { monthName = conn.rs.getString(1); monthid = conn.rs.getInt(2); if (monthid <= 9) { currentYear = year; } else { currentYear = prevYear; } System.out.println(" Months are : " + monthName); allMonths.add(monthName); allReports.add(0); S1cellX = rw1S1.createCell(counterHeader); S1cellX.setCellValue(monthName); S1cellX.setCellStyle(stylex); counterHeader++; } prevFacility = currentFacility = ""; currentDistrict = prevDistrict = ""; currentCounty = prevCounty = ""; counter = districtCounter = countyCounter = districtsMerged = 0; arraySize = allReports.size(); if (allMonths.size() > 0) { String checkReports = "SELECT county.County,district.DistrictNom,subpartnera.SubPartnerNom," + "subpartnera.CentreSanteId,COUNT(moh731.SubPartnerID),month.name,subpartnera.SubPartnerID " + "FROM subpartnera " + "LEFT JOIN moh731 ON moh731.SubPartnerID=subpartnera.SubPartnerID " + "LEFT JOIN district ON subpartnera.DistrictID=district.DistrictID " + "LEFT JOIN county ON county.CountyID=district.CountyID " + "LEFT JOIN month ON moh731.Mois=month.id " + " " + duration + " " + " GROUP BY subpartnera.SubPartnerNom,moh731.Annee,moh731.Mois " + "ORDER BY county.County,district.DistrictNom,subpartnera.SubPartnerNom,moh731.Mois"; System.out.println(checkReports); conn.rs = conn.st.executeQuery(checkReports); while (conn.rs.next()) { countyName = conn.rs.getString(1); districtName = conn.rs.getString(2); facilityName = conn.rs.getString(3); mflcode = conn.rs.getString(4); status = conn.rs.getInt(5); selectedMonth = conn.rs.getString(6); currentFacility = conn.rs.getString(7); currentDistrict = districtName; currentCounty = countyName; // CHECK WHERE TO PLACE THE NUMBER; monthPosition = allMonths.indexOf(selectedMonth); if (!prevFacility.equals(currentFacility)) { if (!prevDistrict.equals(currentDistrict) && !prevDistrict.equals("")) { counter++; noReports = districtCounter + 1; HSSFRow rwTotal = shet1.createRow(counter); HSSFCell SX = rwTotal.createCell(0); SX.setCellStyle(stborder); SX = rwTotal.createCell(1); SX.setCellValue(prevDistrict + " TOTALS : "); SX.setCellStyle(styleHeader); SX = rwTotal.createCell(2); SX.setCellStyle(styleHeader); SX = rwTotal.createCell(3); SX.setCellStyle(styleHeader); SX = rwTotal.createCell(4); SX.setCellValue(noReports); SX.setCellStyle(styleHeader); shet1.addMergedRegion(new CellRangeAddress(counter, counter, 1, 3)); for (int j = 0; j < allReports.size(); j++) { // System.out.println("district name : "+prevDistrict+" no of reports : "+allReports.get(j).toString()+" for month : "+allMonths.get(j).toString()); int dataPos = 5 + j; SX = rwTotal.createCell(dataPos); SX.setCellValue(Integer.parseInt(allReports.get(j).toString())); SX.setCellStyle(styleHeader); } for (int k = 0; k < arraySize; k++) { allReports.set(k, 0); } } counter++; HSSFRow rw1 = shet1.createRow(counter); HSSFCell S1 = rw1.createCell(0); S1.setCellValue(countyName); S1.setCellStyle(stborder); S1 = rw1.createCell(1); S1.setCellValue(districtName); S1.setCellStyle(stborder); S1 = rw1.createCell(2); S1.setCellValue(facilityName); S1.setCellStyle(stborder); S1 = rw1.createCell(3); S1.setCellValue(mflcode); S1.setCellStyle(stborder); S1 = rw1.createCell(4); S1.setCellValue(1); S1.setCellStyle(stborder); for (int j = 0; j < allMonths.size(); j++) { int cellPos = j + 5; S1 = rw1.createCell(cellPos); // System.out.println("counter : "+counter+" datapos : "+cellPos+" status : "+status); S1.setCellStyle(stborder); } int dataPos = 5 + monthPosition; S1 = rw1.getCell(dataPos); S1.setCellValue(status); if (!prevDistrict.equals(currentDistrict) && !prevDistrict.equals("")) { int distStart = counter - districtCounter - 2; int distEnd = counter - 2; shet1.addMergedRegion(new CellRangeAddress(distStart, distEnd, 1, 1)); districtsMerged++; districtCounter = 0; for (int j = 0; j < arraySize; j++) { allReports.set(j, 0); } } else { if (counter == 1) { } else { districtCounter++; } } if (!prevCounty.equals(currentCounty) && !prevCounty.equals("")) { int countyStart = counter - countyCounter - districtsMerged - 1; int countyEnd = counter - 1; shet1.addMergedRegion(new CellRangeAddress(countyStart, countyEnd, 0, 0)); countyCounter = 0; districtsMerged = 0; } else { if (counter == 1) { } else { countyCounter++; } } prevCounty = currentCounty; prevDistrict = currentDistrict; } else { HSSFRow rw1 = shet1.getRow(counter); int dataPos = 5 + monthPosition; HSSFCell S1 = rw1.getCell(dataPos); S1.setCellValue(status); // } if (status == 1) { int currentData = Integer.parseInt(allReports.get(monthPosition).toString()) + 1; allReports.set(monthPosition, currentData); } prevFacility = currentFacility; } // MATCH THE LAST DISTRICTS counter++; noReports = districtCounter + 1; HSSFRow rwTotal = shet1.createRow(counter); HSSFCell SX = rwTotal.createCell(0); SX.setCellStyle(stborder); SX = rwTotal.createCell(1); SX.setCellValue(prevDistrict + " TOTALS : "); SX.setCellStyle(styleHeader); SX = rwTotal.createCell(2); SX.setCellStyle(styleHeader); SX = rwTotal.createCell(3); SX.setCellStyle(styleHeader); SX = rwTotal.createCell(4); SX.setCellValue(noReports); SX.setCellStyle(styleHeader); shet1.addMergedRegion(new CellRangeAddress(counter, counter, 1, 3)); int distStart = counter - districtCounter - 1; int distEnd = counter - 1; // System.out.println("MERGE BETWEEN : START : "+distStart+" END : "+distEnd); shet1.addMergedRegion(new CellRangeAddress(distStart, distEnd, 1, 1)); districtCounter = 0; int countyStart = counter - countyCounter - 2; int countyEnd = counter; // System.out.println("MERGE BETWEEN : START : "+distStart+" END : "+distEnd); shet1.addMergedRegion(new CellRangeAddress(countyStart, countyEnd, 0, 0)); countyCounter = 0; for (int j = 0; j < allReports.size(); j++) { // System.out.println("district name : "+prevDistrict+" no of reports : "+allReports.get(j).toString()+" for month : "+allMonths.get(j).toString()); int dataPos = 5 + j; SX = rwTotal.createCell(dataPos); SX.setCellValue(Integer.parseInt(allReports.get(j).toString())); SX.setCellStyle(styleHeader); } 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(); } 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=HTC_Tracker_summary_for_PEPFAR_YEAR(" + year + ")_" + createdOn.trim() + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } else { session.setAttribute("noTrackerReport", "<font color=\"red\"><b>SORRY:</b> No report was found for " + year + ".</red>"); response.sendRedirect("reportsTracker.jsp"); } }
From source file:reports.IndvRawData.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();/*from w ww . j av a 2s .co m*/ dbConn conn = new dbConn(); group_ids = session.getAttribute("customInd").toString(); startDate = session.getAttribute("custstartDate").toString(); endDate = session.getAttribute("custendDate").toString(); System.out.println(" group _ids are : " + group_ids); i = 4; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet(); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); shet1.setColumnWidth(0, 5000); shet1.setColumnWidth(1, 5000); shet1.setColumnWidth(2, 5000); shet1.setColumnWidth(3, 5500); shet1.setColumnWidth(4, 7000); shet1.setColumnWidth(5, 5300); shet1.setColumnWidth(6, 3000); shet1.setColumnWidth(7, 3200); shet1.setColumnWidth(8, 3200); shet1.setColumnWidth(9, 3200); shet1.setColumnWidth(10, 3800); shet1.setColumnWidth(11, 3000); shet1.setColumnWidth(12, 5300); shet1.setColumnWidth(13, 5000); shet1.setColumnWidth(14, 5300); shet1.setColumnWidth(15, 5000); shet1.setColumnWidth(16, 5200); shet1.setColumnWidth(17, 5200); shet1.setColumnWidth(18, 5200); shet1.setColumnWidth(19, 5800); shet1.setColumnWidth(20, 5000); shet1.setColumnWidth(21, 5300); shet1.setColumnWidth(22, 5300); shet1.setColumnWidth(23, 5000); shet1.setColumnWidth(24, 5200); shet1.setColumnWidth(25, 5200); // shet1.setColumnWidth(20, 2000); HSSFCellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFRow rw1 = shet1.createRow(1); HSSFCell cell; HSSFRow rw4 = shet1.createRow(0); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell0, cell1, cell2, cell3, cell4, cell5, cell6, cell7, cell8, cell9, cell10, cell11, cell12, cell13, cell14, cell15, cell16, cell17, cell18, cell19, cell20; HSSFCell cell21, cell22, cell23, cell24, cell25, cell26, cell27, cell28, cell29, cell30, cell31, cell32, cell33; cell0 = rw4.createCell(0); cell1 = rw4.createCell(1); cell2 = rw4.createCell(2); cell3 = rw4.createCell(3); cell4 = rw4.createCell(4); cell5 = rw4.createCell(5); cell6 = rw4.createCell(6); cell7 = rw4.createCell(7); cell8 = rw4.createCell(8); cell9 = rw4.createCell(9); cell10 = rw4.createCell(10); cell11 = rw4.createCell(11); cell12 = rw4.createCell(12); cell13 = rw4.createCell(13); cell14 = rw4.createCell(14); cell15 = rw4.createCell(15); cell16 = rw4.createCell(16); cell17 = rw4.createCell(17); cell18 = rw4.createCell(18); cell19 = rw4.createCell(19); cell20 = rw4.createCell(20); cell21 = rw4.createCell(21); cell22 = rw4.createCell(22); cell23 = rw4.createCell(23); cell24 = rw4.createCell(24); cell25 = rw4.createCell(25); cell0.setCellValue("COUNTY NAME"); cell1.setCellValue("PARTNER NAME"); cell2.setCellValue("NEAREST FACILITY"); cell3.setCellValue("GROUP NAME"); cell4.setCellValue("SERVICE PROVIDER"); cell5.setCellValue("FULL NAME"); cell6.setCellValue("AGE"); cell7.setCellValue("GENDER"); cell8.setCellValue("DATE OF BIRTH"); cell9.setCellValue("NATIONAL ID"); cell10.setCellValue("MOBILE NO"); cell11.setCellValue("CCC NO"); cell12.setCellValue("No. of Messages Attended"); cell13.setCellValue("Knowledge of HIV Status"); cell14.setCellValue("Partner HIV Testing"); cell15.setCellValue("Child HIV Testing"); cell16.setCellValue("Discordance"); cell17.setCellValue("HIV Disclosure"); cell18.setCellValue("Risk Factor/Reduction"); cell19.setCellValue("Condom Use"); cell20.setCellValue("Alcohol and Substance Abuse"); cell21.setCellValue("Adherence"); cell22.setCellValue("STIs"); cell23.setCellValue("Family Planning"); cell24.setCellValue("PMTCT"); cell25.setCellValue("TB"); HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); cell0.setCellStyle(stylex); cell1.setCellStyle(stylex); cell2.setCellStyle(stylex); cell3.setCellStyle(stylex); cell4.setCellStyle(stylex); cell5.setCellStyle(stylex); cell6.setCellStyle(stylex); cell7.setCellStyle(stylex); cell8.setCellStyle(stylex); cell9.setCellStyle(stylex); cell10.setCellStyle(stylex); cell11.setCellStyle(stylex); cell12.setCellStyle(stylex); cell13.setCellStyle(stylex); cell14.setCellStyle(stylex); cell15.setCellStyle(stylex); cell16.setCellStyle(stylex); cell17.setCellStyle(stylex); cell18.setCellStyle(stylex); cell19.setCellStyle(stylex); cell20.setCellStyle(stylex); cell21.setCellStyle(stylex); cell22.setCellStyle(stylex); cell23.setCellStyle(stylex); cell24.setCellStyle(stylex); cell25.setCellStyle(stylex); i = 1; String[] groupIDS = group_ids.split(","); for (String group_id : groupIDS) { if (!group_id.equals("") && !group_id.equals(",")) { groupid = group_id; System.out.println("group id is : " + groupid); String getClients = "SELECT personal_information.client_id,personal_information.fname,personal_information.mname,personal_information.lname," + "DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )" + ",personal_information.gender,groups.group_name,district.district_name,partner.partner_name," + "service_provider.fname,service_provider.mname,service_provider.lname,personal_information.lessons_attended," + "personal_information.national_id,personal_information.ccc_no,personal_information.mobile_no,health_facility.hf_name,county.county_name " + " FROM personal_information" + " LEFT JOIN health_facility ON personal_information.hf_id=health_facility.hf_id " + "LEFT JOIN district ON personal_information.district_id=district.district_id " + "LEFT JOIN county ON district.county_id=county.county_id " + "LEFT JOIN service_provider ON personal_information.provider_id=service_provider.provider_id " + "LEFT JOIN partner ON personal_information.partner_id=partner.partner_id " + "LEFT JOIN groups ON personal_information.group_id =groups.group_id" + " WHERE personal_information.group_id='" + groupid + "' ORDER BY partner.partner_name,district.district_name,personal_information.fname,personal_information.mname,personal_information.lname"; conn.rs = conn.st.executeQuery(getClients); while (conn.rs.next()) { county = district = hf = partner = groupname = serviceprovider = clientname = age = gender = groupings = year = providerid = ""; countyid = districtid = hfid = partnerid = groupid = serviceproviderid = clientid = ""; s1 = s2 = s3 = s4 = s5 = s6 = s7 = s8 = s9 = s10 = s11 = s12 = s13 = 0; cm = rsp = cd = tb = sti = testedpartner = testedchild = session_no = value = status; sess = val = cds = 0; hf_id = lessons_attended = national_id = ccc_no = mobile_no = dob = ""; added = 0; clientid = conn.rs.getString(1); if (!conn.rs.getString(3).equalsIgnoreCase(conn.rs.getString(4))) { clientname = conn.rs.getString(2) + " " + conn.rs.getString(3) + " " + conn.rs.getString(4); } else { clientname = conn.rs.getString(2) + " " + conn.rs.getString(4); } age = conn.rs.getString(5); gender = conn.rs.getString(6); groupname = conn.rs.getString(7); if (groupname == null) { groupname = "INDIVIDUAL"; } // districtname=conn.rs.getString(8); partner = conn.rs.getString(9); serviceprovider = conn.rs.getString(10) + " " + conn.rs.getString(11) + " " + conn.rs.getString(12); if (conn.rs.getString(11) != null && conn.rs.getString(12) != null) { if (conn.rs.getString(11).equals(conn.rs.getString(12))) { serviceprovider = conn.rs.getString(10) + " " + conn.rs.getString(12); } } lessons_attended = conn.rs.getString(13); national_id = conn.rs.getString(14); ccc_no = conn.rs.getString(15); mobile_no = conn.rs.getString(16); hf = conn.rs.getString(17); county = conn.rs.getString(18); String getAttended = "SELECT session_no,value FROM register2 WHERE client_id='" + clientid + "' && " + " STR_TO_DATE(register2.date,'%m/%d/%Y') BETWEEN STR_TO_DATE('" + startDate + "','%m/%d/%Y') AND STR_TO_DATE('" + endDate + "','%m/%d/%Y') "; System.out.println(getAttended); conn.rs1 = conn.st1.executeQuery(getAttended); while (conn.rs1.next()) { sess = conn.rs1.getInt(1); val = conn.rs1.getInt(2); if (sess == 1) { if (val == 1) { s1 = 1; added++; } else if (val == 2) { s1 = 0; } else { s1 = 2; } } if (sess == 2) { if (val == 1) { s2 = 1; added++; } else if (val == 2) { s2 = 0; } else { s2 = 2; } } if (sess == 3) { if (val == 1) { s3 = 1; added++; } else if (val == 2) { s3 = 0; } else { s3 = 2; } } if (sess == 4) { if (val == 1) { s4 = 1; added++; } else if (val == 2) { s4 = 0; } else { s4 = 2; } } if (sess == 5) { if (val == 1) { s5 = 1; added++; } else if (val == 2) { s5 = 0; } else { s5 = 2; } } if (sess == 6) { if (val == 1) { s6 = 1; added++; } else if (val == 2) { s6 = 0; } else { s6 = 2; } } if (sess == 7) { if (val == 1) { s7 = 1; added++; } else if (val == 2) { s7 = 0; } else { s7 = 2; } } if (sess == 8) { if (val == 1) { s8 = 1; added++; } else if (val == 2) { s8 = 0; } else { s8 = 2; } } if (sess == 9) { if (val == 1) { s9 = 1; added++; } else if (val == 2) { s9 = 0; } else { s9 = 2; } } if (sess == 10) { if (val == 1) { s10 = 1; added++; } else if (val == 2) { s10 = 0; } else { s10 = 2; } } if (sess == 11) { if (val == 1) { s11 = 1; added++; } else if (val == 2) { s11 = 0; } else { s11 = 2; } } if (sess == 12) { if (val == 1) { s12 = 1; added++; } else if (val == 2) { s12 = 0; } else { s12 = 2; } } if (sess == 13) { if (val == 1) { s13 = 1; added++; } else if (val == 2) { s13 = 0; } else { s13 = 2; } } } if (added > 10) { System.out.println("added id : " + clientid); } // OUTPUT ATTENDED-------------------------------- if (added > 0) { // OUTPUT SERVICES PROVIDED================================ HSSFRow rw4x = shet1.createRow(i); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell0x, cell1x, cell2x, cell3x, cell4x, cell5x, cell6x, cell7x, cell8x, cell9x, cell10x, cell11x, cell12x, cell13x, cell14x, cell15x, cell16x, cell17x, cell18x, cell19x, cell20x; HSSFCell cell21x, cell22x, cell23x, cell24x, cell25x, cell26x, cell27x, cell28x, cell29x, cell30x, cell31x, cell32x, cell33x; cell0x = rw4x.createCell(0); cell1x = rw4x.createCell(1); cell2x = rw4x.createCell(2); cell3x = rw4x.createCell(3); cell4x = rw4x.createCell(4); cell5x = rw4x.createCell(5); cell6x = rw4x.createCell(6); cell7x = rw4x.createCell(7); cell8x = rw4x.createCell(8); cell9x = rw4x.createCell(9); cell10x = rw4x.createCell(10); cell11x = rw4x.createCell(11); cell12x = rw4x.createCell(12); cell13x = rw4x.createCell(13); cell14x = rw4x.createCell(14); cell15x = rw4x.createCell(15); cell16x = rw4x.createCell(16); cell17x = rw4x.createCell(17); cell18x = rw4x.createCell(18); cell19x = rw4x.createCell(19); cell20x = rw4x.createCell(20); cell21x = rw4x.createCell(21); cell22x = rw4x.createCell(22); cell23x = rw4x.createCell(23); cell24x = rw4x.createCell(24); cell25x = rw4x.createCell(25); cell26x = rw4x.createCell(26); cell27x = rw4x.createCell(27); cell28x = rw4x.createCell(28); cell29x = rw4x.createCell(29); cell30x = rw4x.createCell(30); cell31x = rw4x.createCell(31); cell32x = rw4x.createCell(32); cell33x = rw4x.createCell(33); cell0x.setCellValue(county); cell1x.setCellValue(partner); cell2x.setCellValue(hf); cell3x.setCellValue(groupname); cell4x.setCellValue(serviceprovider); cell5x.setCellValue(clientname); cell6x.setCellValue(age); cell7x.setCellValue(gender); cell8x.setCellValue(dob); cell9x.setCellValue(national_id); cell10x.setCellValue(mobile_no); cell11x.setCellValue(ccc_no); cell12x.setCellValue(added); if (s1 > 1) { cell13x.setCellValue(""); } else { cell13x.setCellValue(s1); } if (s2 > 1) { cell14x.setCellValue(""); } else { cell14x.setCellValue(s2); } if (s3 > 1) { cell15x.setCellValue(""); } else { cell15x.setCellValue(s3); } if (s4 > 1) { cell16x.setCellValue(""); } else { cell16x.setCellValue(s4); } if (s5 > 1) { cell17x.setCellValue(""); } else { cell17x.setCellValue(s5); } if (s6 > 1) { cell18x.setCellValue(""); } else { cell18x.setCellValue(s6); } if (s7 > 1) { cell19x.setCellValue(""); } else { cell19x.setCellValue(s7); } if (s8 > 1) { cell20x.setCellValue(""); } else { cell20x.setCellValue(s8); } if (s9 > 1) { cell21x.setCellValue(""); } else { cell21x.setCellValue(s9); } if (s10 > 1) { cell22x.setCellValue(""); } else { cell22x.setCellValue(s10); } if (s11 > 1) { cell23x.setCellValue(""); } else { cell23x.setCellValue(s11); } if (s12 > 1) { cell24x.setCellValue(""); } else { cell24x.setCellValue(s12); } if (s13 > 1) { cell25x.setCellValue(""); } else { cell25x.setCellValue(s13); } cell0x.setCellStyle(styleBorder); cell1x.setCellStyle(styleBorder); cell2x.setCellStyle(styleBorder); cell3x.setCellStyle(styleBorder); cell4x.setCellStyle(styleBorder); cell5x.setCellStyle(styleBorder); cell6x.setCellStyle(styleBorder); cell7x.setCellStyle(styleBorder); cell8x.setCellStyle(styleBorder); cell9x.setCellStyle(styleBorder); cell10x.setCellStyle(styleBorder); cell11x.setCellStyle(styleBorder); cell12x.setCellStyle(styleBorder); cell13x.setCellStyle(styleBorder); cell14x.setCellStyle(styleBorder); cell15x.setCellStyle(styleBorder); cell16x.setCellStyle(styleBorder); cell17x.setCellStyle(styleBorder); cell18x.setCellStyle(styleBorder); cell19x.setCellStyle(styleBorder); cell20x.setCellStyle(styleBorder); cell21x.setCellStyle(styleBorder); cell22x.setCellStyle(styleBorder); cell23x.setCellStyle(styleBorder); cell24x.setCellStyle(styleBorder); cell25x.setCellStyle(styleBorder); i++; } System.out.println("here : " + i); } } } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_Raw_Data.xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:reports.kePMS.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException, InvalidFormatException { session = request.getSession();//from www .j a v a2s . c om dbConn conn = new dbConn(); pos = 0; pepfaryear = 2015; // pepfaryear=Integer.parseInt(request.getParameter("year")); prevyear = pepfaryear - 1; String enddate = pepfaryear + "09"; String startdate = prevyear + "10"; start = Integer.parseInt(startdate); end = Integer.parseInt(enddate); System.out.println("start date : " + start + " end date : " + end); // COPY FILE TO BE WRITTEN TO Path original = Paths.get(getServletContext().getRealPath("/TEMPLATE.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/TEMPLATE_1.xlsm")); //new file System.out.println("origin : " + original + " destination : " + destination); try { Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING); System.out.println("file copied----------------"); } catch (IOException x) { //catch all for IO problems System.out.println("fine not copied"); } String allpath = getServletContext().getRealPath("/TEMPLATE_1.xlsm"); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb; OPCPackage pkg = OPCPackage.open(allpath); wb = new XSSFWorkbook(pkg); // HSSFWorkbook wb=new HSSFWorkbook(); XSSFSheet shet1 = wb.getSheet("sheet0"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 4000); shet1.setColumnWidth(1, 4000); shet1.setColumnWidth(2, 4000); shet1.setColumnWidth(3, 4000); shet1.setColumnWidth(4, 4000); XSSFRow rw4 = shet1.createRow(0); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); XSSFCell cell0, cell1, cell2, cell3, cell4; cell0 = rw4.createCell(0); cell1 = rw4.createCell(1); cell2 = rw4.createCell(2); cell3 = rw4.createCell(3); cell4 = rw4.createCell(4); cell0.setCellValue("PARTNER NAME"); cell1.setCellValue("AGE BRACKET"); cell2.setCellValue("GENDER"); cell3.setCellValue("MONTH"); cell4.setCellValue("ACHIEVED"); String getClients = "SELECT partner.partner_name," + "CASE " + "when clients.completionmonth =01 THEN '" + pepfaryear + "-01(JAN)' " + "when clients.completionmonth =02 THEN '" + pepfaryear + "-02 (FEB)' " + "when clients.completionmonth =03 THEN '" + pepfaryear + "-03 (MAR)' " + "when clients.completionmonth=04 THEN '" + pepfaryear + "-04 (APR)' " + "when clients.completionmonth=05 THEN '" + pepfaryear + "-05 (MAY)' " + "when clients.completionmonth=06 THEN '" + pepfaryear + "-06 (JUN)' " + "when clients.completionmonth=07 THEN '" + pepfaryear + "-07 (JUL)' " + "when clients.completionmonth=08 THEN '" + pepfaryear + "-08 (AUG)' " + "when clients.completionmonth=09 THEN '" + pepfaryear + "-09 (SEPT)' " + "when clients.completionmonth=10 THEN '" + prevyear + "-10 (OCT)' " + "when clients.completionmonth=11 THEN '" + prevyear + "-11 (NOV)'" + "when clients.completionmonth=12 THEn '" + prevyear + "-12 (DEC)'" + "END AS MONTHS,clients.completionyear," + "CASE" + " WHEN clients.age BETWEEN 0 AND 14 THEN '0-14'" + " WHEN clients.age BETWEEN 15 AND 19 THEN '15-19'" + " WHEN clients.age BETWEEN 20 AND 24 THEN '20-24'" + " WHEN clients.age >24 THEN '>25'" + " ELSE 'NOT SELECTED'" + " END AS AGEBRACKET," + "CASE " + "when clients.gender LIKE 'Female' THEN 'F' " + "when clients.gender LIKE 'Male' THEN 'M' " + "ELSE 'NO SEX' " + "END AS SEX" + ",completionmonth,COUNT(clients.client_id) FROM clients " + "JOIN partner ON clients.partner_id=partner.partner_id " + " WHERE clients.completionmonth>0 && clients.completionyear>0 GROUP BY partner.partner_name,SEX,MONTHS,AGEBRACKET ORDER BY clients.partner_id"; conn.rs = conn.st.executeQuery(getClients); while (conn.rs.next()) { partnername = clientid = gender = ""; age = 0; partnername = conn.rs.getString(1); month = conn.rs.getString(2); year = conn.rs.getInt(3); agebracket = conn.rs.getString(4); gender = conn.rs.getString(5); String dkey = year + "" + conn.rs.getString(6); datekey = Integer.parseInt(dkey); achieved = conn.rs.getInt(7); if (datekey >= start && datekey <= end && year >= 2014) { pos++; XSSFRow rw4x = shet1.createRow(pos); rw4x.setHeightInPoints(25); rw4x.setRowStyle(style2); XSSFCell cell0x, cell1x, cell2x, cell3x, cell4x; cell0x = rw4x.createCell(0); cell1x = rw4x.createCell(1); cell2x = rw4x.createCell(2); cell3x = rw4x.createCell(3); cell4x = rw4x.createCell(4); // OUTPUT SERVICES PROVIDED================================ cell0x.setCellValue(partnername); cell1x.setCellValue(agebracket); cell2x.setCellValue(gender); cell3x.setCellValue(month); cell4x.setCellValue(achieved); System.out.println(pos + "-----partner :" + partnername + " age bracket :" + agebracket + " gender :" + gender + " completion month : " + month); } } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_kePMS_REPORT_FOR_PEPFAR_YEAR_" + pepfaryear + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); }