Example usage for org.apache.poi.openxml4j.opc OPCPackage close

List of usage examples for org.apache.poi.openxml4j.opc OPCPackage close

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.openxml4j.opc.*
  5. OPCPackage
  6. close

Introduction

In this page you can find the example usage for org.apache.poi.openxml4j.opc OPCPackage close.

Prototype

@Override
public void close() throws IOException

Source Link

Document

Close the open, writable package and save its content.

Usage

From source file:reports.kePMSnew.java

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException, SQLException, InvalidFormatException {
    session = request.getSession();//from  w ww. j  a v a2s .  c o m
    dbConn conn = new dbConn();
    pos = 0;
    incrementor = 0;
    //    pepfaryear=2014;
    //    pepfaryear=Integer.parseInt(request.getParameter("year"));
    pepfaryear = Integer.parseInt(session.getAttribute("PepfarYear").toString());
    prevyear = pepfaryear - 1;
    String enddate = pepfaryear + "09";
    String startdate = prevyear + "10";

    start = Integer.parseInt(startdate);
    end = Integer.parseInt(enddate);
    System.out.println("start date : " + start + " end date  : " + end);
    //    COPY FILE TO BE WRITTEN TO 
    Path original = Paths.get(getServletContext().getRealPath("/TEMPLATE.xlsm")); //original file
    Path destination = Paths.get(getServletContext().getRealPath("/TEMPLATE_1.xlsm")); //new file
    System.out.println("origin :  " + original + " destination    :  " + destination);
    try {
        Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING);
        System.out.println("file copied----------------");
    } catch (IOException x) {
        //catch all for IO problems
        System.out.println("fine not copied");
    }

    String allpath = getServletContext().getRealPath("/TEMPLATE_1.xlsm");

    System.out.println("real path for template : " + allpath);
    //            ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^
    XSSFWorkbook wb;
    OPCPackage pkg = OPCPackage.open(allpath);

    wb = new XSSFWorkbook(pkg);

    //        HSSFWorkbook wb=new HSSFWorkbook();
    XSSFSheet shet1 = wb.getSheet("sheet0");
    XSSFFont font = wb.createFont();
    font.setFontHeightInPoints((short) 18);
    font.setFontName("Arial Black");
    font.setColor((short) 0000);
    CellStyle style = wb.createCellStyle();
    style.setFont(font);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    XSSFFont font2 = wb.createFont();
    font2.setFontName("Arial Black");
    font2.setColor((short) 0000);
    CellStyle style2 = wb.createCellStyle();
    style2.setFont(font2);

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

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

    XSSFFont fontx = wb.createFont();
    fontx.setColor(HSSFColor.DARK_BLUE.index);
    stylex.setFont(fontx);
    stylex.setWrapText(true);

    //  HSSFSheet sheet1 = wb.getSheetAt(0);
    shet1.setColumnWidth(0, 4000);
    shet1.setColumnWidth(1, 4000);
    shet1.setColumnWidth(2, 4000);
    shet1.setColumnWidth(3, 4000);
    shet1.setColumnWidth(4, 4000);

    XSSFRow rw4 = shet1.createRow(0);
    rw4.setHeightInPoints(45);
    rw4.setRowStyle(style2);
    // rw4.createCell(1).setCellValue("Number");
    XSSFCell cell0, cell1, cell2, cell3, cell4;

    cell0 = rw4.createCell(0);
    cell1 = rw4.createCell(1);
    cell2 = rw4.createCell(2);
    cell3 = rw4.createCell(3);
    cell4 = rw4.createCell(4);

    cell0.setCellValue("PARTNER NAME");
    cell1.setCellValue("AGE BRACKET");
    cell2.setCellValue("GENDER");
    cell3.setCellValue("MONTH");
    cell4.setCellValue("ACHIEVED");

    String getClients = "SELECT partner.partner_name," + "CASE "
            + "when personal_information.completionmonth =01 THEN '" + pepfaryear + "-01(JAN)' "
            + "when personal_information.completionmonth =02 THEN '" + pepfaryear + "-02 (FEB)' "
            + "when personal_information.completionmonth =03 THEN '" + pepfaryear + "-03 (MAR)' "
            + "when personal_information.completionmonth=04 THEN '" + pepfaryear + "-04 (APR)' "
            + "when personal_information.completionmonth=05 THEN '" + pepfaryear + "-05 (MAY)' "
            + "when personal_information.completionmonth=06 THEN '" + pepfaryear + "-06 (JUN)' "
            + "when personal_information.completionmonth=07 THEN '" + pepfaryear + "-07 (JUL)' "
            + "when personal_information.completionmonth=08 THEN '" + pepfaryear + "-08 (AUG)' "
            + "when personal_information.completionmonth=09 THEN '" + pepfaryear + "-09 (SEPT)' "
            + "when personal_information.completionmonth=10 THEN '" + prevyear + "-10 (OCT)' "
            + "when personal_information.completionmonth=11 THEN '" + prevyear + "-11 (NOV)'"
            + "when personal_information.completionmonth=12 THEN '" + prevyear + "-12 (DEC)'"
            + "END AS MONTHS,personal_information.completionyear," + "CASE"
            + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9'"
            + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14'"
            + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'"
            + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'"
            + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49'"
            + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above'"
            + " ELSE 'NO DATE OF BIRTH'" + "   END AS AGEBRACKET," + "CASE "
            + "when personal_information.gender LIKE 'Female' THEN 'F' "
            + "when personal_information.gender LIKE 'Male' THEN 'M' " + "ELSE 'NO SEX' " + "END AS SEX"
            + ",completionmonth,COUNT(personal_information.client_id) FROM personal_information "
            + "JOIN partner ON personal_information.partner_id=partner.partner_id "
            + " WHERE personal_information.completionmonth>0 && personal_information.completionyear>0 GROUP BY partner.partner_name,SEX,personal_information.completionyear,MONTHS,AGEBRACKET ORDER BY personal_information.partner_id";
    conn.rs = conn.st.executeQuery(getClients);
    while (conn.rs.next()) {
        partnername = clientid = gender = "";
        age = 0;

        partnername = conn.rs.getString(1);
        month = conn.rs.getString(2);
        year = conn.rs.getInt(3);
        agebracket = conn.rs.getString(4);
        gender = conn.rs.getString(5);
        String dkey = year + "" + conn.rs.getString(6);
        datekey = Integer.parseInt(dkey);
        achieved = conn.rs.getInt(7);
        incrementor += achieved;
        System.out.println(
                "date key : " + datekey + "startdate : " + start + "   end date : " + end + " year : " + year);
        if (datekey >= start && datekey <= end && year >= 2014) {

            pos++;
            XSSFRow rw4x = shet1.createRow(pos);
            rw4x.setHeightInPoints(25);
            rw4x.setRowStyle(style2);
            XSSFCell cell0x, cell1x, cell2x, cell3x, cell4x;
            cell0x = rw4x.createCell(0);
            cell1x = rw4x.createCell(1);
            cell2x = rw4x.createCell(2);
            cell3x = rw4x.createCell(3);
            cell4x = rw4x.createCell(4);

            //  OUTPUT SERVICES PROVIDED================================     
            cell0x.setCellValue(partnername);
            cell1x.setCellValue(agebracket);
            cell2x.setCellValue(gender);
            cell3x.setCellValue(month);
            cell4x.setCellValue(achieved);

            System.out.println(pos + "-----partner :" + partnername + " age bracket :" + agebracket
                    + " gender :" + gender + " completion month : " + month);
        }
    }

    if (conn.rs != null) {
        conn.rs.close();
    }
    if (conn.st != null) {
        conn.st.close();
    }
    if (conn.rs1 != null) {
        conn.rs1.close();
    }
    if (conn.st1 != null) {
        conn.st1.close();
    }
    if (conn.rs2 != null) {
        conn.rs2.close();
    }
    if (conn.st2 != null) {
        conn.st2.close();
    }
    if (conn.st3 != null) {
        conn.st3.close();
    }
    if (conn.pst != null) {
        conn.pst.close();
    }
    if (conn.pst != null) {
        conn.pst.close();
    }
    if (conn.pst1 != null) {
        conn.pst1.close();
    }
    if (conn.pst1 != null) {
        conn.pst1.close();
    }
    if (conn.conn != null) {
        conn.conn.close();
    }

    if (incrementor > 0) {
        IdGenerator CRT = new IdGenerator();
        ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
        wb.write(outByteStream);
        byte[] outArray = outByteStream.toByteArray();
        response.setContentType("application/ms-excel");
        response.setContentLength(outArray.length);
        response.setHeader("Expires:", "0"); // eliminates browser caching
        response.setHeader("Content-Disposition",
                "attachment; filename=PWP_kePMS_ACHIEVED_REPORT_PER_PARTNER_FOR_PEPFAR_YEAR_" + pepfaryear
                        + "_CREATED_ON_" + CRT.timestamp() + ".xlsm");
        OutputStream outStream = response.getOutputStream();
        outStream.write(outArray);
        outStream.flush();

        pkg.close();
    } else {
        session.setAttribute("kePMSError",
                "<font color=\"red\"><b>NO DATA WITHIN THE SELECTED PARAMETERS.</b></font>");
        response.sendRedirect("kePMS.jsp");
    }
}

