Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook getSheet

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. getSheet

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFWorkbook getSheet.

Prototype

@Override
public XSSFSheet getSheet(String name)

Source Link

Document

Get sheet with the given name (case insensitive match)

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 av a2 s  . 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  ww  .  j  av  a2  s  .c o m*/
    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();//from w  w w . jav  a  2s.  c o  m
    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();/*from   www .  ja  v  a  2s  .  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.Reached_OthersMessages.java

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException, SQLException, InvalidFormatException {
    session = request.getSession();//from www  .java  2 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:rocky.sizecounter.SizeCounterUtil.java

License:Apache License

/**
 * Check the input file Path is the UTC or UTR.
 * /*from   w  w w.  j a  v  a 2s  .c  o m*/
 * @param filePath path of file will be checked
 * @param requiredSheetNames Excel file must contains these sheets
 * @return true if the filePath is UTC or UTR
 * @throws IOException when error reading file
 */
public static boolean isUTCFile(String filePath, String[] requiredSheetNames) {
    try {
        InputStream is = CommonUtil.loadResource(filePath);
        if (CommonUtil.getExtension(filePath).equals("xls")) {
            HSSFWorkbook wb = new HSSFWorkbook(is);

            // Check all required sheets
            for (String sheetName : requiredSheetNames) {
                if (wb.getSheet(sheetName) == null) {
                    return false;
                }
            }
        } else if (CommonUtil.getExtension(filePath).equals("xlsx")) {
            XSSFWorkbook xwb = new XSSFWorkbook(is);
            // Check all required sheets
            for (String sheetName : requiredSheetNames) {
                if (xwb.getSheet(sheetName) == null) {
                    return false;
                }
            }
        }
    } catch (IOException ioEx) {
        LOG.error("Could not check file '" + filePath + "'", ioEx);
        return false;
    }

    return true;
}

From source file:ru.jeene.zapretparser.controller.XLSXReportController.java

public void WriteReport(FullReport rep, String timestamp_csv) {
    try (FileInputStream inp = new FileInputStream(shab_name)) {
        XSSFWorkbook wb = new XSSFWorkbook(inp); // Declare XSSF WorkBook
        XSSFSheet sheet = wb.getSheet(" ");
        XSSFCellStyle cs1 = wb.createCellStyle();
        //cs1.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        cs1.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        cs1.setBorderTop(XSSFCellStyle.BORDER_THIN);
        cs1.setBorderRight(XSSFCellStyle.BORDER_THIN);
        cs1.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        cs1.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        cs1.setWrapText(true);//from w w w  .  j a  va 2 s . c  om
        cs1.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        XSSFFont f = wb.createFont();
        f.setBold(false);
        f.setFontName("Times New Roman");
        f.setFontHeightInPoints((short) 14);
        cs1.setFont(f);
        int cnt = 0;
        XSSFRow row;
        XSSFCell cell;
        for (Model_FullReport m : rep.getList()) {
            int cnt_cell = 0;

            row = sheet.getRow(t1_start - 1 + cnt);
            if (row == null) {
                row = sheet.createRow(t1_start - 1 + cnt);
            }
            //? URL
            cell = row.getCell(cnt_cell);
            if (cell == null) {
                cell = row.createCell(cnt_cell);
            }
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(m.getElement().getUrl());
            cell.setCellStyle(cs1);
            cnt_cell++;

            //
            cell = row.getCell(cnt_cell);
            if (cell == null) {
                cell = row.createCell(cnt_cell);
            }
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(m.getElement().getOrg());
            cell.setCellStyle(cs1);
            cnt_cell++;

            //? 
            cell = row.getCell(cnt_cell);
            if (cell == null) {
                cell = row.createCell(cnt_cell);
            }
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(m.getElement().getDoc());
            cell.setCellStyle(cs1);
            cnt_cell++;
            //
            cell = row.getCell(cnt_cell);
            if (cell == null) {
                cell = row.createCell(cnt_cell);
            }
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(m.getElement().getDate());
            cell.setCellStyle(cs1);
            cnt_cell++;

            //
            cell = row.getCell(cnt_cell);
            if (cell == null) {
                cell = row.createCell(cnt_cell);
            }
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(m.getResult().name());
            cell.setCellStyle(cs1);
            cnt_cell++;

            // (?)
            cell = row.getCell(cnt_cell);
            if (cell == null) {
                cell = row.createCell(cnt_cell);
            }
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(m.getResult().getDesc());
            cell.setCellStyle(cs1);
            cnt_cell++;

            // ()
            cell = row.getCell(cnt_cell);
            if (cell == null) {
                cell = row.createCell(cnt_cell);
            }
            cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue(m.getResult().getCode());
            cell.setCellStyle(cs1);
            cnt_cell++;
            cnt++;
        }
        // 
        row = sheet.getRow(0);
        cell = row.getCell(0);
        cell.setCellType(XSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(MAIN_ZAG_TEMPL + timestamp_csv);

        //   
        HashMap<ResponseResult, Model_NumberReport> map = rep.reportCountBytype();

        //  ?
        sheet = wb.getSheet("");

        //  
        cnt = 0;
        for (Map.Entry<ResponseResult, Model_NumberReport> entry : map.entrySet()) {
            ResponseResult key = entry.getKey();
            Model_NumberReport value = entry.getValue();

            int cnt_cell = 0;
            row = sheet.getRow(t0_start - 1 + cnt);
            if (row == null) {
                row = sheet.createRow(t0_start - 1 + cnt);
            }
            /*//
             cell = row.getCell(cnt_cell);
             if (cell == null) {
             cell = row.createCell(cnt_cell);
             }
             cell.setCellType(XSSFCell.CELL_TYPE_STRING);
             cell.setCellValue(key.name());
             cell.setCellStyle(cs1);
             cnt_cell++;*/

            // (?)
            cell = row.getCell(cnt_cell);
            if (cell == null) {
                cell = row.createCell(cnt_cell);
            }
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(key.getDesc());
            cell.setCellStyle(cs1);
            cnt_cell++;

            /*// ()
             cell = row.getCell(cnt_cell);
             if (cell == null) {
             cell = row.createCell(cnt_cell);
             }
             cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
             cell.setCellValue(key.getCode());
             cell.setCellStyle(cs1);
             cnt_cell++;*/
            // (?)
            cell = row.getCell(cnt_cell);
            if (cell == null) {
                cell = row.createCell(cnt_cell);
            }
            cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue(value.getNumber());
            cell.setCellStyle(cs1);
            cnt_cell++;
            // ()
            cell = row.getCell(cnt_cell);
            if (cell == null) {
                cell = row.createCell(cnt_cell);
            }
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(FormatUtils.FormatDoubleD(value.getPercent()));
            cell.setCellStyle(cs1);
            cnt_cell++;
            cnt++;
        }

        // 
        row = sheet.getRow(0);
        cell = row.getCell(0);
        cell.setCellType(XSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(MAIN_ZAG_TEMPL + timestamp_csv);

        //    HTTP
        map = rep.reportCountBytypeHTTP();
        //  ?
        sheet = wb.getSheet(" HTTP");

        //  
        cnt = 0;
        for (Map.Entry<ResponseResult, Model_NumberReport> entry : map.entrySet()) {
            ResponseResult key = entry.getKey();
            Model_NumberReport value = entry.getValue();

            int cnt_cell = 0;
            row = sheet.getRow(t0_start - 1 + cnt);
            if (row == null) {
                row = sheet.createRow(t0_start - 1 + cnt);
            }
            /*//
             cell = row.getCell(cnt_cell);
             if (cell == null) {
             cell = row.createCell(cnt_cell);
             }
             cell.setCellType(XSSFCell.CELL_TYPE_STRING);
             cell.setCellValue(key.name());
             cell.setCellStyle(cs1);
             cnt_cell++;*/

            // (?)
            cell = row.getCell(cnt_cell);
            if (cell == null) {
                cell = row.createCell(cnt_cell);
            }
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(key.getDesc());
            cell.setCellStyle(cs1);
            cnt_cell++;

            /*// ()
             cell = row.getCell(cnt_cell);
             if (cell == null) {
             cell = row.createCell(cnt_cell);
             }
             cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
             cell.setCellValue(key.getCode());
             cell.setCellStyle(cs1);
             cnt_cell++;*/
            // (?)
            cell = row.getCell(cnt_cell);
            if (cell == null) {
                cell = row.createCell(cnt_cell);
            }
            cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue(value.getNumber());
            cell.setCellStyle(cs1);
            cnt_cell++;
            // ()
            cell = row.getCell(cnt_cell);
            if (cell == null) {
                cell = row.createCell(cnt_cell);
            }
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(FormatUtils.FormatDoubleD(value.getPercent()));
            cell.setCellStyle(cs1);
            cnt_cell++;
            cnt++;
        }

        // 
        row = sheet.getRow(0);
        cell = row.getCell(0);
        cell.setCellType(XSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(MAIN_ZAG_TEMPL + timestamp_csv);

        //    HTTPS
        map = rep.reportCountBytypeHTTPS();
        //  ?
        sheet = wb.getSheet(" HTTPS");

        //  
        cnt = 0;
        for (Map.Entry<ResponseResult, Model_NumberReport> entry : map.entrySet()) {
            ResponseResult key = entry.getKey();
            Model_NumberReport value = entry.getValue();

            int cnt_cell = 0;
            row = sheet.getRow(t0_start - 1 + cnt);
            if (row == null) {
                row = sheet.createRow(t0_start - 1 + cnt);
            }
            /*//
             cell = row.getCell(cnt_cell);
             if (cell == null) {
             cell = row.createCell(cnt_cell);
             }
             cell.setCellType(XSSFCell.CELL_TYPE_STRING);
             cell.setCellValue(key.name());
             cell.setCellStyle(cs1);
             cnt_cell++;*/

            // (?)
            cell = row.getCell(cnt_cell);
            if (cell == null) {
                cell = row.createCell(cnt_cell);
            }
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(key.getDesc());
            cell.setCellStyle(cs1);
            cnt_cell++;

            /*// ()
             cell = row.getCell(cnt_cell);
             if (cell == null) {
             cell = row.createCell(cnt_cell);
             }
             cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
             cell.setCellValue(key.getCode());
             cell.setCellStyle(cs1);
             cnt_cell++;*/
            // (?)
            cell = row.getCell(cnt_cell);
            if (cell == null) {
                cell = row.createCell(cnt_cell);
            }
            cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue(value.getNumber());
            cell.setCellStyle(cs1);
            cnt_cell++;
            // ()
            cell = row.getCell(cnt_cell);
            if (cell == null) {
                cell = row.createCell(cnt_cell);
            }
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(FormatUtils.FormatDoubleD(value.getPercent()));
            cell.setCellStyle(cs1);
            cnt_cell++;
            cnt++;
        }

        // 
        row = sheet.getRow(0);
        cell = row.getCell(0);
        cell.setCellType(XSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(MAIN_ZAG_TEMPL + timestamp_csv);

        //? 
        String tmp_out = StringUtils.replaceAll(report_name, "!dt!",
                DateUtils.DateToString(new Date(System.currentTimeMillis()), "ddMMyyyy_Hms"));
        try (FileOutputStream out = new FileOutputStream(tmp_out)) {
            wb.write(out);
            logger.info("Report file " + tmp_out + " created");
        }

    } catch (Exception ex) {
        logger.error(ex);
    }
}

From source file:ru.letoapp.tests.RegistrationQuickDDTest.java

public String[][] getTestDataFromXlsxFile() throws Exception {
    String path = "src/main/resources/TestData.xlsx";
    String[][] dataList = new String[2][2];
    FileInputStream fis = null;//from ww w .ja  v  a  2  s  .  com
    try {
        fis = new FileInputStream(new File(path));
        XSSFWorkbook workbook = new XSSFWorkbook(fis);
        XSSFSheet sheet = workbook.getSheet("TestData");
        Iterator<Row> rows = sheet.rowIterator();
        if (rows.hasNext()) {
            rows.next();
        }
        for (int i = 0; rows.hasNext(); ++i) {
            XSSFRow row = ((XSSFRow) rows.next());
            Iterator<Cell> cells = row.cellIterator();
            for (int j = 0; cells.hasNext(); ++j) {
                XSSFCell cell = (XSSFCell) cells.next();
                String value = cell.getStringCellValue();
                if (!value.equals(null)) {
                    dataList[i][j] = value;
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return dataList;
}

From source file:Servelt.ExcelReader.java

public ExcelReader(File excelFile) {

    try {/*from ww  w .j a  va 2s  .  c  o  m*/
        XSSFWorkbook workbook = new XSSFWorkbook(excelFile);

        ComponentSet comSet = null;

        if (workbook.getSheet(CPU.ComponentName) != null) {
            comSet = new CpuSet();
            readSheet(workbook.getSheet(CPU.ComponentName), CPU.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(Antennas.ComponentName) != null) {
            comSet = new AntennasSet();
            readSheet(workbook.getSheet(Antennas.ComponentName), Antennas.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(Button.ComponentName) != null) {
            comSet = new ButtonSet();
            readSheet(workbook.getSheet(Button.ComponentName), Button.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(Camera.ComponentName) != null) {
            comSet = new CameraSet();
            readSheet(workbook.getSheet(Camera.ComponentName), Camera.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(CardReader.ComponentName) != null) {
            comSet = new CardReaderSet();
            readSheet(workbook.getSheet(CardReader.ComponentName), CardReader.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(ClickPad.ComponentName) != null) {
            comSet = new ClickPadSet();
            readSheet(workbook.getSheet(ClickPad.ComponentName), ClickPad.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(Ethernet.ComponentName) != null) {
            comSet = new EthernetSet();
            readSheet(workbook.getSheet(Ethernet.ComponentName), Ethernet.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(KBCEBC.ComponentName) != null) {
            comSet = new KbcEbcSet();
            readSheet(workbook.getSheet(KBCEBC.ComponentName), KBCEBC.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(LcdPanel.ComponentName) != null) {
            comSet = new LcdPanelSet();
            readSheet(workbook.getSheet(LcdPanel.ComponentName), LcdPanel.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(Memory.ComponentName) != null) {
            comSet = new MemorySet();
            readSheet(workbook.getSheet(Memory.ComponentName), Memory.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(Sensor.ComponentName) != null) {
            comSet = new SensorSet();
            readSheet(workbook.getSheet(Sensor.ComponentName), Sensor.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(Storage.ComponentName) != null) {
            comSet = new StorageSet();
            readSheet(workbook.getSheet(Storage.ComponentName), Storage.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(TouchPanel.ComponentName) != null) {
            comSet = new TouchPanelSet();
            readSheet(workbook.getSheet(TouchPanel.ComponentName), TouchPanel.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(WLAN.ComponentName) != null) {
            comSet = new WlanSet();
            readSheet(workbook.getSheet(WLAN.ComponentName), WLAN.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(WWAN.ComponentName) != null) {
            comSet = new WwanSet();
            readSheet(workbook.getSheet(WWAN.ComponentName), WWAN.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(Keyboard.ComponentName) != null) {
            comSet = new KeyboardSet();
            readSheet(workbook.getSheet(Keyboard.ComponentName), Keyboard.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(Battery.ComponentName) != null) {
            comSet = new BatterySet();
            readSheet(workbook.getSheet(Battery.ComponentName), Battery.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(Charger.ComponentName) != null) {
            comSet = new ChargerSet();
            readSheet(workbook.getSheet(Charger.ComponentName), Charger.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(Measurement.ComponentName) != null) {
            comSet = new MeasurementSet();
            readSheet(workbook.getSheet(Measurement.ComponentName), Measurement.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(Graphic.ComponentName) != null) {
            comSet = new GraphicSet();
            readSheet(workbook.getSheet(Graphic.ComponentName), Graphic.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(AudioCodec.ComponentName) != null) {
            comSet = new AudioCodecSet();
            readSheet(workbook.getSheet(AudioCodec.ComponentName), AudioCodec.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(PanelInterfaceBridge.ComponentName) != null) {
            comSet = new PanelInterfaceBridgeSet();
            readSheet(workbook.getSheet(PanelInterfaceBridge.ComponentName),
                    PanelInterfaceBridge.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(ExternalStorageCard.ComponentName) != null) {
            comSet = new ExternalStorageCardSet();
            readSheet(workbook.getSheet(ExternalStorageCard.ComponentName),
                    ExternalStorageCard.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(ODD.ComponentName) != null) {
            comSet = new ODDSet();
            readSheet(workbook.getSheet(ODD.ComponentName), ODD.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(Speaker.ComponentName) != null) {
            comSet = new SpeakerSet();
            readSheet(workbook.getSheet(Speaker.ComponentName), Speaker.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(Mic.ComponentName) != null) {
            comSet = new MicSet();
            readSheet(workbook.getSheet(Mic.ComponentName), Mic.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(IoPort.ComponentName) != null) {
            comSet = new IoPortSet();
            readSheet(workbook.getSheet(IoPort.ComponentName), IoPort.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        if (workbook.getSheet(OS.ComponentName) != null) {
            comSet = new OSSet();
            readSheet(workbook.getSheet(OS.ComponentName), OS.AllKeyList.size(), comSet);
            sheetList.add(comSet);
        }

        for (Object set : sheetList) {
            for (Object obj : ((ComponentSet) set).getComList()) {
                logger.log(Level.INFO, "\n" + ((Component) obj).ComponentToString());
            }
        }

    } catch (Exception ex) {
        ex.printStackTrace();
    }
}

From source file:uk.ac.bbsrc.tgac.miso.core.util.FormUtils.java

License:Open Source License

public static void createPlateExportForm(File outpath, JSONArray jsonArray) throws Exception {
    InputStream in = null;/*from   w w  w  .  j  av  a 2  s  .  c o m*/
    in = FormUtils.class.getResourceAsStream("/forms/ods/plate_input.xlsx");
    if (in != null) {
        XSSFWorkbook oDoc = new XSSFWorkbook(in);

        XSSFSheet sheet = oDoc.getSheet("Input");
        FileOutputStream fileOut = new FileOutputStream(outpath);
        int i = 4;
        for (JSONObject jsonObject : (Iterable<JSONObject>) jsonArray) {
            String sampleinwell = jsonObject.getString("value");
            //"sampleid:wellid:samplealias:projectname"
            String sampleId = sampleinwell.split(":")[0];
            String wellId = sampleinwell.split(":")[1];
            String sampleAlias = sampleinwell.split(":")[2];
            String projectName = sampleinwell.split(":")[3];
            XSSFRow row = sheet.createRow(i);
            XSSFCell cellA = row.createCell(0);
            cellA.setCellValue(wellId);
            XSSFCell cellB = row.createCell(1);
            cellB.setCellValue(projectName);
            XSSFCell cellC = row.createCell(2);
            cellC.setCellValue(sampleAlias);
            i++;
        }
        oDoc.write(fileOut);
        fileOut.close();
    } else {
        throw new IOException("Could not read from resource.");
    }

}