Example usage for org.apache.poi.ss.usermodel CellStyle setBorderTop

List of usage examples for org.apache.poi.ss.usermodel CellStyle setBorderTop

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel CellStyle setBorderTop.

Prototype

void setBorderTop(BorderStyle border);

Source Link

Document

set the type of border to use for the top border of the cell

Usage

From source file:org.sigmah.server.endpoint.export.sigmah.spreadsheet.ExcelUtils.java

License:Open Source License

public CellStyle createBorderedStyle(Workbook wb) {
    CellStyle style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    return style;
}

From source file:org.unitime.timetable.export.XLSPrinter.java

License:Apache License

protected CellStyle getStyle(A f, boolean dashed, String format) {
    String styleId = (dashed ? "D" : "") + (f.has(F.BOLD) ? "b" : "") + (f.has(F.ITALIC) ? "i" : "")
            + (f.has(F.UNDERLINE) ? "u" : "") + (f.has(F.RIGHT) ? "R" : f.has(F.CENTER) ? "C" : "L")
            + (f.hasColor() ? "#" + Integer.toHexString(f.getColor().getRGB()) : "")
            + (format == null ? "" : "|" + format);
    CellStyle style = iStyles.get(styleId);
    if (style == null) {
        style = iWorkbook.createCellStyle();
        if (dashed) {
            style.setBorderTop(BorderStyle.DASHED);
            style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        }/*from ww  w.  j  av  a 2  s . c  om*/
        style.setAlignment(f.has(F.RIGHT) ? HorizontalAlignment.RIGHT
                : f.has(F.CENTER) ? HorizontalAlignment.CENTER : HorizontalAlignment.LEFT);
        style.setVerticalAlignment(VerticalAlignment.TOP);
        style.setFont(getFont(f.has(F.BOLD), f.has(F.ITALIC), f.has(F.UNDERLINE), f.getColor()));
        style.setWrapText(true);
        if (format != null)
            style.setDataFormat(iWorkbook.createDataFormat().getFormat(format));
        iStyles.put(styleId, style);
    }
    return style;
}

From source file:org.xianairlines.action.staffs.StaffsList.java

public void exportStaffsByColumNames() throws UnsupportedEncodingException {
    ServletOutputStream os = null;//from  ww w . j av a  2  s  .c om
    try {
        final HttpServletResponse response = (HttpServletResponse) extCtx.getResponse();
        os = response.getOutputStream();
        response.setContentType("application/x-download");
        final String newFileName = encodeFileName("??.xls");
        response.addHeader("Content-disposition", "attachment;filename=" + newFileName + ";charset=UTF-8");
        Workbook wb = new HSSFWorkbook();
        Sheet sheet1 = wb.createSheet("sheet1");
        Row row = null;
        Cell cell = null;
        CellStyle cellStyle = wb.createCellStyle();
        // ?
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        // ?
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        // Font
        Font font = wb.createFont();
        font.setFontName("");
        font.setColor(HSSFColor.BLUE.index);
        font.setItalic(true);
        font.setFontHeight((short) 300);
        row = sheet1.createRow(0);
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
        for (int i = 0; i < columNames.length; i++) {
            sheet1.setColumnWidth(i, (short) 6000);
            String[] colums = columNames[i].split(",");
            cell = row.createCell(i);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(colums[1]);
        }
        List<Staffs> list = this.getResultList();
        for (int i = 1; i <= list.size(); i = i + 1) {
            row = sheet1.createRow(i);
            row.setHeightInPoints(20);
            for (int j = 0; j < columNames.length; j++) {
                String[] colums = columNames[j].split(",");
                cell = row.createCell(j);
                cell.setCellStyle(cellStyle);
                Object value = this.getStaffsFieldValue((Staffs) list.get(i - 1), colums[0]);
                if (value == null) {
                    cell.setCellValue("");
                } else if (value instanceof java.util.Date) {
                    String cellValue = dateFormat.format((java.util.Date) value);
                    cell.setCellValue(cellValue);
                } else {
                    cell.setCellValue(value.toString());
                }

            }
        }
        wb.write(os);
        os.flush();
    } catch (Exception e) {
    } finally {
        if (os != null) {
            try {
                os.close();
            } catch (IOException e) {
            }
        }
        facesContext.responseComplete();
    }

}

From source file:org.zafritech.zidingorms.io.excel.ExcelFunctions.java

private static CellStyle createBorderedStyle(Workbook wb) {

    CellStyle style = wb.createCellStyle();
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(BorderStyle.THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(BorderStyle.THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());

    return style;
}

From source file:packtest.WorkingWithBorders.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    Sheet sheet = wb.createSheet("borders");

    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow((short) 1);

    // Create a cell and put a value in it.
    Cell cell = row.createCell((short) 1);
    cell.setCellValue(4);//w w  w. jav a 2 s.  c  o  m

    // Style the cell with borders all around.
    CellStyle style = wb.createCellStyle();
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLUE.getIndex());
    style.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    cell.setCellStyle(style);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("xssf-borders.xlsx");
    wb.write(fileOut);
    fileOut.close();
}