From source file:reports.kePMSNotAchieved.java

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException, InvalidFormatException, SQLException {
    session = request.getSession();// w w w  .  ja va  2s.c  om
    dbConn conn = new dbConn();
    pos = 0;

    //    COPY FILE TO BE WRITTEN TO 
    Path original = Paths.get(getServletContext().getRealPath("/NotAchieved.xlsm")); //original file
    Path destination = Paths.get(getServletContext().getRealPath("/NotAchieved_1.xlsm")); //new file
    System.out.println("origin :  " + original + " destination    :  " + destination);
    try {
        Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING);
        System.out.println("file copied----------------");
    } catch (IOException x) {
        //catch all for IO problems
        System.out.println("fine not copied");
    }

    String allpath = getServletContext().getRealPath("/NotAchieved_1.xlsm");

    //            ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^
    XSSFWorkbook wb;
    OPCPackage pkg = OPCPackage.open(allpath);

    wb = new XSSFWorkbook(pkg);

    //        HSSFWorkbook wb=new HSSFWorkbook();
    XSSFSheet shet1 = wb.getSheet("sheet1");
    XSSFFont font = wb.createFont();
    font.setFontHeightInPoints((short) 18);
    font.setFontName("Arial Black");
    font.setColor((short) 0000);
    CellStyle style = wb.createCellStyle();
    style.setFont(font);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    XSSFFont font2 = wb.createFont();
    font2.setFontName("Arial Black");
    font2.setColor((short) 0000);
    CellStyle style2 = wb.createCellStyle();
    style2.setFont(font2);

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

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

    XSSFFont fontx = wb.createFont();
    fontx.setColor(HSSFColor.DARK_BLUE.index);
    stylex.setFont(fontx);
    stylex.setWrapText(true);

    //  HSSFSheet sheet1 = wb.getSheetAt(0);
    shet1.setColumnWidth(0, 4000);
    shet1.setColumnWidth(1, 4000);
    shet1.setColumnWidth(2, 4000);
    shet1.setColumnWidth(3, 4000);
    shet1.setColumnWidth(4, 4000);

    XSSFRow rw4 = shet1.createRow(0);
    rw4.setHeightInPoints(45);
    rw4.setRowStyle(style2);
    // rw4.createCell(1).setCellValue("Number");
    XSSFCell cell0, cell1, cell2, cell3, cell4, cell5, cell6;

    cell0 = rw4.createCell(0);
    cell1 = rw4.createCell(1);
    cell2 = rw4.createCell(2);
    cell3 = rw4.createCell(3);
    cell4 = rw4.createCell(4);
    cell5 = rw4.createCell(5);
    cell6 = rw4.createCell(6);

    cell0.setCellValue("COUNTY NAME");
    cell1.setCellValue("DISTRICT NAME");
    cell2.setCellValue("PARTNER NAME");
    cell3.setCellValue("GROUP NAME");
    cell4.setCellValue("AGE BRACKET");
    cell5.setCellValue("GENDER");
    cell6.setCellValue("CLIENTS");

    String getClients = "SELECT county.county_name,district.district_name,partner.partner_name,"

            + "CASE"
            + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 14 THEN '0-14'"
            + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'"
            + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'"
            + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >24 THEN '>25'"
            + " ELSE 'NO DATE OF BIRTH'" + "   END AS AGEBRACKET," + "CASE "
            + "when personal_information.gender LIKE 'Female' THEN 'F' "
            + "when personal_information.gender LIKE 'Male' THEN 'M' " + "ELSE 'NO SEX' " + "END AS SEX"
            + ",COUNT(personal_information.client_id),groups.group_name as groupname FROM personal_information "
            + "JOIN partner ON personal_information.partner_id=partner.partner_id "
            + " JOIN district ON district.district_id=personal_information.district_id "
            + "JOIN county ON county.county_id=district.county_id "
            + "LEFT JOIN groups ON personal_information.group_id=groups.group_id "
            + " WHERE personal_information.lessons_attended>0 && personal_information.completionyear=0 "
            + "GROUP BY partner.partner_name,district.district_name,groupname,SEX,AGEBRACKET ORDER BY personal_information.partner_id";
    conn.rs = conn.st.executeQuery(getClients);
    while (conn.rs.next()) {
        partnername = clientid = gender = "";
        age = 0;

        countyname = conn.rs.getString(1);
        districtname = conn.rs.getString(2);
        partnername = conn.rs.getString(3);
        agebracket = conn.rs.getString(4);
        gender = conn.rs.getString(5);
        achieved = conn.rs.getInt(6);
        groupname = conn.rs.getString(7);

        if (groupname == null) {
            groupname = "INDIVIDUAL";
        }
        pos++;
        XSSFRow rw4x = shet1.createRow(pos);
        rw4x.setHeightInPoints(25);
        rw4x.setRowStyle(style2);
        XSSFCell cell0x, cell1x, cell2x, cell3x, cell4x, cell5x, cell6x;
        cell0x = rw4x.createCell(0);
        cell1x = rw4x.createCell(1);
        cell2x = rw4x.createCell(2);
        cell3x = rw4x.createCell(3);
        cell4x = rw4x.createCell(4);
        cell5x = rw4x.createCell(5);
        cell6x = rw4x.createCell(6);

        //  OUTPUT SERVICES PROVIDED================================     
        cell0x.setCellValue(countyname);
        cell1x.setCellValue(districtname);
        cell2x.setCellValue(partnername);
        cell3x.setCellValue(groupname);
        cell4x.setCellValue(agebracket);
        cell5x.setCellValue(gender);
        cell6x.setCellValue(achieved);
        System.out.println(pos + "-----partner :" + partnername + " age bracket :" + agebracket + " gender :"
                + gender + " completion month : " + month);

    }

    if (conn.rs != null) {
        conn.rs.close();
    }
    if (conn.st != null) {
        conn.st.close();
    }
    if (conn.rs1 != null) {
        conn.rs1.close();
    }
    if (conn.st1 != null) {
        conn.st1.close();
    }
    if (conn.rs2 != null) {
        conn.rs2.close();
    }
    if (conn.st2 != null) {
        conn.st2.close();
    }
    if (conn.st3 != null) {
        conn.st3.close();
    }
    if (conn.pst != null) {
        conn.pst.close();
    }
    if (conn.pst != null) {
        conn.pst.close();
    }
    if (conn.pst1 != null) {
        conn.pst1.close();
    }
    if (conn.pst1 != null) {
        conn.pst1.close();
    }
    if (conn.conn != null) {
        conn.conn.close();
    }

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

    pkg.close();
}

