List of usage examples for org.apache.poi.ss.usermodel CellStyle setAlignment
void setAlignment(HorizontalAlignment align);
From source file:PPMP.ppmpreport.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { response.setContentType("text/html;charset=UTF-8"); try {//from w w w . j a va 2s .c o m /* 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 = 2018; int projectstartyear = 2018; int minimumcolumns = 9;//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("Times New Roman"); font.setColor((short) 0000); font.setBoldweight(HSSFFont.COLOR_NORMAL); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_LEFT); style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style.setWrapText(true); CellStyle spstyle = wb.createCellStyle(); spstyle.setFont(font); spstyle.setBorderTop(HSSFCellStyle.BORDER_THIN); spstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); spstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); spstyle.setBorderRight(HSSFCellStyle.BORDER_THIN); spstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); spstyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); spstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); spstyle.setWrapText(true); spstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); System.out.println("Blue index:" + HSSFColor.BLUE.index); HSSFFont font2 = wb.createFont(); font2.setFontHeightInPoints((short) 12); font2.setFontName("Times New Roman"); 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); style2.setWrapText(true); 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.WHITE.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); HSSFFont fontx = wb.createFont(); fontx.setFontHeightInPoints((short) 12); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Times New Roman"); 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("Code"); headerone.add("Indicator"); 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("Code"); headertwo.add("Indicator"); 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' and inppmp='yes' order by tableNo,output "; dbConnect conn = new dbConnect(); conn.rs = conn.state.executeQuery(getindicators); int rownumber = 3; shet.setColumnWidth(1, 20000); shet.setColumnWidth(2, 2300); shet.setColumnWidth(0, 2100); shet.setColumnWidth(3, 2500); shet.setColumnWidth(4, 2500); shet.setColumnWidth(5, 2500); shet.setColumnWidth(6, 2500); shet.setColumnWidth(7, 2500); shet.setColumnWidth(8, 2500); shet.setColumnWidth(9, 2500); shet.setColumnWidth(10, 2500); shet.setColumnWidth(11, 2500); shet.setColumnWidth(12, 2500); shet.setColumnWidth(13, 2500); shet.setColumnWidth(14, 2500); 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; int rowgani = 1; while (conn.rs.next()) { System.out.println("SP__" + conn.rs.getString("output")); //merge subpartner row sp.add(conn.rs.getString("output")); 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); rwxa.setHeightInPoints(25); HSSFCell cl01 = rwxa.createCell(colpos); cl01.setCellValue(conn.rs.getString("output")); cl01.setCellStyle(spstyle); subpurposerowcopy = subpurposerow; //should merge entire row shet.addMergedRegion( new CellRangeAddress(subpurposerow, subpurposerow, 0, selectedyear - 2009)); rownumber++; } } else { subpurposerow = rownumber; //===================================================subpurpose======================== HSSFRow rwxa = shet.createRow(rownumber); rwxa.setHeightInPoints(25); HSSFCell cl01 = rwxa.createCell(colpos); cl01.setCellValue(conn.rs.getString("output")); cl01.setCellStyle(spstyle); subpurposerowcopy = subpurposerow; //should merge entire row shet.addMergedRegion( new CellRangeAddress(subpurposerow, subpurposerow, 0, selectedyear - 2009)); 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")); String tn = conn.rs.getString("tableno"); if (!tn.contains(".")) { if (isNumeric(tn)) { cl02.setCellValue(new Integer(tn)); } else { cl02.setCellValue(tn); } } else { cl02.setCellValue(tn); } 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 if (selectedyear >= 2018) { getdata = " select ROUND((sum(case when reportingPeriod='Q1' then ((men_numerator + women_numerator)) end)/sum(case when reportingPeriod='Q1' then ((men_denominator + women_denominator)) end))*100 ) as Q1, ROUND((sum(case when reportingPeriod='Q2' then ((men_numerator + women_numerator)) end)/sum(case when reportingPeriod='Q2' then ((men_denominator + women_denominator)) end))*100 ) as Q2, ROUND((sum(case when reportingPeriod='Q3' then ((men_numerator + women_numerator)) end)/sum(case when reportingPeriod='Q3' then ((men_denominator + women_denominator)) end))*100 ) as Q3, ROUND((sum(case when reportingPeriod='Q4' then ((men_numerator + women_numerator)) end)/sum(case when reportingPeriod='Q4' then ((men_denominator + women_denominator)) end))*100 ) as Q4 from indicatorachieved where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear + "' group by titleID "; System.out.println("@@" + getdata); } else { 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 "; } } else { //combined i.e male and female if (selectedyear >= 2018) { getdata = "select ROUND((SUM(case when reportingPeriod='Q1' then numerator end)/SUM(case when reportingPeriod='Q1' then denominator end))*100) as Q1 , ROUND((SUM(case when reportingPeriod='Q2' then numerator end)/SUM(case when reportingPeriod='Q2' then denominator end))*100) as Q2, ROUND((SUM(case when reportingPeriod='Q3' then numerator end)/SUM(case when reportingPeriod='Q3' then denominator end))*100) as Q3, ROUND((SUM(case when reportingPeriod='Q4' then numerator end)/SUM(case when reportingPeriod='Q4' then denominator end))*100) as Q4 from indicatorachievedcombined where titleID='" + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear + "' group by titleID "; } else { //use old way of averages 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 if (yearval >= 2018) { qry = " select ROUND((SUM(case when financialYear='" + yearval + "' then numerator end)/SUM(case when financialYear='" + yearval + "' then denominator end))*100) as y" + yearval + " from indicatorachievedcombined where financialyear='" + yearval + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } else { qry = " select ROUND(AVG(totalAchieved)) as y" + yearval + " from indicatorachievedcombined where financialyear='" + yearval + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID"; } } else { if (yearval >= 2018) { qry = " select ROUND((sum(case when financialYear='" + yearval + "' then ((men_numerator + women_numerator)) end)/sum(case when financialYear='" + yearval + "' then ((men_denominator + women_denominator)) end))*100) as y" + yearval + " from indicatorachieved 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 else { System.out.println("failed:" + conn.rs.getString("cumulative_chooser") + " *** " + conn.rs.getString("title")); } } //end of else of non percents System.out.println("@ annual query" + 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) (((double) conn.rs1.getDouble(1) * 100) / (double) annualtarget) + "%"; //System.out.println("For id "+conn.rs.getString("titleID")+" "+achievednonpercent); } } } 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 && annualtarget != 0) { 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.getRow(rowgani).autoSizeColumn(e,true); } //shet.autoSizeColumn(1,false); //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:PPMP.ppmpreport_2.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { response.setContentType("text/html;charset=UTF-8"); try {// ww w . j av a 2s. co m /* 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 w w . ja va 2 s. c o m*/ * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, 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.achievedReport.java
public String getAchievedReport(int passedYear, String passedPath, String dates) throws InvalidFormatException, IOException, SQLException { pepfaryear = passedYear;/* w ww .j a v a 2 s .c o m*/ full_date = dates; dbConn conn = new dbConn(); pos = 0; incrementor = 0; prevyear = pepfaryear - 1; String enddate = pepfaryear + "09"; String startdate = prevyear + "10"; start = Integer.parseInt(startdate); end = Integer.parseInt(enddate); System.out.println("start date : " + start + " end date : " + end); String allpath = passedPath; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb; OPCPackage pkg = OPCPackage.open(allpath); wb = new XSSFWorkbook(pkg); // HSSFWorkbook wb=new HSSFWorkbook(); XSSFSheet shet1 = wb.getSheet("sheet0"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 4000); shet1.setColumnWidth(1, 4000); shet1.setColumnWidth(2, 4000); shet1.setColumnWidth(3, 4000); shet1.setColumnWidth(4, 4000); XSSFRow rw4 = shet1.createRow(0); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); XSSFCell cell0, cell1, cell2, cell3, cell4; cell0 = rw4.createCell(0); cell1 = rw4.createCell(1); cell2 = rw4.createCell(2); cell3 = rw4.createCell(3); cell4 = rw4.createCell(4); cell0.setCellValue("PARTNER NAME"); cell1.setCellValue("AGE BRACKET"); cell2.setCellValue("GENDER"); cell3.setCellValue("MONTH"); cell4.setCellValue("ACHIEVED"); String getClients = "SELECT partner.partner_name," + "CASE " + "when personal_information.completionmonth =01 THEN '" + pepfaryear + "-01(JAN)' " + "when personal_information.completionmonth =02 THEN '" + pepfaryear + "-02 (FEB)' " + "when personal_information.completionmonth =03 THEN '" + pepfaryear + "-03 (MAR)' " + "when personal_information.completionmonth=04 THEN '" + pepfaryear + "-04 (APR)' " + "when personal_information.completionmonth=05 THEN '" + pepfaryear + "-05 (MAY)' " + "when personal_information.completionmonth=06 THEN '" + pepfaryear + "-06 (JUN)' " + "when personal_information.completionmonth=07 THEN '" + pepfaryear + "-07 (JUL)' " + "when personal_information.completionmonth=08 THEN '" + pepfaryear + "-08 (AUG)' " + "when personal_information.completionmonth=09 THEN '" + pepfaryear + "-09 (SEPT)' " + "when personal_information.completionmonth=10 THEN '" + prevyear + "-10 (OCT)' " + "when personal_information.completionmonth=11 THEN '" + prevyear + "-11 (NOV)'" + "when personal_information.completionmonth=12 THEN '" + prevyear + "-12 (DEC)'" + "END AS MONTHS,personal_information.completionyear," + "CASE" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above'" + " ELSE 'NO DATE OF BIRTH'" + " END AS AGEBRACKET," + "CASE " + "when personal_information.gender LIKE 'Female' THEN 'F' " + "when personal_information.gender LIKE 'Male' THEN 'M' " + "ELSE 'NO SEX' " + "END AS SEX" + ",completionmonth,COUNT(personal_information.client_id) FROM personal_information " + "JOIN partner ON personal_information.partner_id=partner.partner_id " + " WHERE personal_information.completionmonth>0 && personal_information.completionyear>0 GROUP BY partner.partner_name,SEX,personal_information.completionyear,MONTHS,AGEBRACKET ORDER BY personal_information.partner_id"; conn.rs = conn.st.executeQuery(getClients); while (conn.rs.next()) { partnername = clientid = gender = ""; age = 0; partnername = conn.rs.getString(1); month = conn.rs.getString(2); year = conn.rs.getInt(3); agebracket = conn.rs.getString(4); gender = conn.rs.getString(5); String dkey = year + "" + conn.rs.getString(6); datekey = Integer.parseInt(dkey); achieved = conn.rs.getInt(7); incrementor += achieved; System.out.println( "date key : " + datekey + "startdate : " + start + " end date : " + end + " year : " + year); if (datekey >= start && datekey <= end && year >= 2014) { pos++; XSSFRow rw4x = shet1.createRow(pos); rw4x.setHeightInPoints(25); rw4x.setRowStyle(style2); XSSFCell cell0x, cell1x, cell2x, cell3x, cell4x; cell0x = rw4x.createCell(0); cell1x = rw4x.createCell(1); cell2x = rw4x.createCell(2); cell3x = rw4x.createCell(3); cell4x = rw4x.createCell(4); // OUTPUT SERVICES PROVIDED================================ cell0x.setCellValue(partnername); cell1x.setCellValue(agebracket); cell2x.setCellValue(gender); cell3x.setCellValue(month); cell4x.setCellValue(achieved); System.out.println(pos + "-----partner :" + partnername + " age bracket :" + agebracket + " gender :" + gender + " completion month : " + month); } } for (int i = 0; i < myalphabet.length; i++) { try { System.out.println("at position : " + myalphabet[i]); String current_drive = myalphabet[i]; File f3 = new File(current_drive + ":\\APHIAPLUS\\PWPDBCONNECTION"); // CREATE A DIRECTORY AND THE FILE TO HOLD DATA if (f3.exists() && f3.isDirectory()) { path = current_drive + ":\\APHIAPLUS\\PWPDBCONNECTION\\DATA\\REPORTS"; new File(path).mkdirs(); filePath = path + "\\PWP_ATTACHED_REPORT" + full_date + ".xlsm"; } //select the last timestamp which a backup was picked from..... } finally { } } FileOutputStream fileOut = new FileOutputStream(filePath); wb.write(fileOut); if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } //if(incrementor==0){ // filePath="noreport"; //} //else{ //// url="no url to the report"; //} return filePath; }
From source file:reports.allformsreportstracker.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { String form = "kmmp"; String tablename = ""; if (request.getParameter("form") != null) { form = request.getParameter("form"); }//from ww w. j a v a2 s . com //get the table name from the form name tablename = form.toLowerCase(); if (form.equalsIgnoreCase("MOH 711A")) { tablename = "moh711"; } //for now, redirect Hei to 711. this will be corrected later if (form.equalsIgnoreCase("MOH 711 (New)")) { tablename = "moh711_new"; } else if (tablename.equals("hei")) { tablename = "moh711"; } String kmmpor = "subpartnera.KMMP=1"; String vmmcor = "subpartnera.VMMC=1"; String genderor = "subpartnera.Gender=1"; String nutritionor = "subpartnera.Gender=1"; String tbor = "subpartnera.TB=1"; String form711or = "subpartnera.HTC=1 OR subpartnera.PMTCT OR subpartnera.FP OR subpartnera.Maternity"; //Maureen to add more conditions in 711 or String orquery = kmmpor; if (form.equalsIgnoreCase("KMMP")) { orquery = kmmpor; } else if (form.equalsIgnoreCase("VMMC")) { orquery = vmmcor; } else if (form.equalsIgnoreCase("Gender")) { orquery = genderor; } else if (form.equalsIgnoreCase("Nutrition")) { orquery = nutritionor; } else if (form.equalsIgnoreCase("MOH 711A")) { orquery = form711or; } else if (form.equalsIgnoreCase("TB")) { orquery = tbor; } else { orquery = " subpartnera.PMTCT=1 OR subpartnera.ART=1 OR subpartnera.PEP=1 "; } //731 dbConn conn = new dbConn(); session = request.getSession(); year = Integer.parseInt(request.getParameter("year")); // year=2015; prevYear = year - 1; IdGenerator IG = new IdGenerator(); allMonths.clear(); allReports.clear(); duration = "WHERE (" + tablename + ".yearmonth BETWEEN " + prevYear + "" + 10 + " AND " + year + "09) AND ( " + orquery + " )"; currentMonth = IG.CurrentMonth(); monthsData = ""; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet(form + " REPORTS TRACKER"); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); stborder.setWrapText(true); HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); HSSFCellStyle styleHeader = wb.createCellStyle(); styleHeader.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); styleHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontHeader = wb.createFont(); fontHeader.setColor(HSSFColor.DARK_BLUE.index); styleHeader.setFont(fontx); styleHeader.setWrapText(true); for (int i = 0; i <= 2; i++) { shet1.setColumnWidth(i, 8000); } HSSFRow rw1S1 = shet1.createRow(0); HSSFCell S1cell = rw1S1.createCell(0); S1cell.setCellValue("COUNTY NAME"); S1cell.setCellStyle(stylex); HSSFCell S1cellX = rw1S1.createCell(1); S1cellX.setCellValue("SUB COUNTY"); S1cellX.setCellStyle(stylex); S1cellX = rw1S1.createCell(2); S1cellX.setCellValue("HEALTH FACILITY"); S1cellX.setCellStyle(stylex); S1cellX = rw1S1.createCell(3); S1cellX.setCellValue("MFL CODE"); S1cellX.setCellStyle(stylex); S1cellX = rw1S1.createCell(4); S1cellX.setCellValue("EXPECTED REPORTS"); S1cellX.setCellStyle(stylex); counterHeader = 5; String getMaxandMin = "SELECT DISTINCT(month.name),month.id FROM " + tablename + " JOIN month ON " + tablename + ".Mois=month.id JOIN subpartnera ON " + tablename + ".SubPartnerID=subpartnera.SubPartnerID " + duration + " " + " ORDER BY " + tablename + ".yearmonth"; conn.rs = conn.st.executeQuery(getMaxandMin); while (conn.rs.next()) { monthName = conn.rs.getString(1); monthid = conn.rs.getInt(2); if (monthid <= 9) { currentYear = year; } else { currentYear = prevYear; } System.out.println(" Months are : " + monthName); allMonths.add(monthName); allReports.add(0); S1cellX = rw1S1.createCell(counterHeader); S1cellX.setCellValue(monthName); S1cellX.setCellStyle(stylex); counterHeader++; } prevFacility = currentFacility = ""; currentDistrict = prevDistrict = ""; currentCounty = prevCounty = ""; counter = districtCounter = countyCounter = districtsMerged = 0; arraySize = allReports.size(); if (allMonths.size() > 0) { String checkReports = "SELECT county.County,district.DistrictNom,subpartnera.SubPartnerNom," + "subpartnera.CentreSanteId,COUNT(" + tablename + ".SubPartnerID),month.name,subpartnera.SubPartnerID " + "FROM subpartnera " + "LEFT JOIN " + tablename + " ON " + tablename + ".SubPartnerID=subpartnera.SubPartnerID " + "LEFT JOIN district ON subpartnera.DistrictID=district.DistrictID " + "LEFT JOIN county ON county.CountyID=district.CountyID " + "LEFT JOIN month ON " + tablename + ".Mois=month.id " + " " + duration + " " + " GROUP BY subpartnera.SubPartnerNom," + tablename + ".Annee," + tablename + ".Mois " + "ORDER BY county.County,district.DistrictNom,subpartnera.SubPartnerNom," + tablename + ".Mois"; System.out.println(checkReports); conn.rs = conn.st.executeQuery(checkReports); while (conn.rs.next()) { countyName = conn.rs.getString(1); districtName = conn.rs.getString(2); facilityName = conn.rs.getString(3); mflcode = conn.rs.getString(4); status = conn.rs.getInt(5); selectedMonth = conn.rs.getString(6); currentFacility = conn.rs.getString(7); currentDistrict = districtName; currentCounty = countyName; // CHECK WHERE TO PLACE THE NUMBER; monthPosition = allMonths.indexOf(selectedMonth); if (!prevFacility.equals(currentFacility)) { if (!prevDistrict.equals(currentDistrict) && !prevDistrict.equals("")) { counter++; noReports = districtCounter + 1; HSSFRow rwTotal = shet1.createRow(counter); HSSFCell SX = rwTotal.createCell(0); SX.setCellStyle(stborder); SX = rwTotal.createCell(1); SX.setCellValue(prevDistrict + " TOTALS : "); SX.setCellStyle(styleHeader); SX = rwTotal.createCell(2); SX.setCellStyle(styleHeader); SX = rwTotal.createCell(3); SX.setCellStyle(styleHeader); SX = rwTotal.createCell(4); SX.setCellValue(noReports); SX.setCellStyle(styleHeader); shet1.addMergedRegion(new CellRangeAddress(counter, counter, 1, 3)); for (int j = 0; j < allReports.size(); j++) { // System.out.println("district name : "+prevDistrict+" no of reports : "+allReports.get(j).toString()+" for month : "+allMonths.get(j).toString()); int dataPos = 5 + j; SX = rwTotal.createCell(dataPos); SX.setCellValue(Integer.parseInt(allReports.get(j).toString())); SX.setCellStyle(styleHeader); } for (int k = 0; k < arraySize; k++) { allReports.set(k, 0); } } counter++; HSSFRow rw1 = shet1.createRow(counter); HSSFCell S1 = rw1.createCell(0); S1.setCellValue(countyName); S1.setCellStyle(stborder); S1 = rw1.createCell(1); S1.setCellValue(districtName); S1.setCellStyle(stborder); S1 = rw1.createCell(2); S1.setCellValue(facilityName); S1.setCellStyle(stborder); S1 = rw1.createCell(3); S1.setCellValue(mflcode); S1.setCellStyle(stborder); S1 = rw1.createCell(4); S1.setCellValue(1); S1.setCellStyle(stborder); for (int j = 0; j < allMonths.size(); j++) { int cellPos = j + 5; S1 = rw1.createCell(cellPos); // System.out.println("counter : "+counter+" datapos : "+cellPos+" status : "+status); S1.setCellStyle(stborder); } int dataPos = 5 + monthPosition; S1 = rw1.getCell(dataPos); S1.setCellValue(status); if (!prevDistrict.equals(currentDistrict) && !prevDistrict.equals("")) { int distStart = counter - districtCounter - 2; int distEnd = counter - 2; shet1.addMergedRegion(new CellRangeAddress(distStart, distEnd, 1, 1)); districtsMerged++; districtCounter = 0; for (int j = 0; j < arraySize; j++) { allReports.set(j, 0); } } else { if (counter == 1) { } else { districtCounter++; } } if (!prevCounty.equals(currentCounty) && !prevCounty.equals("")) { int countyStart = counter - countyCounter - districtsMerged - 1; int countyEnd = counter - 1; shet1.addMergedRegion(new CellRangeAddress(countyStart, countyEnd, 0, 0)); countyCounter = 0; districtsMerged = 0; } else { if (counter == 1) { } else { countyCounter++; } } prevCounty = currentCounty; prevDistrict = currentDistrict; } else { HSSFRow rw1 = shet1.getRow(counter); int dataPos = 5 + monthPosition; HSSFCell S1 = rw1.getCell(dataPos); S1.setCellValue(status); // } if (status == 1) { int currentData = Integer.parseInt(allReports.get(monthPosition).toString()) + 1; allReports.set(monthPosition, currentData); } prevFacility = currentFacility; } // MATCH THE LAST DISTRICTS counter++; noReports = districtCounter + 1; HSSFRow rwTotal = shet1.createRow(counter); HSSFCell SX = rwTotal.createCell(0); SX.setCellStyle(stborder); SX = rwTotal.createCell(1); SX.setCellValue(prevDistrict + " TOTALS : "); SX.setCellStyle(styleHeader); SX = rwTotal.createCell(2); SX.setCellStyle(styleHeader); SX = rwTotal.createCell(3); SX.setCellStyle(styleHeader); SX = rwTotal.createCell(4); SX.setCellValue(noReports); SX.setCellStyle(styleHeader); shet1.addMergedRegion(new CellRangeAddress(counter, counter, 1, 3)); int distStart = counter - districtCounter - 1; int distEnd = counter - 1; // System.out.println("MERGE BETWEEN : START : "+distStart+" END : "+distEnd); shet1.addMergedRegion(new CellRangeAddress(distStart, distEnd, 1, 1)); districtCounter = 0; int countyStart = counter - countyCounter - 2; int countyEnd = counter; // System.out.println("MERGE BETWEEN : START : "+distStart+" END : "+distEnd); shet1.addMergedRegion(new CellRangeAddress(countyStart, countyEnd, 0, 0)); countyCounter = 0; for (int j = 0; j < allReports.size(); j++) { // System.out.println("district name : "+prevDistrict+" no of reports : "+allReports.get(j).toString()+" for month : "+allMonths.get(j).toString()); int dataPos = 5 + j; SX = rwTotal.createCell(dataPos); SX.setCellValue(Integer.parseInt(allReports.get(j).toString())); SX.setCellStyle(styleHeader); } if (conn.st != null) { conn.st.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.conn != null) { conn.conn.close(); } createdOn = IG.CreatedOn(); // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=" + tablename + "_ReportsTracker_YEAR(" + year + ")_Generated_on_" + createdOn.trim() + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } else { session.setAttribute("noTrackerReport", "<font color=\"red\"><b>SORRY:</b> No report was found for " + year + ".</red>"); response.sendRedirect("reportsTracker.jsp"); } }
From source file:reports.allRawData.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();/*from www . j a v a 2 s . c o m*/ dbConn conn = new dbConn(); reportType = request.getParameter("partnerAll"); partner_ids = ""; if (reportType.equals("all_partners")) { String getPartnerIDs = "SELECT * FROM partner"; conn.rs = conn.st.executeQuery(getPartnerIDs); while (conn.rs.next() == true) { partner_ids += conn.rs.getString(1) + ","; } } if (reportType.equals("selected_partners")) { String[] ids = request.getParameterValues("partner"); for (String partid : ids) { if (!partid.equals("") && !partid.equals(",")) { partner_ids += partid + ","; } } } System.out.println(" partner _ids are : " + partner_ids); i = 4; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet(); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); shet1.setColumnWidth(0, 5000); shet1.setColumnWidth(1, 5000); shet1.setColumnWidth(2, 5000); shet1.setColumnWidth(3, 5500); shet1.setColumnWidth(4, 7000); shet1.setColumnWidth(5, 5300); shet1.setColumnWidth(6, 3000); shet1.setColumnWidth(7, 3200); shet1.setColumnWidth(8, 3200); shet1.setColumnWidth(9, 3200); shet1.setColumnWidth(10, 3800); shet1.setColumnWidth(11, 3000); shet1.setColumnWidth(12, 5300); shet1.setColumnWidth(13, 5000); shet1.setColumnWidth(14, 5300); shet1.setColumnWidth(15, 5000); shet1.setColumnWidth(16, 5200); shet1.setColumnWidth(17, 5200); shet1.setColumnWidth(18, 5200); shet1.setColumnWidth(19, 5800); shet1.setColumnWidth(20, 5000); shet1.setColumnWidth(21, 5300); shet1.setColumnWidth(22, 5300); shet1.setColumnWidth(23, 5000); shet1.setColumnWidth(24, 5200); shet1.setColumnWidth(25, 5200); shet1.setColumnWidth(26, 5200); shet1.setColumnWidth(27, 5800); shet1.setColumnWidth(28, 5000); shet1.setColumnWidth(29, 5300); shet1.setColumnWidth(30, 5800); shet1.setColumnWidth(31, 5000); shet1.setColumnWidth(32, 5300); // shet1.setColumnWidth(20, 2000); HSSFCellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFRow rw1 = shet1.createRow(1); HSSFCell cell; HSSFRow rw4 = shet1.createRow(0); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell0, cell1, cell2, cell3, cell4, cell5, cell6, cell7, cell8, cell9, cell10, cell11, cell12, cell13, cell14, cell15, cell16, cell17, cell18, cell19, cell20; HSSFCell cell21, cell22, cell23, cell24, cell25, cell26, cell27, cell28, cell29, cell30, cell31, cell32, cell33; cell0 = rw4.createCell(0); cell1 = rw4.createCell(1); cell2 = rw4.createCell(2); cell3 = rw4.createCell(3); cell4 = rw4.createCell(4); cell5 = rw4.createCell(5); cell6 = rw4.createCell(6); cell7 = rw4.createCell(7); cell8 = rw4.createCell(8); cell9 = rw4.createCell(9); cell10 = rw4.createCell(10); cell11 = rw4.createCell(11); cell12 = rw4.createCell(12); cell13 = rw4.createCell(13); cell14 = rw4.createCell(14); cell15 = rw4.createCell(15); cell16 = rw4.createCell(16); cell17 = rw4.createCell(17); cell18 = rw4.createCell(18); cell19 = rw4.createCell(19); cell20 = rw4.createCell(20); cell21 = rw4.createCell(21); cell22 = rw4.createCell(22); cell23 = rw4.createCell(23); cell24 = rw4.createCell(24); cell25 = rw4.createCell(25); cell26 = rw4.createCell(26); cell27 = rw4.createCell(27); cell28 = rw4.createCell(28); cell29 = rw4.createCell(29); cell30 = rw4.createCell(30); cell31 = rw4.createCell(31); cell32 = rw4.createCell(32); cell33 = rw4.createCell(33); cell0.setCellValue("COUNTY NAME"); cell1.setCellValue("PARTNER NAME"); cell2.setCellValue("NEAREST FACILITY"); cell3.setCellValue("GROUP NAME"); cell4.setCellValue("SERVICE PROVIDER"); cell5.setCellValue("FULL NAME"); cell6.setCellValue("AGE"); cell7.setCellValue("GENDER"); cell8.setCellValue("DATE OF BIRTH"); cell9.setCellValue("NATIONAL ID"); cell10.setCellValue("MOBILE NO"); cell11.setCellValue("CCC NO"); cell12.setCellValue("Messages Attended"); cell13.setCellValue("Knowledge of HIV Status"); cell14.setCellValue("Partner HIV Testing"); cell15.setCellValue("Child HIV Testing"); cell16.setCellValue("Discordance"); cell17.setCellValue("HIV Disclosure"); cell18.setCellValue("Risk Factor/Reduction"); cell19.setCellValue("Condom Use"); cell20.setCellValue("Alcohol and Substance Abuse"); cell21.setCellValue("Adherence"); cell22.setCellValue("STIs"); cell23.setCellValue("Family Planning"); cell24.setCellValue("PMTCT"); cell25.setCellValue("TB"); cell26.setCellValue("Received Contraceptives"); cell27.setCellValue("Reffered To Service Point"); cell28.setCellValue("Given Condoms"); cell29.setCellValue("Screened For TB"); cell30.setCellValue("Screened For STIs"); cell31.setCellValue("Partner Tested"); cell32.setCellValue("Children Tested"); cell33.setCellValue("Disclosed Status"); HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); cell0.setCellStyle(stylex); cell1.setCellStyle(stylex); cell2.setCellStyle(stylex); cell3.setCellStyle(stylex); cell4.setCellStyle(stylex); cell5.setCellStyle(stylex); cell6.setCellStyle(stylex); cell7.setCellStyle(stylex); cell8.setCellStyle(stylex); cell9.setCellStyle(stylex); cell10.setCellStyle(stylex); cell11.setCellStyle(stylex); cell12.setCellStyle(stylex); cell13.setCellStyle(stylex); cell14.setCellStyle(stylex); cell15.setCellStyle(stylex); cell16.setCellStyle(stylex); cell17.setCellStyle(stylex); cell18.setCellStyle(stylex); cell19.setCellStyle(stylex); cell20.setCellStyle(stylex); cell21.setCellStyle(stylex); cell22.setCellStyle(stylex); cell23.setCellStyle(stylex); cell24.setCellStyle(stylex); cell25.setCellStyle(stylex); cell26.setCellStyle(stylex); cell27.setCellStyle(stylex); cell28.setCellStyle(stylex); cell29.setCellStyle(stylex); cell30.setCellStyle(stylex); cell31.setCellStyle(stylex); cell32.setCellStyle(stylex); cell33.setCellStyle(stylex); i = 1; String[] partIDS = partner_ids.split(","); for (String partner_id : partIDS) { if (!partner_id.equals("") && !partner_id.equals(",")) { partnerid = partner_id; System.out.println("partner id is : " + partnerid); String getClients = "SELECT client_id,fname,mname,lname," + "DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( dob, 'YYYY-%mm-%dd' ) )" + ",gender,group_id,district_id,partner_id,provider_id,lessons_attended,national_id,ccc_no,mobile_no,dob,hf_id FROM personal_information" + " WHERE partner_id='" + partnerid + "' ORDER BY partner_id,district_id,group_id,fname,mname,lname"; conn.rs = conn.st.executeQuery(getClients); while (conn.rs.next()) { county = district = hf = partner = groupname = serviceprovider = clientname = age = gender = groupings = year = providerid = ""; countyid = districtid = hfid = partnerid = groupid = serviceproviderid = clientid = ""; s1 = s2 = s3 = s4 = s5 = s6 = s7 = s8 = s9 = s10 = s11 = s12 = s13 = ""; cm = rsp = cd = tb = sti = testedpartner = testedchild = session_no = value = status; sess = val = cds = 0; hf_id = lessons_attended = national_id = ccc_no = mobile_no = dob = ""; HSSFRow rw4x = shet1.createRow(i); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell0x, cell1x, cell2x, cell3x, cell4x, cell5x, cell6x, cell7x, cell8x, cell9x, cell10x, cell11x, cell12x, cell13x, cell14x, cell15x, cell16x, cell17x, cell18x, cell19x, cell20x; HSSFCell cell21x, cell22x, cell23x, cell24x, cell25x, cell26x, cell27x, cell28x, cell29x, cell30x, cell31x, cell32x, cell33x; cell0x = rw4x.createCell(0); cell1x = rw4x.createCell(1); cell2x = rw4x.createCell(2); cell3x = rw4x.createCell(3); cell4x = rw4x.createCell(4); cell5x = rw4x.createCell(5); cell6x = rw4x.createCell(6); cell7x = rw4x.createCell(7); cell8x = rw4x.createCell(8); cell9x = rw4x.createCell(9); cell10x = rw4x.createCell(10); cell11x = rw4x.createCell(11); cell12x = rw4x.createCell(12); cell13x = rw4x.createCell(13); cell14x = rw4x.createCell(14); cell15x = rw4x.createCell(15); cell16x = rw4x.createCell(16); cell17x = rw4x.createCell(17); cell18x = rw4x.createCell(18); cell19x = rw4x.createCell(19); cell20x = rw4x.createCell(20); cell21x = rw4x.createCell(21); cell22x = rw4x.createCell(22); cell23x = rw4x.createCell(23); cell24x = rw4x.createCell(24); cell25x = rw4x.createCell(25); cell26x = rw4x.createCell(26); cell27x = rw4x.createCell(27); cell28x = rw4x.createCell(28); cell29x = rw4x.createCell(29); cell30x = rw4x.createCell(30); cell31x = rw4x.createCell(31); cell32x = rw4x.createCell(32); cell33x = rw4x.createCell(33); clientid = conn.rs.getString(1); clientname = conn.rs.getString(2) + " " + conn.rs.getString(3) + " " + conn.rs.getString(4); age = conn.rs.getString(5); gender = conn.rs.getString(6); groupid = conn.rs.getString(7); districtid = conn.rs.getString(8); partnerid = conn.rs.getString(9); providerid = conn.rs.getString(10); lessons_attended = conn.rs.getString(11); national_id = conn.rs.getString(12); ccc_no = conn.rs.getString(13); mobile_no = conn.rs.getString(14); dob = conn.rs.getString(15); hfid = conn.rs.getString(16); if (conn.rs.getString(3).equals(conn.rs.getString(4))) { clientname = conn.rs.getString(2) + " " + conn.rs.getString(4); } String getServiceProvider = "SELECT fname,mname,lname FROM service_provider WHERE provider_id='" + providerid + "'"; conn.rs1 = conn.st1.executeQuery(getServiceProvider); if (conn.rs1.next() == true) { serviceprovider = conn.rs1.getString(1) + " " + conn.rs1.getString(2) + " " + conn.rs1.getString(3); if (conn.rs1.getString(2).equals(conn.rs1.getString(3))) { serviceprovider = conn.rs1.getString(1) + " " + conn.rs1.getString(3); } } String getCnt = "SELECT district.district_name,county.county_name FROM district JOIN county ON district.county_id=county.county_id WHERE district.district_id='" + districtid + "'"; conn.rs1 = conn.st1.executeQuery(getCnt); if (conn.rs1.next() == true) { district = conn.rs1.getString(1); county = conn.rs1.getString(2); } String getPart = "SELECT partner_name FROM partner WHERE partner_id='" + partnerid + "'"; conn.rs1 = conn.st1.executeQuery(getPart); if (conn.rs1.next() == true) { partner = conn.rs1.getString(1); } String getgrp1 = "SELECT health_facility.hf_name FROM health_facility WHERE health_facility.hf_id='" + hfid + "' LIMIT 1"; conn.rs1 = conn.st1.executeQuery(getgrp1); if (conn.rs1.next() == true) { hf = conn.rs1.getString(1); } if (!groupid.equals("0")) { String getgrp = "SELECT groups.group_name FROM groups WHERE groups.group_id='" + groupid + "'"; conn.rs1 = conn.st1.executeQuery(getgrp); if (conn.rs1.next() == true) { groupname = conn.rs1.getString(1); } } if (groupid.equals("0")) { groupname = "INDIVIDUAL"; } String getAttended = "SELECT session_no,value FROM register2 WHERE client_id='" + clientid + "'"; conn.rs1 = conn.st1.executeQuery(getAttended); while (conn.rs1.next()) { sess = conn.rs1.getInt(1); val = conn.rs1.getInt(2); if (sess == 1) { if (val == 1) { s1 = "1"; } else if (val == 2) { s1 = "0"; } else { s1 = ""; } } if (sess == 2) { if (val == 1) { s2 = "1"; } else if (val == 2) { s2 = "0"; } else { s2 = ""; } } if (sess == 3) { if (val == 1) { s3 = "1"; } else if (val == 2) { s3 = "0"; } else { s3 = ""; } } if (sess == 4) { if (val == 1) { s4 = "1"; } else if (val == 2) { s4 = "0"; } else { s4 = ""; } } if (sess == 5) { if (val == 1) { s5 = "1"; } else if (val == 2) { s5 = "0"; } else { s5 = ""; } } if (sess == 6) { if (val == 1) { s6 = "1"; } else if (val == 2) { s6 = "0"; } else { s6 = ""; } } if (sess == 7) { if (val == 1) { s7 = "1"; } else if (val == 2) { s7 = "0"; } else { s7 = ""; } } if (sess == 8) { if (val == 1) { s8 = "1"; } else if (val == 2) { s8 = "0"; } else { s8 = ""; } } if (sess == 9) { if (val == 1) { s9 = "1"; } else if (val == 2) { s9 = "0"; } else { s9 = ""; } } if (sess == 10) { if (val == 1) { s10 = "1"; } else if (val == 2) { s10 = "0"; } else { s10 = ""; } } if (sess == 11) { if (val == 1) { s11 = "1"; } else if (val == 2) { s11 = "0"; } else { s11 = ""; } } if (sess == 12) { if (val == 1) { s12 = "1"; } else if (val == 2) { s12 = "0"; } else { s12 = ""; } } if (sess == 13) { if (val == 1) { s13 = "1"; } else if (val == 2) { s13 = "0"; } else { s13 = ""; } } } // OUTPUT ATTENDED-------------------------------- cm = rsp = tb = sti = testedpartner = testedchild = session_no = value = status = "NO"; cds = 0; String getServices = "SELECT contraceptive_method,rsp,cds_given,screened_tb,screened_stis,tested_partner,tested_children,disclosed_status FROM services_provided WHERE client_id='" + clientid + "'"; conn.rs1 = conn.st1.executeQuery(getServices); while (conn.rs1.next()) { cds += conn.rs1.getInt(3); if (conn.rs1.getString(1).equals("YES")) { cm = conn.rs1.getString(1); } if (conn.rs1.getString(2).equals("YES")) { rsp = conn.rs1.getString(2); } if (conn.rs1.getString(4).equals("YES")) { tb = conn.rs1.getString(4); } if (conn.rs1.getString(5).equals("YES")) { sti = conn.rs1.getString(5); } if (conn.rs1.getString(6).equals("YES")) { testedpartner = conn.rs1.getString(6); } if (conn.rs1.getString(7).equals("YES")) { testedchild = conn.rs1.getString(7); } if (conn.rs1.getString(8).equals("YES")) { status = conn.rs1.getString(8); } } // OUTPUT SERVICES PROVIDED================================ cell0x.setCellValue(county); cell1x.setCellValue(partner); cell2x.setCellValue(hf); cell3x.setCellValue(groupname); cell4x.setCellValue(serviceprovider); cell5x.setCellValue(clientname); cell6x.setCellValue(age); cell7x.setCellValue(gender); cell8x.setCellValue(dob); cell9x.setCellValue(national_id); cell10x.setCellValue(mobile_no); cell11x.setCellValue(ccc_no); cell12x.setCellValue(lessons_attended); cell13x.setCellValue(s1); cell14x.setCellValue(s2); cell15x.setCellValue(s3); cell16x.setCellValue(s4); cell17x.setCellValue(s5); cell18x.setCellValue(s6); cell19x.setCellValue(s7); cell20x.setCellValue(s8); cell21x.setCellValue(s9); cell22x.setCellValue(s10); cell23x.setCellValue(s11); cell24x.setCellValue(s12); cell25x.setCellValue(s13); cell26x.setCellValue(cm); cell27x.setCellValue(rsp); cell28x.setCellValue(cds); cell29x.setCellValue(tb); cell30x.setCellValue(sti); cell31x.setCellValue(testedpartner); cell32x.setCellValue(testedchild); cell33x.setCellValue(status); cell0x.setCellStyle(styleBorder); cell1x.setCellStyle(styleBorder); cell2x.setCellStyle(styleBorder); cell3x.setCellStyle(styleBorder); cell4x.setCellStyle(styleBorder); cell5x.setCellStyle(styleBorder); cell6x.setCellStyle(styleBorder); cell7x.setCellStyle(styleBorder); cell8x.setCellStyle(styleBorder); cell9x.setCellStyle(styleBorder); cell10x.setCellStyle(styleBorder); cell11x.setCellStyle(styleBorder); cell12x.setCellStyle(styleBorder); cell13x.setCellStyle(styleBorder); cell14x.setCellStyle(styleBorder); cell15x.setCellStyle(styleBorder); cell16x.setCellStyle(styleBorder); cell17x.setCellStyle(styleBorder); cell18x.setCellStyle(styleBorder); cell19x.setCellStyle(styleBorder); cell20x.setCellStyle(styleBorder); cell21x.setCellStyle(styleBorder); cell22x.setCellStyle(styleBorder); cell23x.setCellStyle(styleBorder); cell24x.setCellStyle(styleBorder); cell25x.setCellStyle(styleBorder); cell26x.setCellStyle(styleBorder); cell27x.setCellStyle(styleBorder); cell28x.setCellStyle(styleBorder); cell29x.setCellStyle(styleBorder); cell30x.setCellStyle(styleBorder); cell31x.setCellStyle(styleBorder); cell32x.setCellStyle(styleBorder); cell33x.setCellStyle(styleBorder); i++; System.out.println("here : " + i); } } } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_Raw_Data.xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:reports.allStaticReports.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {/*from w w w .j a va 2s .co m*/ response.setContentType("text/html;charset=UTF-8"); //a page to get Report of all the servlets String year = "2015"; String month = ""; String county = ""; String form = "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 ww . j a v a 2 s .c om response.setContentType("text/html;charset=UTF-8"); //a page to get Report of all the servlets String 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 {//w w w . j av a 2 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 . j av a2 s. 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 = ""; 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); } }