From source file:PPMP.ppmpreport.java

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException, SQLException {
    response.setContentType("text/html;charset=UTF-8");

    try {//from   www. j av  a 2s  .co  m
        /* TODO output your page here. You may use following sample code. */

        //create ppmp report here

        //define some variables for keeping number of columns. 
        // this should be dynamic because of the annual cumulatives depending on the selected year
        //the minimum year is 2011
        //
        int selectedyear = 2018;
        int projectstartyear = 2018;
        int minimumcolumns = 9;//this is if the year is 2011
        int currentcolumns = minimumcolumns + (selectedyear - projectstartyear);
        String selectedQTR = "Q2";

        if (request.getParameter("year") != null) {

            selectedyear = new Integer(request.getParameter("year"));

        }

        if (request.getParameter("year") != null) {

            selectedQTR = request.getParameter("quarter");

        }

        HSSFWorkbook wb = new HSSFWorkbook();

        Calendar cal = Calendar.getInstance();
        int year = cal.get(Calendar.YEAR);
        String month = String.format("%02d", cal.get(Calendar.MONTH) + 1);
        String date = String.format("%02d", cal.get(Calendar.DATE));
        String hour = String.format("%02d", cal.get(Calendar.HOUR_OF_DAY));
        String min = String.format("%02d", cal.get(Calendar.MINUTE));
        String sec = String.format("%02d", cal.get(Calendar.SECOND));

        String generationtime = "(" + year + "_" + month + "_" + date + ")_" + hour + "-" + min + "-" + sec;

        //______________________________________________________________________________________
        //______________________________________________________________________________________

        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setFontName("Times New Roman");
        font.setColor((short) 0000);
        font.setBoldweight(HSSFFont.COLOR_NORMAL);

        CellStyle style = wb.createCellStyle();
        style.setFont(font);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setWrapText(true);

        CellStyle spstyle = wb.createCellStyle();
        spstyle.setFont(font);
        spstyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        spstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        spstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        spstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        spstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        spstyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        spstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        spstyle.setWrapText(true);
        spstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        System.out.println("Blue index:" + HSSFColor.BLUE.index);

        HSSFFont font2 = wb.createFont();
        font2.setFontHeightInPoints((short) 12);
        font2.setFontName("Times New Roman");
        font2.setColor((short) 0000);
        CellStyle style2 = wb.createCellStyle();
        style2.setFont(font2);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style2.setWrapText(true);

        HSSFCellStyle stborder = wb.createCellStyle();
        stborder.setBorderTop(HSSFCellStyle.BORDER_THIN);
        stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        stborder.setBorderRight(HSSFCellStyle.BORDER_THIN);
        stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        HSSFCellStyle stylex = wb.createCellStyle();
        stylex.setFillForegroundColor(HSSFColor.WHITE.index);
        stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        stylex.setBorderTop(HSSFCellStyle.BORDER_THIN);
        stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        stylex.setBorderRight(HSSFCellStyle.BORDER_THIN);
        stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT);

        HSSFFont fontx = wb.createFont();
        fontx.setFontHeightInPoints((short) 12);
        fontx.setColor(HSSFColor.BLACK.index);
        fontx.setFontName("Times New Roman");
        fontx.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        fontx.setFontHeightInPoints((short) 16);
        stylex.setFont(fontx);
        stylex.setWrapText(true);

        HSSFSheet shet = wb.createSheet("PPMP " + selectedyear + " Report ");

        //create headers for that worksheet

        HSSFRow rw = shet.createRow(0);
        rw.setHeightInPoints(25);
        HSSFCell cl0 = rw.createCell(0);
        cl0.setCellValue("PROJECT PERFORMANCE MONITORING PLAN (PPMP)");
        cl0.setCellStyle(stylex);

        for (int a = 1; a < currentcolumns; a++) {
            HSSFCell clx = rw.createCell(a);
            clx.setCellValue("");
            clx.setCellStyle(stylex);
        }
        //merge row one

        shet.addMergedRegion(new CellRangeAddress(0, 0, 0, currentcolumns - 1));

        //firt row

        ArrayList headerone = new ArrayList();
        //headerone.add("Sub Purpose");
        headerone.add("Code");
        headerone.add("Indicator");
        headerone.add("Baseline");
        headerone.add("Year " + selectedyear + " Target");
        headerone.add(selectedyear + " Quarterly Achievements ");
        headerone.add("");
        headerone.add("");
        headerone.add("");
        headerone.add("Cumulative Year Achievements");
        //the header Cumulative Year Achievements could be in the report depending on the selected year
        //for 2011, its not expected to appear in the report

        for (int a = 0; a <= (selectedyear - projectstartyear); a++) {
            if (a == 0) {
                //do nothing 
            } else {
                headerone.add("");
            }

        }

        headerone.add("Percentage (%) Achieved vs Year " + selectedyear);

        //header two which contains quartersa dn yearly achievement

        ArrayList headertwo = new ArrayList();
        //headertwo.add("Sub Purpose");
        headertwo.add("Code");
        headertwo.add("Indicator");
        headertwo.add("Baseline");
        headertwo.add("");
        headertwo.add("Oct-Dec " + (selectedyear - 1));
        headertwo.add("Jan-Mar");
        headertwo.add("Apr-Jun");
        headertwo.add("Jul-Sep");
        headertwo.add(selectedyear);
        //the header Cumulative Year Achievements could be in the report depending on the selected year
        //for 2011, its not expected to appear in the report

        for (int a = 0; a <= (selectedyear - projectstartyear); a++) {
            if (a == 0) {
                //do nothing 
            } else {
                headertwo.add(selectedyear - a);
                //eg 2016, 2015, 2014 ...
            }

        }

        headertwo.add("");

        //=================================================================================

        //DISPLAY HEADER   DISPLAY HEADER   DISPLAY HEADER   DISPLAY HEADER  DISPLAY HEADER

        //=================================================================================

        //display the header values for row one and two
        HSSFRow rw1 = shet.createRow(1);
        for (int a = 0; a < headerone.size(); a++) {
            HSSFCell cellx = rw1.createCell(a);

            cellx.setCellValue(headerone.get(a).toString());
            cellx.setCellStyle(style);
            shet.setColumnWidth(a, 3500);

        }

        //===================row 2=======================

        //display the header values for row one and two
        HSSFRow rw2 = shet.createRow(2);
        rw2.setHeightInPoints(35);
        for (int a = 0; a < headertwo.size(); a++) {
            HSSFCell cellx = rw2.createCell(a);

            if (headertwo.get(a).toString().startsWith("20")) {

                cellx.setCellValue(new Integer(headertwo.get(a).toString()));
            } else {
                cellx.setCellValue(headertwo.get(a).toString());
            }

            cellx.setCellStyle(style);

        }

        // shet.addMergedRegion(new CellRangeAddress(start row, end row, start column ,end column));   
        String mergingarray[] = { "1_2_0_0", "1_2_1_1", "1_2_2_2", "1_2_3_3", "1_1_4_7",
                "1_1_8_" + (8 + (selectedyear - projectstartyear)),
                "1_2_" + (8 + (selectedyear - projectstartyear) + 1) + "_"
                        + (8 + (selectedyear - projectstartyear) + 1) };

        for (int a = 0; a < mergingarray.length; a++) {

            String content[] = mergingarray[a].split("_");

            shet.addMergedRegion(new CellRangeAddress(new Integer(content[0]), new Integer(content[1]),
                    new Integer(content[2]), new Integer(content[3])));

        }

        //===================================================================================

        //DISPLAY VALUES    DISPLAY VALUES    DISPLAY VALUES   DISPLAY VALUES  DISPLAY VALUES

        //===================================================================================

        String getindicators = "select * from indicatortitles where  active='yes' and inppmp='yes' order by  tableNo,output   ";

        dbConnect conn = new dbConnect();

        conn.rs = conn.state.executeQuery(getindicators);
        int rownumber = 3;

        shet.setColumnWidth(1, 20000);
        shet.setColumnWidth(2, 2300);
        shet.setColumnWidth(0, 2100);
        shet.setColumnWidth(3, 2500);
        shet.setColumnWidth(4, 2500);
        shet.setColumnWidth(5, 2500);
        shet.setColumnWidth(6, 2500);
        shet.setColumnWidth(7, 2500);
        shet.setColumnWidth(8, 2500);
        shet.setColumnWidth(9, 2500);
        shet.setColumnWidth(10, 2500);
        shet.setColumnWidth(11, 2500);
        shet.setColumnWidth(12, 2500);
        shet.setColumnWidth(13, 2500);
        shet.setColumnWidth(14, 2500);
        ArrayList sp = new ArrayList();
        int subpurposerow = 3;
        int subpurposerowcopy = 3;

        ArrayList op = new ArrayList();
        int outputrow = 3;
        int outputrowcopy = 4;
        int colpos = 0;
        int count = 0;
        int rowgani = 1;
        while (conn.rs.next()) {
            System.out.println("SP__" + conn.rs.getString("output"));
            //merge subpartner row
            sp.add(conn.rs.getString("output"));

            if (sp.size() > 1) {
                //check if subporpose has changed
                if (!sp.get(count).toString().equals(sp.get(count - 1).toString())) {

                    subpurposerow = rownumber;

                    //===================================================subpurpose========================

                    HSSFRow rwxa = shet.createRow(rownumber);
                    rwxa.setHeightInPoints(25);
                    HSSFCell cl01 = rwxa.createCell(colpos);
                    cl01.setCellValue(conn.rs.getString("output"));
                    cl01.setCellStyle(spstyle);

                    subpurposerowcopy = subpurposerow;
                    //should merge entire row
                    shet.addMergedRegion(
                            new CellRangeAddress(subpurposerow, subpurposerow, 0, selectedyear - 2009));

                    rownumber++;
                }

            }

            else {

                subpurposerow = rownumber;

                //===================================================subpurpose========================

                HSSFRow rwxa = shet.createRow(rownumber);
                rwxa.setHeightInPoints(25);
                HSSFCell cl01 = rwxa.createCell(colpos);
                cl01.setCellValue(conn.rs.getString("output"));
                cl01.setCellStyle(spstyle);

                subpurposerowcopy = subpurposerow;
                //should merge entire row
                shet.addMergedRegion(
                        new CellRangeAddress(subpurposerow, subpurposerow, 0, selectedyear - 2009));

                rownumber++;

            }
            String outputval = "";

            if (conn.rs.getString("output") != null) {
                outputval = conn.rs.getString("output");
            }

            //merge ouput rows
            op.add(outputval);
            if (op.size() > 1) {
                //check if out has changed
                if (!op.get(count).toString().equals(op.get(count - 1).toString())) {

                    outputrow = rownumber;

                    //should merge
                    shet.addMergedRegion(new CellRangeAddress(outputrowcopy, outputrow - 1, 0, 0));
                    outputrowcopy = outputrow;
                    System.out.println(" Comparison :" + op.get(count).toString() + "  %%% "
                            + op.get(count - 1).toString());

                }

            }

            //now output the first part of the report

            HSSFRow rwx = shet.createRow(rownumber);

            //====================================================output============================= 
            HSSFCell cl02 = rwx.createCell(colpos);
            //  cl02.setCellValue(conn.rs.getString("output"));

            String tn = conn.rs.getString("tableno");

            if (!tn.contains(".")) {
                if (isNumeric(tn)) {
                    cl02.setCellValue(new Integer(tn));
                } else {
                    cl02.setCellValue(tn);

                }

            } else {
                cl02.setCellValue(tn);
            }
            cl02.setCellStyle(style2);

            colpos++;

            //===================================================indicators===========================
            HSSFCell cl03 = rwx.createCell(colpos);
            cl03.setCellValue(conn.rs.getString("title"));
            cl03.setCellStyle(style2);
            colpos++;
            //====================================================baseline=============================
            HSSFCell cl04 = rwx.createCell(colpos);
            cl04.setCellValue(conn.rs.getString("totalbaseline"));
            cl04.setCellStyle(style2);
            colpos++;
            //====================================================targets=================================
            String gettargets = " select sum(target_combined) as target from yearly_targets where indicator_id='"
                    + conn.rs.getString("titleID") + "' and year='" + selectedyear + "' ";

            //for percent indicators, get avg
            if (conn.rs.getInt("percentage") == 1) {

                gettargets = "select avg(target_combined) as target from yearly_targets where indicator_id='"
                        + conn.rs.getString("titleID") + "'  and year='" + selectedyear + "' ";

            }

            HSSFCell cl05 = rwx.createCell(colpos);
            colpos++;
            int annualtarget = 1;

            conn.rs1 = conn.state1.executeQuery(gettargets);
            if (conn.rs1.next()) {
                //set the target
                cl05.setCellValue(conn.rs1.getInt("target"));
                if (conn.rs.getInt("percentage") == 1) {
                    if (conn.rs1.getInt("target") < 200) {
                        cl05.setCellValue(conn.rs1.getInt("target") + "%");

                    } else {
                        cl05.setCellValue(conn.rs1.getInt("target"));
                    }
                } else {
                    cl05.setCellValue(conn.rs1.getInt("target"));
                }

                cl05.setCellStyle(style2);
                if (conn.rs1.getString("target") != null) {
                    if (!conn.rs1.getString("target").equals("")) {

                        annualtarget = conn.rs1.getInt("target");

                    }
                }

            }

            //===========================================current year values====================================

            String ispercent = "";

            int highestvalue = 0;
            String getdata = " select sum(case  when  reportingPeriod='Q1' then totalAchieved end) as Q1, sum(case  when  reportingPeriod='Q2' then totalAchieved end) as Q2, sum(case  when  reportingPeriod='Q3' then totalAchieved end) as Q3, sum(case  when  reportingPeriod='Q4' then totalAchieved end) as Q4  where titleID='"
                    + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear
                    + "' group by titleID ";

            //for percent indicators, get avg
            if (conn.rs.getInt("percentage") == 1) {
                ispercent = "%";
                if (conn.rs.getString("tableIdentifier").equals("1")) {
                    //by gender and thus separate columns
                    if (selectedyear >= 2018) {
                        getdata = " select  ROUND((sum(case  when  reportingPeriod='Q1' then ((men_numerator + women_numerator)) end)/sum(case  when  reportingPeriod='Q1' then ((men_denominator + women_denominator)) end))*100 ) as Q1, ROUND((sum(case  when  reportingPeriod='Q2' then ((men_numerator + women_numerator)) end)/sum(case  when  reportingPeriod='Q2' then ((men_denominator + women_denominator)) end))*100 ) as Q2, ROUND((sum(case  when  reportingPeriod='Q3' then ((men_numerator + women_numerator)) end)/sum(case  when  reportingPeriod='Q3' then ((men_denominator + women_denominator)) end))*100 ) as Q3, ROUND((sum(case  when  reportingPeriod='Q4' then ((men_numerator + women_numerator)) end)/sum(case  when  reportingPeriod='Q4' then ((men_denominator + women_denominator)) end))*100 ) as Q4 from indicatorachieved   where titleID='"
                                + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear
                                + "' group by titleID ";
                        System.out.println("@@" + getdata);
                    }

                    else {
                        getdata = " select  ROUND(AVG(case  when  reportingPeriod='Q1' then ((menAchieved + womenAchieved)/2) end)) as Q1, ROUND(AVG(case  when  reportingPeriod='Q2' then ((menAchieved + womenAchieved)/2) end)) as Q2, ROUND(AVG(case  when  reportingPeriod='Q3' then ((menAchieved + womenAchieved)/2) end)) as Q3, ROUND(AVG(case  when  reportingPeriod='Q4' then ((menAchieved + womenAchieved)/2) end)) as Q4 from indicatorachieved   where titleID='"
                                + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear
                                + "' group by titleID ";

                    }
                }

                else {
                    //combined i.e male and female
                    if (selectedyear >= 2018) {
                        getdata = "select ROUND((SUM(case  when  reportingPeriod='Q1' then numerator end)/SUM(case  when  reportingPeriod='Q1' then denominator end))*100) as Q1  , ROUND((SUM(case  when  reportingPeriod='Q2' then numerator end)/SUM(case  when  reportingPeriod='Q2' then denominator end))*100) as Q2, ROUND((SUM(case  when  reportingPeriod='Q3' then numerator end)/SUM(case  when  reportingPeriod='Q3' then denominator end))*100) as Q3, ROUND((SUM(case  when  reportingPeriod='Q4' then numerator end)/SUM(case  when  reportingPeriod='Q4' then denominator end))*100) as Q4   from indicatorachievedcombined    where titleID='"
                                + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear
                                + "' group by titleID ";
                    } else {
                        //use old way of averages
                        getdata = " select ROUND(AVG(case  when  reportingPeriod='Q1' then totalAchieved end)) as Q1,ROUND(AVG(case  when  reportingPeriod='Q2' then totalAchieved end)) as Q2, ROUND(AVG(case  when  reportingPeriod='Q3' then totalAchieved end)) as Q3, ROUND(AVG(case  when  reportingPeriod='Q4' then totalAchieved end)) as Q4  from indicatorachievedcombined    where titleID='"
                                + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear
                                + "' group by titleID ";

                    }
                    System.out.println("@@" + getdata);

                }
            }
            //non percentages
            else {
                //if 
                if (conn.rs.getString("tableIdentifier").equals("1")) {
                    //by gender and thus separate columns
                    getdata = " select  sum(case  when  reportingPeriod='Q1' then (menAchieved + womenAchieved) end) as Q1,sum(case  when  reportingPeriod='Q2' then (menAchieved + womenAchieved) end) as Q2, sum(case  when  reportingPeriod='Q3' then (menAchieved + womenAchieved) end) as Q3, sum(case  when  reportingPeriod='Q4' then (menAchieved + womenAchieved) end) as Q4 from indicatorachieved  where titleID='"
                            + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear
                            + "' group by titleID ";

                } else {

                    getdata = " select sum(case  when  reportingPeriod='Q1' then totalAchieved end) as Q1, sum(case  when  reportingPeriod='Q2' then totalAchieved end) as Q2, sum(case  when  reportingPeriod='Q3' then totalAchieved end) as Q3, sum(case  when  reportingPeriod='Q4' then totalAchieved end) as Q4 from indicatorachievedcombined  where titleID='"
                            + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear
                            + "' group by titleID ";

                }

            }

            String Q1 = "";
            String Q2 = "";
            String Q3 = "";
            String Q4 = "";

            conn.rs1 = conn.state1.executeQuery(getdata);

            //Q1

            HSSFCell clQ1 = rwx.createCell(colpos);
            colpos++;
            HSSFCell clQ2 = rwx.createCell(colpos);
            colpos++;
            HSSFCell clQ3 = rwx.createCell(colpos);
            colpos++;
            HSSFCell clQ4 = rwx.createCell(colpos);
            colpos++;

            if (conn.rs1.next()) {

                if (conn.rs1.getString("Q1") != null) {
                    if (!conn.rs1.getString("Q1").equals("")) {
                        highestvalue = conn.rs1.getInt("Q1");
                        if (!ispercent.equals("")) {

                            clQ1.setCellValue(conn.rs1.getInt("Q1") + ispercent);

                        } else {
                            clQ1.setCellValue(conn.rs1.getInt("Q1"));
                        }

                    }
                }

                if (conn.rs1.getString("Q2") != null) {
                    if (!conn.rs1.getString("Q2").equals("")) {
                        if (conn.rs1.getInt("Q2") > highestvalue) {
                            highestvalue = conn.rs1.getInt("Q2");
                        }
                        if (!ispercent.equals("")) {
                            clQ2.setCellValue(conn.rs1.getInt("Q2") + ispercent);

                        } else {
                            clQ2.setCellValue(conn.rs1.getInt("Q2"));
                        }

                    }
                }

                if (conn.rs1.getString("Q3") != null) {
                    if (!conn.rs1.getString("Q3").equals("")) {

                        if (conn.rs1.getInt("Q3") > highestvalue) {
                            highestvalue = conn.rs1.getInt("Q3");
                        }
                        if (!ispercent.equals("")) {
                            clQ3.setCellValue(conn.rs1.getInt("Q3") + ispercent);
                        } else {
                            clQ3.setCellValue(conn.rs1.getInt("Q3"));
                        }

                    }
                }

                if (conn.rs1.getString("Q4") != null) {
                    if (!conn.rs1.getString("Q4").equals("")) {
                        if (conn.rs1.getInt("Q4") > highestvalue) {
                            highestvalue = conn.rs1.getInt("Q4");
                        }
                        if (!ispercent.equals("")) {
                            clQ4.setCellValue(conn.rs1.getInt("Q4") + ispercent);
                        } else {
                            clQ4.setCellValue(conn.rs1.getInt("Q4"));
                        }

                    }
                }

            }

            clQ2.setCellStyle(style2);
            clQ1.setCellStyle(style2);
            clQ3.setCellStyle(style2);
            clQ4.setCellStyle(style2);

            //====================================Annual figures=======================  
            String percentageachievement = "";
            String achievednonpercent = "No target / achieved value";
            int curcol = colpos;
            String annualispercent = "";

            int currentyearvalue = 0;
            int currentyearhighestqtr = 0;
            for (int yearval = selectedyear; yearval >= projectstartyear; yearval--) {

                HSSFCell clx = rwx.createCell(curcol);

                // System.out.println("******"+curcol);
                //separate cumulates + average with the rest
                if (conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Cumulative")
                        || conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Average")) {
                    String qry = "";

                    if (conn.rs.getString("percentage").equals("1"))

                    {
                        annualispercent = "%";
                        if (conn.rs.getString("tableidentifier").equals("2")) {
                            //no gender thus its combined 
                            if (yearval >= 2018) {

                                qry = " select ROUND((SUM(case  when  financialYear='" + yearval
                                        + "' then numerator end)/SUM(case  when   financialYear='" + yearval
                                        + "' then denominator end))*100)  as y" + yearval
                                        + " from indicatorachievedcombined where financialyear='" + yearval
                                        + "' and titleID='" + conn.rs.getString("titleID")
                                        + "' group by titleID";

                            } else {
                                qry = " select ROUND(AVG(totalAchieved))  as y" + yearval
                                        + " from indicatorachievedcombined where financialyear='" + yearval
                                        + "' and titleID='" + conn.rs.getString("titleID")
                                        + "' group by titleID";

                            }

                        } else {

                            if (yearval >= 2018) {

                                qry = " select ROUND((sum(case  when  financialYear='" + yearval
                                        + "' then ((men_numerator + women_numerator)) end)/sum(case  when  financialYear='"
                                        + yearval
                                        + "' then ((men_denominator + women_denominator)) end))*100)  as y"
                                        + yearval + " from indicatorachieved where financialyear='" + yearval
                                        + "' and titleID='" + conn.rs.getString("titleID")
                                        + "' group by titleID";
                            } else {

                                qry = " select ROUND(AVG((menAchieved + womenAchieved)/2))  as y" + yearval
                                        + " from indicatorachieved where financialyear='" + yearval
                                        + "' and titleID='" + conn.rs.getString("titleID")
                                        + "' group by titleID";

                            }

                        }

                    }

                    //non percents
                    else {
                        //for cumulatives
                        //check tabletype
                        //1 is by gender

                        //_____________CUMULATIVES______________

                        if (conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Cumulative")) {

                            if (conn.rs.getString("tableIdentifier").equals("1")) {

                                qry = " select sum((menAchieved + womenAchieved)) as y" + yearval
                                        + " from indicatorachieved where financialyear='" + yearval
                                        + "' and titleID='" + conn.rs.getString("titleID")
                                        + "' group by titleID";

                            }

                            else {

                                qry = " select sum(totalAchieved) as y" + yearval
                                        + " from indicatorachievedcombined where financialyear='" + yearval
                                        + "' and titleID='" + conn.rs.getString("titleID")
                                        + "' group by titleID";

                            } //end of else of table identifier

                        } //end of sum/cumulatives
                        else {

                            System.out.println("failed:" + conn.rs.getString("cumulative_chooser") + " *** "
                                    + conn.rs.getString("title"));

                        }

                    } //end of else of non percents

                    System.out.println("@ annual query" + qry);

                    conn.rs1 = conn.state1.executeQuery(qry);

                    if (conn.rs1.next()) {

                        // System.out.println("__"+conn.rs1.getString(1));

                        if (conn.rs1.getString(1) != null) {
                            if (!conn.rs1.getString(1).equals("")) {
                                if (annualispercent.equals("")) {

                                    clx.setCellValue(conn.rs1.getInt(1));
                                    //achieved nonpercentage
                                    if (yearval == selectedyear) {

                                        if (annualtarget > 1) {
                                            achievednonpercent = ""
                                                    + (int) (((double) conn.rs1.getDouble(1) * 100)
                                                            / (double) annualtarget)
                                                    + "%";

                                            //System.out.println("For id "+conn.rs.getString("titleID")+" "+achievednonpercent);

                                        }
                                    }

                                } else {
                                    clx.setCellValue(conn.rs1.getInt(1) + annualispercent);
                                    //do this for the cureent year
                                    if (yearval == selectedyear) {
                                        percentageachievement = conn.rs1.getInt(1) + annualispercent;

                                    }
                                }

                            }
                        }
                    }
                    clx.setCellStyle(style2);

                } //end of cumulatives and percentages

                else if (conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Highest")) {
                    String qry = "";

                    if (conn.rs.getString("tableIdentifier").equals("1")) {
                        //by gender and thus separate columns
                        qry = " select  sum(case  when  reportingPeriod='Q1' then (menAchieved + womenAchieved) end) as Q1,sum(case  when  reportingPeriod='Q2' then (menAchieved + womenAchieved) end) as Q2, sum(case  when  reportingPeriod='Q3' then (menAchieved + womenAchieved) end) as Q3, sum(case  when  reportingPeriod='Q4' then (menAchieved + womenAchieved) end) as Q4 from indicatorachieved  where titleID='"
                                + conn.rs.getString("titleID") + "' and financialyear='" + yearval
                                + "' group by titleID ";

                    } else {

                        qry = " select sum(case  when  reportingPeriod='Q1' then totalAchieved end) as Q1, sum(case  when  reportingPeriod='Q2' then totalAchieved end) as Q2, sum(case  when  reportingPeriod='Q3' then totalAchieved end) as Q3, sum(case  when  reportingPeriod='Q4' then totalAchieved end) as Q4 from indicatorachievedcombined  where titleID='"
                                + conn.rs.getString("titleID") + "' and financialyear='" + yearval
                                + "' group by titleID ";

                    }
                    int highestqtr = 0;
                    //excecute query
                    conn.rs1 = conn.state1.executeQuery(qry);
                    while (conn.rs1.next()) {
                        //

                        if (conn.rs1.getString("Q1") != null) {
                            if (!conn.rs1.getString("Q1").equals("")) {
                                if (conn.rs1.getInt("Q1") > highestqtr) {
                                    highestqtr = conn.rs1.getInt("Q1");
                                }
                            }
                        } else if (conn.rs1.getString("Q2") != null) {
                            if (!conn.rs1.getString("Q2").equals("")) {
                                if (conn.rs1.getInt("Q2") > highestqtr) {
                                    highestqtr = conn.rs1.getInt("Q2");
                                }
                            }
                        }

                        else if (conn.rs1.getString("Q3") != null) {
                            if (!conn.rs1.getString("Q3").equals("")) {
                                if (conn.rs1.getInt("Q3") > highestqtr) {
                                    highestqtr = conn.rs1.getInt("Q3");
                                }
                            }
                        }

                        else if (conn.rs1.getString("Q4") != null) {
                            if (!conn.rs1.getString("Q4").equals("")) {
                                if (conn.rs1.getInt("Q4") > highestqtr) {
                                    highestqtr = conn.rs1.getInt("Q4");
                                }
                            }
                        }

                    }
                    if (highestqtr > 0) {
                        clx.setCellValue(highestqtr);

                    } else {
                        clx.setCellValue("");
                    }

                    //achieved nonpercentage
                    if (yearval == selectedyear) {

                        if (annualtarget != 1) {
                            achievednonpercent = "" + (int) highestqtr * 100 / (int) annualtarget + "%";

                            System.out.println(highestqtr + " / " + annualtarget + "___"
                                    + (int) highestqtr * 100 / (int) (annualtarget) + "%");
                        }
                    }

                    clx.setCellStyle(style2);

                } //   end of highest 

                else if (conn.rs.getString("cumulative_chooser").equalsIgnoreCase("OLMIS")
                        || conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Last Reported")) {

                    String qry = "";
                    //get data for the last input quarter
                    if (yearval == selectedyear) {
                        //get data for that quarter

                        if (conn.rs.getString("tableidentifier").equals("2")) {
                            //no gender
                            qry = " select sum((totalAchieved)) as y" + yearval
                                    + " from indicatorachievedcombined where financialyear='" + yearval
                                    + "' and reportingPeriod='" + selectedQTR + "' and  titleID='"
                                    + conn.rs.getString("titleID") + "' group by titleID";

                        } else {

                            qry = " select sum((menAchieved + womenAchieved)) as y" + yearval
                                    + " from indicatorachieved where financialyear='" + yearval
                                    + "' and reportingPeriod='" + selectedQTR + "' and  titleID='"
                                    + conn.rs.getString("titleID") + "' group by titleID";

                        }

                    }

                    else {

                        if (conn.rs.getString("tableidentifier").equals("2")) {
                            //no gender
                            qry = " select sum((totalAchieved)) as y" + yearval
                                    + " from indicatorachievedcombined where financialyear='" + yearval
                                    + "' and reportingPeriod='Q4' and  titleID='" + conn.rs.getString("titleID")
                                    + "' group by titleID";

                        } else {
                            qry = " select sum((menAchieved + womenAchieved)) as y" + yearval
                                    + " from indicatorachieved where financialyear='" + yearval
                                    + "' and reportingPeriod='Q4' and  titleID='" + conn.rs.getString("titleID")
                                    + "' group by titleID";

                        }

                    }

                    //execute the query  
                    conn.rs1 = conn.state1.executeQuery(qry);

                    if (conn.rs1.next()) {
                        // System.out.println("__"+conn.rs1.getString(1));

                        if (conn.rs1.getString(1) != null) {
                            if (!conn.rs1.getString(1).equals("")) {

                                clx.setCellValue(conn.rs1.getInt(1));
                                //this is for percentage purpose
                                if (selectedyear == yearval) {
                                    currentyearvalue = conn.rs1.getInt(1);
                                }

                            }
                        }

                    }

                    //achieved nonpercentage
                    if (yearval == selectedyear) {

                        if (annualtarget != 1 && annualtarget != 0) {
                            achievednonpercent = "" + (((int) currentyearvalue * 100) / (int) annualtarget)
                                    + "%";
                        }
                    }

                    clx.setCellStyle(style2);

                } //end of olmis and last reported indicators

                curcol++;

            } //end of for loop
            HSSFCell clx = rwx.createCell(curcol);

            if (conn.rs.getInt("percentage") == 1) {

                clx.setCellValue(percentageachievement);

            } else {
                clx.setCellValue(achievednonpercent);

            }

            clx.setCellStyle(style2);
            rwx.setHeightInPoints(42);

            rownumber++;
            count++;
            colpos = 0;
        }

        for (int e = 0; e < 13; e++) {
            //shet.getRow(rowgani).autoSizeColumn(e,true);
        }
        //shet.autoSizeColumn(1,false);

        //Made my life veery simple...
        shet.setDisplayGridlines(false);
        shet.createFreezePane(2, 3);

        if (conn.rs != null) {
            conn.rs.close();
        }
        if (conn.rs1 != null) {
            conn.rs1.close();
        }
        if (conn.state1 != null) {
            conn.state1.close();
        }
        if (conn.state2 != null) {
            conn.state2.close();
        }

        // write it as an excel attachment
        ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
        wb.write(outByteStream);
        byte[] outArray = outByteStream.toByteArray();
        response.setContentType("application/ms-excel");
        response.setContentLength(outArray.length);
        response.setHeader("Expires:", "0"); // eliminates browser caching
        response.setHeader("Content-Disposition", "attachment; filename=PPMPREPORT_" + selectedyear + "_"
                + selectedQTR + "_gen_on_" + generationtime + ".xls");
        OutputStream outStream = response.getOutputStream();
        outStream.write(outArray);
        outStream.flush();

    } finally {

    }
}