From source file:reports.kePMSNotAchievedDIC.java

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException, InvalidFormatException, SQLException {
    session = request.getSession();//w ww . jav  a 2s.  com
    dbConn conn = new dbConn();
    pos = 0;
    //        partner_id="4";
    partner_id = session.getAttribute("partnerDIC").toString();

    String getPartner = "SELECT partner_name FROM partner WHERE partner_id='" + partner_id + "'";
    conn.rs = conn.st.executeQuery(getPartner);
    if (conn.rs.next() == true) {
        partnername = conn.rs.getString(1).trim().replace(" ", "_");
    }

    //    COPY FILE TO BE WRITTEN TO 
    Path original = Paths.get(getServletContext().getRealPath("/TEMPLATE.xlsm")); //original file
    Path destination = Paths.get(getServletContext().getRealPath("/TEMPLATE_1.xlsm")); //new file
    System.out.println("origin :  " + original + " destination    :  " + destination);
    try {
        Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING);
        System.out.println("file copied----------------");
    } catch (IOException x) {
        //catch all for IO problems
        System.out.println("fine not copied");
    }

    String allpath = getServletContext().getRealPath("/TEMPLATE_1.xlsm");

    //            ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^
    XSSFWorkbook wb;
    OPCPackage pkg = OPCPackage.open(allpath);

    wb = new XSSFWorkbook(pkg);

    //        HSSFWorkbook wb=new HSSFWorkbook();
    XSSFSheet shet1 = wb.getSheet("sheet0");
    XSSFFont font = wb.createFont();
    font.setFontHeightInPoints((short) 18);
    font.setFontName("Arial Black");
    font.setColor((short) 0000);
    CellStyle style = wb.createCellStyle();
    style.setFont(font);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    XSSFFont font2 = wb.createFont();
    font2.setFontName("Arial Black");
    font2.setColor((short) 0000);
    CellStyle style2 = wb.createCellStyle();
    style2.setFont(font2);

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

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

    XSSFFont fontx = wb.createFont();
    fontx.setColor(HSSFColor.DARK_BLUE.index);
    stylex.setFont(fontx);
    stylex.setWrapText(true);

    //  HSSFSheet sheet1 = wb.getSheetAt(0);
    shet1.setColumnWidth(0, 4000);
    shet1.setColumnWidth(1, 4000);
    shet1.setColumnWidth(2, 4000);
    shet1.setColumnWidth(3, 4000);
    shet1.setColumnWidth(4, 4000);

    XSSFRow rw4 = shet1.createRow(0);
    rw4.setHeightInPoints(45);
    rw4.setRowStyle(style2);
    // rw4.createCell(1).setCellValue("Number");
    XSSFCell cell0, cell1, cell2, cell3, cell4, cell5;

    cell0 = rw4.createCell(0);
    cell1 = rw4.createCell(1);
    cell2 = rw4.createCell(2);
    cell3 = rw4.createCell(3);
    cell4 = rw4.createCell(4);
    cell5 = rw4.createCell(5);

    cell0.setCellValue("COUNTY NAME");
    cell1.setCellValue("DISTRICT NAME");
    cell2.setCellValue("DIC NAME");
    cell3.setCellValue("AGE BRACKET");
    cell4.setCellValue("GENDER");
    cell5.setCellValue("CLIENTS");

    String getClients = "SELECT county.county_name,district.district_name,dic.dic_name,"

            + "CASE"
            + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 14 THEN '0-14'"
            + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'"
            + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'"
            + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >24 THEN '>25'"
            + " ELSE 'NOT SELECTED'" + "   END AS AGEBRACKET," + "CASE "
            + "when personal_information.gender LIKE 'Female' THEN 'F' "
            + "when personal_information.gender LIKE 'Male' THEN 'M' " + "ELSE 'NO SEX' " + "END AS SEX"
            + ",COUNT(personal_information.client_id) FROM personal_information "
            + "JOIN dic ON personal_information.dic_id=dic.dic_id "
            + " JOIN district ON district.district_id=personal_information.district_id "
            + "JOIN county ON county.county_id=district.county_id " + " WHERE personal_information.partner_id='"
            + partner_id
            + "' && personal_information.lessons_attended>0 && personal_information.completionyear=0 "
            + "GROUP BY dic.dic_name,district.district_name,SEX,AGEBRACKET ORDER BY personal_information.partner_id";
    conn.rs = conn.st.executeQuery(getClients);
    while (conn.rs.next()) {
        partnername = clientid = gender = "";
        age = 0;

        countyname = conn.rs.getString(1);
        districtname = conn.rs.getString(2);
        partnername = conn.rs.getString(3);
        agebracket = conn.rs.getString(4);
        gender = conn.rs.getString(5);
        achieved = conn.rs.getInt(6);

        pos++;
        XSSFRow rw4x = shet1.createRow(pos);
        rw4x.setHeightInPoints(25);
        rw4x.setRowStyle(style2);
        XSSFCell cell0x, cell1x, cell2x, cell3x, cell4x, cell5x;
        cell0x = rw4x.createCell(0);
        cell1x = rw4x.createCell(1);
        cell2x = rw4x.createCell(2);
        cell3x = rw4x.createCell(3);
        cell4x = rw4x.createCell(4);
        cell5x = rw4x.createCell(5);

        //  OUTPUT SERVICES PROVIDED================================     
        cell0x.setCellValue(countyname);
        cell1x.setCellValue(districtname);
        cell2x.setCellValue(partnername);
        cell3x.setCellValue(agebracket);
        cell4x.setCellValue(gender);
        cell5x.setCellValue(achieved);

        System.out.println(pos + "-----partner :" + partnername + " age bracket :" + agebracket + " gender :"
                + gender + " completion month : " + month);

    }

    if (conn.rs != null) {
        conn.rs.close();
    }
    if (conn.st != null) {
        conn.st.close();
    }
    if (conn.rs1 != null) {
        conn.rs1.close();
    }
    if (conn.st1 != null) {
        conn.st1.close();
    }
    if (conn.rs2 != null) {
        conn.rs2.close();
    }
    if (conn.st2 != null) {
        conn.st2.close();
    }
    if (conn.st3 != null) {
        conn.st3.close();
    }
    if (conn.pst != null) {
        conn.pst.close();
    }
    if (conn.pst != null) {
        conn.pst.close();
    }
    if (conn.pst1 != null) {
        conn.pst1.close();
    }
    if (conn.pst1 != null) {
        conn.pst1.close();
    }
    if (conn.conn != null) {
        conn.conn.close();
    }

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

    pkg.close();
}

