List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook createSheet
@Override
public XSSFSheet createSheet(String sheetname)
From source file:packtest.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); if (args.length > 0) calendar.set(Calendar.YEAR, Integer.parseInt(args[0])); int year = calendar.get(Calendar.YEAR); XSSFWorkbook wb = new XSSFWorkbook(); Map<String, XSSFCellStyle> styles = createStyles(wb); for (int month = 0; month < 12; month++) { calendar.set(Calendar.MONTH, month); calendar.set(Calendar.DAY_OF_MONTH, 1); //create a sheet for each month XSSFSheet sheet = wb.createSheet(months[month]); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false);//from ww w . ja v a2 s. c o m XSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setOrientation(PrintOrientation.LANDSCAPE); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //the header row: centered text in 48pt font XSSFRow headerRow = sheet.createRow(0); headerRow.setHeightInPoints(80); XSSFCell titleCell = headerRow.createCell(0); titleCell.setCellValue(months[month] + " " + year); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1")); //header with month titles XSSFRow monthRow = sheet.createRow(1); for (int i = 0; i < days.length; i++) { //for compatibility with HSSF we have to set column width in units of 1/256th of a character width sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1)); XSSFCell monthCell = monthRow.createCell(i * 2); monthCell.setCellValue(days[i]); monthCell.setCellStyle(styles.get("month")); } int cnt = 1, day = 1; int rownum = 2; for (int j = 0; j < 6; j++) { XSSFRow row = sheet.createRow(rownum++); row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { XSSFCell dayCell_1 = row.createCell(i * 2); XSSFCell dayCell_2 = row.createCell(i * 2 + 1); int day_of_week = calendar.get(Calendar.DAY_OF_WEEK); if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) { dayCell_1.setCellValue(day); calendar.set(Calendar.DAY_OF_MONTH, ++day); if (i == 0 || i == days.length - 1) { dayCell_1.setCellStyle(styles.get("weekend_left")); dayCell_2.setCellStyle(styles.get("weekend_right")); } else { dayCell_1.setCellStyle(styles.get("workday_left")); dayCell_2.setCellStyle(styles.get("workday_right")); } } else { dayCell_1.setCellStyle(styles.get("grey_left")); dayCell_2.setCellStyle(styles.get("grey_right")); } cnt++; } if (calendar.get(Calendar.MONTH) > month) break; } } // Write the output to a file FileOutputStream out = new FileOutputStream("calendar-" + year + ".xlsx"); wb.write(out); out.close(); wb.close(); }
From source file:packtest.WorkbookProperties.java
License:Apache License
public static void main(String[] args) throws Exception { XSSFWorkbook workbook = new XSSFWorkbook(); workbook.createSheet("Workbook Properties"); POIXMLProperties props = workbook.getProperties(); /**//from ww w .java 2s . com * Extended properties are a predefined set of metadata properties * that are specifically applicable to Office Open XML documents. * Extended properties consist of 24 simple properties and 3 complex properties stored in the * part targeted by the relationship of type */ POIXMLProperties.ExtendedProperties ext = props.getExtendedProperties(); ext.getUnderlyingProperties().setCompany("Apache Software Foundation"); ext.getUnderlyingProperties().setTemplate("XSSF"); /** * Custom properties enable users to define custom metadata properties. */ POIXMLProperties.CustomProperties cust = props.getCustomProperties(); cust.addProperty("Author", "John Smith"); cust.addProperty("Year", 2009); cust.addProperty("Price", 45.50); cust.addProperty("Available", true); FileOutputStream out = new FileOutputStream(Utils.getPath("workbook.xlsx")); workbook.write(out); out.close(); }
From source file:poi.xssf.usermodel.examples.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); if (args.length > 0) calendar.set(Calendar.YEAR, Integer.parseInt(args[0])); int year = calendar.get(Calendar.YEAR); XSSFWorkbook wb = new XSSFWorkbook(); Map<String, XSSFCellStyle> styles = createStyles(wb); for (int month = 0; month < 12; month++) { calendar.set(Calendar.MONTH, month); calendar.set(Calendar.DAY_OF_MONTH, 1); //create a sheet for each month XSSFSheet sheet = wb.createSheet(months[month]); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false);// ww w .j a va2s .c om XSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setOrientation(PrintOrientation.LANDSCAPE); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //the header row: centered text in 48pt font XSSFRow headerRow = sheet.createRow(0); headerRow.setHeightInPoints(80); XSSFCell titleCell = headerRow.createCell(0); titleCell.setCellValue(months[month] + " " + year); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1")); //header with month titles XSSFRow monthRow = sheet.createRow(1); for (int i = 0; i < days.length; i++) { //for compatibility with HSSF we have to set column width in units of 1/256th of a character width sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1)); XSSFCell monthCell = monthRow.createCell(i * 2); monthCell.setCellValue(days[i]); monthCell.setCellStyle(styles.get("month")); } int cnt = 1, day = 1; int rownum = 2; for (int j = 0; j < 6; j++) { XSSFRow row = sheet.createRow(rownum++); row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { XSSFCell dayCell_1 = row.createCell(i * 2); XSSFCell dayCell_2 = row.createCell(i * 2 + 1); int day_of_week = calendar.get(Calendar.DAY_OF_WEEK); if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) { dayCell_1.setCellValue(day); calendar.set(Calendar.DAY_OF_MONTH, ++day); if (i == 0 || i == days.length - 1) { dayCell_1.setCellStyle(styles.get("weekend_left")); dayCell_2.setCellStyle(styles.get("weekend_right")); } else { dayCell_1.setCellStyle(styles.get("workday_left")); dayCell_2.setCellStyle(styles.get("workday_right")); } } else { dayCell_1.setCellStyle(styles.get("grey_left")); dayCell_2.setCellStyle(styles.get("grey_right")); } cnt++; } if (calendar.get(Calendar.MONTH) > month) break; } } // Write the output to a file FileOutputStream out = new FileOutputStream("calendar-" + year + ".xlsx"); wb.write(out); out.close(); }
From source file:poi.xssf.usermodel.examples.WorkbookProperties.java
License:Apache License
public static void main(String[] args) throws Exception { XSSFWorkbook workbook = new XSSFWorkbook(); workbook.createSheet("Workbook Properties"); POIXMLProperties props = workbook.getProperties(); /**//from w ww. j ava 2s. c o m * Extended properties are a predefined set of metadata properties * that are specifically applicable to Office Open XML documents. * Extended properties consist of 24 simple properties and 3 complex properties stored in the * part targeted by the relationship of type */ POIXMLProperties.ExtendedProperties ext = props.getExtendedProperties(); ext.getUnderlyingProperties().setCompany("Apache Software Foundation"); ext.getUnderlyingProperties().setTemplate("XSSF"); /** * Custom properties enable users to define custom metadata properties. */ POIXMLProperties.CustomProperties cust = props.getCustomProperties(); cust.addProperty("Author", "John Smith"); cust.addProperty("Year", 2009); cust.addProperty("Price", 45.50); cust.addProperty("Available", true); FileOutputStream out = new FileOutputStream("workbook.xlsx"); workbook.write(out); out.close(); }
From source file:regression.gui.MainWindow.java
void createExcelFile(List<Double> xes) { try {// w w w .j av a2 s . c o m String filename = "single.xlsx"; XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("FirstSheet"); int rownum = 0; for (Iterator<Double> iterator = xes.iterator(); iterator.hasNext();) { Double next = iterator.next(); XSSFRow row = sheet.createRow(rownum); row.createCell(0).setCellValue(next); row.createCell(1).setCellValue(0.0); rownum++; } FileOutputStream fileOut = new FileOutputStream(filename); workbook.write(fileOut); fileOut.close(); System.out.println("Stworzono"); } catch (Exception ex) { System.out.println(ex); } }
From source file:reports.allStaticReports.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {/*from w w w. j a va 2s . c o m*/ response.setContentType("text/html;charset=UTF-8"); //a page to get Report of all the servlets String year = "2015"; String month = ""; String county = ""; String form = "moh731"; if (request.getParameter("year") != null) { year = request.getParameter("year"); } if (request.getParameter("county") != null) { county = request.getParameter("county"); } if (request.getParameter("month") != null) { month = request.getParameter("month"); } if (request.getParameter("form") != null) { form = request.getParameter("form"); } String facilitywhere = ""; String yearwhere = ""; String monthwhere = ""; String countywhere = ""; String districtwhere = ""; String reporttype = ""; if (!year.equals("")) { yearwhere = " and Annee = '" + year + "'"; } if (!county.equals("")) { countywhere = " and countyid = '" + county + "'"; } if (!month.equals("")) { monthwhere = " and Mois = '" + month + "'"; } dbConn conn = new dbConn(); //an array to store haeder information. //the header information should appear only if a certain parameters are met //The parameters listed in here can be removed if the report type doesnt require certain parameters ArrayList Headerorgunits = new ArrayList(); Headerorgunits.add("COUNTY"); Headerorgunits.add("SUB-COUNTY"); Headerorgunits.add("FACILITY"); Headerorgunits.add("MFL CODE"); //An arralist to store a list of columns that will be selected from the database ArrayList dbcolumns = new ArrayList(); ArrayList labels = new ArrayList(); ArrayList tablename = new ArrayList(); ArrayList iscumulative = new ArrayList(); ArrayList ispercent = new ArrayList(); // ArrayList isactive=new ArrayList(); //An arralist to store a list of worksheets that will be selected from the sections ArrayList worksheets = new ArrayList(); //An arralist to store distinct worksheets. This will be derived from the the sections column ArrayList distinctsheets = new ArrayList(); String selectdistinctworksheet = "select distinct(section) from pivottable where form='" + form + "' and active='1' order by order_per_form"; conn.rs = conn.st.executeQuery(selectdistinctworksheet); while (conn.rs.next()) { //add the name of distinct sections distinctsheets.add(conn.rs.getString(1).replace("/", "_")); } String getattribs = "select indicator,label,section,cumulative,percentage,active ,shortlabel from pivottable where form='" + form + "' order by order_per_form, section"; conn.rs = conn.st.executeQuery(getattribs); while (conn.rs.next()) { //add active indicators only if (conn.rs.getString("active").equals("1")) { System.out.println(conn.rs.getString("indicator") + ""); //add indicator dbcolumns.add(conn.rs.getString("indicator")); //add label if (form.equals("moh731")) { labels.add(conn.rs.getString("shortlabel") + " \n " + conn.rs.getString("label")); } else { labels.add(conn.rs.getString("label")); } //add worksheets worksheets.add(conn.rs.getString("section").replace("/", "_")); String perc = "0"; String cum = "0"; if (conn.rs.getString("cumulative") != null) { iscumulative.add(conn.rs.getString("cumulative")); } else { iscumulative.add(cum); } if (conn.rs.getString("percentage") != null) { ispercent.add(conn.rs.getString("percentage")); } else { ispercent.add(perc); } } //end of active } //end of worksheet //if String perfacilselect = "select Upper(County) as County , Upper(DistrictNom) as District , UPPER(SubPartnerNom) as facility ,CentreSanteId as mflcode , district.CountyID as countyid , "; //-------------------------------------------------------------------------------------------- // PREPARE SELECT //-------------------------------------------------------------------------------------------- //prepare selects for (int a = 0; a < dbcolumns.size(); a++) { //if the indicator is a percent, get an avaerage if (ispercent.get(a).equals("1")) { perfacilselect += " AVG(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } else if (iscumulative.get(a).equals("1")) { perfacilselect += " " + dbcolumns.get(a) + " as " + dbcolumns.get(a); } else { perfacilselect += " SUM(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } //if the item is not the last, append a comma if (a < dbcolumns.size() - 1) { perfacilselect += " ,"; } } //------------------------------------------------------------------------------------ // FROM //------------------------------------------------------------------------------------ perfacilselect += " , isValidated as Form_Validated from " + form + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on " + form + ".SubPartnerID = subpartnera.SubPartnerID "; //------------------------------------------------------------------------------------------ // WHERE //------------------------------------------------------------------------------------------ perfacilselect += " where 1=1 " + monthwhere + yearwhere; //----------------------------------------------------------------------------------------- //GROUP BY //---------------------------------------------------------------------------------------- perfacilselect += " group by subpartnera.SubPartnerID"; System.out.println(perfacilselect); //______________________________________________________________________________________ // NOW CREATE THE WORKSHEETS //______________________________________________________________________________________ XSSFWorkbook wb = new XSSFWorkbook(); //______________________________________________________________________________________ //______________________________________________________________________________________ XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); for (int b = 0; b < distinctsheets.size(); b++) { XSSFSheet shet = wb.createSheet(distinctsheets.get(b).toString().toUpperCase()); //create headers for that worksheet XSSFRow rw = shet.createRow(1); int headercellpos = 0; //create the orgunit header eg COUNTY | SUBCOUNTY | FACILITY for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(Headerorgunits.get(e).toString()); cell0.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(e, 6000); } //create the indicators header eg HV0101 | HIV 09676 | TOTAL for (int c = 0; c < dbcolumns.size(); c++) { //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(b))) { shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(labels.get(c).toString()); cell0.setCellStyle(stylex); headercellpos++; } //end of comparing if } //end of for loop //create is validated header shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue("Form Validated ?"); cell0.setCellStyle(stylex); headercellpos++; } conn.rs = conn.st.executeQuery(perfacilselect); String sectioncopy = ""; int sheetpos = 0; int rowpos = 2; while (conn.rs.next()) { //-----------------INSIDE THE DATA FORM--------------------------------- //if the section changes, change the position of the worksheet too //also, reset the position counter to begin from 2 again. XSSFSheet shet = null; // if(--!sectioncopy.equals(shet)){} for (int g = 0; g < distinctsheets.size(); g++) { shet = wb.getSheetAt(g); int colpos = 0; //the fourth cell should XSSFRow rw = shet.createRow(rowpos); for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getString(e + 1)); cell0.setCellStyle(style2); colpos++; } //_________________________________________________________________ //VALUES //_________________________________________________________________ //create the indicators values eg 90 | 45 | 356 for (int c = 0; c < dbcolumns.size(); c++) { //get the section of the current dbcolumn //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(g))) { XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getString(dbcolumns.get(c).toString())); cell0.setCellStyle(stborder); colpos++; } //end of comparing if } //end of for loop String isvalidated = "Yes"; if (conn.rs.getString("Form_Validated").equals("0")) { isvalidated = "No"; } XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(isvalidated); cell0.setCellStyle(stborder); colpos++; } rowpos++; } IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); System.out.println("" + form.toUpperCase().trim() + "_REPORT_FOR_" + year.trim() + "(" + month.trim() + ")_CREATED_" + createdOn.trim() + ".xlsx"); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=" + form.toUpperCase().trim() + "_REPORT_FOR_" + year.trim() + "(" + month.trim() + ")_CREATED_" + createdOn.trim() + ".xlsx"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(allStaticReports.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.allStaticReportsdynamic.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String monthrange = ""; String periodlabel = ""; String period = "Month"; //The default one String periodicgroupby = " "; //note that in the current query there is an existing group by. Therefore this will be an extra infor to be added on the existing group by String isgroupby = "yes"; try {//w w w . jav a 2 s. c o m response.setContentType("text/html;charset=UTF-8"); //a page to get Report of all the servlets String months[] = null; String year = "2015"; String month = ""; String county = ""; String form = "moh731"; if (request.getParameter("year") != null) { year = request.getParameter("year"); } if (request.getParameter("county") != null) { county = request.getParameter("county"); } if (request.getParameter("month") != null) { months = request.getParameterValues("month"); } if (request.getParameter("form") != null) { form = request.getParameter("form"); } if (request.getParameter("groupby") != null) { isgroupby = request.getParameter("groupby"); } String pivotform = form; if (form.equalsIgnoreCase("MOH 731")) { form = "MOH731"; } if (form.equalsIgnoreCase("MOH 711A")) { form = "MOH711"; } if (form.equalsIgnoreCase("MOH 711 (New)")) { form = "moh711_new"; } String facilitywhere = ""; String yearwhere = ""; String monthwhere = ""; String countywhere = ""; String districtwhere = ""; String reporttype = ""; String subcounty_countywhere = ""; String indicatorslist = "all"; String sections = "all"; String subsections = "all"; String indicatorswhere = ""; //________________________________________________________________________________________________________________________________________________________ //________________________________________________________________________________________________________________________________________________________ String duration = ""; String semi_annual = ""; String quarter = ""; //================================================================================================== //XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX String facil = "361"; String yearmonthstart = ""; String yearmonthend = ""; //===================================================================================================== String header = ""; String reportType = ""; dbConn conn = new dbConn(); if (request.getParameter("reportType") != null) { reportType = request.getParameter("reportType"); } if (request.getParameter("indicators") != null) { indicatorslist = request.getParameter("indicators"); } //--------------------------sections------------ String sectionid[] = null; if (request.getParameterValues("sections") != null) { sectionid = request.getParameterValues("sections"); } String sectionvals = "("; if (request.getParameterValues("sections") != null) { for (int a = 0; a < sectionid.length; a++) { if (a == sectionid.length - 1) { sectionvals += sectionid[a] + ""; } else { sectionvals += sectionid[a] + ","; } } } sectionvals += ")"; if (sectionvals.equals("()")) { indicatorswhere += " "; } else { indicatorswhere += " and sectionid in " + sectionvals + " "; } //______________________________________________________subsections_______________________________________ String subsectionid[] = null; if (request.getParameterValues("subsection") != null) { subsectionid = request.getParameterValues("subsection"); } String subsectionvals = "("; if (request.getParameterValues("subsection") != null) { for (int a = 0; a < subsectionid.length; a++) { if (a == subsectionid.length - 1) { subsectionvals += subsectionid[a] + ""; } else { subsectionvals += subsectionid[a] + ","; } } } subsectionvals += ")"; if (subsectionvals.equals("()")) { indicatorswhere += " "; } else { indicatorswhere += " and subsectionid in " + subsectionvals + " "; } //______________________________________________________subsections_______________________________________ //add sections //special indicators if (indicatorslist.equals("special")) { indicatorswhere += " and specialindicator='1'"; } String reportDuration = ""; if (request.getParameter("reportDuration") != null) { reportDuration = request.getParameter("reportDuration"); } if (request.getParameter("facility") != null && reportType.equals("2")) { try { facil = request.getParameter("facility"); String getfacil = "select SubPartnerNom,CentreSanteId as mflcode from subpartnera where SubPartnerID='" + facil + "'"; conn.rs = conn.st.executeQuery(getfacil); while (conn.rs.next()) { header += " FACILITY : " + conn.rs.getString(1).toUpperCase() + " MFL CODE : " + conn.rs.getString(2) + " "; } } catch (SQLException ex) { Logger.getLogger(allStaticReportsdynamic.class.getName()).log(Level.SEVERE, null, ex); } } if (request.getParameter("county") != null && reportType.equals("2")) { try { county = request.getParameter("county"); subcounty_countywhere = " (county.CountyID='" + county + "') and ";//20160711 String getcounty = "select County from county where CountyID='" + county + "'"; conn.rs = conn.st.executeQuery(getcounty); while (conn.rs.next()) { header += " COUNTY : " + conn.rs.getString(1).toUpperCase() + " "; } } catch (SQLException ex) { Logger.getLogger(allStaticReportsdynamic.class.getName()).log(Level.SEVERE, null, ex); } } int yearcopy = Integer.parseInt(year); String yearmonth = "" + year; int prevYear = yearcopy - 1; int maxYearMonth = 0; int monthcopy = 0; String currentperiodlabel = "";//a variable to hold the name of a period inside a conn.rs if . header += " YEAR : " + year + ""; // GET REPORT DURATION============================================ //annually //____________________________________________________________________________________________________________Annual____________________________________ if (reportDuration.equals("1")) { yearmonth = "Annual Report For " + year; duration = " and " + form + ".yearmonth BETWEEN '" + prevYear + "10' AND '" + year + "09'"; period = "Year"; periodicgroupby = ", period "; monthrange = year; // tbstatduration="year='"+year+"'"; } //____________________________________________________________________________________________________________Semi_annual_____________________________ else if (reportDuration.equals("2")) { period = "Semi-Annual"; periodicgroupby = ", period "; try { String startMonth = "", endMonth = ""; String semiannualarray[] = request.getParameterValues("semi_annual"); String temporaryheader = " SEMI-ANNUAL"; for (int p = 0; p < semiannualarray.length; p++) { semi_annual = semiannualarray[p]; String getperiodname = "SELECT name as semiannual_name,months FROM semi_annual WHERE id='" + semi_annual + "'"; conn.rs = conn.st.executeQuery(getperiodname); if (conn.rs.next() == true) { String monthsinsemiannual[] = conn.rs.getString("months").split(","); currentperiodlabel = conn.rs.getString("semiannual_name"); //_________________add year at the end of period label if (semi_annual.equals("1")) { //periodname is in format oct-Mar we will make it to be in format Oct 2015-Mar 2016 currentperiodlabel = conn.rs.getString("semiannual_name").replace("-", " " + prevYear + "-") + " " + year; } else { //periodname is in format Apr-Sep we will make it to be in format Apr-Sep 2016 currentperiodlabel = conn.rs.getString("semiannual_name") + " " + year; } //____________________ if (p == 0) { startMonth = monthsinsemiannual[0]; endMonth = monthsinsemiannual[5]; monthrange = currentperiodlabel; } if (p == semiannualarray.length - 1 && semiannualarray.length > 1) { //last row monthrange += " to " + currentperiodlabel; //by now we expect monthrange to be something like Oct-Mar-Apr-Sep endMonth = monthsinsemiannual[5]; } if (periodlabel.equals("")) { periodlabel = conn.rs.getString("semiannual_name"); } else { periodlabel += "_" + conn.rs.getString("semiannual_name"); } } //end of conn. //get the yearmonthstart date if (p == 0 && semiannualarray.length > 1) { //if the quarters selected are several //this is the starting quarter temporaryheader += "S " + currentperiodlabel + " To "; if (new Integer(semi_annual) == 1) { //the year will be deducted by one if the selected months are 10, 11, 12 yearmonthstart = " " + form + ".yearmonth between '" + prevYear + "" + startMonth + "' and "; } else { yearmonthstart = " " + form + ".yearmonth between '" + year + "" + startMonth + "' and "; } } else if (p == semiannualarray.length - 1 && semiannualarray.length > 1) { //the last month temporaryheader += currentperiodlabel; //by now we expct something like from Semi-annuals oct-Mar to Apr -Sep //this assumes that the last month can never be yearmonthend = "'" + year + "" + endMonth + "'"; } else if (p == 0 && semiannualarray.length == 1) { // the number of quarters selected is one temporaryheader += " " + currentperiodlabel + " "; if (new Integer(semi_annual) == 1) { yearmonthstart = " " + form + ".yearmonth between '" + prevYear + startMonth + "' and"; } else { yearmonthstart = " " + form + ".yearmonth between '" + year + "" + startMonth + "' and "; } yearmonthend = "'" + year + "" + endMonth + "'"; //this assumes no end month date can come in oct-dec ..all the year month ends come on mar or sep } //end of last monthly row } //end of for loop header += " " + temporaryheader + ""; } catch (SQLException ex) { System.out.println(ex); } if (!semi_annual.equals("")) { duration = " and " + yearmonthstart + "" + yearmonthend; } } // ____________________________________________________________________________________________________Quarterly____________________ else if (reportDuration.equals("3")) { period = "Quarter"; periodicgroupby = ", period "; try { String startMonth = "", endMonth = ""; String quarterarray[] = request.getParameterValues("quarter"); String temporaryheader = " QUARTER"; for (int p = 0; p < quarterarray.length; p++) { quarter = quarterarray[p]; String getMonth = "SELECT months,name as qtrname FROM quarter WHERE id='" + quarter + "'"; conn.rs = conn.st.executeQuery(getMonth); if (conn.rs.next() == true) { String monthsinqtr[] = conn.rs.getString(1).split(","); //_________________add year at the end of period label if (quarter.equals("1")) { //periodname is in format oct-Mar we will make it to be in format Oct 2015-Mar 2016 currentperiodlabel = conn.rs.getString("qtrname").replace("-", " " + prevYear + "-") + " " + year; } else { //periodname is in format Apr-Sep we will make it to be in format Apr-Sep 2016 currentperiodlabel = conn.rs.getString("qtrname") + " " + year; } //____________________ if (p == 0) { startMonth = monthsinqtr[0]; monthrange = conn.rs.getString("qtrname"); endMonth = monthsinqtr[2]; } if (p == quarterarray.length - 1 && quarterarray.length > 1) { //last row monthrange += " to " + conn.rs.getString("qtrname"); endMonth = monthsinqtr[2]; } if (periodlabel.equals(""))// note period label gets all the periods in my loop { periodlabel = conn.rs.getString("qtrname"); } else { periodlabel += "_" + conn.rs.getString("qtrname"); } } //end of if //get the yearmonthstart date if (p == 0 && quarterarray.length > 1) { //if the quarters selected are several //this is the starting quarter temporaryheader += "S " + currentperiodlabel + " To "; if (new Integer(quarter) == 1) { //the year will be deducted by one if the selected months are 10, 11, 12 yearmonthstart = " " + form + ".yearmonth between '" + prevYear + "" + startMonth + "' and"; } else { yearmonthstart = " " + form + ".yearmonth between '" + year + "" + startMonth + "' and"; } } else if (p == quarterarray.length - 1 && quarterarray.length > 1) { //the last month temporaryheader += currentperiodlabel; //by now we expct something like from Quarters oct-Dec to Jan -Mar yearmonthend = "'" + year + "" + endMonth + "'"; } else if (p == 0 && quarterarray.length == 1) { // the number of quarters selected is one temporaryheader += " " + currentperiodlabel + " "; if (new Integer(quarter) == 1) { yearmonthstart = " " + form + ".yearmonth between '" + prevYear + startMonth + "' and "; yearmonthend = " '" + prevYear + "" + endMonth + "' "; } else { yearmonthstart = " " + form + ".yearmonth between '" + year + "" + startMonth + "' and "; yearmonthend = " '" + year + "" + endMonth + "' "; } } //end of last monthly row } //end of for loop header += " " + temporaryheader + ""; } catch (SQLException ex) { System.out.println(ex); } if (!quarter.equals("")) { duration = " and " + yearmonthstart + "" + yearmonthend; } } //_______________________________________________________________________________________________________________monthly______________________________ else if (reportDuration.equals("4")) { period = "Month"; periodicgroupby = ", period "; try { months = request.getParameterValues("month"); String temporaryheader = " MONTH"; for (int u = 0; u < months.length; u++) { month = months[u]; String getMonth = "SELECT name FROM month WHERE id='" + month + "'"; conn.rs = conn.st.executeQuery(getMonth); if (conn.rs.next() == true) { //_________________add year at the end of period label if (new Integer(month) >= 10 && new Integer(month) <= 12) { //periodname is in format oct-Mar we will make it to be in format Oct 2015-Mar 2016 currentperiodlabel = conn.rs.getString("name").substring(0, 3) + " " + prevYear; } else { //periodname is in format Apr-Sep we will make it to be in format Apr-Sep 2016 currentperiodlabel = conn.rs.getString("name").substring(0, 3) + " " + year; } //____________________ if (u == 0) { monthrange = conn.rs.getString(1); } if (u == months.length - 1 && months.length > 1) { monthrange += " to " + conn.rs.getString(1); } if (periodlabel.equals("")) { periodlabel = conn.rs.getString("name"); } else { periodlabel += "_" + conn.rs.getString("name"); } } //get the yearmonthstart date if (u == 0 && months.length > 1) { //if the month selected are several //this is the starting month temporaryheader += "S " + currentperiodlabel + " To "; if (new Integer(month) >= 10) { //the year will be deducted by one if the selected months are 10, 11, 12 yearmonthstart = " " + form + ".yearmonth between '" + prevYear + "" + month + "' and "; } else { yearmonthstart = " " + form + ".yearmonth between '" + year + "0" + month + "' and "; } } else if (u == months.length - 1 && months.length > 1) { //the last month if (new Integer(month) >= 10) { yearmonthend = "'" + prevYear + month + "'"; } else { yearmonthend = "'" + year + "0" + month + "'"; } } else if (u == 0 && months.length == 1) { // the number of months selected is one temporaryheader += " " + currentperiodlabel + " "; if (new Integer(month) >= 10) { yearmonthstart = " " + form + ".yearmonth = " + prevYear + month; } else { yearmonthstart = " " + form + ".yearmonth = " + year + "0" + month; } yearmonthend = ""; } //end of last monthly row } //end of for loop header += " " + temporaryheader + ""; } catch (SQLException ex) { Logger.getLogger(allStaticReportsdynamic.class.getName()).log(Level.SEVERE, null, ex); } if (!month.equals("")) { duration = " and " + yearmonthstart + "" + yearmonthend; } } //end of monthly records else { duration = ""; } //if someone doest want periodic grouping, then dont add a group by in the report.. get numbers for all rows. if (isgroupby.equals("No")) { periodicgroupby = ""; } //______________________________________________________________________________________COUNTY , SUBCOUNTY AND String subcountywhere = ""; String subcounty = ""; if (reportType.equalsIgnoreCase("2")) { //get data per county if the selected if (!request.getParameter("subcounty").equals("")) { subcounty = request.getParameter("subcounty"); subcounty_countywhere = " (district.DistrictID='" + subcounty + "') and "; } if (!request.getParameter("county").equals("")) { county = request.getParameter("county"); subcounty_countywhere = " (district.countyid='" + request.getParameter("county") + "') and ";//20160711 } if (!county.equals("")) { countywhere = " and district.countyid = '" + county + "'"; } if (!subcounty.equals("")) { subcountywhere = " and subpartnera.DistrictID = '" + subcounty + "'"; } if (!facil.equals("") && reportType.equalsIgnoreCase("2")) { facilitywhere = " and " + form + ".SubPartnerID = '" + facil + "'"; } } String joinedwhwere = " where 1=1 " + duration + " " + countywhere + " " + subcountywhere + " " + facilitywhere; // System.out.println(""+joinedwhwere); //we need a case statement in our main query. This will allow for friendly display of String myperiodcase = ""; if (isgroupby.equals("No")) { myperiodcase = "'" + monthrange + "' as period"; } else { if (period.equalsIgnoreCase("Year")) { myperiodcase = " case when Annee !='' then Annee else 'no year' end as period "; } else if (period.equalsIgnoreCase("Semi-Annual")) { myperiodcase = " case when (SUBSTRING(Yearmonth,5,6) >=01 and SUBSTRING(Yearmonth,5,6) <=03) || (SUBSTRING(Yearmonth,5,6) >=10 and SUBSTRING(Yearmonth,5,6) <=12) then 'Oct-Mar' " + "when SUBSTRING(Yearmonth,5,6) >=04 and SUBSTRING(Yearmonth,5,6) <=09 then 'Apr-Sep' else 'No period' end as period "; } else if (period.equalsIgnoreCase("Quarter")) { myperiodcase = " case when (SUBSTRING(Yearmonth,5,6) >=01 and SUBSTRING(Yearmonth,5,6) <=03) then 'Jan-Mar'" + "when (SUBSTRING(Yearmonth,5,6) >=10 and SUBSTRING(Yearmonth,5,6) <=12) then 'Oct-Dec' " + "when SUBSTRING(Yearmonth,5,6) >=04 and SUBSTRING(Yearmonth,5,6) <=06 then 'Apr-Jun' " + "when SUBSTRING(Yearmonth,5,6) >=07 and SUBSTRING(Yearmonth,5,6) <=09 then 'Jul-Sep' " + "else 'No period' end as period "; } else if (period.equalsIgnoreCase("Month")) { myperiodcase = " case " + " when SUBSTRING(Yearmonth,5,6)= '01' then 'Jan' " + " when SUBSTRING(Yearmonth,5,6)= '02' then 'Feb' " + " when SUBSTRING(Yearmonth,5,6)= '03' then 'Mar' " + " when SUBSTRING(Yearmonth,5,6)= '04' then 'Apr' " + " when SUBSTRING(Yearmonth,5,6)= '05' then 'May' " + " when SUBSTRING(Yearmonth,5,6)= '06' then 'Jun' " + " when SUBSTRING(Yearmonth,5,6)= '07' then 'Jul' " + " when SUBSTRING(Yearmonth,5,6)= '08' then 'Aug' " + " when SUBSTRING(Yearmonth,5,6)= '09' then 'Sep' " + " when SUBSTRING(Yearmonth,5,6)= '10' then 'Oct' " + " when SUBSTRING(Yearmonth,5,6)= '11' then 'Nov' " + " when SUBSTRING(Yearmonth,5,6)= '12' then 'Dec' " + " else 'No period' end as period "; } } //_________________________________________________________________END OF FILTERING SECTION_______________________________________________________________ //________________________________________________________________________________________________________________________________________________________ //an array to store haeder information. //the header information should appear only if certain parameters are met //The parameters listed in here can be removed if the report type doesnt require some parameters ArrayList Headerorgunits = new ArrayList(); Headerorgunits.add(period); Headerorgunits.add("County"); Headerorgunits.add("Sub-County"); Headerorgunits.add("Facility"); Headerorgunits.add("MFL Code"); //An arralist to store a list of columns that will be selected from the database ArrayList dbcolumns = new ArrayList(); ArrayList labels = new ArrayList(); ArrayList tablename = new ArrayList(); ArrayList iscumulative = new ArrayList(); ArrayList ispercent = new ArrayList(); // ArrayList isactive=new ArrayList(); //An arralist to store a list of worksheets that will be selected from the sections and the respective service area to determine the facilities whose data will appear in that sheet ArrayList worksheets = new ArrayList(); //An arralist to store distinct worksheets. This will be derived from the the sections column ArrayList distinctsheets = new ArrayList(); ArrayList distinctservicearea = new ArrayList(); //create an array to store the number of row for each excel worksheet. //This will help in retrieving the number of rows for each month since we are wring data for different months with increasing rows. //the size of that array will be determined by the number of excel worksheets String selectdistinctworksheet = "select section,servicearea from pivottable where form='" + form.replace("_", "") + "' and active='1' " + indicatorswhere + " group by section order by order_per_form"; conn.rs = conn.st.executeQuery(selectdistinctworksheet); while (conn.rs.next()) { //add the name of distinct sections distinctsheets.add(conn.rs.getString(1).replace("/", "_")); String servicearea = " 2=2 "; if (conn.rs.getString(2) != null) { servicearea = " " + conn.rs.getString(2) + "=1"; } distinctservicearea.add(servicearea); } int rowstartpersheet[] = new int[distinctsheets.size()]; //initialize the row start position for each workshett with 2 for (int x = 0; x < rowstartpersheet.length; x++) { rowstartpersheet[x] = 2; } String getattribs = "select indicator,label,section,cumulative,percentage,active ,shortlabel from pivottable where form='" + form.replace("_", "") + "' " + indicatorswhere + " order by order_per_form, section"; conn.rs = conn.st.executeQuery(getattribs); while (conn.rs.next()) { //add active indicators only if (conn.rs.getString("active").equals("1")) { //System.out.println(conn.rs.getString("indicator")+""); //add indicator dbcolumns.add(conn.rs.getString("indicator")); //add label if (form.equalsIgnoreCase("MOH731")) { labels.add(conn.rs.getString("shortlabel") + " \n" + conn.rs.getString("label")); } else { labels.add(conn.rs.getString("label")); } //add worksheets worksheets.add(conn.rs.getString("section").replace("/", "_")); String perc = "0"; String cum = "0"; if (conn.rs.getString("cumulative") != null) { iscumulative.add(conn.rs.getString("cumulative")); } else { iscumulative.add(cum); } if (conn.rs.getString("percentage") != null) { ispercent.add(conn.rs.getString("percentage")); } else { ispercent.add(perc); } } //end of active } //end of pivot table active //labels.add("ART High Volume"); //labels.add("HTC High Volume"); //labels.add("PMTCT High Volume"); //System.out.println(perfacilselect); //______________________________________________________________________________________ // CREATE THE WORKSHEETS //______________________________________________________________________________________ XSSFWorkbook wb = new XSSFWorkbook(); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFCellStyle stylesum = wb.createCellStyle(); stylesum.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylesum.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylesum.setBorderTop(HSSFCellStyle.BORDER_THIN); stylesum.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylesum.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylesum.setBorderRight(HSSFCellStyle.BORDER_THIN); stylesum.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); stylesum.setFont(fontx); stylesum.setWrapText(true); int cellrange[] = new int[4]; for (int b = 0; b < distinctsheets.size(); b++) { XSSFSheet shet = wb.createSheet(distinctsheets.get(b).toString().toUpperCase()); //Made my life veery simple... shet.setDisplayGridlines(false); shet.createFreezePane(5, 2); int firstcell = 1; //create headers for that worksheet XSSFRow rw = shet.createRow(1); int headercellpos = 0; //create the orgunit header eg MONTH | COUNTY | SUBCOUNTY | FACILITY for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(Headerorgunits.get(e).toString()); cell0.setCellStyle(stylex); headercellpos++; //shet.setColumnWidth(e, 6000); } //create the indicators header eg HV0101 | HIV 09676 | TOTAL for (int c = 0; c < dbcolumns.size(); c++) { //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(b))) { shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(labels.get(c).toString()); cell0.setCellStyle(stylex); headercellpos++; } //end of comparing if } //end of for loop //create is validated header shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue("ART High Volume"); cell0.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(headercellpos, 6000); XSSFCell cell1 = rw.createCell(headercellpos); cell1.setCellValue("HTC High Volume"); cell1.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(headercellpos, 6000); XSSFCell cell2 = rw.createCell(headercellpos); cell2.setCellValue("PMTCT High Volume"); cell2.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(headercellpos, 6000); XSSFCell cell2a = rw.createCell(headercellpos); cell2a.setCellValue("GSN"); cell2a.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(headercellpos, 6000); XSSFCell cell3 = rw.createCell(headercellpos); cell3.setCellValue("Form Validated ?"); cell3.setCellStyle(stylex); headercellpos++; } //______________________________________________________________________________________ //______________________________________________________________________________________ //-------------------------------------------------------------------------------------------- // MONTH LOOPS //-------------------------------------------------------------------------------------------- int cumulativestatingpoint = 3; boolean cumstartpointnoted = false; int colposcopy = 0; //for (int w = 0; w < months.length; w++) { String perfacilselect = "select " + myperiodcase + " , CONCAT(UPPER(SUBSTRING(County,1,1)),LOWER(SUBSTRING(County,2))) as County , DistrictNom as District , SubPartnerNom as facility ,CentreSanteId as mflcode , district.CountyID as countyid , "; //have a stringto get all distinct periods before execution of the main query. This will help in knowing the last String getdistinctperiod = "select " + myperiodcase; //-------------------------------------------------------------------------------------------- // PREPARE SELECT //-------------------------------------------------------------------------------------------- //prepare selects for (int a = 0; a < dbcolumns.size(); a++) { //if the indicator is a percent, get an avaerage if (ispercent.get(a).equals("1")) { perfacilselect += " AVG(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } else if (iscumulative.get(a).equals("1")) { perfacilselect += " SUBSTRING_INDEX(GROUP_CONCAT(CAST(IFNULL(" + dbcolumns.get(a) + ",0) AS CHAR) ORDER BY yearmonth DESC),',',1) as " + dbcolumns.get(a); //SUBSTRING_INDEX(GROUP_CONCAT(CAST(IFNULL(HV0303,0) AS CHAR) ORDER BY yearmonth DESC),',',1) } else { perfacilselect += " SUM(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } //if the item is not the last, append a comma //if(a<dbcolumns.size()-1){ perfacilselect += " ,"; // } } //---------------------------------add highvolume------------------------------------------------ perfacilselect += " IFNULL(ART_highvolume,0) as ART_highvolume, IFNULL(HTC_highvolume,0) as HTC_highvolume, IFNULL(PMTCT_highvolume,0) as PMTCT_highvolume, IFNULL(GSN,0) as GSN,"; //------------------------------------------------------------------------------------------------- // FROM //------------------------------------------------------------------------------------ perfacilselect += " isValidated as Form_Validated from " + form + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on " + form + ".SubPartnerID = subpartnera.SubPartnerID "; getdistinctperiod += " from " + form + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on " + form + ".SubPartnerID = subpartnera.SubPartnerID "; //------------------------------------------------------------------------------------------ // WHERE //------------------------------------------------------------------------------------------ perfacilselect += joinedwhwere; //contains any filterings getdistinctperiod += joinedwhwere; //contains any filterings //----------------------------------------------------------------------------------------- //GROUP BY //---------------------------------------------------------------------------------------- perfacilselect += " group by subpartnera.SubPartnerID " + periodicgroupby + " order by yearmonth "; getdistinctperiod += " group by period order by yearmonth "; String lastperiod = ""; ArrayList alldistinctperiods = new ArrayList(); //System.out.println(""+getdistinctperiod); conn.rs = conn.st.executeQuery(getdistinctperiod); while (conn.rs.next() == true) { lastperiod = conn.rs.getString(1); //here am asumming the last period will appear last and so will be mantained in the alldistinctperiods.add(lastperiod); } String sectioncopy = ""; int sheetpos = 0; int rowpos = 2; //-----------------INSIDE THE DATA FORM--------------------------------- //if the section (eg HTC, PMTCT) changes, change the current workshhet index too //also, reset the row position counter to begin from 2 again. XSSFSheet shet = null; // if(--!sectioncopy.equals(shet)){} //create the org unit data values e.g BARINGO | BARINGO CENTRAL |KABARNET DISTRICT HOSPITAL | MFL CODE for (int g = 0; g < distinctsheets.size(); g++) { rowpos = rowstartpersheet[g]; shet = wb.getSheetAt(g); int colpos = 0; String finalquery = perfacilselect.replace("1=1", distinctservicearea.get(g).toString()); //System.out.println("" + finalquery); conn.rs = conn.st.executeQuery(finalquery); while (conn.rs.next()) { if (lastperiod.equalsIgnoreCase(conn.rs.getString("period")) && cumstartpointnoted == false) { //save the current row position. //get it from the current rowstartpersheet[g] cumulativestatingpoint = rowstartpersheet[0]; cumstartpointnoted = true; //this ensures that we are fetching dataonce. // System.out.println("LAST STARTING POINT__"+cumulativestatingpoint); } //the fourth cell should XSSFRow rw = shet.createRow(rowpos); for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(colpos); //for mfl code, last header, output integers if (e > 3) { cell0.setCellValue(conn.rs.getInt(e + 1)); } else { cell0.setCellValue(conn.rs.getString(e + 1)); } cell0.setCellStyle(style2); colpos++; } //_________________________________________________________________ //VALUES //_________________________________________________________________ //create the indicators values eg 90 | 45 | 356 for (int c = 0; c < dbcolumns.size(); c++) { //get the section of the current dbcolumn //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(g))) { XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getInt(dbcolumns.get(c).toString())); cell0.setCellStyle(stborder); colpos++; } //end of comparing if } //end of for loop //ART_highvolume XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getInt("ART_highvolume")); cell0.setCellStyle(stborder); colpos++; //HTC_highvolume XSSFCell cell1 = rw.createCell(colpos); cell1.setCellValue(conn.rs.getInt("HTC_highvolume")); cell1.setCellStyle(stborder); colpos++; //PMTCT_highvolume XSSFCell cell2 = rw.createCell(colpos); cell2.setCellValue(conn.rs.getInt("PMTCT_highvolume")); cell2.setCellStyle(stborder); colpos++; //PMTCT_highvolume XSSFCell cell2a = rw.createCell(colpos); cell2a.setCellValue(conn.rs.getInt("GSN")); cell2a.setCellStyle(stborder); colpos++; String isvalidated = "Yes"; if (conn.rs.getString("Form_Validated").equals("0")) { isvalidated = "No"; } XSSFCell cell3 = rw.createCell(colpos); cell3.setCellValue(isvalidated); cell3.setCellStyle(stborder); colpos++; rowpos++; if (colpos > 1) { colposcopy = colpos - 1; } colpos = 0; } // end of while loop getting data from the db //____________________________________________________________________________________ //COLUMN AUTOSIZE COLUMN WIDTH , AUTOFILTER & AUTOSUM //____________________________________________________________________________________ //At this point we are sure this is the last row and we have exhausted fetching data for all periods //System.out.println(" Column position Before "+colposcopy); // if (lastperiod.equalsIgnoreCase(conn.rs.getString("period"))) { //System.out.println(" Column after "+colposcopy); shet.setAutoFilter(new CellRangeAddress(1, rowpos - 1, 0, colposcopy)); //System.out.println("1,"+rowpos+",0,"+colposcopy); for (int e = 0; e < Headerorgunits.size(); e++) { shet.autoSizeColumn(e); } //autosum XSSFRow initialrow = shet.getRow(2); XSSFRow prevrow = shet.getRow(rowpos - 1); XSSFRow cumrow = shet.getRow(cumulativestatingpoint);//not used for now but let it stay here may be of use in future XSSFRow rwsum = shet.createRow(rowpos); int colpossum = 0; int firstcols = 5; int periodcolumn = 0; for (int f = 0; f < firstcols; f++) { if (f == 0) { XSSFCell cellsum = rwsum.createCell(0); cellsum.setCellValue("Total"); cellsum.setCellStyle(stylesum); } else if (f > 0 && f < firstcols) { XSSFCell cellsum = rwsum.createCell(f); cellsum.setCellValue(" "); cellsum.setCellStyle(stylesum); } } for (int c = 0; c < dbcolumns.size(); c++) { if (worksheets.get(c).equals(distinctsheets.get(g))) { XSSFCell cellsum = rwsum.createCell(colpossum + firstcols); XSSFCell initialcell = initialrow.getCell(colpossum + firstcols); String cellformula = ""; XSSFCell prevcell = prevrow.getCell(colpossum + firstcols);//the last cell of the current column. rem whe are looping through all the columns //periodcolumncell XSSFCell initialperiodcell = initialrow.getCell(periodcolumn);// the first cell of the period(month, year) column XSSFCell currentperiodcell = prevrow.getCell(periodcolumn);//the last cell of the period column cellsum.setCellType(cellsum.CELL_TYPE_FORMULA); String startcellreference = initialcell.getReference(); String lastavailableperiod = currentperiodcell.getStringCellValue(); if (iscumulative.get(c).equals("1")) { //initialcell=cumrow.getCell(colpossum+firstcols); //Create a formula that sums all the data for the last column to appear in the row. This should pick the first column which is the period column. cellformula = "SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX(" + initialperiodcell.getReference() + ":" + currentperiodcell.getReference() + ",1,1),ROW(" + initialperiodcell.getReference() + ":" + currentperiodcell.getReference() + ")-ROW(INDEX(" + initialperiodcell.getReference() + ":" + currentperiodcell.getReference() + ",1,1)),0))=1),--(" + initialperiodcell.getReference() + ":" + currentperiodcell.getReference() + "=\"" + lastavailableperiod + "\")," + startcellreference + ":" + prevcell.getReference() + ")"; } else if (ispercent.get(c).equals("1")) { //initialcell=cumrow.getCell(colpossum+firstcols); //Create a formula that sums all the data for the last column to appear in the row. This should pick the first column which is the period column. cellformula = "ROUNDUP(SUBTOTAL(9," + startcellreference + ":" + prevcell.getReference() + "),1)"; // round of maximum values } else { cellformula = "SUBTOTAL(9," + startcellreference + ":" + prevcell.getReference() + ")"; } //for cumulative indicators, we need to do column total for the last selected month //we therefore need to always track where the previous month started at. cellsum.setCellFormula(cellformula); cellsum.setCellStyle(stylesum); colpossum++; } } //merge last cell shet.addMergedRegion(new CellRangeAddress(rowpos, rowpos, 0, 4)); // } end of checking if this is the last month //disbled for now // rowstartpersheet[g] = rowpos; } // end of distinct sheets report // }//end of monthly loop IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); System.out.println("" + form.toUpperCase().trim() + "_RPT_FOR_" + year.trim() + "(" + periodlabel + ")_GEN_" + createdOn.trim() + ".xlsx"); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=" + form.toUpperCase().trim() + "_REPORT_FOR_" + year.trim() + "(" + periodlabel.replace(" ", "") + ")_CREATED_" + createdOn.trim() + ".xlsx"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(allStaticReportsdynamic.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.allStaticReportsMonthly.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String monthrange = ""; String periodlabel = ""; String period = "Month"; //The default one try {/*from w w w .j a v a2s .c o m*/ response.setContentType("text/html;charset=UTF-8"); //a page to get Report of all the servlets String months[] = null; String year = "2016"; String month = "7"; String county = ""; String form = "moh731"; if (request.getParameter("year") != null) { year = request.getParameter("year"); } if (request.getParameter("county") != null) { county = request.getParameter("county"); } if (request.getParameter("month") != null) { months = request.getParameterValues("month"); } if (request.getParameter("form") != null) { form = request.getParameter("form"); } String pivotform = form; if (form.equalsIgnoreCase("MOH 731")) { form = "MOH731"; } if (form.equalsIgnoreCase("MOH 711A")) { form = "MOH711"; } if (form.equalsIgnoreCase("MOH 711 (New)")) { form = "moh711_new"; } String facilitywhere = ""; String yearwhere = ""; String monthwhere = ""; String countywhere = ""; String districtwhere = ""; String reporttype = ""; if (!year.equals("")) { yearwhere = " and Annee = '" + year + "'"; } if (!county.equals("")) { countywhere = " and countyid = '" + county + "'"; } dbConn conn = new dbConn(); //an array to store haeder information. //the header information should appear only if certain parameters are met //The parameters listed in here can be removed if the report type doesnt require some parameters ArrayList Headerorgunits = new ArrayList(); Headerorgunits.add(period); Headerorgunits.add("County"); Headerorgunits.add("Sub-County"); Headerorgunits.add("Facility"); Headerorgunits.add("MFL Code"); //An arralist to store a list of columns that will be selected from the database ArrayList dbcolumns = new ArrayList(); ArrayList labels = new ArrayList(); ArrayList tablename = new ArrayList(); ArrayList iscumulative = new ArrayList(); ArrayList ispercent = new ArrayList(); // ArrayList isactive=new ArrayList(); //An arralist to store a list of worksheets that will be selected from the sections and the respective service area to determine the facilities whose data will appear in that sheet ArrayList worksheets = new ArrayList(); //An arralist to store distinct worksheets. This will be derived from the the sections column ArrayList distinctsheets = new ArrayList(); ArrayList distinctservicearea = new ArrayList(); //create an array to store the number of row for each excel worksheet. //This will help in retrieving the number of rows for each month since we are wring data for different months with increasing rows. //the size of that array will be determined by the number of excel worksheets String selectdistinctworksheet = "select section,servicearea from pivottable where form='" + form.replace("_", "") + "' and active='1' group by section order by order_per_form"; conn.rs = conn.st.executeQuery(selectdistinctworksheet); while (conn.rs.next()) { //add the name of distinct sections distinctsheets.add(conn.rs.getString(1).replace("/", "_")); String servicearea = " 2=2 "; if (conn.rs.getString(2) != null) { servicearea = " " + conn.rs.getString(2) + "=1"; } distinctservicearea.add(servicearea); } int rowstartpersheet[] = new int[distinctsheets.size()]; //initialize the row start with 2 for (int x = 0; x < rowstartpersheet.length; x++) { rowstartpersheet[x] = 2; } String getattribs = "select indicator,label,section,cumulative,percentage,active ,shortlabel from pivottable where form='" + form.replace("_", "") + "' order by order_per_form, section"; conn.rs = conn.st.executeQuery(getattribs); while (conn.rs.next()) { //add active indicators only if (conn.rs.getString("active").equals("1")) { //System.out.println(conn.rs.getString("indicator")+""); //add indicator dbcolumns.add(conn.rs.getString("indicator")); //add label if (form.equalsIgnoreCase("MOH731")) { labels.add(conn.rs.getString("shortlabel") + " \n" + conn.rs.getString("label")); } else { labels.add(conn.rs.getString("label")); } //add worksheets worksheets.add(conn.rs.getString("section").replace("/", "_")); String perc = "0"; String cum = "0"; if (conn.rs.getString("cumulative") != null) { iscumulative.add(conn.rs.getString("cumulative")); } else { iscumulative.add(cum); } if (conn.rs.getString("percentage") != null) { ispercent.add(conn.rs.getString("percentage")); } else { ispercent.add(perc); } } //end of active } //end of pivot table active //labels.add("ART High Volume"); //labels.add("HTC High Volume"); //labels.add("PMTCT High Volume"); //System.out.println(perfacilselect); //______________________________________________________________________________________ // CREATE THE WORKSHEETS //______________________________________________________________________________________ XSSFWorkbook wb = new XSSFWorkbook(); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFCellStyle stylesum = wb.createCellStyle(); stylesum.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylesum.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylesum.setBorderTop(HSSFCellStyle.BORDER_THIN); stylesum.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylesum.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylesum.setBorderRight(HSSFCellStyle.BORDER_THIN); stylesum.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); stylesum.setFont(fontx); stylesum.setWrapText(true); int cellrange[] = new int[4]; for (int b = 0; b < distinctsheets.size(); b++) { XSSFSheet shet = wb.createSheet(distinctsheets.get(b).toString().toUpperCase()); //Made my life veery simple... shet.setDisplayGridlines(false); shet.createFreezePane(5, 2); int firstcell = 1; //shet.setAutoFilter(CellRangeAddress.valueOf("A2:N1")); //create headers for that worksheet XSSFRow rw = shet.createRow(1); int headercellpos = 0; //create the orgunit header eg COUNTY | SUBCOUNTY | FACILITY for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(Headerorgunits.get(e).toString()); cell0.setCellStyle(stylex); headercellpos++; //shet.setColumnWidth(e, 6000); } //create the indicators header eg HV0101 | HIV 09676 | TOTAL for (int c = 0; c < dbcolumns.size(); c++) { //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(b))) { shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(labels.get(c).toString()); cell0.setCellStyle(stylex); headercellpos++; } //end of comparing if } //end of for loop //create is validated header shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue("ART High Volume"); cell0.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(headercellpos, 6000); XSSFCell cell1 = rw.createCell(headercellpos); cell1.setCellValue("HTC High Volume"); cell1.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(headercellpos, 6000); XSSFCell cell2 = rw.createCell(headercellpos); cell2.setCellValue("PMTCT High Volume"); cell2.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(headercellpos, 6000); XSSFCell cell3 = rw.createCell(headercellpos); cell3.setCellValue("Form Validated ?"); cell3.setCellStyle(stylex); headercellpos++; } //______________________________________________________________________________________ //______________________________________________________________________________________ //-------------------------------------------------------------------------------------------- // MONTH LOOPS //-------------------------------------------------------------------------------------------- int cumulativestatingpoint = 3; boolean cumstartpointnoted = false; int colposcopy = 0; for (int w = 0; w < months.length; w++) { month = months[w]; if (!month.equals("")) { monthwhere = " and Mois = '" + month + "'"; } String getMonth = "SELECT name FROM month WHERE id='" + month + "'"; conn.rs = conn.st.executeQuery(getMonth); if (conn.rs.next() == true) { monthrange = conn.rs.getString(1); if (periodlabel.equals("")) { periodlabel = monthrange.substring(0, 3); } else { periodlabel += "_" + monthrange.substring(0, 3); } } String perfacilselect = "select '" + monthrange + "', CONCAT(UPPER(SUBSTRING(County,1,1)),LOWER(SUBSTRING(County,2))) as County , DistrictNom as District , SubPartnerNom as facility ,CentreSanteId as mflcode , district.CountyID as countyid , "; //-------------------------------------------------------------------------------------------- // PREPARE SELECT //-------------------------------------------------------------------------------------------- //prepare selects for (int a = 0; a < dbcolumns.size(); a++) { //if the indicator is a percent, get an avaerage if (ispercent.get(a).equals("1")) { perfacilselect += " AVG(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } else if (iscumulative.get(a).equals("1")) { perfacilselect += " " + dbcolumns.get(a) + " as " + dbcolumns.get(a); } else { perfacilselect += " SUM(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } //if the item is not the last, append a comma //if(a<dbcolumns.size()-1){ perfacilselect += " ,"; // } } //---------------------------------add highvolume------------------------------------------------ perfacilselect += " IFNULL(ART_highvolume,0) as ART_highvolume, IFNULL(HTC_highvolume,0) as HTC_highvolume, IFNULL(PMTCT_highvolume,0) as PMTCT_highvolume,"; //------------------------------------------------------------------------------------------------- // FROM //------------------------------------------------------------------------------------ perfacilselect += " isValidated as Form_Validated from " + form + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on " + form + ".SubPartnerID = subpartnera.SubPartnerID "; //------------------------------------------------------------------------------------------ // WHERE //------------------------------------------------------------------------------------------ perfacilselect += " where 1=1 " + monthwhere + yearwhere; //----------------------------------------------------------------------------------------- //GROUP BY //---------------------------------------------------------------------------------------- perfacilselect += " group by subpartnera.SubPartnerID"; String sectioncopy = ""; int sheetpos = 0; int rowpos = 2; //-----------------INSIDE THE DATA FORM--------------------------------- //if the section changes, change the position of the worksheet too //also, reset the position counter to begin from 2 again. XSSFSheet shet = null; // if(--!sectioncopy.equals(shet)){} //create the org unit data values e.g BARINGO | BARINGO CENTRAL |KABARNET DISTRICT HOSPITAL | MFL CODE for (int g = 0; g < distinctsheets.size(); g++) { if (w == months.length - 1 && cumstartpointnoted == false) { //save the current row position. //get it from the current rowstartpersheet[g] cumulativestatingpoint = rowstartpersheet[0]; cumstartpointnoted = true; //this ensures that we are fetching dataonce. // System.out.println("LAST STARTING POINT__"+cumulativestatingpoint); } rowpos = rowstartpersheet[g]; shet = wb.getSheetAt(g); int colpos = 0; String finalquery = perfacilselect.replace("1=1", distinctservicearea.get(g).toString()); System.out.println("" + finalquery); conn.rs = conn.st.executeQuery(finalquery); while (conn.rs.next()) { //the fourth cell should XSSFRow rw = shet.createRow(rowpos); for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(colpos); //for mfl code, last header, output integers if (e > 3) { cell0.setCellValue(conn.rs.getInt(e + 1)); } else { cell0.setCellValue(conn.rs.getString(e + 1)); } cell0.setCellStyle(style2); colpos++; } //_________________________________________________________________ //VALUES //_________________________________________________________________ //create the indicators values eg 90 | 45 | 356 for (int c = 0; c < dbcolumns.size(); c++) { //get the section of the current dbcolumn //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(g))) { XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getInt(dbcolumns.get(c).toString())); cell0.setCellStyle(stborder); colpos++; } //end of comparing if } //end of for loop //ART_highvolume XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getInt("ART_highvolume")); cell0.setCellStyle(stborder); colpos++; //HTC_highvolume XSSFCell cell1 = rw.createCell(colpos); cell1.setCellValue(conn.rs.getInt("HTC_highvolume")); cell1.setCellStyle(stborder); colpos++; //PMTCT_highvolume XSSFCell cell2 = rw.createCell(colpos); cell2.setCellValue(conn.rs.getInt("PMTCT_highvolume")); cell2.setCellStyle(stborder); colpos++; String isvalidated = "Yes"; if (conn.rs.getString("Form_Validated").equals("0")) { isvalidated = "No"; } XSSFCell cell3 = rw.createCell(colpos); cell3.setCellValue(isvalidated); cell3.setCellStyle(stborder); colpos++; rowpos++; if (colpos > 1) { colposcopy = colpos - 1; } colpos = 0; } // end of while loop getting data from the db //____________________________________________________________________________________ //COLUMN AUTOSIZE COLUMN WIDTH , AUTOFILTER & AUTOSUM //____________________________________________________________________________________ //At this point we are sure this is the last row and we have exhausted fetching data for all months //System.out.println(" Column position Before "+colposcopy); if (w == months.length - 1) { //System.out.println(" Column after "+colposcopy); shet.setAutoFilter(new CellRangeAddress(1, rowpos - 1, 0, colposcopy)); //System.out.println("1,"+rowpos+",0,"+colposcopy); for (int e = 0; e < Headerorgunits.size(); e++) { shet.autoSizeColumn(e); } //autosum XSSFRow rwsum = shet.createRow(rowpos); XSSFRow initialrow = shet.getRow(2); XSSFRow prevrow = shet.getRow(rowpos - 1); XSSFRow cumrow = shet.getRow(cumulativestatingpoint);//not used for now but let it stay here may be of use in future int colpossum = 0; int firstcols = 5; int periodcolumn = 0; for (int f = 0; f < firstcols; f++) { if (f == 0) { XSSFCell cellsum = rwsum.createCell(0); cellsum.setCellValue("Total"); cellsum.setCellStyle(stylesum); } else if (f > 0 && f < firstcols) { XSSFCell cellsum = rwsum.createCell(f); cellsum.setCellValue(" "); cellsum.setCellStyle(stylesum); } } for (int c = 0; c < dbcolumns.size(); c++) { if (worksheets.get(c).equals(distinctsheets.get(g))) { XSSFCell cellsum = rwsum.createCell(colpossum + firstcols); XSSFCell initialcell = initialrow.getCell(colpossum + firstcols); String cellformula = ""; XSSFCell prevcell = prevrow.getCell(colpossum + firstcols);//the last cell of the current column. rem whe are looping through all the columns //periodcolumncell XSSFCell initialperiodcell = initialrow.getCell(periodcolumn);// the first cell of the period(month, year) column XSSFCell currentperiodcell = prevrow.getCell(periodcolumn);//the last cell of the period column cellsum.setCellType(cellsum.CELL_TYPE_FORMULA); String startcellreference = initialcell.getReference(); String lastavailableperiod = currentperiodcell.getStringCellValue(); if (iscumulative.get(c).equals("1")) { //initialcell=cumrow.getCell(colpossum+firstcols); //Create a formula that sums all the data for the last column to appear in the row. This should pick the first column which is the period column. cellformula = "SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX(" + initialperiodcell.getReference() + ":" + currentperiodcell.getReference() + ",1,1),ROW(" + initialperiodcell.getReference() + ":" + currentperiodcell.getReference() + ")-ROW(INDEX(" + initialperiodcell.getReference() + ":" + currentperiodcell.getReference() + ",1,1)),0))=1),--(" + initialperiodcell.getReference() + ":" + currentperiodcell.getReference() + "=\"" + lastavailableperiod + "\")," + startcellreference + ":" + prevcell.getReference() + ")"; } else if (ispercent.get(c).equals("1")) { //initialcell=cumrow.getCell(colpossum+firstcols); //Create a formula that sums all the data for the last column to appear in the row. This should pick the first column which is the period column. cellformula = "ROUNDUP(SUBTOTAL(9," + startcellreference + ":" + prevcell.getReference() + "),1)"; // round of maximum values } else { cellformula = "SUBTOTAL(9," + startcellreference + ":" + prevcell.getReference() + ")"; } //for cumulative indicators, we need to do column total for the last selected month //we therefore need to always track where the previous month started at. cellsum.setCellFormula(cellformula); cellsum.setCellStyle(stylesum); colpossum++; } } //merge last cell shet.addMergedRegion(new CellRangeAddress(rowpos, rowpos, 0, 4)); } // rowstartpersheet[g] = rowpos; } // end of distinct sheets report } //end of monthly loop IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); System.out.println("" + form.toUpperCase().trim() + "_RPT_FOR_" + year.trim() + "(" + month.trim() + ")_GEN_" + createdOn.trim() + ".xlsx"); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=" + form.toUpperCase().trim() + "_REPORT_FOR_" + year.trim() + "(" + periodlabel + ")_CREATED_" + createdOn.trim() + ".xlsx"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(allStaticReportsMonthly.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.excelstaticreports.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {/*from ww w .j av a 2s . c o m*/ response.setContentType("text/html;charset=UTF-8"); //a page to get Report of all the servlets String year = "2015"; String month = ""; String county = ""; String form = "kmmp"; if (request.getParameter("year") != null) { year = request.getParameter("year"); } if (request.getParameter("county") != null) { county = request.getParameter("county"); } if (request.getParameter("month") != null) { month = request.getParameter("month"); } if (request.getParameter("form") != null) { form = request.getParameter("form"); } String facilitywhere = ""; String yearwhere = ""; String monthwhere = ""; String countywhere = ""; String districtwhere = ""; String reporttype = ""; if (!year.equals("")) { yearwhere = " and Annee = '" + year + "'"; } if (!county.equals("")) { countywhere = " and countyid = '" + county + "'"; } if (!month.equals("")) { monthwhere = " and Mois = '" + month + "'"; } dbConn conn = new dbConn(); //an array to store haeder information. //the header information should appear only if a certain parameters are met //The parameters listed in here can be removed if the report type doesnt require certain parameters ArrayList Headerorgunits = new ArrayList(); Headerorgunits.add("COUNTY"); Headerorgunits.add("SUB-COUNTY"); Headerorgunits.add("FACILITY"); Headerorgunits.add("MFL CODE"); //An arralist to store a list of columns that will be selected from the database ArrayList dbcolumns = new ArrayList(); ArrayList labels = new ArrayList(); ArrayList tablename = new ArrayList(); ArrayList iscumulative = new ArrayList(); ArrayList ispercent = new ArrayList(); // ArrayList isactive=new ArrayList(); //An arralist to store a list of worksheets that will be selected from the sections ArrayList worksheets = new ArrayList(); //An arralist to store distinct worksheets. This will be derived from the the sections column ArrayList distinctsheets = new ArrayList(); String selectdistinctworksheet = "select distinct(section) from pivottable where form='" + form + "' and active='1'"; conn.rs = conn.st.executeQuery(selectdistinctworksheet); while (conn.rs.next()) { //add the name of distinct sections distinctsheets.add(conn.rs.getString(1).replace("/", "_")); } String getattribs = "select indicator,label,section,cumulative,percentage,active ,shortlabel from pivottable where form='" + form + "' order by tableid, section"; conn.rs = conn.st.executeQuery(getattribs); while (conn.rs.next()) { //add active indicators only if (conn.rs.getString("active").equals("1")) { System.out.println(conn.rs.getString("indicator") + ""); //add indicator dbcolumns.add(conn.rs.getString("indicator")); //add label if (form.equals("moh731")) { labels.add(conn.rs.getString("shortlabel") + " \n " + conn.rs.getString("label")); } else { labels.add(conn.rs.getString("label")); } //add worksheets worksheets.add(conn.rs.getString("section").replace("/", "_")); String perc = "0"; String cum = "0"; if (conn.rs.getString("cumulative") != null) { iscumulative.add(conn.rs.getString("cumulative")); } else { iscumulative.add(cum); } if (conn.rs.getString("percentage") != null) { ispercent.add(conn.rs.getString("percentage")); } else { ispercent.add(perc); } } //end of active } //end of worksheet //if String perfacilselect = "select Upper(County) as County , Upper(DistrictNom) as District , UPPER(SubPartnerNom) as facility ,CentreSanteId as mflcode , district.CountyID as countyid , "; //-------------------------------------------------------------------------------------------- // PREPARE SELECT //-------------------------------------------------------------------------------------------- //prepare selects for (int a = 0; a < dbcolumns.size(); a++) { //if the indicator is a percent, get an avaerage if (ispercent.get(a).equals("1")) { perfacilselect += " AVG(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } else if (iscumulative.get(a).equals("1")) { perfacilselect += " " + dbcolumns.get(a) + " as " + dbcolumns.get(a); } else { perfacilselect += " SUM(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a); } //if the item is not the last, append a comma if (a < dbcolumns.size() - 1) { perfacilselect += " ,"; } } //------------------------------------------------------------------------------------ // FROM //------------------------------------------------------------------------------------ perfacilselect += " , isValidated as Form_Validated from " + form + " join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID ) on " + form + ".SubPartnerID = subpartnera.SubPartnerID "; //------------------------------------------------------------------------------------------ // WHERE //------------------------------------------------------------------------------------------ perfacilselect += " where 1=1 " + monthwhere + yearwhere; //----------------------------------------------------------------------------------------- //GROUP BY //---------------------------------------------------------------------------------------- perfacilselect += " group by subpartnera.SubPartnerID"; System.out.println(perfacilselect); //______________________________________________________________________________________ // NOW CREATE THE WORKSHEETS //______________________________________________________________________________________ XSSFWorkbook wb = new XSSFWorkbook(); //______________________________________________________________________________________ //______________________________________________________________________________________ XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); for (int b = 0; b < distinctsheets.size(); b++) { XSSFSheet shet = wb.createSheet(distinctsheets.get(b).toString().toUpperCase()); //create headers for that worksheet XSSFRow rw = shet.createRow(1); int headercellpos = 0; //create the orgunit header eg COUNTY | SUBCOUNTY | FACILITY for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(Headerorgunits.get(e).toString()); cell0.setCellStyle(stylex); headercellpos++; shet.setColumnWidth(e, 6000); } //create the indicators header eg HV0101 | HIV 09676 | TOTAL for (int c = 0; c < dbcolumns.size(); c++) { //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(b))) { shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue(labels.get(c).toString()); cell0.setCellStyle(stylex); headercellpos++; } //end of comparing if } //end of for loop //create is validated header shet.setColumnWidth(headercellpos, 6000); XSSFCell cell0 = rw.createCell(headercellpos); cell0.setCellValue("Form Validated ?"); cell0.setCellStyle(stylex); headercellpos++; } conn.rs = conn.st.executeQuery(perfacilselect); String sectioncopy = ""; int sheetpos = 0; int rowpos = 2; while (conn.rs.next()) { //-----------------INSIDE THE DATA FORM--------------------------------- //if the section changes, change the position of the worksheet too //also, reset the position counter to begin from 2 again. XSSFSheet shet = null; // if(--!sectioncopy.equals(shet)){} for (int g = 0; g < distinctsheets.size(); g++) { shet = wb.getSheetAt(g); int colpos = 0; //the fourth cell should XSSFRow rw = shet.createRow(rowpos); for (int e = 0; e < Headerorgunits.size(); e++) { XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getString(e + 1)); cell0.setCellStyle(style2); colpos++; } //_________________________________________________________________ //VALUES //_________________________________________________________________ //create the indicators values eg 90 | 45 | 356 for (int c = 0; c < dbcolumns.size(); c++) { //get the section of the current dbcolumn //compare if the indicator belongs to the specified section and hence worksheet //recall, each indicator has got an associated section / worksheet //An indicator should be put as an header in the respective worksheet if (worksheets.get(c).equals(distinctsheets.get(g))) { XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(conn.rs.getString(dbcolumns.get(c).toString())); cell0.setCellStyle(stborder); colpos++; } //end of comparing if } //end of for loop String isvalidated = "Yes"; if (conn.rs.getString("Form_Validated").equals("0")) { isvalidated = "No"; } XSSFCell cell0 = rw.createCell(colpos); cell0.setCellValue(isvalidated); cell0.setCellStyle(stborder); colpos++; } rowpos++; } IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); System.out.println("" + form.toUpperCase().trim() + "_REPORT_FOR_" + year.trim() + "(" + month.trim() + ")_CREATED_" + createdOn.trim() + ".xlsx"); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=" + form.toUpperCase().trim() + "_REPORT_FOR_" + year.trim() + "(" + month.trim() + ")_CREATED_" + createdOn.trim() + ".xlsx"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(allStaticReportsMonthly.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.moh731_Facility.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();// w w w . j a v a 2s. c o m dbConn conn = new dbConn(); pmtcthv = 0; htchv = 0; arthv = 0; year = request.getParameter("year"); month = request.getParameter("month"); //-------------------------------------------------------------------------------- //-------------------------------------------------------------------------------- //added later to accomodate the years String subpartnerid = "SubPartnerID"; int monthint = 0; int yearint = 0; monthint = Integer.parseInt(month); yearint = Integer.parseInt(year); String subpartnera = "subpartnera"; if (yearint == 2015) { if (monthint == 10 || monthint == 11 || monthint == 12 || monthint == 1 || monthint == 2 || monthint == 3) { //here use a different subpartner id subpartnerid = "SP_ID"; subpartnera = "subpartnera2014"; } else { subpartnerid = "SubPartnerID"; subpartnera = "subpartnera"; } } else if (yearint <= 2014) { subpartnerid = "SP_ID"; subpartnera = "subpartnera2014"; } else if (yearint > 2015) { subpartnerid = "SubPartnerID"; subpartnera = "subpartnera"; } //--------------------------------------------------------------------------------------- //--------------------------------------------------------------------------------------- // year="2015"; // month="5"; counter = 0; monthName = ""; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet shet4 = wb.createSheet("HTC"); XSSFSheet shet1 = wb.createSheet("PMTCT"); XSSFSheet shet2 = wb.createSheet("Care and Treatment"); XSSFSheet shet3 = wb.createSheet("PEP"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); for (int i = 0; i <= 3; i++) { shet1.setColumnWidth(i, 6000); } for (int i = 4; i <= 47; i++) { shet1.setColumnWidth(i, 4000); } for (int i = 0; i <= 3; i++) { shet2.setColumnWidth(i, 6000); } for (int i = 4; i <= 66; i++) { shet2.setColumnWidth(i, 3000); } for (int i = 0; i <= 3; i++) { shet3.setColumnWidth(i, 6000); } for (int i = 4; i <= 19; i++) { shet3.setColumnWidth(i, 3000); } for (int i = 0; i <= 3; i++) { shet4.setColumnWidth(i, 6000); } for (int i = 4; i <= 14; i++) { shet4.setColumnWidth(i, 3000); } headers = "COUNTY,SUB COUNTY,FACILITY NAME,MFL CODE,ART HIGH VOLUME,HTC HIGH VOLUME,PMTCT HIGH VOLUME"; String arrayHeader[] = headers.split(","); int headerno = 0; // XSSFRow rw0S1=shet1.createRow(0); XSSFRow rw1S1 = shet1.createRow(0); // XSSFRow rw0S2=shet2.createRow(0); XSSFRow rw1S2 = shet2.createRow(0); // XSSFRow rw0S3=shet3.createRow(0); XSSFRow rw1S3 = shet3.createRow(0); // XSSFRow rw0S3=shet3.createRow(0); XSSFRow rw1S4 = shet4.createRow(0); // LOOP THROUGH AND WRITE STATIC HEADERS TO THE ELEMENTS for (String headername : arrayHeader) { XSSFCell S1cell = rw1S1.createCell(headerno); S1cell.setCellValue(headername); S1cell.setCellStyle(stylex); XSSFCell S2cell = rw1S2.createCell(headerno); S2cell.setCellValue(headername); S2cell.setCellStyle(stylex); XSSFCell S3cell = rw1S3.createCell(headerno); S3cell.setCellValue(headername); S3cell.setCellStyle(stylex); XSSFCell S4cell = rw1S4.createCell(headerno); S4cell.setCellValue(headername); S4cell.setCellStyle(stylex); headerno++; } String getMonth = "SELECT name FROM month WHERE id='" + month + "'"; conn.rs = conn.st.executeQuery(getMonth); if (conn.rs.next() == true) { monthName = conn.rs.getString(1); } counterPMTCT = counterART = counterPEP = counterHTC = 6; //get the specifc indicator names and the respective section and a count of indicators in each section which dictates the number of columns in the whole excel String getVariables = "SELECT * FROM moh731_indicators ORDER BY id"; conn.rs1 = conn.st1.executeQuery(getVariables); while (conn.rs1.next()) { elementName = conn.rs1.getString("indicator") + " \n" + conn.rs1.getString("code"); if (conn.rs1.getString("section").equals("1. HIV Counselling and Testing")) { counterHTC++; XSSFCell S4cell = rw1S4.createCell(counterHTC); S4cell.setCellValue(elementName); S4cell.setCellStyle(stylex); } else if (conn.rs1.getString("section").equals("2. Prevention of Mother-to-Child Transmission")) { counterPMTCT++; XSSFCell S1cell = rw1S1.createCell(counterPMTCT); S1cell.setCellValue(elementName); S1cell.setCellStyle(stylex); } else if (conn.rs1.getString("section").equals("3. Care and Treatment")) { counterART++; XSSFCell S2cell = rw1S2.createCell(counterART); S2cell.setCellValue(elementName); S2cell.setCellStyle(stylex); } else if (conn.rs1.getString("section").equals("5. Post-Exposure Prophylaxis")) { counterPEP++; XSSFCell S3cell = rw1S3.createCell(counterPEP); S3cell.setCellValue(elementName); S3cell.setCellStyle(stylex); } else { } } XSSFCell S3cella = rw1S1.createCell(49); S3cella.setCellValue("Validation Run"); S3cella.setCellStyle(stylex); XSSFCell S3cell2a = rw1S2.createCell(68); S3cell2a.setCellValue("Validation Run"); S3cell2a.setCellStyle(stylex); XSSFCell S3cell3a = rw1S3.createCell(20); S3cell3a.setCellValue("Validation Run"); S3cell3a.setCellStyle(stylex); XSSFCell S4cell3a = rw1S4.createCell(22); S4cell3a.setCellValue("Validation Run"); S4cell3a.setCellStyle(stylex); // counter=1; System.out.println("pmtct : " + counterPMTCT + " art : " + counterART + " PEP : " + counterPEP + " HTC : " + counterHTC); // counterPMTCT=counterPMTCT-5; // counterART=counterART-5; // counterPEP=counterPEP-5; counterPMTCT1 = counterART1 = counterPEP1 = counterHTC1 = 0; String getData = "SELECT county.County,district.DistrictNom," + subpartnera + ".SubPartnerNom," + subpartnera + ".CentreSanteId," + " moh731.HV0201,moh731.HV0202,moh731.HV0203,moh731.HV0204,moh731.HV0205,moh731.HV0206,moh731.HV0207,moh731.HV0208,moh731.HV0209,moh731.HV0210,moh731.HV0211,moh731.HV0212,moh731.HV0213," + "moh731.HV0214,moh731.HV0215,moh731.HV0216,moh731.HV0217,moh731.HV0218,moh731.HV0219,moh731.HV0220,moh731.HV0221,moh731.HV0224,moh731.HV0225,moh731.HV0226,moh731.HV0227,moh731.HV0228,moh731.HV0229," + " moh731.HV0230,moh731.HV0231,moh731.HV0232,moh731.HV0233,moh731.HV0234,moh731.HV0235,moh731.HV0236,moh731.HV0237,moh731.HV0238,moh731.HV0239,moh731.HV0240,moh731.HV0241,moh731.HV0242," + " moh731.HV0243,moh731.HV0244,moh731.HV0301,moh731.HV0302,moh731.HV0303,moh731.HV0304,moh731.HV0305,moh731.HV0306,moh731.HV0307,moh731.HV0308,moh731.HV0309,moh731.HV0310,moh731.HV0311,moh731.HV0312,moh731.HV0313,moh731.HV0314," + " moh731.HV0315,moh731.HV0316,moh731.HV0317,moh731.HV0318,moh731.HV0319,moh731.HV0320,moh731.HV0321,moh731.HV0322,moh731.HV0323,moh731.HV0324,moh731.HV0325,moh731.HV0326,moh731.HV0327,moh731.HV0328," + " moh731.HV0329,moh731.HV0330,moh731.HV0331,moh731.HV0332,moh731.HV0333,moh731.HV0334,moh731.HV0335,moh731.HV0336,moh731.HV0337,moh731.HV0338,moh731.HV0339,moh731.HV0340,moh731.HV0341," + " moh731.HV0342,moh731.HV0343,moh731.HV0344,moh731.HV0345,moh731.HV0346,moh731.HV0347,moh731.HV0348,moh731.HV0349,moh731.HV0350,moh731.HV0351,moh731.HV0352,moh731.HV0353," + " moh731.HV0354,moh731.HV0355,moh731.HV0904,moh731.HV0905,moh731.HV0370,moh731.HV0371,moh731.HV0372,moh731.HV0373," + " moh731.HV0501,moh731.HV0502,moh731.HV0503,moh731.HV0504,moh731.HV0505,moh731.HV0506,moh731.HV0507,moh731.HV0508,moh731.HV0509,moh731.HV0510,moh731.HV0511,moh731.HV0512,moh731.HV0513,moh731.HV0514," + " moh731.HV0101,moh731.HV0102,moh731.HV0103,moh731.HV0105,moh731.HV0106,moh731.HV0107,moh731.HV0108,moh731.HV0109,moh731.HV0110,moh731.HV0111,moh731.HV0112,moh731.HV0113,moh731.HV0114,moh731.HV0115,moh731.HV0116 " + "," + subpartnera + ".PMTCT," + subpartnera + ".ART," + subpartnera + ".PEP," + subpartnera + ".HTC,isValidated ,IFNULL(ART_highvolume,0) as ART_highvolume, IFNULL(HTC_highvolume,0) as HTC_highvolume, IFNULL(PMTCT_highvolume,0) as PMTCT_highvolume " + " FROM moh731 JOIN " + subpartnera + " ON moh731.SubPartnerID=" + subpartnera + "." + subpartnerid + " " + "JOIN district ON " + subpartnera + ".DistrictID=district.DistrictID " + "JOIN county ON county.CountyID=district.CountyID " + " WHERE moh731.Mois='" + month + "' && moh731.Annee='" + year + "'" + " ORDER BY county.County,district.DistrictNom," + subpartnera + "." + subpartnerid + ""; conn.rs = conn.st.executeQuery(getData); int valuesstartrow = 7; System.out.println("|__" + getData); while (conn.rs.next()) { counter++; county = conn.rs.getString(1); district = conn.rs.getString(2); facilityname = conn.rs.getString(3); mflcode = conn.rs.getString(4); arthv = conn.rs.getInt("ART_highvolume"); htchv = conn.rs.getInt("HTC_highvolume"); pmtcthv = conn.rs.getInt("PMTCT_highvolume"); System.out.println("@@@@@@" + arthv + "_" + htchv + "_" + pmtcthv); //ART High Volume HTC High Volume PMTCT High Volume String basicDetails = county + "@" + district + "@" + facilityname + "@" + mflcode + "@" + arthv + "@" + htchv + "@" + pmtcthv; String arrayDetails[] = basicDetails.split("@"); if (conn.rs.getInt(subpartnera + ".PMTCT") == 1) { counterPMTCT1++; XSSFRow rw2S1 = shet1.createRow(counterPMTCT1); int facilno = 0; for (int b = 0; b < arrayDetails.length; b++) { XSSFCell S3cell = rw2S1.createCell(facilno); if (b < 3) { S3cell.setCellValue(arrayDetails[b]); } else { S3cell.setCellValue(Integer.parseInt(arrayDetails[b])); } S3cell.setCellStyle(stborder); facilno++; } int pos; for (int i = valuesstartrow; i <= counterPMTCT; i++) { XSSFCell S3cell = rw2S1.createCell(i); pos = i + 1; //System.out.println("cell no 1 : "+i+" value no : "+pos); S3cell.setCellValue(conn.rs.getInt(pos - 3)); S3cell.setCellStyle(stborder); } isValidated = conn.rs.getString("isValidated"); if (isValidated.equals("1")) { isValidated = "Yes"; } else { isValidated = "No"; } XSSFCell S3cell = rw2S1.createCell(49); S3cell.setCellValue(isValidated); S3cell.setCellStyle(stborder); } if (conn.rs.getInt(subpartnera + ".ART") == 1) { counterART1++; XSSFRow rw2S2 = shet2.createRow(counterART1); int facilno = 0; for (int b = 0; b < arrayDetails.length; b++) { XSSFCell S3cell = rw2S2.createCell(facilno); if (b < 3) { S3cell.setCellValue(arrayDetails[b]); } else { S3cell.setCellValue(Integer.parseInt(arrayDetails[b])); } S3cell.setCellStyle(stborder); facilno++; } int pos; for (int i = valuesstartrow; i <= counterART; i++) { XSSFCell S3cell = rw2S2.createCell(i); pos = i + 43; System.out.println("cell no 2 : " + i + " value no : " + pos); S3cell.setCellValue(conn.rs.getInt(pos - 3)); S3cell.setCellStyle(stborder); } isValidated = conn.rs.getString("isValidated"); if (isValidated.equals("1")) { isValidated = "Yes"; } else { isValidated = "No"; } XSSFCell S3cell = rw2S2.createCell(68); S3cell.setCellValue(isValidated); S3cell.setCellStyle(stborder); } if (conn.rs.getInt(subpartnera + ".PEP") == 1) { counterPEP1++; XSSFRow rw2S3 = shet3.createRow(counterPEP1); int facilno = 0; for (int b = 0; b < arrayDetails.length; b++) { XSSFCell S3cell = rw2S3.createCell(facilno); if (b < 3) { S3cell.setCellValue(arrayDetails[b]); } else { S3cell.setCellValue(Integer.parseInt(arrayDetails[b])); } S3cell.setCellStyle(stborder); facilno++; } int pos; for (int i = valuesstartrow; i <= counterPEP; i++) { XSSFCell S3cell = rw2S3.createCell(i); pos = i + 104; //System.out.println("cell no 3 : "+i+" value no : "+pos); S3cell.setCellValue(conn.rs.getInt(pos - 3)); S3cell.setCellStyle(stborder); } isValidated = conn.rs.getString("isValidated"); if (isValidated.equals("1")) { isValidated = "Yes"; } else { isValidated = "No"; } XSSFCell S3cell = rw2S3.createCell(20); S3cell.setCellValue(isValidated); S3cell.setCellStyle(stborder); } //HTC_____________________added 2016 if (conn.rs.getInt(subpartnera + ".HTC") == 1) { counterHTC1++; XSSFRow rw2S4 = shet4.createRow(counterHTC1); int facilno = 0; for (int b = 0; b < arrayDetails.length; b++) { XSSFCell S3cell = rw2S4.createCell(facilno); if (b < 3) { S3cell.setCellValue(arrayDetails[b]); } else { S3cell.setCellValue(Integer.parseInt(arrayDetails[b])); } S3cell.setCellStyle(stborder); facilno++; } String pos; int cellpos = 0; for (int i = 1; i <= 16; i++) { if (i != 4) { cellpos++; XSSFCell S4cell = rw2S4.createCell(cellpos + 6); pos = "0" + i; if (i >= 10) { pos = "" + i; } S4cell.setCellValue(conn.rs.getInt("moh731.HV01" + pos)); S4cell.setCellStyle(stborder); } } isValidated = conn.rs.getString("isValidated"); if (isValidated.equals("1")) { isValidated = "Yes"; } else { isValidated = "No"; } XSSFCell S4cell = rw2S4.createCell(22); S4cell.setCellValue(isValidated); S4cell.setCellStyle(stborder); } System.out.println("counter : " + counter); } if (conn.st != null) { conn.st.close(); } if (conn.st1 != null) { conn.st1.close(); } // if(conn.st2!=null){conn.st2.close();} if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } // if(conn.rs2!=null){conn.rs2.close();} if (conn.conn != null) { conn.conn.close(); } IdGenerator IG = new IdGenerator(); createdOn = IG.CreatedOn(); // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=MOH731_RAW_DATA_REPORT_FOR_" + year.trim() + "(" + monthName.trim() + ")_CREATED_" + createdOn.trim() + ".xlsx"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }