List of usage examples for org.apache.poi.ss.usermodel CellStyle setBorderBottom
void setBorderBottom(BorderStyle border);
From source file:PPMP.ppmpreport_2.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { response.setContentType("text/html;charset=UTF-8"); try {//from w w w . java2 s .com /* TODO output your page here. You may use following sample code. */ //create ppmp report here //define some variables for keeping number of columns. // this should be dynamic because of the annual cumulatives depending on the selected year //the minimum year is 2011 // int selectedyear = 2016; int projectstartyear = 2011; int minimumcolumns = 11;//this is if the year is 2011 int currentcolumns = minimumcolumns + (selectedyear - projectstartyear); String selectedQTR = "Q2"; if (request.getParameter("year") != null) { selectedyear = new Integer(request.getParameter("year")); } if (request.getParameter("year") != null) { selectedQTR = request.getParameter("quarter"); } HSSFWorkbook wb = new HSSFWorkbook(); Calendar cal = Calendar.getInstance(); int year = cal.get(Calendar.YEAR); String month = String.format("%02d", cal.get(Calendar.MONTH) + 1); String date = String.format("%02d", cal.get(Calendar.DATE)); String hour = String.format("%02d", cal.get(Calendar.HOUR_OF_DAY)); String min = String.format("%02d", cal.get(Calendar.MINUTE)); String sec = String.format("%02d", cal.get(Calendar.SECOND)); String generationtime = "(" + year + "_" + month + "_" + date + ")_" + hour + "-" + min + "-" + sec; //______________________________________________________________________________________ //______________________________________________________________________________________ HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Arial Narrow"); font.setColor((short) 0000); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setBorderTop(HSSFCellStyle.BORDER_THICK); style.setBorderBottom(HSSFCellStyle.BORDER_THICK); style.setBorderLeft(HSSFCellStyle.BORDER_THICK); style.setBorderRight(HSSFCellStyle.BORDER_THICK); style.setAlignment(HSSFCellStyle.ALIGN_LEFT); style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setWrapText(true); CellStyle spstyle = wb.createCellStyle(); spstyle.setFont(font); spstyle.setBorderTop(HSSFCellStyle.BORDER_THICK); spstyle.setBorderBottom(HSSFCellStyle.BORDER_THICK); spstyle.setBorderLeft(HSSFCellStyle.BORDER_THICK); spstyle.setBorderRight(HSSFCellStyle.BORDER_THICK); spstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); spstyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); spstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); spstyle.setWrapText(true); System.out.println("Blue index:" + HSSFColor.BLUE.index); HSSFFont font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THICK); style2.setBorderBottom(HSSFCellStyle.BORDER_THICK); style2.setBorderLeft(HSSFCellStyle.BORDER_THICK); style2.setBorderRight(HSSFCellStyle.BORDER_THICK); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style2.setWrapText(true); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THICK); stborder.setBorderBottom(HSSFCellStyle.BORDER_THICK); stborder.setBorderLeft(HSSFCellStyle.BORDER_THICK); stborder.setBorderRight(HSSFCellStyle.BORDER_THICK); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.WHITE.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THICK); stylex.setBorderBottom(HSSFCellStyle.BORDER_THICK); stylex.setBorderLeft(HSSFCellStyle.BORDER_THICK); stylex.setBorderRight(HSSFCellStyle.BORDER_THICK); stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Arial Narrow"); fontx.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); fontx.setFontHeightInPoints((short) 16); stylex.setFont(fontx); stylex.setWrapText(true); HSSFSheet shet = wb.createSheet("PPMP " + selectedyear + " Report "); //create headers for that worksheet HSSFRow rw = shet.createRow(0); rw.setHeightInPoints(25); HSSFCell cl0 = rw.createCell(0); cl0.setCellValue("PROJECT PERFORMANCE MONITORING PLAN (PPMP)"); cl0.setCellStyle(stylex); for (int a = 1; a < currentcolumns; a++) { HSSFCell clx = rw.createCell(a); clx.setCellValue(""); clx.setCellStyle(stylex); } //merge row one shet.addMergedRegion(new CellRangeAddress(0, 0, 0, currentcolumns - 1)); //firt row ArrayList headerone = new ArrayList(); //headerone.add("Sub Purpose"); headerone.add("Output"); headerone.add("Indicators"); headerone.add("Baseline"); headerone.add("Year " + selectedyear + " Target"); headerone.add(selectedyear + " Quarterly Achievements "); headerone.add(""); headerone.add(""); headerone.add(""); headerone.add("Cumulative Year Achievements"); //the header Cumulative Year Achievements could be in the report depending on the selected year //for 2011, its not expected to appear in the report for (int a = 0; a <= (selectedyear - projectstartyear); a++) { if (a == 0) { //do nothing } else { headerone.add(""); } } headerone.add("Percentage (%) Achieved vs Year " + selectedyear); //header two which contains quartersa dn yearly achievement ArrayList headertwo = new ArrayList(); //headertwo.add("Sub Purpose"); headertwo.add("Output"); headertwo.add("Indicators"); headertwo.add("Baseline"); headertwo.add(""); headertwo.add("Oct-Dec " + (selectedyear - 1)); headertwo.add("Jan-Mar"); headertwo.add("Apr-Jun"); headertwo.add("Jul-Sep"); headertwo.add(selectedyear); //the header Cumulative Year Achievements could be in the report depending on the selected year //for 2011, its not expected to appear in the report for (int a = 0; a <= (selectedyear - projectstartyear); a++) { if (a == 0) { //do nothing } else { headertwo.add(selectedyear - a); //eg 2016, 2015, 2014 ... } } headertwo.add(""); //================================================================================= //DISPLAY HEADER DISPLAY HEADER DISPLAY HEADER DISPLAY HEADER DISPLAY HEADER //================================================================================= //display the header values for row one and two HSSFRow rw1 = shet.createRow(1); for (int a = 0; a < headerone.size(); a++) { HSSFCell cellx = rw1.createCell(a); cellx.setCellValue(headerone.get(a).toString()); cellx.setCellStyle(style); shet.setColumnWidth(a, 3500); } //===================row 2======================= //display the header values for row one and two HSSFRow rw2 = shet.createRow(2); rw2.setHeightInPoints(35); for (int a = 0; a < headertwo.size(); a++) { HSSFCell cellx = rw2.createCell(a); if (headertwo.get(a).toString().startsWith("20")) { cellx.setCellValue(new Integer(headertwo.get(a).toString())); } else { cellx.setCellValue(headertwo.get(a).toString()); } cellx.setCellStyle(style); } // shet.addMergedRegion(new CellRangeAddress(start row, end row, start column ,end column)); String mergingarray[] = { "1_2_0_0", "1_2_1_1", "1_2_2_2", "1_2_3_3", "1_1_4_7", "1_1_8_" + (8 + (selectedyear - projectstartyear)), "1_2_" + (8 + (selectedyear - projectstartyear) + 1) + "_" + (8 + (selectedyear - projectstartyear) + 1) }; for (int a = 0; a < mergingarray.length; a++) { String content[] = mergingarray[a].split("_"); shet.addMergedRegion(new CellRangeAddress(new Integer(content[0]), new Integer(content[1]), new Integer(content[2]), new Integer(content[3]))); } //=================================================================================== //DISPLAY VALUES DISPLAY VALUES DISPLAY VALUES DISPLAY VALUES DISPLAY VALUES //=================================================================================== String getindicators = "select * from indicatortitles where active='yes' order by subpurpose, output , title "; dbConnect conn = new dbConnect(); conn.rs = conn.state.executeQuery(getindicators); int rownumber = 3; shet.setColumnWidth(1, 14000); shet.setColumnWidth(2, 2300); shet.setColumnWidth(0, 7000); shet.setColumnWidth(3, 2300); shet.setColumnWidth(4, 2300); shet.setColumnWidth(5, 2300); shet.setColumnWidth(6, 2300); shet.setColumnWidth(7, 2300); shet.setColumnWidth(8, 2300); shet.setColumnWidth(9, 2300); shet.setColumnWidth(10, 2300); shet.setColumnWidth(11, 2300); shet.setColumnWidth(12, 2300); shet.setColumnWidth(13, 2300); shet.setColumnWidth(14, 2300); ArrayList sp = new ArrayList(); int subpurposerow = 3; int subpurposerowcopy = 3; ArrayList op = new ArrayList(); int outputrow = 3; int outputrowcopy = 4; int colpos = 0; int count = 0; while (conn.rs.next()) { System.out.println("SP__" + conn.rs.getString("subpurpose")); //merge subpartner row sp.add(conn.rs.getString("subpurpose")); if (sp.size() > 1) { //check if subporpose has changed if (!sp.get(count).toString().equals(sp.get(count - 1).toString())) { subpurposerow = rownumber; //===================================================subpurpose======================== HSSFRow rwxa = shet.createRow(rownumber); HSSFCell cl01 = rwxa.createCell(colpos); cl01.setCellValue(conn.rs.getString("subpurpose")); cl01.setCellStyle(spstyle); subpurposerowcopy = subpurposerow; //should merge entire row shet.addMergedRegion(new CellRangeAddress(subpurposerow, subpurposerow, 0, 15)); rownumber++; } } else { subpurposerow = rownumber; //===================================================subpurpose======================== HSSFRow rwxa = shet.createRow(rownumber); HSSFCell cl01 = rwxa.createCell(colpos); cl01.setCellValue(conn.rs.getString("subpurpose")); cl01.setCellStyle(spstyle); subpurposerowcopy = subpurposerow; //should merge entire row shet.addMergedRegion(new CellRangeAddress(subpurposerow, subpurposerow, 0, 15)); rownumber++; } String outputval = ""; if (conn.rs.getString("output") != null) { outputval = conn.rs.getString("output"); } //merge ouput rows op.add(outputval); if (op.size() > 1) { //check if out has changed if (!op.get(count).toString().equals(op.get(count - 1).toString())) { outputrow = rownumber; //should merge shet.addMergedRegion(new CellRangeAddress(outputrowcopy, outputrow - 1, 0, 0)); outputrowcopy = outputrow; System.out.println(" Comparison :" + op.get(count).toString() + " %%% " + op.get(count - 1).toString()); } } //now output the first part of the report HSSFRow rwx = shet.createRow(rownumber); //====================================================output============================= HSSFCell cl02 = rwx.createCell(colpos); cl02.setCellValue(conn.rs.getString("output")); cl02.setCellStyle(style2); colpos++; //===================================================indicators=========================== HSSFCell cl03 = rwx.createCell(colpos); cl03.setCellValue(conn.rs.getString("title")); cl03.setCellStyle(style2); colpos++; //====================================================baseline============================= HSSFCell cl04 = rwx.createCell(colpos); cl04.setCellValue(conn.rs.getString("totalbaseline")); cl04.setCellStyle(style2); colpos++; //====================================================targets================================= String gettargets = " select sum(target_combined) as target from yearly_targets where indicator_id='" + conn.rs.getString("titleID") + "' and year='" + selectedyear + "' "; //for percent indicators, get avg if (conn.rs.getInt("percentage") == 1) { gettargets = "select avg(target_combined) as target from yearly_targets where indicator_id='" + conn.rs.getString("titleID") + "' and year='" + selectedyear + "' "; } HSSFCell cl05 = rwx.createCell(colpos); colpos++; int annualtarget = 1; conn.rs1 = conn.state1.executeQuery(gettargets); if (conn.rs1.next()) { //set the target cl05.setCellValue(conn.rs1.getInt("target")); if (conn.rs.getInt("percentage") == 1) { if (conn.rs1.getInt("target") < 200) { cl05.setCellValue(conn.rs1.getInt("target") + "%"); } else { cl05.setCellValue(conn.rs1.getInt("target")); } } else { cl05.setCellValue(conn.rs1.getInt("target")); } cl05.setCellStyle(style2); if (conn.rs1.getString("target") != null) { if (!conn.rs1.getString("target").equals("")) { annualtarget = conn.rs1.getInt("target"); } } } //===========================================current year values==================================== String ispercent = ""; int highestvalue = 0; String getdata = " select sum(case when reportingPeriod='Q1' then totalAchieved end) as Q1, sum(case when reportingPeriod='Q2' then totalAchieved end) as Q2, sum(case when reportingPeriod='Q3' then totalAchieved end) as Q3, sum(case when reportingPeriod='Q4' then totalAchieved end) as Q4 where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear + "' group by titleID "; //for percent indicators, get avg if (conn.rs.getInt("percentage") == 1) { ispercent = "%"; if (conn.rs.getString("tableIdentifier").equals("1")) { //by gender and thus separate columns getdata = " select ROUND(AVG(case when reportingPeriod='Q1' then ((menAchieved + womenAchieved)/2) end)) as Q1, ROUND(AVG(case when reportingPeriod='Q2' then ((menAchieved + womenAchieved)/2) end)) as Q2, ROUND(AVG(case when reportingPeriod='Q3' then ((menAchieved + womenAchieved)/2) end)) as Q3, ROUND(AVG(case when reportingPeriod='Q4' then ((menAchieved + womenAchieved)/2) end)) as Q4 from indicatorachieved where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear + "' group by titleID "; System.out.println("@@" + getdata); } else { //combined i.e male and female getdata = " select ROUND(AVG(case when reportingPeriod='Q1' then totalAchieved end)) as Q1,ROUND(AVG(case when reportingPeriod='Q2' then totalAchieved end)) as Q2, ROUND(AVG(case when reportingPeriod='Q3' then totalAchieved end)) as Q3, ROUND(AVG(case when reportingPeriod='Q4' then totalAchieved end)) as Q4 from indicatorachievedcombined where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear + "' group by titleID "; System.out.println("@@" + getdata); } } //non percentages else { //if if (conn.rs.getString("tableIdentifier").equals("1")) { //by gender and thus separate columns getdata = " select sum(case when reportingPeriod='Q1' then (menAchieved + womenAchieved) end) as Q1,sum(case when reportingPeriod='Q2' then (menAchieved + womenAchieved) end) as Q2, sum(case when reportingPeriod='Q3' then (menAchieved + womenAchieved) end) as Q3, sum(case when reportingPeriod='Q4' then (menAchieved + womenAchieved) end) as Q4 from indicatorachieved where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear + "' group by titleID "; } else { getdata = " select sum(case when reportingPeriod='Q1' then totalAchieved end) as Q1, sum(case when reportingPeriod='Q2' then totalAchieved end) as Q2, sum(case when reportingPeriod='Q3' then totalAchieved end) as Q3, sum(case when reportingPeriod='Q4' then totalAchieved end) as Q4 from indicatorachievedcombined where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear + "' group by titleID "; } } String Q1 = ""; String Q2 = ""; String Q3 = ""; String Q4 = ""; conn.rs1 = conn.state1.executeQuery(getdata); //Q1 HSSFCell clQ1 = rwx.createCell(colpos); colpos++; HSSFCell clQ2 = rwx.createCell(colpos); colpos++; HSSFCell clQ3 = rwx.createCell(colpos); colpos++; HSSFCell clQ4 = rwx.createCell(colpos); colpos++; if (conn.rs1.next()) { if (conn.rs1.getString("Q1") != null) { if (!conn.rs1.getString("Q1").equals("")) { highestvalue = conn.rs1.getInt("Q1"); if (!ispercent.equals("")) { clQ1.setCellValue(conn.rs1.getInt("Q1") + ispercent); } else { clQ1.setCellValue(conn.rs1.getInt("Q1")); } } } if (conn.rs1.getString("Q2") != null) { if (!conn.rs1.getString("Q2").equals("")) { if (conn.rs1.getInt("Q2") > highestvalue) { highestvalue = conn.rs1.getInt("Q2"); } if (!ispercent.equals("")) { clQ2.setCellValue(conn.rs1.getInt("Q2") + ispercent); } else { clQ2.setCellValue(conn.rs1.getInt("Q2")); } } } if (conn.rs1.getString("Q3") != null) { if (!conn.rs1.getString("Q3").equals("")) { if (conn.rs1.getInt("Q3") > highestvalue) { highestvalue = conn.rs1.getInt("Q3"); } if (!ispercent.equals("")) { clQ3.setCellValue(conn.rs1.getInt("Q3") + ispercent); } else { clQ3.setCellValue(conn.rs1.getInt("Q3")); } } } if (conn.rs1.getString("Q4") != null) { if (!conn.rs1.getString("Q4").equals("")) { if (conn.rs1.getInt("Q4") > highestvalue) { highestvalue = conn.rs1.getInt("Q4"); } if (!ispercent.equals("")) { clQ4.setCellValue(conn.rs1.getInt("Q4") + ispercent); } else { clQ4.setCellValue(conn.rs1.getInt("Q4")); } } } } clQ2.setCellStyle(style2); clQ1.setCellStyle(style2); clQ3.setCellStyle(style2); clQ4.setCellStyle(style2); //====================================Annual figures======================= String percentageachievement = ""; String achievednonpercent = "No target / achieved value"; int curcol = colpos; String annualispercent = ""; int currentyearvalue = 0; int currentyearhighestqtr = 0; for (int yearval = selectedyear; yearval >= projectstartyear; yearval--) { HSSFCell clx = rwx.createCell(curcol); // System.out.println("******"+curcol); //separate cumulates + average with the rest if (conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Cumulative") || conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Average")) { String qry = ""; if (conn.rs.getString("percentage").equals("1")) { annualispercent = "%"; if (conn.rs.getString("tableidentifier").equals("2")) { //no gender thus its combined qry = " select ROUND(AVG(totalAchieved)) as y" + yearval + " from indicatorachievedcombined where financialyear='" + yearval + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } else { qry = " select ROUND(AVG((menAchieved + womenAchieved)/2)) as y" + yearval + " from indicatorachieved where financialyear='" + yearval + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } } //non percents else { //for cumulatives //check tabletype //1 is by gender //_____________CUMULATIVES______________ if (conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Cumulative")) { if (conn.rs.getString("tableIdentifier").equals("1")) { qry = " select sum((menAchieved + womenAchieved)) as y" + yearval + " from indicatorachieved where financialyear='" + yearval + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } else { qry = " select sum(totalAchieved) as y" + yearval + " from indicatorachievedcombined where financialyear='" + yearval + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } //end of else of table identifier } //end of sum/cumulatives } //end of else of non percents System.out.println("@" + qry); conn.rs1 = conn.state1.executeQuery(qry); if (conn.rs1.next()) { // System.out.println("__"+conn.rs1.getString(1)); if (conn.rs1.getString(1) != null) { if (!conn.rs1.getString(1).equals("")) { if (annualispercent.equals("")) { clx.setCellValue(conn.rs1.getInt(1)); //achieved nonpercentage if (yearval == selectedyear) { if (annualtarget > 1) { achievednonpercent = "" + (int) conn.rs1.getInt(1) * 100 / (int) annualtarget + "%"; } } } else { clx.setCellValue(conn.rs1.getInt(1) + annualispercent); //do this for the cureent year if (yearval == selectedyear) { percentageachievement = conn.rs1.getInt(1) + annualispercent; } } } } } clx.setCellStyle(style2); } //end of cumulatives and percentages else if (conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Highest")) { String qry = ""; if (conn.rs.getString("tableIdentifier").equals("1")) { //by gender and thus separate columns qry = " select sum(case when reportingPeriod='Q1' then (menAchieved + womenAchieved) end) as Q1,sum(case when reportingPeriod='Q2' then (menAchieved + womenAchieved) end) as Q2, sum(case when reportingPeriod='Q3' then (menAchieved + womenAchieved) end) as Q3, sum(case when reportingPeriod='Q4' then (menAchieved + womenAchieved) end) as Q4 from indicatorachieved where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + yearval + "' group by titleID "; } else { qry = " select sum(case when reportingPeriod='Q1' then totalAchieved end) as Q1, sum(case when reportingPeriod='Q2' then totalAchieved end) as Q2, sum(case when reportingPeriod='Q3' then totalAchieved end) as Q3, sum(case when reportingPeriod='Q4' then totalAchieved end) as Q4 from indicatorachievedcombined where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + yearval + "' group by titleID "; } int highestqtr = 0; //excecute query conn.rs1 = conn.state1.executeQuery(qry); while (conn.rs1.next()) { // if (conn.rs1.getString("Q1") != null) { if (!conn.rs1.getString("Q1").equals("")) { if (conn.rs1.getInt("Q1") > highestqtr) { highestqtr = conn.rs1.getInt("Q1"); } } } else if (conn.rs1.getString("Q2") != null) { if (!conn.rs1.getString("Q2").equals("")) { if (conn.rs1.getInt("Q2") > highestqtr) { highestqtr = conn.rs1.getInt("Q2"); } } } else if (conn.rs1.getString("Q3") != null) { if (!conn.rs1.getString("Q3").equals("")) { if (conn.rs1.getInt("Q3") > highestqtr) { highestqtr = conn.rs1.getInt("Q3"); } } } else if (conn.rs1.getString("Q4") != null) { if (!conn.rs1.getString("Q4").equals("")) { if (conn.rs1.getInt("Q4") > highestqtr) { highestqtr = conn.rs1.getInt("Q4"); } } } } if (highestqtr > 0) { clx.setCellValue(highestqtr); } else { clx.setCellValue(""); } //achieved nonpercentage if (yearval == selectedyear) { if (annualtarget != 1) { achievednonpercent = "" + (int) highestqtr * 100 / (int) annualtarget + "%"; System.out.println(highestqtr + " / " + annualtarget + "___" + (int) highestqtr * 100 / (int) (annualtarget) + "%"); } } clx.setCellStyle(style2); } // end of highest else if (conn.rs.getString("cumulative_chooser").equalsIgnoreCase("OLMIS") || conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Last Reported")) { String qry = ""; //get data for the last input quarter if (yearval == selectedyear) { //get data for that quarter if (conn.rs.getString("tableidentifier").equals("2")) { //no gender qry = " select sum((totalAchieved)) as y" + yearval + " from indicatorachievedcombined where financialyear='" + yearval + "' and reportingPeriod='" + selectedQTR + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } else { qry = " select sum((menAchieved + womenAchieved)) as y" + yearval + " from indicatorachieved where financialyear='" + yearval + "' and reportingPeriod='" + selectedQTR + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } } else { if (conn.rs.getString("tableidentifier").equals("2")) { //no gender qry = " select sum((totalAchieved)) as y" + yearval + " from indicatorachievedcombined where financialyear='" + yearval + "' and reportingPeriod='Q4' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } else { qry = " select sum((menAchieved + womenAchieved)) as y" + yearval + " from indicatorachieved where financialyear='" + yearval + "' and reportingPeriod='Q4' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } } //execute the query conn.rs1 = conn.state1.executeQuery(qry); if (conn.rs1.next()) { // System.out.println("__"+conn.rs1.getString(1)); if (conn.rs1.getString(1) != null) { if (!conn.rs1.getString(1).equals("")) { clx.setCellValue(conn.rs1.getInt(1)); //this is for percentage purpose if (selectedyear == yearval) { currentyearvalue = conn.rs1.getInt(1); } } } } //achieved nonpercentage if (yearval == selectedyear) { if (annualtarget != 1) { achievednonpercent = "" + (int) currentyearvalue * 100 / (int) annualtarget + "%"; } } clx.setCellStyle(style2); } //end of olmis and last reported indicators curcol++; } //end of for loop HSSFCell clx = rwx.createCell(curcol); if (conn.rs.getInt("percentage") == 1) { clx.setCellValue(percentageachievement); } else { clx.setCellValue(achievednonpercent); } clx.setCellStyle(style2); rwx.setHeightInPoints(42); rownumber++; count++; colpos = 0; } for (int e = 0; e < 13; e++) { //shet.autoSizeColumn(e); } //Made my life veery simple... shet.setDisplayGridlines(false); shet.createFreezePane(2, 3); if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.state1 != null) { conn.state1.close(); } if (conn.state2 != null) { conn.state2.close(); } // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PPMPREPORT_" + selectedyear + "_" + selectedQTR + "_gen_on_" + generationtime + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } finally { } }
From source file:quickreports.masterlist.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.// w ww . j a v a2 s . c om * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { response.setContentType("text/html;charset=UTF-8"); //PrintWriter out = response.getWriter(); /* TODO output your page here. You may use following sample code. */ //______________________________________________________________________________________ // 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); 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_LEFT); HSSFCellStyle stylesum = wb.createCellStyle(); stylesum.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylesum.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylesum.setBorderTop(HSSFCellStyle.BORDER_THIN); stylesum.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylesum.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylesum.setBorderRight(HSSFCellStyle.BORDER_THIN); stylesum.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); stylesum.setFont(fontx); stylesum.setWrapText(true); HSSFSheet shet = wb.createSheet("Masterlist"); String year = ""; if (request.getParameter("year") != null) { year = request.getParameter("year"); } dbConn conn = new dbConn(); //========Query 1================= HSSFRow rw0 = shet.createRow(1); HSSFCell cell = rw0.createCell(0); cell.setCellValue("APHIAPLUS Nuru Ya Bonde Supported Sites Summary " + year); cell.setCellStyle(style); shet.addMergedRegion(new CellRangeAddress(1, 1, 0, 3)); int count1 = 3; String qry1 = "call rpt_masterlist_summary('2015-10-01','2016-09-30','" + year + "')"; conn.rs = conn.st.executeQuery(qry1); ResultSetMetaData metaData = conn.rs.getMetaData(); int columnCount = metaData.getColumnCount(); ArrayList mycolumns1 = new ArrayList(); while (conn.rs.next()) { if (count1 == 3) { //header rows HSSFRow rw = shet.createRow(count1); rw.setHeightInPoints(26); for (int i = 1; i <= columnCount; i++) { mycolumns1.add(metaData.getColumnLabel(i)); HSSFCell cell0 = rw.createCell(i - 1); cell0.setCellValue(metaData.getColumnLabel(i)); cell0.setCellStyle(stylex); //create row header } //end of for loop count1++; } //end of if //data rows HSSFRow rw = shet.createRow(count1); for (int a = 0; a < columnCount; a++) { // System.out.print(mycolumns1.get(a) + ":" + conn.rs.getString("" + mycolumns1.get(a))); HSSFCell cell0 = rw.createCell(a); if (a > 0) { cell0.setCellValue(conn.rs.getInt(mycolumns1.get(a).toString())); } else { cell0.setCellValue(conn.rs.getString("" + mycolumns1.get(a))); } cell0.setCellStyle(style2); } // System.out.println(""); count1++; } HSSFRow rw01 = shet.createRow(count1 + 1); HSSFCell cell1 = rw01.createCell(0); cell1.setCellValue("APHIAPLUS Nuru Ya Bonde supported sites details " + year); cell1.setCellStyle(style); shet.addMergedRegion(new CellRangeAddress(count1 + 1, count1 + 1, 0, 3)); //========Query two====Facility Details============== String qry = "call rpt_masterlist('2015-10-01','2016-09-30','" + year + "')"; conn.rs = conn.st.executeQuery(qry); metaData = conn.rs.getMetaData(); columnCount = metaData.getColumnCount(); int count = count1 + 3; ArrayList mycolumns = new ArrayList(); while (conn.rs.next()) { if (count == (count1 + 3)) { //header rows HSSFRow rw = shet.createRow(count); rw.setHeightInPoints(26); for (int i = 1; i <= columnCount; i++) { mycolumns.add(metaData.getColumnLabel(i)); HSSFCell cell0 = rw.createCell(i - 1); cell0.setCellValue(metaData.getColumnLabel(i)); cell0.setCellStyle(stylex); //create row header } //end of for loop count++; } //end of if //data rows HSSFRow rw = shet.createRow(count); for (int a = 0; a < columnCount; a++) { //System.out.print(mycolumns.get(a) + ":" + conn.rs.getString("" + mycolumns.get(a))); HSSFCell cell0 = rw.createCell(a); if ((a >= 3 && a <= 14) || (a >= 18 && a <= 22) || (a >= 27 && a <= 29)) { cell0.setCellValue(conn.rs.getInt(mycolumns.get(a).toString())); } else { cell0.setCellValue(conn.rs.getString("" + mycolumns.get(a))); } cell0.setCellStyle(style2); } // System.out.println(""); count++; } //Autofreeze || Autofilter || Remove Gridlines || shet.setAutoFilter(new CellRangeAddress(count1 + 3, count - 1, 0, columnCount - 1)); //System.out.println("1,"+rowpos+",0,"+colposcopy); for (int i = 0; i <= columnCount; i++) { shet.autoSizeColumn(i); } shet.setDisplayGridlines(false); shet.createFreezePane(4, 14); if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st != null) { conn.st.close(); } if (conn.st1 != null) { conn.st1.close(); } IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); System.out.println("" + "MasterList_Gen_" + createdOn.trim() + ".xls"); 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=" + "MasterList_Gen_" + createdOn.trim() + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:reports.allStaticReports.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {//from ww w. j av a 2 s . c o m response.setContentType("text/html;charset=UTF-8"); //a page to get Report of all the servlets String year = "2015"; String month = ""; String county = ""; String form = "moh731"; if (request.getParameter("year") != null) { year = request.getParameter("year"); } if (request.getParameter("county") != null) { county = request.getParameter("county"); } if (request.getParameter("month") != null) { month = request.getParameter("month"); } if (request.getParameter("form") != null) { form = request.getParameter("form"); } String facilitywhere = ""; String yearwhere = ""; String monthwhere = ""; String countywhere = ""; String districtwhere = ""; String reporttype = ""; if (!year.equals("")) { yearwhere = " and Annee = '" + year + "'"; } if (!county.equals("")) { countywhere = " and countyid = '" + county + "'"; } if (!month.equals("")) { monthwhere = " and Mois = '" + month + "'"; } dbConn conn = new dbConn(); //an array to store haeder information. //the header information should appear only if a certain parameters are met //The parameters listed in here can be removed if the report type doesnt require certain parameters ArrayList Headerorgunits = new ArrayList(); Headerorgunits.add("COUNTY"); Headerorgunits.add("SUB-COUNTY"); Headerorgunits.add("FACILITY"); Headerorgunits.add("MFL CODE"); //An arralist to store a list of columns that will be selected from the database ArrayList dbcolumns = new ArrayList(); ArrayList labels = new ArrayList(); ArrayList tablename = new ArrayList(); ArrayList iscumulative = new ArrayList(); ArrayList ispercent = new ArrayList(); // ArrayList isactive=new ArrayList(); //An arralist to store a list of worksheets that will be selected from the sections ArrayList worksheets = new ArrayList(); //An arralist to store distinct worksheets. This will be derived from the the sections column ArrayList distinctsheets = new ArrayList(); String selectdistinctworksheet = "select distinct(section) from pivottable where form='" + form + "' and active='1' order by order_per_form"; conn.rs = conn.st.executeQuery(selectdistinctworksheet); while (conn.rs.next()) { //add the name of distinct sections distinctsheets.add(conn.rs.getString(1).replace("/", "_")); } String getattribs = "select indicator,label,section,cumulative,percentage,active ,shortlabel from pivottable where form='" + form + "' order by order_per_form, section"; conn.rs = conn.st.executeQuery(getattribs); while (conn.rs.next()) { //add active indicators only if (conn.rs.getString("active").equals("1")) { System.out.println(conn.rs.getString("indicator") + ""); //add indicator dbcolumns.add(conn.rs.getString("indicator")); //add label if (form.equals("moh731")) { labels.add(conn.rs.getString("shortlabel") + " \n " + conn.rs.getString("label")); } else { labels.add(conn.rs.getString("label")); } //add worksheets worksheets.add(conn.rs.getString("section").replace("/", "_")); String perc = "0"; String cum = "0"; if (conn.rs.getString("cumulative") != null) { iscumulative.add(conn.rs.getString("cumulative")); } else { iscumulative.add(cum); } if (conn.rs.getString("percentage") != null) { ispercent.add(conn.rs.getString("percentage")); } else { ispercent.add(perc); } } //end of active } //end of worksheet //if String perfacilselect = "select Upper(County) as County , Upper(DistrictNom) as District , UPPER(SubPartnerNom) as facility ,CentreSanteId as mflcode , district.CountyID as countyid , "; //-------------------------------------------------------------------------------------------- // PREPARE SELECT //-------------------------------------------------------------------------------------------- //prepare selects for (int a = 0; a < dbcolumns.size(); a++) { //if the indicator is a percent, get an avaerage if (ispercent.get(a).equals("1")) { perfacilselect += " AVG(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } else if (iscumulative.get(a).equals("1")) { perfacilselect += " " + dbcolumns.get(a) + " as " + dbcolumns.get(a); } else { perfacilselect += " SUM(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } //if the item is not the last, append a comma if (a < dbcolumns.size() - 1) { perfacilselect += " ,"; } } //------------------------------------------------------------------------------------ // FROM //------------------------------------------------------------------------------------ perfacilselect += " , isValidated as Form_Validated from " + form + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on " + form + ".SubPartnerID = subpartnera.SubPartnerID "; //------------------------------------------------------------------------------------------ // WHERE //------------------------------------------------------------------------------------------ perfacilselect += " where 1=1 " + monthwhere + yearwhere; //----------------------------------------------------------------------------------------- //GROUP BY //---------------------------------------------------------------------------------------- perfacilselect += " group by subpartnera.SubPartnerID"; System.out.println(perfacilselect); //______________________________________________________________________________________ // NOW CREATE THE WORKSHEETS //______________________________________________________________________________________ XSSFWorkbook wb = new XSSFWorkbook(); //______________________________________________________________________________________ //______________________________________________________________________________________ XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); for (int b = 0; b < distinctsheets.size(); b++) { XSSFSheet shet = wb.createSheet(distinctsheets.get(b).toString().toUpperCase()); //create headers for that worksheet XSSFRow rw = shet.createRow(1); int headercellpos = 0; //create the orgunit header eg COUNTY | SUBCOUNTY | FACILITY for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(Headerorgunits.get(e).toString()); cell0.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(e, 6000); } //create the indicators header eg HV0101 | HIV 09676 | TOTAL for (int c = 0; c < dbcolumns.size(); c++) { //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(b))) { shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(labels.get(c).toString()); cell0.setCellStyle(stylex); headercellpos++; } //end of comparing if } //end of for loop //create is validated header shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue("Form Validated ?"); cell0.setCellStyle(stylex); headercellpos++; } conn.rs = conn.st.executeQuery(perfacilselect); String sectioncopy = ""; int sheetpos = 0; int rowpos = 2; while (conn.rs.next()) { //-----------------INSIDE THE DATA FORM--------------------------------- //if the section changes, change the position of the worksheet too //also, reset the position counter to begin from 2 again. XSSFSheet shet = null; // if(--!sectioncopy.equals(shet)){} for (int g = 0; g < distinctsheets.size(); g++) { shet = wb.getSheetAt(g); int colpos = 0; //the fourth cell should XSSFRow rw = shet.createRow(rowpos); for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getString(e + 1)); cell0.setCellStyle(style2); colpos++; } //_________________________________________________________________ //VALUES //_________________________________________________________________ //create the indicators values eg 90 | 45 | 356 for (int c = 0; c < dbcolumns.size(); c++) { //get the section of the current dbcolumn //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(g))) { XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getString(dbcolumns.get(c).toString())); cell0.setCellStyle(stborder); colpos++; } //end of comparing if } //end of for loop String isvalidated = "Yes"; if (conn.rs.getString("Form_Validated").equals("0")) { isvalidated = "No"; } XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(isvalidated); cell0.setCellStyle(stborder); colpos++; } rowpos++; } IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); System.out.println("" + form.toUpperCase().trim() + "_REPORT_FOR_" + year.trim() + "(" + month.trim() + ")_CREATED_" + createdOn.trim() + ".xlsx"); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=" + form.toUpperCase().trim() + "_REPORT_FOR_" + year.trim() + "(" + month.trim() + ")_CREATED_" + createdOn.trim() + ".xlsx"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(allStaticReports.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.allStaticReportsdynamic.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String monthrange = ""; String periodlabel = ""; String period = "Month"; //The default one String periodicgroupby = " "; //note that in the current query there is an existing group by. Therefore this will be an extra infor to be added on the existing group by String isgroupby = "yes"; try {/*from w w w . j a v a2s. c o m*/ response.setContentType("text/html;charset=UTF-8"); //a page to get Report of all the servlets String months[] = null; String year = "2015"; String month = ""; String county = ""; String form = "moh731"; if (request.getParameter("year") != null) { year = request.getParameter("year"); } if (request.getParameter("county") != null) { county = request.getParameter("county"); } if (request.getParameter("month") != null) { months = request.getParameterValues("month"); } if (request.getParameter("form") != null) { form = request.getParameter("form"); } if (request.getParameter("groupby") != null) { isgroupby = request.getParameter("groupby"); } String pivotform = form; if (form.equalsIgnoreCase("MOH 731")) { form = "MOH731"; } if (form.equalsIgnoreCase("MOH 711A")) { form = "MOH711"; } if (form.equalsIgnoreCase("MOH 711 (New)")) { form = "moh711_new"; } String facilitywhere = ""; String yearwhere = ""; String monthwhere = ""; String countywhere = ""; String districtwhere = ""; String reporttype = ""; String subcounty_countywhere = ""; String indicatorslist = "all"; String sections = "all"; String subsections = "all"; String indicatorswhere = ""; //________________________________________________________________________________________________________________________________________________________ //________________________________________________________________________________________________________________________________________________________ String duration = ""; String semi_annual = ""; String quarter = ""; //================================================================================================== //XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX String facil = "361"; String yearmonthstart = ""; String yearmonthend = ""; //===================================================================================================== String header = ""; String reportType = ""; dbConn conn = new dbConn(); if (request.getParameter("reportType") != null) { reportType = request.getParameter("reportType"); } if (request.getParameter("indicators") != null) { indicatorslist = request.getParameter("indicators"); } //--------------------------sections------------ String sectionid[] = null; if (request.getParameterValues("sections") != null) { sectionid = request.getParameterValues("sections"); } String sectionvals = "("; if (request.getParameterValues("sections") != null) { for (int a = 0; a < sectionid.length; a++) { if (a == sectionid.length - 1) { sectionvals += sectionid[a] + ""; } else { sectionvals += sectionid[a] + ","; } } } sectionvals += ")"; if (sectionvals.equals("()")) { indicatorswhere += " "; } else { indicatorswhere += " and sectionid in " + sectionvals + " "; } //______________________________________________________subsections_______________________________________ String subsectionid[] = null; if (request.getParameterValues("subsection") != null) { subsectionid = request.getParameterValues("subsection"); } String subsectionvals = "("; if (request.getParameterValues("subsection") != null) { for (int a = 0; a < subsectionid.length; a++) { if (a == subsectionid.length - 1) { subsectionvals += subsectionid[a] + ""; } else { subsectionvals += subsectionid[a] + ","; } } } subsectionvals += ")"; if (subsectionvals.equals("()")) { indicatorswhere += " "; } else { indicatorswhere += " and subsectionid in " + subsectionvals + " "; } //______________________________________________________subsections_______________________________________ //add sections //special indicators if (indicatorslist.equals("special")) { indicatorswhere += " and specialindicator='1'"; } String reportDuration = ""; if (request.getParameter("reportDuration") != null) { reportDuration = request.getParameter("reportDuration"); } if (request.getParameter("facility") != null && reportType.equals("2")) { try { 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) + " "; } } catch (SQLException ex) { Logger.getLogger(allStaticReportsdynamic.class.getName()).log(Level.SEVERE, null, ex); } } if (request.getParameter("county") != null && reportType.equals("2")) { try { county = request.getParameter("county"); subcounty_countywhere = " (county.CountyID='" + county + "') and ";//20160711 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() + " "; } } catch (SQLException ex) { Logger.getLogger(allStaticReportsdynamic.class.getName()).log(Level.SEVERE, null, ex); } } int yearcopy = Integer.parseInt(year); String yearmonth = "" + year; int prevYear = yearcopy - 1; int maxYearMonth = 0; int monthcopy = 0; String currentperiodlabel = "";//a variable to hold the name of a period inside a conn.rs if . header += " YEAR : " + year + ""; // GET REPORT DURATION============================================ //annually //____________________________________________________________________________________________________________Annual____________________________________ if (reportDuration.equals("1")) { yearmonth = "Annual Report For " + year; duration = " and " + form + ".yearmonth BETWEEN '" + prevYear + "10' AND '" + year + "09'"; period = "Year"; periodicgroupby = ", period "; monthrange = year; // tbstatduration="year='"+year+"'"; } //____________________________________________________________________________________________________________Semi_annual_____________________________ else if (reportDuration.equals("2")) { period = "Semi-Annual"; periodicgroupby = ", period "; try { String startMonth = "", endMonth = ""; String semiannualarray[] = request.getParameterValues("semi_annual"); String temporaryheader = " SEMI-ANNUAL"; for (int p = 0; p < semiannualarray.length; p++) { semi_annual = semiannualarray[p]; String getperiodname = "SELECT name as semiannual_name,months FROM semi_annual WHERE id='" + semi_annual + "'"; conn.rs = conn.st.executeQuery(getperiodname); if (conn.rs.next() == true) { String monthsinsemiannual[] = conn.rs.getString("months").split(","); currentperiodlabel = conn.rs.getString("semiannual_name"); //_________________add year at the end of period label if (semi_annual.equals("1")) { //periodname is in format oct-Mar we will make it to be in format Oct 2015-Mar 2016 currentperiodlabel = conn.rs.getString("semiannual_name").replace("-", " " + prevYear + "-") + " " + year; } else { //periodname is in format Apr-Sep we will make it to be in format Apr-Sep 2016 currentperiodlabel = conn.rs.getString("semiannual_name") + " " + year; } //____________________ if (p == 0) { startMonth = monthsinsemiannual[0]; endMonth = monthsinsemiannual[5]; monthrange = currentperiodlabel; } if (p == semiannualarray.length - 1 && semiannualarray.length > 1) { //last row monthrange += " to " + currentperiodlabel; //by now we expect monthrange to be something like Oct-Mar-Apr-Sep endMonth = monthsinsemiannual[5]; } if (periodlabel.equals("")) { periodlabel = conn.rs.getString("semiannual_name"); } else { periodlabel += "_" + conn.rs.getString("semiannual_name"); } } //end of conn. //get the yearmonthstart date if (p == 0 && semiannualarray.length > 1) { //if the quarters selected are several //this is the starting quarter temporaryheader += "S " + currentperiodlabel + " To "; if (new Integer(semi_annual) == 1) { //the year will be deducted by one if the selected months are 10, 11, 12 yearmonthstart = " " + form + ".yearmonth between '" + prevYear + "" + startMonth + "' and "; } else { yearmonthstart = " " + form + ".yearmonth between '" + year + "" + startMonth + "' and "; } } else if (p == semiannualarray.length - 1 && semiannualarray.length > 1) { //the last month temporaryheader += currentperiodlabel; //by now we expct something like from Semi-annuals oct-Mar to Apr -Sep //this assumes that the last month can never be yearmonthend = "'" + year + "" + endMonth + "'"; } else if (p == 0 && semiannualarray.length == 1) { // the number of quarters selected is one temporaryheader += " " + currentperiodlabel + " "; if (new Integer(semi_annual) == 1) { yearmonthstart = " " + form + ".yearmonth between '" + prevYear + startMonth + "' and"; } else { yearmonthstart = " " + form + ".yearmonth between '" + year + "" + startMonth + "' and "; } yearmonthend = "'" + year + "" + endMonth + "'"; //this assumes no end month date can come in oct-dec ..all the year month ends come on mar or sep } //end of last monthly row } //end of for loop header += " " + temporaryheader + ""; } catch (SQLException ex) { System.out.println(ex); } if (!semi_annual.equals("")) { duration = " and " + yearmonthstart + "" + yearmonthend; } } // ____________________________________________________________________________________________________Quarterly____________________ else if (reportDuration.equals("3")) { period = "Quarter"; periodicgroupby = ", period "; try { String startMonth = "", endMonth = ""; String quarterarray[] = request.getParameterValues("quarter"); String temporaryheader = " QUARTER"; for (int p = 0; p < quarterarray.length; p++) { quarter = quarterarray[p]; String getMonth = "SELECT months,name as qtrname FROM quarter WHERE id='" + quarter + "'"; conn.rs = conn.st.executeQuery(getMonth); if (conn.rs.next() == true) { String monthsinqtr[] = conn.rs.getString(1).split(","); //_________________add year at the end of period label if (quarter.equals("1")) { //periodname is in format oct-Mar we will make it to be in format Oct 2015-Mar 2016 currentperiodlabel = conn.rs.getString("qtrname").replace("-", " " + prevYear + "-") + " " + year; } else { //periodname is in format Apr-Sep we will make it to be in format Apr-Sep 2016 currentperiodlabel = conn.rs.getString("qtrname") + " " + year; } //____________________ if (p == 0) { startMonth = monthsinqtr[0]; monthrange = conn.rs.getString("qtrname"); endMonth = monthsinqtr[2]; } if (p == quarterarray.length - 1 && quarterarray.length > 1) { //last row monthrange += " to " + conn.rs.getString("qtrname"); endMonth = monthsinqtr[2]; } if (periodlabel.equals(""))// note period label gets all the periods in my loop { periodlabel = conn.rs.getString("qtrname"); } else { periodlabel += "_" + conn.rs.getString("qtrname"); } } //end of if //get the yearmonthstart date if (p == 0 && quarterarray.length > 1) { //if the quarters selected are several //this is the starting quarter temporaryheader += "S " + currentperiodlabel + " To "; if (new Integer(quarter) == 1) { //the year will be deducted by one if the selected months are 10, 11, 12 yearmonthstart = " " + form + ".yearmonth between '" + prevYear + "" + startMonth + "' and"; } else { yearmonthstart = " " + form + ".yearmonth between '" + year + "" + startMonth + "' and"; } } else if (p == quarterarray.length - 1 && quarterarray.length > 1) { //the last month temporaryheader += currentperiodlabel; //by now we expct something like from Quarters oct-Dec to Jan -Mar yearmonthend = "'" + year + "" + endMonth + "'"; } else if (p == 0 && quarterarray.length == 1) { // the number of quarters selected is one temporaryheader += " " + currentperiodlabel + " "; if (new Integer(quarter) == 1) { yearmonthstart = " " + form + ".yearmonth between '" + prevYear + startMonth + "' and "; yearmonthend = " '" + prevYear + "" + endMonth + "' "; } else { yearmonthstart = " " + form + ".yearmonth between '" + year + "" + startMonth + "' and "; yearmonthend = " '" + year + "" + endMonth + "' "; } } //end of last monthly row } //end of for loop header += " " + temporaryheader + ""; } catch (SQLException ex) { System.out.println(ex); } if (!quarter.equals("")) { duration = " and " + yearmonthstart + "" + yearmonthend; } } //_______________________________________________________________________________________________________________monthly______________________________ else if (reportDuration.equals("4")) { period = "Month"; periodicgroupby = ", period "; try { months = request.getParameterValues("month"); String temporaryheader = " MONTH"; for (int u = 0; u < months.length; u++) { month = months[u]; String getMonth = "SELECT name FROM month WHERE id='" + month + "'"; conn.rs = conn.st.executeQuery(getMonth); if (conn.rs.next() == true) { //_________________add year at the end of period label if (new Integer(month) >= 10 && new Integer(month) <= 12) { //periodname is in format oct-Mar we will make it to be in format Oct 2015-Mar 2016 currentperiodlabel = conn.rs.getString("name").substring(0, 3) + " " + prevYear; } else { //periodname is in format Apr-Sep we will make it to be in format Apr-Sep 2016 currentperiodlabel = conn.rs.getString("name").substring(0, 3) + " " + year; } //____________________ if (u == 0) { monthrange = conn.rs.getString(1); } if (u == months.length - 1 && months.length > 1) { monthrange += " to " + conn.rs.getString(1); } if (periodlabel.equals("")) { periodlabel = conn.rs.getString("name"); } else { periodlabel += "_" + conn.rs.getString("name"); } } //get the yearmonthstart date if (u == 0 && months.length > 1) { //if the month selected are several //this is the starting month temporaryheader += "S " + currentperiodlabel + " To "; if (new Integer(month) >= 10) { //the year will be deducted by one if the selected months are 10, 11, 12 yearmonthstart = " " + form + ".yearmonth between '" + prevYear + "" + month + "' and "; } else { yearmonthstart = " " + form + ".yearmonth between '" + year + "0" + month + "' and "; } } else if (u == months.length - 1 && months.length > 1) { //the last month if (new Integer(month) >= 10) { yearmonthend = "'" + prevYear + month + "'"; } else { yearmonthend = "'" + year + "0" + month + "'"; } } else if (u == 0 && months.length == 1) { // the number of months selected is one temporaryheader += " " + currentperiodlabel + " "; if (new Integer(month) >= 10) { yearmonthstart = " " + form + ".yearmonth = " + prevYear + month; } else { yearmonthstart = " " + form + ".yearmonth = " + year + "0" + month; } yearmonthend = ""; } //end of last monthly row } //end of for loop header += " " + temporaryheader + ""; } catch (SQLException ex) { Logger.getLogger(allStaticReportsdynamic.class.getName()).log(Level.SEVERE, null, ex); } if (!month.equals("")) { duration = " and " + yearmonthstart + "" + yearmonthend; } } //end of monthly records else { duration = ""; } //if someone doest want periodic grouping, then dont add a group by in the report.. get numbers for all rows. if (isgroupby.equals("No")) { periodicgroupby = ""; } //______________________________________________________________________________________COUNTY , SUBCOUNTY AND String subcountywhere = ""; String subcounty = ""; if (reportType.equalsIgnoreCase("2")) { //get data per county if the selected if (!request.getParameter("subcounty").equals("")) { subcounty = request.getParameter("subcounty"); subcounty_countywhere = " (district.DistrictID='" + subcounty + "') and "; } if (!request.getParameter("county").equals("")) { county = request.getParameter("county"); subcounty_countywhere = " (district.countyid='" + request.getParameter("county") + "') and ";//20160711 } if (!county.equals("")) { countywhere = " and district.countyid = '" + county + "'"; } if (!subcounty.equals("")) { subcountywhere = " and subpartnera.DistrictID = '" + subcounty + "'"; } if (!facil.equals("") && reportType.equalsIgnoreCase("2")) { facilitywhere = " and " + form + ".SubPartnerID = '" + facil + "'"; } } String joinedwhwere = " where 1=1 " + duration + " " + countywhere + " " + subcountywhere + " " + facilitywhere; // System.out.println(""+joinedwhwere); //we need a case statement in our main query. This will allow for friendly display of String myperiodcase = ""; if (isgroupby.equals("No")) { myperiodcase = "'" + monthrange + "' as period"; } else { if (period.equalsIgnoreCase("Year")) { myperiodcase = " case when Annee !='' then Annee else 'no year' end as period "; } else if (period.equalsIgnoreCase("Semi-Annual")) { myperiodcase = " case when (SUBSTRING(Yearmonth,5,6) >=01 and SUBSTRING(Yearmonth,5,6) <=03) || (SUBSTRING(Yearmonth,5,6) >=10 and SUBSTRING(Yearmonth,5,6) <=12) then 'Oct-Mar' " + "when SUBSTRING(Yearmonth,5,6) >=04 and SUBSTRING(Yearmonth,5,6) <=09 then 'Apr-Sep' else 'No period' end as period "; } else if (period.equalsIgnoreCase("Quarter")) { myperiodcase = " case when (SUBSTRING(Yearmonth,5,6) >=01 and SUBSTRING(Yearmonth,5,6) <=03) then 'Jan-Mar'" + "when (SUBSTRING(Yearmonth,5,6) >=10 and SUBSTRING(Yearmonth,5,6) <=12) then 'Oct-Dec' " + "when SUBSTRING(Yearmonth,5,6) >=04 and SUBSTRING(Yearmonth,5,6) <=06 then 'Apr-Jun' " + "when SUBSTRING(Yearmonth,5,6) >=07 and SUBSTRING(Yearmonth,5,6) <=09 then 'Jul-Sep' " + "else 'No period' end as period "; } else if (period.equalsIgnoreCase("Month")) { myperiodcase = " case " + " when SUBSTRING(Yearmonth,5,6)= '01' then 'Jan' " + " when SUBSTRING(Yearmonth,5,6)= '02' then 'Feb' " + " when SUBSTRING(Yearmonth,5,6)= '03' then 'Mar' " + " when SUBSTRING(Yearmonth,5,6)= '04' then 'Apr' " + " when SUBSTRING(Yearmonth,5,6)= '05' then 'May' " + " when SUBSTRING(Yearmonth,5,6)= '06' then 'Jun' " + " when SUBSTRING(Yearmonth,5,6)= '07' then 'Jul' " + " when SUBSTRING(Yearmonth,5,6)= '08' then 'Aug' " + " when SUBSTRING(Yearmonth,5,6)= '09' then 'Sep' " + " when SUBSTRING(Yearmonth,5,6)= '10' then 'Oct' " + " when SUBSTRING(Yearmonth,5,6)= '11' then 'Nov' " + " when SUBSTRING(Yearmonth,5,6)= '12' then 'Dec' " + " else 'No period' end as period "; } } //_________________________________________________________________END OF FILTERING SECTION_______________________________________________________________ //________________________________________________________________________________________________________________________________________________________ //an array to store haeder information. //the header information should appear only if certain parameters are met //The parameters listed in here can be removed if the report type doesnt require some parameters ArrayList Headerorgunits = new ArrayList(); Headerorgunits.add(period); Headerorgunits.add("County"); Headerorgunits.add("Sub-County"); Headerorgunits.add("Facility"); Headerorgunits.add("MFL Code"); //An arralist to store a list of columns that will be selected from the database ArrayList dbcolumns = new ArrayList(); ArrayList labels = new ArrayList(); ArrayList tablename = new ArrayList(); ArrayList iscumulative = new ArrayList(); ArrayList ispercent = new ArrayList(); // ArrayList isactive=new ArrayList(); //An arralist to store a list of worksheets that will be selected from the sections and the respective service area to determine the facilities whose data will appear in that sheet ArrayList worksheets = new ArrayList(); //An arralist to store distinct worksheets. This will be derived from the the sections column ArrayList distinctsheets = new ArrayList(); ArrayList distinctservicearea = new ArrayList(); //create an array to store the number of row for each excel worksheet. //This will help in retrieving the number of rows for each month since we are wring data for different months with increasing rows. //the size of that array will be determined by the number of excel worksheets String selectdistinctworksheet = "select section,servicearea from pivottable where form='" + form.replace("_", "") + "' and active='1' " + indicatorswhere + " group by section order by order_per_form"; conn.rs = conn.st.executeQuery(selectdistinctworksheet); while (conn.rs.next()) { //add the name of distinct sections distinctsheets.add(conn.rs.getString(1).replace("/", "_")); String servicearea = " 2=2 "; if (conn.rs.getString(2) != null) { servicearea = " " + conn.rs.getString(2) + "=1"; } distinctservicearea.add(servicearea); } int rowstartpersheet[] = new int[distinctsheets.size()]; //initialize the row start position for each workshett with 2 for (int x = 0; x < rowstartpersheet.length; x++) { rowstartpersheet[x] = 2; } String getattribs = "select indicator,label,section,cumulative,percentage,active ,shortlabel from pivottable where form='" + form.replace("_", "") + "' " + indicatorswhere + " order by order_per_form, section"; conn.rs = conn.st.executeQuery(getattribs); while (conn.rs.next()) { //add active indicators only if (conn.rs.getString("active").equals("1")) { //System.out.println(conn.rs.getString("indicator")+""); //add indicator dbcolumns.add(conn.rs.getString("indicator")); //add label if (form.equalsIgnoreCase("MOH731")) { labels.add(conn.rs.getString("shortlabel") + " \n" + conn.rs.getString("label")); } else { labels.add(conn.rs.getString("label")); } //add worksheets worksheets.add(conn.rs.getString("section").replace("/", "_")); String perc = "0"; String cum = "0"; if (conn.rs.getString("cumulative") != null) { iscumulative.add(conn.rs.getString("cumulative")); } else { iscumulative.add(cum); } if (conn.rs.getString("percentage") != null) { ispercent.add(conn.rs.getString("percentage")); } else { ispercent.add(perc); } } //end of active } //end of pivot table active //labels.add("ART High Volume"); //labels.add("HTC High Volume"); //labels.add("PMTCT High Volume"); //System.out.println(perfacilselect); //______________________________________________________________________________________ // CREATE THE WORKSHEETS //______________________________________________________________________________________ XSSFWorkbook wb = new XSSFWorkbook(); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFCellStyle stylesum = wb.createCellStyle(); stylesum.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylesum.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylesum.setBorderTop(HSSFCellStyle.BORDER_THIN); stylesum.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylesum.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylesum.setBorderRight(HSSFCellStyle.BORDER_THIN); stylesum.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); stylesum.setFont(fontx); stylesum.setWrapText(true); int cellrange[] = new int[4]; for (int b = 0; b < distinctsheets.size(); b++) { XSSFSheet shet = wb.createSheet(distinctsheets.get(b).toString().toUpperCase()); //Made my life veery simple... shet.setDisplayGridlines(false); shet.createFreezePane(5, 2); int firstcell = 1; //create headers for that worksheet XSSFRow rw = shet.createRow(1); int headercellpos = 0; //create the orgunit header eg MONTH | COUNTY | SUBCOUNTY | FACILITY for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(Headerorgunits.get(e).toString()); cell0.setCellStyle(stylex); headercellpos++; //shet.setColumnWidth(e, 6000); } //create the indicators header eg HV0101 | HIV 09676 | TOTAL for (int c = 0; c < dbcolumns.size(); c++) { //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(b))) { shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(labels.get(c).toString()); cell0.setCellStyle(stylex); headercellpos++; } //end of comparing if } //end of for loop //create is validated header shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue("ART High Volume"); cell0.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(headercellpos, 6000); XSSFCell cell1 = rw.createCell(headercellpos); cell1.setCellValue("HTC High Volume"); cell1.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(headercellpos, 6000); XSSFCell cell2 = rw.createCell(headercellpos); cell2.setCellValue("PMTCT High Volume"); cell2.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(headercellpos, 6000); XSSFCell cell2a = rw.createCell(headercellpos); cell2a.setCellValue("GSN"); cell2a.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(headercellpos, 6000); XSSFCell cell3 = rw.createCell(headercellpos); cell3.setCellValue("Form Validated ?"); cell3.setCellStyle(stylex); headercellpos++; } //______________________________________________________________________________________ //______________________________________________________________________________________ //-------------------------------------------------------------------------------------------- // MONTH LOOPS //-------------------------------------------------------------------------------------------- int cumulativestatingpoint = 3; boolean cumstartpointnoted = false; int colposcopy = 0; //for (int w = 0; w < months.length; w++) { String perfacilselect = "select " + myperiodcase + " , CONCAT(UPPER(SUBSTRING(County,1,1)),LOWER(SUBSTRING(County,2))) as County , DistrictNom as District , SubPartnerNom as facility ,CentreSanteId as mflcode , district.CountyID as countyid , "; //have a stringto get all distinct periods before execution of the main query. This will help in knowing the last String getdistinctperiod = "select " + myperiodcase; //-------------------------------------------------------------------------------------------- // PREPARE SELECT //-------------------------------------------------------------------------------------------- //prepare selects for (int a = 0; a < dbcolumns.size(); a++) { //if the indicator is a percent, get an avaerage if (ispercent.get(a).equals("1")) { perfacilselect += " AVG(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } else if (iscumulative.get(a).equals("1")) { perfacilselect += " SUBSTRING_INDEX(GROUP_CONCAT(CAST(IFNULL(" + dbcolumns.get(a) + ",0) AS CHAR) ORDER BY yearmonth DESC),',',1) as " + dbcolumns.get(a); //SUBSTRING_INDEX(GROUP_CONCAT(CAST(IFNULL(HV0303,0) AS CHAR) ORDER BY yearmonth DESC),',',1) } else { perfacilselect += " SUM(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } //if the item is not the last, append a comma //if(a<dbcolumns.size()-1){ perfacilselect += " ,"; // } } //---------------------------------add highvolume------------------------------------------------ perfacilselect += " IFNULL(ART_highvolume,0) as ART_highvolume, IFNULL(HTC_highvolume,0) as HTC_highvolume, IFNULL(PMTCT_highvolume,0) as PMTCT_highvolume, IFNULL(GSN,0) as GSN,"; //------------------------------------------------------------------------------------------------- // FROM //------------------------------------------------------------------------------------ perfacilselect += " isValidated as Form_Validated from " + form + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on " + form + ".SubPartnerID = subpartnera.SubPartnerID "; getdistinctperiod += " from " + form + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on " + form + ".SubPartnerID = subpartnera.SubPartnerID "; //------------------------------------------------------------------------------------------ // WHERE //------------------------------------------------------------------------------------------ perfacilselect += joinedwhwere; //contains any filterings getdistinctperiod += joinedwhwere; //contains any filterings //----------------------------------------------------------------------------------------- //GROUP BY //---------------------------------------------------------------------------------------- perfacilselect += " group by subpartnera.SubPartnerID " + periodicgroupby + " order by yearmonth "; getdistinctperiod += " group by period order by yearmonth "; String lastperiod = ""; ArrayList alldistinctperiods = new ArrayList(); //System.out.println(""+getdistinctperiod); conn.rs = conn.st.executeQuery(getdistinctperiod); while (conn.rs.next() == true) { lastperiod = conn.rs.getString(1); //here am asumming the last period will appear last and so will be mantained in the alldistinctperiods.add(lastperiod); } String sectioncopy = ""; int sheetpos = 0; int rowpos = 2; //-----------------INSIDE THE DATA FORM--------------------------------- //if the section (eg HTC, PMTCT) changes, change the current workshhet index too //also, reset the row position counter to begin from 2 again. XSSFSheet shet = null; // if(--!sectioncopy.equals(shet)){} //create the org unit data values e.g BARINGO | BARINGO CENTRAL |KABARNET DISTRICT HOSPITAL | MFL CODE for (int g = 0; g < distinctsheets.size(); g++) { rowpos = rowstartpersheet[g]; shet = wb.getSheetAt(g); int colpos = 0; String finalquery = perfacilselect.replace("1=1", distinctservicearea.get(g).toString()); //System.out.println("" + finalquery); conn.rs = conn.st.executeQuery(finalquery); while (conn.rs.next()) { if (lastperiod.equalsIgnoreCase(conn.rs.getString("period")) && cumstartpointnoted == false) { //save the current row position. //get it from the current rowstartpersheet[g] cumulativestatingpoint = rowstartpersheet[0]; cumstartpointnoted = true; //this ensures that we are fetching dataonce. // System.out.println("LAST STARTING POINT__"+cumulativestatingpoint); } //the fourth cell should XSSFRow rw = shet.createRow(rowpos); for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(colpos); //for mfl code, last header, output integers if (e > 3) { cell0.setCellValue(conn.rs.getInt(e + 1)); } else { cell0.setCellValue(conn.rs.getString(e + 1)); } cell0.setCellStyle(style2); colpos++; } //_________________________________________________________________ //VALUES //_________________________________________________________________ //create the indicators values eg 90 | 45 | 356 for (int c = 0; c < dbcolumns.size(); c++) { //get the section of the current dbcolumn //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(g))) { XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getInt(dbcolumns.get(c).toString())); cell0.setCellStyle(stborder); colpos++; } //end of comparing if } //end of for loop //ART_highvolume XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getInt("ART_highvolume")); cell0.setCellStyle(stborder); colpos++; //HTC_highvolume XSSFCell cell1 = rw.createCell(colpos); cell1.setCellValue(conn.rs.getInt("HTC_highvolume")); cell1.setCellStyle(stborder); colpos++; //PMTCT_highvolume XSSFCell cell2 = rw.createCell(colpos); cell2.setCellValue(conn.rs.getInt("PMTCT_highvolume")); cell2.setCellStyle(stborder); colpos++; //PMTCT_highvolume XSSFCell cell2a = rw.createCell(colpos); cell2a.setCellValue(conn.rs.getInt("GSN")); cell2a.setCellStyle(stborder); colpos++; String isvalidated = "Yes"; if (conn.rs.getString("Form_Validated").equals("0")) { isvalidated = "No"; } XSSFCell cell3 = rw.createCell(colpos); cell3.setCellValue(isvalidated); cell3.setCellStyle(stborder); colpos++; rowpos++; if (colpos > 1) { colposcopy = colpos - 1; } colpos = 0; } // end of while loop getting data from the db //____________________________________________________________________________________ //COLUMN AUTOSIZE COLUMN WIDTH , AUTOFILTER & AUTOSUM //____________________________________________________________________________________ //At this point we are sure this is the last row and we have exhausted fetching data for all periods //System.out.println(" Column position Before "+colposcopy); // if (lastperiod.equalsIgnoreCase(conn.rs.getString("period"))) { //System.out.println(" Column after "+colposcopy); shet.setAutoFilter(new CellRangeAddress(1, rowpos - 1, 0, colposcopy)); //System.out.println("1,"+rowpos+",0,"+colposcopy); for (int e = 0; e < Headerorgunits.size(); e++) { shet.autoSizeColumn(e); } //autosum XSSFRow initialrow = shet.getRow(2); XSSFRow prevrow = shet.getRow(rowpos - 1); XSSFRow cumrow = shet.getRow(cumulativestatingpoint);//not used for now but let it stay here may be of use in future XSSFRow rwsum = shet.createRow(rowpos); int colpossum = 0; int firstcols = 5; int periodcolumn = 0; for (int f = 0; f < firstcols; f++) { if (f == 0) { XSSFCell cellsum = rwsum.createCell(0); cellsum.setCellValue("Total"); cellsum.setCellStyle(stylesum); } else if (f > 0 && f < firstcols) { XSSFCell cellsum = rwsum.createCell(f); cellsum.setCellValue(" "); cellsum.setCellStyle(stylesum); } } for (int c = 0; c < dbcolumns.size(); c++) { if (worksheets.get(c).equals(distinctsheets.get(g))) { XSSFCell cellsum = rwsum.createCell(colpossum + firstcols); XSSFCell initialcell = initialrow.getCell(colpossum + firstcols); String cellformula = ""; XSSFCell prevcell = prevrow.getCell(colpossum + firstcols);//the last cell of the current column. rem whe are looping through all the columns //periodcolumncell XSSFCell initialperiodcell = initialrow.getCell(periodcolumn);// the first cell of the period(month, year) column XSSFCell currentperiodcell = prevrow.getCell(periodcolumn);//the last cell of the period column cellsum.setCellType(cellsum.CELL_TYPE_FORMULA); String startcellreference = initialcell.getReference(); String lastavailableperiod = currentperiodcell.getStringCellValue(); if (iscumulative.get(c).equals("1")) { //initialcell=cumrow.getCell(colpossum+firstcols); //Create a formula that sums all the data for the last column to appear in the row. This should pick the first column which is the period column. cellformula = "SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX(" + initialperiodcell.getReference() + ":" + currentperiodcell.getReference() + ",1,1),ROW(" + initialperiodcell.getReference() + ":" + currentperiodcell.getReference() + ")-ROW(INDEX(" + initialperiodcell.getReference() + ":" + currentperiodcell.getReference() + ",1,1)),0))=1),--(" + initialperiodcell.getReference() + ":" + currentperiodcell.getReference() + "=\"" + lastavailableperiod + "\")," + startcellreference + ":" + prevcell.getReference() + ")"; } else if (ispercent.get(c).equals("1")) { //initialcell=cumrow.getCell(colpossum+firstcols); //Create a formula that sums all the data for the last column to appear in the row. This should pick the first column which is the period column. cellformula = "ROUNDUP(SUBTOTAL(9," + startcellreference + ":" + prevcell.getReference() + "),1)"; // round of maximum values } else { cellformula = "SUBTOTAL(9," + startcellreference + ":" + prevcell.getReference() + ")"; } //for cumulative indicators, we need to do column total for the last selected month //we therefore need to always track where the previous month started at. cellsum.setCellFormula(cellformula); cellsum.setCellStyle(stylesum); colpossum++; } } //merge last cell shet.addMergedRegion(new CellRangeAddress(rowpos, rowpos, 0, 4)); // } end of checking if this is the last month //disbled for now // rowstartpersheet[g] = rowpos; } // end of distinct sheets report // }//end of monthly loop IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); System.out.println("" + form.toUpperCase().trim() + "_RPT_FOR_" + year.trim() + "(" + periodlabel + ")_GEN_" + createdOn.trim() + ".xlsx"); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=" + form.toUpperCase().trim() + "_REPORT_FOR_" + year.trim() + "(" + periodlabel.replace(" ", "") + ")_CREATED_" + createdOn.trim() + ".xlsx"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(allStaticReportsdynamic.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.allStaticReportsMonthly.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String monthrange = ""; String periodlabel = ""; String period = "Month"; //The default one try {/*from w ww . jav a2 s. co m*/ response.setContentType("text/html;charset=UTF-8"); //a page to get Report of all the servlets String months[] = null; String year = "2016"; String month = "7"; String county = ""; String form = "moh731"; if (request.getParameter("year") != null) { year = request.getParameter("year"); } if (request.getParameter("county") != null) { county = request.getParameter("county"); } if (request.getParameter("month") != null) { months = request.getParameterValues("month"); } if (request.getParameter("form") != null) { form = request.getParameter("form"); } String pivotform = form; if (form.equalsIgnoreCase("MOH 731")) { form = "MOH731"; } if (form.equalsIgnoreCase("MOH 711A")) { form = "MOH711"; } if (form.equalsIgnoreCase("MOH 711 (New)")) { form = "moh711_new"; } String facilitywhere = ""; String yearwhere = ""; String monthwhere = ""; String countywhere = ""; String districtwhere = ""; String reporttype = ""; if (!year.equals("")) { yearwhere = " and Annee = '" + year + "'"; } if (!county.equals("")) { countywhere = " and countyid = '" + county + "'"; } dbConn conn = new dbConn(); //an array to store haeder information. //the header information should appear only if certain parameters are met //The parameters listed in here can be removed if the report type doesnt require some parameters ArrayList Headerorgunits = new ArrayList(); Headerorgunits.add(period); Headerorgunits.add("County"); Headerorgunits.add("Sub-County"); Headerorgunits.add("Facility"); Headerorgunits.add("MFL Code"); //An arralist to store a list of columns that will be selected from the database ArrayList dbcolumns = new ArrayList(); ArrayList labels = new ArrayList(); ArrayList tablename = new ArrayList(); ArrayList iscumulative = new ArrayList(); ArrayList ispercent = new ArrayList(); // ArrayList isactive=new ArrayList(); //An arralist to store a list of worksheets that will be selected from the sections and the respective service area to determine the facilities whose data will appear in that sheet ArrayList worksheets = new ArrayList(); //An arralist to store distinct worksheets. This will be derived from the the sections column ArrayList distinctsheets = new ArrayList(); ArrayList distinctservicearea = new ArrayList(); //create an array to store the number of row for each excel worksheet. //This will help in retrieving the number of rows for each month since we are wring data for different months with increasing rows. //the size of that array will be determined by the number of excel worksheets String selectdistinctworksheet = "select section,servicearea from pivottable where form='" + form.replace("_", "") + "' and active='1' group by section order by order_per_form"; conn.rs = conn.st.executeQuery(selectdistinctworksheet); while (conn.rs.next()) { //add the name of distinct sections distinctsheets.add(conn.rs.getString(1).replace("/", "_")); String servicearea = " 2=2 "; if (conn.rs.getString(2) != null) { servicearea = " " + conn.rs.getString(2) + "=1"; } distinctservicearea.add(servicearea); } int rowstartpersheet[] = new int[distinctsheets.size()]; //initialize the row start with 2 for (int x = 0; x < rowstartpersheet.length; x++) { rowstartpersheet[x] = 2; } String getattribs = "select indicator,label,section,cumulative,percentage,active ,shortlabel from pivottable where form='" + form.replace("_", "") + "' order by order_per_form, section"; conn.rs = conn.st.executeQuery(getattribs); while (conn.rs.next()) { //add active indicators only if (conn.rs.getString("active").equals("1")) { //System.out.println(conn.rs.getString("indicator")+""); //add indicator dbcolumns.add(conn.rs.getString("indicator")); //add label if (form.equalsIgnoreCase("MOH731")) { labels.add(conn.rs.getString("shortlabel") + " \n" + conn.rs.getString("label")); } else { labels.add(conn.rs.getString("label")); } //add worksheets worksheets.add(conn.rs.getString("section").replace("/", "_")); String perc = "0"; String cum = "0"; if (conn.rs.getString("cumulative") != null) { iscumulative.add(conn.rs.getString("cumulative")); } else { iscumulative.add(cum); } if (conn.rs.getString("percentage") != null) { ispercent.add(conn.rs.getString("percentage")); } else { ispercent.add(perc); } } //end of active } //end of pivot table active //labels.add("ART High Volume"); //labels.add("HTC High Volume"); //labels.add("PMTCT High Volume"); //System.out.println(perfacilselect); //______________________________________________________________________________________ // CREATE THE WORKSHEETS //______________________________________________________________________________________ XSSFWorkbook wb = new XSSFWorkbook(); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFCellStyle stylesum = wb.createCellStyle(); stylesum.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylesum.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylesum.setBorderTop(HSSFCellStyle.BORDER_THIN); stylesum.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylesum.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylesum.setBorderRight(HSSFCellStyle.BORDER_THIN); stylesum.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); stylesum.setFont(fontx); stylesum.setWrapText(true); int cellrange[] = new int[4]; for (int b = 0; b < distinctsheets.size(); b++) { XSSFSheet shet = wb.createSheet(distinctsheets.get(b).toString().toUpperCase()); //Made my life veery simple... shet.setDisplayGridlines(false); shet.createFreezePane(5, 2); int firstcell = 1; //shet.setAutoFilter(CellRangeAddress.valueOf("A2:N1")); //create headers for that worksheet XSSFRow rw = shet.createRow(1); int headercellpos = 0; //create the orgunit header eg COUNTY | SUBCOUNTY | FACILITY for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(Headerorgunits.get(e).toString()); cell0.setCellStyle(stylex); headercellpos++; //shet.setColumnWidth(e, 6000); } //create the indicators header eg HV0101 | HIV 09676 | TOTAL for (int c = 0; c < dbcolumns.size(); c++) { //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(b))) { shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(labels.get(c).toString()); cell0.setCellStyle(stylex); headercellpos++; } //end of comparing if } //end of for loop //create is validated header shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue("ART High Volume"); cell0.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(headercellpos, 6000); XSSFCell cell1 = rw.createCell(headercellpos); cell1.setCellValue("HTC High Volume"); cell1.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(headercellpos, 6000); XSSFCell cell2 = rw.createCell(headercellpos); cell2.setCellValue("PMTCT High Volume"); cell2.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(headercellpos, 6000); XSSFCell cell3 = rw.createCell(headercellpos); cell3.setCellValue("Form Validated ?"); cell3.setCellStyle(stylex); headercellpos++; } //______________________________________________________________________________________ //______________________________________________________________________________________ //-------------------------------------------------------------------------------------------- // MONTH LOOPS //-------------------------------------------------------------------------------------------- int cumulativestatingpoint = 3; boolean cumstartpointnoted = false; int colposcopy = 0; for (int w = 0; w < months.length; w++) { month = months[w]; if (!month.equals("")) { monthwhere = " and Mois = '" + month + "'"; } String getMonth = "SELECT name FROM month WHERE id='" + month + "'"; conn.rs = conn.st.executeQuery(getMonth); if (conn.rs.next() == true) { monthrange = conn.rs.getString(1); if (periodlabel.equals("")) { periodlabel = monthrange.substring(0, 3); } else { periodlabel += "_" + monthrange.substring(0, 3); } } String perfacilselect = "select '" + monthrange + "', CONCAT(UPPER(SUBSTRING(County,1,1)),LOWER(SUBSTRING(County,2))) as County , DistrictNom as District , SubPartnerNom as facility ,CentreSanteId as mflcode , district.CountyID as countyid , "; //-------------------------------------------------------------------------------------------- // PREPARE SELECT //-------------------------------------------------------------------------------------------- //prepare selects for (int a = 0; a < dbcolumns.size(); a++) { //if the indicator is a percent, get an avaerage if (ispercent.get(a).equals("1")) { perfacilselect += " AVG(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } else if (iscumulative.get(a).equals("1")) { perfacilselect += " " + dbcolumns.get(a) + " as " + dbcolumns.get(a); } else { perfacilselect += " SUM(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } //if the item is not the last, append a comma //if(a<dbcolumns.size()-1){ perfacilselect += " ,"; // } } //---------------------------------add highvolume------------------------------------------------ perfacilselect += " IFNULL(ART_highvolume,0) as ART_highvolume, IFNULL(HTC_highvolume,0) as HTC_highvolume, IFNULL(PMTCT_highvolume,0) as PMTCT_highvolume,"; //------------------------------------------------------------------------------------------------- // FROM //------------------------------------------------------------------------------------ perfacilselect += " isValidated as Form_Validated from " + form + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on " + form + ".SubPartnerID = subpartnera.SubPartnerID "; //------------------------------------------------------------------------------------------ // WHERE //------------------------------------------------------------------------------------------ perfacilselect += " where 1=1 " + monthwhere + yearwhere; //----------------------------------------------------------------------------------------- //GROUP BY //---------------------------------------------------------------------------------------- perfacilselect += " group by subpartnera.SubPartnerID"; String sectioncopy = ""; int sheetpos = 0; int rowpos = 2; //-----------------INSIDE THE DATA FORM--------------------------------- //if the section changes, change the position of the worksheet too //also, reset the position counter to begin from 2 again. XSSFSheet shet = null; // if(--!sectioncopy.equals(shet)){} //create the org unit data values e.g BARINGO | BARINGO CENTRAL |KABARNET DISTRICT HOSPITAL | MFL CODE for (int g = 0; g < distinctsheets.size(); g++) { if (w == months.length - 1 && cumstartpointnoted == false) { //save the current row position. //get it from the current rowstartpersheet[g] cumulativestatingpoint = rowstartpersheet[0]; cumstartpointnoted = true; //this ensures that we are fetching dataonce. // System.out.println("LAST STARTING POINT__"+cumulativestatingpoint); } rowpos = rowstartpersheet[g]; shet = wb.getSheetAt(g); int colpos = 0; String finalquery = perfacilselect.replace("1=1", distinctservicearea.get(g).toString()); System.out.println("" + finalquery); conn.rs = conn.st.executeQuery(finalquery); while (conn.rs.next()) { //the fourth cell should XSSFRow rw = shet.createRow(rowpos); for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(colpos); //for mfl code, last header, output integers if (e > 3) { cell0.setCellValue(conn.rs.getInt(e + 1)); } else { cell0.setCellValue(conn.rs.getString(e + 1)); } cell0.setCellStyle(style2); colpos++; } //_________________________________________________________________ //VALUES //_________________________________________________________________ //create the indicators values eg 90 | 45 | 356 for (int c = 0; c < dbcolumns.size(); c++) { //get the section of the current dbcolumn //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(g))) { XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getInt(dbcolumns.get(c).toString())); cell0.setCellStyle(stborder); colpos++; } //end of comparing if } //end of for loop //ART_highvolume XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getInt("ART_highvolume")); cell0.setCellStyle(stborder); colpos++; //HTC_highvolume XSSFCell cell1 = rw.createCell(colpos); cell1.setCellValue(conn.rs.getInt("HTC_highvolume")); cell1.setCellStyle(stborder); colpos++; //PMTCT_highvolume XSSFCell cell2 = rw.createCell(colpos); cell2.setCellValue(conn.rs.getInt("PMTCT_highvolume")); cell2.setCellStyle(stborder); colpos++; String isvalidated = "Yes"; if (conn.rs.getString("Form_Validated").equals("0")) { isvalidated = "No"; } XSSFCell cell3 = rw.createCell(colpos); cell3.setCellValue(isvalidated); cell3.setCellStyle(stborder); colpos++; rowpos++; if (colpos > 1) { colposcopy = colpos - 1; } colpos = 0; } // end of while loop getting data from the db //____________________________________________________________________________________ //COLUMN AUTOSIZE COLUMN WIDTH , AUTOFILTER & AUTOSUM //____________________________________________________________________________________ //At this point we are sure this is the last row and we have exhausted fetching data for all months //System.out.println(" Column position Before "+colposcopy); if (w == months.length - 1) { //System.out.println(" Column after "+colposcopy); shet.setAutoFilter(new CellRangeAddress(1, rowpos - 1, 0, colposcopy)); //System.out.println("1,"+rowpos+",0,"+colposcopy); for (int e = 0; e < Headerorgunits.size(); e++) { shet.autoSizeColumn(e); } //autosum XSSFRow rwsum = shet.createRow(rowpos); XSSFRow initialrow = shet.getRow(2); XSSFRow prevrow = shet.getRow(rowpos - 1); XSSFRow cumrow = shet.getRow(cumulativestatingpoint);//not used for now but let it stay here may be of use in future int colpossum = 0; int firstcols = 5; int periodcolumn = 0; for (int f = 0; f < firstcols; f++) { if (f == 0) { XSSFCell cellsum = rwsum.createCell(0); cellsum.setCellValue("Total"); cellsum.setCellStyle(stylesum); } else if (f > 0 && f < firstcols) { XSSFCell cellsum = rwsum.createCell(f); cellsum.setCellValue(" "); cellsum.setCellStyle(stylesum); } } for (int c = 0; c < dbcolumns.size(); c++) { if (worksheets.get(c).equals(distinctsheets.get(g))) { XSSFCell cellsum = rwsum.createCell(colpossum + firstcols); XSSFCell initialcell = initialrow.getCell(colpossum + firstcols); String cellformula = ""; XSSFCell prevcell = prevrow.getCell(colpossum + firstcols);//the last cell of the current column. rem whe are looping through all the columns //periodcolumncell XSSFCell initialperiodcell = initialrow.getCell(periodcolumn);// the first cell of the period(month, year) column XSSFCell currentperiodcell = prevrow.getCell(periodcolumn);//the last cell of the period column cellsum.setCellType(cellsum.CELL_TYPE_FORMULA); String startcellreference = initialcell.getReference(); String lastavailableperiod = currentperiodcell.getStringCellValue(); if (iscumulative.get(c).equals("1")) { //initialcell=cumrow.getCell(colpossum+firstcols); //Create a formula that sums all the data for the last column to appear in the row. This should pick the first column which is the period column. cellformula = "SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX(" + initialperiodcell.getReference() + ":" + currentperiodcell.getReference() + ",1,1),ROW(" + initialperiodcell.getReference() + ":" + currentperiodcell.getReference() + ")-ROW(INDEX(" + initialperiodcell.getReference() + ":" + currentperiodcell.getReference() + ",1,1)),0))=1),--(" + initialperiodcell.getReference() + ":" + currentperiodcell.getReference() + "=\"" + lastavailableperiod + "\")," + startcellreference + ":" + prevcell.getReference() + ")"; } else if (ispercent.get(c).equals("1")) { //initialcell=cumrow.getCell(colpossum+firstcols); //Create a formula that sums all the data for the last column to appear in the row. This should pick the first column which is the period column. cellformula = "ROUNDUP(SUBTOTAL(9," + startcellreference + ":" + prevcell.getReference() + "),1)"; // round of maximum values } else { cellformula = "SUBTOTAL(9," + startcellreference + ":" + prevcell.getReference() + ")"; } //for cumulative indicators, we need to do column total for the last selected month //we therefore need to always track where the previous month started at. cellsum.setCellFormula(cellformula); cellsum.setCellStyle(stylesum); colpossum++; } } //merge last cell shet.addMergedRegion(new CellRangeAddress(rowpos, rowpos, 0, 4)); } // rowstartpersheet[g] = rowpos; } // end of distinct sheets report } //end of monthly loop IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); System.out.println("" + form.toUpperCase().trim() + "_RPT_FOR_" + year.trim() + "(" + month.trim() + ")_GEN_" + createdOn.trim() + ".xlsx"); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=" + form.toUpperCase().trim() + "_REPORT_FOR_" + year.trim() + "(" + periodlabel + ")_CREATED_" + createdOn.trim() + ".xlsx"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(allStaticReportsMonthly.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.barCharts.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods./*from w ww . ja v a 2 s .c o m*/ * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try { dbConn conn = new dbConn(); wb = new HSSFWorkbook(); HSSFSheet shet2 = null; String year = ""; String site = ""; String period = ""; String cbo = ""; String startdate = "2015-01-01"; String enddate = "2015-03-30"; startdate = request.getParameter("startdate"); enddate = request.getParameter("enddate"); // year=request.getParameter("year"); //site=request.getParameter("sitecbo"); //period=request.getParameter("period"); //cbo=request.getParameter("staffcbo"); String sitename = ""; String cboname = ""; //begin a loop that will create as many reports as possible HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Cambria"); // font.setItalic(true); font.setBoldweight((short) 02); font.setColor(HSSFColor.BLACK.index); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setWrapText(true); style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); style.setAlignment(style.ALIGN_CENTER); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //%%%%%%%%%%%%%%%%HEADER FONTS AND COLORATION%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% HSSFFont font_header = wb.createFont(); font_header.setFontHeightInPoints((short) 10); font_header.setFontName("Eras Bold ITC"); // font.setItalic(true); font_header.setBoldweight((short) 05); font_header.setColor(HSSFColor.BLACK.index); CellStyle style_header = wb.createCellStyle(); style_header.setFont(font_header); style_header.setWrapText(true); style_header.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); style_header.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style_header.setAlignment(style_header.ALIGN_CENTER); // style_header.setBorderBottom(HSSFCellStyle.BORDER_THIN); // style_header.setBorderTop(HSSFCellStyle.BORDER_THIN); // style_header.setBorderRight(HSSFCellStyle.BORDER_THIN); // style_header.setBorderLeft(HSSFCellStyle.BORDER_THIN); //%%%%%%%%%%%%%%%%%%%%%%%%%DATA FONT%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% //font data HSSFFont datafont = wb.createFont(); datafont.setBoldweight((short) 03); datafont.setColor(HSSFColor.BLACK.index); datafont.setFontHeightInPoints((short) 10); datafont.setFontName("Cambria"); datafont.setItalic(true); //bold font HSSFFont bolfont = wb.createFont(); bolfont.setBoldweight((short) 05); bolfont.setColor(HSSFColor.BLACK.index); bolfont.setFontHeightInPoints((short) 12); bolfont.setFontName("Cambria"); //=========================ROW STYLE=============================== HSSFCellStyle rowstyle = wb.createCellStyle(); rowstyle.setWrapText(true); //=======INNER DATA STYLING=========================== CellStyle innerdata_style = wb.createCellStyle(); innerdata_style.setFont(datafont); innerdata_style.setWrapText(true); innerdata_style.setAlignment(innerdata_style.ALIGN_CENTER); innerdata_style.setFillForegroundColor(HSSFColor.WHITE.index); innerdata_style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN); innerdata_style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle lastcellrighborder = wb.createCellStyle(); lastcellrighborder.setFont(datafont); lastcellrighborder.setWrapText(true); lastcellrighborder.setAlignment(lastcellrighborder.ALIGN_CENTER); lastcellrighborder.setFillForegroundColor(HSSFColor.WHITE.index); lastcellrighborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // lastcellrighborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); // lastcellrighborder.setBorderTop(HSSFCellStyle.BORDER_THIN); lastcellrighborder.setBorderRight(HSSFCellStyle.BORDER_THIN); // lastcellrighborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); lastcellrighborder.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle innerdata_style2 = wb.createCellStyle(); innerdata_style2.setFont(bolfont); innerdata_style2.setWrapText(true); innerdata_style2.setAlignment(innerdata_style.ALIGN_LEFT); innerdata_style2.setFillForegroundColor(HSSFColor.WHITE.index); innerdata_style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN); innerdata_style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle orangestyle = wb.createCellStyle(); orangestyle.setFont(bolfont); orangestyle.setWrapText(true); orangestyle.setAlignment(orangestyle.ALIGN_CENTER); orangestyle.setFillForegroundColor(HSSFColor.ORANGE.index); orangestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN); orangestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //Code colors CellStyle lg = wb.createCellStyle(); lg.setFont(bolfont); lg.setWrapText(true); lg.setAlignment(lg.ALIGN_CENTER); lg.setFillForegroundColor(HSSFColor.GREEN.index); lg.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); lg.setBorderBottom(HSSFCellStyle.BORDER_THIN); lg.setBorderTop(HSSFCellStyle.BORDER_THIN); lg.setBorderRight(HSSFCellStyle.BORDER_THIN); lg.setBorderLeft(HSSFCellStyle.BORDER_THIN); lg.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle Y = wb.createCellStyle(); Y.setFont(bolfont); Y.setWrapText(true); Y.setAlignment(Y.ALIGN_CENTER); Y.setFillForegroundColor(HSSFColor.WHITE.index); Y.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); Y.setBorderBottom(HSSFCellStyle.BORDER_THIN); Y.setBorderTop(HSSFCellStyle.BORDER_THIN); Y.setBorderRight(HSSFCellStyle.BORDER_THIN); Y.setBorderLeft(HSSFCellStyle.BORDER_THIN); Y.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle R = wb.createCellStyle(); R.setFont(bolfont); R.setWrapText(true); R.setAlignment(R.ALIGN_CENTER); R.setFillForegroundColor(HSSFColor.RED.index); R.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); R.setBorderBottom(HSSFCellStyle.BORDER_THIN); R.setBorderTop(HSSFCellStyle.BORDER_THIN); R.setBorderRight(HSSFCellStyle.BORDER_THIN); R.setBorderLeft(HSSFCellStyle.BORDER_THIN); R.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //=======INNER LEFT DATA STYLING=========================== CellStyle binnerdata_style2 = wb.createCellStyle(); binnerdata_style2.setFont(datafont); binnerdata_style2.setWrapText(true); binnerdata_style2.setAlignment(binnerdata_style2.ALIGN_LEFT); binnerdata_style2.setFillForegroundColor(HSSFColor.WHITE.index); binnerdata_style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); binnerdata_style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderTop(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderRight(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //create a header //=======================Domainname styles CellStyle dnamestyle = wb.createCellStyle(); dnamestyle.setFont(bolfont); dnamestyle.setWrapText(true); dnamestyle.setAlignment(dnamestyle.ALIGN_LEFT); dnamestyle.setFillForegroundColor(HSSFColor.WHITE.index); dnamestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); dnamestyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderTop(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderRight(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); dnamestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); shet2 = wb.createSheet("Column Charts Per Cbo"); shet2.setColumnWidth(0, 12000); shet2.setColumnWidth(1, 12000); shet2.setColumnWidth(2, 4000); shet2.setColumnWidth(3, 10000); shet2.setColumnWidth(4, 5000); shet2.setColumnWidth(5, 5000); shet2.setColumnWidth(6, 5000); shet2.setColumnWidth(7, 5000); shet2.setColumnWidth(8, 5000); shet2.setColumnWidth(9, 5000); shet2.setColumnWidth(10, 5000); shet2.setColumnWidth(11, 5000); shet2.setColumnWidth(12, 5000); shet2.setColumnWidth(13, 5000); //11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 //11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 //11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 //11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 String gettables = "SELECT avg(value) as domainvalue,domain_totals.domainid as domainid,domain_name, section_name,cbo,avg(aggregate_sum) as aggregate_sum FROM ovc_lip.domain_totals join (sites join cbo on sites.cbo_id=cbo.cboid) on domain_totals.site=sites.site_id join ( domains join sections on domains.section_id=sections.section_id ) on domain_totals.domainid=domains.domain_id where date between '" + startdate + "' and '" + enddate + "' group by cbo.cboid,domainid order by cbo,domainid"; System.out.println(gettables); conn.rs = conn.st.executeQuery(gettables); int rwcount = 0; int rowcountcopy = 0; String tableheaders[] = { "Section", "Domain", "% Overall Achievement", "Column chart" }; HSSFRow rwx = null; int monitorrows = 0; int secAcopy = 0; int secBcopy = 0; HSSFCell celx = null; int noofcols = 3; boolean isrow1 = true; while (conn.rs.next()) { //if the section has changed monitorrows++; String domainid = conn.rs.getString("domainid"); float domainvalue = conn.rs.getFloat("domainvalue"); domainvalue = domainvalue * 100; domainvalue = Math.round(domainvalue); float totalsum = conn.rs.getFloat("aggregate_sum"); int dmn = (int) domainvalue; totalsum = Math.round(totalsum); //determine the cell to print data on int ttlsm = (int) totalsum; int hearderheight = 40; //if its the first row in each if (isrow1) { isrow1 = false; rwx = shet2.createRow(rwcount); HSSFCell headercel = rwx.createCell(0); headercel.setCellValue(conn.rs.getString("cbo")); headercel.setCellStyle(style); rwx.setHeightInPoints(hearderheight); //create a blank HSSFCell cel = null; for (int b = 1; b < tableheaders.length; b++) { cel = rwx.createCell(b); cel.setCellValue(""); cel.setCellStyle(style); } //now merge the header cell shet2.addMergedRegion(new CellRangeAddress(rwcount, rwcount, 0, tableheaders.length - 1)); rwcount++; //now create the header part HSSFRow headerrw = shet2.createRow(rwcount); rwx.setHeightInPoints(hearderheight); for (int b = 0; b < tableheaders.length; b++) { HSSFCell cel1 = headerrw.createCell(b); cel1.setCellValue(tableheaders[b]); cel1.setCellStyle(style); } rwcount++; } //create the section part HSSFRow rw = shet2.createRow(rwcount); rw.setHeightInPoints(25); //column one --- section HSSFCell seccell = rw.createCell(0); seccell.setCellValue(conn.rs.getString("section_name")); seccell.setCellStyle(dnamestyle); HSSFCell domcell = rw.createCell(1); domcell.setCellValue(conn.rs.getString("domain_name")); domcell.setCellStyle(dnamestyle); //values only HSSFCell domval = rw.createCell(2); domval.setCellValue(dmn); domval.setCellStyle(dnamestyle); HSSFCell blank = rw.createCell(3); blank.setCellValue(""); blank.setCellStyle(dnamestyle); //now, draw the chart HSSFPatriarch patriarch = shet2.createDrawingPatriarch(); HSSFTextbox textbox1 = patriarch.createTextbox( new HSSFClientAnchor(0, 0, (dmn * 10), 255, (short) 3, rwcount, (short) 3, rwcount)); textbox1.setString(new HSSFRichTextString("" + dmn)); textbox1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //green 18,174,55 //red 250 32 32 //yellow 248 255 9 if (dmn >= 75) { textbox1.setFillColor(18, 174, 55); } else if (dmn > 59 && dmn < 75) { textbox1.setFillColor(248, 255, 9); } else { textbox1.setFillColor(250, 32, 32); } rwcount++; if (monitorrows == 4) { shet2.addMergedRegion(new CellRangeAddress(rwcount - 4, rwcount - 1, 0, 0)); } if (monitorrows == 12) { shet2.addMergedRegion(new CellRangeAddress(rwcount - 8, rwcount - 1, 0, 0)); HSSFRow lastrw = shet2.createRow(rwcount); lastrw.setHeightInPoints(25); //now create a row with average HSSFCell avcell0 = lastrw.createCell(0); avcell0.setCellValue("Average"); avcell0.setCellStyle(dnamestyle); HSSFCell avcell = lastrw.createCell(1); avcell.setCellValue("Average"); avcell.setCellStyle(dnamestyle); HSSFCell avcell1 = lastrw.createCell(2); avcell1.setCellValue(ttlsm); avcell1.setCellStyle(dnamestyle); HSSFCell blank1 = lastrw.createCell(3); blank1.setCellValue(""); blank1.setCellStyle(dnamestyle); HSSFTextbox textbox = patriarch.createTextbox( new HSSFClientAnchor(0, 0, (dmn * (10)), 255, (short) 3, rwcount, (short) 3, rwcount)); textbox.setString(new HSSFRichTextString("" + ttlsm)); if (dmn >= 75) { textbox.setFillColor(18, 174, 55); } else if (dmn > 59 && dmn < 75) { textbox.setFillColor(248, 255, 9); } else { textbox.setFillColor(250, 32, 32); } textbox.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); isrow1 = true; monitorrows = 0; //dont print anything rwcount++; //last blank cell HSSFRow blankrow = shet2.createRow(rwcount); blankrow.setHeightInPoints(30); for (int b = 0; b < tableheaders.length; b++) { HSSFCell cel1 = blankrow.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(innerdata_style); } rwcount++; } } ///=========================end of while loop //write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=OVC_CBO_CHARTS_FROM_" + startdate + "_TO_" + enddate + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(resultspercbo.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.basicreports.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.//from www.java2s .com * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try { dbConn conn = new dbConn(); wb = new HSSFWorkbook(); HSSFSheet shet2 = null; String year = ""; String site = ""; String period = ""; String cbo = ""; year = request.getParameter("year"); site = request.getParameter("sitecbo"); period = request.getParameter("period"); cbo = request.getParameter("staffcbo"); String sitename = ""; String cboname = ""; conn.rs = conn.st.executeQuery("select cbo from cbo where cboid='" + cbo + "'"); if (conn.rs.next()) { cboname = conn.rs.getString(1); } conn.rs = conn.st.executeQuery("select site_name from sites where site_id='" + site + "'"); if (conn.rs.next()) { sitename = conn.rs.getString(1); } HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Cambria"); // font.setItalic(true); font.setBoldweight((short) 02); font.setColor(HSSFColor.BLACK.index); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setWrapText(true); style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); style.setAlignment(style.ALIGN_CENTER); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //%%%%%%%%%%%%%%%%HEADER FONTS AND COLORATION%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% HSSFFont font_header = wb.createFont(); font_header.setFontHeightInPoints((short) 10); font_header.setFontName("Eras Bold ITC"); // font.setItalic(true); font_header.setBoldweight((short) 05); font_header.setColor(HSSFColor.BLACK.index); CellStyle style_header = wb.createCellStyle(); style_header.setFont(font_header); style_header.setWrapText(true); style_header.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); style_header.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style_header.setAlignment(style_header.ALIGN_CENTER); // style_header.setBorderBottom(HSSFCellStyle.BORDER_THIN); // style_header.setBorderTop(HSSFCellStyle.BORDER_THIN); // style_header.setBorderRight(HSSFCellStyle.BORDER_THIN); // style_header.setBorderLeft(HSSFCellStyle.BORDER_THIN); //%%%%%%%%%%%%%%%%%%%%%%%%%DATA FONT%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% //font data HSSFFont datafont = wb.createFont(); datafont.setBoldweight((short) 03); datafont.setColor(HSSFColor.BLACK.index); datafont.setFontHeightInPoints((short) 10); datafont.setFontName("Cambria"); datafont.setItalic(true); //bold font HSSFFont bolfont = wb.createFont(); bolfont.setBoldweight((short) 05); bolfont.setColor(HSSFColor.BLACK.index); bolfont.setFontHeightInPoints((short) 12); bolfont.setFontName("Cambria"); //=======INNER DATA STYLING=========================== CellStyle innerdata_style = wb.createCellStyle(); innerdata_style.setFont(datafont); innerdata_style.setWrapText(true); innerdata_style.setAlignment(innerdata_style.ALIGN_CENTER); innerdata_style.setFillForegroundColor(HSSFColor.WHITE.index); innerdata_style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN); innerdata_style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle lastcellrighborder = wb.createCellStyle(); lastcellrighborder.setFont(datafont); lastcellrighborder.setWrapText(true); lastcellrighborder.setAlignment(lastcellrighborder.ALIGN_CENTER); lastcellrighborder.setFillForegroundColor(HSSFColor.WHITE.index); lastcellrighborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // lastcellrighborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); // lastcellrighborder.setBorderTop(HSSFCellStyle.BORDER_THIN); lastcellrighborder.setBorderRight(HSSFCellStyle.BORDER_THIN); // lastcellrighborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); lastcellrighborder.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle innerdata_style2 = wb.createCellStyle(); innerdata_style2.setFont(bolfont); innerdata_style2.setWrapText(true); innerdata_style2.setAlignment(innerdata_style.ALIGN_LEFT); innerdata_style2.setFillForegroundColor(HSSFColor.WHITE.index); innerdata_style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN); innerdata_style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //Code colors CellStyle lg = wb.createCellStyle(); lg.setFont(bolfont); lg.setWrapText(true); lg.setAlignment(lg.ALIGN_CENTER); lg.setFillForegroundColor(HSSFColor.GREEN.index); lg.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); lg.setBorderBottom(HSSFCellStyle.BORDER_THIN); lg.setBorderTop(HSSFCellStyle.BORDER_THIN); lg.setBorderRight(HSSFCellStyle.BORDER_THIN); lg.setBorderLeft(HSSFCellStyle.BORDER_THIN); lg.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle Y = wb.createCellStyle(); Y.setFont(bolfont); Y.setWrapText(true); Y.setAlignment(Y.ALIGN_CENTER); Y.setFillForegroundColor(HSSFColor.YELLOW.index); Y.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); Y.setBorderBottom(HSSFCellStyle.BORDER_THIN); Y.setBorderTop(HSSFCellStyle.BORDER_THIN); Y.setBorderRight(HSSFCellStyle.BORDER_THIN); Y.setBorderLeft(HSSFCellStyle.BORDER_THIN); Y.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle R = wb.createCellStyle(); R.setFont(bolfont); R.setWrapText(true); R.setAlignment(R.ALIGN_CENTER); R.setFillForegroundColor(HSSFColor.RED.index); R.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); R.setBorderBottom(HSSFCellStyle.BORDER_THIN); R.setBorderTop(HSSFCellStyle.BORDER_THIN); R.setBorderRight(HSSFCellStyle.BORDER_THIN); R.setBorderLeft(HSSFCellStyle.BORDER_THIN); R.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //=======INNER LEFT DATA STYLING=========================== CellStyle binnerdata_style2 = wb.createCellStyle(); binnerdata_style2.setFont(datafont); binnerdata_style2.setWrapText(true); binnerdata_style2.setAlignment(binnerdata_style2.ALIGN_LEFT); binnerdata_style2.setFillForegroundColor(HSSFColor.WHITE.index); binnerdata_style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); binnerdata_style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderTop(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderRight(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //create a header //=======================Domainname styles CellStyle dnamestyle = wb.createCellStyle(); dnamestyle.setFont(bolfont); dnamestyle.setWrapText(true); dnamestyle.setAlignment(dnamestyle.ALIGN_LEFT); dnamestyle.setFillForegroundColor(HSSFColor.WHITE.index); dnamestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); dnamestyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderTop(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderRight(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); dnamestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); shet2 = wb.createSheet("Report"); shet2.setColumnWidth(0, 10000); shet2.setColumnWidth(1, 5000); shet2.setColumnWidth(2, 5000); shet2.setColumnWidth(3, 5000); shet2.setColumnWidth(4, 8000); shet2.setColumnWidth(5, 8000); //create header one HSSFRow header = shet2.createRow(0); header.setHeightInPoints(30); HSSFCell cel1 = header.createCell(0); cel1.setCellValue("APHIAplus NURU YA BONDE"); cel1.setCellStyle(style); for (int b = 1; b <= 5; b++) { cel1 = header.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(style); } shet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); //create header two HSSFRow header2 = shet2.createRow(1); header2.setHeightInPoints(28); HSSFCell cel2 = null; for (int b = 1; b <= 5; b++) { cel2 = header2.createCell(b); cel2.setCellValue(""); cel2.setCellStyle(style); } cel2 = header2.createCell(0); cel2.setCellValue("OVC LIP SUPPORT SUPERVISION DASH BOARD"); cel2.setCellStyle(style); shet2.addMergedRegion(new CellRangeAddress(1, 1, 0, 5)); //cbo name //create header three HSSFRow header3 = shet2.createRow(2); HSSFCell cel3 = header3.createCell(0); cel3.setCellValue("Name of LIP/CBO"); cel3.setCellStyle(innerdata_style2); HSSFCell cel4 = header3.createCell(1); cel4.setCellValue("" + cboname); cel4.setCellStyle(innerdata_style); //blank cells for purpose of clear worksheet only for (int x = 2; x <= 3; x++) { HSSFCell cel = header3.createCell(x); cel.setCellValue(""); cel.setCellStyle(innerdata_style); } HSSFCell cel5 = header3.createCell(4); cel5.setCellValue("Site Visited:"); cel5.setCellStyle(innerdata_style2); HSSFCell cel6 = header3.createCell(5); cel6.setCellValue("" + sitename); cel6.setCellStyle(lastcellrighborder); //add the width of this column //create a blank row whose last cell has a border HSSFRow blankrw = shet2.createRow(3); for (int z = 0; z < 5; z++) { HSSFCell cl = blankrw.createCell(z); cl.setCellValue(""); cl.setCellStyle(innerdata_style); } HSSFCell cl = blankrw.createCell(5); cl.setCellValue(""); cl.setCellStyle(lastcellrighborder); //==========DATE OF VISIT String mywhere = "site='" + site + "' and period='" + period + "' and year='" + year + "' "; String supervisor = ""; String dateofvisit = ""; String strengths = ""; String constraints = ""; String loadbasicdetails = "select * from backgroundinfor join staff on backgroundinfor.supervisor=staff.staff_id where " + mywhere + " "; System.out.println(loadbasicdetails); conn.rs = conn.st.executeQuery(loadbasicdetails); while (conn.rs.next()) { supervisor = conn.rs.getString("fname") + " " + conn.rs.getString("mname"); dateofvisit = conn.rs.getString("ass_date"); strengths = conn.rs.getString("strengths"); constraints = conn.rs.getString("constraints"); } //================Create the second header================= //create header three HSSFRow header4 = shet2.createRow(4); HSSFCell cel = header4.createCell(0); cel.setCellValue("Date of Visit"); cel.setCellStyle(innerdata_style2); HSSFCell cell = header4.createCell(1); cell.setCellValue("" + dateofvisit); cell.setCellStyle(innerdata_style); //blank cells for purpose of clear worksheet only for (int x = 2; x <= 3; x++) { HSSFCell ceel = header4.createCell(x); ceel.setCellValue(""); ceel.setCellStyle(innerdata_style); } HSSFCell cell5 = header4.createCell(4); cell5.setCellValue("Supervision Team Lead:"); cell5.setCellStyle(innerdata_style2); HSSFCell cell6 = header4.createCell(5); cell6.setCellValue("" + supervisor); cell6.setCellStyle(lastcellrighborder); //another blank row HSSFRow blankrw2 = shet2.createRow(5); for (int z = 0; z < 5; z++) { HSSFCell cl2 = blankrw2.createCell(z); cl2.setCellValue(""); cl2.setCellStyle(innerdata_style); } HSSFCell cl2 = blankrw2.createCell(5); cl2.setCellValue(""); cl2.setCellStyle(lastcellrighborder); //create a header String theaderar[] = { "Assesment Domain", "LG", "Y", "R", "Comments/Action", "" }; HSSFRow theader = shet2.createRow(6); for (int x = 0; x < theaderar.length; x++) { HSSFCell tcel = theader.createCell(x); tcel.setCellValue(theaderar[x]); if (theaderar[x].equalsIgnoreCase("LG")) { tcel.setCellStyle(lg); } else if (theaderar[x].equalsIgnoreCase("Y")) { tcel.setCellStyle(Y); } else if (theaderar[x].equalsIgnoreCase("R")) { tcel.setCellStyle(R); } else { tcel.setCellStyle(style); } } shet2.addMergedRegion(new CellRangeAddress(6, 6, 4, 5)); //SECTION A HEADER HSSFRow seca = shet2.createRow(7); HSSFCell tcel1 = seca.createCell(0); tcel1.setCellValue("Section A: Data management and Reporting Systems"); tcel1.setCellStyle(style); for (int b = 1; b <= 5; b++) { cel1 = seca.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(style); } shet2.addMergedRegion(new CellRangeAddress(7, 7, 0, 5)); String gettables = "SELECT domain_name,domains.domain_id as domainid,section_name,domains.section_id as secid,value as domainvalue,aggregate_sum,period,year,site FROM domains join sections on domains.section_id=sections.section_id join domain_totals on domains.domain_id=domain_totals.domainid where " + mywhere + " order by domainid"; System.out.println(gettables); conn.rs = conn.st.executeQuery(gettables); int rwcount = 8; HSSFRow rwx = null; HSSFCell celx = null; String sectioncopy = ""; while (conn.rs.next()) { if (sectioncopy.equals("")) { sectioncopy = conn.rs.getString("section_name"); } //if the section has changed if (!sectioncopy.equals(conn.rs.getString("section_name"))) { //create a section header //``````````````````````````````INNER SECTION HEADERS`````````````````` //``````````````````````````````INNER SECTION HEADERS`````````````````` //``````````````````````````````INNER SECTION HEADERS`````````````````` HSSFRow secb = shet2.createRow(rwcount); HSSFCell t = secb.createCell(0); t.setCellValue("Section " + conn.rs.getString("section_name")); t.setCellStyle(style); //for purpose of merging for (int b = 1; b <= 5; b++) { cel1 = secb.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(style); } //equalize copy and current value sectioncopy = conn.rs.getString("section_name"); shet2.addMergedRegion(new CellRangeAddress(rwcount, rwcount, 0, 5)); //increment rowcount to skip the current row rwcount++; } String valu[] = { conn.rs.getString("domain_name"), "", "", "", "", "" }; rwx = shet2.createRow(rwcount); for (int t = 0; t < valu.length; t++) { celx = rwx.createCell(t); celx.setCellValue("" + valu[t]); celx.setCellStyle(dnamestyle); } shet2.addMergedRegion(new CellRangeAddress(rwcount, rwcount, 4, 5)); //get the value of percentange achievement per domian //multiply by 100 //round off float domainvalue = conn.rs.getFloat("domainvalue"); domainvalue = domainvalue * 100; //BigDecimal bd=new BigDecimal(domainvalue).setScale(0,RoundingMode.HALF_EVEN); //domainval=bd.doubleValue(); domainvalue = Math.round(domainvalue); //determine the cell to print data on if (domainvalue >= 75) { celx = rwx.createCell(1); celx.setCellValue("" + domainvalue + "%"); celx.setCellStyle(lg); } else if (domainvalue >= 60 && domainvalue < 75) { celx = rwx.createCell(2); celx.setCellValue("" + domainvalue + "%"); celx.setCellStyle(Y); } else if (domainvalue < 60) { celx = rwx.createCell(3); celx.setCellValue("" + domainvalue + "%"); celx.setCellStyle(R); } rwcount++; } ///=========================end of while loop //====================STRENGTHS=========================== HSSFRow secb = shet2.createRow(rwcount); //for purpose of merging for (int b = 1; b <= 5; b++) { cel1 = secb.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(style); } HSSFCell t = secb.createCell(0); t.setCellValue("What has worked well and key areas of strengths observed"); t.setCellStyle(style); shet2.addMergedRegion(new CellRangeAddress(rwcount, rwcount, 0, 5)); rwcount++; HSSFRow str = shet2.createRow(rwcount); for (int b = 1; b <= 5; b++) { cel1 = str.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(dnamestyle); } HSSFCell t1 = str.createCell(0); t1.setCellValue("" + strengths); t1.setCellStyle(dnamestyle); shet2.addMergedRegion(new CellRangeAddress(rwcount, rwcount, 0, 5)); //for purpose of merging str.setHeightInPoints(50); rwcount++; //=======Contraints HSSFRow sec3 = shet2.createRow(rwcount); //for purpose of merging for (int b = 1; b <= 5; b++) { cel1 = sec3.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(style); } HSSFCell t2 = sec3.createCell(0); t2.setCellValue("Critical consraints affecting quality programming and data management"); t2.setCellStyle(style); shet2.addMergedRegion(new CellRangeAddress(rwcount, rwcount, 0, 5)); rwcount++; HSSFRow str2 = shet2.createRow(rwcount); for (int b = 1; b <= 5; b++) { cel1 = str2.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(dnamestyle); } HSSFCell t4 = str2.createCell(0); t4.setCellValue("" + constraints); t4.setCellStyle(dnamestyle); shet2.addMergedRegion(new CellRangeAddress(rwcount, rwcount, 0, 5)); str2.setHeightInPoints(50); rwcount++; //a line of codes String codes[] = { "LG - Meets Expectations (>=75%); ", " Y- Needs Improvement (60%- 74%);", "R - Needs Urgent Attention (<=59%);" }; HSSFRow rwl = shet2.createRow(rwcount); HSSFCell ce = rwl.createCell(0); ce.setCellValue("CODES"); ce.setCellStyle(dnamestyle); for (int b = 0; b < codes.length; b++) { ce = rwl.createCell(b + 1); ce.setCellValue("" + codes[b]); if (b == 0) { ce.setCellStyle(lg); } else if (b == 1) { ce.setCellStyle(Y); } else { ce.setCellStyle(R); } } ce = rwl.createCell(4); ce.setCellValue(""); ce.setCellStyle(dnamestyle); ce = rwl.createCell(5); ce.setCellValue(""); ce.setCellStyle(dnamestyle); shet2.addMergedRegion(new CellRangeAddress(rwcount, rwcount, 4, 5)); //write it as an excel attachment sitename = sitename.replace(" ", "_"); sitename = sitename.replace("'", ""); cboname = cboname.replace(" ", "_"); cboname = cboname.replace("'", "_"); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=OVC_LIP_REPORT_" + cboname + "_" + sitename + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(basicreports.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.countyreport.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods./*from w w w.j a va 2s .c om*/ * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try { dbConn conn = new dbConn(); wb = new HSSFWorkbook(); HSSFSheet shet2 = null; String year = ""; String site = ""; String period = ""; String cbo = ""; String startdate = "2015-01-01"; String enddate = "2015-03-30"; startdate = request.getParameter("startdate"); enddate = request.getParameter("enddate"); String getdistinctsites = "SELECT county.county_id as countyid,county_name FROM ovc_lip.backgroundinfor join (sites join (district join county on district.county_id=county.county_id) on sites.districtid=district.district_id) on backgroundinfor.site=sites.site_id where ass_date between '" + startdate + "' and '" + enddate + "' group by county_name "; ArrayList countyids = new ArrayList(); ArrayList countynames = new ArrayList(); countyids.add("1000"); countynames.add("OVERALL COUNTIES REPORT"); //ArrayList years=new ArrayList(); //ArrayList periods=new ArrayList(); //ArrayList cbos=new ArrayList(); conn.rs = conn.st.executeQuery(getdistinctsites); while (conn.rs.next()) { countyids.add(conn.rs.getString(1)); countynames.add(conn.rs.getString(2).toUpperCase() + " COUNTY"); } // year=request.getParameter("year"); //site=request.getParameter("sitecbo"); //period=request.getParameter("period"); //cbo=request.getParameter("staffcbo"); String sitename = ""; String cboname = ""; //begin a loop that will create as many reports as possible for (int u = 0; u < countyids.size(); u++) { HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Cambria"); // font.setItalic(true); font.setBoldweight((short) 02); font.setColor(HSSFColor.BLACK.index); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setWrapText(true); style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); style.setAlignment(style.ALIGN_CENTER); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //%%%%%%%%%%%%%%%%HEADER FONTS AND COLORATION%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% HSSFFont font_header = wb.createFont(); font_header.setFontHeightInPoints((short) 10); font_header.setFontName("Eras Bold ITC"); // font.setItalic(true); font_header.setBoldweight((short) 05); font_header.setColor(HSSFColor.BLACK.index); CellStyle style_header = wb.createCellStyle(); style_header.setFont(font_header); style_header.setWrapText(true); style_header.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); style_header.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style_header.setAlignment(style_header.ALIGN_CENTER); // style_header.setBorderBottom(HSSFCellStyle.BORDER_THIN); // style_header.setBorderTop(HSSFCellStyle.BORDER_THIN); // style_header.setBorderRight(HSSFCellStyle.BORDER_THIN); // style_header.setBorderLeft(HSSFCellStyle.BORDER_THIN); //%%%%%%%%%%%%%%%%%%%%%%%%%DATA FONT%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% //font data HSSFFont datafont = wb.createFont(); datafont.setBoldweight((short) 03); datafont.setColor(HSSFColor.BLACK.index); datafont.setFontHeightInPoints((short) 10); datafont.setFontName("Cambria"); datafont.setItalic(true); //bold font HSSFFont bolfont = wb.createFont(); bolfont.setBoldweight((short) 05); bolfont.setColor(HSSFColor.BLACK.index); bolfont.setFontHeightInPoints((short) 12); bolfont.setFontName("Cambria"); //=========================ROW STYLE=============================== HSSFCellStyle rowstyle = wb.createCellStyle(); rowstyle.setWrapText(true); //=======INNER DATA STYLING=========================== CellStyle innerdata_style = wb.createCellStyle(); innerdata_style.setFont(datafont); innerdata_style.setWrapText(true); innerdata_style.setAlignment(innerdata_style.ALIGN_CENTER); innerdata_style.setFillForegroundColor(HSSFColor.WHITE.index); innerdata_style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN); innerdata_style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle lastcellrighborder = wb.createCellStyle(); lastcellrighborder.setFont(datafont); lastcellrighborder.setWrapText(true); lastcellrighborder.setAlignment(lastcellrighborder.ALIGN_CENTER); lastcellrighborder.setFillForegroundColor(HSSFColor.WHITE.index); lastcellrighborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // lastcellrighborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); // lastcellrighborder.setBorderTop(HSSFCellStyle.BORDER_THIN); lastcellrighborder.setBorderRight(HSSFCellStyle.BORDER_THIN); // lastcellrighborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); lastcellrighborder.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle innerdata_style2 = wb.createCellStyle(); innerdata_style2.setFont(bolfont); innerdata_style2.setWrapText(true); innerdata_style2.setAlignment(innerdata_style.ALIGN_LEFT); innerdata_style2.setFillForegroundColor(HSSFColor.WHITE.index); innerdata_style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN); innerdata_style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle orangestyle = wb.createCellStyle(); orangestyle.setFont(bolfont); orangestyle.setWrapText(true); orangestyle.setAlignment(orangestyle.ALIGN_CENTER); orangestyle.setFillForegroundColor(HSSFColor.ORANGE.index); orangestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN); orangestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //Code colors CellStyle lg = wb.createCellStyle(); lg.setFont(bolfont); lg.setWrapText(true); lg.setAlignment(lg.ALIGN_CENTER); lg.setFillForegroundColor(HSSFColor.GREEN.index); lg.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); lg.setBorderBottom(HSSFCellStyle.BORDER_THIN); lg.setBorderTop(HSSFCellStyle.BORDER_THIN); lg.setBorderRight(HSSFCellStyle.BORDER_THIN); lg.setBorderLeft(HSSFCellStyle.BORDER_THIN); lg.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle Y = wb.createCellStyle(); Y.setFont(bolfont); Y.setWrapText(true); Y.setAlignment(Y.ALIGN_CENTER); Y.setFillForegroundColor(HSSFColor.YELLOW.index); Y.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); Y.setBorderBottom(HSSFCellStyle.BORDER_THIN); Y.setBorderTop(HSSFCellStyle.BORDER_THIN); Y.setBorderRight(HSSFCellStyle.BORDER_THIN); Y.setBorderLeft(HSSFCellStyle.BORDER_THIN); Y.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle R = wb.createCellStyle(); R.setFont(bolfont); R.setWrapText(true); R.setAlignment(R.ALIGN_CENTER); R.setFillForegroundColor(HSSFColor.RED.index); R.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); R.setBorderBottom(HSSFCellStyle.BORDER_THIN); R.setBorderTop(HSSFCellStyle.BORDER_THIN); R.setBorderRight(HSSFCellStyle.BORDER_THIN); R.setBorderLeft(HSSFCellStyle.BORDER_THIN); R.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //=======INNER LEFT DATA STYLING=========================== CellStyle binnerdata_style2 = wb.createCellStyle(); binnerdata_style2.setFont(datafont); binnerdata_style2.setWrapText(true); binnerdata_style2.setAlignment(binnerdata_style2.ALIGN_LEFT); binnerdata_style2.setFillForegroundColor(HSSFColor.WHITE.index); binnerdata_style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); binnerdata_style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderTop(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderRight(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //create a header //=======================Domainname styles CellStyle dnamestyle = wb.createCellStyle(); dnamestyle.setFont(bolfont); dnamestyle.setWrapText(true); dnamestyle.setAlignment(dnamestyle.ALIGN_LEFT); dnamestyle.setFillForegroundColor(HSSFColor.WHITE.index); dnamestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); dnamestyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderTop(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderRight(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); dnamestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); shet2 = wb.createSheet(countynames.get(u).toString().toUpperCase()); shet2.setColumnWidth(0, 10000); shet2.setColumnWidth(1, 5000); shet2.setColumnWidth(2, 5000); shet2.setColumnWidth(3, 5000); shet2.setColumnWidth(4, 8000); shet2.setColumnWidth(5, 8000); //create header one HSSFRow header = shet2.createRow(0); header.setHeightInPoints(30); HSSFCell cel1 = header.createCell(0); cel1.setCellValue("APHIAplus NURU YA BONDE"); cel1.setCellStyle(style); for (int b = 1; b <= 5; b++) { cel1 = header.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(style); } shet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); //create header two HSSFRow header2 = shet2.createRow(1); header2.setHeightInPoints(28); HSSFCell cel2 = null; for (int b = 1; b <= 5; b++) { cel2 = header2.createCell(b); cel2.setCellValue(""); cel2.setCellStyle(style); } cel2 = header2.createCell(0); cel2.setCellValue("OVC LIP SUPPORT SUPERVISION DASH BOARD"); cel2.setCellStyle(style); shet2.addMergedRegion(new CellRangeAddress(1, 1, 0, 5)); //cbo name //create header three HSSFRow header3 = shet2.createRow(2); HSSFCell cel3 = header3.createCell(0); cel3.setCellValue(countynames.get(u).toString()); cel3.setCellStyle(orangestyle); HSSFCell cel4 = header3.createCell(1); cel4.setCellValue(""); cel4.setCellStyle(orangestyle); //blank cells for purpose of clear worksheet only for (int x = 2; x <= 3; x++) { HSSFCell cel = header3.createCell(x); cel.setCellValue(""); cel.setCellStyle(orangestyle); } HSSFCell cel5 = header3.createCell(4); cel5.setCellValue(""); cel5.setCellStyle(orangestyle); HSSFCell cel6 = header3.createCell(5); cel6.setCellValue(""); cel6.setCellStyle(orangestyle); //create a merged region shet2.addMergedRegion(new CellRangeAddress(2, 2, 0, 5)); //create a blank row whose last cell has a border HSSFRow blankrw = shet2.createRow(3); for (int z = 0; z < 5; z++) { HSSFCell cl = blankrw.createCell(z); cl.setCellValue(""); cl.setCellStyle(innerdata_style); } HSSFCell cl = blankrw.createCell(5); cl.setCellValue(""); cl.setCellStyle(lastcellrighborder); //==========DATE OF VISIT String mywhere = " district.county_id='" + countyids.get(u).toString() + "' and ass_date between '" + startdate + "' and '" + enddate + "' "; //if the current countyid is 0, then the where code should not specify the county name if (countyids.get(u).toString().equals("1000")) { mywhere = " ass_date between '" + startdate + "' and '" + enddate + "' "; } String supervisor = ""; String dateofvisit = ""; String strengths = ""; String constraints = ""; // String loadbasicdetails="select * from backgroundinfor join staff on backgroundinfor.supervisor=staff.staff_id where "+mywhere+" "; String loadbasicdetails = "select strengths,constraints,county_name from backgroundinfor join (sites join (district join county on district.county_id=county.county_id) on sites.districtid=district.district_id) on backgroundinfor.site=sites.site_id where " + mywhere + " order by county_name"; //System.out.println("~~~~ "+loadbasicdetails); conn.rs = conn.st.executeQuery(loadbasicdetails); //if this is the overal query ArrayList countycomments = new ArrayList(); if (countyids.get(u).toString().equals("1000")) { while (conn.rs.next()) { // supervisor=conn.rs.getString("fname")+" "+conn.rs.getString("mname"); //dateofvisit=conn.rs.getString("ass_date"); if (!conn.rs.getString("strengths").equals("")) { //add the county header if it has not been added before only if (countycomments.contains(conn.rs.getString("county_name"))) { } else { countycomments.add(conn.rs.getString("county_name")); strengths += "________________________________________" + conn.rs.getString("county_name") + " County ________________________________________\n"; constraints += "________________________________________" + conn.rs.getString("county_name") + " County ________________________________________\n"; } } strengths += conn.rs.getString("strengths"); constraints += conn.rs.getString("constraints"); if (!conn.rs.getString("strengths").equals("")) { strengths += "\n"; constraints += "\n"; } } } else { while (conn.rs.next()) { // supervisor=conn.rs.getString("fname")+" "+conn.rs.getString("mname"); //dateofvisit=conn.rs.getString("ass_date"); strengths += conn.rs.getString("strengths"); constraints += conn.rs.getString("constraints"); if (!conn.rs.getString("strengths").equals("")) { strengths += "\n"; constraints += "\n"; } } } //end of while //================Create the second header================= //create header three HSSFRow header4 = shet2.createRow(4); HSSFCell cel = header4.createCell(0); cel.setCellValue(""); cel.setCellStyle(innerdata_style); HSSFCell cell = header4.createCell(1); cell.setCellValue(""); cell.setCellStyle(innerdata_style); //blank cells for purpose of clear worksheet only for (int x = 2; x <= 3; x++) { HSSFCell ceel = header4.createCell(x); ceel.setCellValue(""); ceel.setCellStyle(innerdata_style); } HSSFCell cell5 = header4.createCell(4); cell5.setCellValue(""); cell5.setCellStyle(innerdata_style); HSSFCell cell6 = header4.createCell(5); cell6.setCellValue(""); cell6.setCellStyle(lastcellrighborder); //another blank row HSSFRow blankrw2 = shet2.createRow(5); for (int z = 0; z < 5; z++) { HSSFCell cl2 = blankrw2.createCell(z); cl2.setCellValue(""); cl2.setCellStyle(innerdata_style); } HSSFCell cl2 = blankrw2.createCell(5); cl2.setCellValue(""); cl2.setCellStyle(lastcellrighborder); //create a header String theaderar[] = { "Assesment Domain", "LG", "Y", "R", "Comments/Action", "" }; HSSFRow theader = shet2.createRow(6); for (int x = 0; x < theaderar.length; x++) { HSSFCell tcel = theader.createCell(x); tcel.setCellValue(theaderar[x]); if (theaderar[x].equalsIgnoreCase("LG")) { tcel.setCellStyle(lg); } else if (theaderar[x].equalsIgnoreCase("Y")) { tcel.setCellStyle(Y); } else if (theaderar[x].equalsIgnoreCase("R")) { tcel.setCellStyle(R); } else { tcel.setCellStyle(style); } } shet2.addMergedRegion(new CellRangeAddress(6, 6, 4, 5)); //SECTION A HEADER HSSFRow seca = shet2.createRow(7); HSSFCell tcel1 = seca.createCell(0); tcel1.setCellValue("Section A: Data management and Reporting Systems"); tcel1.setCellStyle(style); for (int b = 1; b <= 5; b++) { cel1 = seca.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(style); } shet2.addMergedRegion(new CellRangeAddress(7, 7, 0, 5)); // String gettables = "SELECT domain_name,domains.domain_id as domainid,section_name,domains.section_id as secid,value as domainvalue,aggregate_sum,period,year,site FROM domains join sections on domains.section_id=sections.section_id join domain_totals on domains.domain_id=domain_totals.domainid where "+mywhere+" order by domainid"; String gettables = "SELECT domain_name,avg(value) as domainvalue,section_name ,domains.section_id as secid FROM ovc_lip.domain_totals join (sites join (district join county on district.county_id=county.county_id) on sites.districtid=district.district_id) on domain_totals.site=sites.site_id join (domains join sections on domains.section_id=sections.section_id) on domain_totals.domainid=domains.domain_id where county.county_id='" + countyids.get(u) + "' and date between '" + startdate + "' and '" + enddate + "' group by domain_totals.domainid,county_name order by domainid"; //if its the first county, themn skip the county part if (countyids.get(u).toString().equalsIgnoreCase("1000")) { gettables = "SELECT domain_name,avg(value) as domainvalue,section_name ,domains.section_id as secid FROM ovc_lip.domain_totals join (domains join sections on domains.section_id=sections.section_id) on domain_totals.domainid=domains.domain_id where date between '" + startdate + "' and '" + enddate + "' group by domain_totals.domainid order by domainid"; } System.out.println(gettables); conn.rs = conn.st.executeQuery(gettables); int rwcount = 8; HSSFRow rwx = null; HSSFCell celx = null; String sectioncopy = ""; while (conn.rs.next()) { if (sectioncopy.equals("")) { sectioncopy = conn.rs.getString("section_name"); } //if the section has changed if (!sectioncopy.equals(conn.rs.getString("section_name"))) { //create a section header //``````````````````````````````INNER SECTION HEADERS`````````````````` //``````````````````````````````INNER SECTION HEADERS`````````````````` //``````````````````````````````INNER SECTION HEADERS`````````````````` HSSFRow secb = shet2.createRow(rwcount); HSSFCell t = secb.createCell(0); t.setCellValue("Section " + conn.rs.getString("section_name")); t.setCellStyle(style); //for purpose of merging for (int b = 1; b <= 5; b++) { cel1 = secb.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(style); } //equalize copy and current value sectioncopy = conn.rs.getString("section_name"); shet2.addMergedRegion(new CellRangeAddress(rwcount, rwcount, 0, 5)); //increment rowcount to skip the current row rwcount++; } String valu[] = { conn.rs.getString("domain_name"), "", "", "", "", "" }; rwx = shet2.createRow(rwcount); for (int t = 0; t < valu.length; t++) { celx = rwx.createCell(t); celx.setCellValue("" + valu[t]); celx.setCellStyle(dnamestyle); } shet2.addMergedRegion(new CellRangeAddress(rwcount, rwcount, 4, 5)); //get the value of percentange achievement per domian //multiply by 100 //round off float domainvalue = conn.rs.getFloat("domainvalue"); domainvalue = domainvalue * 100; //BigDecimal bd=new BigDecimal(domainvalue).setScale(0,RoundingMode.HALF_EVEN); //domainval=bd.doubleValue(); domainvalue = Math.round(domainvalue); //determine the cell to print data on if (domainvalue >= 75) { celx = rwx.createCell(1); celx.setCellValue("" + domainvalue + "%"); celx.setCellStyle(lg); } else if (domainvalue >= 60 && domainvalue < 75) { celx = rwx.createCell(2); celx.setCellValue("" + domainvalue + "%"); celx.setCellStyle(Y); } else if (domainvalue < 60) { celx = rwx.createCell(3); celx.setCellValue("" + domainvalue + "%"); celx.setCellStyle(R); } rwcount++; } ///=========================end of while loop //====================STRENGTHS=========================== HSSFRow secb = shet2.createRow(rwcount); //for purpose of merging for (int b = 1; b <= 5; b++) { cel1 = secb.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(style); } HSSFCell t = secb.createCell(0); t.setCellValue("What has worked well and key areas of strengths observed"); t.setCellStyle(style); shet2.addMergedRegion(new CellRangeAddress(rwcount, rwcount, 0, 5)); rwcount++; HSSFRow str = shet2.createRow(rwcount); //str.setRowStyle(rowstyle); for (int b = 1; b <= 5; b++) { cel1 = str.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(dnamestyle); } HSSFCell t1 = str.createCell(0); t1.setCellValue("" + strengths); t1.setCellStyle(dnamestyle); shet2.addMergedRegion(new CellRangeAddress(rwcount, rwcount, 0, 5)); //for purpose of merging gen g = new gen(); int rwheight1 = g.countLines(strengths); rwheight1 = rwheight1 * 17; str.setHeightInPoints(rwheight1); rwcount++; //=======Contraints HSSFRow sec3 = shet2.createRow(rwcount); //for purpose of merging for (int b = 1; b <= 5; b++) { cel1 = sec3.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(style); } HSSFCell t2 = sec3.createCell(0); t2.setCellValue("Critical consraints affecting quality programming and data management"); t2.setCellStyle(style); shet2.addMergedRegion(new CellRangeAddress(rwcount, rwcount, 0, 5)); rwcount++; HSSFRow str2 = shet2.createRow(rwcount); str2.setRowStyle(rowstyle); for (int b = 1; b <= 5; b++) { cel1 = str2.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(dnamestyle); } HSSFCell t4 = str2.createCell(0); t4.setCellValue("" + constraints); t4.setCellStyle(dnamestyle); shet2.addMergedRegion(new CellRangeAddress(rwcount, rwcount, 0, 5)); //count the number of lines then multiply by a certain fixed unit int rwheight = g.countLines(constraints); rwheight = rwheight * 17; str2.setHeightInPoints(rwheight); rwcount++; //a line of codes String codes[] = { "LG - Meets Expectations (>=75%); ", " Y- Needs Improvement (60%- 74%);", "R - Needs Urgent Attention (<=59%);" }; HSSFRow rwl = shet2.createRow(rwcount); HSSFCell ce = rwl.createCell(0); ce.setCellValue("CODES"); ce.setCellStyle(dnamestyle); for (int b = 0; b < codes.length; b++) { ce = rwl.createCell(b + 1); ce.setCellValue("" + codes[b]); if (b == 0) { ce.setCellStyle(lg); } else if (b == 1) { ce.setCellStyle(Y); } else { ce.setCellStyle(R); } } ce = rwl.createCell(4); ce.setCellValue(""); ce.setCellStyle(dnamestyle); ce = rwl.createCell(5); ce.setCellValue(""); ce.setCellStyle(dnamestyle); shet2.addMergedRegion(new CellRangeAddress(rwcount, rwcount, 4, 5)); } //write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=OVC_LIP_COUNTY_REPORT_" + startdate + "_" + enddate + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(basicreports.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.excelstaticreports.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {/*from w ww . j av a2s. c om*/ response.setContentType("text/html;charset=UTF-8"); //a page to get Report of all the servlets String year = "2015"; String month = ""; String county = ""; String form = "kmmp"; if (request.getParameter("year") != null) { year = request.getParameter("year"); } if (request.getParameter("county") != null) { county = request.getParameter("county"); } if (request.getParameter("month") != null) { month = request.getParameter("month"); } if (request.getParameter("form") != null) { form = request.getParameter("form"); } String facilitywhere = ""; String yearwhere = ""; String monthwhere = ""; String countywhere = ""; String districtwhere = ""; String reporttype = ""; if (!year.equals("")) { yearwhere = " and Annee = '" + year + "'"; } if (!county.equals("")) { countywhere = " and countyid = '" + county + "'"; } if (!month.equals("")) { monthwhere = " and Mois = '" + month + "'"; } dbConn conn = new dbConn(); //an array to store haeder information. //the header information should appear only if a certain parameters are met //The parameters listed in here can be removed if the report type doesnt require certain parameters ArrayList Headerorgunits = new ArrayList(); Headerorgunits.add("COUNTY"); Headerorgunits.add("SUB-COUNTY"); Headerorgunits.add("FACILITY"); Headerorgunits.add("MFL CODE"); //An arralist to store a list of columns that will be selected from the database ArrayList dbcolumns = new ArrayList(); ArrayList labels = new ArrayList(); ArrayList tablename = new ArrayList(); ArrayList iscumulative = new ArrayList(); ArrayList ispercent = new ArrayList(); // ArrayList isactive=new ArrayList(); //An arralist to store a list of worksheets that will be selected from the sections ArrayList worksheets = new ArrayList(); //An arralist to store distinct worksheets. This will be derived from the the sections column ArrayList distinctsheets = new ArrayList(); String selectdistinctworksheet = "select distinct(section) from pivottable where form='" + form + "' and active='1'"; conn.rs = conn.st.executeQuery(selectdistinctworksheet); while (conn.rs.next()) { //add the name of distinct sections distinctsheets.add(conn.rs.getString(1).replace("/", "_")); } String getattribs = "select indicator,label,section,cumulative,percentage,active ,shortlabel from pivottable where form='" + form + "' order by tableid, section"; conn.rs = conn.st.executeQuery(getattribs); while (conn.rs.next()) { //add active indicators only if (conn.rs.getString("active").equals("1")) { System.out.println(conn.rs.getString("indicator") + ""); //add indicator dbcolumns.add(conn.rs.getString("indicator")); //add label if (form.equals("moh731")) { labels.add(conn.rs.getString("shortlabel") + " \n " + conn.rs.getString("label")); } else { labels.add(conn.rs.getString("label")); } //add worksheets worksheets.add(conn.rs.getString("section").replace("/", "_")); String perc = "0"; String cum = "0"; if (conn.rs.getString("cumulative") != null) { iscumulative.add(conn.rs.getString("cumulative")); } else { iscumulative.add(cum); } if (conn.rs.getString("percentage") != null) { ispercent.add(conn.rs.getString("percentage")); } else { ispercent.add(perc); } } //end of active } //end of worksheet //if String perfacilselect = "select Upper(County) as County , Upper(DistrictNom) as District , UPPER(SubPartnerNom) as facility ,CentreSanteId as mflcode , district.CountyID as countyid , "; //-------------------------------------------------------------------------------------------- // PREPARE SELECT //-------------------------------------------------------------------------------------------- //prepare selects for (int a = 0; a < dbcolumns.size(); a++) { //if the indicator is a percent, get an avaerage if (ispercent.get(a).equals("1")) { perfacilselect += " AVG(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } else if (iscumulative.get(a).equals("1")) { perfacilselect += " " + dbcolumns.get(a) + " as " + dbcolumns.get(a); } else { perfacilselect += " SUM(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } //if the item is not the last, append a comma if (a < dbcolumns.size() - 1) { perfacilselect += " ,"; } } //------------------------------------------------------------------------------------ // FROM //------------------------------------------------------------------------------------ perfacilselect += " , isValidated as Form_Validated from " + form + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on " + form + ".SubPartnerID = subpartnera.SubPartnerID "; //------------------------------------------------------------------------------------------ // WHERE //------------------------------------------------------------------------------------------ perfacilselect += " where 1=1 " + monthwhere + yearwhere; //----------------------------------------------------------------------------------------- //GROUP BY //---------------------------------------------------------------------------------------- perfacilselect += " group by subpartnera.SubPartnerID"; System.out.println(perfacilselect); //______________________________________________________________________________________ // NOW CREATE THE WORKSHEETS //______________________________________________________________________________________ XSSFWorkbook wb = new XSSFWorkbook(); //______________________________________________________________________________________ //______________________________________________________________________________________ XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); for (int b = 0; b < distinctsheets.size(); b++) { XSSFSheet shet = wb.createSheet(distinctsheets.get(b).toString().toUpperCase()); //create headers for that worksheet XSSFRow rw = shet.createRow(1); int headercellpos = 0; //create the orgunit header eg COUNTY | SUBCOUNTY | FACILITY for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(Headerorgunits.get(e).toString()); cell0.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(e, 6000); } //create the indicators header eg HV0101 | HIV 09676 | TOTAL for (int c = 0; c < dbcolumns.size(); c++) { //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(b))) { shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(labels.get(c).toString()); cell0.setCellStyle(stylex); headercellpos++; } //end of comparing if } //end of for loop //create is validated header shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue("Form Validated ?"); cell0.setCellStyle(stylex); headercellpos++; } conn.rs = conn.st.executeQuery(perfacilselect); String sectioncopy = ""; int sheetpos = 0; int rowpos = 2; while (conn.rs.next()) { //-----------------INSIDE THE DATA FORM--------------------------------- //if the section changes, change the position of the worksheet too //also, reset the position counter to begin from 2 again. XSSFSheet shet = null; // if(--!sectioncopy.equals(shet)){} for (int g = 0; g < distinctsheets.size(); g++) { shet = wb.getSheetAt(g); int colpos = 0; //the fourth cell should XSSFRow rw = shet.createRow(rowpos); for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getString(e + 1)); cell0.setCellStyle(style2); colpos++; } //_________________________________________________________________ //VALUES //_________________________________________________________________ //create the indicators values eg 90 | 45 | 356 for (int c = 0; c < dbcolumns.size(); c++) { //get the section of the current dbcolumn //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(g))) { XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getString(dbcolumns.get(c).toString())); cell0.setCellStyle(stborder); colpos++; } //end of comparing if } //end of for loop String isvalidated = "Yes"; if (conn.rs.getString("Form_Validated").equals("0")) { isvalidated = "No"; } XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(isvalidated); cell0.setCellStyle(stborder); colpos++; } rowpos++; } IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); System.out.println("" + form.toUpperCase().trim() + "_REPORT_FOR_" + year.trim() + "(" + month.trim() + ")_CREATED_" + createdOn.trim() + ".xlsx"); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=" + form.toUpperCase().trim() + "_REPORT_FOR_" + year.trim() + "(" + month.trim() + ")_CREATED_" + createdOn.trim() + ".xlsx"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(allStaticReportsMonthly.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.genderexcel.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {/* www. j a v a 2 s. co m*/ response.setContentType("text/html;charset=UTF-8"); session = request.getSession(); dbConn conn = new dbConn(); //get the existing data for the month, year and facility that is already on session String month = ""; String year = ""; String facil = ""; String form = "gender"; //===================================================================================================== year = "2015"; month = "5"; String county = ""; String header = ""; String reportType = ""; if (request.getParameter("reportType") != null) { reportType = request.getParameter("reportType"); } String reportDuration = ""; if (request.getParameter("reportDuration") != null) { reportDuration = request.getParameter("reportDuration"); } if (request.getParameter("year") != null) { year = request.getParameter("year"); } if (request.getParameter("facility") != null && reportType.equals("2")) { facil = request.getParameter("facility"); String getfacil = "select SubPartnerNom,CentreSanteId as mflcode from subpartnera where SubPartnerID='" + facil + "'"; conn.rs = conn.st.executeQuery(getfacil); while (conn.rs.next()) { header += " FACILITY : " + conn.rs.getString(1).toUpperCase() + " MFL CODE : " + conn.rs.getString(2) + " "; } } if (request.getParameter("county") != null && reportType.equals("2")) { county = request.getParameter("county"); String getcounty = "select County from county where CountyID='" + county + "'"; conn.rs = conn.st.executeQuery(getcounty); while (conn.rs.next()) { header += " COUNTY : " + conn.rs.getString(1).toUpperCase() + " "; } } if (request.getParameter("month") != null && reportDuration.equals("4")) { month = request.getParameter("month"); String getmonth = "select name as monthname from month where id='" + month + "'"; conn.rs = conn.st.executeQuery(getmonth); while (conn.rs.next()) { header += " MONTH : " + conn.rs.getString(1).toUpperCase() + " "; } } header += " YEAR : " + year + ""; String facilitywhere = ""; String yearwhere = ""; String monthwhere = ""; String countywhere = ""; String duration = ""; String semi_annual = ""; String quarter = ""; //================================================================================================== //XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX int yearcopy = Integer.parseInt(year); // reportType="2"; // year=2015; // reportDuration="3"; String yearmonth = "" + year; int prevYear = yearcopy - 1; int maxYearMonth = 0; int monthcopy = 0; // GET REPORT DURATION============================================ if (reportDuration.equals("1")) { yearmonth += "_AnnualReport"; duration = " " + form + ".yearmonth BETWEEN " + prevYear + "10 AND " + year + "09"; } else if (reportDuration.equals("2")) { semi_annual = request.getParameter("semi_annual"); // semi_annual="2"; if (semi_annual.equals("1")) { yearmonth = prevYear + "_Oct_" + year + "_Mar"; duration = " " + form + ".yearmonth BETWEEN " + prevYear + "10 AND " + year + "03"; } else { yearmonth += "_Apr_Sep"; duration = " " + form + ".yearmonth BETWEEN " + year + "04 AND " + year + "09"; } } else if (reportDuration.equals("3")) { String startMonth, endMonth; quarter = request.getParameter("quarter"); // quarter="3"; String getMonths = "SELECT months,name FROM quarter WHERE id='" + quarter + "'"; conn.rs = conn.st.executeQuery(getMonths); if (conn.rs.next() == true) { String months[] = conn.rs.getString(1).split(","); startMonth = months[0]; endMonth = months[2]; if (quarter.equals("1")) { duration = " " + form + ".yearmonth BETWEEN " + prevYear + "" + startMonth + " AND " + prevYear + "" + endMonth; yearmonth = prevYear + "_" + conn.rs.getString(2); } else { yearmonth = year + "_" + conn.rs.getString(2); duration = " " + form + ".yearmonth BETWEEN " + year + "" + startMonth + " AND " + year + "" + endMonth; } } } else if (reportDuration.equals("4")) { monthcopy = Integer.parseInt(request.getParameter("month")); // month=5; if (monthcopy >= 10) { yearmonth = prevYear + "_" + month; duration = " " + form + ".yearmonth=" + prevYear + "" + month; } else { duration = " " + form + ".yearmonth=" + year + "0" + month; yearmonth = year + "_(" + month + ")"; } } else { duration = ""; } //================================================================================================== //XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX String getexistingdata = ""; if (!county.equals("")) { countywhere = " and countyid = '" + county + "'"; } if (!facil.equals("") && reportType.equalsIgnoreCase("2")) { facilitywhere = " and " + form + ".SubPartnerID = '" + facil + "'"; } //String joinedwhwere=" where 1=1 "+yearwhere+" && "+duration; String joinedwhwere = " where 1=1 " + facilitywhere + " " + yearwhere + " && " + duration; //===================================================================================================== //______________________________________________________________________________________ // NOW CREATE THE WORKSHEETS //______________________________________________________________________________________ HSSFWorkbook wb = new HSSFWorkbook(); //______________________________________________________________________________________ //______________________________________________________________________________________ HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); HSSFSheet shet = wb.createSheet(form); //create headers for that worksheet HSSFRow rw = shet.createRow(0); rw.setHeightInPoints(25); HSSFCell cl0 = rw.createCell(0); cl0.setCellValue("Prevention Sub Area 12: Gender"); cl0.setCellStyle(stylex); for (int a = 1; a <= 5; a++) { HSSFCell clx = rw.createCell(a); clx.setCellValue(""); clx.setCellStyle(stylex); } HSSFRow rw1 = shet.createRow(1); rw1.setHeightInPoints(23); HSSFCell cl = rw1.createCell(0); cl.setCellValue(header); cl.setCellStyle(stylex); for (int a = 1; a <= 5; a++) { HSSFCell clx = rw1.createCell(a); clx.setCellValue(""); clx.setCellStyle(stylex); } HSSFRow rw2 = shet.createRow(2); rw2.setHeightInPoints(23); HSSFCell cl3 = rw2.createCell(0); cl3.setCellValue(""); cl3.setCellStyle(stylex); HSSFCell cl31 = rw2.createCell(1); cl31.setCellValue(""); cl31.setCellStyle(stylex); String head[] = { "AGE", "MALE", "FEMALE", "TOTAL" }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rw2.createCell(a + 2); clx.setCellValue(head[a]); clx.setCellStyle(stylex); } //shet.addMergedRegion(new CellRangeAddress(3,10,0,0)); shet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); shet.addMergedRegion(new CellRangeAddress(1, 1, 0, 5)); shet.addMergedRegion(new CellRangeAddress(2, 2, 1, 1)); shet.setColumnWidth(0, 2500); shet.setColumnWidth(1, 25000); shet.setColumnWidth(2, 5000); shet.setColumnWidth(3, 5000); shet.setColumnWidth(4, 5000); shet.setColumnWidth(5, 5000); getexistingdata = "select sum(P121DM0) as P121DM0, sum(P121DF0) as P121DF0, sum(P121DM10) as P121DM10, sum(P121DF10) as P121DF10, sum(P121DM15) as P121DM15, sum(P121DF15) as P121DF15, sum(P121DM20) as P121DM20, sum(P121DF20) as P121DF20, sum(P121DM25) as P121DM25, sum(P121DF25) as P121DF25, sum(P121DMT) as P121DMT, sum(P121DFT) as P121DFT, sum(P121DTT) as P121DTT, sum(P122DM0) as P122DM0, sum(P122DF0) as P122DF0, sum(P122DM15) as P122DM15, sum(P122DF15) as P122DF15, sum(P122DM25) as P122DM25, sum(P122DF25) as P122DF25, sum(P122DMT) as P122DMT, sum(P122DFT) as P122DFT, sum(P122DTT) as P122DTT, sum(P123DM0) as P123DM0, sum(P123DF0) as P123DF0, sum(P123DM15) as P123DM15, sum(P123DF15) as P123DF15, sum(P123DM25) as P123DM25, sum(P123DF25) as P123DF25, sum(P123DMT) as P123DMT, sum(P123DFT) as P123DFT, sum(P123DTT) as P123DTT, sum(P124DM0) as P124DM0, sum(P124DF0) as P124DF0, sum(P124DM15) as P124DM15, sum(P124DF15) as P124DF15, sum(P124DM25) as P124DM25, sum(P124DF25) as P124DF25, sum(P124DMT) as P124DMT, sum(P124DFT) as P124DFT, sum(P124DTT) as P124DTT, sum(GEND_GBV9M) as GEND_GBV9M, sum(GEND_GBV9F) as GEND_GBV9F, sum(GEND_GBV9) as GEND_GBV9, sum(GEND_GBV14M) as GEND_GBV14M, sum(GEND_GBV14F) as GEND_GBV14F, sum(GEND_GBV14) as GEND_GBV14, sum(GEND_GBV17M) as GEND_GBV17M, sum(GEND_GBV17F) as GEND_GBV17F, sum(GEND_GBV17) as GEND_GBV17, sum(GEND_GBV24M) as GEND_GBV24M, sum(GEND_GBV24F) as GEND_GBV24F, sum(GEND_GBV24) as GEND_GBV24, sum(GEND_GBV25M) as GEND_GBV25M, sum(GEND_GBV25F) as GEND_GBV25F, sum(GEND_GBV25) as GEND_GBV25, sum(GEND_GBVM) as GEND_GBVM, sum(GEND_GBVF) as GEND_GBVF, sum(GEND_GBV) as GEND_GBV, sum(P121D0) as P121D0, sum(P121D10) as P121D10, sum(P121D15) as P121D15, sum(P121D20) as P121D20, sum(P121D25) as P121D25, sum(P122D0) as P122D0, sum(P122D15) as P122D15, sum(P122D25) as P122D25, sum(P123D0) as P123D0, sum(P123D15) as P123D15, sum(P123D25) as P123D25, sum(P124D0) as P124D0, sum(P124D15) as P124D15, sum(P124D25) as P124D25 from " + form + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on " + form + ".SubPartnerID = subpartnera.SubPartnerID " + joinedwhwere + " "; System.out.println(getexistingdata); String P121DM0 = ""; String P121DF0 = ""; String P121DM10 = ""; String P121DF10 = ""; String P121DM15 = ""; String P121DF15 = ""; String P121DM20 = ""; String P121DF20 = ""; String P121DM25 = ""; String P121DF25 = ""; String P121DMT = ""; String P121DFT = ""; String P121DTT = ""; String P122DM0 = ""; String P122DF0 = ""; String P122DM15 = ""; String P122DF15 = ""; String P122DM25 = ""; String P122DF25 = ""; String P122DMT = ""; String P122DFT = ""; String P122DTT = ""; String P123DM0 = ""; String P123DF0 = ""; String P123DM15 = ""; String P123DF15 = ""; String P123DM25 = ""; String P123DF25 = ""; String P123DMT = ""; String P123DFT = ""; String P123DTT = ""; String P124DM0 = ""; String P124DF0 = ""; String P124DM15 = ""; String P124DF15 = ""; String P124DM25 = ""; String P124DF25 = ""; String P124DMT = ""; String P124DFT = ""; String P124DTT = ""; String GEND_GBV9M = ""; String GEND_GBV9F = ""; String GEND_GBV9 = ""; String GEND_GBV14M = ""; String GEND_GBV14F = ""; String GEND_GBV14 = ""; String GEND_GBV17M = ""; String GEND_GBV17F = ""; String GEND_GBV17 = ""; String GEND_GBV24M = ""; String GEND_GBV24F = ""; String GEND_GBV24 = ""; String GEND_GBV25M = ""; String GEND_GBV25F = ""; String GEND_GBV25 = ""; String GEND_GBVM = ""; String GEND_GBVF = ""; String GEND_GBV = ""; String P121D0 = ""; String P121D10 = ""; String P121D15 = ""; String P121D20 = ""; String P121D25 = ""; String P122D0 = ""; String P122D15 = ""; String P122D25 = ""; String P123D0 = ""; String P123D15 = ""; String P123D25 = ""; String P124D0 = ""; String P124D15 = ""; String P124D25 = ""; String distid = ""; if (session.getAttribute("subcountyid") != null) { distid = session.getAttribute("subcountyid").toString(); } int counter = 0; conn.rs = conn.st.executeQuery(getexistingdata); while (conn.rs.next()) { //now check if form was updated and if its one month after data entry //now load the column values here //====================================================================p122 P121DM0 = conn.rs.getString("P121DM0"); if (P121DM0 == null) { P121DM0 = ""; } P121DF0 = conn.rs.getString("P121DF0"); if (P121DF0 == null) { P121DF0 = ""; } P121DM10 = conn.rs.getString("P121DM10"); if (P121DM10 == null) { P121DM10 = ""; } P121DF10 = conn.rs.getString("P121DF10"); if (P121DF10 == null) { P121DF10 = ""; } P121DM15 = conn.rs.getString("P121DM15"); if (P121DM15 == null) { P121DM15 = ""; } P121DF15 = conn.rs.getString("P121DF15"); if (P121DF15 == null) { P121DF15 = ""; } P121DM20 = conn.rs.getString("P121DM20"); if (P121DM20 == null) { P121DM20 = ""; } P121DF20 = conn.rs.getString("P121DF20"); if (P121DF20 == null) { P121DF20 = ""; } P121DM25 = conn.rs.getString("P121DM25"); if (P121DM25 == null) { P121DM25 = ""; } P121DF25 = conn.rs.getString("P121DF25"); if (P121DF25 == null) { P121DF25 = ""; } P121DMT = conn.rs.getString("P121DMT"); if (P121DMT == null) { P121DMT = ""; } P121DFT = conn.rs.getString("P121DFT"); if (P121DFT == null) { P121DFT = ""; } P121DTT = conn.rs.getString("P121DTT"); if (P121DTT == null) { P121DTT = ""; } //====================================================================p122 P122DM0 = conn.rs.getString("P122DM0"); if (P122DM0 == null) { P122DM0 = ""; } P122DF0 = conn.rs.getString("P122DF0"); if (P122DF0 == null) { P122DF0 = ""; } P122DM15 = conn.rs.getString("P122DM15"); if (P122DM15 == null) { P122DM15 = ""; } P122DF15 = conn.rs.getString("P122DF15"); if (P122DF15 == null) { P122DF15 = ""; } P122DM25 = conn.rs.getString("P122DM25"); if (P122DM25 == null) { P122DM25 = ""; } P122DF25 = conn.rs.getString("P122DF25"); if (P122DF25 == null) { P122DF25 = ""; } P122DMT = conn.rs.getString("P122DMT"); if (P122DMT == null) { P122DMT = ""; } P122DFT = conn.rs.getString("P122DFT"); if (P122DFT == null) { P122DFT = ""; } P122DTT = conn.rs.getString("P122DTT"); if (P122DTT == null) { P122DTT = ""; } //====================================================================p123 P123DM0 = conn.rs.getString("P123DM0"); if (P123DM0 == null) { P123DM0 = ""; } P123DF0 = conn.rs.getString("P123DF0"); if (P123DF0 == null) { P123DF0 = ""; } P123DM15 = conn.rs.getString("P123DM15"); if (P123DM15 == null) { P123DM15 = ""; } P123DF15 = conn.rs.getString("P123DF15"); if (P123DF15 == null) { P123DF15 = ""; } P123DM25 = conn.rs.getString("P123DM25"); if (P123DM25 == null) { P123DM25 = ""; } P123DF25 = conn.rs.getString("P123DF25"); if (P123DF25 == null) { P123DF25 = ""; } P123DMT = conn.rs.getString("P123DMT"); if (P123DMT == null) { P123DMT = ""; } P123DFT = conn.rs.getString("P123DFT"); if (P123DFT == null) { P123DFT = ""; } P123DTT = conn.rs.getString("P123DTT"); if (P123DTT == null) { P123DTT = ""; } //====================================================================p124 P124DM0 = conn.rs.getString("P124DM0"); if (P124DM0 == null) { P124DM0 = ""; } P124DF0 = conn.rs.getString("P124DF0"); if (P124DF0 == null) { P124DF0 = ""; } P124DM15 = conn.rs.getString("P124DM15"); if (P124DM15 == null) { P124DM15 = ""; } P124DF15 = conn.rs.getString("P124DF15"); if (P124DF15 == null) { P124DF15 = ""; } P124DM25 = conn.rs.getString("P124DM25"); if (P124DM25 == null) { P124DM25 = ""; } P124DF25 = conn.rs.getString("P124DF25"); if (P124DF25 == null) { P124DF25 = ""; } P124DMT = conn.rs.getString("P124DMT"); if (P124DMT == null) { P124DMT = ""; } P124DFT = conn.rs.getString("P124DFT"); if (P124DFT == null) { P124DFT = ""; } P124DTT = conn.rs.getString("P124DTT"); if (P124DTT == null) { P124DTT = ""; } //=========================================================GEND_GBV GEND_GBV9M = conn.rs.getString("GEND_GBV9M"); if (GEND_GBV9M == null) { GEND_GBV9M = ""; } GEND_GBV9F = conn.rs.getString("GEND_GBV9F"); if (GEND_GBV9F == null) { GEND_GBV9F = ""; } GEND_GBV9 = conn.rs.getString("GEND_GBV9"); if (GEND_GBV9 == null) { GEND_GBV9 = ""; } //============ GEND_GBV14M = conn.rs.getString("GEND_GBV14M"); if (GEND_GBV14M == null) { GEND_GBV14M = ""; } GEND_GBV14F = conn.rs.getString("GEND_GBV14F"); if (GEND_GBV14F == null) { GEND_GBV14F = ""; } GEND_GBV14 = conn.rs.getString("GEND_GBV14"); if (GEND_GBV14 == null) { GEND_GBV14 = ""; } //======= //============ GEND_GBV17M = conn.rs.getString("GEND_GBV17M"); if (GEND_GBV17M == null) { GEND_GBV17M = ""; } GEND_GBV17F = conn.rs.getString("GEND_GBV17F"); if (GEND_GBV17F == null) { GEND_GBV17F = ""; } GEND_GBV17 = conn.rs.getString("GEND_GBV17"); if (GEND_GBV17 == null) { GEND_GBV17 = ""; } //======= //============ GEND_GBV24M = conn.rs.getString("GEND_GBV24M"); if (GEND_GBV24M == null) { GEND_GBV24M = ""; } GEND_GBV24F = conn.rs.getString("GEND_GBV24F"); if (GEND_GBV24F == null) { GEND_GBV24F = ""; } GEND_GBV24 = conn.rs.getString("GEND_GBV24"); if (GEND_GBV24 == null) { GEND_GBV24 = ""; } //======= //============ GEND_GBV25M = conn.rs.getString("GEND_GBV25M"); if (GEND_GBV25M == null) { GEND_GBV25M = ""; } GEND_GBV25F = conn.rs.getString("GEND_GBV25F"); if (GEND_GBV25F == null) { GEND_GBV25F = ""; } GEND_GBV25 = conn.rs.getString("GEND_GBV25"); if (GEND_GBV25 == null) { GEND_GBV25 = ""; } //======= //============ GEND_GBVM = conn.rs.getString("GEND_GBVM"); if (GEND_GBVM == null) { GEND_GBVM = ""; } GEND_GBVF = conn.rs.getString("GEND_GBVF"); if (GEND_GBVF == null) { GEND_GBVF = ""; } GEND_GBV = conn.rs.getString("GEND_GBV"); if (GEND_GBV == null) { GEND_GBV = ""; } //======= //added totals //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ P121D0 = conn.rs.getString("P121D0"); if (P121D0 == null) { P121D0 = ""; } P121D10 = conn.rs.getString("P121D10"); if (P121D10 == null) { P121D10 = ""; } P121D15 = conn.rs.getString("P121D15"); if (P121D15 == null) { P121D15 = ""; } P121D20 = conn.rs.getString("P121D20"); if (P121D20 == null) { P121D20 = ""; } P121D25 = conn.rs.getString("P121D25"); if (P121D25 == null) { P121D25 = ""; } P122D0 = conn.rs.getString("P122D0"); if (P122D0 == null) { P122D0 = ""; } P122D15 = conn.rs.getString("P122D15"); if (P122D15 == null) { P122D15 = ""; } P122D25 = conn.rs.getString("P122D25"); if (P122D25 == null) { P122D25 = ""; } P123D0 = conn.rs.getString("P123D0"); if (P123D0 == null) { P123D0 = ""; } P123D15 = conn.rs.getString("P123D15"); if (P123D15 == null) { P123D15 = ""; } P123D25 = conn.rs.getString("P123D25"); if (P123D25 == null) { P123D25 = ""; } P124D0 = conn.rs.getString("P124D0"); if (P124D0 == null) { P124D0 = ""; } P124D15 = conn.rs.getString("P124D15"); if (P124D15 == null) { P124D15 = ""; } P124D25 = conn.rs.getString("P124D25"); if (P124D25 == null) { P124D25 = ""; } } String createdtable = ""; if (1 == 1) { int r = 3; if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue("P12.1.D:"); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue( "GEND_NORM: Number of people completing an intervention pertaining to gender norms, that meets minimum criteria"); cl3x1.setCellStyle(style2); String head1[] = { "0-9", P121DM0, P121DF0, P121D0 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r + 5, 1, 1)); shet.addMergedRegion(new CellRangeAddress(r, r + 5, 0, 0)); r++; } //================================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "10-14", P121DM10, P121DF10, P121D10 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //================================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "15-19", P121DM15, P121DF15, P121D15 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //================================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "20-24", P121DM20, P121DF20, P121D20 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //================================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "25+", P121DM25, P121DF25, P121D25 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //================================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "Total", P121DMT, P121DFT, P121DTT }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //================================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue("P12.2.D:"); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue( "Gender Based Violence and Coercion: Number of people reached by an individual, small group or community?level intervention or service that explicitly addresses "); cl3x1.setCellStyle(style2); String head1[] = { "0-14", P122DM0, P122DF0, P122D0 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r + 3, 1, 1)); shet.addMergedRegion(new CellRangeAddress(r, r + 3, 0, 0)); r++; } //================================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "15-24", P122DM15, P122DF15, P122D15 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //=================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "25+", P122DM25, P122DF25, P122D25 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //=================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "Total", P122DMT, P122DFT, P122DTT }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //=================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue("P12.3.D:"); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue( "Women's Legal Rights and Protection Number of people reached by an individual, smallgroup, or community?level intervention or service that explicitly addresses the legal "); cl3x1.setCellStyle(style2); String head1[] = { "0-14", P123DM0, P123DF0, P123D0 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r + 3, 1, 1)); shet.addMergedRegion(new CellRangeAddress(r, r + 3, 0, 0)); r++; } //================================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "15-24", P123DM15, P123DF15, P123D15 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //=================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "25+", P123DM25, P123DF25, P123D25 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //=================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "TOTAL", P123DMT, P123DFT, P123DTT }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //=================================================================================== //=================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue("P12.4.D:"); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue( "Number of people reached by an individual, small group, or community?level intervention or service that explicitly aims to increase access to income and productive "); cl3x1.setCellStyle(style2); String head1[] = { "0-14", P124DM0, P124DF0, P124D0 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r + 3, 1, 1)); shet.addMergedRegion(new CellRangeAddress(r, r + 3, 0, 0)); r++; } //================================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "15-24", P124DM15, P124DF15, P124D15 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //=================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "25+", P124DM25, P124DF25, P124D25 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //=================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "TOTAL", P124DMT, P124DFT, P124DTT }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //=================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue("GEND GBV:"); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue("Number of people receiving post-GBV Care"); cl3x1.setCellStyle(style2); String head1[] = { "<10", GEND_GBV9M, GEND_GBV9F, GEND_GBV9 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } shet.addMergedRegion(new CellRangeAddress(r, r + 5, 1, 1)); shet.addMergedRegion(new CellRangeAddress(r, r + 5, 0, 0)); r++; } //=================================================================================== if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "10-14", GEND_GBV14M, GEND_GBV14F, GEND_GBV14 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //================================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "15-17", GEND_GBV17M, GEND_GBV17F, GEND_GBV17 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //================================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "18-24", GEND_GBV24M, GEND_GBV24F, GEND_GBV24 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //================================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "25+", GEND_GBV25M, GEND_GBV25F, GEND_GBV25 }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //================================================================================================ if (1 == 1) { HSSFRow rwx = shet.createRow(r); rwx.setHeightInPoints(23); HSSFCell cl3x = rwx.createCell(0); cl3x.setCellValue(""); cl3x.setCellStyle(style2); HSSFCell cl3x1 = rwx.createCell(1); cl3x1.setCellValue(""); cl3x1.setCellStyle(style2); String head1[] = { "Total", GEND_GBVM, GEND_GBVF, GEND_GBV }; for (int a = 0; a < head.length; a++) { HSSFCell clx = rwx.createCell(a + 2); clx.setCellValue(head1[a]); clx.setCellStyle(style2); } r++; } //================================================================================================ createdtable = header + "<br/><br/><table border='1' style='border-color: #e5e5e5;margin-bottom: 3px;font-size:10;font-family:cambria;'>" + "<tr class='form-actions'><th colspan='6'><b style='text-align:center;'> Prevention Sub Area 12:Gender</b></th></tr>"; createdtable += "<tr><td rowspan='7'><b> P12.1.D: </b></td><td rowspan='7'> GEND_NORM: Number of people completing an intervention pertaining to gender norms, that meets minimum</td><td class='form-actions'>AGE</td><td class='form-actions'>MALE</td><td style='width:80px;' class='form-actions'>FEMALE</td><td class='form-actions'>TOTAL</td></tr>"; createdtable += "<tr><td><b>0-9</b></td><td>" + P121DM0 + "</td><td>" + P121DF0 + "</td><td>" + P121D0 + "</td></tr>"; createdtable += "<tr><td><b>10-14</b></td><td>" + P121DM10 + "</td><td>" + P121DF10 + "</td><td>" + P121D10 + "</td></tr>"; createdtable += "<tr><td><b>15-19</b></td><td>" + P121DM15 + "</td><td>" + P121DF15 + "</td><td>" + P121D15 + "</td></tr>"; createdtable += "<tr><td><b>20-24</b></td><td>" + P121DM20 + "</td><td>" + P121DF20 + "</td><td>" + P121D20 + "</td></tr>"; createdtable += "<tr><td><b>25+ </b></b></td><td>" + P121DM25 + "</td><td>" + P121DF25 + "</td><td>" + P121D25 + "</td></tr>"; createdtable += "<tr><td><b>Total</b></td><td>" + P121DMT + "</td><td>" + P121DFT + "</td><td>" + P121DTT + "</td></tr>"; createdtable += "<tr><td rowspan='4'><b> P12.2.D: </b></td><td rowspan='4'>Gender Based Violence and Coercion: Number of people reached by an individual, small group or community?level intervention or service that explicitly addresses gender?based violence and coercion related to HIV/AIDS<td><b>0-14</b></td><td>" + P122DM0 + "</td><td>" + P122DF0 + "</td><td>" + P122D0 + "</td></tr>"; createdtable += "<tr><td><b>15-24</b></td><td>" + P122DM15 + "</td><td>" + P122DF15 + "</td><td>" + P122D15 + "</td></tr>"; createdtable += "<tr><td><b>25+</b></td><td>" + P122DM25 + "</td><td>" + P122DF25 + "</td><td>" + P122D25 + "</td></tr>"; createdtable += "<tr><td><b>Total</b></td><td>" + P122DMT + "</td><td>" + P122DFT + "</td><td>" + P122DTT + "</td></tr>"; createdtable += "<tr><td rowspan='4'><b> P12.3.D: </b></td><td rowspan='4'>Women's Legal Rights and Protection Number of people reached by an individual, small group, or community?level intervention or service that explicitly addresses the legal rights and protection of women and girls impacted by HIV/AIDS<td><b>0-14</b></td><td>" + P123DM0 + "</td><td>" + P123DF0 + "</td><td>" + P123D0 + "</td></tr>"; createdtable += "<tr><td><b>15-24</b></td><td>" + P123DM15 + "</td><td>" + P123DF15 + "</td><td>" + P123D15 + "</td></tr>"; createdtable += "<tr><td><b>25+</b></td><td>" + P123DM25 + "</td><td>" + P123DF25 + "</td><td>" + P123D25 + "</td></tr>"; createdtable += "<tr><td><b>Total</b></td><td>" + P123DMT + "</td><td>" + P123DFT + "</td><td>" + P123DTT + "</td></tr>"; createdtable += "<tr><td rowspan='4'><b> P12.4.D: </b></td><td rowspan='4'>Number of people reached by an individual, small group, or community?level intervention or service that explicitly aims to increase access to income and productive resources of women and girls impacted by HIV/AIDS M 0-15<td><b>0-14</b></td><td>" + P124DM0 + "</td><td>" + P124DF0 + "</td><td>" + P124D0 + "</td></tr>"; createdtable += "<tr><td><b>15-24</b></td><td>" + P124DM15 + "</td><td>" + P124DF15 + "</td><td> " + P124D15 + "</td></tr>"; createdtable += "<tr><td><b>25+</b></td><td>" + P124DM25 + "</td><td>" + P124DF25 + "</td><td>" + P124D25 + "</td></tr>"; createdtable += "<tr><td><b>Total</b></td><td>" + P124DMT + "</td><td>" + P124DFT + "</td><td>" + P124DTT + "</td></tr>"; createdtable += "<tr><td rowspan='6'><b> GEND_GBV </b></td><td rowspan='6'>Number of people receiving post-GBV Care<td><b> less than 10 </b> </td><td>" + GEND_GBV9M + "</td><td>" + GEND_GBV9F + "</td><td>" + GEND_GBV9 + "</td></tr>"; createdtable += "<tr><td><b>10-14</b></td><td>" + GEND_GBV14M + "</td><td>" + GEND_GBV14F + "</td><td>" + GEND_GBV14 + "</td></tr>"; createdtable += "<tr><td><b>15-17</b></td><td>" + GEND_GBV17M + "</td><td>" + GEND_GBV17F + "</td><td>" + GEND_GBV17 + "</td></tr>"; createdtable += "<tr><td><b>18-24</b></td><td>" + GEND_GBV24M + "</td><td>" + GEND_GBV24F + "</td><td>" + GEND_GBV24 + "</td></tr>"; createdtable += "<tr><td><b>25+</b></td><td>" + GEND_GBV25M + "</td><td>" + GEND_GBV25F + "</td><td>" + GEND_GBV25 + "</td></tr>"; createdtable += "<tr><td><b>Total</b></td><td>" + GEND_GBVM + "</td><td>" + GEND_GBVF + "</td><td>" + GEND_GBV + "</td></tr>"; createdtable += "<tr><td></td><td></td><td class='form-actions'>AGE</td><td class='form-actions'>MALE</td><td style='width:80px;' class='form-actions'>FEMALE</td><td class='form-actions'>TOTAL</td></tr>"; createdtable += "</table>"; } //System.out.println(createdtable); if (conn.conn != null) { conn.conn.close(); } if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st != null) { conn.st.close(); } if (conn.st2 != null) { conn.st2.close(); } IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=" + form + yearmonth + "_Generated_On_" + createdOn + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); outStream.close(); } catch (SQLException ex) { Logger.getLogger(Vmmcpdf.class.getName()).log(Level.SEVERE, null, ex); } }