From source file:reports.kePMSServices.java

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException, SQLException, InvalidFormatException {
    session = request.getSession();//  w  w  w  . ja va 2  s.  c o  m
    dbConn conn = new dbConn();
    incrementor = 0;
    pos = 0;

    pepfaryear = Integer.parseInt(session.getAttribute("PepfarYear").toString());
    prevyear = pepfaryear - 1;
    String[] periods = session.getAttribute("period").toString().split("-");
    if (session.getAttribute("period").toString().equals("10-12")) {
        period = "OCT-DEC";
    } else if (session.getAttribute("period").toString().equals("01-03")) {
        period = "JAN-MARCH";
    } else if (session.getAttribute("period").toString().equals("04-06")) {
        period = "APRIL-JUNE";
    } else if (session.getAttribute("period").toString().equals("07-09")) {
        period = "JULY-SEPT";
    } else {
    }
    startdate = periods[0];
    enddate = periods[1];
    if (session.getAttribute("period").toString().equals("10-12")) {
        pepfaryear = pepfaryear - 1;
    }

    start = Integer.parseInt(startdate);
    end = Integer.parseInt(enddate);

    Path original = Paths.get(getServletContext().getRealPath("/ServicesAll.xlsm")); //original file
    Path destination = Paths.get(getServletContext().getRealPath("/ServicesAll_1.xlsm")); //new file
    System.out.println("origin :  " + original + " destination    :  " + destination);
    try {
        Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING);
        System.out.println("file copied----------------");
    } catch (IOException x) {
        //catch all for IO problems
        System.out.println("fine not copied");
    }

    String allpath = getServletContext().getRealPath("/ServicesAll_1.xlsm");

    //            ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^
    XSSFWorkbook wb;
    OPCPackage pkg = OPCPackage.open(allpath);

    wb = new XSSFWorkbook(pkg);

    //            ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^
    XSSFSheet shet1 = wb.getSheet("sheet1");
    XSSFFont font = wb.createFont();
    font.setFontHeightInPoints((short) 18);
    font.setFontName("Arial Black");
    //    font.setItalic(true);
    //    font.setBoldweight((short)12);
    font.setColor((short) 0000);
    CellStyle style = wb.createCellStyle();
    style.setFont(font);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    XSSFFont font2 = wb.createFont();
    // font2.setFontHeightInPoints((short)15);
    font2.setFontName("Arial Black");
    //    font.setItalic(true);
    //    font2.setBoldweight((short)18);
    font2.setColor((short) 0000);
    CellStyle style2 = wb.createCellStyle();
    style2.setFont(font2);

    XSSFCellStyle stborder = wb.createCellStyle();
    stborder.setBorderTop(HSSFCellStyle.BORDER_THIN);
    stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    stborder.setBorderRight(HSSFCellStyle.BORDER_THIN);
    //    stborder.setFillForegroundColor(HSSFColor.ORANGE.index);
    //    stborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    //  HSSFSheet sheet1 = wb.getSheetAt(0);
    shet1.setColumnWidth(0, 6000);
    shet1.setColumnWidth(1, 6000);
    shet1.setColumnWidth(2, 6000);
    shet1.setColumnWidth(3, 6000);
    shet1.setColumnWidth(4, 6000);
    shet1.setColumnWidth(5, 6000);
    shet1.setColumnWidth(6, 6000);
    shet1.setColumnWidth(7, 6000);
    shet1.setColumnWidth(8, 6000);
    shet1.setColumnWidth(9, 6000);
    shet1.setColumnWidth(10, 6000);
    shet1.setColumnWidth(11, 6000);

    //    shet1.setColumnWidth(20, 2000);
    XSSFCellStyle styleBorder = wb.createCellStyle();
    styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleBorder.setFillForegroundColor(HSSFColor.ORANGE.index);
    styleBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    //  CREATE HEADING 2
    XSSFRow rheading2 = shet1.createRow(0);
    rheading2.setHeightInPoints(25);
    XSSFCell cellxx1, cellxx2, cellxx3, cellxx4, cellxx5, cellxx6, cellxx7, cellxx8, cellxx9, cellxx10,
            cellxx11, cellxx12, cellxx13, cellxx14, cellxx15;
    cellxx1 = rheading2.createCell(0);
    cellxx2 = rheading2.createCell(1);
    cellxx3 = rheading2.createCell(2);
    cellxx4 = rheading2.createCell(3);
    cellxx5 = rheading2.createCell(4);
    cellxx6 = rheading2.createCell(5);
    cellxx7 = rheading2.createCell(6);
    cellxx8 = rheading2.createCell(7);
    cellxx9 = rheading2.createCell(8);
    cellxx10 = rheading2.createCell(9);
    cellxx11 = rheading2.createCell(10);
    cellxx12 = rheading2.createCell(11);
    cellxx13 = rheading2.createCell(12);
    //   cellxx14=rheading2.createCell(13);
    //   cellxx15=rheading2.createCell(14);

    cellxx1.setCellValue("COUNTY NAME");
    cellxx2.setCellValue("PARTNER NAME");
    cellxx3.setCellValue("DISTRICT NAME");
    cellxx4.setCellValue("GENDER");
    cellxx5.setCellValue("CONTRACEPTIVE METHOD");
    cellxx6.setCellValue("REFERRED TO A SERVICE POINT");
    cellxx7.setCellValue("GIVEN CONDOMS");
    cellxx8.setCellValue("SCREENED FOR TB");
    cellxx9.setCellValue("SCREENED FOR STIS");
    cellxx10.setCellValue("TESTED PARTNER");
    cellxx11.setCellValue("TESTED CHILDREN");
    cellxx12.setCellValue("DISCLOSED STATUS");
    // cellxx13.setCellValue("YEAR");
    // cellxx14.setCellValue("MONTH");
    cellxx13.setCellValue("AGE BRACKET");

    cellxx1.setCellStyle(styleBorder);
    cellxx2.setCellStyle(styleBorder);
    cellxx3.setCellStyle(styleBorder);
    cellxx4.setCellStyle(styleBorder);
    cellxx5.setCellStyle(styleBorder);
    cellxx6.setCellStyle(styleBorder);
    cellxx7.setCellStyle(styleBorder);
    cellxx8.setCellStyle(styleBorder);
    cellxx9.setCellStyle(styleBorder);
    cellxx10.setCellStyle(styleBorder);
    cellxx11.setCellStyle(styleBorder);
    cellxx12.setCellStyle(styleBorder);
    cellxx13.setCellStyle(styleBorder);
    //             cellxx14.setCellStyle(styleBorder);
    //             cellxx15.setCellStyle(styleBorder);

    pos = 1;

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

    XSSFFont fontx = wb.createFont();
    fontx.setColor(HSSFColor.DARK_BLUE.index);
    stylex.setFont(fontx);
    stylex.setWrapText(true);

    String getServices = "SELECT client_id,COUNTY,GENDER, bit_or(cm) AS CONTRACEPTIVE_METHOD,bit_or(sp) AS REFERRED_TO_SERVICE_POINT, "
            + "SUM(cg) AS CONDOMS_GIVEN ,bit_or(st) AS SCREENED_TB ,bit_or(ss) AS SCREENED_STIS,bit_or(tp) TESTED_PARTNER, "
            + "bit_or(tc) AS TESTED_CHILDREN,bit_or(ds) as DISCLOSED_STATUS,year AS pepfaryear,month as pepfarmonth,partnername as partner,districtname as district,AGEBRACKET FROM ( "
            + "SELECT personal_information.client_id as client_id,county.county_name as COUNTY,personal_information.gender as GENDER, "
            + " CASE " + " WHEN services_provided.contraceptive_method= 'YES' THEN 1 "
            + " WHEN services_provided.contraceptive_method= 'NO' THEN 0 " + "ELSE 'NONE' " + "END AS cm, "
            + "CASE " + " WHEN services_provided.rsp LIKE 'YES' THEN 1 "
            + " WHEN services_provided.rsp LIKE 'NO' THEN 0 " + "ELSE 'NONE' " + "END AS sp, "
            + " services_provided.cds_given AS cg, " + "CASE "
            + " WHEN services_provided.screened_tb='YES' THEN 1 "
            + " WHEN services_provided.screened_tb='NO' THEN 0 " + "ELSE 'NONE' " + "END AS st, " + "CASE "
            + " WHEN services_provided.screened_stis='YES' THEN 1 "
            + " WHEN services_provided.screened_stis='NO' THEN 0 " + "ELSE 'NONE' " + "END AS ss, " + "CASE "
            + " WHEN services_provided.tested_partner='YES' THEN 1 "
            + " WHEN services_provided.tested_partner='NO' THEN 0 " + "ELSE 'NONE' " + "END AS tp, " + "CASE "
            + " WHEN services_provided.tested_children= 'YES' THEN 1 "
            + " WHEN services_provided.tested_children= 'NO' THEN 0 " + "ELSE 'NONE' " + "END AS tc, " + "CASE "
            + " WHEN services_provided.disclosed_status= 'YES' THEN 1 "
            + " WHEN services_provided.disclosed_status= 'NO' THEN 0 " + "ELSE 'NONE' "
            + "END AS ds,services_provided.submission_month AS month,services_provided.submission_year as year,partner.partner_name as partnername,"
            + "district.district_name as districtname,"
            //            + " CASE "
            //            + " WHEN services_provided.submission_month=1 THEN 'JAN' " 
            //            + " WHEN services_provided.submission_month=2 THEN 'FEB' " 
            //            + " WHEN services_provided.submission_month=3 THEN 'MAR' "
            //            + " WHEN services_provided.submission_month=4 THEN 'APR' "
            //            + " WHEN services_provided.submission_month=5 THEN 'MAY' " 
            //            + " WHEN services_provided.submission_month=6 THEN 'JUN' " 
            //            + " WHEN services_provided.submission_month=7 THEN 'JUL' " 
            //            + " WHEN services_provided.submission_month=8 THEN 'AUG' " 
            //            + " WHEN services_provided.submission_month=9 THEN 'SEPT' " 
            //            + " WHEN services_provided.submission_month=10 THEN 'OCT' " 
            //            + " WHEN services_provided.submission_month=11 THEN 'NOV' " 
            //            + " WHEN services_provided.submission_month=12 THEN 'DEC'"
            //            + "ELSE 'NO MONTH' END AS MONTHNAME,"
            + "CASE"
            + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9'"
            + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14'"
            + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'"
            + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'"
            + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49'"
            + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above'"
            + " ELSE 'NO DATE OF BIRTH' " + "   END AS AGEBRACKET "
            + " FROM personal_information JOIN partner ON personal_information.partner_id=partner.partner_id "
            + "" + ""
            + " JOIN  (district JOIN county ON county.county_id=district.county_id) ON district.district_id=personal_information.district_id "
            + "           JOIN services_provided ON services_provided.client_id=personal_information.client_id"
            + "           WHERE services_provided.submission_month>='" + startdate
            + "' && services_provided.submission_month<='" + enddate + "' "
            + "&& services_provided.submission_year='" + pepfaryear
            + "' order by personal_information.client_id ) as temptbl"
            + " WHERE (cm>0 || sp>0 || cg>0 || st>0 || ss>0"
            + " || tp>0 || tc>0 || ds>0)  GROUP BY client_id ORDER BY client_id";
    conn.rs = conn.st.executeQuery(getServices);
    while (conn.rs.next()) {
        countyname = conn.rs.getString(2);
        gender = conn.rs.getString(3);
        contraceptive_method = conn.rs.getInt(4);
        rsp = conn.rs.getInt(5);
        cds_given = conn.rs.getInt(6);
        screened_tb = conn.rs.getInt(7);
        screened_stis = conn.rs.getInt(8);
        tested_partner = conn.rs.getInt(9);
        tested_children = conn.rs.getInt(10);
        disclosed_status = conn.rs.getInt(11);
        datekey = Integer.parseInt(conn.rs.getInt(12) + "" + conn.rs.getInt(13));
        partner = conn.rs.getString(14);
        district = conn.rs.getString(15);
        //       monthname=conn.rs.getString(16);
        agebracket = conn.rs.getString(16);
        if (contraceptive_method > 0 || rsp > 0 || cds_given > 0 || screened_tb > 0 || screened_stis > 0
                || tested_partner > 0 || tested_children > 0 || disclosed_status > 0) {
            //  CREATE ROW AND ADD DATA TO THE DATA CELLS======================
            incrementor++;
            XSSFRow data = shet1.createRow(pos);
            data.setHeightInPoints(25);
            XSSFCell cellx1, cellx2, cellx3, cellx4, cellx5, cellx6, cellx7, cellx8, cellx9, cellx10, cellx11,
                    cellx12, cellx13, cellx14, cellx15;
            cellx1 = data.createCell(0);
            cellx2 = data.createCell(1);
            cellx3 = data.createCell(2);
            cellx4 = data.createCell(3);
            cellx5 = data.createCell(4);
            cellx6 = data.createCell(5);
            cellx7 = data.createCell(6);
            cellx8 = data.createCell(7);
            cellx9 = data.createCell(8);
            cellx10 = data.createCell(9);
            cellx11 = data.createCell(10);
            cellx12 = data.createCell(11);
            cellx13 = data.createCell(12);
            //    cellx14=data.createCell(13);
            //    cellx15=data.createCell(14); 

            cellx1.setCellValue(countyname);
            cellx2.setCellValue(partner);
            cellx3.setCellValue(district);
            cellx4.setCellValue(gender);
            cellx5.setCellValue(contraceptive_method);
            cellx6.setCellValue(rsp);
            cellx7.setCellValue(cds_given);
            cellx8.setCellValue(screened_tb);
            cellx9.setCellValue(screened_stis);
            cellx10.setCellValue(tested_partner);
            cellx11.setCellValue(tested_children);
            cellx12.setCellValue(disclosed_status);
            // cellx13.setCellValue(pepfaryear);
            cellx13.setCellValue(agebracket);
            // cellx15.setCellValue();

            cellx1.setCellStyle(stylex);
            cellx2.setCellStyle(stylex);
            cellx3.setCellStyle(stylex);
            cellx4.setCellStyle(stylex);
            cellx5.setCellStyle(stylex);
            cellx6.setCellStyle(stylex);
            cellx7.setCellStyle(stylex);
            cellx8.setCellStyle(stylex);
            cellx9.setCellStyle(stylex);
            cellx10.setCellStyle(stylex);
            cellx11.setCellStyle(stylex);
            cellx12.setCellStyle(stylex);
            cellx13.setCellStyle(stylex);
            //             cellx14.setCellStyle(stylex);
            //             cellx15.setCellStyle(stylex); 

            pos++;
        }

    }

    if (conn.rs != null) {
        conn.rs.close();
    }
    if (conn.st != null) {
        conn.st.close();
    }
    if (conn.rs1 != null) {
        conn.rs1.close();
    }
    if (conn.st1 != null) {
        conn.st1.close();
    }
    if (conn.rs2 != null) {
        conn.rs2.close();
    }
    if (conn.st2 != null) {
        conn.st2.close();
    }
    if (conn.st3 != null) {
        conn.st3.close();
    }
    if (conn.pst != null) {
        conn.pst.close();
    }
    if (conn.pst != null) {
        conn.pst.close();
    }
    if (conn.pst1 != null) {
        conn.pst1.close();
    }
    if (conn.pst1 != null) {
        conn.pst1.close();
    }
    if (conn.conn != null) {
        conn.conn.close();
    }

    if (incrementor > 0) {
        // write it as an excel attachment
        if (session.getAttribute("period").toString().equals("10-12")) {
            pepfaryear++;
        }
        ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
        wb.write(outByteStream);
        byte[] outArray = outByteStream.toByteArray();
        response.setContentType("application/ms-excel");
        response.setContentLength(outArray.length);
        response.setHeader("Expires:", "0"); // eliminates browser caching
        response.setHeader("Content-Disposition",
                "attachment; filename=PWP_SERVICES_PROVIDED_REPORT_FOR_PEPFAR_YEAR_" + pepfaryear + "(" + period
                        + ").xlsm");
        OutputStream outStream = response.getOutputStream();
        outStream.write(outArray);
        outStream.flush();

        pkg.close();
    } else {
        session.setAttribute("kePMSError",
                "<font color=\"red\"><b>NO DATA WITHIN THE SELECTED PARAMETERS.</b></font>");
        response.sendRedirect("kePMS.jsp");
    }
}

