Example usage for org.apache.poi.xssf.usermodel XSSFCellStyle setAlignment

List of usage examples for org.apache.poi.xssf.usermodel XSSFCellStyle setAlignment

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFCellStyle
  6. setAlignment

Introduction

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

Prototype

@Override
public void setAlignment(HorizontalAlignment align)

Source Link

Document

Set the type of horizontal alignment for the cell

Usage

From source file:org.javasoft.ciclope.servlets.ExportMaterialePratica.java

/**
 * Aggiunge il materiale utilizzato nello sheet.
 *
 * @param nextRowFree l'indice zero-based della prima riga da cui scrivere.
 * @param element Il tipo di gruppo di elementi costituenti le righe.
 * @param sheet Il foglio di lavoro su cui aggiungere il contenuto.
 * @param rows Le righe dei dati./*from   w  w w.j a va2 s .c  o m*/
 * @return L'indice zero-based della prima riga libera da cui poter
 * continuare a modificare <param>sheet</param>
 */
private static int AddElements(int nextRowFree, XSSFCellStyle titleStyle, EXPORTED_ELEMENT element,
        XSSFSheet sheet, List<Object[]> rows) {
    //Crea l'intestazione...
    int rid = nextRowFree;
    int cid = 0;
    Row head = sheet.createRow(rid);
    Cell headCell = head.createCell(cid);
    headCell.setCellStyle(titleStyle);
    //imposta l'header
    headCell.setCellValue(element.getTitle());
    //Crea lo style di default delle celle
    XSSFCellStyle defStyle = sheet.getWorkbook().createCellStyle();
    defStyle.setAlignment(CellStyle.ALIGN_LEFT);
    //metti i valori
    SimpleDateFormat sdf = new SimpleDateFormat("EEEEEEEEEEE dd-MM-yyyy", Locale.ITALY);
    //Iterate over data and write to sheet
    try {
        for (Object[] objs : rows) {
            Row row = sheet.createRow(rid++);
            int cellnum = 0;
            if (objs != null) {
                for (Object obj : objs) {
                    Cell cell = row.createCell(cellnum++);
                    //sets left alignement
                    cell.setCellStyle(defStyle);
                    if (obj instanceof String) {
                        cell.setCellValue((String) obj);
                    } else if (obj instanceof Float) {
                        cell.setCellValue((Float) obj);
                    } else if (obj instanceof Integer) {
                        cell.setCellValue((Integer) obj);
                    } else if (obj instanceof BigDecimal) {
                        cell.setCellValue(((BigDecimal) obj).floatValue());
                    } else if (obj instanceof Date) {
                        cell.setCellValue(sdf.format((Date) obj));
                    }
                }
            }
        }
    } catch (ClassCastException ex) {
        for (Object obj : rows) {
            Row row = sheet.createRow(rid++);
            int cellnum = 0;
            Cell cell = row.createCell(cellnum++);
            //sets left alignement
            cell.setCellStyle(defStyle);
            if (obj instanceof String) {
                cell.setCellValue((String) obj);
            } else if (obj instanceof Float) {
                cell.setCellValue((Float) obj);
            } else if (obj instanceof Integer) {
                cell.setCellValue((Integer) obj);
            } else if (obj instanceof BigDecimal) {
                cell.setCellValue(((BigDecimal) obj).floatValue());
            } else if (obj instanceof Date) {
                cell.setCellValue(sdf.format((Date) obj));
            }
        }
    }
    //lascia alcune celle per spaziare i gruppi di elementi.
    sheet.createRow(rid++);
    sheet.createRow(rid++);
    return rid++;
}

From source file:org.jboss.windup.reporting.spreadsheet.ScorecardReporter.java

License:Open Source License

private static void appendTotalRow(XSSFWorkbook wb, XSSFSheet sheet, int rowNum) {
    Font boldFont = wb.createFont();
    boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    boldFont.setColor((short) 0x0);

    XSSFCellStyle commentCell = wb.createCellStyle();
    commentCell.setBorderTop(CellStyle.BORDER_THIN);

    XSSFCellStyle totalCell = wb.createCellStyle();
    totalCell.setBorderTop(CellStyle.BORDER_THIN);
    totalCell.setFont(boldFont);/*from   w  w  w .j a  v  a  2  s . com*/

    XSSFCellStyle totalCellRight = wb.createCellStyle();
    totalCellRight.setBorderTop(CellStyle.BORDER_THIN);
    totalCellRight.setAlignment(HorizontalAlignment.RIGHT);
    totalCellRight.setFont(boldFont);

    XSSFRow row = sheet.createRow(rowNum);
    XSSFCell t1 = row.createCell(0);
    t1.setCellValue("Total:");
    t1.setCellStyle(totalCellRight);

    XSSFCell t2 = row.createCell(1);
    t2.setCellFormula("SUM(B1:B" + rowNum + ")*" + TEST_PADDING);
    t2.setCellStyle(totalCell);

    XSSFCell t3 = row.createCell(2);
    t3.setCellStyle(totalCell);

    XSSFCell t4 = row.createCell(3);
    t4.setCellValue("Total with Testing & App Migration Factors");
    t4.setCellStyle(commentCell);
}

