List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setFillPattern
@Override public void setFillPattern(FillPatternType fp)
From source file:reports.verificationreports.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();/* w w w . ja v a 2s .c o m*/ dbConn conn = new dbConn(); allFacilities.clear(); year = Integer.parseInt(request.getParameter("year")); reportDuration = request.getParameter("reportDuration"); String headerART[] = "County,Sub County,Health Facility,MFL Code,Type of support,Numerator,<1,1-4Y,5-14Y,15-19Y,20+Y,<1,1-4Y,5-14Y,15-19Y,20+Y,Numerator,<1,1-4Y,5-9Y,10-14Y,15-19Y,20-24Y,25-49Y,50+Y,<1,1-4Y,5-9Y,10-14Y,15-19Y,20-24Y,25-49Y,50+Y" .split(","); String headerCARE[] = "County,Sub County,Health Facility,MFL Code,Type of support,Numerator,<1,1-4Y,5-9Y,10-14Y,15-19Y,20-24Y,25-49Y,50+Y,<1,1-4Y,5-9Y,10-14Y,15-19Y,20-24Y,25-49Y,50+Y,NUMERATOR,<1,1-4Y,5-9Y,10-14Y,15-19Y,20-24Y,25-49Y,50+Y,<1,1-4Y,5-9Y,10-14Y,15-19Y,20-24Y,25-49Y,50+Y" .split(","); String headerPMTCT[] = "County,Sub County,Health Facility,MFL Code,Type of support,Numerator,Denominator,HIV-infected:Linked to ART,HIV-infected: Not linked to ART,HIV-infected : Unknown link,HIV-uninfected:Not beastfeeding,HIV-uninfected: Still breastfeeeding,HIV-uninfected:Breastfeeding unknown,Other outcomes: In care but not test done, Other outcomes:Lost to follow up,Other outcomes : Died,Other outcomes:Transferred out,Numerator,Denominator,Life-long ART:New,Life-long ART: Already on treatment at the beginning of the current pregnancy,Maternal Triple-Drug ARV,Maternal AZT,Single-dose nevirapine(with or without tail),Numerator,Infants who received a virologic test within 2 months of birth, Infants who received their first virologic HIV test between 2 and 12 months of age,Infants with a postive virologic test results within 2 months of birth, Infants with a postive virologic test resultsbetween 2 and 12 months of age,Numerator,Known postive at entry,New postives,Denominator,Numerator " .split(","); // year=2015; // reportDuration="4"; String facilityIds1 = ""; period = ""; prevYear = year - 1; maxYearMonth = 0; facilityIds = "("; facilityIds1 = "("; artpos = carepos = pmtctpos = 0; // GET REPORT DURATION============================================ if (reportDuration.equals("1")) { duration = " moh731.yearmonth BETWEEN " + prevYear + "10 AND " + year + "09"; period = "DATIM ANNUAL DATA REPORT FOR PEPFAR YEAR : " + year; } else if (reportDuration.equals("2")) { semi_annual = request.getParameter("semi_annual"); // semi_annual="2"; if (semi_annual.equals("1")) { duration = " moh731.yearmonth BETWEEN " + prevYear + "10 AND " + year + "03"; period = "DATIM SEMI - ANNUAL DATA REPORT FOR : OCT " + prevYear + " to MARCH " + year; } else { duration = " moh731.yearmonth BETWEEN " + year + "04 AND " + year + "09"; period = "DATIM SEMI - ANNUAL DATA REPORT FOR : APRIL " + year + " to SEPT " + year; } } else if (reportDuration.equals("3")) { String startMonth, endMonth; quarter = request.getParameter("quarter"); // quarter="3"; String getMonths = "SELECT months,name FROM quarter WHERE id='" + quarter + "'"; conn.rs = conn.st.executeQuery(getMonths); if (conn.rs.next() == true) { String months[] = conn.rs.getString(1).split(","); startMonth = months[0]; endMonth = months[2]; if (quarter.equals("1")) { duration = " moh731.yearmonth BETWEEN " + prevYear + "" + startMonth + " AND " + prevYear + "" + endMonth; period = "DATIM QUARTERLY DATA REPORT FOR : " + conn.rs.getString(2).replace("-", " " + prevYear + " TO ") + " " + prevYear + ""; } else { duration = " moh731.yearmonth BETWEEN " + year + "" + startMonth + " AND " + year + "" + endMonth; period = "DATIM QUARTERLY DATA REPORT FOR : " + conn.rs.getString(2).replace("-", " " + year + " TO ") + " " + year + ""; } } } else if (reportDuration.equals("4")) { month = Integer.parseInt(request.getParameter("month")); // month=5; String getMonthName = "SELECT name FROM month WHERE id='" + month + "'"; conn.rs = conn.st.executeQuery(getMonthName); if (conn.rs.next() == true) { if (month >= 10) { duration = " moh731.yearmonth=" + prevYear + "" + month; period = "DATIM MONTHLY DATA REPORT FOR : " + conn.rs.getString(1) + "(" + prevYear + ")"; } else { duration = " moh731.yearmonth=" + year + "0" + month; period = "DATIM MONTHLY DATA REPORT FOR : " + conn.rs.getString(1) + "(" + year + ")"; } } } else { duration = ""; } if (request.getParameter("subcounty") != null && !request.getParameter("subcounty").equals("")) { String subcounty = request.getParameter("subcounty"); String getDist = "SELECT subpartnera.SubPartnerID FROM subpartnera " + "JOIN district ON subpartnera.DistrictID=district.DistrictID " + "WHERE district.DistrictID='" + subcounty + "'"; conn.rs = conn.st.executeQuery(getDist); while (conn.rs.next()) { allFacilities.add(conn.rs.getString(1)); facilityIds += " moh731.SubPartnerID='" + conn.rs.getString(1) + "' || "; facilityIds1 += " moh711.SubPartnerID='" + conn.rs.getString(1) + "' || "; } facilityIds = facilityIds.substring(0, facilityIds.length() - 3); facilityIds += ") && "; facilityIds1 = facilityIds1.substring(0, facilityIds1.length() - 3); facilityIds1 += ") && "; } else { if (request.getParameter("county") != null && !request.getParameter("county").equals("")) { String county = request.getParameter("county"); String getCounty = "SELECT subpartnera.SubPartnerID FROM subpartnera " + "JOIN district ON subpartnera.DistrictID=district.DistrictID " + "JOIN county ON district.CountyID=county.CountyID WHERE county.CountyID='" + county + "'"; conn.rs = conn.st.executeQuery(getCounty); while (conn.rs.next()) { allFacilities.add(conn.rs.getString(1)); facilityIds += " moh731.SubPartnerID='" + conn.rs.getString(1) + "' || "; facilityIds1 += " moh711.SubPartnerID='" + conn.rs.getString(1) + "' || "; } facilityIds = facilityIds.substring(0, facilityIds.length() - 3); facilityIds += ") && "; facilityIds1 = facilityIds1.substring(0, facilityIds1.length() - 3); facilityIds1 += ") && "; } else { facilityIds = ""; facilityIds1 = ""; } } System.out.println("period is : " + period); // GET FACILITIES TO OUTPUT................................. mflcode = countyName = districtName = facilityName = ""; // facilityId=request.getParameter("facility"); // facilityIds="403"; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet("ART"); HSSFSheet shet2 = wb.createSheet("CARE"); HSSFSheet shet3 = wb.createSheet("HTC "); HSSFSheet shetPMTCT = wb.createSheet("PMTCT"); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); stborder.setWrapText(true); // for the red color HSSFCellStyle redstyle = wb.createCellStyle(); redstyle.setFillForegroundColor(HSSFColor.RED.index); redstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); redstyle.setBorderTop(HSSFCellStyle.BORDER_THIN); redstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); redstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); redstyle.setBorderRight(HSSFCellStyle.BORDER_THIN); redstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); redstyle.setWrapText(true); // HSSFCellStyle stylex = wb.createCellStyle(); //stylex.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); //stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); // stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); // stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); // stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); // stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); // //HSSFFont fontx = wb.createFont(); //fontx.setColor(HSSFColor.DARK_BLUE.index); //stylex.setFont(fontx); //stylex.setWrapText(true); // HSSFCellStyle stylemainHeader = wb.createCellStyle(); //stylemainHeader.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); //stylemainHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // stylemainHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); // stylemainHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); // stylemainHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); // stylemainHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); // stylemainHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); // stylemainHeader.setWrapText(true); HSSFCellStyle styleHeader = wb.createCellStyle(); styleHeader.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); styleHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleHeader.setWrapText(true); HSSFCellStyle styleminiHeader = wb.createCellStyle(); styleminiHeader.setFillForegroundColor(HSSFColor.ORCHID.index); styleminiHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleminiHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); styleminiHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleminiHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleminiHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); styleminiHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleminiHeader.setWrapText(true); HSSFFont fontHeader = wb.createFont(); fontHeader.setColor(HSSFColor.DARK_BLUE.index); styleHeader.setFont(fontHeader); styleHeader.setWrapText(true); for (int i = 3; i <= headerPMTCT.length; i++) { shetPMTCT.setColumnWidth(i, 4000); } for (int i = 0; i <= 1; i++) { shetPMTCT.setColumnWidth(i, 5000); } shetPMTCT.setColumnWidth(2, 8000); for (int i = 5; i <= 16; i++) { shetPMTCT.setColumnWidth(i, 4000); } for (int i = 3; i <= 33; i++) { shet1.setColumnWidth(i, 2000); } for (int i = 0; i <= 1; i++) { shet1.setColumnWidth(i, 5000); } shet1.setColumnWidth(2, 8000); for (int i = 3; i <= 33; i++) { shet2.setColumnWidth(i, 2000); } // for sheet3 for (int i = 3; i <= 42; i++) { shet3.setColumnWidth(i, 2000); } for (int i = 0; i <= 1; i++) { shet2.setColumnWidth(i, 5000); } for (int i = 0; i <= 1; i++) { shet3.setColumnWidth(i, 5000); } shet2.setColumnWidth(2, 8000); shet1.setColumnWidth(5, 3500); shet1.setColumnWidth(16, 3500); shet2.setColumnWidth(5, 3500); shet2.setColumnWidth(22, 3500); shet3.setColumnWidth(7, 3500); shet3.setColumnWidth(25, 3500); // ART AND CARE HEADERS============================================================================================ HSSFRow rw00shet1 = shet1.createRow(1); rw00shet1.setHeightInPoints(30); HSSFRow rw00shet2 = shet2.createRow(1); rw00shet2.setHeightInPoints(30); HSSFCell c011; for (int j = 0; j < headerART.length; j++) { c011 = rw00shet1.createCell(j); c011.setCellStyle(styleHeader); } for (int j = 0; j < headerCARE.length; j++) { c011 = rw00shet2.createCell(j); c011.setCellStyle(styleHeader); } c011 = rw00shet1.getCell(0); c011.setCellValue(period); c011 = rw00shet1.getCell(5); c011.setCellValue("CURRENT ON ART"); c011 = rw00shet1.getCell(16); c011.setCellValue("NEW ON ART"); shet1.addMergedRegion(new CellRangeAddress(1, 1, 5, 15)); shet1.addMergedRegion(new CellRangeAddress(1, 1, 16, 32)); c011 = rw00shet2.getCell(0); c011.setCellValue(period); c011 = rw00shet2.getCell(5); c011.setCellValue("CURRENTLY ON CARE"); c011 = rw00shet2.getCell(22); c011.setCellValue("NEW ON CARE"); shet2.addMergedRegion(new CellRangeAddress(1, 1, 5, 21)); shet2.addMergedRegion(new CellRangeAddress(1, 1, 22, 38)); HSSFRow rw0shet1 = shet1.createRow(2); rw0shet1.setHeightInPoints(30); HSSFRow rw0shet2 = shet2.createRow(2); rw0shet2.setHeightInPoints(30); HSSFCell c001; for (int j = 0; j < headerART.length; j++) { c001 = rw0shet1.createCell(j); c001.setCellStyle(styleHeader); } for (int j = 0; j < headerCARE.length; j++) { c001 = rw0shet2.createCell(j); c001.setCellStyle(styleHeader); } c001 = rw0shet1.getCell(5); c001.setCellValue("NUMERATOR"); c001 = rw0shet1.getCell(16); c001.setCellValue("NUMERATOR"); c001 = rw0shet1.getCell(6); c001.setCellValue("FEMALE"); c001 = rw0shet1.getCell(11); c001.setCellValue("MALE"); c001 = rw0shet1.getCell(17); c001.setCellValue("FEMALE"); c001 = rw0shet1.getCell(25); c001.setCellValue("MALE"); shet1.addMergedRegion(new CellRangeAddress(2, 2, 6, 10)); shet1.addMergedRegion(new CellRangeAddress(2, 2, 11, 15)); shet1.addMergedRegion(new CellRangeAddress(2, 2, 17, 24)); shet1.addMergedRegion(new CellRangeAddress(2, 2, 25, 32)); c001 = rw0shet2.getCell(5); c001.setCellValue("NUMERATOR"); c001 = rw0shet2.getCell(22); c001.setCellValue("NUMERATOR"); c001 = rw0shet2.getCell(6); c001.setCellValue("FEMALE"); c001 = rw0shet2.getCell(14); c001.setCellValue("MALE"); c001 = rw0shet2.getCell(23); c001.setCellValue("FEMALE"); c001 = rw0shet2.getCell(31); c001.setCellValue("MALE"); shet2.addMergedRegion(new CellRangeAddress(2, 2, 6, 13)); shet2.addMergedRegion(new CellRangeAddress(2, 2, 14, 21)); shet2.addMergedRegion(new CellRangeAddress(2, 2, 23, 30)); shet2.addMergedRegion(new CellRangeAddress(2, 2, 31, 38)); //ROW 3 FOR PAEDS STARTS HERE HSSFRow rw1shet1 = shet1.createRow(3); rw1shet1.setHeightInPoints(30); HSSFRow rw1shet2 = shet2.createRow(3); rw1shet2.setHeightInPoints(30); HSSFCell c01; for (int j = 0; j < headerART.length; j++) { c01 = rw1shet1.createCell(j); c01.setCellStyle(styleHeader); } for (int j = 0; j < headerCARE.length; j++) { c01 = rw1shet2.createCell(j); c01.setCellStyle(styleHeader); } c01 = rw1shet1.getCell(6); c01.setCellValue("Paeds <15yrs"); c01 = rw1shet1.getCell(9); c01.setCellValue("Adults 15+ Yr"); c01 = rw1shet1.getCell(11); c01.setCellValue("Paeds <15yrs"); c01 = rw1shet1.getCell(14); c01.setCellValue("Adults 15+ Yr"); c01 = rw1shet1.getCell(17); c01.setCellValue("Paeds <15yrs"); c01 = rw1shet1.getCell(21); c01.setCellValue("Adults 15+ Yr"); c01 = rw1shet1.getCell(25); c01.setCellValue("Paeds <15yrs"); c01 = rw1shet1.getCell(29); c01.setCellValue("Adults 15+ Yr"); shet1.addMergedRegion(new CellRangeAddress(3, 3, 6, 8)); shet1.addMergedRegion(new CellRangeAddress(3, 3, 9, 10)); shet1.addMergedRegion(new CellRangeAddress(3, 3, 11, 13)); shet1.addMergedRegion(new CellRangeAddress(3, 3, 14, 15)); shet1.addMergedRegion(new CellRangeAddress(3, 3, 17, 20)); shet1.addMergedRegion(new CellRangeAddress(3, 3, 21, 24)); shet1.addMergedRegion(new CellRangeAddress(3, 3, 25, 28)); shet1.addMergedRegion(new CellRangeAddress(3, 3, 29, 32)); c01 = rw1shet2.getCell(6); c01.setCellValue("Paeds <15yrs"); c01 = rw1shet2.getCell(10); c01.setCellValue("Adults 15+ Yr"); c01 = rw1shet2.getCell(14); c01.setCellValue("Paeds <15yrs"); c01 = rw1shet2.getCell(18); c01.setCellValue("Adults 15+ Yr"); c01 = rw1shet2.getCell(23); c01.setCellValue("Paeds <15yrs"); c01 = rw1shet2.getCell(27); c01.setCellValue("Adults 15+ Yr"); c01 = rw1shet2.getCell(31); c01.setCellValue("Paeds <15yrs"); c01 = rw1shet2.getCell(35); c01.setCellValue("Adults 15+ Yr"); shet2.addMergedRegion(new CellRangeAddress(3, 3, 6, 9)); shet2.addMergedRegion(new CellRangeAddress(3, 3, 10, 13)); shet2.addMergedRegion(new CellRangeAddress(3, 3, 14, 17)); shet2.addMergedRegion(new CellRangeAddress(3, 3, 18, 21)); shet2.addMergedRegion(new CellRangeAddress(3, 3, 23, 26)); shet2.addMergedRegion(new CellRangeAddress(3, 3, 27, 30)); shet2.addMergedRegion(new CellRangeAddress(3, 3, 31, 34)); shet2.addMergedRegion(new CellRangeAddress(3, 3, 35, 38)); shet1.addMergedRegion(new CellRangeAddress(1, 3, 0, 4)); shet2.addMergedRegion(new CellRangeAddress(1, 3, 0, 4)); HSSFRow rw2shet1 = shet1.createRow(4); rw2shet1.setHeightInPoints(30); HSSFRow rw2shet2 = shet2.createRow(4); rw2shet2.setHeightInPoints(30); HSSFCell c11; for (int headerpos = 0; headerpos < headerART.length; headerpos++) { String headerInfor = headerART[headerpos]; c11 = rw2shet1.createCell(headerpos); c11.setCellValue(headerInfor); c11.setCellStyle(styleHeader); } System.out.println("art header length : " + headerART.length); for (int headerpos = 0; headerpos < headerCARE.length; headerpos++) { String headerInfor = headerCARE[headerpos]; c11 = rw2shet2.createCell(headerpos); c11.setCellValue(headerInfor); c11.setCellStyle(styleHeader); } shet1.addMergedRegion(new CellRangeAddress(2, 4, 5, 5)); shet2.addMergedRegion(new CellRangeAddress(2, 4, 5, 5)); shet1.addMergedRegion(new CellRangeAddress(2, 4, 16, 16)); shet2.addMergedRegion(new CellRangeAddress(2, 4, 22, 22)); // PMTCT HEADER===================================================================================== HSSFRow rw0shetPMTCT = shetPMTCT.createRow(1); rw0shetPMTCT.setHeightInPoints(30); for (int j = 0; j < headerPMTCT.length; j++) { c001 = rw0shetPMTCT.createCell(j); c001.setCellStyle(styleHeader); } c001 = rw0shetPMTCT.getCell(0); c001.setCellValue(period); c001 = rw0shetPMTCT.getCell(5); c001.setCellValue("PMTCT_FO"); c001 = rw0shetPMTCT.getCell(17); c001.setCellValue("PMTCT_ARV"); c001 = rw0shetPMTCT.getCell(24); c001.setCellValue("PMTCT_EID"); c001 = rw0shetPMTCT.getCell(29); c001.setCellValue("PMTCT_STAT (Numerator)"); c001 = rw0shetPMTCT.getCell(32); c001.setCellValue("PMTCT_STAT (Denominator)"); c001 = rw0shetPMTCT.getCell(33); c001.setCellValue("PMTCT_CTX"); shetPMTCT.addMergedRegion(new CellRangeAddress(1, 1, 0, 4)); shetPMTCT.addMergedRegion(new CellRangeAddress(1, 1, 5, 16)); shetPMTCT.addMergedRegion(new CellRangeAddress(1, 1, 17, 23)); shetPMTCT.addMergedRegion(new CellRangeAddress(1, 1, 24, 28)); shetPMTCT.addMergedRegion(new CellRangeAddress(1, 1, 29, 31)); // shetPMTCT.addMergedRegion(new CellRangeAddress(2,2,25,32)); HSSFRow rw2shetPMTCT = shetPMTCT.createRow(2); rw2shetPMTCT.setHeightInPoints(50); for (int headerpos = 0; headerpos < headerPMTCT.length; headerpos++) { String headerInfor = headerPMTCT[headerpos]; c11 = rw2shetPMTCT.createCell(headerpos); c11.setCellValue(headerInfor); c11.setCellStyle(styleHeader); } System.out.println("art header length : " + headerPMTCT.length); artpos = 4; pmtctpos = 3; totalNewART = totalCurrentART = totalNewCARE = totalCurrentCARE = 0; // String getData="SELECT subpartnera.SubPartnerNom,district.DistrictNom,county.County," // + "subpartnera.CentreSanteId,ART_Support,PMTCT_Support," // + "SUM(HV0308),SUM(HV0309),SUM(HV0310),SUM(HV0311),SUM(HV0312)," // + "SUM(HV0320),SUM(HV0321),SUM(HV0322),SUM(HV0323),SUM(HV0324)," // + "subpartnera.SubPartnerID FROM moh731 JOIN subpartnera " // + "ON moh731.SubPartnerID=subpartnera.SubPartnerID " // + "JOIN district ON subpartnera.DistrictID=district.DistrictID JOIN county ON " // + "district.CountyID=county.CountyID" // + " WHERE " // + " "+facilityIds+" "+duration+" && (subpartnera.PMTCT=1 || ART=1) " // + "GROUP BY moh731.SubPartnerID " ; String getData = "SELECT subpartnera.SubPartnerNom,district.DistrictNom,county.County," + "subpartnera.CentreSanteId,ART_Support,PMTCT_Support," + "SUM(HV0308),SUM(HV0309),SUM(HV0310),SUM(HV0311),SUM(HV0312)," + "SUM(HV0320),SUM(HV0321),SUM(HV0322),SUM(HV0323),SUM(HV0324)," + "subpartnera.SubPartnerID," + "SUM(HV0205),SUM(HV0209),SUM(HV0210),SUM(HV0216),SUM(HV0217)," + "SUM(HV0224),SUM(HV0225),SUM(HV0227),SUM(HV0229),SUM(HV0230),SUM(HV0231),SUM(HV0232)," + "SUM(HV0301),SUM(HV0206),SUM(HV0207),SUM(HV0208) " + " FROM moh731 JOIN subpartnera " + "ON moh731.SubPartnerID=subpartnera.SubPartnerID " + "JOIN district ON subpartnera.DistrictID=district.DistrictID JOIN county ON " + "district.CountyID=county.CountyID" + " WHERE " + " " + facilityIds + " " + duration + " && (subpartnera.PMTCT=1 || ART=1) " + "GROUP BY moh731.SubPartnerID "; System.out.println("new : " + getData); conn.rs = conn.st.executeQuery(getData); while (conn.rs.next()) { HV0308 = HV0309 = HV0310 = HV0311 = HV0312 = HV0320 = HV0321 = HV0322 = HV0323 = HV0324 = 0; HV0314 = HV0315 = HV0316 = HV0317 = HV0318 = HV0334 = HV0335 = HV0336 = HV0337 = HV0338 = 0; currentART1M = currentART1_4M = currentART5_14M = currentART15_19M = currentART20M = 0; currentART1F = currentART1_4F = currentART5_14F = currentART15_19F = currentART20F = 0; newART1M = newART1_4M = newART5_9M = newART10_14M = newART15_19M = newART20_24M = newART25_49M = newART50M = 0; newART1F = newART1_4F = newART5_9F = newART10_14F = newART15_19F = newART20_24F = newART25_49F = newART50F = 0; newCARE1M = newCARE1_4M = newCARE5_9M = newCARE10_14M = newCARE15_19M = newCARE20_24M = newCARE25_49M = newCARE50M = 0; newCARE1F = newCARE1_4F = newCARE5_9F = newCARE10_14F = newCARE15_19F = newCARE20_24F = newCARE25_49F = newCARE50F = 0; currentCARE1M = currentCARE1_4M = currentCARE5_9M = currentCARE10_14M = currentCARE15_19M = currentCARE20_24M = currentCARE25_49M = currentCARE50M = 0; currentCARE1F = currentCARE1_4F = currentCARE5_9F = currentCARE10_14F = currentCARE15_19F = currentCARE20_24F = currentCARE25_49F = currentCARE50F = 0; PMTCT_FO_I_N = PMTCT_FO_I_D = PMTCT_FO_I_LINKED = PMTCT_FO_I_NOT_LINKED = PMTCT_FO_I_UNKNOWN = PMTCT_FO_U_NOT_BREASTFEEDING = PMTCT_FO_U_STILL_BREASTFEEDING = PMTCT_FO_U_BREASTFEEDING_UNKNOWN = PMTCT_FO_OTHER_INCARE = PMTCT_FO_OTHER_NOFOLLOWUP = PMTCT_FO_DIED = PMTCT_FO_TRANSFERRED = 0.0; PMTCT_ARV_N = PMTCT_ARV_D = PMTCT_ARV_LIFELONGART_NEW = PMTCT_ARV_LIFELONGART_EXISTING = PMTCT_ARV_MATERNAL_TRIPLEDRUG_ARV = PMTCT_ARV_MATERNAL_AZT = PMTCT_ARV_SINGLEDOSE = 0.0; PMTCT_EID_N = PMTCT_EID_VIRO_2MONTHS = PMTCT_EID_VIRO_2_12MONTHS = PMTCT_EID_P_VIRO_2MONTHS = PMTCT_EID_P_VIRO_2_12MONTHS = 0.0; PMTCT_STATN_N = PMTCT_STATN_KNOWNPOSTIVE = PMTCTN_STAT_NEWPOSTIVE = 0.0; PMTCT_STATD_D = PMTCT_STATD_LESS15 = PMTCT_STATD_15_19 = PMTCT_STATD_20_24 = PMTCT_STATD_25 = 0.0; PMTCT_CTX = 0.0; facilityName = conn.rs.getString(1); districtName = conn.rs.getString(2); countyName = conn.rs.getString(3); mflcode = conn.rs.getString(4); ARTSupport = conn.rs.getString(5); // CARESuport=conn.rs.getString(6); PMTCTSupport = conn.rs.getString(6); HV0308 = conn.rs.getInt(7); HV0309 = conn.rs.getInt(8); HV0310 = conn.rs.getInt(9); HV0311 = conn.rs.getInt(10); HV0312 = conn.rs.getInt(11); HV0320 = conn.rs.getInt(12); HV0321 = conn.rs.getInt(13); HV0322 = conn.rs.getInt(14); HV0323 = conn.rs.getInt(15); HV0324 = conn.rs.getInt(16); facilityId = conn.rs.getString(17); HV0205 = conn.rs.getInt(18); HV0209 = conn.rs.getInt(19); HV0210 = conn.rs.getInt(20); HV0216 = conn.rs.getInt(21); HV0217 = conn.rs.getInt(22); HV0224 = conn.rs.getInt(23); HV0225 = conn.rs.getInt(24); HV0227 = conn.rs.getInt(25); HV0229 = conn.rs.getInt(26); HV0230 = conn.rs.getInt(27); HV0231 = conn.rs.getInt(28); HV0232 = conn.rs.getInt(29); // HV0301=conn.rs.getInt(30); HV0206 = conn.rs.getInt(31); HV0207 = conn.rs.getInt(32); HV0208 = conn.rs.getInt(33); String getMaxYearMonth = "SELECT MAX(yearmonth) FROM moh731 WHERE moh731.SubPartnerID='" + facilityId + "' && " + duration; conn.rs2 = conn.st2.executeQuery(getMaxYearMonth); if (conn.rs2.next() == true) { maxYearMonth = conn.rs2.getInt(1); } String getCurrent = "SELECT HV0314,HV0315,HV0316,HV0317,HV0318," + "HV0334,HV0335,HV0336,HV0337,HV0338,HV0301 FROM moh731 WHERE " + "moh731.SubPartnerID='" + facilityId + "' && yearmonth='" + maxYearMonth + "'"; System.out.println("current : " + getCurrent); conn.rs1 = conn.st1.executeQuery(getCurrent); if (conn.rs1.next() == true) { HV0314 = conn.rs1.getInt(1); HV0315 = conn.rs1.getInt(2); HV0316 = conn.rs1.getInt(3); HV0317 = conn.rs1.getInt(4); HV0318 = conn.rs1.getInt(5); HV0334 = conn.rs1.getInt(6); HV0335 = conn.rs1.getInt(7); HV0336 = conn.rs1.getInt(8); HV0337 = conn.rs1.getInt(9); HV0338 = conn.rs1.getInt(10); HV0301 = conn.rs1.getInt(11); } if (ARTSupport != null) { double splitData; int adderPos = 0; // VALUES FOR CURRENT ON ART currentART1M = (float) Math.round((0.03 * HV0335)); currentART1_4M = (float) Math.round((0.32 * HV0335)); currentART5_14M = (float) Math.round((0.65 * HV0335)); splitData = currentART1M + currentART1_4M + currentART5_14M; adderPos = 0; while (splitData < HV0335) { if (adderPos < 2) { currentART5_14M += 1; } else { currentART1_4M += 1; } splitData++; adderPos++; if (adderPos > 2) { adderPos = 0; } if (splitData == HV0335) { } } splitData = currentART1M + currentART1_4M + currentART5_14M; adderPos = 0; while (splitData > HV0335) { if (adderPos < 2) { currentART5_14M -= 1; } else { currentART1_4M -= 1; } splitData--; adderPos++; if (adderPos > 2) { adderPos = 0; } if (splitData == HV0335) { } } currentART15_19M = (float) Math.round((0.02 * HV0337)); currentART20M = (float) Math.round((0.98 * HV0337)); splitData = currentART20M + currentART15_19M; while (splitData < HV0337) { currentART20M += 1; splitData++; } splitData = currentART20M + currentART15_19M; while (splitData > HV0337) { currentART20M -= 1; splitData--; } currentART1F = (float) Math.round((0.03 * HV0336));//NEED CLARIFICATION currentART1_4F = (float) Math.round((0.32 * HV0336)); currentART5_14F = (float) Math.round((0.65 * HV0336)); splitData = currentART5_14F + currentART1_4F + currentART1F; adderPos = 0; while (splitData < HV0336) { if (adderPos < 2) { currentART5_14F += 1; } else { currentART1_4F += 1; } splitData++; adderPos++; if (adderPos > 2) { adderPos = 0; } } splitData = currentART5_14F + currentART1_4F + currentART1F; adderPos = 0; while (splitData > HV0336) { if (adderPos < 2) { currentART5_14F -= 1; } else { currentART1_4F -= 1; } splitData--; adderPos++; if (adderPos > 2) { adderPos = 0; } } currentART15_19F = (float) Math.round((0.02 * HV0338)); currentART20F = (float) Math.round((0.98 * HV0338)); splitData = currentART20F + currentART15_19F; while (splitData < HV0338) { currentART20F += 1; splitData++; } splitData = currentART20F + currentART15_19F; while (splitData > HV0338) { currentART20F -= 1; splitData--; } totalCurrentART = HV0338 + HV0336 + HV0337 + HV0335; // VALUES newART1M = (float) Math.round((0.034 * HV0321)); newART1_4M = (float) Math.round((0.214 * HV0321)); newART5_9M = (float) Math.round((0.37 * HV0321)); newART10_14M = (float) Math.round((0.382 * HV0321)); splitData = newART10_14M + newART5_9M + newART1_4M + newART1M; adderPos = 0; while (splitData < HV0321) { if (adderPos == 0) { newART10_14M += 1; } else if (adderPos == 1) { newART5_9M += 1; } else { newART1_4M += 1; } splitData++; adderPos++; if (adderPos > 2) { adderPos = 0; } } splitData = newART10_14M + newART5_9M + newART1_4M + newART1M; adderPos = 0; while (splitData > HV0321) { if (adderPos == 0) { newART10_14M -= 1; } else if (adderPos == 1) { newART5_9M -= 1; } else { newART1_4M -= 1; } splitData--; adderPos++; if (adderPos > 2) { adderPos = 0; } } newART15_19M = (float) Math.round((0.008 * HV0323)); newART20_24M = (float) Math.round((0.008 * HV0323)); newART25_49M = (float) Math.round((0.775 * HV0323)); newART50M = (float) Math.round((0.209 * HV0323)); splitData = newART25_49M + newART50M + newART20_24M + newART15_19M; System.out.println("split data : " + splitData + " all data " + HV0323); adderPos = 0; while (splitData < HV0323) { if (adderPos < 3) { newART25_49M += 1; } else { newART50M += 1; } splitData++; adderPos++; if (adderPos > 3) { adderPos = 0; } } splitData = newART25_49M + newART50M + newART20_24M + newART15_19M; // System.out.println("split data : "+splitData+" all data"+HV0323); adderPos = 0; while (splitData > HV0323) { if (adderPos < 3) { newART25_49M -= 1; } else { newART50M -= 1; } splitData--; adderPos++; if (adderPos > 3) { adderPos = 0; } } newART1F = (float) Math.round((0.034 * HV0322)); newART1_4F = (float) Math.round((0.214 * HV0322)); newART5_9F = (float) Math.round((0.37 * HV0322)); newART10_14F = (float) Math.round((0.382 * HV0322)); splitData = newART10_14F + newART5_9F + newART1_4F + newART1F; adderPos = 0; while (splitData < HV0322) { if (adderPos == 0) { newART10_14F += 1; } else if (adderPos == 1) { newART5_9F += 1; } else { newART1_4F += 1; } splitData++; adderPos++; if (adderPos > 2) { adderPos = 0; } } splitData = newART10_14F + newART5_9F + newART1_4F + newART1F; adderPos = 0; while (splitData > HV0322) { if (adderPos == 0) { newART10_14F -= 1; } else if (adderPos == 1) { newART5_9F -= 1; } else { newART1_4F -= 1; } splitData--; adderPos++; if (adderPos > 2) { adderPos = 0; } } newART15_19F = (float) Math.round((0.008 * HV0324)); newART20_24F = (float) Math.round((0.008 * HV0324)); newART25_49F = (float) Math.round((0.775 * HV0324)); newART50F = (float) Math.round((0.209 * HV0324)); splitData = newART25_49F + newART50F + newART20_24F + newART15_19F; adderPos = 0; while (splitData < HV0324) { if (adderPos < 3) { newART25_49F += 1; } else { newART50F += 1; } splitData++; adderPos++; if (adderPos > 3) { adderPos = 0; } } splitData = newART25_49F + newART50F + newART20_24F + newART15_19F; adderPos = 0; while (splitData > HV0324) { if (adderPos < 3) { newART25_49F -= 1; } else { newART50F -= 1; } splitData--; adderPos++; if (adderPos > 3) { adderPos = 0; } } totalNewART = HV0324 + HV0322 + HV0323 + HV0321; System.out.println(); // VALUES newCARE1M = (float) Math.round((0.18 * HV0309)); newCARE1_4M = (float) Math.round((0.34 * HV0309)); newCARE5_9M = (float) Math.round((0.28 * HV0309)); newCARE10_14M = (float) Math.round((0.20 * HV0309)); splitData = newCARE10_14M + newCARE5_9M + newCARE1_4M + newCARE1M; adderPos = 0; while (splitData < HV0309) { if (adderPos == 0) { newCARE1_4M += 1; } else if (adderPos == 1) { newCARE5_9M += 1; } else if (adderPos == 2) { newCARE10_14M += 1; } else if (adderPos == 3) { newCARE1M += 1; } splitData++; adderPos++; if (adderPos > 3) { adderPos = 0; } } splitData = newCARE10_14M + newCARE5_9M + newCARE1_4M + newCARE1M; adderPos = 0; while (splitData > HV0309) { if (adderPos == 0) { newCARE1_4M -= 1; } else if (adderPos == 1) { newCARE5_9M -= 1; } else if (adderPos == 2) { newCARE10_14M -= 1; } else if (adderPos == 3) { newCARE1M -= 1; } splitData--; adderPos++; if (adderPos > 3) { adderPos = 0; } } newCARE15_19M = (float) Math.round((0.02 * HV0311)); newCARE20_24M = (float) Math.round((0.09 * HV0311)); newCARE25_49M = (float) Math.round((0.80 * HV0311)); newCARE50M = (float) Math.round((0.09 * HV0311)); splitData = newCARE50M + newCARE25_49M + newCARE20_24M + newCARE15_19M; while (splitData < HV0311) { newCARE25_49M += 1; splitData++; } splitData = newCARE50M + newCARE25_49M + newCARE20_24M + newCARE15_19M; while (splitData > HV0311) { newCARE25_49M -= 1; splitData--; } newCARE1F = (float) Math.round((0.18 * HV0310)); newCARE1_4F = (float) Math.round((0.34 * HV0310)); newCARE5_9F = (float) Math.round((0.28 * HV0310)); newCARE10_14F = (float) Math.round((0.20 * HV0310)); splitData = newCARE10_14F + newCARE5_9F + newCARE1_4F + newCARE1F; adderPos = 0; while (splitData < HV0310) { if (adderPos == 0) { newCARE1_4F += 1; } else if (adderPos == 1) { newCARE5_9F += 1; } else if (adderPos == 2) { newCARE10_14F += 1; } else if (adderPos == 3) { newCARE1F += 1; } splitData++; adderPos++; if (adderPos > 3) { adderPos = 0; } } splitData = newCARE10_14F + newCARE5_9F + newCARE1_4F + newCARE1F; adderPos = 0; while (splitData > HV0310) { if (adderPos == 0) { newCARE1_4F -= 1; } else if (adderPos == 1) { newCARE5_9F -= 1; } else if (adderPos == 2) { newCARE10_14F -= 1; } else if (adderPos == 3) { newCARE1F -= 1; } splitData--; adderPos++; if (adderPos > 3) { adderPos = 0; } } newCARE15_19F = (float) Math.round((0.02 * HV0312)); newCARE20_24F = (float) Math.round((0.09 * HV0312)); newCARE25_49F = (float) Math.round((0.80 * HV0312)); newCARE50F = (float) Math.round((0.09 * HV0312)); splitData = newCARE50F + newCARE25_49F + newCARE20_24F + newCARE15_19F; while (splitData < HV0312) { newCARE25_49F += 1; splitData++; } splitData = newCARE50F + newCARE25_49F + newCARE20_24F + newCARE15_19F; while (splitData > HV0312) { newCARE25_49F -= 1; splitData--; } totalNewCARE = HV0312 + HV0310 + HV0311 + HV0309; // VALUES currentCARE1M = (float) Math.round((0.03 * HV0315)); currentCARE1_4M = (float) Math.round((0.22 * HV0315)); currentCARE5_9M = (float) Math.round((0.37 * HV0315)); currentCARE10_14M = (float) Math.round((0.38 * HV0315)); splitData = currentCARE10_14M + currentCARE5_9M + currentCARE1_4M + currentCARE1M; adderPos = 0; while (splitData < HV0315) { if (adderPos == 0) { currentCARE10_14M += 1; } else if (adderPos == 1) { currentCARE5_9M += 1; } else if (adderPos == 2) { currentCARE1_4M += 1; } splitData++; adderPos++; if (adderPos > 2) { adderPos = 0; } } splitData = currentCARE10_14M + currentCARE5_9M + currentCARE1_4M + currentCARE1M; adderPos = 0; while (splitData > HV0315) { if (adderPos == 0) { currentCARE10_14M -= 1; } else if (adderPos == 1) { currentCARE5_9M -= 1; } else if (adderPos == 2) { currentCARE1_4M -= 1; } splitData--; adderPos++; if (adderPos > 2) { adderPos = 0; } } currentCARE15_19M = (float) Math.round((0.02 * HV0317)); currentCARE20_24M = (float) Math.round((0.09 * HV0317)); currentCARE25_49M = (float) Math.round((0.80 * HV0317)); currentCARE50M = (float) Math.round((0.09 * HV0317)); splitData = currentCARE50M + currentCARE25_49M + currentCARE20_24M + currentCARE15_19M; while (splitData < HV0317) { currentCARE25_49M += 1; splitData++; } splitData = currentCARE50M + currentCARE25_49M + currentCARE20_24M + currentCARE15_19M; while (splitData > HV0317) { currentCARE25_49M -= 1; splitData--; } currentCARE1F = (float) Math.round((0.03 * HV0316)); currentCARE1_4F = (float) Math.round((0.22 * HV0316)); currentCARE5_9F = (float) Math.round((0.37 * HV0316)); currentCARE10_14F = (float) Math.round((0.38 * HV0316)); splitData = currentCARE10_14F + currentCARE5_9F + currentCARE1_4F + currentCARE1F; adderPos = 0; while (splitData < HV0316) { if (adderPos == 0) { currentCARE10_14F += 1; } else if (adderPos == 1) { currentCARE5_9F += 1; } else if (adderPos == 2) { currentCARE1_4F += 1; } splitData++; adderPos++; if (adderPos > 2) { adderPos = 0; } } splitData = currentCARE10_14F + currentCARE5_9F + currentCARE1_4F + currentCARE1F; adderPos = 0; while (splitData > HV0316) { if (adderPos == 0) { currentCARE10_14F -= 1; } else if (adderPos == 1) { currentCARE5_9F -= 1; } else if (adderPos == 2) { currentCARE1_4F -= 1; } splitData--; adderPos++; if (adderPos > 2) { adderPos = 0; } } currentCARE15_19F = (float) Math.round((0.02 * HV0318)); currentCARE20_24F = (float) Math.round((0.09 * HV0318)); currentCARE25_49F = (float) Math.round((0.80 * HV0318)); currentCARE50F = (float) Math.round((0.09 * HV0318)); splitData = currentCARE50F + currentCARE25_49F + currentCARE20_24F + currentCARE15_19F; while (splitData < HV0318) { currentCARE25_49F += 1; splitData++; } splitData = currentCARE50F + currentCARE25_49F + currentCARE20_24F + currentCARE15_19F; while (splitData > HV0318) { currentCARE25_49F -= 1; splitData--; } totalCurrentCARE = HV0318 + HV0316 + HV0317 + HV0315; String dataART[] = (countyName + "," + districtName + "," + facilityName + "," + mflcode + "," + ARTSupport + "," + totalCurrentART + "," + "" + currentART1F + "," + currentART1_4F + "," + currentART5_14F + "," + currentART15_19F + "," + "" + currentART20F + "," + currentART1M + "," + currentART1_4M + "," + currentART5_14M + "," + "" + currentART15_19M + "," + currentART20M + "," + totalNewART + "," + "" + newART1F + "," + newART1_4F + "," + newART5_9F + "," + newART10_14F + "," + newART15_19F + "," + newART20_24F + "," + "" + newART25_49F + "," + newART50F + "," + newART1M + "," + newART1_4M + "," + newART5_9M + "," + newART10_14M + "," + "" + newART15_19M + "," + newART20_24M + "," + newART25_49M + "," + newART50M).split(","); String dataCARE[] = (countyName + "," + districtName + "," + facilityName + "," + mflcode + "," + ARTSupport + "," + totalCurrentCARE + "," + "" + currentCARE1F + "," + currentCARE1_4F + "," + currentCARE5_9F + "," + currentCARE10_14F + "," + currentCARE15_19F + "," + "" + currentCARE20_24F + "," + currentCARE25_49F + "," + currentCARE50F + "," + currentCARE1M + "," + currentCARE1_4M + "," + "" + currentCARE5_9M + "," + currentCARE10_14M + "," + currentCARE15_19M + "," + currentCARE20_24M + "," + "" + currentCARE25_49M + "," + currentCARE50M + "," + totalNewCARE + "," + "" + newCARE1F + "," + newCARE1_4F + "," + newCARE5_9F + "," + newCARE10_14F + "," + newCARE15_19F + "," + newCARE20_24F + "," + "" + newCARE25_49F + "," + newCARE50F + "," + newCARE1M + "," + newCARE1_4M + "," + newCARE5_9M + "," + newCARE10_14M + "," + "" + newCARE15_19M + "," + newCARE20_24M + "," + newCARE25_49M + "," + newCARE50M).split(","); artpos++; HSSFRow rw3shet1 = shet1.createRow(artpos); rw3shet1.setHeightInPoints(25); for (int positionART = 0; positionART < dataART.length; positionART++) { String value = dataART[positionART]; c11 = rw3shet1.createCell(positionART); if (positionART > 4) { c11.setCellValue(Double.parseDouble(value)); } else { c11.setCellValue(value); } c11.setCellStyle(stborder); if (positionART == 5 || positionART == 16) { c11.setCellStyle(styleHeader); } } // System.out.println("art data length : "+dataART.length); HSSFRow rw3Shet2 = shet2.createRow(artpos); rw3Shet2.setHeightInPoints(25); for (int positionCARE = 0; positionCARE < dataCARE.length; positionCARE++) { String value = dataCARE[positionCARE]; c11 = rw3Shet2.createCell(positionCARE); if (positionCARE > 4) { c11.setCellValue(Double.parseDouble(value)); } else { c11.setCellValue(value); } c11.setCellStyle(stborder); if (positionCARE == 5 || positionCARE == 22) { c11.setCellStyle(styleHeader); } } } // OUTPUT PMTCT DATA HERE +=========================================================================================== if (PMTCTSupport != null) { numerator = denominator = 0; String pmtct_fo_id = year + "_" + quarter + "_" + facilityId; String getPMTCTFO = "SELECT numerator,denominator FROM pmtct_fo WHERE id='" + pmtct_fo_id + "' "; conn.rs1 = conn.st1.executeQuery(getPMTCTFO); if (conn.rs1.next() == true) { numerator = conn.rs1.getInt(1); denominator = conn.rs1.getInt(2); } if (!reportDuration.equals("3")) { numerator = denominator = 0; } // PMTCT_FO=================================================================================================== PMTCT_FO_I_N = (double) numerator; PMTCT_FO_I_D = (double) denominator; PMTCT_FO_I_LINKED = (double) Math.round((0.05 * numerator)); PMTCT_FO_I_NOT_LINKED = 0.0; PMTCT_FO_I_UNKNOWN = 0.0; PMTCT_FO_U_NOT_BREASTFEEDING = (double) Math.round((0.83 * numerator)); PMTCT_FO_U_STILL_BREASTFEEDING = 0.0; PMTCT_FO_U_BREASTFEEDING_UNKNOWN = 0.0; PMTCT_FO_OTHER_INCARE = 0.0; PMTCT_FO_OTHER_NOFOLLOWUP = (double) Math.round((0.08 * numerator)); PMTCT_FO_DIED = (double) Math.round((0.02 * numerator)); PMTCT_FO_TRANSFERRED = (double) Math.round((0.02 * numerator)); double normalizer = PMTCT_FO_I_LINKED + PMTCT_FO_I_NOT_LINKED + PMTCT_FO_I_UNKNOWN + PMTCT_FO_U_NOT_BREASTFEEDING + PMTCT_FO_U_STILL_BREASTFEEDING + PMTCT_FO_U_BREASTFEEDING_UNKNOWN + PMTCT_FO_OTHER_INCARE + PMTCT_FO_OTHER_NOFOLLOWUP + PMTCT_FO_DIED + PMTCT_FO_TRANSFERRED; int pmtctnum = 0; while (numerator > normalizer) { PMTCT_FO_U_NOT_BREASTFEEDING++; normalizer++; } while (numerator < normalizer) { PMTCT_FO_U_NOT_BREASTFEEDING--; normalizer--; } // PMTCT_ARV=================================================================================================== PMTCT_ARV_N = (double) HV0217; PMTCT_ARV_D = (double) HV0209; PMTCT_ARV_LIFELONGART_NEW = (double) Math.round((0.75 * HV0217)); PMTCT_ARV_LIFELONGART_EXISTING = (double) Math.round((0.25 * HV0217)); normalizer = PMTCT_ARV_LIFELONGART_NEW + PMTCT_ARV_LIFELONGART_EXISTING; pmtctnum = 0; while (HV0217 > normalizer) { if (pmtctnum < 3) { PMTCT_ARV_LIFELONGART_NEW++; } else { PMTCT_ARV_LIFELONGART_EXISTING++; } if (pmtctnum == 3) { pmtctnum = 0; } normalizer++; pmtctnum++; } normalizer = PMTCT_ARV_LIFELONGART_NEW + PMTCT_ARV_LIFELONGART_EXISTING; pmtctnum = 0; while (normalizer > HV0217) { if (pmtctnum < 3) { PMTCT_ARV_LIFELONGART_NEW--; } else { PMTCT_ARV_LIFELONGART_EXISTING--; } if (pmtctnum == 3) { pmtctnum = 0; } normalizer--; pmtctnum++; } PMTCT_ARV_MATERNAL_TRIPLEDRUG_ARV = 0.0; PMTCT_ARV_MATERNAL_AZT = 0.0; PMTCT_ARV_SINGLEDOSE = 0.0; // PMTCT_EID=================================================================================================== PMTCT_EID_N = (double) (HV0224 + HV0225 + HV0227); PMTCT_EID_VIRO_2MONTHS = (double) (HV0224); PMTCT_EID_VIRO_2_12MONTHS = (double) (HV0225 + HV0227); PMTCT_EID_P_VIRO_2MONTHS = (double) (HV0229); PMTCT_EID_P_VIRO_2_12MONTHS = (double) (HV0230 + HV0231); // PMTCT_STAT NUMERATOR====================================================================================================== PMTCT_STATN_N = (double) HV0210; PMTCT_STATN_KNOWNPOSTIVE = (double) HV0205; PMTCTN_STAT_NEWPOSTIVE = (double) (HV0206 + HV0207 + HV0208); PMTCT_STATD_D = (double) Math.round((1.03 * HV0210)); // PMTCT_CTX===================================================================================================== PMTCT_CTX = (double) HV0301; //Mo clarification whether to um or take most recent if (PMTCTSupport != null) { if (!reportDuration.equals("3")) { numerator = denominator = 0; String dataPMTCT[] = (countyName + "," + districtName + "," + facilityName + "," + mflcode + "," + PMTCTSupport + ",," + ",,,,,,,,,,," + "" + PMTCT_ARV_N + "," + PMTCT_ARV_D + "," + PMTCT_ARV_LIFELONGART_NEW + "," + PMTCT_ARV_LIFELONGART_EXISTING + "," + PMTCT_ARV_MATERNAL_TRIPLEDRUG_ARV + "," + PMTCT_ARV_MATERNAL_AZT + "," + "" + PMTCT_ARV_SINGLEDOSE + "," + PMTCT_EID_N + "," + PMTCT_EID_VIRO_2MONTHS + "," + PMTCT_EID_VIRO_2_12MONTHS + "," + PMTCT_EID_P_VIRO_2MONTHS + "," + PMTCT_EID_P_VIRO_2_12MONTHS + "," + "" + PMTCT_STATN_N + "," + PMTCT_STATN_KNOWNPOSTIVE + "," + PMTCTN_STAT_NEWPOSTIVE + "," + "" + PMTCT_STATD_D + "," + PMTCT_CTX).split(","); HSSFRow rw3shetPMTCT = shetPMTCT.createRow(pmtctpos); rw3shetPMTCT.setHeightInPoints(25); for (int positionPMTCT = 0; positionPMTCT < dataPMTCT.length; positionPMTCT++) { String value = dataPMTCT[positionPMTCT]; c11 = rw3shetPMTCT.createCell(positionPMTCT); if (positionPMTCT > 16 && positionPMTCT < (dataPMTCT.length)) { c11.setCellValue(Double.parseDouble(value)); } else { c11.setCellValue(value); } c11.setCellStyle(stborder); if (positionPMTCT == 17 || positionPMTCT == 18 || positionPMTCT == 24 || positionPMTCT == 29 || positionPMTCT == 32 || positionPMTCT == 33) { c11.setCellStyle(styleHeader); } } pmtctpos++; } else { // HAVE FORMULAS HERE AND THE OUTPUT FOR PMTCT String dataPMTCT[] = (countyName + "," + districtName + "," + facilityName + "," + mflcode + "," + PMTCTSupport + "," + PMTCT_FO_I_N + "," + "" + PMTCT_FO_I_D + "," + PMTCT_FO_I_LINKED + "," + PMTCT_FO_I_NOT_LINKED + "," + PMTCT_FO_I_UNKNOWN + "," + "" + PMTCT_FO_U_NOT_BREASTFEEDING + "," + PMTCT_FO_U_STILL_BREASTFEEDING + "," + PMTCT_FO_U_BREASTFEEDING_UNKNOWN + "," + PMTCT_FO_OTHER_INCARE + "," + PMTCT_FO_OTHER_NOFOLLOWUP + "," + PMTCT_FO_DIED + "," + PMTCT_FO_TRANSFERRED + "," + "" + PMTCT_ARV_N + "," + PMTCT_ARV_D + "," + PMTCT_ARV_LIFELONGART_NEW + "," + PMTCT_ARV_LIFELONGART_EXISTING + "," + PMTCT_ARV_MATERNAL_TRIPLEDRUG_ARV + "," + PMTCT_ARV_MATERNAL_AZT + "," + "" + PMTCT_ARV_SINGLEDOSE + "," + PMTCT_EID_N + "," + PMTCT_EID_VIRO_2MONTHS + "," + PMTCT_EID_VIRO_2_12MONTHS + "," + PMTCT_EID_P_VIRO_2MONTHS + "," + PMTCT_EID_P_VIRO_2_12MONTHS + "," + "" + PMTCT_STATN_N + "," + PMTCT_STATN_KNOWNPOSTIVE + "," + PMTCTN_STAT_NEWPOSTIVE + "," + "" + PMTCT_STATD_D + ",").split(","); HSSFRow rw3shetPMTCT = shetPMTCT.createRow(pmtctpos); rw3shetPMTCT.setHeightInPoints(25); for (int positionPMTCT = 0; positionPMTCT < dataPMTCT.length; positionPMTCT++) { String value = dataPMTCT[positionPMTCT]; c11 = rw3shetPMTCT.createCell(positionPMTCT); if (positionPMTCT > 4 && positionPMTCT < (dataPMTCT.length)) { c11.setCellValue(Double.parseDouble(value)); } else { c11.setCellValue(value); } c11.setCellStyle(stborder); if (positionPMTCT == 5 || positionPMTCT == 6 || positionPMTCT == 17 || positionPMTCT == 18 || positionPMTCT == 24 || positionPMTCT == 29 || positionPMTCT == 32 || positionPMTCT == 33) { c11.setCellStyle(styleHeader); } } pmtctpos++; } } } } // 711 REPORT // MALES int TestedAdultMale = 0, TestedAdultFemale = 0; int TestedChildMale = 0, TestedChildFemale = 0; int HIV_AdultMale = 0, HIV_AdultFemale = 0; int HIV_ChildMale = 0, HIV_ChildFemale = 0; double FemaleAdultTested; double FemaleTestedChild; double AdultFemaleHIV; double ChildFemaleHIV; double MaleAdultTested; double MaleTestedChild; double AdultMaleHIV; double ChildMaleHIV; double FemaleAdultTested1 = 0; double FemaleAdultTested4 = 0; double FemaleAdultTested9 = 0; double FemaleAdultTested14 = 0; double FemaleAdultTested19 = 0; double FemaleAdultTested24 = 0; double FemaleAdultTested49 = 0; double FemaleAdultTested50 = 0; double FemaleTestedChild1 = 0; double FemaleTestedChild4 = 0; double FemaleTestedChild9 = 0; double FemaleTestedChild14 = 0; double FemaleTestedChild19 = 0; double FemaleTestedChild24 = 0; double FemaleTestedChild49 = 0; double FemaleTestedChild50 = 0; double AdultFemaleHIV19Neg = 0; double AdultFemaleHIV24Neg = 0; double AdultFemaleHIV49Neg = 0; double AdultFemaleHIV50Neg = 0; double AdultFemaleHIV19 = 0; double AdultFemaleHIV24 = 0; double AdultFemaleHIV49 = 0; double AdultFemaleHIV50 = 0; double ChildFemaleHIV1 = 0; double ChildFemaleHIV4 = 0; double ChildFemaleHIV9 = 0; double ChildFemaleHIV14 = 0; double ChildFemaleHIV1Neg = 0; double ChildFemaleHIV4Neg = 0; double ChildFemaleHIV9Neg = 0; double ChildFemaleHIV14Neg = 0; // MALES double MaleAdultTested19Neg = 0; double MaleAdultTested21Neg = 0; double MaleAdultTested49Neg = 0; double MaleAdultTested50Neg = 0; double MaleAdultTested19 = 0; double MaleAdultTested24 = 0; double MaleAdultTested49 = 0; double MaleAdultTested50 = 0; double MaleTestedChild1 = 0; double MaleTestedChild4 = 0; double MaleTestedChild9 = 0; double MaleTestedChild14 = 0; double MaleTestedChild1Neg = 0; double MaleTestedChild4Neg = 0; double MaleTestedChild9Neg = 0; double MaleTestedChild14Neg = 0; double AdultMaleHIV19Neg = 0; double AdultMaleHIV24Neg = 0; double AdultMaleHIV49Neg = 0; double AdultMaleHIV50Neg = 0; double AdultMaleHIV19 = 0; double AdultMaleHIV24 = 0; double AdultMaleHIV49 = 0; double AdultMaleHIV50 = 0; double ChildMaleHIV1 = 0; double ChildMaleHIV4 = 0; double ChildMaleHIV9 = 0; double ChildMaleHIV14 = 0; double ChildMaleHIV1Neg = 0; double ChildMaleHIV4Neg = 0; double ChildMaleHIV9Neg = 0; double ChildMaleHIV14Neg = 0; double splitData = 0; int adderPos = 0; double childSplitData = 0; year = Integer.parseInt(request.getParameter("year")); reportDuration = request.getParameter("reportDuration"); // year=2015; // reportDuration="4"; String period1 = ""; String duration1 = ""; prevYear = year - 1; maxYearMonth = 0; // GET REPORT DURATION============================================ if (reportDuration.equals("1")) { duration1 = " moh711.yearmonth BETWEEN " + prevYear + "10 AND " + year + "09"; period1 = "DATIM ANNUAL DATA REPORT FOR PEPFAR YEAR : " + year; } else if (reportDuration.equals("2")) { semi_annual = request.getParameter("semi_annual"); // semi_annual="2"; if (semi_annual.equals("1")) { duration1 = " moh711.yearmonth BETWEEN " + prevYear + "10 AND " + year + "03"; period1 = "DATIM SEMI - ANNUAL DATA REPORT FOR PERIOD : OCT " + prevYear + " to MARCH " + year; } else { duration1 = " moh711.yearmonth BETWEEN " + year + "04 AND " + year + "09"; period1 = "DATIM SEMI - ANNUAL DATA REPORT FOR PERIOD : APRIL " + year + " to SEPT " + year; } } else if (reportDuration.equals("3")) { String startMonth, endMonth; quarter = request.getParameter("quarter"); // quarter="3"; String getMonths = "SELECT months,name FROM quarter WHERE id='" + quarter + "'"; conn.rs = conn.st.executeQuery(getMonths); if (conn.rs.next() == true) { String months[] = conn.rs.getString(1).split(","); startMonth = months[0]; endMonth = months[2]; if (quarter.equals("1")) { duration1 = " moh711.yearmonth BETWEEN " + prevYear + "" + startMonth + " AND " + prevYear + "" + endMonth; period1 = "DATIM QUARTERLY DATA REPORT FOR PERIOD : " + conn.rs.getString(2).replace("-", " " + prevYear + " TO ") + " " + prevYear + ""; } else { duration1 = " moh711.yearmonth BETWEEN " + year + "" + startMonth + " AND " + year + "" + endMonth; period1 = "DATIM QUARTERLY DATA REPORT FOR PERIOD : " + conn.rs.getString(2).replace("-", " " + year + " TO ") + " " + year + ""; } } } else if (reportDuration.equals("4")) { month = Integer.parseInt(request.getParameter("month")); // month=5; String getMonthName = "SELECT name FROM month WHERE id='" + month + "'"; conn.rs = conn.st.executeQuery(getMonthName); if (conn.rs.next() == true) { if (month >= 10) { duration1 = " moh711.yearmonth=" + prevYear + "" + month; period1 = "DATIM MONTHLY DATA REPORT FOR : " + conn.rs.getString(1) + "(" + prevYear + ")"; } else { duration1 = " moh711.yearmonth=" + year + "0" + month; period1 = "DATIM MONTHLY DATA REPORT FOR : " + conn.rs.getString(1) + "(" + year + ")"; } } } else { duration1 = ""; } FemaleAdultTested = 0; FemaleTestedChild = 0; AdultFemaleHIV = 0; ChildFemaleHIV = 0; double TotalTested = 0; double TotalPositiveFemale = 0; double TotalPositiveMale = 0; double TotalNegativeFemale = 0; double TotalNegativeMale = 0; // MALES MaleAdultTested = 0; MaleTestedChild = 0; AdultMaleHIV = 0; ChildMaleHIV = 0; double TotalPositive = 0; double TotalNegative = 0; String county = ""; String district = ""; String facilityname = ""; HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); HSSFCellStyle stylemainHeader = wb.createCellStyle(); stylemainHeader.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylemainHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylemainHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); stylemainHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylemainHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylemainHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); stylemainHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); stylemainHeader.setWrapText(true); shet3.setColumnWidth(0, 4000); shet3.setColumnWidth(1, 5000); shet3.setColumnWidth(2, 7000); shet3.setColumnWidth(6, 5000); // for (int i=1;i<=42;i++){ // shet3.setColumnWidth(i, 2000); // } HSSFRow rw0 = shet3.createRow(0); rw0.setHeightInPoints(30); HSSFCell c1, c2, c3, c4, c5, c6, c7, c8; c1 = rw0.createCell(0); c1.setCellValue(period); c1.setCellStyle(stylemainHeader); for (int j = 1; j <= 43; j++) { c1 = rw0.createCell(j); c1.setCellStyle(stylemainHeader); } shet3.addMergedRegion(new CellRangeAddress(0, 0, 0, 17)); HSSFCell c12, c13, c14, c15, c16, c17, c18, c19, c20, c110, c111, c112, c113, c114, c115, c116, c117, c118, c219; HSSFCell c119, c120, c121, c122, c123, c124, c125, c126, c127, c128, c129, c130, c131, c132, c133, c134, c135, c136, c137; HSSFCell c211, c212, c213, c214, c215, c216, c217; rw0 = shet3.createRow(2); rw0.setHeightInPoints(30); c211 = rw0.createCell(0); c212 = rw0.createCell(1); c213 = rw0.createCell(2); c214 = rw0.createCell(3); c215 = rw0.createCell(4); c216 = rw0.createCell(5); c217 = rw0.createCell(6); c211.setCellValue("COUNTY"); c212.setCellValue("SUB-COUNTY"); c213.setCellValue("FACILITY"); c214.setCellValue("MFL-CODE"); c215.setCellValue("TYPE OF SUPPORT"); c11 = rw0.createCell(7); c12 = rw0.createCell(8); c13 = rw0.createCell(9); c14 = rw0.createCell(10); c15 = rw0.createCell(11); c16 = rw0.createCell(12); c17 = rw0.createCell(13); c18 = rw0.createCell(14); c19 = rw0.createCell(15); c110 = rw0.createCell(16); c111 = rw0.createCell(17); c112 = rw0.createCell(18); c113 = rw0.createCell(19); c114 = rw0.createCell(20); c115 = rw0.createCell(21); c116 = rw0.createCell(22); c117 = rw0.createCell(23); c118 = rw0.createCell(24); c119 = rw0.createCell(25); c120 = rw0.createCell(26); c121 = rw0.createCell(27); c122 = rw0.createCell(28); c123 = rw0.createCell(29); c124 = rw0.createCell(30); c125 = rw0.createCell(31); c126 = rw0.createCell(32); c127 = rw0.createCell(33); c128 = rw0.createCell(34); c129 = rw0.createCell(35); c130 = rw0.createCell(36); c131 = rw0.createCell(37); c132 = rw0.createCell(38); c133 = rw0.createCell(39); c134 = rw0.createCell(40); c135 = rw0.createCell(41); c136 = rw0.createCell(42); c137 = rw0.createCell(43); c216.setCellValue("Total HIV+"); c217.setCellValue("Total HIV+ (F)"); c11.setCellValue("POSITIVE"); c12.setCellValue("FEMALE"); c216.setCellStyle(stylemainHeader); c217.setCellStyle(stylemainHeader); c11.setCellStyle(stylemainHeader); c12.setCellStyle(stylemainHeader); c13.setCellValue(""); c14.setCellValue(""); c15.setCellValue(""); c16.setCellValue(""); c17.setCellValue(""); c18.setCellValue(""); c19.setCellValue(""); c110.setCellValue(""); c111.setCellValue("MALE"); c111.setCellStyle(stylemainHeader); c112.setCellValue(""); c113.setCellValue(""); c114.setCellValue(""); c115.setCellValue(""); c116.setCellValue(""); c117.setCellValue(""); c118.setCellValue(""); // // rw0=shet3.createRow(2); // rw0.setHeightInPoints(20); c119.setCellValue("NEGATIVE"); c120.setCellValue("FEMALE"); c119.setCellStyle(stylemainHeader); c120.setCellStyle(stylemainHeader); c121.setCellValue(""); c122.setCellValue(""); c123.setCellValue(""); c124.setCellValue(""); c125.setCellValue(""); c126.setCellValue(""); c127.setCellValue(""); c128.setCellValue(""); c129.setCellValue("MALE"); c129.setCellStyle(stylemainHeader); c130.setCellValue(""); c131.setCellValue(""); c132.setCellValue(""); c133.setCellValue(""); c134.setCellValue(""); c135.setCellValue(""); c136.setCellValue(""); c137.setCellValue(""); for (int i = 0; i <= 22; i++) { c11 = rw0.getCell(i); c11.setCellStyle(stylemainHeader); } for (int i = 23; i <= 43; i++) { c11 = rw0.getCell(i); c11.setCellStyle(stylemainHeader); } shet3.addMergedRegion(new CellRangeAddress(2, 2, 8, 15)); shet3.addMergedRegion(new CellRangeAddress(2, 2, 17, 24)); shet3.addMergedRegion(new CellRangeAddress(2, 2, 26, 33)); shet3.addMergedRegion(new CellRangeAddress(2, 2, 35, 42)); rw0 = shet3.createRow(3); rw0.setHeightInPoints(30); c211 = rw0.createCell(0); c212 = rw0.createCell(1); c213 = rw0.createCell(2); c214 = rw0.createCell(3); c215 = rw0.createCell(4); c216 = rw0.createCell(5); c217 = rw0.createCell(6); // other data c11 = rw0.createCell(7); c12 = rw0.createCell(8); c13 = rw0.createCell(9); c14 = rw0.createCell(10); c15 = rw0.createCell(11); c16 = rw0.createCell(12); c17 = rw0.createCell(13); c18 = rw0.createCell(14); c19 = rw0.createCell(15); c110 = rw0.createCell(16); c111 = rw0.createCell(17); c112 = rw0.createCell(18); c113 = rw0.createCell(19); c114 = rw0.createCell(20); c115 = rw0.createCell(21); c116 = rw0.createCell(22); c117 = rw0.createCell(23); c118 = rw0.createCell(24); c11.setCellValue("Num"); c216.setCellValue("TOTAL HIV+"); c217.setCellValue("TOTAL +VE (F)"); c12.setCellValue("Paeds <15Yr"); c13.setCellValue(""); c14.setCellValue(""); c15.setCellValue(""); c16.setCellValue("Adults 15+Yr"); c17.setCellValue(""); c18.setCellValue(""); c19.setCellValue(""); c110.setCellValue("TOTAL +VE MALE"); c111.setCellValue("Paeds <15Yr"); c112.setCellValue(""); c113.setCellValue(""); c114.setCellValue(""); c115.setCellValue("Adults 15+Yr"); c116.setCellValue(""); c117.setCellValue(""); c118.setCellValue(""); c11 = rw0.createCell(25); c12 = rw0.createCell(26); c13 = rw0.createCell(27); c14 = rw0.createCell(28); c15 = rw0.createCell(29); c16 = rw0.createCell(30); c17 = rw0.createCell(31); c18 = rw0.createCell(32); c19 = rw0.createCell(33); c110 = rw0.createCell(34); c111 = rw0.createCell(35); c112 = rw0.createCell(36); c113 = rw0.createCell(37); c114 = rw0.createCell(38); c115 = rw0.createCell(39); c116 = rw0.createCell(40); c117 = rw0.createCell(41); c118 = rw0.createCell(42); c119 = rw0.createCell(43); c11.setCellValue("TOTAL -VE(F)"); c12.setCellValue("Paeds <15Yr"); c13.setCellValue(""); c14.setCellValue(""); c15.setCellValue(""); c16.setCellValue("Adults 15+Yr"); c17.setCellValue(""); c18.setCellValue(""); c19.setCellValue(""); c110.setCellValue("TOTAL -VE(M)"); c111.setCellValue("Paeds <15Yr"); c112.setCellValue(""); c113.setCellValue(""); c114.setCellValue(""); c115.setCellValue("Adults 15+Yr"); c116.setCellValue(""); c117.setCellValue(""); c118.setCellValue(""); c119.setCellValue("DQA"); for (int i = 0; i <= 22; i++) { c11 = rw0.getCell(i); c11.setCellStyle(styleHeader); } shet3.addMergedRegion(new CellRangeAddress(3, 3, 8, 11)); shet3.addMergedRegion(new CellRangeAddress(3, 3, 12, 15)); shet3.addMergedRegion(new CellRangeAddress(3, 3, 17, 20)); shet3.addMergedRegion(new CellRangeAddress(3, 3, 21, 24)); // for (int k = 23; k <= 43; k++) { c113 = rw0.getCell(k); c113.setCellStyle(styleHeader); } shet3.addMergedRegion(new CellRangeAddress(3, 3, 26, 29)); shet3.addMergedRegion(new CellRangeAddress(3, 3, 30, 33)); shet3.addMergedRegion(new CellRangeAddress(3, 3, 35, 38)); shet3.addMergedRegion(new CellRangeAddress(3, 3, 39, 42)); shet3.addMergedRegion(new CellRangeAddress(3, 4, 43, 43)); rw0 = shet3.createRow(4); rw0.setHeightInPoints(30); c211 = rw0.createCell(0); c212 = rw0.createCell(1); c213 = rw0.createCell(2); c214 = rw0.createCell(3); c215 = rw0.createCell(4); c216 = rw0.createCell(5); c217 = rw0.createCell(6); // for ther est c11 = rw0.createCell(7); c12 = rw0.createCell(8); c13 = rw0.createCell(9); c14 = rw0.createCell(10); c15 = rw0.createCell(11); c16 = rw0.createCell(12); c17 = rw0.createCell(13); c18 = rw0.createCell(14); c19 = rw0.createCell(15); c110 = rw0.createCell(16); c111 = rw0.createCell(17); c112 = rw0.createCell(18); c113 = rw0.createCell(19); c114 = rw0.createCell(20); c115 = rw0.createCell(21); c116 = rw0.createCell(22); c117 = rw0.createCell(23); c118 = rw0.createCell(24); c11.setCellValue("NUM"); c216.setCellValue("TOTAL HIV+"); c217.setCellValue("TOTAL +VE(F)"); c12.setCellValue("<1 "); c13.setCellValue("1-4Y"); c14.setCellValue("5-9Y"); c15.setCellValue("10-14Y"); c16.setCellValue("15-19Y"); c17.setCellValue("20-24Y"); c18.setCellValue("25-49Y"); c19.setCellValue("50+Y"); c110.setCellValue("TOTAL +VE MALE"); shet3.addMergedRegion(new CellRangeAddress(3, 4, 16, 16)); c111.setCellValue("<1"); c112.setCellValue("1-4Y"); c113.setCellValue("5-9Y"); c114.setCellValue("10-14Y"); c115.setCellValue("15-19Y"); c116.setCellValue("20-24Y"); c117.setCellValue("25-49Y"); c118.setCellValue("50+Y"); for (int i = 0; i <= 22; i++) { c11 = rw0.getCell(i); c11.setCellStyle(styleHeader); } // // for negative c11 = rw0.createCell(25); c12 = rw0.createCell(26); c13 = rw0.createCell(27); c14 = rw0.createCell(28); c15 = rw0.createCell(29); c16 = rw0.createCell(30); c17 = rw0.createCell(31); c18 = rw0.createCell(32); c19 = rw0.createCell(33); c110 = rw0.createCell(34); c111 = rw0.createCell(35); c112 = rw0.createCell(36); c113 = rw0.createCell(37); c114 = rw0.createCell(38); c115 = rw0.createCell(39); c116 = rw0.createCell(40); c117 = rw0.createCell(41); c118 = rw0.createCell(42); c119 = rw0.createCell(43); c11.setCellValue("TOTAL -VE(F)"); shet3.addMergedRegion(new CellRangeAddress(3, 4, 25, 25)); c12.setCellValue("<1"); c13.setCellValue("1-4Y"); c14.setCellValue("5-9Y"); c15.setCellValue("10-14Y"); c16.setCellValue("15-19Y"); c17.setCellValue("20-24Y"); c18.setCellValue("25-49Y"); c19.setCellValue("50+Y"); c110.setCellValue("TOTAL -VE(M)"); shet3.addMergedRegion(new CellRangeAddress(3, 4, 34, 34)); c111.setCellValue("<1"); c112.setCellValue("1-4Y"); c113.setCellValue("5-9Y"); c114.setCellValue("10-14Y"); c115.setCellValue("15-19Y"); c116.setCellValue("20-24Y"); c117.setCellValue("25-49Y"); c118.setCellValue("50+Y"); c119.setCellValue(""); for (int l = 20; l <= 43; l++) { c113 = rw0.getCell(l); c113.setCellStyle(styleHeader); } double checkdiff = 0; int count = 4; TestedAdultMale = 0; TestedAdultFemale = 0; TestedChildMale = 0; TestedChildFemale = 0; HIV_AdultMale = 0; HIV_AdultFemale = 0; HIV_ChildMale = 0; HIV_ChildFemale = 0; //--------------------------------------------------------------------------- String facilid = ""; String facilname = ""; String dsdta = ""; String get711data = "SELECT(sum(VCTClient_Tested_CF) +sum( VCTClient_Tested_AF)+sum(DTCB_Test_Out_AF)+sum(DTCB_Test_In_AF))" //ADULTS TESTED FEMALE + ",(sum(VCTClient_Tested_CM)+ sum(VCTClient_Tested_AM) + sum(DTCB_Test_Out_AM) + sum(DTCB_Test_In_AM))"//ADULTS TESTED MALES + ", (sum(VCTClient_HIV_CF)+ sum(VCTClient_HIV_AF)+sum(DTCC_HIV_In_AF)+ sum(DTCC_HIV_Out_AF))" // ADULTS HIV+ FEMALE + ",(sum(VCTClient_HIV_CM)+sum(VCTClient_HIV_AM)+ sum(DTCC_HIV_In_AM) +sum(DTCC_HIV_Out_AM)) " // ADULTS HIV+ MALE + ", (sum(DTCB_Test_Out_CF) + sum(DTCB_Test_In_CF))" // CHILDREN TOTAL TESTED FEMALE + ", (sum(DTCB_Test_Out_CM) + sum(DTCB_Test_In_CM))" // CHILDREN TOTAL TESTED MALE + ", ( sum(DTCC_HIV_In_CF)+ sum(DTCC_HIV_Out_CF))" // CHILDREN OSITIVE FEMALE + ", (sum(DTCC_HIV_In_CM)+ sum(DTCC_HIV_Out_CM)),county.County,district.DistrictNom," + "subpartnera.SubPartnerNom,subpartnera.CentreSanteId,subpartnera.HTC_Support1"// CHILDREN POSITIVE MALE + " FROM moh711 JOIN subpartnera " + "ON moh711.SubPartnerID=subpartnera.SubPartnerID " + "JOIN district ON subpartnera.DistrictID=district.DistrictID JOIN county ON " + "district.CountyID=county.CountyID" + " WHERE " + " " + facilityIds1 + " " + duration1 + " && subpartnera.HTC=1 " + "GROUP BY moh711.SubPartnerID "; System.out.println("711 : " + get711data); conn.rs = conn.st.executeQuery(get711data); while (conn.rs.next()) { county = conn.rs.getString(9); district = conn.rs.getString(10); facilityname = conn.rs.getString(11); mflcode = conn.rs.getString(12); dsdta = conn.rs.getString(13); TestedAdultFemale = conn.rs.getInt(1); TestedAdultMale = conn.rs.getInt(2); HIV_AdultFemale = conn.rs.getInt(3); HIV_AdultMale = conn.rs.getInt(4); TestedChildFemale = conn.rs.getInt(5); TestedChildMale = conn.rs.getInt(6); HIV_ChildFemale = conn.rs.getInt(7); HIV_ChildMale = conn.rs.getInt(8); System.out.println(facilityname + " KKK " + HIV_AdultFemale + " " + HIV_AdultMale + " " + HIV_ChildFemale + " " + HIV_ChildMale); System.out.println( facilityname + "TestedChildFemale " + TestedChildFemale + " HIV_ChildFemale " + HIV_ChildFemale + " TestedChildMale " + TestedChildMale + " HIV_ChildMale " + HIV_ChildMale); String basicDetails = county + "@" + district + "@" + facilityname + "@" + mflcode + "@" + dsdta; String arrayDetails[] = basicDetails.split("@"); count++; rw0 = shet3.createRow(count); int facilno = 0; for (int j = 0; j < arrayDetails.length; j++) { HSSFCell S3cell = rw0.createCell(facilno); S3cell.setCellValue(arrayDetails[j]); // System.out.println(arrayDetails[j]); S3cell.setCellStyle(stborder); System.out.println("facildet pos : " + facilno + " det : " + arrayDetails[j]); facilno++; } System.out.println(facilityname + " TestedAdultFemale " + TestedAdultFemale + "TestedAdultMale " + TestedAdultMale + " TestedChildFemale " + TestedChildFemale + " TestedChildMale " + TestedChildMale + " HIV_AdultFemale " + HIV_AdultFemale + " HIV_AdultMale " + HIV_AdultMale + " HIV_ChildFemale " + HIV_ChildFemale + " HIV_ChildMale " + HIV_ChildMale); // FEMALES FemaleAdultTested19 = (float) Math.round((0.05 * TestedAdultFemale)); FemaleAdultTested24 = (float) Math.round((0.11 * TestedAdultFemale)); FemaleAdultTested49 = (float) Math.round((0.72 * TestedAdultFemale)); FemaleAdultTested50 = (float) Math.round((0.12 * TestedAdultFemale)); FemaleTestedChild1 = (float) Math.round((0.05 * TestedChildFemale)); FemaleTestedChild4 = (float) Math.round((0.26 * TestedChildFemale)); FemaleTestedChild9 = (float) Math.round((0.29 * TestedChildFemale)); FemaleTestedChild14 = (float) Math.round((0.40 * TestedChildFemale)); //postive AdultFemaleHIV19 = (float) Math.round((0.02 * HIV_AdultFemale)); AdultFemaleHIV24 = (float) Math.round((0.09 * HIV_AdultFemale)); AdultFemaleHIV49 = (float) Math.round((0.79 * HIV_AdultFemale)); AdultFemaleHIV50 = (float) Math.round((0.10 * HIV_AdultFemale)); //positve ChildFemaleHIV1 = (float) Math.round((0.13 * HIV_ChildFemale)); ChildFemaleHIV4 = (float) Math.round((0.37 * HIV_ChildFemale)); ChildFemaleHIV9 = (float) Math.round((0.25 * HIV_ChildFemale)); ChildFemaleHIV14 = (float) Math.round((0.25 * HIV_ChildFemale)); // MALES MaleAdultTested19 = (float) Math.round((0.05 * TestedAdultMale)); MaleAdultTested24 = (float) Math.round((0.11 * TestedAdultMale)); MaleAdultTested49 = (float) Math.round((0.72 * TestedAdultMale)); MaleAdultTested50 = (float) Math.round((0.12 * TestedAdultMale)); MaleTestedChild1 = (float) Math.round((0.05 * TestedChildMale)); MaleTestedChild4 = (float) Math.round((0.26 * TestedChildMale)); MaleTestedChild9 = (float) Math.round((0.29 * TestedChildMale)); MaleTestedChild14 = (float) Math.round((0.40 * TestedChildMale)); //positive AdultMaleHIV19 = (float) Math.round((0.02 * HIV_AdultMale)); AdultMaleHIV24 = (float) Math.round((0.09 * HIV_AdultMale)); AdultMaleHIV49 = (float) Math.round((0.79 * HIV_AdultMale)); AdultMaleHIV50 = (float) Math.round((0.10 * HIV_AdultMale)); //positives ChildMaleHIV1 = (float) Math.round((0.13 * HIV_ChildMale)); ChildMaleHIV4 = (float) Math.round((0.37 * HIV_ChildMale)); ChildMaleHIV9 = (float) Math.round((0.25 * HIV_ChildMale)); ChildMaleHIV14 = (float) Math.round((0.25 * HIV_ChildMale)); // TestedAdultFemale=conn.rs.getInt(1); // TestedAdultMale=conn.rs.getInt(2); // HIV_AdultFemale=conn.rs.getInt(3); // HIV_AdultMale=conn.rs.getInt(4); // TestedChildFemale=conn.rs.getInt(5); // TestedChildMale=conn.rs.getInt(6); // HIV_ChildFemale=conn.rs.getInt(7); // HIV_ChildMale=conn.rs.getInt(8); // TotalTested=FemaleTestedChild1+FemaleTestedChild4+FemaleTestedChild9+FemaleTestedChild14+FemaleAdultTested19+FemaleAdultTested24+FemaleAdultTested49+FemaleAdultTested50+ MaleAdultTested19+MaleAdultTested24+MaleAdultTested49+MaleAdultTested50+MaleTestedChild1+MaleTestedChild4+MaleTestedChild9+MaleTestedChild14; // TotalNegativeFemale1=AdultFemaleHIV19Neg+AdultFemaleHIV24Neg+AdultFemaleHIV49Neg+AdultFemaleHIV50Neg+ ChildFemaleHIV1Neg+ChildFemaleHIV4Neg+ChildFemaleHIV9Neg+ChildFemaleHIV14Neg; // TotalNegativeMale1=AdultMaleHIV19Neg+AdultMaleHIV24Neg+AdultMaleHIV49Neg+AdultMaleHIV50Neg+ChildMaleHIV1Neg+ChildMaleHIV4Neg+ChildMaleHIV9Neg+ChildMaleHIV14Neg; //negative int neg1male = 0; int neg4male = 0; int neg9male = 0; int neg14male = 0; int neg19male = 0; int neg24male = 0; int neg49male = 0; int neg50male = 0; AdultMaleHIV19Neg = (float) Math.round(MaleAdultTested19) - (AdultMaleHIV19); AdultMaleHIV24Neg = (float) Math.round(MaleAdultTested24) - (AdultMaleHIV24); AdultMaleHIV49Neg = (float) Math.round(MaleAdultTested49) - (AdultMaleHIV49); AdultMaleHIV50Neg = (float) Math.round(MaleAdultTested50) - (AdultMaleHIV50); if (AdultMaleHIV19Neg <= -1) { neg19male = 1; } if (AdultMaleHIV24Neg <= -1) { neg24male = 1; } if (AdultMaleHIV49Neg <= -1) { neg49male = 1; } if (AdultMaleHIV50Neg <= -1) { neg50male = 1; } // child male negatives ChildMaleHIV1Neg = (float) Math.round(MaleTestedChild1) - (ChildMaleHIV1); ChildMaleHIV4Neg = (float) Math.round(MaleTestedChild4) - (ChildMaleHIV4); ChildMaleHIV9Neg = (float) Math.round(MaleTestedChild9) - (ChildMaleHIV9); ChildMaleHIV14Neg = (float) Math.round(MaleTestedChild14) - (ChildMaleHIV14); if (ChildMaleHIV1Neg <= -1) { neg1male = 1; } if (ChildMaleHIV4Neg <= -1) { neg4male = 1; } if (ChildMaleHIV9Neg <= -1) { neg9male = 1; } if (ChildMaleHIV14Neg <= -1) { neg14male = 1; } //negative int neg1female = 0; int neg4female = 0; int neg9female = 0; int neg14female = 0; int neg19female = 0; int neg24female = 0; int neg49female = 0; int neg50female = 0; ChildFemaleHIV1Neg = (float) Math.round(FemaleTestedChild1) - (ChildFemaleHIV1); ChildFemaleHIV4Neg = (float) Math.round(FemaleTestedChild4) - (ChildFemaleHIV4); ChildFemaleHIV9Neg = (float) Math.round(FemaleTestedChild9) - (ChildFemaleHIV9); ChildFemaleHIV14Neg = (float) Math.round(FemaleTestedChild14) - (ChildFemaleHIV14); if (ChildFemaleHIV1Neg <= -1) { neg1female = 1; } if (ChildFemaleHIV4Neg <= -1) { neg4female = 1; } if (ChildFemaleHIV9Neg <= -1) { neg9female = 1; } if (ChildFemaleHIV14Neg <= -1) { neg14female = 1; } System.out.println(facilityname + " fffff " + ChildFemaleHIV1Neg + " " + ChildFemaleHIV4Neg + " " + ChildFemaleHIV9Neg + " " + ChildFemaleHIV14Neg); //negative AdultFemaleHIV19Neg = (float) Math.round(FemaleAdultTested19) - (AdultFemaleHIV19); AdultFemaleHIV24Neg = (float) Math.round(FemaleAdultTested24) - (AdultFemaleHIV24); AdultFemaleHIV49Neg = (float) Math.round(FemaleAdultTested49) - (AdultFemaleHIV49); AdultFemaleHIV50Neg = (float) Math.round(FemaleAdultTested50) - (AdultFemaleHIV50); if (AdultFemaleHIV19Neg <= -1) { neg19female = 1; } if (AdultFemaleHIV24Neg <= -1) { neg24female = 1; } if (AdultFemaleHIV49Neg <= -1) { neg49female = 1; } if (AdultFemaleHIV50Neg <= -1) { neg50female = 1; } double totalpositivesmale = 0; double totalpositivesfemale = 0; double totalpositives = 0; double totalnegatives = 0; double totalfemalehiv = 0; double totalmalehiv = 0; double totalfemaletesteddis = 0; double totalmaletesteddis = 0; double totalfemaletested = 0; double totalmaletested = 0; double negfem = 0; double negmale = 0; int redalert = 0; int redalert1 = 0; int redalert2 = 0; int redalert3 = 0; int finalalert = 0; totalpositives = AdultFemaleHIV19 + AdultFemaleHIV24 + AdultFemaleHIV49 + AdultFemaleHIV50 + ChildFemaleHIV1 + ChildFemaleHIV4 + ChildFemaleHIV9 + ChildFemaleHIV14 + AdultMaleHIV19 + AdultMaleHIV24 + AdultMaleHIV49 + AdultMaleHIV50 + ChildMaleHIV1 + ChildMaleHIV4 + ChildMaleHIV9 + ChildMaleHIV14; totalnegatives = AdultFemaleHIV19Neg + AdultFemaleHIV24Neg + AdultFemaleHIV49Neg + AdultFemaleHIV50Neg + ChildFemaleHIV1Neg + ChildFemaleHIV4Neg + ChildFemaleHIV9Neg + ChildFemaleHIV14Neg + AdultMaleHIV19Neg + AdultMaleHIV24Neg + AdultMaleHIV49Neg + AdultMaleHIV50Neg + ChildMaleHIV1Neg + ChildMaleHIV4Neg + ChildMaleHIV9Neg + ChildMaleHIV14Neg; totalpositivesfemale = AdultFemaleHIV19 + AdultFemaleHIV24 + AdultFemaleHIV49 + AdultFemaleHIV50 + ChildFemaleHIV1 + ChildFemaleHIV4 + ChildFemaleHIV9 + ChildFemaleHIV14; totalpositivesmale = AdultMaleHIV19 + AdultMaleHIV24 + AdultMaleHIV49 + AdultMaleHIV50 + ChildMaleHIV1 + ChildMaleHIV4 + ChildMaleHIV9 + ChildMaleHIV14; // // total tested after distribution totalfemaletesteddis = FemaleTestedChild1 + FemaleTestedChild4 + FemaleTestedChild9 + FemaleTestedChild14 + FemaleAdultTested19 + FemaleAdultTested24 + FemaleAdultTested49 + FemaleAdultTested50; totalmaletesteddis = MaleAdultTested19 + MaleAdultTested24 + MaleAdultTested49 + MaleAdultTested50 + MaleTestedChild1 + MaleTestedChild4 + MaleTestedChild9 + MaleTestedChild14; // totaltested totalfemaletested = TestedAdultFemale + TestedChildFemale; totalmaletested = TestedAdultMale + TestedChildMale; //poistives totalfemalehiv = HIV_AdultFemale + HIV_ChildFemale; totalmalehiv = HIV_AdultMale + HIV_ChildMale; // negative negfem = totalfemaletested - totalfemalehiv; negmale = totalmaletested - totalmalehiv; double TotalNegativeFemale1 = 0; double TotalNegativeMale1 = 0; TotalNegativeFemale1 = AdultFemaleHIV19Neg + AdultFemaleHIV24Neg + AdultFemaleHIV49Neg + AdultFemaleHIV50Neg + ChildFemaleHIV1Neg + ChildFemaleHIV4Neg + ChildFemaleHIV9Neg + ChildFemaleHIV14Neg; TotalNegativeMale1 = AdultMaleHIV19Neg + AdultMaleHIV24Neg + AdultMaleHIV49Neg + AdultMaleHIV50Neg + ChildMaleHIV1Neg + ChildMaleHIV4Neg + ChildMaleHIV9Neg + ChildMaleHIV14Neg; double checkdiff1 = 0; double checkdiff2 = 0; double checkdiff3 = 0; double totalcheckdiff = 0; int redfemalealert = 0; int redmalealert = 0; System.out.println(facilityname + " KKK " + HIV_AdultFemale + " " + HIV_AdultMale + " " + HIV_ChildFemale + " " + HIV_ChildMale); System.out.println( facilityname + "TestedChildFemale " + TestedChildFemale + " HIV_ChildFemale " + HIV_ChildFemale + " TestedChildMale " + TestedChildMale + " HIV_ChildMale " + HIV_ChildMale); double totaltestedmale1 = 0; double totaltestedfemale1 = 0; TotalTested = TestedChildFemale + TestedChildMale + TestedAdultMale + TestedAdultFemale; totaltestedmale1 = TestedChildMale + TestedAdultMale; totaltestedfemale1 = TestedChildFemale + TestedAdultFemale; TotalPositiveFemale = HIV_ChildFemale + HIV_AdultFemale; TotalPositiveMale = HIV_ChildMale + HIV_AdultMale; TotalPositive = HIV_ChildFemale + HIV_AdultFemale + HIV_ChildMale + HIV_AdultMale; TotalNegativeFemale = totaltestedfemale1 - TotalPositiveFemale; TotalNegativeMale = totaltestedmale1 - TotalPositiveMale; checkdiff = totalfemalehiv - totalpositivesfemale; // positive female if (checkdiff > 2 || checkdiff < -2) { redalert = 1; } // positive male checkdiff1 = totalmalehiv - totalpositivesmale; if (checkdiff1 > 2 || checkdiff1 < -2) { redalert1 = 1; } // negative female checkdiff2 = negfem - TotalNegativeFemale1; if (checkdiff2 > 2 || checkdiff2 < -2) { redalert2 = 1; } // negativemale checkdiff3 = negmale - TotalNegativeMale1; if (checkdiff3 > 2 || checkdiff3 < -2) { redalert3 = 1; } totalcheckdiff = TotalTested - (totalfemaletesteddis + totalmaletesteddis); if (totalcheckdiff > 2 || totalcheckdiff < -2) { finalalert = 1; } rw0.setHeightInPoints(25); c211 = rw0.createCell(0); c212 = rw0.createCell(1); c213 = rw0.createCell(2); c214 = rw0.createCell(3); c215 = rw0.createCell(4); c216 = rw0.createCell(5); c217 = rw0.createCell(6); // the rest c11 = rw0.createCell(7); c12 = rw0.createCell(8); c13 = rw0.createCell(9); c14 = rw0.createCell(10); c15 = rw0.createCell(11); c16 = rw0.createCell(12); c17 = rw0.createCell(13); c18 = rw0.createCell(14); c19 = rw0.createCell(15); c20 = rw0.createCell(16); c110 = rw0.createCell(17); c111 = rw0.createCell(18); c112 = rw0.createCell(19); c113 = rw0.createCell(20); c114 = rw0.createCell(21); c115 = rw0.createCell(22); c116 = rw0.createCell(23); c117 = rw0.createCell(24); //c11.setCellValue(facilname); //String basicDetails=county+"@"+district+"@"+facilityname+"@"+mflcode+"@"+dsdta; c211.setCellValue(county); c212.setCellValue(district); c213.setCellValue(facilityname); c214.setCellValue(mflcode); c215.setCellValue(dsdta); shet3.addMergedRegion(new CellRangeAddress(2, 4, 0, 0)); shet3.addMergedRegion(new CellRangeAddress(2, 4, 1, 1)); shet3.addMergedRegion(new CellRangeAddress(2, 4, 2, 2)); shet3.addMergedRegion(new CellRangeAddress(2, 4, 3, 3)); shet3.addMergedRegion(new CellRangeAddress(2, 4, 4, 4)); // Female c11.setCellValue(TotalTested); c216.setCellValue(TotalPositive); c217.setCellValue(TotalPositiveFemale); shet3.addMergedRegion(new CellRangeAddress(2, 4, 5, 5)); shet3.addMergedRegion(new CellRangeAddress(2, 4, 6, 6)); shet3.addMergedRegion(new CellRangeAddress(3, 4, 7, 7)); c12.setCellValue((float) Math.round(ChildFemaleHIV1)); c13.setCellValue((float) Math.round(ChildFemaleHIV4)); c14.setCellValue((float) Math.round(ChildFemaleHIV9)); c15.setCellValue((float) Math.round(ChildFemaleHIV14)); c16.setCellValue((float) Math.round(AdultFemaleHIV19)); c17.setCellValue((float) Math.round(AdultFemaleHIV24)); c18.setCellValue((float) Math.round(AdultFemaleHIV49)); c19.setCellValue((float) Math.round(AdultFemaleHIV50)); c20.setCellValue(TotalPositiveMale); //male c110.setCellValue((float) Math.round(ChildMaleHIV1)); c111.setCellValue((float) Math.round(ChildMaleHIV4)); c112.setCellValue((float) Math.round(ChildMaleHIV9)); c113.setCellValue((float) Math.round(ChildMaleHIV14)); c114.setCellValue((float) Math.round(AdultMaleHIV19)); c115.setCellValue((float) Math.round(AdultMaleHIV24)); c116.setCellValue((float) Math.round(AdultMaleHIV49)); c117.setCellValue((float) Math.round(AdultMaleHIV50)); for (int i = 0; i <= 22; i++) { c11 = rw0.getCell(i); c11.setCellStyle(stborder); if (redalert == 1) { c217 = rw0.getCell(6); c217.setCellStyle(redstyle); } if (redalert1 == 1) { c20 = rw0.getCell(16); c20.setCellStyle(redstyle); } } // shet3.addMergedRegion(new CellRangeAddress(2,5,0,0)); c11 = rw0.createCell(25); c12 = rw0.createCell(26); c13 = rw0.createCell(27); c14 = rw0.createCell(28); c15 = rw0.createCell(29); c16 = rw0.createCell(30); c17 = rw0.createCell(31); c18 = rw0.createCell(32); c19 = rw0.createCell(33); c110 = rw0.createCell(34); c111 = rw0.createCell(35); c112 = rw0.createCell(36); c113 = rw0.createCell(37); c114 = rw0.createCell(38); c115 = rw0.createCell(39); c116 = rw0.createCell(40); c117 = rw0.createCell(41); c118 = rw0.createCell(42); c119 = rw0.createCell(43); c11.setCellValue((float) Math.round(TotalNegativeFemale)); c12.setCellValue((float) Math.round(ChildFemaleHIV1Neg)); c13.setCellValue((float) Math.round(ChildFemaleHIV4Neg)); c14.setCellValue((float) Math.round(ChildFemaleHIV9Neg)); c15.setCellValue((float) Math.round(ChildFemaleHIV14Neg)); c16.setCellValue((float) Math.round(AdultFemaleHIV19Neg)); c17.setCellValue((float) Math.round(AdultFemaleHIV24Neg)); c18.setCellValue((float) Math.round(AdultFemaleHIV49Neg)); c19.setCellValue((float) Math.round(AdultFemaleHIV50Neg)); c110.setCellValue((float) Math.round(TotalNegativeMale)); c111.setCellValue((float) Math.round(ChildMaleHIV1Neg)); c112.setCellValue((float) Math.round(ChildMaleHIV4Neg)); c113.setCellValue((float) Math.round(ChildMaleHIV9Neg)); c114.setCellValue((float) Math.round(ChildMaleHIV14Neg)); c115.setCellValue((float) Math.round(AdultMaleHIV19Neg)); c116.setCellValue((float) Math.round(AdultMaleHIV24Neg)); c117.setCellValue((float) Math.round(AdultMaleHIV49Neg)); c118.setCellValue((float) Math.round(AdultMaleHIV50Neg)); c119.setCellValue(""); System.out.println(facilityname + " jjj " + AdultMaleHIV19Neg + "__________" + AdultMaleHIV24Neg + "__________" + AdultMaleHIV49Neg + "__________" + AdultMaleHIV50Neg + "__________" + ChildMaleHIV1Neg + "__________" + ChildMaleHIV4Neg + "__________" + ChildMaleHIV9Neg + "__________" + ChildMaleHIV14Neg); for (int i = 23; i <= 43; i++) { c11 = rw0.getCell(i); c11.setCellStyle(stborder); if (redalert2 == 1) { c11 = rw0.getCell(25); c11.setCellStyle(redstyle); } if (redalert3 == 1) { c110 = rw0.getCell(34); c110.setCellStyle(redstyle); } if (finalalert == 1) { c119 = rw0.getCell(43); c119.setCellStyle(redstyle); } if (neg1female == 1) { c12 = rw0.getCell(26); c12.setCellStyle(redstyle); } if (neg4female == 1) { c12 = rw0.getCell(27); c12.setCellStyle(redstyle); } if (neg9female == 1) { c13 = rw0.getCell(28); c13.setCellStyle(redstyle); } if (neg14female == 1) { c14 = rw0.getCell(29); c14.setCellStyle(redstyle); } if (neg19female == 1) { c15 = rw0.getCell(30); c15.setCellStyle(redstyle); } if (neg24female == 1) { c16 = rw0.getCell(31); c16.setCellStyle(redstyle); } if (neg49female == 1) { c17 = rw0.getCell(32); c17.setCellStyle(redstyle); } if (neg50female == 1) { c18 = rw0.getCell(33); c18.setCellStyle(redstyle); } //male if (neg1male == 1) { c111 = rw0.getCell(35); c111.setCellStyle(redstyle); } if (neg4male == 1) { c112 = rw0.getCell(36); c112.setCellStyle(redstyle); } if (neg9male == 1) { c113 = rw0.getCell(37); c113.setCellStyle(redstyle); } if (neg14male == 1) { c114 = rw0.getCell(38); c114.setCellStyle(redstyle); } if (neg19male == 1) { c115 = rw0.getCell(39); c115.setCellStyle(redstyle); } if (neg24male == 1) { c116 = rw0.getCell(40); c116.setCellStyle(redstyle); } if (neg49male == 1) { c117 = rw0.getCell(41); c117.setCellStyle(redstyle); } if (neg50male == 1) { c118 = rw0.getCell(42); c118.setCellStyle(redstyle); } } // shet3.addMergedRegion(new CellRangeAddress(2,5,20,20)); } 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=DATIM_VERIFICATION_REPORT_CREATED_ON_" + createdOn.trim() + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:reports.vmmcexcel.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {/*from ww w . j a v a2s . c o m*/ response.setContentType("text/html;charset=UTF-8"); session = request.getSession(); dbConn conn = new dbConn(); //get the existing data for the month, year and facility that is already on session String month = ""; String year = ""; String facil = "361"; String form = "vmmc"; //===================================================================================================== year = "2015"; month = "5"; String county = ""; String header = ""; String reportType = ""; if (request.getParameter("reportType") != null) { reportType = request.getParameter("reportType"); } String reportDuration = ""; if (request.getParameter("reportDuration") != null) { reportDuration = request.getParameter("reportDuration"); } if (request.getParameter("year") != null) { year = request.getParameter("year"); } if (request.getParameter("facility") != null && reportType.equals("2")) { facil = request.getParameter("facility"); String getfacil = "select SubPartnerNom,CentreSanteId as mflcode from subpartnera where SubPartnerID='" + facil + "'"; conn.rs = conn.st.executeQuery(getfacil); while (conn.rs.next()) { header += " FACILITY : " + conn.rs.getString(1).toUpperCase() + " MFL CODE : " + conn.rs.getString(2) + " "; } } if (request.getParameter("county") != null && reportType.equals("2")) { county = request.getParameter("county"); String getcounty = "select County from county where CountyID='" + county + "'"; conn.rs = conn.st.executeQuery(getcounty); while (conn.rs.next()) { header += " COUNTY : " + conn.rs.getString(1).toUpperCase() + " "; } } if (request.getParameter("month") != null && reportDuration.equals("4")) { month = request.getParameter("month"); String getmonth = "select name as monthname from month where id='" + month + "'"; conn.rs = conn.st.executeQuery(getmonth); while (conn.rs.next()) { header += " MONTH : " + conn.rs.getString(1).toUpperCase() + ""; } } header += " YEAR : " + year + ""; String facilitywhere = ""; String yearwhere = ""; String monthwhere = ""; String countywhere = ""; String duration = ""; String semi_annual = ""; String quarter = ""; //================================================================================================== //XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX int yearcopy = Integer.parseInt(year); // reportType="2"; // year=2015; // reportDuration="3"; String yearmonth = "" + year; int prevYear = yearcopy - 1; int maxYearMonth = 0; int monthcopy = 0; // GET REPORT DURATION============================================ if (reportDuration.equals("1")) { yearmonth += "_AnnualReport"; duration = " " + form + ".yearmonth BETWEEN " + prevYear + "10 AND " + year + "09"; } else if (reportDuration.equals("2")) { semi_annual = request.getParameter("semi_annual"); // semi_annual="2"; if (semi_annual.equals("1")) { yearmonth = prevYear + "_Oct_" + year + "_Mar"; duration = " " + form + ".yearmonth BETWEEN " + prevYear + "10 AND " + year + "03"; } else { yearmonth += "_Apr_Sep"; duration = " " + form + ".yearmonth BETWEEN " + year + "04 AND " + year + "09"; } } else if (reportDuration.equals("3")) { String startMonth, endMonth; quarter = request.getParameter("quarter"); // quarter="3"; String getMonths = "SELECT months,name FROM quarter WHERE id='" + quarter + "'"; conn.rs = conn.st.executeQuery(getMonths); if (conn.rs.next() == true) { String months[] = conn.rs.getString(1).split(","); startMonth = months[0]; endMonth = months[2]; if (quarter.equals("1")) { duration = " " + form + ".yearmonth BETWEEN " + prevYear + "" + startMonth + " AND " + prevYear + "" + endMonth; yearmonth = prevYear + "_" + conn.rs.getString(2); } else { yearmonth = year + "_" + conn.rs.getString(2); duration = " " + form + ".yearmonth BETWEEN " + year + "" + startMonth + " AND " + year + "" + endMonth; } } } else if (reportDuration.equals("4")) { monthcopy = Integer.parseInt(request.getParameter("month")); // month=5; if (monthcopy >= 10) { yearmonth = prevYear + "_" + month; duration = " " + form + ".yearmonth=" + prevYear + "" + month; } else { duration = " " + form + ".yearmonth=" + year + "0" + month; yearmonth = year + "_(" + month + ")"; } } else { duration = ""; } //====================================================================== //================================================================================================== //XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX String getexistingdata = ""; if (!county.equals("")) { countywhere = " and countyid = '" + county + "'"; } if (!facil.equals("") && reportType.equalsIgnoreCase("2")) { facilitywhere = " and " + form + ".SubPartnerID = '" + facil + "' "; } String joinedwhwere = " where 1=1 " + facilitywhere + " " + yearwhere + " && " + duration; //===================================================================================================== //===================================================================================================== //______________________________________________________________________________________ // NOW CREATE THE WORKSHEETS //______________________________________________________________________________________ HSSFWorkbook wb = new HSSFWorkbook(); //______________________________________________________________________________________ //______________________________________________________________________________________ HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); HSSFSheet shet = wb.createSheet(form); //create headers for that worksheet HSSFRow rw = shet.createRow(0); rw.setHeightInPoints(25); HSSFCell cl0 = rw.createCell(0); cl0.setCellValue("VOLUNTARY MALE CIRCUMCISION REPORTING FORM"); cl0.setCellStyle(stylex); for (int a = 1; a <= 4; a++) { HSSFCell clx = rw.createCell(a); clx.setCellValue(""); clx.setCellStyle(stylex); } HSSFRow rw1 = shet.createRow(1); rw1.setHeightInPoints(23); HSSFCell cl = rw1.createCell(0); cl.setCellValue(header); cl.setCellStyle(stylex); for (int a = 1; a <= 4; a++) { HSSFCell clx = rw1.createCell(a); clx.setCellValue(""); clx.setCellStyle(stylex); } HSSFRow rw2 = shet.createRow(2); rw2.setHeightInPoints(23); HSSFCell cl3 = rw2.createCell(0); cl3.setCellValue("P5.1.D:"); cl3.setCellStyle(style2); HSSFCell cl31 = rw2.createCell(1); cl31.setCellValue( "Number of Males Circumcised as part of the minimum package of MC for HIV prevention services:"); cl31.setCellStyle(stylex); for (int a = 2; a <= 4; a++) { HSSFCell clx = rw2.createCell(a); clx.setCellValue(""); clx.setCellStyle(stylex); } shet.addMergedRegion(new CellRangeAddress(2, 11, 0, 0)); shet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4)); shet.addMergedRegion(new CellRangeAddress(1, 1, 0, 4)); shet.addMergedRegion(new CellRangeAddress(2, 2, 1, 4)); shet.setColumnWidth(0, 2000); shet.setColumnWidth(1, 9000); shet.setColumnWidth(2, 5000); shet.setColumnWidth(3, 5000); shet.setColumnWidth(4, 5000); getexistingdata = "select sum(P51D1) as P51D1, sum(P51D9) as P51D9, sum(P51D10) as P51D10, sum(P51D19) as P51D19,sum(P51D24) as P51D24, sum(P51D29) as P51D29, sum(P51D49) as P51D49, sum(P51D50) as P51D50, sum(P51DT) as P51DT, sum(P521DM) as P521DM, sum(P521DS) as P521DS, sum(P521DT) as P521DT, sum(P522DM) as P522DM, sum(P522DS) as P522DS, sum(P522DT) as P522DT, sum(P52DM) as P52DM, sum(P52DS) as P52DS, sum(P52DT) as P52DT, sum(P511KP) as P511KP, sum(P511KN) as P511KN, sum(P511KU) as P511KU, sum(P511Surg) as P511Surg, sum(P511Dev) as P511Dev, sum(P53DF) as P53DF, sum(P53DO) as P53DO, sum(P53DM) as P53DM, sum(P53D) as P53D, sum(P54D) as P54D from " + form + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on " + form + ".SubPartnerID = subpartnera.SubPartnerID " + joinedwhwere + " "; System.out.println(getexistingdata); String P51D1 = ""; String P51D9 = ""; String P51D10 = ""; String P51D19 = ""; String P51D24 = ""; String P51D29 = ""; String P51D49 = ""; String P51D50 = ""; String P51DT = ""; String P521DM = ""; String P521DS = ""; String P521DT = ""; String P522DM = ""; String P522DS = ""; String P522DT = ""; String P52DM = ""; String P52DS = ""; String P52DT = ""; String P511KP = ""; String P511KN = ""; String P511KU = ""; String P511Surg = ""; String P511Dev = ""; String P53DF = ""; String P53DO = ""; String P53DM = ""; String P53D = ""; String P54D = ""; String distid = ""; if (session.getAttribute("subcountyid") != null) { distid = session.getAttribute("subcountyid").toString(); } int counter = 0; conn.rs = conn.st.executeQuery(getexistingdata); while (conn.rs.next()) { //now check if form was updated and if its one month after data entry //now load the column values here P51D1 = conn.rs.getString("P51D1"); if (P51D1 == null) { P51D1 = ""; } P51D9 = conn.rs.getString("P51D9"); if (P51D9 == null) { P51D9 = ""; } P51D10 = conn.rs.getString("P51D10"); if (P51D10 == null) { P51D10 = ""; } P51D19 = conn.rs.getString("P51D19"); if (P51D19 == null) { P51D19 = ""; } P51D24 = conn.rs.getString("P51D24"); if (P51D24 == null) { P51D24 = ""; } P51D29 = conn.rs.getString("P51D29"); if (P51D29 == null) { P51D29 = ""; } P51D49 = conn.rs.getString("P51D49"); if (P51D49 == null) { P51D49 = ""; } P51D50 = conn.rs.getString("P51D50"); if (P51D50 == null) { P51D50 = ""; } P51DT = conn.rs.getString("P51DT"); if (P51DT == null) { P51DT = ""; } P521DM = conn.rs.getString("P521DM"); if (P521DM == null) { P521DM = ""; } P521DS = conn.rs.getString("P521DS"); if (P521DS == null) { P521DS = ""; } P521DT = conn.rs.getString("P521DT"); if (P521DT == null) { P521DT = ""; } P522DM = conn.rs.getString("P522DM"); if (P522DM == null) { P522DM = ""; } P522DS = conn.rs.getString("P522DS"); if (P522DS == null) { P522DS = ""; } P522DT = conn.rs.getString("P522DT"); if (P522DT == null) { P522DT = ""; } P52DM = conn.rs.getString("P52DM"); if (P52DM == null) { P52DM = ""; } P52DS = conn.rs.getString("P52DS"); if (P52DS == null) { P52DS = ""; } P52DT = conn.rs.getString("P52DT"); if (P52DT == null) { P52DT = ""; } P511KP = conn.rs.getString("P511KP"); if (P511KP == null) { P511KP = ""; } P511KN = conn.rs.getString("P511KN"); if (P511KN == null) { P511KN = ""; } P511KU = conn.rs.getString("P511KU"); if (P511KU == null) { P511KU = ""; } P511Surg = conn.rs.getString("P511Surg"); if (P511Surg == null) { P511Surg = ""; } P511Dev = conn.rs.getString("P511Dev"); if (P511Dev == null) { P511Dev = ""; } P53DF = conn.rs.getString("P53DF"); if (P53DF == null) { P53DF = ""; } P53DO = conn.rs.getString("P53DO"); if (P53DO == null) { P53DO = ""; } P53DM = conn.rs.getString("P53DM"); if (P53DM == null) { P53DM = ""; } P53D = conn.rs.getString("P53D"); if (P53D == null) { P53D = ""; } P54D = conn.rs.getString("P54D"); if (P54D == null) { P54D = ""; } } String createdtable = ""; if (1 == 1) { if (1 == 1) { int r = 3; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("< 1"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51D1); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } if (1 == 1) { int r = 4; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("1-9"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51D9); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } //===================================================================================== if (1 == 1) { int r = 5; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("10-14"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51D10); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } //===================================================================================== if (1 == 1) { int r = 6; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("15-19"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51D19); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } //================================================================================== if (1 == 1) { int r = 7; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("20-24"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51D24); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } //================================================================================== if (1 == 1) { int r = 8; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("25-29"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51D29); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } //================================================================================== if (1 == 1) { int r = 9; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("30-49"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51D49); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } //================================================================================== if (1 == 1) { int r = 10; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("50 +"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51D50); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } //================================================================================== if (1 == 1) { int r = 11; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Total"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P51DT); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } //================================================================================== if (1 == 1) { int r = 12; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue("P5.2.D"); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue( "Number of Clients circumcised who experienced one or more moderate or severe adverse events(s)"); clx.setCellStyle(stylex); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(""); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 4)); shet.addMergedRegion(new CellRangeAddress(r, r + 4, 0, 0)); } //================================================================================== if (1 == 1) { int r = 13; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue(""); clx.setCellStyle(style2); HSSFCell clxm = rwx.createCell(2); clxm.setCellValue("Moderate"); clxm.setCellStyle(stylex); HSSFCell clxs = rwx.createCell(3); clxs.setCellValue("Severe"); clxs.setCellStyle(stylex); HSSFCell clxt = rwx.createCell(4); clxt.setCellValue("Total"); clxt.setCellStyle(stylex); //shet.addMergedRegion(new CellRangeAddress(r,r,1,3)); } //================================================================================== if (1 == 1) { int r = 14; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("During Circumcission"); clx.setCellStyle(stylex); HSSFCell clxm = rwx.createCell(2); clxm.setCellValue(P521DM); clxm.setCellStyle(style2); HSSFCell clxs = rwx.createCell(3); clxs.setCellValue(P521DS); clxs.setCellStyle(style2); HSSFCell clxt = rwx.createCell(4); clxt.setCellValue(P521DT); clxt.setCellStyle(style2); //shet.addMergedRegion(new CellRangeAddress(r,r,1,3)); } //================================================================================== if (1 == 1) { int r = 15; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Post CircumCission"); clx.setCellStyle(stylex); HSSFCell clxm = rwx.createCell(2); clxm.setCellValue(P522DM); clxm.setCellStyle(style2); HSSFCell clxs = rwx.createCell(3); clxs.setCellValue(P522DS); clxs.setCellStyle(style2); HSSFCell clxt = rwx.createCell(4); clxt.setCellValue(P522DT); clxt.setCellStyle(style2); //shet.addMergedRegion(new CellRangeAddress(r,r,1,3)); } //================================================================================== if (1 == 1) { int r = 16; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Total Adverse Events"); clx.setCellStyle(stylex); HSSFCell clxm = rwx.createCell(2); clxm.setCellValue(P52DM); clxm.setCellStyle(style2); HSSFCell clxs = rwx.createCell(3); clxs.setCellValue(P52DS); clxs.setCellStyle(style2); HSSFCell clxt = rwx.createCell(4); clxt.setCellValue(P52DT); clxt.setCellStyle(style2); //shet.addMergedRegion(new CellRangeAddress(r,r,1,3)); } //================================================================================== if (1 == 1) { int r = 17; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue("P5.1.1.K"); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("HIV Status of MC clients)"); clx.setCellStyle(stylex); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(""); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 4)); shet.addMergedRegion(new CellRangeAddress(r, r + 3, 0, 0)); } //================================================================================== if (1 == 1) { int r = 18; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Tested/self-reported positive"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P511KP); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //================================================================================== if (1 == 1) { int r = 19; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Tested negative"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P511KN); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //================================================================================== if (1 == 1) { int r = 20; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Unknown/self-reported negative"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P511KU); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //================================================================================== //================================================================================== if (1 == 1) { int r = 21; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue("P5.1.1.T"); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Circumcission Technique)"); clx.setCellStyle(stylex); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(""); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 4)); shet.addMergedRegion(new CellRangeAddress(r, r + 2, 0, 0)); } //================================================================================== if (1 == 1) { int r = 22; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Surgical VMMC"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P511Surg); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //================================================================================== if (1 == 1) { int r = 23; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Device-Based VMMC"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P511Dev); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //================================================================================== //================================================================================== if (1 == 1) { int r = 24; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue("P5.3.D:"); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue( "Number of locations providing MC surgery as part of the minimum package of MC for HIV prevention services within the reporting period "); clx.setCellStyle(stylex); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(""); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 4)); shet.addMergedRegion(new CellRangeAddress(r, r + 4, 0, 0)); } //================================================================================== if (1 == 1) { int r = 25; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Fixed/Static"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P53DF); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //================================================================================== if (1 == 1) { int r = 26; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Outreach"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P53DO); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //============================================================================================== //================================================================================== if (1 == 1) { int r = 27; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Mobile"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P53DM); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //============================================================================================== //================================================================================== if (1 == 1) { int r = 28; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue("Total"); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P53D); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //============================================================================================== //================================================================================== if (1 == 1) { int r = 29; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(28); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue("P5.3.D:"); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue( "Number of males circumcised within the reporting period who return at least once for postoperative follow?up care (routine or emergent) within 14 days of surgery"); clx.setCellStyle(stylex); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(""); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r, 1, 4)); shet.addMergedRegion(new CellRangeAddress(r, r + 1, 0, 0)); } //================================================================================== if (1 == 1) { int r = 30; HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell clx0 = rwx.createCell(0); clx0.setCellValue(""); clx0.setCellStyle(style2); HSSFCell clx = rwx.createCell(1); clx.setCellValue(""); clx.setCellStyle(style2); HSSFCell clx1 = rwx.createCell(4); clx1.setCellValue(P54D); clx1.setCellStyle(style2); for (int a = 2; a <= 3; a++) { HSSFCell clx2 = rwx.createCell(a); clx2.setCellValue(""); clx2.setCellStyle(style2); shet.addMergedRegion(new CellRangeAddress(r, r, 1, 3)); } } //================================================================================== } //System.out.println(createdtable); if (conn.conn != null) { conn.conn.close(); } if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st != null) { conn.st.close(); } if (conn.st2 != null) { conn.st2.close(); } IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=" + form + yearmonth + "_Generatted_On_" + createdOn + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); outStream.close(); } catch (SQLException ex) { Logger.getLogger(Vmmcpdf.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:ro.nextreports.engine.exporter.XlsExporter.java
License:Apache License
private HSSFCellStyle buildBandElementStyle(BandElement bandElement, Object value, int gridRow, int gridColumn, int colSpan) { Map<String, Object> style = buildCellStyleMap(bandElement, value, gridRow, gridColumn, colSpan); HSSFCellStyle cellStyle; HSSFFont cellFont = null;//from www . java 2 s . c o m int fontKey = -1; // we have to create new fonts and styles if some formatting conditions are met // also for subreports we may have a subreportCellStyle passed by ReportBandElement boolean cacheFont = false; boolean cacheAllFont = false; if ((modifiedStyle[gridRow][gridColumn]) || bean.isSubreport()) { fontKey = getFontKey(style); if (fontKey != -1) { cellFont = condFonts.get(fontKey); } cellStyle = wb.createCellStyle(); if (cellFont == null) { cellFont = wb.createFont(); cacheFont = true; } modifiedStyle[gridRow][gridColumn] = false; } else { cellStyle = styles[gridRow][gridColumn]; fontKey = getFontKey(style); if (fontKey != -1) { cellFont = fonts.get(fontKey); } if ((cellFont == null) && (bandElement != null)) { cellFont = wb.createFont(); cacheAllFont = true; } } // HSSFPalette cellPal = wb.getCustomPalette(); if (style.containsKey(StyleFormatConstants.FONT_FAMILY_KEY)) { String val = (String) style.get(StyleFormatConstants.FONT_FAMILY_KEY); cellFont.setFontName(val); } if (style.containsKey(StyleFormatConstants.FONT_SIZE)) { Float val = (Float) style.get(StyleFormatConstants.FONT_SIZE); cellFont.setFontHeightInPoints(val.shortValue()); } if (style.containsKey(StyleFormatConstants.FONT_COLOR)) { Color val = (Color) style.get(StyleFormatConstants.FONT_COLOR); cellFont.setColor(ExcelColorSupport.getNearestColor(val)); } if (style.containsKey(StyleFormatConstants.FONT_STYLE_KEY)) { if (StyleFormatConstants.FONT_STYLE_NORMAL.equals(style.get(StyleFormatConstants.FONT_STYLE_KEY))) { cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); } if (StyleFormatConstants.FONT_STYLE_BOLD.equals(style.get(StyleFormatConstants.FONT_STYLE_KEY))) { cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); } if (StyleFormatConstants.FONT_STYLE_ITALIC.equals(style.get(StyleFormatConstants.FONT_STYLE_KEY))) { cellFont.setItalic(true); } if (StyleFormatConstants.FONT_STYLE_BOLDITALIC.equals(style.get(StyleFormatConstants.FONT_STYLE_KEY))) { cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellFont.setItalic(true); } } if (cacheFont && (fontKey != -1)) { condFonts.put(fontKey, cellFont); } if (cacheAllFont && (fontKey != -1)) { fonts.put(fontKey, cellFont); } if (style.containsKey(StyleFormatConstants.BACKGROUND_COLOR)) { Color val = (Color) style.get(StyleFormatConstants.BACKGROUND_COLOR); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(ExcelColorSupport.getNearestColor(val)); } if (style.containsKey(StyleFormatConstants.HORIZONTAL_ALIGN_KEY)) { if (StyleFormatConstants.HORIZONTAL_ALIGN_LEFT .equals(style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) { cellStyle.setAlignment((short) 1); } if (StyleFormatConstants.HORIZONTAL_ALIGN_RIGHT .equals(style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) { cellStyle.setAlignment((short) 3); } if (StyleFormatConstants.HORIZONTAL_ALIGN_CENTER .equals(style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) { cellStyle.setAlignment((short) 2); } } if (style.containsKey(StyleFormatConstants.VERTICAL_ALIGN_KEY)) { if (StyleFormatConstants.VERTICAL_ALIGN_TOP .equals(style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) { cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); } if (StyleFormatConstants.VERTICAL_ALIGN_MIDDLE .equals(style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) { cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); } if (StyleFormatConstants.VERTICAL_ALIGN_BOTTOM .equals(style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) { cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_BOTTOM); } } else { cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); } short left = 0, right = 0, top = 0, bottom = 0; Color leftColor = Color.BLACK, rightColor = Color.BLACK, topColor = Color.BLACK, bottomColor = Color.BLACK; if (style.containsKey(StyleFormatConstants.BORDER_LEFT)) { Float val = (Float) style.get(StyleFormatConstants.BORDER_LEFT); // left = val.shortValue(); if (left == BORDER_THIN_VALUE) { cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); } if (left == BORDER_MEDIUM_VALUE) { cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); } if (left == BORDER_THICK_VALUE) { cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THICK); } Color color = (Color) style.get(StyleFormatConstants.BORDER_LEFT_COLOR); leftColor = color; cellStyle.setLeftBorderColor(ExcelColorSupport.getNearestColor(color)); } if (style.containsKey(StyleFormatConstants.BORDER_RIGHT)) { Float val = (Float) style.get(StyleFormatConstants.BORDER_RIGHT); // right = val.shortValue(); if (right == BORDER_THIN_VALUE) { cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); } if (right == BORDER_MEDIUM_VALUE) { cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); } if (right == BORDER_THICK_VALUE) { cellStyle.setBorderRight(HSSFCellStyle.BORDER_THICK); } Color color = (Color) style.get(StyleFormatConstants.BORDER_RIGHT_COLOR); rightColor = color; cellStyle.setRightBorderColor(ExcelColorSupport.getNearestColor(color)); } if (style.containsKey(StyleFormatConstants.BORDER_TOP)) { Float val = (Float) style.get(StyleFormatConstants.BORDER_TOP); // top = val.shortValue(); if (top == BORDER_THIN_VALUE) { cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); } if (top == BORDER_MEDIUM_VALUE) { cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); } if (top == BORDER_THICK_VALUE) { cellStyle.setBorderTop(HSSFCellStyle.BORDER_THICK); } Color color = (Color) style.get(StyleFormatConstants.BORDER_TOP_COLOR); topColor = color; cellStyle.setTopBorderColor(ExcelColorSupport.getNearestColor(color)); } if (style.containsKey(StyleFormatConstants.BORDER_BOTTOM)) { Float val = (Float) style.get(StyleFormatConstants.BORDER_BOTTOM); // bottom = val.shortValue(); if (bottom == BORDER_THIN_VALUE) { cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); } if (bottom == BORDER_MEDIUM_VALUE) { cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); } if (bottom == BORDER_THICK_VALUE) { cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THICK); } Color color = (Color) style.get(StyleFormatConstants.BORDER_BOTTOM_COLOR); bottomColor = color; cellStyle.setBottomBorderColor(ExcelColorSupport.getNearestColor(color)); } border = new Border(left, right, top, bottom); border.setLeftColor(leftColor); border.setRightColor(rightColor); border.setTopColor(topColor); border.setBottomColor(bottomColor); if (cellFont != null) { cellStyle.setFont(cellFont); } if (style.containsKey(StyleFormatConstants.PATTERN)) { String pattern = (String) style.get(StyleFormatConstants.PATTERN); HSSFDataFormat format = wb.createDataFormat(); cellStyle.setDataFormat(format.getFormat(pattern)); } if (bandElement != null) { cellStyle.setWrapText(bandElement.isWrapText()); } cellStyle = updateSubreportBandElementStyle(cellStyle, bandElement, value, gridRow, gridColumn, colSpan); return cellStyle; }
From source file:send.sms.az.util.SqlExcel.java
License:Open Source License
/** * This method generates an excel sheet containing data from the given Map. * The name of the excel sheet will be the String passed as a parameter. *///from w ww. ja v a2 s .c o m public void generateExcel(Map<String, LinkedHashMap<String, String>> resultMap, String name) { FileOutputStream fileOut = null; try { HSSFWorkbook wb = new HSSFWorkbook(); HSSFCellStyle headerStyle = wb.createCellStyle(); HSSFSheet sheet3 = wb.createSheet(name); HSSFFont headerFont = wb.createFont(); // headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index); headerStyle.setFillBackgroundColor(HSSFColor.WHITE.index); headerStyle.setFont(headerFont); try { fileOut = new FileOutputStream("C:\\" + name + ".xls"); } catch (FileNotFoundException e) { e.printStackTrace(); } HSSFRow sessionname = sheet3.createRow(2); HSSFCell title = sessionname.createCell(3); title.setCellStyle(headerStyle); title.setCellValue(name); HSSFRow row = sheet3.createRow(5); Map<String, LinkedHashMap<String, String>> rMap = resultMap; Map<String, String> columnDetails = rMap.get("1"); Set<String> s = columnDetails.keySet(); int cellNo = 0; for (String s1 : s) { HSSFCell cell0 = row.createCell(cellNo); cell0.setCellStyle(headerStyle); cell0.setCellValue(s1); cellNo++; } for (int i = 1; i <= rMap.size(); i++) { columnDetails = rMap.get(new Integer(i).toString()); System.out.println(i); HSSFRow nextrow = sheet3.createRow(5 + i); Set<String> set = columnDetails.keySet(); int cellNum = 0; for (String s2 : set) { nextrow.createCell(cellNum).setCellValue(columnDetails.get(s2)); cellNum++; } } sheet3.autoSizeColumn(0); sheet3.autoSizeColumn(1); sheet3.autoSizeColumn(2); sheet3.autoSizeColumn(3); wb.write(fileOut); fileOut.flush(); fileOut.close(); } catch (FileNotFoundException fe) { fe.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { try { fileOut.flush(); fileOut.close(); } catch (IOException e) { e.printStackTrace(); } } }
From source file:temp1.ExportExcel.java
public void export() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet hs = wb.createSheet();/*www. ja va2 s . c o m*/ TableModel tm = table.getModel(); int row = tm.getRowCount(); int cloumn = tm.getColumnCount(); HSSFCellStyle style = wb.createCellStyle(); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 11); style.setFont(font); HSSFCellStyle style1 = wb.createCellStyle(); style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); style1.setBorderLeft(HSSFCellStyle.BORDER_THIN); style1.setBorderRight(HSSFCellStyle.BORDER_THIN); style1.setBorderTop(HSSFCellStyle.BORDER_THIN); style1.setFillForegroundColor(HSSFColor.ORANGE.index); style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font1 = wb.createFont(); font1.setFontHeightInPoints((short) 15); font1.setBoldweight((short) 700); style1.setFont(font); for (int i = 0; i < row + 1; i++) { HSSFRow hr = hs.createRow(i); for (int j = 0; j < cloumn; j++) { if (i == 0) { String value = tm.getColumnName(j); int len = value.length(); hs.setColumnWidth((short) j, (short) (len * 400)); HSSFRichTextString srts = new HSSFRichTextString(value); HSSFCell hc = hr.createCell((short) j); hc.setEncoding((short) 1); hc.setCellStyle(style1); hc.setCellValue(srts); } else { System.out.println("vlue " + tm.getValueAt(i - 1, j)); if (tm.getValueAt(i - 1, j) != null) { String value = tm.getValueAt(i - 1, j).toString(); HSSFRichTextString srts = new HSSFRichTextString(value); HSSFCell hc = hr.createCell((short) j); hc.setEncoding((short) 1); hc.setCellStyle(style); if (value.equals("") || value == null) { hc.setCellValue(new HSSFRichTextString("")); } else { hc.setCellValue(srts); } } } } } try { wb.write(fos); fos.close(); } catch (IOException ex) { ex.printStackTrace(); } }
From source file:uk.ac.manchester.cs.owl.semspreadsheets.model.hssf.impl.CellHSSFImpl.java
License:BSD License
private HSSFCellStyle getFillStyleForColour(Color colour) { Map<Color, HSSFCellStyle> styles = colourStylesForWorkbook.get(getWorkbook()); if (styles == null) { styles = new HashMap<Color, HSSFCellStyle>(); colourStylesForWorkbook.put(getWorkbook(), styles); }//from w w w . j a v a 2 s . c o m HSSFCellStyle style = styles.get(colour); if (style == null) { HSSFColor col = translateColour(colour); style = getWorkbook().createCellStyle(); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setFillForegroundColor(col.getIndex()); styles.put(colour, style); } return style; }
From source file:uk.co.spudsoft.birt.emitters.excel.StyleManagerHUtils.java
License:Open Source License
@Override public void addBackgroundColourToStyle(Workbook workbook, CellStyle style, String colour) { if (colour == null) { return;//w w w.j a va 2s. c om } if (IStyle.TRANSPARENT_VALUE.equals(colour)) { return; } if (style instanceof HSSFCellStyle) { HSSFCellStyle cellStyle = (HSSFCellStyle) style; short colourIndex = getHColour((HSSFWorkbook) workbook, colour); if (colourIndex > 0) { cellStyle.setFillForegroundColor(colourIndex); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); } } }