From source file:reports.notReached.java

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException, SQLException, InvalidFormatException {
    session = request.getSession();//from w  w w  .  j  av  a 2  s  .co  m
    dbConn conn = new dbConn();

    position = 1;

    String reportHeader[] = ("COUNTY NAME ,PARTNER NAME,DISTRICT NAME, DIC NAME, GROUP NAME,CLIENT FULL NAME ,"
            + " CCC NO. , MOBILE NUMBER , GENDER , DATE OF BIRTH , MARITAL STATUS , EMPLOYMENT STATUS ,"
            + "EDUCATION LEVEL , ART STATUS , SERVICE PROVIDER NAME , HEALTH FACILITY, LESSONS ATTENDED,AGE BRACKET, Knowledge of HIV Status,"
            + "Partner HIV Testing,Child HIV Testing,Discordance,HIV Disclosure,Risk Factor/Reduction,Condom Use,"
            + "Alcohol and Substance Abuse,Adherence,STIs,Family Planning,PMTCT,TB").split(",");

    //    COPY FILE TO BE WRITTEN TO 
    Path original = Paths.get(getServletContext().getRealPath("/NOT_ACHIEVED_TEMPLATE.xlsm")); //original file
    Path destination = Paths.get(getServletContext().getRealPath("/NOT_ACHIEVED_TEMPLATE_1.xlsm")); //new file
    System.out.println("origin :  " + original + " destination    :  " + destination);
    try {
        Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING);
        System.out.println("file copied----------------");
    } catch (IOException x) {
        //catch all for IO problems
        System.out.println("fine not copied");
    }

    String allpath = getServletContext().getRealPath("/NOT_ACHIEVED_TEMPLATE_1.xlsm");

    //            ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^
    XSSFWorkbook wb1;
    OPCPackage pkg = OPCPackage.open(allpath);

    wb1 = new XSSFWorkbook(pkg);

    SXSSFWorkbook wb = new SXSSFWorkbook(wb1, 100);

    //            ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^
    //   HSSFWorkbook wb=new HSSFWorkbook();
    Sheet shet1 = wb.getSheet("Sheet1");
    Font font = wb.createFont();
    font.setFontHeightInPoints((short) 18);
    font.setFontName("Arial Black");
    font.setColor((short) 0000);
    CellStyle style = wb.createCellStyle();
    style.setFont(font);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    Font font2 = wb.createFont();
    font2.setFontName("Arial Black");
    font2.setColor((short) 0000);
    CellStyle style2 = wb.createCellStyle();
    style2.setFont(font2);

    CellStyle 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);

    for (int i = 0; i <= reportHeader.length; i++) {
        shet1.setColumnWidth(i, 4000);
    }

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

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

    Font fontx = wb.createFont();
    fontx.setColor(HSSFColor.DARK_BLUE.index);
    stylex.setFont(fontx);
    stylex.setWrapText(true);

    Cell cell;
    Row rw0 = shet1.createRow(0);
    rw0.setHeightInPoints(30);
    rw0.setRowStyle(style2);

    for (int i = 0; i <= (reportHeader.length - 1); i++) {
        cell = rw0.createCell(i);
        cell.setCellValue(reportHeader[i]);
        cell.setCellStyle(stylex);
    }

    String getClients = "SELECT county.county_name,partner.partner_name,district.district_name,dic.dic_name,"
            + "groups.group_name,personal_information.fname,personal_information.mname,personal_information.lname,"
            + "personal_information.ccc_no,personal_information.mobile_no, personal_information.gender,"
            + "personal_information.dob,marital_status.name,employment_status.name,education_levels.name,"
            + "art_status.name,"
            + "service_provider.fname,service_provider.mname,service_provider.lname,health_facility.hf_name, "
            + "personal_information.lessons_attended," + "CASE"
            + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9'"
            + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14'"
            + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'"
            + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'"
            + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49'"
            + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above'"
            + " ELSE 'NO DATE OF BIRTH'" + "   END AS AGEBRACKET," + "CASE "
            + "when personal_information.gender LIKE 'Female' THEN 'F' "
            + "when personal_information.gender LIKE 'Male' THEN 'M' " + "ELSE 'NO SEX' "
            + "END AS SEX,s1,s2,s3,s4,s5,s6,s7,s8,s9,s10,s11,s12,s13 " + " FROM personal_information "
            + " LEFT JOIN groups ON personal_information.group_id=groups.group_id "
            + " LEFT JOIN dic ON personal_information.dic_id=dic.dic_id "
            + " LEFT JOIN service_provider ON personal_information.provider_id=service_provider.provider_id "
            + " LEFT JOIN health_facility ON personal_information.hf_id=health_facility.hf_id "
            + " LEFT JOIN district ON personal_information.district_id=district.district_id "

            + " LEFT JOIN marital_status ON personal_information.marital_status=marital_status.id "
            + " LEFT JOIN employment_status ON personal_information.employment_status=employment_status.id "
            + " LEFT JOIN education_levels ON personal_information.education_level=education_levels.id "
            + " LEFT JOIN art_status ON personal_information.art_status=art_status.id "
            + " LEFT JOIN register ON personal_information.client_id=register.client_id "

            + " LEFT JOIN partner ON personal_information.partner_id=partner.partner_id "
            + " LEFT JOIN county ON district.county_id=county.county_id "

            + " WHERE (personal_information.completionmonth=0 || personal_information.completionyear=0) "
            + " ORDER BY partner.partner_name,county.county_name,district.district_name,dic.dic_name,"
            + "groups.group_name";
    System.out.println("query is : " + getClients);
    conn.rs = conn.st.executeQuery(getClients);
    while (conn.rs.next()) {
        //     ADD THE DATA TO EXCEL HERE 
        groupName = DICName = districtName = partnerName = countyName = agebracket = lessons_attended = year = "";
        clientFname = clientMname = clientLname = ccc_no = mobile_no = gender = dob = marital_status = "";
        location = employment_status = education_level = under_18 = ovc_children = hiv_year = art_status = "";
        registration_date = approved_by = designation = approval_date = "";
        SPFname = SPMname = SPLname = SPFullName = healthFacility = "";
        s1 = s2 = s3 = s4 = s5 = s6 = s7 = s8 = s9 = s10 = s11 = s12 = s13 = "";
        if (conn.rs.getString(1) != null) {
            countyName = conn.rs.getString(1);
        }
        if (conn.rs.getString(2) != null) {
            partnerName = conn.rs.getString(2);
        }
        if (conn.rs.getString(3) != null) {
            districtName = conn.rs.getString(3);
        }
        if (conn.rs.getString(4) != null) {
            DICName = conn.rs.getString(4);
        } else {
            DICName = "NO DIC";
        }

        if (conn.rs.getString(5) != null) {
            groupName = conn.rs.getString(5);
        } else {
            groupName = "Individual";
        }

        if (conn.rs.getString(6) != null) {
            clientFname = conn.rs.getString(6);
        }
        if (conn.rs.getString(7) != null) {
            clientMname = conn.rs.getString(7);
        }
        if (conn.rs.getString(8) != null) {
            clientLname = conn.rs.getString(8);
        }
        if (conn.rs.getString(9) != null) {
            ccc_no = conn.rs.getString(9);
        }
        if (conn.rs.getString(10) != null) {
            mobile_no = conn.rs.getString(10);
        }
        if (conn.rs.getString(11) != null) {
            gender = conn.rs.getString(11);
        }
        if (conn.rs.getString(12) != null) {
            dob = conn.rs.getString(12);
        }
        if (conn.rs.getString(13) != null) {
            marital_status = conn.rs.getString(13);
        }
        if (conn.rs.getString(14) != null) {
            employment_status = conn.rs.getString(14);
        }
        if (conn.rs.getString(15) != null) {
            education_level = conn.rs.getString(15);
        }
        if (conn.rs.getString(16) != null) {
            art_status = conn.rs.getString(16);
        }
        if (conn.rs.getString(17) != null) {
            SPFname = conn.rs.getString(17);
        }
        if (conn.rs.getString(18) != null) {
            SPMname = conn.rs.getString(18);
        }
        if (conn.rs.getString(19) != null) {
            SPLname = conn.rs.getString(19);
        }
        if (conn.rs.getString(20) != null) {
            healthFacility = conn.rs.getString(20);
        }
        if (conn.rs.getString(21) != null) {
            lessons_attended = conn.rs.getString(21);
        }
        if (conn.rs.getString(22) != null) {
            agebracket = conn.rs.getString(22);
        }
        if (conn.rs.getString(23) != null) {
            gender = conn.rs.getString(23);
        }

        if (conn.rs.getString(24) != null) {
            s1 = conn.rs.getString(24);
        }
        if (conn.rs.getString(25) != null) {
            s2 = conn.rs.getString(25);
        }
        if (conn.rs.getString(26) != null) {
            s3 = conn.rs.getString(26);
        }
        if (conn.rs.getString(27) != null) {
            s4 = conn.rs.getString(27);
        }
        if (conn.rs.getString(28) != null) {
            s5 = conn.rs.getString(28);
        }
        if (conn.rs.getString(29) != null) {
            s6 = conn.rs.getString(29);
        }
        if (conn.rs.getString(30) != null) {
            s7 = conn.rs.getString(30);
        }
        if (conn.rs.getString(31) != null) {
            s8 = conn.rs.getString(31);
        }
        if (conn.rs.getString(32) != null) {
            s9 = conn.rs.getString(32);
        }
        if (conn.rs.getString(33) != null) {
            s10 = conn.rs.getString(33);
        }
        if (conn.rs.getString(34) != null) {
            s11 = conn.rs.getString(34);
        }
        if (conn.rs.getString(35) != null) {
            s12 = conn.rs.getString(35);
        }
        if (conn.rs.getString(36) != null) {
            s13 = conn.rs.getString(36);
        }

        if (s1.equals("5")) {
            s1 = "";
        }
        if (s1.equals("2")) {
            s1 = "0";
        }
        if (s2.equals("5")) {
            s2 = "";
        }
        if (s2.equals("2")) {
            s2 = "0";
        }
        if (s3.equals("5")) {
            s3 = "";
        }
        if (s3.equals("2")) {
            s3 = "0";
        }
        if (s4.equals("5")) {
            s4 = "";
        }
        if (s4.equals("2")) {
            s4 = "0";
        }
        if (s5.equals("5")) {
            s5 = "";
        }
        if (s5.equals("2")) {
            s5 = "0";
        }
        if (s6.equals("5")) {
            s6 = "";
        }
        if (s6.equals("2")) {
            s6 = "0";
        }
        if (s7.equals("5")) {
            s7 = "";
        }
        if (s7.equals("2")) {
            s7 = "0";
        }
        if (s8.equals("5")) {
            s8 = "";
        }
        if (s8.equals("2")) {
            s8 = "0";
        }
        if (s9.equals("5")) {
            s9 = "";
        }
        if (s9.equals("2")) {
            s9 = "0";
        }
        if (s10.equals("5")) {
            s10 = "";
        }
        if (s10.equals("2")) {
            s10 = "0";
        }
        if (s11.equals("5")) {
            s11 = "";
        }
        if (s11.equals("2")) {
            s11 = "0";
        }
        if (s12.equals("5")) {
            s12 = "";
        }
        if (s12.equals("2")) {
            s12 = "0";
        }
        if (s13.equals("5")) {
            s13 = "";
        }
        if (s13.equals("2")) {
            s13 = "0";
        }

        if (clientMname.equals(clientLname)) {
            clientMname = "";
        }

        if (SPMname.equals(SPLname)) {
            SPMname = "";
        }

        SPFullName = SPFname + " " + SPMname + " " + SPLname;
        clientFullName = clientFname + " " + clientMname + " " + clientLname;

        String rawData[] = (countyName + "," + partnerName + "," + districtName + "," + DICName + ","
                + groupName + "," + clientFullName + "," + ccc_no + "," + mobile_no + "," + gender + "," + dob
                + "," + marital_status + "," + employment_status + "," + education_level + "," + art_status
                + "," + SPFullName + "," + healthFacility + "," + lessons_attended + "," + agebracket + "," + s1
                + "," + s2 + "," + s3 + "," + s4 + "," + s5 + "," + s6 + "," + s7 + "," + s8 + "," + s9 + ","
                + s10 + "," + s11 + "," + s12 + "," + s13).split(",");

        Row rw1 = shet1.createRow(position);
        rw1.setHeightInPoints(25);
        rw1.setRowStyle(style2);

        for (int i = 0; i <= (reportHeader.length - 1); i++) {
            cell = rw1.createCell(i);
            cell.setCellStyle(styleBorder);
        }

        for (int i = 0; i <= (rawData.length - 1); i++) {
            cell = rw1.getCell(i);
            cell.setCellValue(rawData[i]);
        }

        position++;
        System.out.println("at position : " + position);
    }

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

    pkg.close();
}