From source file:PPMP.ppmpreport_2.java

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException, SQLException {
    response.setContentType("text/html;charset=UTF-8");

    try {//from  w w  w .  ja  v  a 2  s.c om
        /* TODO output your page here. You may use following sample code. */

        //create ppmp report here

        //define some variables for keeping number of columns. 
        // this should be dynamic because of the annual cumulatives depending on the selected year
        //the minimum year is 2011
        //
        int selectedyear = 2016;
        int projectstartyear = 2011;
        int minimumcolumns = 11;//this is if the year is 2011
        int currentcolumns = minimumcolumns + (selectedyear - projectstartyear);
        String selectedQTR = "Q2";

        if (request.getParameter("year") != null) {

            selectedyear = new Integer(request.getParameter("year"));

        }

        if (request.getParameter("year") != null) {

            selectedQTR = request.getParameter("quarter");

        }

        HSSFWorkbook wb = new HSSFWorkbook();

        Calendar cal = Calendar.getInstance();
        int year = cal.get(Calendar.YEAR);
        String month = String.format("%02d", cal.get(Calendar.MONTH) + 1);
        String date = String.format("%02d", cal.get(Calendar.DATE));
        String hour = String.format("%02d", cal.get(Calendar.HOUR_OF_DAY));
        String min = String.format("%02d", cal.get(Calendar.MINUTE));
        String sec = String.format("%02d", cal.get(Calendar.SECOND));

        String generationtime = "(" + year + "_" + month + "_" + date + ")_" + hour + "-" + min + "-" + sec;

        //______________________________________________________________________________________
        //______________________________________________________________________________________

        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setFontName("Arial Narrow");
        font.setColor((short) 0000);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        CellStyle style = wb.createCellStyle();
        style.setFont(font);
        style.setBorderTop(HSSFCellStyle.BORDER_THICK);
        style.setBorderBottom(HSSFCellStyle.BORDER_THICK);
        style.setBorderLeft(HSSFCellStyle.BORDER_THICK);
        style.setBorderRight(HSSFCellStyle.BORDER_THICK);
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setWrapText(true);

        CellStyle spstyle = wb.createCellStyle();
        spstyle.setFont(font);
        spstyle.setBorderTop(HSSFCellStyle.BORDER_THICK);
        spstyle.setBorderBottom(HSSFCellStyle.BORDER_THICK);
        spstyle.setBorderLeft(HSSFCellStyle.BORDER_THICK);
        spstyle.setBorderRight(HSSFCellStyle.BORDER_THICK);
        spstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        spstyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
        spstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        spstyle.setWrapText(true);
        System.out.println("Blue index:" + HSSFColor.BLUE.index);

        HSSFFont font2 = wb.createFont();
        font2.setFontName("Cambria");
        font2.setColor((short) 0000);
        CellStyle style2 = wb.createCellStyle();
        style2.setFont(font2);
        style2.setBorderTop(HSSFCellStyle.BORDER_THICK);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THICK);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THICK);
        style2.setBorderRight(HSSFCellStyle.BORDER_THICK);
        style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style2.setWrapText(true);

        HSSFCellStyle stborder = wb.createCellStyle();
        stborder.setBorderTop(HSSFCellStyle.BORDER_THICK);
        stborder.setBorderBottom(HSSFCellStyle.BORDER_THICK);
        stborder.setBorderLeft(HSSFCellStyle.BORDER_THICK);
        stborder.setBorderRight(HSSFCellStyle.BORDER_THICK);
        stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        HSSFCellStyle stylex = wb.createCellStyle();
        stylex.setFillForegroundColor(HSSFColor.WHITE.index);
        stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        stylex.setBorderTop(HSSFCellStyle.BORDER_THICK);
        stylex.setBorderBottom(HSSFCellStyle.BORDER_THICK);
        stylex.setBorderLeft(HSSFCellStyle.BORDER_THICK);
        stylex.setBorderRight(HSSFCellStyle.BORDER_THICK);
        stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT);

        HSSFFont fontx = wb.createFont();
        fontx.setColor(HSSFColor.BLACK.index);
        fontx.setFontName("Arial Narrow");
        fontx.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        fontx.setFontHeightInPoints((short) 16);
        stylex.setFont(fontx);
        stylex.setWrapText(true);

        HSSFSheet shet = wb.createSheet("PPMP " + selectedyear + " Report ");

        //create headers for that worksheet

        HSSFRow rw = shet.createRow(0);
        rw.setHeightInPoints(25);
        HSSFCell cl0 = rw.createCell(0);
        cl0.setCellValue("PROJECT PERFORMANCE MONITORING PLAN (PPMP)");
        cl0.setCellStyle(stylex);

        for (int a = 1; a < currentcolumns; a++) {
            HSSFCell clx = rw.createCell(a);
            clx.setCellValue("");
            clx.setCellStyle(stylex);
        }
        //merge row one

        shet.addMergedRegion(new CellRangeAddress(0, 0, 0, currentcolumns - 1));

        //firt row

        ArrayList headerone = new ArrayList();
        //headerone.add("Sub Purpose");
        headerone.add("Output");
        headerone.add("Indicators");
        headerone.add("Baseline");
        headerone.add("Year " + selectedyear + " Target");
        headerone.add(selectedyear + " Quarterly Achievements ");
        headerone.add("");
        headerone.add("");
        headerone.add("");
        headerone.add("Cumulative Year Achievements");
        //the header Cumulative Year Achievements could be in the report depending on the selected year
        //for 2011, its not expected to appear in the report

        for (int a = 0; a <= (selectedyear - projectstartyear); a++) {
            if (a == 0) {
                //do nothing 
            } else {
                headerone.add("");
            }

        }

        headerone.add("Percentage (%) Achieved vs Year " + selectedyear);

        //header two which contains quartersa dn yearly achievement

        ArrayList headertwo = new ArrayList();
        //headertwo.add("Sub Purpose");
        headertwo.add("Output");
        headertwo.add("Indicators");
        headertwo.add("Baseline");
        headertwo.add("");
        headertwo.add("Oct-Dec " + (selectedyear - 1));
        headertwo.add("Jan-Mar");
        headertwo.add("Apr-Jun");
        headertwo.add("Jul-Sep");
        headertwo.add(selectedyear);
        //the header Cumulative Year Achievements could be in the report depending on the selected year
        //for 2011, its not expected to appear in the report

        for (int a = 0; a <= (selectedyear - projectstartyear); a++) {
            if (a == 0) {
                //do nothing 
            } else {
                headertwo.add(selectedyear - a);
                //eg 2016, 2015, 2014 ...
            }

        }

        headertwo.add("");

        //=================================================================================

        //DISPLAY HEADER   DISPLAY HEADER   DISPLAY HEADER   DISPLAY HEADER  DISPLAY HEADER

        //=================================================================================

        //display the header values for row one and two
        HSSFRow rw1 = shet.createRow(1);
        for (int a = 0; a < headerone.size(); a++) {
            HSSFCell cellx = rw1.createCell(a);

            cellx.setCellValue(headerone.get(a).toString());
            cellx.setCellStyle(style);
            shet.setColumnWidth(a, 3500);

        }

        //===================row 2=======================

        //display the header values for row one and two
        HSSFRow rw2 = shet.createRow(2);
        rw2.setHeightInPoints(35);
        for (int a = 0; a < headertwo.size(); a++) {
            HSSFCell cellx = rw2.createCell(a);

            if (headertwo.get(a).toString().startsWith("20")) {

                cellx.setCellValue(new Integer(headertwo.get(a).toString()));
            } else {
                cellx.setCellValue(headertwo.get(a).toString());
            }

            cellx.setCellStyle(style);

        }

        // shet.addMergedRegion(new CellRangeAddress(start row, end row, start column ,end column));   
        String mergingarray[] = { "1_2_0_0", "1_2_1_1", "1_2_2_2", "1_2_3_3", "1_1_4_7",
                "1_1_8_" + (8 + (selectedyear - projectstartyear)),
                "1_2_" + (8 + (selectedyear - projectstartyear) + 1) + "_"
                        + (8 + (selectedyear - projectstartyear) + 1) };

        for (int a = 0; a < mergingarray.length; a++) {

            String content[] = mergingarray[a].split("_");

            shet.addMergedRegion(new CellRangeAddress(new Integer(content[0]), new Integer(content[1]),
                    new Integer(content[2]), new Integer(content[3])));

        }

        //===================================================================================

        //DISPLAY VALUES    DISPLAY VALUES    DISPLAY VALUES   DISPLAY VALUES  DISPLAY VALUES

        //===================================================================================

        String getindicators = "select * from indicatortitles where  active='yes' order by subpurpose, output , title ";

        dbConnect conn = new dbConnect();

        conn.rs = conn.state.executeQuery(getindicators);
        int rownumber = 3;

        shet.setColumnWidth(1, 14000);
        shet.setColumnWidth(2, 2300);
        shet.setColumnWidth(0, 7000);
        shet.setColumnWidth(3, 2300);
        shet.setColumnWidth(4, 2300);
        shet.setColumnWidth(5, 2300);
        shet.setColumnWidth(6, 2300);
        shet.setColumnWidth(7, 2300);
        shet.setColumnWidth(8, 2300);
        shet.setColumnWidth(9, 2300);
        shet.setColumnWidth(10, 2300);
        shet.setColumnWidth(11, 2300);
        shet.setColumnWidth(12, 2300);
        shet.setColumnWidth(13, 2300);
        shet.setColumnWidth(14, 2300);
        ArrayList sp = new ArrayList();
        int subpurposerow = 3;
        int subpurposerowcopy = 3;

        ArrayList op = new ArrayList();
        int outputrow = 3;
        int outputrowcopy = 4;
        int colpos = 0;
        int count = 0;

        while (conn.rs.next()) {
            System.out.println("SP__" + conn.rs.getString("subpurpose"));
            //merge subpartner row
            sp.add(conn.rs.getString("subpurpose"));
            if (sp.size() > 1) {
                //check if subporpose has changed
                if (!sp.get(count).toString().equals(sp.get(count - 1).toString())) {

                    subpurposerow = rownumber;

                    //===================================================subpurpose========================

                    HSSFRow rwxa = shet.createRow(rownumber);

                    HSSFCell cl01 = rwxa.createCell(colpos);
                    cl01.setCellValue(conn.rs.getString("subpurpose"));
                    cl01.setCellStyle(spstyle);

                    subpurposerowcopy = subpurposerow;
                    //should merge entire row
                    shet.addMergedRegion(new CellRangeAddress(subpurposerow, subpurposerow, 0, 15));

                    rownumber++;
                }

            }

            else {

                subpurposerow = rownumber;

                //===================================================subpurpose========================

                HSSFRow rwxa = shet.createRow(rownumber);

                HSSFCell cl01 = rwxa.createCell(colpos);
                cl01.setCellValue(conn.rs.getString("subpurpose"));
                cl01.setCellStyle(spstyle);

                subpurposerowcopy = subpurposerow;
                //should merge entire row
                shet.addMergedRegion(new CellRangeAddress(subpurposerow, subpurposerow, 0, 15));

                rownumber++;

            }
            String outputval = "";

            if (conn.rs.getString("output") != null) {
                outputval = conn.rs.getString("output");
            }

            //merge ouput rows
            op.add(outputval);
            if (op.size() > 1) {
                //check if out has changed
                if (!op.get(count).toString().equals(op.get(count - 1).toString())) {

                    outputrow = rownumber;

                    //should merge
                    shet.addMergedRegion(new CellRangeAddress(outputrowcopy, outputrow - 1, 0, 0));
                    outputrowcopy = outputrow;
                    System.out.println(" Comparison :" + op.get(count).toString() + "  %%% "
                            + op.get(count - 1).toString());

                }

            }

            //now output the first part of the report

            HSSFRow rwx = shet.createRow(rownumber);

            //====================================================output============================= 
            HSSFCell cl02 = rwx.createCell(colpos);
            cl02.setCellValue(conn.rs.getString("output"));
            cl02.setCellStyle(style2);

            colpos++;

            //===================================================indicators===========================
            HSSFCell cl03 = rwx.createCell(colpos);
            cl03.setCellValue(conn.rs.getString("title"));
            cl03.setCellStyle(style2);
            colpos++;
            //====================================================baseline=============================
            HSSFCell cl04 = rwx.createCell(colpos);
            cl04.setCellValue(conn.rs.getString("totalbaseline"));
            cl04.setCellStyle(style2);
            colpos++;
            //====================================================targets=================================
            String gettargets = " select sum(target_combined) as target from yearly_targets where indicator_id='"
                    + conn.rs.getString("titleID") + "' and year='" + selectedyear + "' ";

            //for percent indicators, get avg
            if (conn.rs.getInt("percentage") == 1) {

                gettargets = "select avg(target_combined) as target from yearly_targets where indicator_id='"
                        + conn.rs.getString("titleID") + "'  and year='" + selectedyear + "' ";

            }

            HSSFCell cl05 = rwx.createCell(colpos);
            colpos++;
            int annualtarget = 1;

            conn.rs1 = conn.state1.executeQuery(gettargets);
            if (conn.rs1.next()) {
                //set the target
                cl05.setCellValue(conn.rs1.getInt("target"));
                if (conn.rs.getInt("percentage") == 1) {
                    if (conn.rs1.getInt("target") < 200) {
                        cl05.setCellValue(conn.rs1.getInt("target") + "%");

                    } else {
                        cl05.setCellValue(conn.rs1.getInt("target"));
                    }
                } else {
                    cl05.setCellValue(conn.rs1.getInt("target"));
                }

                cl05.setCellStyle(style2);
                if (conn.rs1.getString("target") != null) {
                    if (!conn.rs1.getString("target").equals("")) {

                        annualtarget = conn.rs1.getInt("target");

                    }
                }

            }

            //===========================================current year values====================================

            String ispercent = "";

            int highestvalue = 0;
            String getdata = " select sum(case  when  reportingPeriod='Q1' then totalAchieved end) as Q1, sum(case  when  reportingPeriod='Q2' then totalAchieved end) as Q2, sum(case  when  reportingPeriod='Q3' then totalAchieved end) as Q3, sum(case  when  reportingPeriod='Q4' then totalAchieved end) as Q4  where titleID='"
                    + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear
                    + "' group by titleID ";

            //for percent indicators, get avg
            if (conn.rs.getInt("percentage") == 1) {
                ispercent = "%";
                if (conn.rs.getString("tableIdentifier").equals("1")) {
                    //by gender and thus separate columns

                    getdata = " select  ROUND(AVG(case  when  reportingPeriod='Q1' then ((menAchieved + womenAchieved)/2) end)) as Q1, ROUND(AVG(case  when  reportingPeriod='Q2' then ((menAchieved + womenAchieved)/2) end)) as Q2, ROUND(AVG(case  when  reportingPeriod='Q3' then ((menAchieved + womenAchieved)/2) end)) as Q3, ROUND(AVG(case  when  reportingPeriod='Q4' then ((menAchieved + womenAchieved)/2) end)) as Q4 from indicatorachieved   where titleID='"
                            + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear
                            + "' group by titleID ";
                    System.out.println("@@" + getdata);

                }

                else {
                    //combined i.e male and female
                    getdata = " select ROUND(AVG(case  when  reportingPeriod='Q1' then totalAchieved end)) as Q1,ROUND(AVG(case  when  reportingPeriod='Q2' then totalAchieved end)) as Q2, ROUND(AVG(case  when  reportingPeriod='Q3' then totalAchieved end)) as Q3, ROUND(AVG(case  when  reportingPeriod='Q4' then totalAchieved end)) as Q4  from indicatorachievedcombined    where titleID='"
                            + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear
                            + "' group by titleID ";

                    System.out.println("@@" + getdata);
                }
            }
            //non percentages
            else {
                //if 
                if (conn.rs.getString("tableIdentifier").equals("1")) {
                    //by gender and thus separate columns
                    getdata = " select  sum(case  when  reportingPeriod='Q1' then (menAchieved + womenAchieved) end) as Q1,sum(case  when  reportingPeriod='Q2' then (menAchieved + womenAchieved) end) as Q2, sum(case  when  reportingPeriod='Q3' then (menAchieved + womenAchieved) end) as Q3, sum(case  when  reportingPeriod='Q4' then (menAchieved + womenAchieved) end) as Q4 from indicatorachieved  where titleID='"
                            + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear
                            + "' group by titleID ";

                } else {

                    getdata = " select sum(case  when  reportingPeriod='Q1' then totalAchieved end) as Q1, sum(case  when  reportingPeriod='Q2' then totalAchieved end) as Q2, sum(case  when  reportingPeriod='Q3' then totalAchieved end) as Q3, sum(case  when  reportingPeriod='Q4' then totalAchieved end) as Q4 from indicatorachievedcombined  where titleID='"
                            + conn.rs.getString("titleID") + "' and financialyear='" + selectedyear
                            + "' group by titleID ";

                }

            }

            String Q1 = "";
            String Q2 = "";
            String Q3 = "";
            String Q4 = "";

            conn.rs1 = conn.state1.executeQuery(getdata);

            //Q1

            HSSFCell clQ1 = rwx.createCell(colpos);
            colpos++;
            HSSFCell clQ2 = rwx.createCell(colpos);
            colpos++;
            HSSFCell clQ3 = rwx.createCell(colpos);
            colpos++;
            HSSFCell clQ4 = rwx.createCell(colpos);
            colpos++;

            if (conn.rs1.next()) {

                if (conn.rs1.getString("Q1") != null) {
                    if (!conn.rs1.getString("Q1").equals("")) {
                        highestvalue = conn.rs1.getInt("Q1");
                        if (!ispercent.equals("")) {

                            clQ1.setCellValue(conn.rs1.getInt("Q1") + ispercent);

                        } else {
                            clQ1.setCellValue(conn.rs1.getInt("Q1"));
                        }

                    }
                }

                if (conn.rs1.getString("Q2") != null) {
                    if (!conn.rs1.getString("Q2").equals("")) {
                        if (conn.rs1.getInt("Q2") > highestvalue) {
                            highestvalue = conn.rs1.getInt("Q2");
                        }
                        if (!ispercent.equals("")) {
                            clQ2.setCellValue(conn.rs1.getInt("Q2") + ispercent);

                        } else {
                            clQ2.setCellValue(conn.rs1.getInt("Q2"));
                        }

                    }
                }

                if (conn.rs1.getString("Q3") != null) {
                    if (!conn.rs1.getString("Q3").equals("")) {

                        if (conn.rs1.getInt("Q3") > highestvalue) {
                            highestvalue = conn.rs1.getInt("Q3");
                        }
                        if (!ispercent.equals("")) {
                            clQ3.setCellValue(conn.rs1.getInt("Q3") + ispercent);
                        } else {
                            clQ3.setCellValue(conn.rs1.getInt("Q3"));
                        }

                    }
                }

                if (conn.rs1.getString("Q4") != null) {
                    if (!conn.rs1.getString("Q4").equals("")) {
                        if (conn.rs1.getInt("Q4") > highestvalue) {
                            highestvalue = conn.rs1.getInt("Q4");
                        }
                        if (!ispercent.equals("")) {
                            clQ4.setCellValue(conn.rs1.getInt("Q4") + ispercent);
                        } else {
                            clQ4.setCellValue(conn.rs1.getInt("Q4"));
                        }

                    }
                }

            }

            clQ2.setCellStyle(style2);
            clQ1.setCellStyle(style2);
            clQ3.setCellStyle(style2);
            clQ4.setCellStyle(style2);

            //====================================Annual figures=======================  
            String percentageachievement = "";
            String achievednonpercent = "No target / achieved value";
            int curcol = colpos;
            String annualispercent = "";

            int currentyearvalue = 0;
            int currentyearhighestqtr = 0;
            for (int yearval = selectedyear; yearval >= projectstartyear; yearval--) {

                HSSFCell clx = rwx.createCell(curcol);

                // System.out.println("******"+curcol);
                //separate cumulates + average with the rest
                if (conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Cumulative")
                        || conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Average")) {
                    String qry = "";

                    if (conn.rs.getString("percentage").equals("1"))

                    {
                        annualispercent = "%";
                        if (conn.rs.getString("tableidentifier").equals("2")) {
                            //no gender thus its combined 

                            qry = " select ROUND(AVG(totalAchieved))  as y" + yearval
                                    + " from indicatorachievedcombined where financialyear='" + yearval
                                    + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID";

                        } else {

                            qry = " select ROUND(AVG((menAchieved + womenAchieved)/2))  as y" + yearval
                                    + " from indicatorachieved where financialyear='" + yearval
                                    + "' and titleID='" + conn.rs.getString("titleID") + "' group by titleID";

                        }

                    }

                    //non percents
                    else {
                        //for cumulatives
                        //check tabletype
                        //1 is by gender

                        //_____________CUMULATIVES______________

                        if (conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Cumulative")) {

                            if (conn.rs.getString("tableIdentifier").equals("1")) {

                                qry = " select sum((menAchieved + womenAchieved)) as y" + yearval
                                        + " from indicatorachieved where financialyear='" + yearval
                                        + "' and titleID='" + conn.rs.getString("titleID")
                                        + "' group by titleID";

                            }

                            else {

                                qry = " select sum(totalAchieved) as y" + yearval
                                        + " from indicatorachievedcombined where financialyear='" + yearval
                                        + "' and titleID='" + conn.rs.getString("titleID")
                                        + "' group by titleID";

                            } //end of else of table identifier

                        } //end of sum/cumulatives

                    } //end of else of non percents
                    System.out.println("@" + qry);

                    conn.rs1 = conn.state1.executeQuery(qry);

                    if (conn.rs1.next()) {

                        // System.out.println("__"+conn.rs1.getString(1));

                        if (conn.rs1.getString(1) != null) {
                            if (!conn.rs1.getString(1).equals("")) {
                                if (annualispercent.equals("")) {

                                    clx.setCellValue(conn.rs1.getInt(1));
                                    //achieved nonpercentage
                                    if (yearval == selectedyear) {

                                        if (annualtarget > 1) {
                                            achievednonpercent = ""
                                                    + (int) conn.rs1.getInt(1) * 100 / (int) annualtarget + "%";
                                        }
                                    }

                                } else {
                                    clx.setCellValue(conn.rs1.getInt(1) + annualispercent);
                                    //do this for the cureent year
                                    if (yearval == selectedyear) {
                                        percentageachievement = conn.rs1.getInt(1) + annualispercent;

                                    }
                                }

                            }
                        }
                    }
                    clx.setCellStyle(style2);

                } //end of cumulatives and percentages

                else if (conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Highest")) {
                    String qry = "";

                    if (conn.rs.getString("tableIdentifier").equals("1")) {
                        //by gender and thus separate columns
                        qry = " select  sum(case  when  reportingPeriod='Q1' then (menAchieved + womenAchieved) end) as Q1,sum(case  when  reportingPeriod='Q2' then (menAchieved + womenAchieved) end) as Q2, sum(case  when  reportingPeriod='Q3' then (menAchieved + womenAchieved) end) as Q3, sum(case  when  reportingPeriod='Q4' then (menAchieved + womenAchieved) end) as Q4 from indicatorachieved  where titleID='"
                                + conn.rs.getString("titleID") + "' and financialyear='" + yearval
                                + "' group by titleID ";

                    } else {

                        qry = " select sum(case  when  reportingPeriod='Q1' then totalAchieved end) as Q1, sum(case  when  reportingPeriod='Q2' then totalAchieved end) as Q2, sum(case  when  reportingPeriod='Q3' then totalAchieved end) as Q3, sum(case  when  reportingPeriod='Q4' then totalAchieved end) as Q4 from indicatorachievedcombined  where titleID='"
                                + conn.rs.getString("titleID") + "' and financialyear='" + yearval
                                + "' group by titleID ";

                    }
                    int highestqtr = 0;
                    //excecute query
                    conn.rs1 = conn.state1.executeQuery(qry);
                    while (conn.rs1.next()) {
                        //

                        if (conn.rs1.getString("Q1") != null) {
                            if (!conn.rs1.getString("Q1").equals("")) {
                                if (conn.rs1.getInt("Q1") > highestqtr) {
                                    highestqtr = conn.rs1.getInt("Q1");
                                }
                            }
                        } else if (conn.rs1.getString("Q2") != null) {
                            if (!conn.rs1.getString("Q2").equals("")) {
                                if (conn.rs1.getInt("Q2") > highestqtr) {
                                    highestqtr = conn.rs1.getInt("Q2");
                                }
                            }
                        }

                        else if (conn.rs1.getString("Q3") != null) {
                            if (!conn.rs1.getString("Q3").equals("")) {
                                if (conn.rs1.getInt("Q3") > highestqtr) {
                                    highestqtr = conn.rs1.getInt("Q3");
                                }
                            }
                        }

                        else if (conn.rs1.getString("Q4") != null) {
                            if (!conn.rs1.getString("Q4").equals("")) {
                                if (conn.rs1.getInt("Q4") > highestqtr) {
                                    highestqtr = conn.rs1.getInt("Q4");
                                }
                            }
                        }

                    }
                    if (highestqtr > 0) {
                        clx.setCellValue(highestqtr);

                    } else {
                        clx.setCellValue("");
                    }

                    //achieved nonpercentage
                    if (yearval == selectedyear) {

                        if (annualtarget != 1) {
                            achievednonpercent = "" + (int) highestqtr * 100 / (int) annualtarget + "%";

                            System.out.println(highestqtr + " / " + annualtarget + "___"
                                    + (int) highestqtr * 100 / (int) (annualtarget) + "%");
                        }
                    }

                    clx.setCellStyle(style2);

                } //   end of highest 

                else if (conn.rs.getString("cumulative_chooser").equalsIgnoreCase("OLMIS")
                        || conn.rs.getString("cumulative_chooser").equalsIgnoreCase("Last Reported")) {

                    String qry = "";
                    //get data for the last input quarter
                    if (yearval == selectedyear) {
                        //get data for that quarter

                        if (conn.rs.getString("tableidentifier").equals("2")) {
                            //no gender
                            qry = " select sum((totalAchieved)) as y" + yearval
                                    + " from indicatorachievedcombined where financialyear='" + yearval
                                    + "' and reportingPeriod='" + selectedQTR + "' and  titleID='"
                                    + conn.rs.getString("titleID") + "' group by titleID";

                        } else {

                            qry = " select sum((menAchieved + womenAchieved)) as y" + yearval
                                    + " from indicatorachieved where financialyear='" + yearval
                                    + "' and reportingPeriod='" + selectedQTR + "' and  titleID='"
                                    + conn.rs.getString("titleID") + "' group by titleID";

                        }

                    }

                    else {

                        if (conn.rs.getString("tableidentifier").equals("2")) {
                            //no gender
                            qry = " select sum((totalAchieved)) as y" + yearval
                                    + " from indicatorachievedcombined where financialyear='" + yearval
                                    + "' and reportingPeriod='Q4' and  titleID='" + conn.rs.getString("titleID")
                                    + "' group by titleID";

                        } else {
                            qry = " select sum((menAchieved + womenAchieved)) as y" + yearval
                                    + " from indicatorachieved where financialyear='" + yearval
                                    + "' and reportingPeriod='Q4' and  titleID='" + conn.rs.getString("titleID")
                                    + "' group by titleID";

                        }

                    }

                    //execute the query  
                    conn.rs1 = conn.state1.executeQuery(qry);

                    if (conn.rs1.next()) {
                        // System.out.println("__"+conn.rs1.getString(1));

                        if (conn.rs1.getString(1) != null) {
                            if (!conn.rs1.getString(1).equals("")) {

                                clx.setCellValue(conn.rs1.getInt(1));
                                //this is for percentage purpose
                                if (selectedyear == yearval) {
                                    currentyearvalue = conn.rs1.getInt(1);
                                }

                            }
                        }

                    }

                    //achieved nonpercentage
                    if (yearval == selectedyear) {

                        if (annualtarget != 1) {
                            achievednonpercent = "" + (int) currentyearvalue * 100 / (int) annualtarget + "%";
                        }
                    }

                    clx.setCellStyle(style2);

                } //end of olmis and last reported indicators

                curcol++;

            } //end of for loop
            HSSFCell clx = rwx.createCell(curcol);

            if (conn.rs.getInt("percentage") == 1) {

                clx.setCellValue(percentageachievement);

            } else {
                clx.setCellValue(achievednonpercent);

            }

            clx.setCellStyle(style2);
            rwx.setHeightInPoints(42);

            rownumber++;
            count++;
            colpos = 0;
        }

        for (int e = 0; e < 13; e++) {
            //shet.autoSizeColumn(e);
        }
        //Made my life veery simple...
        shet.setDisplayGridlines(false);
        shet.createFreezePane(2, 3);

        if (conn.rs != null) {
            conn.rs.close();
        }
        if (conn.rs1 != null) {
            conn.rs1.close();
        }
        if (conn.state1 != null) {
            conn.state1.close();
        }
        if (conn.state2 != null) {
            conn.state2.close();
        }

        // write it as an excel attachment
        ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
        wb.write(outByteStream);
        byte[] outArray = outByteStream.toByteArray();
        response.setContentType("application/ms-excel");
        response.setContentLength(outArray.length);
        response.setHeader("Expires:", "0"); // eliminates browser caching
        response.setHeader("Content-Disposition", "attachment; filename=PPMPREPORT_" + selectedyear + "_"
                + selectedQTR + "_gen_on_" + generationtime + ".xls");
        OutputStream outStream = response.getOutputStream();
        outStream.write(outArray);
        outStream.flush();

    } finally {

    }
}