From source file:org.jboss.windup.reporting.spreadsheet.ScorecardReporter.java

License:Open Source License

private static void appendMentoringTotalRow(XSSFWorkbook wb, XSSFSheet sheet, int rowNum, int start, int end) {
    Font boldFont = wb.createFont();
    boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    boldFont.setColor((short) 0x0);

    XSSFCellStyle commentCell = wb.createCellStyle();
    commentCell.setBorderTop(CellStyle.BORDER_THIN);

    XSSFCellStyle totalCell = wb.createCellStyle();
    totalCell.setBorderTop(CellStyle.BORDER_THIN);
    totalCell.setFont(boldFont);//from w w  w .  ja  va  2 s.  c o m

    XSSFCellStyle totalCellRight = wb.createCellStyle();
    totalCellRight.setBorderTop(CellStyle.BORDER_THIN);
    totalCellRight.setAlignment(HorizontalAlignment.RIGHT);
    totalCellRight.setFont(boldFont);

    XSSFRow row = sheet.createRow(rowNum);
    XSSFCell t1 = row.createCell(0);
    t1.setCellValue("Total:");
    t1.setCellStyle(totalCellRight);

    XSSFCell t2 = row.createCell(1);
    t2.setCellFormula("SUM(B" + start + ":B" + end + ")");
    t2.setCellStyle(totalCell);

    XSSFCell t3 = row.createCell(2);
    t3.setCellStyle(totalCell);

    XSSFCell t4 = row.createCell(3);
    t4.setCellStyle(commentCell);
}

From source file:org.jboss.windup.reporting.spreadsheet.ScorecardReporter.java

License:Open Source License

private static void appendNotesRow(XSSFWorkbook wb, XSSFSheet sheet, int rowNum, String app, double effort,
        String notes) {/*  ww  w . j av a 2s .  c  o m*/
    XSSFRow row = sheet.createRow(rowNum);
    XSSFCell t1 = row.createCell(0);
    t1.setCellValue(app);

    XSSFCell t2 = row.createCell(1);
    XSSFCellStyle t2s = wb.createCellStyle();
    t2s.setAlignment(HorizontalAlignment.RIGHT);
    t2.setCellStyle(t2s);

    t2.setCellValue(effort);
    row.createCell(2);

    XSSFCell t4 = row.createCell(3);
    t4.setCellValue(notes);
}

From source file:org.nekorp.workflow.desktop.servicio.reporte.cliente.AbstractSeccionXLS.java

License:Apache License

public void addHeaderStyle(XSSFCellStyle cellStyle, XSSFWorkbook wb) {
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(85, 142, 213)));
    cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    XSSFFont font = wb.createFont();//  w ww  . j  a  v  a 2  s  .c o  m
    font.setColor(IndexedColors.WHITE.getIndex());
    font.setBold(true);
    cellStyle.setFont(font);
}

From source file:packtest.AligningCells.java

License:Apache License

/**
 * Center a text over multiple columns using ALIGN_CENTER_SELECTION
 *
 * @param wb the workbook/*from w  w  w. j  a  va 2s  . c o  m*/
 * @param row the row to create the cell in
 * @param start_column  the column number to create the cell in and where the selection starts
 * @param end_column    the column number where the selection ends
 * @param valign the horizontal alignment for the cell.
 *
 * @author Cristian Petrula, Romania
 */
private static void centerAcrossSelection(XSSFWorkbook wb, XSSFRow row, short start_column, short end_column,
        short valign) {

    // Create cell style with ALIGN_CENTER_SELECTION
    XSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER_SELECTION);
    cellStyle.setVerticalAlignment(valign);

    // Create cells over the selected area
    for (int i = start_column; i <= end_column; i++) {
        XSSFCell cell = row.createCell(i);
        cell.setCellStyle(cellStyle);
    }

    // Set value to the first cell
    XSSFCell cell = row.getCell(start_column);
    cell.setCellValue(new XSSFRichTextString("Align It"));

    // Make the selection
    CTRowImpl ctRow = (CTRowImpl) row.getCTRow();

    // Add object with format start_coll:end_coll. For example 1:3 will span from
    // cell 1 to cell 3, where the column index starts with 0
    //
    // You can add multiple spans for one row
    Object span = start_column + ":" + end_column;

    List<Object> spanList = new ArrayList<Object>();
    spanList.add(span);

    //add spns to the row
    ctRow.setSpans(spanList);
}

From source file:packtest.CalendarDemo.java

License:Apache License

/**
 * cell styles used for formatting calendar sheets
 *//*from w  w  w.j  av a 2s .c om*/