From source file:reports.Reached_OthersMessages.java

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException, SQLException, InvalidFormatException {
    session = request.getSession();//from   www.  java2 s . c  o  m
    dbConn conn = new dbConn();

    ouputyear = outputMonth = "";

    if (session.getAttribute("PepfarYear") != null) {
        year = Integer.parseInt(session.getAttribute("PepfarYear").toString());
        quarter = Integer.parseInt(session.getAttribute("period").toString());
        //      year=2015;
        //      quarter=2;  
        System.out.println("dates are : " + year + " quarter : " + quarter);
        if (quarter == 4) {
            reportYear = year - 1;
            startmonth = "10";
            endMonth = "12";
            prevReportYear = reportYear;
            prevReportMonth = "09";

        } else {
            reportYear = year;
            if (quarter == 1) {
                startmonth = "01";
                endMonth = "03";
                prevReportYear = reportYear - 1;
                prevReportMonth = "12";
            }
            if (quarter == 2) {
                startmonth = "04";
                endMonth = "06";
                prevReportYear = reportYear;
                prevReportMonth = "03";
            }
            if (quarter == 3) {
                startmonth = "07";
                endMonth = "09";
                prevReportYear = reportYear;
                prevReportMonth = "06";
            }
        }

        startDate = "" + startmonth + "/01/" + reportYear + "";
        endDate = "" + endMonth + "/31/" + reportYear + "";

        startDate1 = "" + reportYear + "-" + startmonth + "-01";
        endDate1 = "" + reportYear + "-" + endMonth + "-31";

        endYearMonth = prevReportYear + "" + prevReportMonth;
        position = 1;

        //      ouputyear=""+reportYear;
        if (quarter == 1) {
            outputMonth = "Jan_March_" + reportYear;
        }
        if (quarter == 2) {
            outputMonth = "Apr_Jun_" + reportYear;
        }
        if (quarter == 3) {
            outputMonth = "July_Sept_" + reportYear;
        }
        if (quarter == 4) {
            outputMonth = "Oct_Dec_" + reportYear;
        }

        String reportHeader[] = ("COUNTY NAME ,PARTNER NAME,DISTRICT NAME,DIC NAME, GROUP NAME,CLIENT NAME , AGE BRACKET, GENDER,YEAR,MONTH")
                .split(",");

        //    COPY FILE TO BE WRITTEN TO 
        Path original = Paths.get(getServletContext().getRealPath("/OTHER_MESSAGES.xlsm")); //original file
        Path destination = Paths.get(getServletContext().getRealPath("/OTHER_MESSAGES_1.xlsm")); //new file
        System.out.println("origin :  " + original + " destination    :  " + destination);
        try {
            Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING);
            System.out.println("file copied----------------");
        } catch (IOException x) {
            //catch all for IO problems
            System.out.println("fine not copied");
        }

        String allpath = getServletContext().getRealPath("/OTHER_MESSAGES_1.xlsm");

        //            ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^
        XSSFWorkbook wb;
        OPCPackage pkg = OPCPackage.open(allpath);

        wb = new XSSFWorkbook(pkg);

        //            ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^
        //   HSSFWorkbook wb=new HSSFWorkbook();
        XSSFSheet shet1 = wb.getSheet("Sheet1");
        XSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) 18);
        font.setFontName("Arial Black");
        font.setColor((short) 0000);
        CellStyle style = wb.createCellStyle();
        style.setFont(font);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        XSSFFont font2 = wb.createFont();
        font2.setFontName("Arial Black");
        font2.setColor((short) 0000);
        CellStyle style2 = wb.createCellStyle();
        style2.setFont(font2);

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

        for (int i = 0; i <= reportHeader.length; i++) {
            shet1.setColumnWidth(i, 4000);
        }

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

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

        XSSFFont fontx = wb.createFont();
        fontx.setColor(HSSFColor.DARK_BLUE.index);
        stylex.setFont(fontx);
        stylex.setWrapText(true);

        XSSFCell cell;
        XSSFRow rw0 = shet1.createRow(0);
        rw0.setHeightInPoints(30);
        rw0.setRowStyle(style2);

        for (int i = 0; i <= (reportHeader.length - 1); i++) {
            cell = rw0.createCell(i);
            cell.setCellValue(reportHeader[i]);
            cell.setCellStyle(stylex);
        }

        String query = "SELECT CLIENT, " + "" + "if( (dateRegister BETWEEN '" + startDate1 + "' AND '"
                + endDate1 + "'),dateRegister,dateAdherence) REPORTDATE," + " "
                + "dateRegister,dateAdherence, countyName, partnerName, districtName, AGEBRACKET, SEX,fname,mname,lname,GROUPNAME,DIC FROM ("
                + "SELECT DISTINCT(tempData.clientID) as CLIENT,if( (reg2Date BETWEEN '" + startDate1
                + "' AND '" + endDate1 + "'),reg2Date,'0') dateRegister,if( (AdherenceDate BETWEEN '"
                + startDate1 + "' AND '" + endDate1
                + "'),AdherenceDate,'0') dateAdherence, countyName, partnerName, districtName, AGEBRACKET, SEX,fname,mname,lname,GROUPNAME,DIC FROM ("
                + "SELECT DISTINCT(personal_information.client_id) as clientID,STR_TO_DATE(register2.date,'%m/%d/%Y') as reg2Date,STR_TO_DATE(adherence.date_of_session,'%m/%d/%Y') as AdherenceDate,"
                + "             CONCAT(personal_information.completionyear,personal_information.completionmonth) AS YEARMONTH,"
                + "county.county_name AS countyName,partner.partner_name AS partnerName,district.district_name AS districtName,"
                + "CASE "
                + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9' "
                + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14' "
                + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19' "
                + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24' "
                + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49' "
                + "      WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above' "
                + "               ELSE 'NO DATE OF BIRTH'" + " END AS AGEBRACKET," + "               CASE  "
                + "when personal_information.gender LIKE 'Female' THEN 'F'  "
                + "when personal_information.gender LIKE 'Male' THEN 'M'  " + "ELSE 'NO SEX'  "
                + "END AS SEX,personal_information.fname as fname,personal_information.mname as mname ,personal_information.lname as lname,"
                + "groups.group_name as GROUPNAME,dic.dic_name as DIC " + "         FROM personal_information "
                + "LEFT JOIN register2 ON personal_information.client_id=register2.client_id "
                + "LEFT JOIN adherence ON personal_information.client_id=adherence.client_id "
                + "LEFT JOIN groups ON personal_information.group_id=groups.group_id "
                + "LEFT JOIN dic ON personal_information.dic_id=dic.dic_id "
                + "LEFT JOIN district ON personal_information.district_id=district.district_id "
                + "LEFT JOIN partner ON personal_information.partner_id=partner.partner_id "
                + "LEFT JOIN county ON district.county_id=county.county_id "
                + "WHERE personal_information.completionyear>0 " + "&& (register2.datekey BETWEEN '"
                + startDate1.replace("-", "") + "' AND '" + endDate1.replace("-", "")
                + "' || STR_TO_DATE(adherence.date_of_session,'%m/%d/%Y') BETWEEN STR_TO_DATE('" + startDate
                + "','%m/%d/%Y') AND STR_TO_DATE('" + endDate + "','%m/%d/%Y'))" + "         HAVING YEARMONTH<="
                + endYearMonth + ") AS tempData GROUP BY tempData.clientID) AS finalTable ";
        System.out.println(query);
        conn.rs = conn.st.executeQuery(query);
        while (conn.rs.next()) {
            countyName = conn.rs.getString(5);
            partnerName = conn.rs.getString(6);
            districtName = conn.rs.getString(7);
            reportDate = conn.rs.getString(2);
            ageBracket = conn.rs.getString(8);
            gender = conn.rs.getString(9);
            fname = conn.rs.getString(10);
            mname = conn.rs.getString(11);
            lname = conn.rs.getString(12);
            if (conn.rs.getString(13) != null) {
                groupName = conn.rs.getString(13);
            } else {
                groupName = "Individual";
            }
            if (conn.rs.getString(14) != null) {
                groupName = conn.rs.getString(14);
            } else {
                dic_name = "NO DIC";
            }

            if (mname.equals(lname)) {
                mname = "";
            }
            fullName = fname + " " + mname + " " + lname;
            String dateSplit[] = reportDate.split("-");

            if (dateSplit[1].equals("01")) {
                month = "Jan";
            }
            if (dateSplit[1].equals("02")) {
                month = "Feb";
            }
            if (dateSplit[1].equals("03")) {
                month = "Mar";
            }
            if (dateSplit[1].equals("04")) {
                month = "Apr";
            }
            if (dateSplit[1].equals("05")) {
                month = "May";
            }
            if (dateSplit[1].equals("06")) {
                month = "Jun";
            }
            if (dateSplit[1].equals("07")) {
                month = "Jul";
            }
            if (dateSplit[1].equals("08")) {
                month = "Aug";
            }
            if (dateSplit[1].equals("09")) {
                month = "Sept";
            }
            if (dateSplit[1].equals("10")) {
                month = "Oct";
            }
            if (dateSplit[1].equals("11")) {
                month = "Nov";
            }
            if (dateSplit[1].equals("12")) {
                month = "Dec";
            }

            year2 = dateSplit[0];

            String data[] = (countyName + "," + partnerName + "," + districtName + "," + dic_name + ","
                    + groupName + "," + fullName + "," + ageBracket + "," + gender + "," + year2 + "," + month)
                            .split(",");

            XSSFRow rw1 = shet1.createRow(position);
            rw1.setHeightInPoints(25);
            rw1.setRowStyle(style2);

            for (int i = 0; i <= (data.length - 1); i++) {
                cell = rw1.createCell(i);
                cell.setCellValue(data[i]);
                cell.setCellStyle(styleBorder);
            }

            position++;

        }

        IdGenerator CRT = new IdGenerator();
        ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
        wb.write(outByteStream);
        byte[] outArray = outByteStream.toByteArray();
        response.setContentType("application/ms-excel");
        response.setContentLength(outArray.length);
        response.setHeader("Expires:", "0"); // eliminates browser caching
        response.setHeader("Content-Disposition",
                "attachment; filename=PWP_REACHED_WITH_OTHER_MESSAGES_REPORT_FOR_" + outputMonth
                        + "_CREATED_ON_" + CRT.timestamp() + ".xlsm");
        OutputStream outStream = response.getOutputStream();
        outStream.write(outArray);
        outStream.flush();

        pkg.close();

    } else {
        session.setAttribute("kePMSError", "<font color=\"red\">Error : Please try again.</font>");
        response.sendRedirect("kePMS.jsp");
    }
}

