List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setFont
public void setFont(HSSFFont font)
From source file:poi.hssf.usermodel.examples.NewLinesInCells.java
License:Apache License
public static void main(String[] args) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet();/*from ww w . ja v a2 s . c om*/ HSSFRow r = null; HSSFCell c = null; HSSFCellStyle cs = wb.createCellStyle(); HSSFFont f2 = wb.createFont(); cs = wb.createCellStyle(); cs.setFont(f2); // Word Wrap MUST be turned on cs.setWrapText(true); r = s.createRow(2); r.setHeight((short) 0x349); c = r.createCell(2); c.setCellType(HSSFCell.CELL_TYPE_STRING); c.setCellValue("Use \n with word wrap on to create a new line"); c.setCellStyle(cs); s.setColumnWidth(2, (int) ((50 * 8) / ((double) 1 / 20))); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); }
From source file:poi.hssf.usermodel.examples.RepeatingRowsAndColumns.java
License:Apache License
public static void main(String[] args) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet1 = wb.createSheet("first sheet"); wb.createSheet("second sheet"); wb.createSheet("third sheet"); HSSFFont boldFont = wb.createFont(); boldFont.setFontHeightInPoints((short) 22); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle boldStyle = wb.createCellStyle(); boldStyle.setFont(boldFont); HSSFRow row = sheet1.createRow(1);// www . ja v a2 s. co m HSSFCell cell = row.createCell(0); cell.setCellValue("This quick brown fox"); cell.setCellStyle(boldStyle); // Set the columns to repeat from column 0 to 2 on the first sheet wb.setRepeatingRowsAndColumns(0, 0, 2, -1, -1); // Set the rows to repeat from row 0 to 2 on the second sheet. wb.setRepeatingRowsAndColumns(1, -1, -1, 0, 2); // Set the the repeating rows and columns on the third sheet. wb.setRepeatingRowsAndColumns(2, 4, 5, 1, 2); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); }
From source file:poi.hssf.usermodel.examples.WorkingWithFonts.java
License:Apache License
public static void main(String[] args) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("new sheet"); // Create a row and put some cells in it. Rows are 0 based. HSSFRow row = sheet.createRow(1);/*from w w w. j a v a2 s . co m*/ // Create a new font and alter it. HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 24); font.setFontName("Courier New"); font.setItalic(true); font.setStrikeout(true); // Fonts are set into a style so create a new one to use. HSSFCellStyle style = wb.createCellStyle(); style.setFont(font); // Create a cell and put a value in it. HSSFCell cell = row.createCell(1); cell.setCellValue("This is a test of fonts"); cell.setCellStyle(style); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); }
From source file:poi.HSSFReadWrite.java
License:Apache License
/** * given a filename this outputs a sample sheet with just a set of * rows/cells.//from w w w .j a v a 2 s . c om */ private static void testCreateSampleSheet(String outputFilename) throws IOException { int rownum; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle(); HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); f.setFontHeightInPoints((short) 12); f.setColor((short) 0xA); f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); f2.setFontHeightInPoints((short) 10); f2.setColor((short) 0xf); f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cs.setFont(f); cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs2.setFillPattern((short) 1); // fill w fg cs2.setFillForegroundColor((short) 0xA); cs2.setFont(f2); wb.setSheetName(0, "HSSF Test"); for (rownum = 0; rownum < 300; rownum++) { HSSFRow r = s.createRow(rownum); if ((rownum % 2) == 0) { r.setHeight((short) 0x249); } for (int cellnum = 0; cellnum < 50; cellnum += 2) { HSSFCell c = r.createCell(cellnum); c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); if ((rownum % 2) == 0) { c.setCellStyle(cs); } c = r.createCell(cellnum + 1); c.setCellValue(new HSSFRichTextString("TEST")); // 50 characters divided by 1/20th of a point s.setColumnWidth(cellnum + 1, (int) (50 * 8 / 0.05)); if ((rownum % 2) == 0) { c.setCellStyle(cs2); } } } // draw a thick black border on the row at the bottom using BLANKS rownum++; rownum++; HSSFRow r = s.createRow(rownum); cs3.setBorderBottom(HSSFCellStyle.BORDER_THICK); for (int cellnum = 0; cellnum < 50; cellnum++) { HSSFCell c = r.createCell(cellnum); c.setCellStyle(cs3); } s.addMergedRegion(new CellRangeAddress(0, 3, 0, 3)); s.addMergedRegion(new CellRangeAddress(100, 110, 100, 110)); // end draw thick black border // create a sheet, set its title then delete it wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); // end deleted sheet FileOutputStream out = new FileOutputStream(outputFilename); wb.write(out); out.close(); // wb.close(); }
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 {//w w w.j a va2 s. c om /* 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 {//from w w w . j av a 2 s. 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./*from w w w . ja v a 2 s . c o m*/ * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, 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:ReadExcel.HSSFReadWrite.java
License:Apache License
/** * given a filename this outputs a sample sheet with just a set of * rows/cells./*from ww w . java 2 s. c om*/ */ private static void testCreateSampleSheet(String outputFilename) throws IOException { int rownum; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle(); HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); f.setFontHeightInPoints((short) 12); f.setColor((short) 0xA); f.setBold(true); f2.setFontHeightInPoints((short) 10); f2.setColor((short) 0xf); f2.setBold(true); cs.setFont(f); cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); cs2.setBorderBottom(BorderStyle.THIN); cs2.setFillPattern((short) 1); // fill w fg cs2.setFillForegroundColor((short) 0xA); cs2.setFont(f2); wb.setSheetName(0, "HSSF Test"); for (rownum = 0; rownum < 300; rownum++) { HSSFRow r = s.createRow(rownum); if ((rownum % 2) == 0) { r.setHeight((short) 0x249); } for (int cellnum = 0; cellnum < 50; cellnum += 2) { HSSFCell c = r.createCell(cellnum); c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); if ((rownum % 2) == 0) { c.setCellStyle(cs); } c = r.createCell(cellnum + 1); c.setCellValue(new HSSFRichTextString("TEST")); // 50 characters divided by 1/20th of a point s.setColumnWidth(cellnum + 1, (int) (50 * 8 / 0.05)); if ((rownum % 2) == 0) { c.setCellStyle(cs2); } } } // draw a thick black border on the row at the bottom using BLANKS rownum++; rownum++; HSSFRow r = s.createRow(rownum); cs3.setBorderBottom(BorderStyle.THICK); for (int cellnum = 0; cellnum < 50; cellnum++) { HSSFCell c = r.createCell(cellnum); c.setCellStyle(cs3); } s.addMergedRegion(new CellRangeAddress(0, 3, 0, 3)); s.addMergedRegion(new CellRangeAddress(100, 110, 100, 110)); // end draw thick black border // create a sheet, set its title then delete it wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); // end deleted sheet FileOutputStream out = new FileOutputStream(outputFilename); try { wb.write(out); } finally { out.close(); } wb.close(); }
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"); }//ww w . ja v a2s .c o m //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();/* w ww . j ava 2 s .com*/ 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(); }