private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb) {
    Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();

    XSSFCellStyle style;
    XSSFFont titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 48);
    titleFont.setColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFont(titleFont);
    styles.put("title", style);

    XSSFFont monthFont = wb.createFont();
    monthFont.setFontHeightInPoints((short) 12);
    monthFont.setColor(new XSSFColor(new java.awt.Color(255, 255, 255)));
    monthFont.setBold(true);
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFillForegroundColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setFont(monthFont);
    styles.put("month", style);

    XSSFFont dayFont = wb.createFont();
    dayFont.setFontHeightInPoints((short) 14);
    dayFont.setBold(true);
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.LEFT);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    style.setFillForegroundColor(new XSSFColor(new java.awt.Color(228, 232, 243)));
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setBorderLeft(BorderStyle.THIN);
    style.setLeftBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
    style.setFont(dayFont);
    styles.put("weekend_left", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    style.setFillForegroundColor(new XSSFColor(new java.awt.Color(228, 232, 243)));
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
    styles.put("weekend_right", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.LEFT);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    style.setBorderLeft(BorderStyle.THIN);
    style.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 255, 255)));
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setLeftBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
    style.setFont(dayFont);
    styles.put("workday_left", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    style.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 255, 255)));
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
    styles.put("workday_right", style);

    style = wb.createCellStyle();
    style.setBorderLeft(BorderStyle.THIN);
    style.setFillForegroundColor(new XSSFColor(new java.awt.Color(234, 234, 234)));
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
    styles.put("grey_left", style);

    style = wb.createCellStyle();
    style.setFillForegroundColor(new XSSFColor(new java.awt.Color(234, 234, 234)));
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
    styles.put("grey_right", style);

    return styles;
}

From source file:pe.gob.mef.gescon.util.ExcelUtil.java

/**
 * Mtodo que crea el estilo de la cabecera.
 * @return estilo Estilo de la cabecera, tipo XSSFCellStyle.
 *//*w w w . j a v  a2s .c  o m*/
public XSSFCellStyle creaEstiloCabecera() {
    XSSFCellStyle estilo = (XSSFCellStyle) libroExcel.createCellStyle();
    estilo.setBorderBottom(XSSFCellStyle.BORDER_THIN);
    estilo.setBorderTop(XSSFCellStyle.BORDER_THIN);
    estilo.setBorderRight(XSSFCellStyle.BORDER_THIN);
    estilo.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    estilo.setVerticalAlignment(VerticalAlignment.CENTER);
    estilo.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    Font fuente = creaFuenteCabecera();
    estilo.setFont(fuente);
    return estilo;
}

From source file:poi.xssf.usermodel.examples.AligningCells.java

License:Apache License

/**
 * Center a text over multiple columns using ALIGN_CENTER_SELECTION
 *
 * @param wb the workbook/*from   w  w w.j  av a  2s. c  om*/
 * @param row the row to create the cell in
 * @param start_column  the column number to create the cell in and where the selection starts
 * @param end_column    the column number where the selection ends
 * @param valign the horizontal alignment for the cell.
 *
 * @author Cristian Petrula, Romania
 */
private static void centerAcrossSelection(XSSFWorkbook wb, XSSFRow row, short start_column, short end_column,
        short valign) {

    // Create cell style with ALIGN_CENTER_SELECTION
    XSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER_SELECTION);
    cellStyle.setVerticalAlignment(valign);

    // Create cells over the selected area
    for (int i = start_column; i <= end_column; i++) {
        XSSFCell cell = row.createCell(i);
        cell.setCellStyle(cellStyle);
    }

    // Set value to the first cell
    XSSFCell cell = row.getCell(start_column);
    cell.setCellValue(new XSSFRichTextString("Align It"));

    // Make the selection
    CTRowImpl ctRow = (CTRowImpl) row.getCTRow();
    List spanList = new ArrayList();

    // Add object with format start_coll:end_coll. For example 1:3 will span from
    // cell 1 to cell 3, where the column index starts with 0
    //
    // You can add multiple spans for one row
    Object span = start_column + ":" + end_column;
    spanList.add(span);

    //add spns to the row
    ctRow.setSpans(spanList);
}

From source file:reports.achievedReport.java

public String getAchievedReport(int passedYear, String passedPath, String dates)
        throws InvalidFormatException, IOException, SQLException {
    pepfaryear = passedYear;/*from w ww  . j  a va2  s.  c o  m*/
    full_date = dates;

    dbConn conn = new dbConn();
    pos = 0;
    incrementor = 0;

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

    String allpath = passedPath;

    //            ^^^^^^^^^^^^^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);
        }
    }

    for (int i = 0; i < myalphabet.length; i++) {
        try {
            System.out.println("at position  :  " + myalphabet[i]);
            String current_drive = myalphabet[i];

            File f3 = new File(current_drive + ":\\APHIAPLUS\\PWPDBCONNECTION");

            //     CREATE A DIRECTORY AND THE FILE TO HOLD DATA
            if (f3.exists() && f3.isDirectory()) {
                path = current_drive + ":\\APHIAPLUS\\PWPDBCONNECTION\\DATA\\REPORTS";
                new File(path).mkdirs();
                filePath = path + "\\PWP_ATTACHED_REPORT" + full_date + ".xlsm";
            }

            //select the last timestamp which a backup was picked from.....
        } finally {

        }

    }
    FileOutputStream fileOut = new FileOutputStream(filePath);

    wb.write(fileOut);

    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){  
    // filePath="noreport";   
    //}
    //else{
    ////  url="no url to the report";  
    //}

    return filePath;
}