From source file:sailpoint.services.task.genericImport.ExcelSaxImport.java

License:Apache License

/**
 * Initiates the processing of the XLS workbook file to CSV.
 *
 * @throws IOException// w  w  w  .  j  av a 2s .com
 * @throws OpenXML4JException
 * @throws ParserConfigurationException
 * @throws SAXException
 */

public void process() throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {

    OPCPackage xlsxPackage = OPCPackage.open(xlFile.getPath(), PackageAccess.READ);

    ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(xlsxPackage);
    XSSFReader xssfReader = new XSSFReader(xlsxPackage);
    StylesTable styles = xssfReader.getStylesTable();
    XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
    int index = 0;
    while (iter.hasNext()) {
        InputStream stream = iter.next();
        if (sheetName.equals(iter.getSheetName()) || sheetName == null) {
            if (log.isDebugEnabled())
                log.debug("Sheet name: " + sheetName + " (" + index + ")");
            processSheet(styles, strings, stream);
        }
        stream.close();
        ++index;
    }
    xlsxPackage.close();

}

From source file:uk.ac.abdn.liftsharing.batch.io.test.ExcelWorkSheetHandlerTest.java

License:Open Source License

/**
 * @param args// w ww  .  ja  va2 s  .  com
 * @throws Exception
 */