From source file:quickreports.masterlist.java

/**
 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
 * methods.//from   w  w  w . j a v a  2s  . c  o  m
 *
 * @param request servlet request
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException, SQLException {
    response.setContentType("text/html;charset=UTF-8");
    //PrintWriter out = response.getWriter();

    /* TODO output your page here. You may use following sample code. */
    //______________________________________________________________________________________
    //                       CREATE THE WORKSHEETS          
    //______________________________________________________________________________________  
    HSSFWorkbook wb = new HSSFWorkbook();

    HSSFFont font = wb.createFont();
    font.setFontHeightInPoints((short) 18);
    font.setFontName("Cambria");
    font.setColor((short) 0000);
    CellStyle style = wb.createCellStyle();
    style.setFont(font);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    HSSFFont font2 = wb.createFont();
    font2.setFontName("Cambria");
    font2.setColor((short) 0000);
    CellStyle style2 = wb.createCellStyle();
    style2.setFont(font2);
    style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);

    HSSFCellStyle stborder = wb.createCellStyle();
    stborder.setBorderTop(HSSFCellStyle.BORDER_THIN);
    stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    stborder.setBorderRight(HSSFCellStyle.BORDER_THIN);
    stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    HSSFCellStyle stylex = wb.createCellStyle();
    stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    stylex.setBorderTop(HSSFCellStyle.BORDER_THIN);
    stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    stylex.setBorderRight(HSSFCellStyle.BORDER_THIN);
    stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT);

    HSSFCellStyle stylesum = wb.createCellStyle();
    stylesum.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    stylesum.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    stylesum.setBorderTop(HSSFCellStyle.BORDER_THIN);
    stylesum.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    stylesum.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    stylesum.setBorderRight(HSSFCellStyle.BORDER_THIN);
    stylesum.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    HSSFFont fontx = wb.createFont();
    fontx.setColor(HSSFColor.BLACK.index);
    fontx.setFontName("Cambria");
    stylex.setFont(fontx);
    stylex.setWrapText(true);

    stylesum.setFont(fontx);
    stylesum.setWrapText(true);

    HSSFSheet shet = wb.createSheet("Masterlist");

    String year = "";

    if (request.getParameter("year") != null) {

        year = request.getParameter("year");

    }
    dbConn conn = new dbConn();
    //========Query 1=================

    HSSFRow rw0 = shet.createRow(1);
    HSSFCell cell = rw0.createCell(0);
    cell.setCellValue("APHIAPLUS Nuru Ya Bonde Supported Sites Summary " + year);
    cell.setCellStyle(style);
    shet.addMergedRegion(new CellRangeAddress(1, 1, 0, 3));

    int count1 = 3;

    String qry1 = "call rpt_masterlist_summary('2015-10-01','2016-09-30','" + year + "')";

    conn.rs = conn.st.executeQuery(qry1);

    ResultSetMetaData metaData = conn.rs.getMetaData();
    int columnCount = metaData.getColumnCount();

    ArrayList mycolumns1 = new ArrayList();

    while (conn.rs.next()) {

        if (count1 == 3) {
            //header rows
            HSSFRow rw = shet.createRow(count1);
            rw.setHeightInPoints(26);
            for (int i = 1; i <= columnCount; i++) {

                mycolumns1.add(metaData.getColumnLabel(i));
                HSSFCell cell0 = rw.createCell(i - 1);
                cell0.setCellValue(metaData.getColumnLabel(i));
                cell0.setCellStyle(stylex);

                //create row header
            } //end of for loop
            count1++;
        } //end of if
          //data rows     
        HSSFRow rw = shet.createRow(count1);

        for (int a = 0; a < columnCount; a++) {
            // System.out.print(mycolumns1.get(a) + ":" + conn.rs.getString("" + mycolumns1.get(a)));

            HSSFCell cell0 = rw.createCell(a);
            if (a > 0) {

                cell0.setCellValue(conn.rs.getInt(mycolumns1.get(a).toString()));

            } else {

                cell0.setCellValue(conn.rs.getString("" + mycolumns1.get(a)));
            }

            cell0.setCellStyle(style2);

        }

        // System.out.println("");
        count1++;
    }

    HSSFRow rw01 = shet.createRow(count1 + 1);
    HSSFCell cell1 = rw01.createCell(0);
    cell1.setCellValue("APHIAPLUS Nuru Ya Bonde supported sites details " + year);
    cell1.setCellStyle(style);
    shet.addMergedRegion(new CellRangeAddress(count1 + 1, count1 + 1, 0, 3));

    //========Query two====Facility Details==============

    String qry = "call rpt_masterlist('2015-10-01','2016-09-30','" + year + "')";

    conn.rs = conn.st.executeQuery(qry);

    metaData = conn.rs.getMetaData();
    columnCount = metaData.getColumnCount();
    int count = count1 + 3;
    ArrayList mycolumns = new ArrayList();

    while (conn.rs.next()) {

        if (count == (count1 + 3)) {
            //header rows
            HSSFRow rw = shet.createRow(count);
            rw.setHeightInPoints(26);
            for (int i = 1; i <= columnCount; i++) {

                mycolumns.add(metaData.getColumnLabel(i));
                HSSFCell cell0 = rw.createCell(i - 1);
                cell0.setCellValue(metaData.getColumnLabel(i));
                cell0.setCellStyle(stylex);

                //create row header
            } //end of for loop
            count++;
        } //end of if
          //data rows     
        HSSFRow rw = shet.createRow(count);

        for (int a = 0; a < columnCount; a++) {
            //System.out.print(mycolumns.get(a) + ":" + conn.rs.getString("" + mycolumns.get(a)));

            HSSFCell cell0 = rw.createCell(a);
            if ((a >= 3 && a <= 14) || (a >= 18 && a <= 22) || (a >= 27 && a <= 29)) {

                cell0.setCellValue(conn.rs.getInt(mycolumns.get(a).toString()));
            } else {
                cell0.setCellValue(conn.rs.getString("" + mycolumns.get(a)));
            }

            cell0.setCellStyle(style2);

        }

        // System.out.println("");
        count++;
    }

    //Autofreeze  || Autofilter  || Remove Gridlines ||  

    shet.setAutoFilter(new CellRangeAddress(count1 + 3, count - 1, 0, columnCount - 1));

    //System.out.println("1,"+rowpos+",0,"+colposcopy);
    for (int i = 0; i <= columnCount; i++) {
        shet.autoSizeColumn(i);
    }

    shet.setDisplayGridlines(false);
    shet.createFreezePane(4, 14);

    if (conn.rs != null) {
        conn.rs.close();
    }
    if (conn.rs1 != null) {
        conn.rs1.close();
    }
    if (conn.st != null) {
        conn.st.close();
    }
    if (conn.st1 != null) {
        conn.st1.close();
    }

    IdGenerator IG = new IdGenerator();
    String createdOn = IG.CreatedOn();

    System.out.println("" + "MasterList_Gen_" + createdOn.trim() + ".xls");

    ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
    wb.write(outByteStream);
    byte[] outArray = outByteStream.toByteArray();
    response.setContentType("application/ms-excel");
    response.setContentLength(outArray.length);
    response.setHeader("Expires:", "0"); // eliminates browser caching
    response.setHeader("Content-Disposition",
            "attachment; filename=" + "MasterList_Gen_" + createdOn.trim() + ".xls");
    OutputStream outStream = response.getOutputStream();
    outStream.write(outArray);
    outStream.flush();

}

