List of usage examples for org.apache.poi.xssf.usermodel XSSFFont setFontName
public void setFontName(String name)
From source file:reports.kePMSGroupServices.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException, InvalidFormatException { session = request.getSession();/*from w w w.j av a 2s . c o m*/ dbConn conn = new dbConn(); incrementor = 0; pos = 0; partner_id = session.getAttribute("partnerDIC").toString(); pepfaryear = Integer.parseInt(session.getAttribute("PepfarYear").toString()); prevyear = pepfaryear - 1; String[] periods = session.getAttribute("period").toString().split("-"); if (session.getAttribute("period").toString().equals("10-12")) { period = "OCT-DEC"; } else if (session.getAttribute("period").toString().equals("01-03")) { period = "JAN-MARCH"; } else if (session.getAttribute("period").toString().equals("04-06")) { period = "APRIL-JUNE"; } else if (session.getAttribute("period").toString().equals("07-09")) { period = "JULY-SEPT"; } else { } startdate = periods[0]; enddate = periods[1]; if (session.getAttribute("period").toString().equals("10-12")) { pepfaryear = pepfaryear - 1; } start = Integer.parseInt(startdate); end = Integer.parseInt(enddate); System.out.println("partner id : " + partner_id + " year : " + pepfaryear); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ Path original = Paths.get(getServletContext().getRealPath("/ServicesGroup.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/ServicesGroup_1.xlsm")); //new file System.out.println("origin : " + original + " destination : " + destination); try { Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING); System.out.println("file copied----------------"); } catch (IOException x) { //catch all for IO problems System.out.println("fine not copied"); } String allpath = getServletContext().getRealPath("/ServicesGroup_1.xlsm"); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb; OPCPackage pkg = OPCPackage.open(allpath); wb = new XSSFWorkbook(pkg); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFSheet shet1 = wb.getSheet("sheet1"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); // font2.setFontHeightInPoints((short)15); font2.setFontName("Arial Black"); // font.setItalic(true); // font2.setBoldweight((short)18); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); // stborder.setFillForegroundColor(HSSFColor.ORANGE.index); // stborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 6000); shet1.setColumnWidth(1, 6000); shet1.setColumnWidth(2, 6000); shet1.setColumnWidth(3, 6000); shet1.setColumnWidth(4, 6000); shet1.setColumnWidth(5, 6000); shet1.setColumnWidth(6, 6000); shet1.setColumnWidth(7, 6000); shet1.setColumnWidth(8, 6000); shet1.setColumnWidth(9, 6000); shet1.setColumnWidth(10, 6000); // shet1.setColumnWidth(20, 2000); XSSFCellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorder.setFillForegroundColor(HSSFColor.ORANGE.index); styleBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // CREATE HEADING 2 XSSFRow rheading2 = shet1.createRow(0); rheading2.setHeightInPoints(25); XSSFCell cellxx1, cellxx2, cellxx3, cellxx4, cellxx5, cellxx6, cellxx7, cellxx8, cellxx9, cellxx10, cellxx11, cellxx12, cellxx13; cellxx1 = rheading2.createCell(0); cellxx2 = rheading2.createCell(1); cellxx3 = rheading2.createCell(2); cellxx4 = rheading2.createCell(3); cellxx5 = rheading2.createCell(4); cellxx6 = rheading2.createCell(5); cellxx7 = rheading2.createCell(6); cellxx8 = rheading2.createCell(7); cellxx9 = rheading2.createCell(8); cellxx10 = rheading2.createCell(9); cellxx11 = rheading2.createCell(10); cellxx12 = rheading2.createCell(11); cellxx13 = rheading2.createCell(12); // cellxx1.setCellValue("GROUP NAME"); // cellxx2.setCellValue("GENDER"); // cellxx3.setCellValue("CONTRACEPTIVE METHOD"); // cellxx4.setCellValue("REFERRED TO A SERVICE POINT"); // cellxx5.setCellValue("GIVEN CONDOMS"); // cellxx6.setCellValue("SCREENED FOR TB"); // cellxx7.setCellValue("SCREENED FOR STIS"); // cellxx8.setCellValue("TESTED PARTNER"); // cellxx9.setCellValue("TESTED CHILDREN"); // cellxx10.setCellValue("DISCLOSED STATUS"); // cellxx11.setCellValue("PERIOD"); cellxx1.setCellValue("DISTRICT"); cellxx2.setCellValue("GROUP NAME"); cellxx3.setCellValue("GENDER"); cellxx4.setCellValue("CONTRACEPTIVE METHOD"); cellxx5.setCellValue("REFERRED TO A SERVICE POINT"); cellxx6.setCellValue("GIVEN CONDOMS"); cellxx7.setCellValue("SCREENED FOR TB"); cellxx8.setCellValue("SCREENED FOR STIS"); cellxx9.setCellValue("TESTED PARTNER"); cellxx10.setCellValue("TESTED CHILDREN"); cellxx11.setCellValue("DISCLOSED STATUS"); cellxx12.setCellValue("PERIOD"); cellxx13.setCellValue("AGE BRACKET"); cellxx1.setCellStyle(styleBorder); cellxx2.setCellStyle(styleBorder); cellxx3.setCellStyle(styleBorder); cellxx4.setCellStyle(styleBorder); cellxx5.setCellStyle(styleBorder); cellxx6.setCellStyle(styleBorder); cellxx7.setCellStyle(styleBorder); cellxx8.setCellStyle(styleBorder); cellxx9.setCellStyle(styleBorder); cellxx10.setCellStyle(styleBorder); cellxx11.setCellStyle(styleBorder); cellxx12.setCellStyle(styleBorder); cellxx13.setCellStyle(styleBorder); pos = 1; XSSFCellStyle stylex = wb.createCellStyle(); //stylex.setFillForegroundColor(HSSFColor.LIME.index); //stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); String getServices = "SELECT client_id,groupName,GENDER, bit_or(cm) AS CONTRACEPTIVE_METHOD,bit_or(sp) AS REFERRED_TO_SERVICE_POINT, " + "SUM(cg) AS CONDOMS_GIVEN ,bit_or(st) AS SCREENED_TB ,bit_or(ss) AS SCREENED_STIS,bit_or(tp) TESTED_PARTNER, " + "bit_or(tc) AS TESTED_CHILDREN,bit_or(ds) as DISCLOSED_STATUS,year AS pepfaryear,month as pepfarmonth, district_name AS district,AGEBRACKET FROM ( " + "SELECT personal_information.client_id as client_id,groups.group_name as groupName,personal_information.gender as GENDER, " + " CASE " + " WHEN services_provided.contraceptive_method= 'YES' THEN 1 " + " WHEN services_provided.contraceptive_method= 'NO' THEN 0 " + "ELSE 'NONE' " + "END AS cm, " + "CASE " + " WHEN services_provided.rsp LIKE 'YES' THEN 1 " + " WHEN services_provided.rsp LIKE 'NO' THEN 0 " + "ELSE 'NONE' " + "END AS sp, " + " services_provided.cds_given AS cg, " + "CASE " + " WHEN services_provided.screened_tb='YES' THEN 1 " + " WHEN services_provided.screened_tb='NO' THEN 0 " + "ELSE 'NONE' " + "END AS st, " + "CASE " + " WHEN services_provided.screened_stis='YES' THEN 1 " + " WHEN services_provided.screened_stis='NO' THEN 0 " + "ELSE 'NONE' " + "END AS ss, " + "CASE " + " WHEN services_provided.tested_partner='YES' THEN 1 " + " WHEN services_provided.tested_partner='NO' THEN 0 " + "ELSE 'NONE' " + "END AS tp, " + "CASE " + " WHEN services_provided.tested_children= 'YES' THEN 1 " + " WHEN services_provided.tested_children= 'NO' THEN 0 " + "ELSE 'NONE' " + "END AS tc, " + "CASE " + " WHEN services_provided.disclosed_status= 'YES' THEN 1 " + " WHEN services_provided.disclosed_status= 'NO' THEN 0 " + "ELSE 'NONE' " + "END AS ds,services_provided.submission_month AS month,services_provided.submission_year as year, district.district_name as district_name," + "CASE" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above'" + " ELSE 'NO DATE OF BIRTH' " + " END AS AGEBRACKET " + "FROM personal_information JOIN district ON personal_information.district_id=district.district_id " + " LEFT JOIN groups ON groups.group_id=personal_information.group_id " + " JOIN services_provided ON services_provided.client_id=personal_information.client_id" + " WHERE personal_information.partner_id='" + partner_id + "' && services_provided.submission_month>='" + startdate + "' && services_provided.submission_month<='" + enddate + "' " + "&& services_provided.submission_year='" + pepfaryear + "' order by personal_information.client_id ) as temptbl" + " WHERE cm>0 || sp>0 || cg>0 || st>0 || ss>0" + " || tp>0 || tc>0 || ds>0 GROUP BY client_id ORDER BY client_id"; conn.rs = conn.st.executeQuery(getServices); while (conn.rs.next()) { groupname = conn.rs.getString(2); if (groupname == null) { groupname = "INDIVIDUAL"; } gender = conn.rs.getString(3); contraceptive_method = conn.rs.getInt(4); rsp = conn.rs.getInt(5); cds_given = conn.rs.getInt(6); screened_tb = conn.rs.getInt(7); screened_stis = conn.rs.getInt(8); tested_partner = conn.rs.getInt(9); tested_children = conn.rs.getInt(10); disclosed_status = conn.rs.getInt(11); datekey = Integer.parseInt(conn.rs.getInt(12) + "" + conn.rs.getInt(13)); periodS = conn.rs.getInt(12) + "-" + conn.rs.getInt(13); district = conn.rs.getString(14); agebracket = conn.rs.getString(15); if (contraceptive_method > 0 || rsp > 0 || cds_given > 0 || screened_tb > 0 || screened_stis > 0 || tested_partner > 0 || tested_children > 0 || disclosed_status > 0) { // CREATE ROW AND ADD DATA TO THE DATA CELLS====================== incrementor++; XSSFRow data = shet1.createRow(pos); data.setHeightInPoints(25); XSSFCell cellx1, cellx2, cellx3, cellx4, cellx5, cellx6, cellx7, cellx8, cellx9, cellx10, cellx11, cellx12, cellx13; cellx1 = data.createCell(0); cellx2 = data.createCell(1); cellx3 = data.createCell(2); cellx4 = data.createCell(3); cellx5 = data.createCell(4); cellx6 = data.createCell(5); cellx7 = data.createCell(6); cellx8 = data.createCell(7); cellx9 = data.createCell(8); cellx10 = data.createCell(9); cellx11 = data.createCell(10); cellx12 = data.createCell(11); cellx13 = data.createCell(12); cellx1.setCellValue(district); cellx2.setCellValue(groupname); cellx3.setCellValue(gender); cellx4.setCellValue(contraceptive_method); cellx5.setCellValue(rsp); cellx6.setCellValue(cds_given); cellx7.setCellValue(screened_tb); cellx8.setCellValue(screened_stis); cellx9.setCellValue(tested_partner); cellx10.setCellValue(tested_children); cellx11.setCellValue(disclosed_status); cellx12.setCellValue(periodS); cellx13.setCellValue(agebracket); cellx1.setCellStyle(stylex); cellx2.setCellStyle(stylex); cellx3.setCellStyle(stylex); cellx4.setCellStyle(stylex); cellx5.setCellStyle(stylex); cellx6.setCellStyle(stylex); cellx7.setCellStyle(stylex); cellx8.setCellStyle(stylex); cellx9.setCellStyle(stylex); cellx10.setCellStyle(stylex); cellx11.setCellStyle(stylex); cellx12.setCellStyle(stylex); cellx13.setCellStyle(stylex); pos++; } } String getPartner = "SELECT partner_name FROM partner WHERE partner_id='" + partner_id + "'"; conn.rs = conn.st.executeQuery(getPartner); if (conn.rs.next() == true) { partner_name = conn.rs.getString(1).trim().replace(" ", "_"); } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } if (incrementor > 0) { // write it as an excel attachment if (session.getAttribute("period").toString().equals("10-12")) { pepfaryear++; } ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_SERVICES_PROVIDED_PER_GROUP_REPORT_FOR_pepfar_year_" + pepfaryear + "(" + period + ")_AND_PARTNER_" + partner_name + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); } else { session.setAttribute("kePMSError", "<font color=\"red\"><b>NO DATA WITHIN THE SELECTED PARAMETERS.</b></font>"); response.sendRedirect("kePMS.jsp"); } }
From source file:reports.kePMSnew.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException, InvalidFormatException { session = request.getSession();//from w w w . j av a 2 s. c o m dbConn conn = new dbConn(); pos = 0; incrementor = 0; // pepfaryear=2014; // pepfaryear=Integer.parseInt(request.getParameter("year")); pepfaryear = Integer.parseInt(session.getAttribute("PepfarYear").toString()); prevyear = pepfaryear - 1; String enddate = pepfaryear + "09"; String startdate = prevyear + "10"; start = Integer.parseInt(startdate); end = Integer.parseInt(enddate); System.out.println("start date : " + start + " end date : " + end); // COPY FILE TO BE WRITTEN TO Path original = Paths.get(getServletContext().getRealPath("/TEMPLATE.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/TEMPLATE_1.xlsm")); //new file System.out.println("origin : " + original + " destination : " + destination); try { Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING); System.out.println("file copied----------------"); } catch (IOException x) { //catch all for IO problems System.out.println("fine not copied"); } String allpath = getServletContext().getRealPath("/TEMPLATE_1.xlsm"); System.out.println("real path for template : " + allpath); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb; OPCPackage pkg = OPCPackage.open(allpath); wb = new XSSFWorkbook(pkg); // HSSFWorkbook wb=new HSSFWorkbook(); XSSFSheet shet1 = wb.getSheet("sheet0"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 4000); shet1.setColumnWidth(1, 4000); shet1.setColumnWidth(2, 4000); shet1.setColumnWidth(3, 4000); shet1.setColumnWidth(4, 4000); XSSFRow rw4 = shet1.createRow(0); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); XSSFCell cell0, cell1, cell2, cell3, cell4; cell0 = rw4.createCell(0); cell1 = rw4.createCell(1); cell2 = rw4.createCell(2); cell3 = rw4.createCell(3); cell4 = rw4.createCell(4); cell0.setCellValue("PARTNER NAME"); cell1.setCellValue("AGE BRACKET"); cell2.setCellValue("GENDER"); cell3.setCellValue("MONTH"); cell4.setCellValue("ACHIEVED"); String getClients = "SELECT partner.partner_name," + "CASE " + "when personal_information.completionmonth =01 THEN '" + pepfaryear + "-01(JAN)' " + "when personal_information.completionmonth =02 THEN '" + pepfaryear + "-02 (FEB)' " + "when personal_information.completionmonth =03 THEN '" + pepfaryear + "-03 (MAR)' " + "when personal_information.completionmonth=04 THEN '" + pepfaryear + "-04 (APR)' " + "when personal_information.completionmonth=05 THEN '" + pepfaryear + "-05 (MAY)' " + "when personal_information.completionmonth=06 THEN '" + pepfaryear + "-06 (JUN)' " + "when personal_information.completionmonth=07 THEN '" + pepfaryear + "-07 (JUL)' " + "when personal_information.completionmonth=08 THEN '" + pepfaryear + "-08 (AUG)' " + "when personal_information.completionmonth=09 THEN '" + pepfaryear + "-09 (SEPT)' " + "when personal_information.completionmonth=10 THEN '" + prevyear + "-10 (OCT)' " + "when personal_information.completionmonth=11 THEN '" + prevyear + "-11 (NOV)'" + "when personal_information.completionmonth=12 THEN '" + prevyear + "-12 (DEC)'" + "END AS MONTHS,personal_information.completionyear," + "CASE" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above'" + " ELSE 'NO DATE OF BIRTH'" + " END AS AGEBRACKET," + "CASE " + "when personal_information.gender LIKE 'Female' THEN 'F' " + "when personal_information.gender LIKE 'Male' THEN 'M' " + "ELSE 'NO SEX' " + "END AS SEX" + ",completionmonth,COUNT(personal_information.client_id) FROM personal_information " + "JOIN partner ON personal_information.partner_id=partner.partner_id " + " WHERE personal_information.completionmonth>0 && personal_information.completionyear>0 GROUP BY partner.partner_name,SEX,personal_information.completionyear,MONTHS,AGEBRACKET ORDER BY personal_information.partner_id"; conn.rs = conn.st.executeQuery(getClients); while (conn.rs.next()) { partnername = clientid = gender = ""; age = 0; partnername = conn.rs.getString(1); month = conn.rs.getString(2); year = conn.rs.getInt(3); agebracket = conn.rs.getString(4); gender = conn.rs.getString(5); String dkey = year + "" + conn.rs.getString(6); datekey = Integer.parseInt(dkey); achieved = conn.rs.getInt(7); incrementor += achieved; System.out.println( "date key : " + datekey + "startdate : " + start + " end date : " + end + " year : " + year); if (datekey >= start && datekey <= end && year >= 2014) { pos++; XSSFRow rw4x = shet1.createRow(pos); rw4x.setHeightInPoints(25); rw4x.setRowStyle(style2); XSSFCell cell0x, cell1x, cell2x, cell3x, cell4x; cell0x = rw4x.createCell(0); cell1x = rw4x.createCell(1); cell2x = rw4x.createCell(2); cell3x = rw4x.createCell(3); cell4x = rw4x.createCell(4); // OUTPUT SERVICES PROVIDED================================ cell0x.setCellValue(partnername); cell1x.setCellValue(agebracket); cell2x.setCellValue(gender); cell3x.setCellValue(month); cell4x.setCellValue(achieved); System.out.println(pos + "-----partner :" + partnername + " age bracket :" + agebracket + " gender :" + gender + " completion month : " + month); } } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } if (incrementor > 0) { IdGenerator CRT = new IdGenerator(); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_kePMS_ACHIEVED_REPORT_PER_PARTNER_FOR_PEPFAR_YEAR_" + pepfaryear + "_CREATED_ON_" + CRT.timestamp() + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); } else { session.setAttribute("kePMSError", "<font color=\"red\"><b>NO DATA WITHIN THE SELECTED PARAMETERS.</b></font>"); response.sendRedirect("kePMS.jsp"); } }
From source file:reports.kePMSNotAchieved.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, InvalidFormatException, SQLException { session = request.getSession();//from www . j a v a 2 s . c o m dbConn conn = new dbConn(); pos = 0; // COPY FILE TO BE WRITTEN TO Path original = Paths.get(getServletContext().getRealPath("/NotAchieved.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/NotAchieved_1.xlsm")); //new file System.out.println("origin : " + original + " destination : " + destination); try { Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING); System.out.println("file copied----------------"); } catch (IOException x) { //catch all for IO problems System.out.println("fine not copied"); } String allpath = getServletContext().getRealPath("/NotAchieved_1.xlsm"); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb; OPCPackage pkg = OPCPackage.open(allpath); wb = new XSSFWorkbook(pkg); // HSSFWorkbook wb=new HSSFWorkbook(); XSSFSheet shet1 = wb.getSheet("sheet1"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 4000); shet1.setColumnWidth(1, 4000); shet1.setColumnWidth(2, 4000); shet1.setColumnWidth(3, 4000); shet1.setColumnWidth(4, 4000); XSSFRow rw4 = shet1.createRow(0); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); XSSFCell cell0, cell1, cell2, cell3, cell4, cell5, cell6; cell0 = rw4.createCell(0); cell1 = rw4.createCell(1); cell2 = rw4.createCell(2); cell3 = rw4.createCell(3); cell4 = rw4.createCell(4); cell5 = rw4.createCell(5); cell6 = rw4.createCell(6); cell0.setCellValue("COUNTY NAME"); cell1.setCellValue("DISTRICT NAME"); cell2.setCellValue("PARTNER NAME"); cell3.setCellValue("GROUP NAME"); cell4.setCellValue("AGE BRACKET"); cell5.setCellValue("GENDER"); cell6.setCellValue("CLIENTS"); String getClients = "SELECT county.county_name,district.district_name,partner.partner_name," + "CASE" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 14 THEN '0-14'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >24 THEN '>25'" + " ELSE 'NO DATE OF BIRTH'" + " END AS AGEBRACKET," + "CASE " + "when personal_information.gender LIKE 'Female' THEN 'F' " + "when personal_information.gender LIKE 'Male' THEN 'M' " + "ELSE 'NO SEX' " + "END AS SEX" + ",COUNT(personal_information.client_id),groups.group_name as groupname FROM personal_information " + "JOIN partner ON personal_information.partner_id=partner.partner_id " + " JOIN district ON district.district_id=personal_information.district_id " + "JOIN county ON county.county_id=district.county_id " + "LEFT JOIN groups ON personal_information.group_id=groups.group_id " + " WHERE personal_information.lessons_attended>0 && personal_information.completionyear=0 " + "GROUP BY partner.partner_name,district.district_name,groupname,SEX,AGEBRACKET ORDER BY personal_information.partner_id"; conn.rs = conn.st.executeQuery(getClients); while (conn.rs.next()) { partnername = clientid = gender = ""; age = 0; countyname = conn.rs.getString(1); districtname = conn.rs.getString(2); partnername = conn.rs.getString(3); agebracket = conn.rs.getString(4); gender = conn.rs.getString(5); achieved = conn.rs.getInt(6); groupname = conn.rs.getString(7); if (groupname == null) { groupname = "INDIVIDUAL"; } pos++; XSSFRow rw4x = shet1.createRow(pos); rw4x.setHeightInPoints(25); rw4x.setRowStyle(style2); XSSFCell cell0x, cell1x, cell2x, cell3x, cell4x, cell5x, cell6x; cell0x = rw4x.createCell(0); cell1x = rw4x.createCell(1); cell2x = rw4x.createCell(2); cell3x = rw4x.createCell(3); cell4x = rw4x.createCell(4); cell5x = rw4x.createCell(5); cell6x = rw4x.createCell(6); // OUTPUT SERVICES PROVIDED================================ cell0x.setCellValue(countyname); cell1x.setCellValue(districtname); cell2x.setCellValue(partnername); cell3x.setCellValue(groupname); cell4x.setCellValue(agebracket); cell5x.setCellValue(gender); cell6x.setCellValue(achieved); System.out.println(pos + "-----partner :" + partnername + " age bracket :" + agebracket + " gender :" + gender + " completion month : " + month); } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_kePMS_REPORT_FOR_NOT_ACHIEVED.xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); }
From source file:reports.kePMSNotAchievedDIC.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, InvalidFormatException, SQLException { session = request.getSession();//from ww w. j a v a2s. c o m dbConn conn = new dbConn(); pos = 0; // partner_id="4"; partner_id = session.getAttribute("partnerDIC").toString(); String getPartner = "SELECT partner_name FROM partner WHERE partner_id='" + partner_id + "'"; conn.rs = conn.st.executeQuery(getPartner); if (conn.rs.next() == true) { partnername = conn.rs.getString(1).trim().replace(" ", "_"); } // COPY FILE TO BE WRITTEN TO Path original = Paths.get(getServletContext().getRealPath("/TEMPLATE.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/TEMPLATE_1.xlsm")); //new file System.out.println("origin : " + original + " destination : " + destination); try { Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING); System.out.println("file copied----------------"); } catch (IOException x) { //catch all for IO problems System.out.println("fine not copied"); } String allpath = getServletContext().getRealPath("/TEMPLATE_1.xlsm"); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb; OPCPackage pkg = OPCPackage.open(allpath); wb = new XSSFWorkbook(pkg); // HSSFWorkbook wb=new HSSFWorkbook(); XSSFSheet shet1 = wb.getSheet("sheet0"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 4000); shet1.setColumnWidth(1, 4000); shet1.setColumnWidth(2, 4000); shet1.setColumnWidth(3, 4000); shet1.setColumnWidth(4, 4000); XSSFRow rw4 = shet1.createRow(0); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); XSSFCell cell0, cell1, cell2, cell3, cell4, cell5; cell0 = rw4.createCell(0); cell1 = rw4.createCell(1); cell2 = rw4.createCell(2); cell3 = rw4.createCell(3); cell4 = rw4.createCell(4); cell5 = rw4.createCell(5); cell0.setCellValue("COUNTY NAME"); cell1.setCellValue("DISTRICT NAME"); cell2.setCellValue("DIC NAME"); cell3.setCellValue("AGE BRACKET"); cell4.setCellValue("GENDER"); cell5.setCellValue("CLIENTS"); String getClients = "SELECT county.county_name,district.district_name,dic.dic_name," + "CASE" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 14 THEN '0-14'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >24 THEN '>25'" + " ELSE 'NOT SELECTED'" + " END AS AGEBRACKET," + "CASE " + "when personal_information.gender LIKE 'Female' THEN 'F' " + "when personal_information.gender LIKE 'Male' THEN 'M' " + "ELSE 'NO SEX' " + "END AS SEX" + ",COUNT(personal_information.client_id) FROM personal_information " + "JOIN dic ON personal_information.dic_id=dic.dic_id " + " JOIN district ON district.district_id=personal_information.district_id " + "JOIN county ON county.county_id=district.county_id " + " WHERE personal_information.partner_id='" + partner_id + "' && personal_information.lessons_attended>0 && personal_information.completionyear=0 " + "GROUP BY dic.dic_name,district.district_name,SEX,AGEBRACKET ORDER BY personal_information.partner_id"; conn.rs = conn.st.executeQuery(getClients); while (conn.rs.next()) { partnername = clientid = gender = ""; age = 0; countyname = conn.rs.getString(1); districtname = conn.rs.getString(2); partnername = conn.rs.getString(3); agebracket = conn.rs.getString(4); gender = conn.rs.getString(5); achieved = conn.rs.getInt(6); pos++; XSSFRow rw4x = shet1.createRow(pos); rw4x.setHeightInPoints(25); rw4x.setRowStyle(style2); XSSFCell cell0x, cell1x, cell2x, cell3x, cell4x, cell5x; cell0x = rw4x.createCell(0); cell1x = rw4x.createCell(1); cell2x = rw4x.createCell(2); cell3x = rw4x.createCell(3); cell4x = rw4x.createCell(4); cell5x = rw4x.createCell(5); // OUTPUT SERVICES PROVIDED================================ cell0x.setCellValue(countyname); cell1x.setCellValue(districtname); cell2x.setCellValue(partnername); cell3x.setCellValue(agebracket); cell4x.setCellValue(gender); cell5x.setCellValue(achieved); System.out.println(pos + "-----partner :" + partnername + " age bracket :" + agebracket + " gender :" + gender + " completion month : " + month); } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_kePMS_REPORT_FOR_NOT_ACHIEVED_PER_DIC_FOR_" + partnername.trim() + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); }
From source file:reports.kePMSServices.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException, InvalidFormatException { session = request.getSession();//from w ww.jav a 2s . c o m dbConn conn = new dbConn(); incrementor = 0; pos = 0; pepfaryear = Integer.parseInt(session.getAttribute("PepfarYear").toString()); prevyear = pepfaryear - 1; String[] periods = session.getAttribute("period").toString().split("-"); if (session.getAttribute("period").toString().equals("10-12")) { period = "OCT-DEC"; } else if (session.getAttribute("period").toString().equals("01-03")) { period = "JAN-MARCH"; } else if (session.getAttribute("period").toString().equals("04-06")) { period = "APRIL-JUNE"; } else if (session.getAttribute("period").toString().equals("07-09")) { period = "JULY-SEPT"; } else { } startdate = periods[0]; enddate = periods[1]; if (session.getAttribute("period").toString().equals("10-12")) { pepfaryear = pepfaryear - 1; } start = Integer.parseInt(startdate); end = Integer.parseInt(enddate); Path original = Paths.get(getServletContext().getRealPath("/ServicesAll.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/ServicesAll_1.xlsm")); //new file System.out.println("origin : " + original + " destination : " + destination); try { Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING); System.out.println("file copied----------------"); } catch (IOException x) { //catch all for IO problems System.out.println("fine not copied"); } String allpath = getServletContext().getRealPath("/ServicesAll_1.xlsm"); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb; OPCPackage pkg = OPCPackage.open(allpath); wb = new XSSFWorkbook(pkg); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFSheet shet1 = wb.getSheet("sheet1"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); // font.setItalic(true); // font.setBoldweight((short)12); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); // font2.setFontHeightInPoints((short)15); font2.setFontName("Arial Black"); // font.setItalic(true); // font2.setBoldweight((short)18); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); // stborder.setFillForegroundColor(HSSFColor.ORANGE.index); // stborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 6000); shet1.setColumnWidth(1, 6000); shet1.setColumnWidth(2, 6000); shet1.setColumnWidth(3, 6000); shet1.setColumnWidth(4, 6000); shet1.setColumnWidth(5, 6000); shet1.setColumnWidth(6, 6000); shet1.setColumnWidth(7, 6000); shet1.setColumnWidth(8, 6000); shet1.setColumnWidth(9, 6000); shet1.setColumnWidth(10, 6000); shet1.setColumnWidth(11, 6000); // shet1.setColumnWidth(20, 2000); XSSFCellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorder.setFillForegroundColor(HSSFColor.ORANGE.index); styleBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // CREATE HEADING 2 XSSFRow rheading2 = shet1.createRow(0); rheading2.setHeightInPoints(25); XSSFCell cellxx1, cellxx2, cellxx3, cellxx4, cellxx5, cellxx6, cellxx7, cellxx8, cellxx9, cellxx10, cellxx11, cellxx12, cellxx13, cellxx14, cellxx15; cellxx1 = rheading2.createCell(0); cellxx2 = rheading2.createCell(1); cellxx3 = rheading2.createCell(2); cellxx4 = rheading2.createCell(3); cellxx5 = rheading2.createCell(4); cellxx6 = rheading2.createCell(5); cellxx7 = rheading2.createCell(6); cellxx8 = rheading2.createCell(7); cellxx9 = rheading2.createCell(8); cellxx10 = rheading2.createCell(9); cellxx11 = rheading2.createCell(10); cellxx12 = rheading2.createCell(11); cellxx13 = rheading2.createCell(12); // cellxx14=rheading2.createCell(13); // cellxx15=rheading2.createCell(14); cellxx1.setCellValue("COUNTY NAME"); cellxx2.setCellValue("PARTNER NAME"); cellxx3.setCellValue("DISTRICT NAME"); cellxx4.setCellValue("GENDER"); cellxx5.setCellValue("CONTRACEPTIVE METHOD"); cellxx6.setCellValue("REFERRED TO A SERVICE POINT"); cellxx7.setCellValue("GIVEN CONDOMS"); cellxx8.setCellValue("SCREENED FOR TB"); cellxx9.setCellValue("SCREENED FOR STIS"); cellxx10.setCellValue("TESTED PARTNER"); cellxx11.setCellValue("TESTED CHILDREN"); cellxx12.setCellValue("DISCLOSED STATUS"); // cellxx13.setCellValue("YEAR"); // cellxx14.setCellValue("MONTH"); cellxx13.setCellValue("AGE BRACKET"); cellxx1.setCellStyle(styleBorder); cellxx2.setCellStyle(styleBorder); cellxx3.setCellStyle(styleBorder); cellxx4.setCellStyle(styleBorder); cellxx5.setCellStyle(styleBorder); cellxx6.setCellStyle(styleBorder); cellxx7.setCellStyle(styleBorder); cellxx8.setCellStyle(styleBorder); cellxx9.setCellStyle(styleBorder); cellxx10.setCellStyle(styleBorder); cellxx11.setCellStyle(styleBorder); cellxx12.setCellStyle(styleBorder); cellxx13.setCellStyle(styleBorder); // cellxx14.setCellStyle(styleBorder); // cellxx15.setCellStyle(styleBorder); pos = 1; XSSFCellStyle stylex = wb.createCellStyle(); //stylex.setFillForegroundColor(HSSFColor.LIME.index); //stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); String getServices = "SELECT client_id,COUNTY,GENDER, bit_or(cm) AS CONTRACEPTIVE_METHOD,bit_or(sp) AS REFERRED_TO_SERVICE_POINT, " + "SUM(cg) AS CONDOMS_GIVEN ,bit_or(st) AS SCREENED_TB ,bit_or(ss) AS SCREENED_STIS,bit_or(tp) TESTED_PARTNER, " + "bit_or(tc) AS TESTED_CHILDREN,bit_or(ds) as DISCLOSED_STATUS,year AS pepfaryear,month as pepfarmonth,partnername as partner,districtname as district,AGEBRACKET FROM ( " + "SELECT personal_information.client_id as client_id,county.county_name as COUNTY,personal_information.gender as GENDER, " + " CASE " + " WHEN services_provided.contraceptive_method= 'YES' THEN 1 " + " WHEN services_provided.contraceptive_method= 'NO' THEN 0 " + "ELSE 'NONE' " + "END AS cm, " + "CASE " + " WHEN services_provided.rsp LIKE 'YES' THEN 1 " + " WHEN services_provided.rsp LIKE 'NO' THEN 0 " + "ELSE 'NONE' " + "END AS sp, " + " services_provided.cds_given AS cg, " + "CASE " + " WHEN services_provided.screened_tb='YES' THEN 1 " + " WHEN services_provided.screened_tb='NO' THEN 0 " + "ELSE 'NONE' " + "END AS st, " + "CASE " + " WHEN services_provided.screened_stis='YES' THEN 1 " + " WHEN services_provided.screened_stis='NO' THEN 0 " + "ELSE 'NONE' " + "END AS ss, " + "CASE " + " WHEN services_provided.tested_partner='YES' THEN 1 " + " WHEN services_provided.tested_partner='NO' THEN 0 " + "ELSE 'NONE' " + "END AS tp, " + "CASE " + " WHEN services_provided.tested_children= 'YES' THEN 1 " + " WHEN services_provided.tested_children= 'NO' THEN 0 " + "ELSE 'NONE' " + "END AS tc, " + "CASE " + " WHEN services_provided.disclosed_status= 'YES' THEN 1 " + " WHEN services_provided.disclosed_status= 'NO' THEN 0 " + "ELSE 'NONE' " + "END AS ds,services_provided.submission_month AS month,services_provided.submission_year as year,partner.partner_name as partnername," + "district.district_name as districtname," // + " CASE " // + " WHEN services_provided.submission_month=1 THEN 'JAN' " // + " WHEN services_provided.submission_month=2 THEN 'FEB' " // + " WHEN services_provided.submission_month=3 THEN 'MAR' " // + " WHEN services_provided.submission_month=4 THEN 'APR' " // + " WHEN services_provided.submission_month=5 THEN 'MAY' " // + " WHEN services_provided.submission_month=6 THEN 'JUN' " // + " WHEN services_provided.submission_month=7 THEN 'JUL' " // + " WHEN services_provided.submission_month=8 THEN 'AUG' " // + " WHEN services_provided.submission_month=9 THEN 'SEPT' " // + " WHEN services_provided.submission_month=10 THEN 'OCT' " // + " WHEN services_provided.submission_month=11 THEN 'NOV' " // + " WHEN services_provided.submission_month=12 THEN 'DEC'" // + "ELSE 'NO MONTH' END AS MONTHNAME," + "CASE" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above'" + " ELSE 'NO DATE OF BIRTH' " + " END AS AGEBRACKET " + " FROM personal_information JOIN partner ON personal_information.partner_id=partner.partner_id " + "" + "" + " JOIN (district JOIN county ON county.county_id=district.county_id) ON district.district_id=personal_information.district_id " + " JOIN services_provided ON services_provided.client_id=personal_information.client_id" + " WHERE services_provided.submission_month>='" + startdate + "' && services_provided.submission_month<='" + enddate + "' " + "&& services_provided.submission_year='" + pepfaryear + "' order by personal_information.client_id ) as temptbl" + " WHERE (cm>0 || sp>0 || cg>0 || st>0 || ss>0" + " || tp>0 || tc>0 || ds>0) GROUP BY client_id ORDER BY client_id"; conn.rs = conn.st.executeQuery(getServices); while (conn.rs.next()) { countyname = conn.rs.getString(2); gender = conn.rs.getString(3); contraceptive_method = conn.rs.getInt(4); rsp = conn.rs.getInt(5); cds_given = conn.rs.getInt(6); screened_tb = conn.rs.getInt(7); screened_stis = conn.rs.getInt(8); tested_partner = conn.rs.getInt(9); tested_children = conn.rs.getInt(10); disclosed_status = conn.rs.getInt(11); datekey = Integer.parseInt(conn.rs.getInt(12) + "" + conn.rs.getInt(13)); partner = conn.rs.getString(14); district = conn.rs.getString(15); // monthname=conn.rs.getString(16); agebracket = conn.rs.getString(16); if (contraceptive_method > 0 || rsp > 0 || cds_given > 0 || screened_tb > 0 || screened_stis > 0 || tested_partner > 0 || tested_children > 0 || disclosed_status > 0) { // CREATE ROW AND ADD DATA TO THE DATA CELLS====================== incrementor++; XSSFRow data = shet1.createRow(pos); data.setHeightInPoints(25); XSSFCell cellx1, cellx2, cellx3, cellx4, cellx5, cellx6, cellx7, cellx8, cellx9, cellx10, cellx11, cellx12, cellx13, cellx14, cellx15; cellx1 = data.createCell(0); cellx2 = data.createCell(1); cellx3 = data.createCell(2); cellx4 = data.createCell(3); cellx5 = data.createCell(4); cellx6 = data.createCell(5); cellx7 = data.createCell(6); cellx8 = data.createCell(7); cellx9 = data.createCell(8); cellx10 = data.createCell(9); cellx11 = data.createCell(10); cellx12 = data.createCell(11); cellx13 = data.createCell(12); // cellx14=data.createCell(13); // cellx15=data.createCell(14); cellx1.setCellValue(countyname); cellx2.setCellValue(partner); cellx3.setCellValue(district); cellx4.setCellValue(gender); cellx5.setCellValue(contraceptive_method); cellx6.setCellValue(rsp); cellx7.setCellValue(cds_given); cellx8.setCellValue(screened_tb); cellx9.setCellValue(screened_stis); cellx10.setCellValue(tested_partner); cellx11.setCellValue(tested_children); cellx12.setCellValue(disclosed_status); // cellx13.setCellValue(pepfaryear); cellx13.setCellValue(agebracket); // cellx15.setCellValue(); cellx1.setCellStyle(stylex); cellx2.setCellStyle(stylex); cellx3.setCellStyle(stylex); cellx4.setCellStyle(stylex); cellx5.setCellStyle(stylex); cellx6.setCellStyle(stylex); cellx7.setCellStyle(stylex); cellx8.setCellStyle(stylex); cellx9.setCellStyle(stylex); cellx10.setCellStyle(stylex); cellx11.setCellStyle(stylex); cellx12.setCellStyle(stylex); cellx13.setCellStyle(stylex); // cellx14.setCellStyle(stylex); // cellx15.setCellStyle(stylex); pos++; } } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } if (incrementor > 0) { // write it as an excel attachment if (session.getAttribute("period").toString().equals("10-12")) { pepfaryear++; } ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_SERVICES_PROVIDED_REPORT_FOR_PEPFAR_YEAR_" + pepfaryear + "(" + period + ").xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); } else { session.setAttribute("kePMSError", "<font color=\"red\"><b>NO DATA WITHIN THE SELECTED PARAMETERS.</b></font>"); response.sendRedirect("kePMS.jsp"); } }
From source file:reports.moh731_Facility.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();/*from ww w . j a va 2s .co m*/ dbConn conn = new dbConn(); pmtcthv = 0; htchv = 0; arthv = 0; year = request.getParameter("year"); month = request.getParameter("month"); //-------------------------------------------------------------------------------- //-------------------------------------------------------------------------------- //added later to accomodate the years String subpartnerid = "SubPartnerID"; int monthint = 0; int yearint = 0; monthint = Integer.parseInt(month); yearint = Integer.parseInt(year); String subpartnera = "subpartnera"; if (yearint == 2015) { if (monthint == 10 || monthint == 11 || monthint == 12 || monthint == 1 || monthint == 2 || monthint == 3) { //here use a different subpartner id subpartnerid = "SP_ID"; subpartnera = "subpartnera2014"; } else { subpartnerid = "SubPartnerID"; subpartnera = "subpartnera"; } } else if (yearint <= 2014) { subpartnerid = "SP_ID"; subpartnera = "subpartnera2014"; } else if (yearint > 2015) { subpartnerid = "SubPartnerID"; subpartnera = "subpartnera"; } //--------------------------------------------------------------------------------------- //--------------------------------------------------------------------------------------- // year="2015"; // month="5"; counter = 0; monthName = ""; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet shet4 = wb.createSheet("HTC"); XSSFSheet shet1 = wb.createSheet("PMTCT"); XSSFSheet shet2 = wb.createSheet("Care and Treatment"); XSSFSheet shet3 = wb.createSheet("PEP"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); for (int i = 0; i <= 3; i++) { shet1.setColumnWidth(i, 6000); } for (int i = 4; i <= 47; i++) { shet1.setColumnWidth(i, 4000); } for (int i = 0; i <= 3; i++) { shet2.setColumnWidth(i, 6000); } for (int i = 4; i <= 66; i++) { shet2.setColumnWidth(i, 3000); } for (int i = 0; i <= 3; i++) { shet3.setColumnWidth(i, 6000); } for (int i = 4; i <= 19; i++) { shet3.setColumnWidth(i, 3000); } for (int i = 0; i <= 3; i++) { shet4.setColumnWidth(i, 6000); } for (int i = 4; i <= 14; i++) { shet4.setColumnWidth(i, 3000); } headers = "COUNTY,SUB COUNTY,FACILITY NAME,MFL CODE,ART HIGH VOLUME,HTC HIGH VOLUME,PMTCT HIGH VOLUME"; String arrayHeader[] = headers.split(","); int headerno = 0; // XSSFRow rw0S1=shet1.createRow(0); XSSFRow rw1S1 = shet1.createRow(0); // XSSFRow rw0S2=shet2.createRow(0); XSSFRow rw1S2 = shet2.createRow(0); // XSSFRow rw0S3=shet3.createRow(0); XSSFRow rw1S3 = shet3.createRow(0); // XSSFRow rw0S3=shet3.createRow(0); XSSFRow rw1S4 = shet4.createRow(0); // LOOP THROUGH AND WRITE STATIC HEADERS TO THE ELEMENTS for (String headername : arrayHeader) { XSSFCell S1cell = rw1S1.createCell(headerno); S1cell.setCellValue(headername); S1cell.setCellStyle(stylex); XSSFCell S2cell = rw1S2.createCell(headerno); S2cell.setCellValue(headername); S2cell.setCellStyle(stylex); XSSFCell S3cell = rw1S3.createCell(headerno); S3cell.setCellValue(headername); S3cell.setCellStyle(stylex); XSSFCell S4cell = rw1S4.createCell(headerno); S4cell.setCellValue(headername); S4cell.setCellStyle(stylex); headerno++; } String getMonth = "SELECT name FROM month WHERE id='" + month + "'"; conn.rs = conn.st.executeQuery(getMonth); if (conn.rs.next() == true) { monthName = conn.rs.getString(1); } counterPMTCT = counterART = counterPEP = counterHTC = 6; //get the specifc indicator names and the respective section and a count of indicators in each section which dictates the number of columns in the whole excel String getVariables = "SELECT * FROM moh731_indicators ORDER BY id"; conn.rs1 = conn.st1.executeQuery(getVariables); while (conn.rs1.next()) { elementName = conn.rs1.getString("indicator") + " \n" + conn.rs1.getString("code"); if (conn.rs1.getString("section").equals("1. HIV Counselling and Testing")) { counterHTC++; XSSFCell S4cell = rw1S4.createCell(counterHTC); S4cell.setCellValue(elementName); S4cell.setCellStyle(stylex); } else if (conn.rs1.getString("section").equals("2. Prevention of Mother-to-Child Transmission")) { counterPMTCT++; XSSFCell S1cell = rw1S1.createCell(counterPMTCT); S1cell.setCellValue(elementName); S1cell.setCellStyle(stylex); } else if (conn.rs1.getString("section").equals("3. Care and Treatment")) { counterART++; XSSFCell S2cell = rw1S2.createCell(counterART); S2cell.setCellValue(elementName); S2cell.setCellStyle(stylex); } else if (conn.rs1.getString("section").equals("5. Post-Exposure Prophylaxis")) { counterPEP++; XSSFCell S3cell = rw1S3.createCell(counterPEP); S3cell.setCellValue(elementName); S3cell.setCellStyle(stylex); } else { } } XSSFCell S3cella = rw1S1.createCell(49); S3cella.setCellValue("Validation Run"); S3cella.setCellStyle(stylex); XSSFCell S3cell2a = rw1S2.createCell(68); S3cell2a.setCellValue("Validation Run"); S3cell2a.setCellStyle(stylex); XSSFCell S3cell3a = rw1S3.createCell(20); S3cell3a.setCellValue("Validation Run"); S3cell3a.setCellStyle(stylex); XSSFCell S4cell3a = rw1S4.createCell(22); S4cell3a.setCellValue("Validation Run"); S4cell3a.setCellStyle(stylex); // counter=1; System.out.println("pmtct : " + counterPMTCT + " art : " + counterART + " PEP : " + counterPEP + " HTC : " + counterHTC); // counterPMTCT=counterPMTCT-5; // counterART=counterART-5; // counterPEP=counterPEP-5; counterPMTCT1 = counterART1 = counterPEP1 = counterHTC1 = 0; String getData = "SELECT county.County,district.DistrictNom," + subpartnera + ".SubPartnerNom," + subpartnera + ".CentreSanteId," + " moh731.HV0201,moh731.HV0202,moh731.HV0203,moh731.HV0204,moh731.HV0205,moh731.HV0206,moh731.HV0207,moh731.HV0208,moh731.HV0209,moh731.HV0210,moh731.HV0211,moh731.HV0212,moh731.HV0213," + "moh731.HV0214,moh731.HV0215,moh731.HV0216,moh731.HV0217,moh731.HV0218,moh731.HV0219,moh731.HV0220,moh731.HV0221,moh731.HV0224,moh731.HV0225,moh731.HV0226,moh731.HV0227,moh731.HV0228,moh731.HV0229," + " moh731.HV0230,moh731.HV0231,moh731.HV0232,moh731.HV0233,moh731.HV0234,moh731.HV0235,moh731.HV0236,moh731.HV0237,moh731.HV0238,moh731.HV0239,moh731.HV0240,moh731.HV0241,moh731.HV0242," + " moh731.HV0243,moh731.HV0244,moh731.HV0301,moh731.HV0302,moh731.HV0303,moh731.HV0304,moh731.HV0305,moh731.HV0306,moh731.HV0307,moh731.HV0308,moh731.HV0309,moh731.HV0310,moh731.HV0311,moh731.HV0312,moh731.HV0313,moh731.HV0314," + " moh731.HV0315,moh731.HV0316,moh731.HV0317,moh731.HV0318,moh731.HV0319,moh731.HV0320,moh731.HV0321,moh731.HV0322,moh731.HV0323,moh731.HV0324,moh731.HV0325,moh731.HV0326,moh731.HV0327,moh731.HV0328," + " moh731.HV0329,moh731.HV0330,moh731.HV0331,moh731.HV0332,moh731.HV0333,moh731.HV0334,moh731.HV0335,moh731.HV0336,moh731.HV0337,moh731.HV0338,moh731.HV0339,moh731.HV0340,moh731.HV0341," + " moh731.HV0342,moh731.HV0343,moh731.HV0344,moh731.HV0345,moh731.HV0346,moh731.HV0347,moh731.HV0348,moh731.HV0349,moh731.HV0350,moh731.HV0351,moh731.HV0352,moh731.HV0353," + " moh731.HV0354,moh731.HV0355,moh731.HV0904,moh731.HV0905,moh731.HV0370,moh731.HV0371,moh731.HV0372,moh731.HV0373," + " moh731.HV0501,moh731.HV0502,moh731.HV0503,moh731.HV0504,moh731.HV0505,moh731.HV0506,moh731.HV0507,moh731.HV0508,moh731.HV0509,moh731.HV0510,moh731.HV0511,moh731.HV0512,moh731.HV0513,moh731.HV0514," + " moh731.HV0101,moh731.HV0102,moh731.HV0103,moh731.HV0105,moh731.HV0106,moh731.HV0107,moh731.HV0108,moh731.HV0109,moh731.HV0110,moh731.HV0111,moh731.HV0112,moh731.HV0113,moh731.HV0114,moh731.HV0115,moh731.HV0116 " + "," + subpartnera + ".PMTCT," + subpartnera + ".ART," + subpartnera + ".PEP," + subpartnera + ".HTC,isValidated ,IFNULL(ART_highvolume,0) as ART_highvolume, IFNULL(HTC_highvolume,0) as HTC_highvolume, IFNULL(PMTCT_highvolume,0) as PMTCT_highvolume " + " FROM moh731 JOIN " + subpartnera + " ON moh731.SubPartnerID=" + subpartnera + "." + subpartnerid + " " + "JOIN district ON " + subpartnera + ".DistrictID=district.DistrictID " + "JOIN county ON county.CountyID=district.CountyID " + " WHERE moh731.Mois='" + month + "' && moh731.Annee='" + year + "'" + " ORDER BY county.County,district.DistrictNom," + subpartnera + "." + subpartnerid + ""; conn.rs = conn.st.executeQuery(getData); int valuesstartrow = 7; System.out.println("|__" + getData); while (conn.rs.next()) { counter++; county = conn.rs.getString(1); district = conn.rs.getString(2); facilityname = conn.rs.getString(3); mflcode = conn.rs.getString(4); arthv = conn.rs.getInt("ART_highvolume"); htchv = conn.rs.getInt("HTC_highvolume"); pmtcthv = conn.rs.getInt("PMTCT_highvolume"); System.out.println("@@@@@@" + arthv + "_" + htchv + "_" + pmtcthv); //ART High Volume HTC High Volume PMTCT High Volume String basicDetails = county + "@" + district + "@" + facilityname + "@" + mflcode + "@" + arthv + "@" + htchv + "@" + pmtcthv; String arrayDetails[] = basicDetails.split("@"); if (conn.rs.getInt(subpartnera + ".PMTCT") == 1) { counterPMTCT1++; XSSFRow rw2S1 = shet1.createRow(counterPMTCT1); int facilno = 0; for (int b = 0; b < arrayDetails.length; b++) { XSSFCell S3cell = rw2S1.createCell(facilno); if (b < 3) { S3cell.setCellValue(arrayDetails[b]); } else { S3cell.setCellValue(Integer.parseInt(arrayDetails[b])); } S3cell.setCellStyle(stborder); facilno++; } int pos; for (int i = valuesstartrow; i <= counterPMTCT; i++) { XSSFCell S3cell = rw2S1.createCell(i); pos = i + 1; //System.out.println("cell no 1 : "+i+" value no : "+pos); S3cell.setCellValue(conn.rs.getInt(pos - 3)); S3cell.setCellStyle(stborder); } isValidated = conn.rs.getString("isValidated"); if (isValidated.equals("1")) { isValidated = "Yes"; } else { isValidated = "No"; } XSSFCell S3cell = rw2S1.createCell(49); S3cell.setCellValue(isValidated); S3cell.setCellStyle(stborder); } if (conn.rs.getInt(subpartnera + ".ART") == 1) { counterART1++; XSSFRow rw2S2 = shet2.createRow(counterART1); int facilno = 0; for (int b = 0; b < arrayDetails.length; b++) { XSSFCell S3cell = rw2S2.createCell(facilno); if (b < 3) { S3cell.setCellValue(arrayDetails[b]); } else { S3cell.setCellValue(Integer.parseInt(arrayDetails[b])); } S3cell.setCellStyle(stborder); facilno++; } int pos; for (int i = valuesstartrow; i <= counterART; i++) { XSSFCell S3cell = rw2S2.createCell(i); pos = i + 43; System.out.println("cell no 2 : " + i + " value no : " + pos); S3cell.setCellValue(conn.rs.getInt(pos - 3)); S3cell.setCellStyle(stborder); } isValidated = conn.rs.getString("isValidated"); if (isValidated.equals("1")) { isValidated = "Yes"; } else { isValidated = "No"; } XSSFCell S3cell = rw2S2.createCell(68); S3cell.setCellValue(isValidated); S3cell.setCellStyle(stborder); } if (conn.rs.getInt(subpartnera + ".PEP") == 1) { counterPEP1++; XSSFRow rw2S3 = shet3.createRow(counterPEP1); int facilno = 0; for (int b = 0; b < arrayDetails.length; b++) { XSSFCell S3cell = rw2S3.createCell(facilno); if (b < 3) { S3cell.setCellValue(arrayDetails[b]); } else { S3cell.setCellValue(Integer.parseInt(arrayDetails[b])); } S3cell.setCellStyle(stborder); facilno++; } int pos; for (int i = valuesstartrow; i <= counterPEP; i++) { XSSFCell S3cell = rw2S3.createCell(i); pos = i + 104; //System.out.println("cell no 3 : "+i+" value no : "+pos); S3cell.setCellValue(conn.rs.getInt(pos - 3)); S3cell.setCellStyle(stborder); } isValidated = conn.rs.getString("isValidated"); if (isValidated.equals("1")) { isValidated = "Yes"; } else { isValidated = "No"; } XSSFCell S3cell = rw2S3.createCell(20); S3cell.setCellValue(isValidated); S3cell.setCellStyle(stborder); } //HTC_____________________added 2016 if (conn.rs.getInt(subpartnera + ".HTC") == 1) { counterHTC1++; XSSFRow rw2S4 = shet4.createRow(counterHTC1); int facilno = 0; for (int b = 0; b < arrayDetails.length; b++) { XSSFCell S3cell = rw2S4.createCell(facilno); if (b < 3) { S3cell.setCellValue(arrayDetails[b]); } else { S3cell.setCellValue(Integer.parseInt(arrayDetails[b])); } S3cell.setCellStyle(stborder); facilno++; } String pos; int cellpos = 0; for (int i = 1; i <= 16; i++) { if (i != 4) { cellpos++; XSSFCell S4cell = rw2S4.createCell(cellpos + 6); pos = "0" + i; if (i >= 10) { pos = "" + i; } S4cell.setCellValue(conn.rs.getInt("moh731.HV01" + pos)); S4cell.setCellStyle(stborder); } } isValidated = conn.rs.getString("isValidated"); if (isValidated.equals("1")) { isValidated = "Yes"; } else { isValidated = "No"; } XSSFCell S4cell = rw2S4.createCell(22); S4cell.setCellValue(isValidated); S4cell.setCellStyle(stborder); } System.out.println("counter : " + counter); } if (conn.st != null) { conn.st.close(); } if (conn.st1 != null) { conn.st1.close(); } // if(conn.st2!=null){conn.st2.close();} if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } // if(conn.rs2!=null){conn.rs2.close();} if (conn.conn != null) { conn.conn.close(); } IdGenerator IG = new IdGenerator(); createdOn = IG.CreatedOn(); // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=MOH731_RAW_DATA_REPORT_FOR_" + year.trim() + "(" + monthName.trim() + ")_CREATED_" + createdOn.trim() + ".xlsx"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:reports.moh731_subSection.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {/* w w w . ja va 2 s .c om*/ response.setContentType("text/html;charset=UTF-8"); //a page to get Report of all the servlets String year = "2015"; String month = "4"; String county = "1"; String form = "moh731"; if (request.getParameter("year") != null) { year = request.getParameter("year"); } if (request.getParameter("county") != null) { county = request.getParameter("county"); } if (request.getParameter("month") != null) { month = request.getParameter("month"); } if (request.getParameter("form") != null) { form = request.getParameter("form"); } String facilitywhere = ""; String countywhere = ""; String districtwhere = ""; String reporttype = ""; dbConn conn = new dbConn(); //an array to store haeder information. //the header information should appear only if a certain parameters are met //The parameters listed in here can be removed if the report type doesnt require certain parameters ArrayList Headerorgunits = new ArrayList(); Headerorgunits.add("COUNTY"); Headerorgunits.add("SUB-COUNTY"); Headerorgunits.add("FACILITY"); Headerorgunits.add("MFL CODE"); //An arralist to store a list of columns that will be selected from the database ArrayList dbcolumns = new ArrayList(); ArrayList labels = new ArrayList(); ArrayList tablename = new ArrayList(); ArrayList iscumulative = new ArrayList(); ArrayList ispercent = new ArrayList(); // ArrayList isactive=new ArrayList(); //An arralist to store a list of worksheets that will be selected from the sections ArrayList worksheets = new ArrayList(); //An arralist to store distinct worksheets. This will be derived from the the sections column ArrayList distinctsheets = new ArrayList(); String selectdistinctworksheet = "select distinct(section) from pivottable where form='" + form + "' and active='1'"; conn.rs = conn.st.executeQuery(selectdistinctworksheet); while (conn.rs.next()) { //add the name of distinct sections distinctsheets.add(conn.rs.getString(1).replace("/", "_")); } String getattribs = "select indicator,label,section,cumulative,percentage,active ,shortlabel from pivottable where form='" + form + "' order by tableid, section"; conn.rs = conn.st.executeQuery(getattribs); while (conn.rs.next()) { //add active indicators only if (conn.rs.getString("active").equals("1")) { System.out.println(conn.rs.getString("indicator") + ""); //add indicator dbcolumns.add(conn.rs.getString("indicator")); //add label if (form.equals("moh731")) { labels.add(conn.rs.getString("shortlabel") + " \n " + conn.rs.getString("label")); } else { labels.add(conn.rs.getString("label")); } //add worksheets worksheets.add(conn.rs.getString("section").replace("/", "_")); String perc = "0"; String cum = "0"; if (conn.rs.getString("cumulative") != null) { iscumulative.add(conn.rs.getString("cumulative")); } else { iscumulative.add(cum); } if (conn.rs.getString("percentage") != null) { ispercent.add(conn.rs.getString("percentage")); } else { ispercent.add(perc); } } //end of active } //end of worksheet //if String perfacilselect = "select Upper(County) as County , Upper(DistrictNom) as District , UPPER(SubPartnerNom) as facility ,CentreSanteId as mflcode , "; //-------------------------------------------------------------------------------------------- // PREPARE SELECT //-------------------------------------------------------------------------------------------- //prepare selects for (int a = 0; a < dbcolumns.size(); a++) { //if the indicator is a percent, get an avaerage if (ispercent.get(a).equals("1")) { perfacilselect += " AVG(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } else if (iscumulative.get(a).equals("1")) { perfacilselect += " " + dbcolumns.get(a) + " as " + dbcolumns.get(a); } else { perfacilselect += " SUM(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } //if the item is not the last, append a comma if (a < dbcolumns.size() - 1) { perfacilselect += " ,"; } } //------------------------------------------------------------------------------------ // FROM //------------------------------------------------------------------------------------ perfacilselect += " from " + form + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on " + form + ".SubPartnerID = subpartnera.SubPartnerID "; //------------------------------------------------------------------------------------------ // WHERE //------------------------------------------------------------------------------------------ //----------------------------------------------------------------------------------------- //GROUP BY //---------------------------------------------------------------------------------------- perfacilselect += " group by subpartnera.SubPartnerID"; System.out.println(perfacilselect); //______________________________________________________________________________________ // NOW CREATE THE WORKSHEETS //______________________________________________________________________________________ XSSFWorkbook wb = new XSSFWorkbook(); //______________________________________________________________________________________ //______________________________________________________________________________________ XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); for (int b = 0; b < distinctsheets.size(); b++) { XSSFSheet shet = wb.createSheet(distinctsheets.get(b).toString().toUpperCase()); //create headers for that worksheet XSSFRow rw = shet.createRow(1); int headercellpos = 0; //create the orgunit header eg COUNTY | SUBCOUNTY | FACILITY for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(Headerorgunits.get(e).toString()); cell0.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(e, 6000); } //create the indicators header eg HV0101 | HIV 09676 | TOTAL for (int c = 0; c < dbcolumns.size(); c++) { //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(b))) { shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(labels.get(c).toString()); cell0.setCellStyle(stylex); headercellpos++; } //end of comparing if } //end of for loop } conn.rs = conn.st.executeQuery(perfacilselect); String sectioncopy = ""; int sheetpos = 0; int rowpos = 2; while (conn.rs.next()) { //-----------------INSIDE THE DATA FORM--------------------------------- //if the section changes, change the position of the worksheet too //also, reset the position counter to begin from 2 again. XSSFSheet shet = null; // if(--!sectioncopy.equals(shet)){} for (int g = 0; g < distinctsheets.size(); g++) { shet = wb.getSheetAt(g); int colpos = 0; //the fourth cell should XSSFRow rw = shet.createRow(rowpos); for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getString(e + 1)); cell0.setCellStyle(style2); colpos++; } //_________________________________________________________________ //VALUES //_________________________________________________________________ //create the indicators values eg 90 | 45 | 356 for (int c = 0; c < dbcolumns.size(); c++) { //get the section of the current dbcolumn //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(g))) { XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getString(dbcolumns.get(c).toString())); cell0.setCellStyle(stborder); colpos++; } //end of comparing if } //end of for loop } rowpos++; } ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=" + form.toUpperCase() + "_REPORT.xlsx"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(allStaticReportsMonthly.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.new711report.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {//from w w w . j a v a 2 s.c om response.setContentType("text/html;charset=UTF-8"); //a page to get Report of all the servlets String year = "2016"; String month = "4"; String county = ""; String form = "moh711_new"; if (request.getParameter("year") != null) { year = request.getParameter("year"); } if (request.getParameter("county") != null) { county = request.getParameter("county"); } if (request.getParameter("month") != null) { month = request.getParameter("month"); } if (request.getParameter("form") != null) { form = request.getParameter("form"); } String pivotform = form; if (form.equalsIgnoreCase("MOH 731")) { form = "MOH731"; } if (form.equalsIgnoreCase("MOH 711 (New)")) { form = "moh711_new"; } String facilitywhere = ""; String yearwhere = ""; String monthwhere = ""; String countywhere = ""; String districtwhere = ""; String reporttype = ""; if (!year.equals("")) { yearwhere = " and Annee = '" + year + "'"; } if (!county.equals("")) { countywhere = " and countyid = '" + county + "'"; } if (!month.equals("")) { monthwhere = " and Mois = '" + month + "'"; } dbConn conn = new dbConn(); //an array to store haeder information. //the header information should appear only if a certain parameters are met //The parameters listed in here can be removed if the report type doesnt require certain parameters ArrayList Headerorgunits = new ArrayList(); Headerorgunits.add("COUNTY"); Headerorgunits.add("SUB-COUNTY"); Headerorgunits.add("FACILITY"); Headerorgunits.add("MFL CODE"); //An arralist to store a list of columns that will be selected from the database ArrayList dbcolumns = new ArrayList(); ArrayList labels = new ArrayList(); ArrayList tablename = new ArrayList(); ArrayList iscumulative = new ArrayList(); ArrayList ispercent = new ArrayList(); // ArrayList isactive=new ArrayList(); //An arralist to store a list of worksheets that will be selected from the sections and the respective service area to determine the facilities whose data will appear in that sheet ArrayList worksheets = new ArrayList(); //An arralist to store distinct worksheets. This will be derived from the the sections column ArrayList distinctsheets = new ArrayList(); ArrayList distinctservicearea = new ArrayList(); String selectdistinctworksheet = "select section,servicearea from pivottable where form='" + form.replace("_", "") + "' and active='1' group by section order by order_per_form"; conn.rs = conn.st.executeQuery(selectdistinctworksheet); while (conn.rs.next()) { //add the name of distinct sections distinctsheets.add(conn.rs.getString(1).replace("/", "_")); String servicearea = " 2=2 "; if (conn.rs.getString(2) != null) { servicearea = " " + conn.rs.getString(2) + "=1"; } distinctservicearea.add(servicearea); } String getattribs = "select indicator,label,section,cumulative,percentage,active ,shortlabel from pivottable where form='" + form.replace("_", "") + "' order by order_per_form, section"; conn.rs = conn.st.executeQuery(getattribs); while (conn.rs.next()) { //add active indicators only if (conn.rs.getString("active").equals("1")) { System.out.println(conn.rs.getString("indicator") + ""); //add indicator dbcolumns.add(conn.rs.getString("indicator")); //add label if (form.equals("moh731")) { labels.add(conn.rs.getString("shortlabel") + " \n " + conn.rs.getString("label")); } else { labels.add(conn.rs.getString("label")); } //add worksheets worksheets.add(conn.rs.getString("section").replace("/", "_")); String perc = "0"; String cum = "0"; if (conn.rs.getString("cumulative") != null) { iscumulative.add(conn.rs.getString("cumulative")); } else { iscumulative.add(cum); } if (conn.rs.getString("percentage") != null) { ispercent.add(conn.rs.getString("percentage")); } else { ispercent.add(perc); } } //end of active } //end of pivot table active //if String perfacilselect = "select Upper(County) as County , Upper(DistrictNom) as District , UPPER(SubPartnerNom) as facility ,CentreSanteId as mflcode , district.CountyID as countyid , "; //-------------------------------------------------------------------------------------------- // PREPARE SELECT //-------------------------------------------------------------------------------------------- //prepare selects for (int a = 0; a < dbcolumns.size(); a++) { //if the indicator is a percent, get an avaerage if (ispercent.get(a).equals("1")) { perfacilselect += " AVG(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } else if (iscumulative.get(a).equals("1")) { perfacilselect += " " + dbcolumns.get(a) + " as " + dbcolumns.get(a); } else { perfacilselect += " SUM(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } //if the item is not the last, append a comma //if(a<dbcolumns.size()-1){ perfacilselect += " ,"; // } } //------------------------------------------------------------------------------------ // FROM //------------------------------------------------------------------------------------ perfacilselect += " isValidated as Form_Validated from " + form + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on " + form + ".SubPartnerID = subpartnera.SubPartnerID "; //------------------------------------------------------------------------------------------ // WHERE //------------------------------------------------------------------------------------------ perfacilselect += " where 1=1 " + monthwhere + yearwhere; //----------------------------------------------------------------------------------------- //GROUP BY //---------------------------------------------------------------------------------------- perfacilselect += " group by subpartnera.SubPartnerID"; //System.out.println(perfacilselect); //______________________________________________________________________________________ // NOW CREATE THE WORKSHEETS //______________________________________________________________________________________ XSSFWorkbook wb = new XSSFWorkbook(); //______________________________________________________________________________________ //______________________________________________________________________________________ XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); for (int b = 0; b < distinctsheets.size(); b++) { XSSFSheet shet = wb.createSheet(distinctsheets.get(b).toString().toUpperCase()); //create headers for that worksheet XSSFRow rw = shet.createRow(1); int headercellpos = 0; //create the orgunit header eg COUNTY | SUBCOUNTY | FACILITY for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(Headerorgunits.get(e).toString()); cell0.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(e, 6000); } //create the indicators header eg HV0101 | HIV 09676 | TOTAL for (int c = 0; c < dbcolumns.size(); c++) { //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(b))) { shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(labels.get(c).toString()); cell0.setCellStyle(stylex); headercellpos++; } //end of comparing if } //end of for loop //create is validated header shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue("Form Validated ?"); cell0.setCellStyle(stylex); headercellpos++; } String sectioncopy = ""; int sheetpos = 0; int rowpos = 2; //-----------------INSIDE THE DATA FORM--------------------------------- //if the section changes, change the position of the worksheet too //also, reset the position counter to begin from 2 again. XSSFSheet shet = null; // if(--!sectioncopy.equals(shet)){} //create the org unit data values e.g BARINGO | BARINGO CENTRAL |KABARNET DISTRICT HOSPITAL | MFL CODE for (int g = 0; g < distinctsheets.size(); g++) { shet = wb.getSheetAt(g); int colpos = 0; String finalquery = perfacilselect.replace("1=1", distinctservicearea.get(g).toString()); System.out.println("" + finalquery); conn.rs = conn.st.executeQuery(finalquery); while (conn.rs.next()) { //the fourth cell should XSSFRow rw = shet.createRow(rowpos); for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(colpos); //for mfl code, last header, print integers if (Headerorgunits.get(e).toString().equals("MFL CODE")) { cell0.setCellValue(conn.rs.getInt(e + 1)); } else { cell0.setCellValue(conn.rs.getString(e + 1)); } cell0.setCellStyle(style2); colpos++; } //_________________________________________________________________ //VALUES //_________________________________________________________________ //create the indicators values eg 90 | 45 | 356 for (int c = 0; c < dbcolumns.size(); c++) { //get the section of the current dbcolumn //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(g))) { XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getInt(dbcolumns.get(c).toString())); cell0.setCellStyle(stborder); colpos++; } //end of comparing if } //end of for loop String isvalidated = "Yes"; if (conn.rs.getString("Form_Validated").equals("0")) { isvalidated = "No"; } XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(isvalidated); cell0.setCellStyle(stborder); colpos++; rowpos++; colpos = 0; } rowpos = 2; } IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); System.out.println("" + form.toUpperCase().trim() + "_REPORT_FOR_" + year.trim() + "(" + month.trim() + ")_CREATED_" + createdOn.trim() + ".xlsx"); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=" + form.toUpperCase().trim() + "_REPORT_FOR_" + year.trim() + "(" + month.trim() + ")_CREATED_" + createdOn.trim() + ".xlsx"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(allStaticReportsMonthly.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.Reached_OthersMessages.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException, InvalidFormatException { session = request.getSession();// ww w . java 2s. co m dbConn conn = new dbConn(); ouputyear = outputMonth = ""; if (session.getAttribute("PepfarYear") != null) { year = Integer.parseInt(session.getAttribute("PepfarYear").toString()); quarter = Integer.parseInt(session.getAttribute("period").toString()); // year=2015; // quarter=2; System.out.println("dates are : " + year + " quarter : " + quarter); if (quarter == 4) { reportYear = year - 1; startmonth = "10"; endMonth = "12"; prevReportYear = reportYear; prevReportMonth = "09"; } else { reportYear = year; if (quarter == 1) { startmonth = "01"; endMonth = "03"; prevReportYear = reportYear - 1; prevReportMonth = "12"; } if (quarter == 2) { startmonth = "04"; endMonth = "06"; prevReportYear = reportYear; prevReportMonth = "03"; } if (quarter == 3) { startmonth = "07"; endMonth = "09"; prevReportYear = reportYear; prevReportMonth = "06"; } } startDate = "" + startmonth + "/01/" + reportYear + ""; endDate = "" + endMonth + "/31/" + reportYear + ""; startDate1 = "" + reportYear + "-" + startmonth + "-01"; endDate1 = "" + reportYear + "-" + endMonth + "-31"; endYearMonth = prevReportYear + "" + prevReportMonth; position = 1; // ouputyear=""+reportYear; if (quarter == 1) { outputMonth = "Jan_March_" + reportYear; } if (quarter == 2) { outputMonth = "Apr_Jun_" + reportYear; } if (quarter == 3) { outputMonth = "July_Sept_" + reportYear; } if (quarter == 4) { outputMonth = "Oct_Dec_" + reportYear; } String reportHeader[] = ("COUNTY NAME ,PARTNER NAME,DISTRICT NAME,DIC NAME, GROUP NAME,CLIENT NAME , AGE BRACKET, GENDER,YEAR,MONTH") .split(","); // COPY FILE TO BE WRITTEN TO Path original = Paths.get(getServletContext().getRealPath("/OTHER_MESSAGES.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/OTHER_MESSAGES_1.xlsm")); //new file System.out.println("origin : " + original + " destination : " + destination); try { Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING); System.out.println("file copied----------------"); } catch (IOException x) { //catch all for IO problems System.out.println("fine not copied"); } String allpath = getServletContext().getRealPath("/OTHER_MESSAGES_1.xlsm"); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb; OPCPackage pkg = OPCPackage.open(allpath); wb = new XSSFWorkbook(pkg); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ // HSSFWorkbook wb=new HSSFWorkbook(); XSSFSheet shet1 = wb.getSheet("Sheet1"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); for (int i = 0; i <= reportHeader.length; i++) { shet1.setColumnWidth(i, 4000); } XSSFCellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); XSSFCell cell; XSSFRow rw0 = shet1.createRow(0); rw0.setHeightInPoints(30); rw0.setRowStyle(style2); for (int i = 0; i <= (reportHeader.length - 1); i++) { cell = rw0.createCell(i); cell.setCellValue(reportHeader[i]); cell.setCellStyle(stylex); } String query = "SELECT CLIENT, " + "" + "if( (dateRegister BETWEEN '" + startDate1 + "' AND '" + endDate1 + "'),dateRegister,dateAdherence) REPORTDATE," + " " + "dateRegister,dateAdherence, countyName, partnerName, districtName, AGEBRACKET, SEX,fname,mname,lname,GROUPNAME,DIC FROM (" + "SELECT DISTINCT(tempData.clientID) as CLIENT,if( (reg2Date BETWEEN '" + startDate1 + "' AND '" + endDate1 + "'),reg2Date,'0') dateRegister,if( (AdherenceDate BETWEEN '" + startDate1 + "' AND '" + endDate1 + "'),AdherenceDate,'0') dateAdherence, countyName, partnerName, districtName, AGEBRACKET, SEX,fname,mname,lname,GROUPNAME,DIC FROM (" + "SELECT DISTINCT(personal_information.client_id) as clientID,STR_TO_DATE(register2.date,'%m/%d/%Y') as reg2Date,STR_TO_DATE(adherence.date_of_session,'%m/%d/%Y') as AdherenceDate," + " CONCAT(personal_information.completionyear,personal_information.completionmonth) AS YEARMONTH," + "county.county_name AS countyName,partner.partner_name AS partnerName,district.district_name AS districtName," + "CASE " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9' " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14' " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19' " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24' " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49' " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above' " + " ELSE 'NO DATE OF BIRTH'" + " END AS AGEBRACKET," + " CASE " + "when personal_information.gender LIKE 'Female' THEN 'F' " + "when personal_information.gender LIKE 'Male' THEN 'M' " + "ELSE 'NO SEX' " + "END AS SEX,personal_information.fname as fname,personal_information.mname as mname ,personal_information.lname as lname," + "groups.group_name as GROUPNAME,dic.dic_name as DIC " + " FROM personal_information " + "LEFT JOIN register2 ON personal_information.client_id=register2.client_id " + "LEFT JOIN adherence ON personal_information.client_id=adherence.client_id " + "LEFT JOIN groups ON personal_information.group_id=groups.group_id " + "LEFT JOIN dic ON personal_information.dic_id=dic.dic_id " + "LEFT JOIN district ON personal_information.district_id=district.district_id " + "LEFT JOIN partner ON personal_information.partner_id=partner.partner_id " + "LEFT JOIN county ON district.county_id=county.county_id " + "WHERE personal_information.completionyear>0 " + "&& (register2.datekey BETWEEN '" + startDate1.replace("-", "") + "' AND '" + endDate1.replace("-", "") + "' || STR_TO_DATE(adherence.date_of_session,'%m/%d/%Y') BETWEEN STR_TO_DATE('" + startDate + "','%m/%d/%Y') AND STR_TO_DATE('" + endDate + "','%m/%d/%Y'))" + " HAVING YEARMONTH<=" + endYearMonth + ") AS tempData GROUP BY tempData.clientID) AS finalTable "; System.out.println(query); conn.rs = conn.st.executeQuery(query); while (conn.rs.next()) { countyName = conn.rs.getString(5); partnerName = conn.rs.getString(6); districtName = conn.rs.getString(7); reportDate = conn.rs.getString(2); ageBracket = conn.rs.getString(8); gender = conn.rs.getString(9); fname = conn.rs.getString(10); mname = conn.rs.getString(11); lname = conn.rs.getString(12); if (conn.rs.getString(13) != null) { groupName = conn.rs.getString(13); } else { groupName = "Individual"; } if (conn.rs.getString(14) != null) { groupName = conn.rs.getString(14); } else { dic_name = "NO DIC"; } if (mname.equals(lname)) { mname = ""; } fullName = fname + " " + mname + " " + lname; String dateSplit[] = reportDate.split("-"); if (dateSplit[1].equals("01")) { month = "Jan"; } if (dateSplit[1].equals("02")) { month = "Feb"; } if (dateSplit[1].equals("03")) { month = "Mar"; } if (dateSplit[1].equals("04")) { month = "Apr"; } if (dateSplit[1].equals("05")) { month = "May"; } if (dateSplit[1].equals("06")) { month = "Jun"; } if (dateSplit[1].equals("07")) { month = "Jul"; } if (dateSplit[1].equals("08")) { month = "Aug"; } if (dateSplit[1].equals("09")) { month = "Sept"; } if (dateSplit[1].equals("10")) { month = "Oct"; } if (dateSplit[1].equals("11")) { month = "Nov"; } if (dateSplit[1].equals("12")) { month = "Dec"; } year2 = dateSplit[0]; String data[] = (countyName + "," + partnerName + "," + districtName + "," + dic_name + "," + groupName + "," + fullName + "," + ageBracket + "," + gender + "," + year2 + "," + month) .split(","); XSSFRow rw1 = shet1.createRow(position); rw1.setHeightInPoints(25); rw1.setRowStyle(style2); for (int i = 0; i <= (data.length - 1); i++) { cell = rw1.createCell(i); cell.setCellValue(data[i]); cell.setCellStyle(styleBorder); } position++; } IdGenerator CRT = new IdGenerator(); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_REACHED_WITH_OTHER_MESSAGES_REPORT_FOR_" + outputMonth + "_CREATED_ON_" + CRT.timestamp() + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); } else { session.setAttribute("kePMSError", "<font color=\"red\">Error : Please try again.</font>"); response.sendRedirect("kePMS.jsp"); } }
From source file:ru.jeene.zapretparser.controller.XLSXReportController.java
public void WriteReport(FullReport rep, String timestamp_csv) { try (FileInputStream inp = new FileInputStream(shab_name)) { XSSFWorkbook wb = new XSSFWorkbook(inp); // Declare XSSF WorkBook XSSFSheet sheet = wb.getSheet(" "); XSSFCellStyle cs1 = wb.createCellStyle(); //cs1.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); cs1.setBorderBottom(XSSFCellStyle.BORDER_THIN); cs1.setBorderTop(XSSFCellStyle.BORDER_THIN); cs1.setBorderRight(XSSFCellStyle.BORDER_THIN); cs1.setBorderLeft(XSSFCellStyle.BORDER_THIN); cs1.setAlignment(XSSFCellStyle.ALIGN_CENTER); cs1.setWrapText(true);/*from w ww . ja v a2 s . c om*/ cs1.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); XSSFFont f = wb.createFont(); f.setBold(false); f.setFontName("Times New Roman"); f.setFontHeightInPoints((short) 14); cs1.setFont(f); int cnt = 0; XSSFRow row; XSSFCell cell; for (Model_FullReport m : rep.getList()) { int cnt_cell = 0; row = sheet.getRow(t1_start - 1 + cnt); if (row == null) { row = sheet.createRow(t1_start - 1 + cnt); } //? URL cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(m.getElement().getUrl()); cell.setCellStyle(cs1); cnt_cell++; // cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(m.getElement().getOrg()); cell.setCellStyle(cs1); cnt_cell++; //? cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(m.getElement().getDoc()); cell.setCellStyle(cs1); cnt_cell++; // cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(m.getElement().getDate()); cell.setCellStyle(cs1); cnt_cell++; // cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(m.getResult().name()); cell.setCellStyle(cs1); cnt_cell++; // (?) cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(m.getResult().getDesc()); cell.setCellStyle(cs1); cnt_cell++; // () cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(m.getResult().getCode()); cell.setCellStyle(cs1); cnt_cell++; cnt++; } // row = sheet.getRow(0); cell = row.getCell(0); cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(MAIN_ZAG_TEMPL + timestamp_csv); // HashMap<ResponseResult, Model_NumberReport> map = rep.reportCountBytype(); // ? sheet = wb.getSheet(""); // cnt = 0; for (Map.Entry<ResponseResult, Model_NumberReport> entry : map.entrySet()) { ResponseResult key = entry.getKey(); Model_NumberReport value = entry.getValue(); int cnt_cell = 0; row = sheet.getRow(t0_start - 1 + cnt); if (row == null) { row = sheet.createRow(t0_start - 1 + cnt); } /*// cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(key.name()); cell.setCellStyle(cs1); cnt_cell++;*/ // (?) cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(key.getDesc()); cell.setCellStyle(cs1); cnt_cell++; /*// () cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(key.getCode()); cell.setCellStyle(cs1); cnt_cell++;*/ // (?) cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(value.getNumber()); cell.setCellStyle(cs1); cnt_cell++; // () cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(FormatUtils.FormatDoubleD(value.getPercent())); cell.setCellStyle(cs1); cnt_cell++; cnt++; } // row = sheet.getRow(0); cell = row.getCell(0); cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(MAIN_ZAG_TEMPL + timestamp_csv); // HTTP map = rep.reportCountBytypeHTTP(); // ? sheet = wb.getSheet(" HTTP"); // cnt = 0; for (Map.Entry<ResponseResult, Model_NumberReport> entry : map.entrySet()) { ResponseResult key = entry.getKey(); Model_NumberReport value = entry.getValue(); int cnt_cell = 0; row = sheet.getRow(t0_start - 1 + cnt); if (row == null) { row = sheet.createRow(t0_start - 1 + cnt); } /*// cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(key.name()); cell.setCellStyle(cs1); cnt_cell++;*/ // (?) cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(key.getDesc()); cell.setCellStyle(cs1); cnt_cell++; /*// () cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(key.getCode()); cell.setCellStyle(cs1); cnt_cell++;*/ // (?) cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(value.getNumber()); cell.setCellStyle(cs1); cnt_cell++; // () cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(FormatUtils.FormatDoubleD(value.getPercent())); cell.setCellStyle(cs1); cnt_cell++; cnt++; } // row = sheet.getRow(0); cell = row.getCell(0); cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(MAIN_ZAG_TEMPL + timestamp_csv); // HTTPS map = rep.reportCountBytypeHTTPS(); // ? sheet = wb.getSheet(" HTTPS"); // cnt = 0; for (Map.Entry<ResponseResult, Model_NumberReport> entry : map.entrySet()) { ResponseResult key = entry.getKey(); Model_NumberReport value = entry.getValue(); int cnt_cell = 0; row = sheet.getRow(t0_start - 1 + cnt); if (row == null) { row = sheet.createRow(t0_start - 1 + cnt); } /*// cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(key.name()); cell.setCellStyle(cs1); cnt_cell++;*/ // (?) cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(key.getDesc()); cell.setCellStyle(cs1); cnt_cell++; /*// () cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(key.getCode()); cell.setCellStyle(cs1); cnt_cell++;*/ // (?) cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(value.getNumber()); cell.setCellStyle(cs1); cnt_cell++; // () cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(FormatUtils.FormatDoubleD(value.getPercent())); cell.setCellStyle(cs1); cnt_cell++; cnt++; } // row = sheet.getRow(0); cell = row.getCell(0); cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(MAIN_ZAG_TEMPL + timestamp_csv); //? String tmp_out = StringUtils.replaceAll(report_name, "!dt!", DateUtils.DateToString(new Date(System.currentTimeMillis()), "ddMMyyyy_Hms")); try (FileOutputStream out = new FileOutputStream(tmp_out)) { wb.write(out); logger.info("Report file " + tmp_out + " created"); } } catch (Exception ex) { logger.error(ex); } }