public static void main(String[] args) throws Exception {
    String SAMPLE_PERSON_DATA_FILE_PATH = "D:\\Sample-Person-Data.xlsx";

    // Input File initialize
    File file = new File(SAMPLE_PERSON_DATA_FILE_PATH);
    InputStream inputStream = new FileInputStream(file);

    // Excel Cell Mapping
    Map<String, String> cellMapping = new HashMap<String, String>();
    cellMapping.put("HEADER", "Person Id,Name,Height,Email Address,DOB,Salary");
    cellMapping.put("A", "personId");
    cellMapping.put("B", "name");
    cellMapping.put("C", "height");
    cellMapping.put("D", "emailId");
    cellMapping.put("E", "dob");
    cellMapping.put("F", "salary");

    // The package open is instantaneous, as it should be.
    OPCPackage pkg = null;
    try {

        ExcelWorkSheetHandler<PersonVO> workSheetHandler = new ExcelWorkSheetHandler<PersonVO>(PersonVO.class,
                cellMapping);

        pkg = OPCPackage.open(inputStream);

        ExcelSheetCallback sheetCallback = new ExcelSheetCallback() {
            private int sheetNumber = 0;

            @Override
            public void endSheet() {
                System.out.println("Processing completed for sheet number=" + sheetNumber);
            }

            @Override
            public void startSheet(int sheetNum) {
                this.sheetNumber = sheetNum;

            }
        };

        System.out.println("Constructor: pkg, workSheetHandler, sheetCallback");
        ExcelReader example1 = new ExcelReader(pkg, workSheetHandler, sheetCallback);
        example1.process();

        if (workSheetHandler.getValueList().isEmpty()) {
            // No data present
            LOG.error("sHandler.getValueList() is empty");
        } else {

            LOG.info(workSheetHandler.getValueList().size()
                    + " no. of records read from given excel worksheet successfully.");

            // Displaying data ead from Excel file
            displayPersonList(workSheetHandler.getValueList());
        }

        System.out.println("\nConstructor: filePath, workSheetHandler, sheetCallback");
        ExcelReader example2 = new ExcelReader(SAMPLE_PERSON_DATA_FILE_PATH, workSheetHandler, sheetCallback);
        example2.process();

        System.out.println("\nConstructor: file, workSheetHandler, sheetCallback");
        ExcelReader example3 = new ExcelReader(file, workSheetHandler, null);
        example3.process();

    } catch (RuntimeException are) {
        LOG.error(are.getMessage(), are.getCause());
    } catch (InvalidFormatException ife) {
        LOG.error(ife.getMessage(), ife.getCause());
    } catch (IOException ioe) {
        LOG.error(ioe.getMessage(), ioe.getCause());
    } finally {
        IOUtils.closeQuietly(inputStream);
        try {
            if (null != pkg) {
                pkg.close();
            }
        } catch (IOException e) {
            // just ignore IO exception
        }
    }
}

From source file:uk.ac.abdn.liftsharing.batch.io.test.ExcelWorkSheetRowCallbackHandlerTest.java

License:Open Source License

public static void main(String[] args) throws Exception {

    String SAMPLE_PERSON_DATA_FILE_PATH = "D:\\Sample-Person-Data.xlsx";

    File file = new File(SAMPLE_PERSON_DATA_FILE_PATH);
    InputStream inputStream = new FileInputStream(file);

    // The package open is instantaneous, as it should be.
    OPCPackage pkg = null;
    try {//from w ww. j a  v a  2 s.co  m
        ExcelWorkSheetRowCallbackHandler sheetRowCallbackHandler = new ExcelWorkSheetRowCallbackHandler(
                new ExcelRowContentCallback() {

                    @Override
                    public void processRow(int rowNum, Map<String, String> map) {

                        // Do any custom row processing here, such as save
                        // to database
                        // Convert map values, as necessary, to dates or
                        // parse as currency, etc
                        System.out.println("rowNum=" + rowNum + ", map=" + map);

                    }

                });

        pkg = OPCPackage.open(inputStream);

        ExcelSheetCallback sheetCallback = new ExcelSheetCallback() {
            private int sheetNumber = 0;

            @Override
            public void startSheet(int sheetNum) {
                this.sheetNumber = sheetNum;

            }

            @Override
            public void endSheet() {
                System.out.println("Processing completed for sheet number=" + sheetNumber);
            }
        };

        System.out.println("Constructor: pkg, sheetRowCallbackHandler, sheetCallback");
        ExcelReader example1 = new ExcelReader(pkg, sheetRowCallbackHandler, sheetCallback);
        example1.process();

        System.out.println("\nConstructor: filePath, sheetRowCallbackHandler, sheetCallback");
        ExcelReader example2 = new ExcelReader(SAMPLE_PERSON_DATA_FILE_PATH, sheetRowCallbackHandler,
                sheetCallback);
        example2.process();

        System.out.println("\nConstructor: file, sheetRowCallbackHandler, sheetCallback");
        ExcelReader example3 = new ExcelReader(file, sheetRowCallbackHandler, null);
        example3.process();

    } catch (RuntimeException are) {
        LOG.error(are.getMessage(), are.getCause());
    } catch (InvalidFormatException ife) {
        LOG.error(ife.getMessage(), ife.getCause());
    } catch (IOException ioe) {
        LOG.error(ioe.getMessage(), ioe.getCause());
    } finally {
        IOUtils.closeQuietly(inputStream);
        try {
            if (null != pkg) {
                pkg.close();
            }
        } catch (IOException e) {
            // just ignore IO exception
        }
    }
}

From source file:uk.ac.ucl.cs.cmic.giftcloud.uploader.ExcelWriter.java

License:Open Source License

@Override
protected void saveFile(final File file) throws IOException {
    if (spreadsheetPassword.isPresent() && StringUtils.isNotBlank(new String(spreadsheetPassword.get()))) {
        try {/*  ww w  .  ja va2  s  .  c om*/
            // Write the unencrypted spreadsheet to an in-memory stream
            final ByteArrayOutputStream baos = new ByteArrayOutputStream();
            workbook.write(baos);
            baos.close();

            // Create objects for encryption
            POIFSFileSystem fs = new POIFSFileSystem();
            EncryptionInfo info = new EncryptionInfo(fs, EncryptionMode.agile);
            Encryptor enc = info.getEncryptor();

            // Set the password
            enc.confirmPassword(new String(spreadsheetPassword.get()));

            // Open the in-memory spreadsheet
            InputStream inputStream = new ByteArrayInputStream(baos.toByteArray());
            OPCPackage opc = OPCPackage.open(inputStream);
            OutputStream os = enc.getDataStream(fs);
            opc.save(os);
            opc.close();
            inputStream.close();

            // Write the encrypted Excel spreadsheet
            FileOutputStream fos = new FileOutputStream(file);
            fs.writeFilesystem(fos);
            fos.close();
        } catch (InvalidFormatException e) {
            throw new IOException(
                    "Unable to save the patient list file due to the following InvalidFormatException when reading the excel file:"
                            + e.getLocalizedMessage(),
                    e);
        } catch (GeneralSecurityException e) {
            throw new IOException(
                    "Unable to save the patient list file due to the following GeneralSecurityException when reading the excel file:"
                            + e.getLocalizedMessage(),
                    e);
        }
    } else {
        // Write the unencrypted spreadsheet to an in-memory stream
        final FileOutputStream fos = new FileOutputStream(file);
        workbook.write(fos);
        fos.close();
    }
}