From source file:reports.allStaticReports.java

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    try {/*from w  ww. j a va  2  s.c om*/
        response.setContentType("text/html;charset=UTF-8");

        //a page to get Report of all the servlets

        String year = "2015";
        String month = "";
        String county = "";
        String form = "moh731";

        if (request.getParameter("year") != null) {
            year = request.getParameter("year");
        }

        if (request.getParameter("county") != null) {
            county = request.getParameter("county");
        }

        if (request.getParameter("month") != null) {
            month = request.getParameter("month");
        }

        if (request.getParameter("form") != null) {
            form = request.getParameter("form");
        }

        String facilitywhere = "";
        String yearwhere = "";
        String monthwhere = "";
        String countywhere = "";
        String districtwhere = "";
        String reporttype = "";

        if (!year.equals("")) {

            yearwhere = " and Annee = '" + year + "'";

        }
        if (!county.equals("")) {

            countywhere = " and countyid = '" + county + "'";

        }
        if (!month.equals("")) {

            monthwhere = " and Mois = '" + month + "'";

        }

        dbConn conn = new dbConn();

        //an array to store haeder information.

        //the header information should appear only if a certain parameters are met
        //The parameters listed in here can be removed if the report type doesnt require certain parameters
        ArrayList Headerorgunits = new ArrayList();
        Headerorgunits.add("COUNTY");
        Headerorgunits.add("SUB-COUNTY");
        Headerorgunits.add("FACILITY");
        Headerorgunits.add("MFL CODE");

        //An arralist to store a list of columns that will be selected from the database
        ArrayList dbcolumns = new ArrayList();

        ArrayList labels = new ArrayList();

        ArrayList tablename = new ArrayList();

        ArrayList iscumulative = new ArrayList();

        ArrayList ispercent = new ArrayList();

        // ArrayList isactive=new ArrayList();
        //An arralist to store a list of worksheets that will be selected from the sections
        ArrayList worksheets = new ArrayList();
        //An arralist to store distinct worksheets. This will be derived from the the sections column
        ArrayList distinctsheets = new ArrayList();

        String selectdistinctworksheet = "select distinct(section) from pivottable where form='" + form
                + "' and active='1' order by order_per_form";

        conn.rs = conn.st.executeQuery(selectdistinctworksheet);

        while (conn.rs.next()) {
            //add the name of distinct sections
            distinctsheets.add(conn.rs.getString(1).replace("/", "_"));

        }

        String getattribs = "select indicator,label,section,cumulative,percentage,active ,shortlabel from pivottable where form='"
                + form + "' order by order_per_form, section";
        conn.rs = conn.st.executeQuery(getattribs);

        while (conn.rs.next()) {

            //add active indicators only

            if (conn.rs.getString("active").equals("1")) {
                System.out.println(conn.rs.getString("indicator") + "");
                //add indicator
                dbcolumns.add(conn.rs.getString("indicator"));
                //add label
                if (form.equals("moh731")) {
                    labels.add(conn.rs.getString("shortlabel") + " \n " + conn.rs.getString("label"));

                } else {
                    labels.add(conn.rs.getString("label"));
                }
                //add worksheets
                worksheets.add(conn.rs.getString("section").replace("/", "_"));

                String perc = "0";
                String cum = "0";

                if (conn.rs.getString("cumulative") != null) {
                    iscumulative.add(conn.rs.getString("cumulative"));
                } else {
                    iscumulative.add(cum);
                }

                if (conn.rs.getString("percentage") != null) {
                    ispercent.add(conn.rs.getString("percentage"));
                } else {
                    ispercent.add(perc);
                }

            } //end of active 

        } //end of worksheet

        //if

        String perfacilselect = "select   Upper(County) as County , Upper(DistrictNom) as District , UPPER(SubPartnerNom) as facility ,CentreSanteId as mflcode , district.CountyID as countyid , ";

        //--------------------------------------------------------------------------------------------
        //             PREPARE SELECT
        //--------------------------------------------------------------------------------------------
        //prepare selects

        for (int a = 0; a < dbcolumns.size(); a++) {

            //if the indicator is a percent, get an avaerage

            if (ispercent.get(a).equals("1")) {
                perfacilselect += "  AVG(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a);

            } else if (iscumulative.get(a).equals("1")) {
                perfacilselect += "  " + dbcolumns.get(a) + " as " + dbcolumns.get(a);

            }

            else {
                perfacilselect += "  SUM(" + dbcolumns.get(a) + ") as " + dbcolumns.get(a);

            }

            //if the item is not the last, append a comma

            if (a < dbcolumns.size() - 1) {

                perfacilselect += " ,";

            }

        }

        //------------------------------------------------------------------------------------
        //     FROM  
        //------------------------------------------------------------------------------------  

        perfacilselect += " , isValidated as Form_Validated from " + form
                + "  join ( subpartnera join (district join county on county.CountyID=district.CountyID ) on district.DistrictID = subpartnera.DistrictID )  on "
                + form + ".SubPartnerID = subpartnera.SubPartnerID ";

        //------------------------------------------------------------------------------------------
        // WHERE 
        //------------------------------------------------------------------------------------------ 

        perfacilselect += " where  1=1 " + monthwhere + yearwhere;

        //-----------------------------------------------------------------------------------------
        //GROUP BY 
        //----------------------------------------------------------------------------------------

        perfacilselect += " group by subpartnera.SubPartnerID";

        System.out.println(perfacilselect);
        //______________________________________________________________________________________
        //                       NOW CREATE THE WORKSHEETS          
        //______________________________________________________________________________________  

        XSSFWorkbook wb = new XSSFWorkbook();

        //______________________________________________________________________________________
        //______________________________________________________________________________________

        XSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) 18);
        font.setFontName("Cambria");
        font.setColor((short) 0000);
        CellStyle style = wb.createCellStyle();
        style.setFont(font);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        XSSFFont font2 = wb.createFont();
        font2.setFontName("Cambria");
        font2.setColor((short) 0000);
        CellStyle style2 = wb.createCellStyle();
        style2.setFont(font2);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);

        XSSFCellStyle stborder = wb.createCellStyle();
        stborder.setBorderTop(HSSFCellStyle.BORDER_THIN);
        stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        stborder.setBorderRight(HSSFCellStyle.BORDER_THIN);
        stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        XSSFCellStyle stylex = wb.createCellStyle();
        stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        stylex.setBorderTop(HSSFCellStyle.BORDER_THIN);
        stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        stylex.setBorderRight(HSSFCellStyle.BORDER_THIN);
        stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT);

        XSSFFont fontx = wb.createFont();
        fontx.setColor(HSSFColor.BLACK.index);
        fontx.setFontName("Cambria");
        stylex.setFont(fontx);
        stylex.setWrapText(true);

        for (int b = 0; b < distinctsheets.size(); b++) {
            XSSFSheet shet = wb.createSheet(distinctsheets.get(b).toString().toUpperCase());

            //create headers for that worksheet

            XSSFRow rw = shet.createRow(1);
            int headercellpos = 0;
            //create the orgunit header eg COUNTY | SUBCOUNTY  | FACILITY

            for (int e = 0; e < Headerorgunits.size(); e++) {
                XSSFCell cell0 = rw.createCell(headercellpos);
                cell0.setCellValue(Headerorgunits.get(e).toString());
                cell0.setCellStyle(stylex);
                headercellpos++;
                shet.setColumnWidth(e, 6000);
            }

            //create the indicators header eg HV0101 | HIV 09676  | TOTAL    
            for (int c = 0; c < dbcolumns.size(); c++) {
                //compare if the indicator belongs to the specified section and hence worksheet 
                //recall, each indicator has got an associated section / worksheet
                //An indicator should be put as an header in the respective worksheet
                if (worksheets.get(c).equals(distinctsheets.get(b))) {

                    shet.setColumnWidth(headercellpos, 6000);
                    XSSFCell cell0 = rw.createCell(headercellpos);
                    cell0.setCellValue(labels.get(c).toString());
                    cell0.setCellStyle(stylex);
                    headercellpos++;
                } //end of comparing if

            } //end of for loop

            //create is validated header

            shet.setColumnWidth(headercellpos, 6000);
            XSSFCell cell0 = rw.createCell(headercellpos);
            cell0.setCellValue("Form Validated ?");
            cell0.setCellStyle(stylex);
            headercellpos++;

        }

        conn.rs = conn.st.executeQuery(perfacilselect);
        String sectioncopy = "";

        int sheetpos = 0;
        int rowpos = 2;

        while (conn.rs.next()) {
            //-----------------INSIDE THE DATA FORM---------------------------------
            //if the section changes, change the position of the worksheet too
            //also, reset the position counter to begin from 2 again. 

            XSSFSheet shet = null;

            //      if(--!sectioncopy.equals(shet)){}

            for (int g = 0; g < distinctsheets.size(); g++) {
                shet = wb.getSheetAt(g);
                int colpos = 0;
                //the fourth cell should     
                XSSFRow rw = shet.createRow(rowpos);
                for (int e = 0; e < Headerorgunits.size(); e++) {
                    XSSFCell cell0 = rw.createCell(colpos);
                    cell0.setCellValue(conn.rs.getString(e + 1));
                    cell0.setCellStyle(style2);
                    colpos++;

                }

                //_________________________________________________________________
                //VALUES
                //_________________________________________________________________

                //create the indicators values eg 90 | 45  | 356    
                for (int c = 0; c < dbcolumns.size(); c++) {
                    //get the section of the current dbcolumn

                    //compare if the indicator belongs to the specified section and hence worksheet 
                    //recall, each indicator has got an associated section / worksheet
                    //An indicator should be put as an header in the respective worksheet
                    if (worksheets.get(c).equals(distinctsheets.get(g))) {

                        XSSFCell cell0 = rw.createCell(colpos);
                        cell0.setCellValue(conn.rs.getString(dbcolumns.get(c).toString()));
                        cell0.setCellStyle(stborder);
                        colpos++;
                    } //end of comparing if

                } //end of for loop

                String isvalidated = "Yes";

                if (conn.rs.getString("Form_Validated").equals("0")) {
                    isvalidated = "No";
                }
                XSSFCell cell0 = rw.createCell(colpos);
                cell0.setCellValue(isvalidated);
                cell0.setCellStyle(stborder);
                colpos++;

            }

            rowpos++;

        }

        IdGenerator IG = new IdGenerator();
        String createdOn = IG.CreatedOn();

        System.out.println("" + form.toUpperCase().trim() + "_REPORT_FOR_" + year.trim() + "(" + month.trim()
                + ")_CREATED_" + createdOn.trim() + ".xlsx");

        ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
        wb.write(outByteStream);
        byte[] outArray = outByteStream.toByteArray();
        response.setContentType("application/ms-excel");
        response.setContentLength(outArray.length);
        response.setHeader("Expires:", "0"); // eliminates browser caching
        response.setHeader("Content-Disposition",
                "attachment; filename=" + form.toUpperCase().trim() + "_REPORT_FOR_" + year.trim() + "("
                        + month.trim() + ")_CREATED_" + createdOn.trim() + ".xlsx");
        OutputStream outStream = response.getOutputStream();
        outStream.write(outArray);
        outStream.flush();
    } catch (SQLException ex) {
        Logger.getLogger(allStaticReports.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:reports.allStaticReportsdynamic.java

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    String monthrange = "";
    String periodlabel = "";
    String period = "Month"; //The default one
    String periodicgroupby = " "; //note that in the current query there is  an existing group by. Therefore this will be an extra infor to be added on the existing group by
    String isgroupby = "yes";
    try {//from  w  ww  .  j a v  a2 s  . co m
        response.setContentType("text/html;charset=UTF-8");

        //a page to get Report of all the servlets
        String months[] = null;

        String year = "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);
    }
}