Example usage for org.apache.poi.hssf.usermodel HSSFRow createCell

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFRow createCell.

Prototype

@Override
public HSSFCell createCell(int column) 

Source Link

Document

Use this to create new cells within the row and return it.

Usage

From source file:bean.ClassOpData.java

public void export0() {
    Integer columnNo;//w  w  w.j  ava  2s . c o  m
    HSSFWorkbook workbook;
    HSSFSheet sheet;
    HSSFRow headerRow, dataRow, totalRow = null;
    HSSFCell cell;
    HSSFCellStyle cellStyle, boldStyle;
    HSSFFont font;

    ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay();

    getExportData().createFolder(null, themeDisplay, "Other Current Liabilities Report", "DESCRIPTION");
    if (getExportData().getFilename() == null || getExportData().getFilename().length() == 0) {
        getExportData().setFilename("Default(" + new Date() + ")");
    }
    getExportData().setFilename(getExportData().getFilename().replace(":", ""));

    try {
        getExportData().setFilename(getExportData().getFilename().concat(".xls"));
        workbook = new HSSFWorkbook();

        cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));

        font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);

        boldStyle = workbook.createCellStyle();
        boldStyle.setFont(font);

        for (int i = 0; i < getAccountsWithSubsidiaryData().getAccountCodesFiltered().size(); i++) {
            try {
                sheet = workbook.createSheet(getDataConvert()
                        .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i)));
            } catch (Exception e) {
                sheet = workbook.createSheet(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i));
            }

            headerRow = sheet.createRow((short) 0);
            columnNo = 0;
            cell = headerRow.createCell(columnNo++);
            cell.setCellValue(getDataConvert()
                    .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i)));
            cell.setCellStyle(boldStyle);

            try {
                headerRow = sheet.createRow(headerRow.getRowNum() + 1);
                columnNo = 0;
                cell = headerRow.createCell(columnNo++);
                cell.setCellValue("As of " + getCustomDate()
                        .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY"));
                cell.setCellStyle(boldStyle);
            } catch (Exception e) {
                getAccountsWithSubsidiaryData().setReportDate(getCustomDate().getCurrentDate());

                cell.setCellValue("As of " + getCustomDate()
                        .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY"));
                cell.setCellStyle(boldStyle);
            }

            if (getAccountsWithSubsidiaryData().getAcctCreateDateFrom() != null
                    || getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null) {
                headerRow = sheet.createRow(headerRow.getRowNum() + 1);
                columnNo = 0;
                cell = headerRow.createCell(columnNo++);
                cell.setCellValue(getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null
                        ? "Account Created Date: " + getCustomDate()
                                .formatDate(
                                        getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY")
                                .concat(" - ")
                                .concat(getCustomDate().formatDate(
                                        getAccountsWithSubsidiaryData().getAcctCreateDateTo(), "MM-dd-YYYY"))
                        : "Account Created Date: " + getCustomDate().formatDate(
                                getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY"));
                cell.setCellStyle(boldStyle);
            }

            if ((getAccountsWithSubsidiaryData().getAmountFilter() != null
                    && getAccountsWithSubsidiaryData().getAmountFilter().compareTo(BigDecimal.ZERO) == 1)) {
                headerRow = sheet.createRow(headerRow.getRowNum() + 1);
                columnNo = 0;
                cell = headerRow.createCell(columnNo++);
                cell.setCellValue("Amount Range: " + getDataConvert()
                        .numericConvert(getAccountsWithSubsidiaryData().getAmountFilter().doubleValue()));
                cell.setCellStyle(boldStyle);
            }

            headerRow = sheet.createRow(headerRow.getRowNum() + 1);

            headerRow = sheet.createRow(headerRow.getRowNum() + 1);
            columnNo = 0;
            cell = headerRow.createCell(columnNo++);
            cell.setCellValue("Account No.");
            cell.setCellStyle(boldStyle);

            cell = headerRow.createCell(columnNo++);
            cell.setCellValue("Name");
            cell.setCellStyle(boldStyle);

            cell = headerRow.createCell(columnNo++);
            cell.setCellValue("Account Status");
            cell.setCellStyle(boldStyle);

            cell = headerRow.createCell(columnNo++);
            cell.setCellValue("Balance");
            cell.setCellStyle(boldStyle);

            for (int ii = 0; ii < getClassOpSummary().get(i).size(); ii++) {
                columnNo = 0;

                dataRow = sheet.createRow(headerRow.getRowNum() + ii + 1);

                dataRow.createCell(columnNo++).setCellValue(getClassOpSummary().get(i).get(ii)[2].toString());
                dataRow.createCell(columnNo++).setCellValue(getClassOpSummary().get(i).get(ii)[4].toString());
                dataRow.createCell(columnNo++).setCellValue(getDataConvert()
                        .acctStatusConvert(getClassOpSummary().get(i).get(ii)[6].toString().charAt(0)));

                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(((BigDecimal) getClassOpSummary().get(i).get(ii)[5]).doubleValue());
                cell.setCellStyle(cellStyle);

                totalRow = sheet.createRow((short) dataRow.getRowNum() + 2);
            }
            if (getClassOpSummary().get(i).size() > 0) {
                cell = totalRow.createCell(1);
                cell.setCellValue("TOTAL");
                cell.setCellStyle(boldStyle);

                cell = totalRow.createCell(2);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(getAccountsWithSubsidiaryData().getSubtotal().get(i).doubleValue());
                cell.setCellStyle(cellStyle);
            }
        }

        FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename());
        workbook.write(fileOutputStream);
        fileOutputStream.close();

        getExportData().fileUploadByDL(getExportData().getFilename(), "Other Current Liabilities Report",
                themeDisplay, null);

        File file = new File(getExportData().getFilename());
        if (file.exists()) {
            file.delete();
        }
    } catch (Exception e) {
        System.out.print("classOpData().export0() " + e);
        FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error",
                "An error occurred while generating excel file.");
        FacesContext.getCurrentInstance().addMessage(null, message);
    }
}

---

From source file:bean.ClassOtData.java

public void export0() {
    Integer columnNo;/*from   w w  w . ja  va2s. c  o  m*/
    HSSFWorkbook workbook;
    HSSFSheet sheet;
    HSSFRow headerRow, dataRow, totalRow = null;
    HSSFCell cell;
    HSSFCellStyle cellStyle, boldStyle;
    HSSFFont font;

    ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay();

    getExportData().createFolder(null, themeDisplay, "Other Current Receivables Report", "DESCRIPTION");

    if (getExportData().getFilename() == null || getExportData().getFilename().length() == 0) {
        getExportData().setFilename("Default(" + new Date() + ")");
    }
    getExportData().setFilename(getExportData().getFilename().replace(":", ""));

    try {
        getExportData().setFilename(getExportData().getFilename().concat(".xls"));
        workbook = new HSSFWorkbook();

        cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));

        font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);

        boldStyle = workbook.createCellStyle();
        boldStyle.setFont(font);

        for (int i = 0; i < getAccountsWithSubsidiaryData().getAccountCodesFiltered().size(); i++) {
            try {
                sheet = workbook.createSheet(getDataConvert()
                        .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i)));
            } catch (Exception e) {
                sheet = workbook.createSheet(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i));
            }

            headerRow = sheet.createRow((short) 0);
            columnNo = 0;
            cell = headerRow.createCell(columnNo++);
            cell.setCellValue(getDataConvert()
                    .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i)));
            cell.setCellStyle(boldStyle);

            try {
                headerRow = sheet.createRow(headerRow.getRowNum() + 1);
                columnNo = 0;
                cell = headerRow.createCell(columnNo++);
                cell.setCellValue("As of " + getCustomDate()
                        .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY"));
                cell.setCellStyle(boldStyle);
            } catch (Exception e) {
                getAccountsWithSubsidiaryData().setReportDate(getCustomDate().getCurrentDate());

                cell.setCellValue("As of " + getCustomDate()
                        .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY"));
                cell.setCellStyle(boldStyle);
            }

            if (getAccountsWithSubsidiaryData().getAcctCreateDateFrom() != null
                    || getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null) {
                headerRow = sheet.createRow(headerRow.getRowNum() + 1);
                columnNo = 0;
                cell = headerRow.createCell(columnNo++);
                cell.setCellValue(getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null
                        ? "Account Created Date: " + getCustomDate()
                                .formatDate(
                                        getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY")
                                .concat(" - ")
                                .concat(getCustomDate().formatDate(
                                        getAccountsWithSubsidiaryData().getAcctCreateDateTo(), "MM-dd-YYYY"))
                        : "Account Created Date: " + getCustomDate().formatDate(
                                getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY"));
                cell.setCellStyle(boldStyle);
            }

            if ((getAccountsWithSubsidiaryData().getAmountFilter() != null
                    && getAccountsWithSubsidiaryData().getAmountFilter().compareTo(BigDecimal.ZERO) == 1)) {
                headerRow = sheet.createRow(headerRow.getRowNum() + 1);
                columnNo = 0;
                cell = headerRow.createCell(columnNo++);
                cell.setCellValue("Amount Range: " + getDataConvert()
                        .numericConvert(getAccountsWithSubsidiaryData().getAmountFilter().doubleValue()));
                cell.setCellStyle(boldStyle);
            }

            headerRow = sheet.createRow(headerRow.getRowNum() + 1);

            headerRow = sheet.createRow(headerRow.getRowNum() + 1);
            columnNo = 0;
            cell = headerRow.createCell(columnNo++);
            cell.setCellValue("Account No.");
            cell.setCellStyle(boldStyle);

            cell = headerRow.createCell(columnNo++);
            cell.setCellValue("Name");
            cell.setCellStyle(boldStyle);

            cell = headerRow.createCell(columnNo++);
            cell.setCellValue("Account Status");
            cell.setCellStyle(boldStyle);

            cell = headerRow.createCell(columnNo++);
            cell.setCellValue("Balance");
            cell.setCellStyle(boldStyle);

            for (int ii = 0; ii < getClassOtSummary().get(i).size(); ii++) {
                columnNo = 0;

                dataRow = sheet.createRow(headerRow.getRowNum() + ii + 1);

                dataRow.createCell(columnNo++).setCellValue(getClassOtSummary().get(i).get(ii)[2].toString());
                dataRow.createCell(columnNo++).setCellValue(getClassOtSummary().get(i).get(ii)[4].toString());
                dataRow.createCell(columnNo++).setCellValue(getDataConvert()
                        .acctStatusConvert(getClassOtSummary().get(i).get(ii)[6].toString().charAt(0)));

                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(((BigDecimal) getClassOtSummary().get(i).get(ii)[5]).doubleValue());
                cell.setCellStyle(cellStyle);

                totalRow = sheet.createRow((short) dataRow.getRowNum() + 2);
            }
            if (getClassOtSummary().get(i).size() > 0) {
                cell = totalRow.createCell(1);
                cell.setCellValue("TOTAL");
                cell.setCellStyle(boldStyle);

                cell = totalRow.createCell(2);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(getAccountsWithSubsidiaryData().getSubtotal().get(i).doubleValue());
                cell.setCellStyle(cellStyle);
            }
        }

        FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename());
        workbook.write(fileOutputStream);
        fileOutputStream.close();

        getExportData().fileUploadByDL(getExportData().getFilename(), "Other Current Receivables Report",
                themeDisplay, null);

        File file = new File(getExportData().getFilename());
        if (file.exists()) {
            file.delete();
        }
    } catch (Exception e) {
        System.out.print("classOtData().export0() " + e);
        FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error",
                "An error occurred while generating excel file.");
        FacesContext.getCurrentInstance().addMessage(null, message);
    }
}

---

From source file:bean.ClassRiData.java

public void export0() {
    Integer columnNo;// www .  j av a  2 s .c om
    HSSFWorkbook workbook;
    HSSFSheet sheet;
    HSSFRow headerRow, dataRow, totalRow = null;
    HSSFCell cell;
    HSSFCellStyle cellStyle, boldStyle;
    HSSFFont font;

    ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay();

    getExportData().createFolder(null, themeDisplay, "Rental Income SL Report", "DESCRIPTION");

    if (getExportData().getFilename() == null || getExportData().getFilename().length() == 0) {
        getExportData().setFilename("Default(" + new Date() + ")");
    }
    getExportData().setFilename(getExportData().getFilename().replace(":", ""));

    try {
        getExportData().setFilename(getExportData().getFilename().concat(".xls"));
        workbook = new HSSFWorkbook();

        cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));

        font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);

        boldStyle = workbook.createCellStyle();
        boldStyle.setFont(font);

        for (int i = 0; i < getAccountsWithSubsidiaryData().getAccountCodesFiltered().size(); i++) {
            try {
                sheet = workbook.createSheet(getDataConvert()
                        .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i)));
            } catch (Exception e) {
                sheet = workbook.createSheet(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i));
            }

            headerRow = sheet.createRow((short) 0);
            columnNo = 0;
            cell = headerRow.createCell(columnNo++);
            cell.setCellValue(getDataConvert()
                    .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i)));
            cell.setCellStyle(boldStyle);

            try {
                headerRow = sheet.createRow(headerRow.getRowNum() + 1);
                columnNo = 0;
                cell = headerRow.createCell(columnNo++);
                cell.setCellValue("As of " + getCustomDate()
                        .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY"));
                cell.setCellStyle(boldStyle);
            } catch (Exception e) {
                getAccountsWithSubsidiaryData().setReportDate(getCustomDate().getCurrentDate());

                cell.setCellValue("As of " + getCustomDate()
                        .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY"));
                cell.setCellStyle(boldStyle);
            }

            if (getAccountsWithSubsidiaryData().getAcctCreateDateFrom() != null
                    || getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null) {
                headerRow = sheet.createRow(headerRow.getRowNum() + 1);
                columnNo = 0;
                cell = headerRow.createCell(columnNo++);
                cell.setCellValue(getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null
                        ? "Account Created Date: " + getCustomDate()
                                .formatDate(
                                        getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY")
                                .concat(" - ")
                                .concat(getCustomDate().formatDate(
                                        getAccountsWithSubsidiaryData().getAcctCreateDateTo(), "MM-dd-YYYY"))
                        : "Account Created Date: " + getCustomDate().formatDate(
                                getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY"));
                cell.setCellStyle(boldStyle);
            }

            if ((getAccountsWithSubsidiaryData().getAmountFilter() != null
                    && getAccountsWithSubsidiaryData().getAmountFilter().compareTo(BigDecimal.ZERO) == 1)) {
                headerRow = sheet.createRow(headerRow.getRowNum() + 1);
                columnNo = 0;
                cell = headerRow.createCell(columnNo++);
                cell.setCellValue("Amount Range: " + getDataConvert()
                        .numericConvert(getAccountsWithSubsidiaryData().getAmountFilter().doubleValue()));
                cell.setCellStyle(boldStyle);
            }

            headerRow = sheet.createRow(headerRow.getRowNum() + 1);

            headerRow = sheet.createRow(headerRow.getRowNum() + 1);
            columnNo = 0;
            cell = headerRow.createCell(columnNo++);
            cell.setCellValue("Account No.");
            cell.setCellStyle(boldStyle);

            cell = headerRow.createCell(columnNo++);
            cell.setCellValue("Name");
            cell.setCellStyle(boldStyle);

            cell = headerRow.createCell(columnNo++);
            cell.setCellValue("Account Status");
            cell.setCellStyle(boldStyle);

            cell = headerRow.createCell(columnNo++);
            cell.setCellValue("Balance");
            cell.setCellStyle(boldStyle);

            for (int ii = 0; ii < getClassRiSummary().get(i).size(); ii++) {
                columnNo = 0;

                dataRow = sheet.createRow(headerRow.getRowNum() + ii + 1);

                dataRow.createCell(columnNo++).setCellValue(getClassRiSummary().get(i).get(ii)[2].toString());
                dataRow.createCell(columnNo++).setCellValue(getClassRiSummary().get(i).get(ii)[4].toString());
                dataRow.createCell(columnNo++).setCellValue(getDataConvert()
                        .acctStatusConvert(getClassRiSummary().get(i).get(ii)[6].toString().charAt(0)));

                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(((BigDecimal) getClassRiSummary().get(i).get(ii)[5]).doubleValue());
                cell.setCellStyle(cellStyle);

                totalRow = sheet.createRow((short) dataRow.getRowNum() + 2);
            }
            if (getClassRiSummary().get(i).size() > 0) {
                cell = totalRow.createCell(1);
                cell.setCellValue("TOTAL");
                cell.setCellStyle(boldStyle);

                cell = totalRow.createCell(2);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(getAccountsWithSubsidiaryData().getSubtotal().get(i).doubleValue());
                cell.setCellStyle(cellStyle);
            }
        }

        FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename());
        workbook.write(fileOutputStream);
        fileOutputStream.close();

        getExportData().fileUploadByDL(getExportData().getFilename(), "Rental Income SL Report", themeDisplay,
                null);

        File file = new File(getExportData().getFilename());
        if (file.exists()) {
            file.delete();
        }
    } catch (Exception e) {
        System.out.print("classRiData().export0() " + e);
        FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error",
                "An error occurred while generating excel file.");
        FacesContext.getCurrentInstance().addMessage(null, message);
    }
}

---

From source file:bean.ClassScData.java

public void export0() {
    Integer columnNo;//w  w w .  j a v  a2s.com
    HSSFWorkbook workbook;
    HSSFSheet sheet;
    HSSFRow headerRow, dataRow, totalRow = null;
    HSSFCell cell;
    HSSFCellStyle cellStyle, boldStyle;
    HSSFFont font;

    ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay();

    getExportData().createFolder(null, themeDisplay, "Share Capital Report", "DESCRIPTION");

    if (getExportData().getFilename() == null || getExportData().getFilename().length() == 0) {
        getExportData().setFilename("Default(" + new Date() + ")");
    }
    getExportData().setFilename(getExportData().getFilename().replace(":", ""));

    try {
        getExportData().setFilename(getExportData().getFilename().concat(".xls"));
        workbook = new HSSFWorkbook();

        cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));

        font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);

        boldStyle = workbook.createCellStyle();
        boldStyle.setFont(font);

        for (int i = 0; i < getAccountsWithSubsidiaryData().getAccountCodesFiltered().size(); i++) {
            try {
                sheet = workbook.createSheet(getDataConvert()
                        .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i)));
            } catch (Exception e) {
                sheet = workbook.createSheet(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i));
            }

            headerRow = sheet.createRow((short) 0);
            columnNo = 0;
            cell = headerRow.createCell(columnNo++);
            cell.setCellValue(getDataConvert()
                    .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i)));
            cell.setCellStyle(boldStyle);

            try {
                headerRow = sheet.createRow(headerRow.getRowNum() + 1);
                columnNo = 0;
                cell = headerRow.createCell(columnNo++);
                cell.setCellValue("As of " + getCustomDate()
                        .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY"));
                cell.setCellStyle(boldStyle);
            } catch (Exception e) {
                getAccountsWithSubsidiaryData().setReportDate(getCustomDate().getCurrentDate());

                cell.setCellValue("As of " + getCustomDate()
                        .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY"));
                cell.setCellStyle(boldStyle);
            }

            if (getAccountsWithSubsidiaryData().getAcctCreateDateFrom() != null
                    || getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null) {
                headerRow = sheet.createRow(headerRow.getRowNum() + 1);
                columnNo = 0;
                cell = headerRow.createCell(columnNo++);
                cell.setCellValue(getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null
                        ? "Account Created Date: " + getCustomDate()
                                .formatDate(
                                        getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY")
                                .concat(" - ")
                                .concat(getCustomDate().formatDate(
                                        getAccountsWithSubsidiaryData().getAcctCreateDateTo(), "MM-dd-YYYY"))
                        : "Account Created Date: " + getCustomDate().formatDate(
                                getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY"));
                cell.setCellStyle(boldStyle);
            }

            if ((getAccountsWithSubsidiaryData().getAmountFilter() != null
                    && getAccountsWithSubsidiaryData().getAmountFilter().compareTo(BigDecimal.ZERO) == 1)) {
                headerRow = sheet.createRow(headerRow.getRowNum() + 1);
                columnNo = 0;
                cell = headerRow.createCell(columnNo++);
                cell.setCellValue("Amount Range: " + getDataConvert()
                        .numericConvert(getAccountsWithSubsidiaryData().getAmountFilter().doubleValue()));
                cell.setCellStyle(boldStyle);
            }

            headerRow = sheet.createRow(headerRow.getRowNum() + 1);

            headerRow = sheet.createRow(headerRow.getRowNum() + 1);
            columnNo = 0;
            cell = headerRow.createCell(columnNo++);
            cell.setCellValue("Account No.");
            cell.setCellStyle(boldStyle);

            cell = headerRow.createCell(columnNo++);
            cell.setCellValue("Name");
            cell.setCellStyle(boldStyle);

            cell = headerRow.createCell(columnNo++);
            cell.setCellValue("Account Status");
            cell.setCellStyle(boldStyle);

            cell = headerRow.createCell(columnNo++);
            cell.setCellValue("Balance");
            cell.setCellStyle(boldStyle);

            for (int ii = 0; ii < getClassScSummary().get(i).size(); ii++) {
                columnNo = 0;

                dataRow = sheet.createRow(headerRow.getRowNum() + ii + 1);

                dataRow.createCell(columnNo++).setCellValue(getClassScSummary().get(i).get(ii)[2].toString());
                dataRow.createCell(columnNo++).setCellValue(getClassScSummary().get(i).get(ii)[4].toString());
                dataRow.createCell(columnNo++).setCellValue(getDataConvert()
                        .acctStatusConvert(getClassScSummary().get(i).get(ii)[6].toString().charAt(0)));

                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(((BigDecimal) getClassScSummary().get(i).get(ii)[5]).doubleValue());
                cell.setCellStyle(cellStyle);

                totalRow = sheet.createRow((short) dataRow.getRowNum() + 2);
            }
            if (getClassScSummary().get(i).size() > 0) {
                cell = totalRow.createCell(1);
                cell.setCellValue("TOTAL");
                cell.setCellStyle(boldStyle);

                cell = totalRow.createCell(2);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(getAccountsWithSubsidiaryData().getSubtotal().get(i).doubleValue());
                cell.setCellStyle(cellStyle);
            }
        }

        FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename());
        workbook.write(fileOutputStream);
        fileOutputStream.close();

        getExportData().fileUploadByDL(getExportData().getFilename(), "Share Capital Report", themeDisplay,
                null);

        File file = new File(getExportData().getFilename());
        if (file.exists()) {
            file.delete();
        }
    } catch (Exception e) {
        System.out.print("classScData().export0() " + e);
        FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error",
                "An error occurred while generating excel file.");
        FacesContext.getCurrentInstance().addMessage(null, message);
    }
}

---

From source file:bean.ClassSdData.java

public void export0() throws IOException {
    Integer columnNo;/*from   w  ww  . j  a  v  a 2  s .c  o m*/
    HSSFWorkbook workbook;
    HSSFSheet sheet;
    HSSFRow headerRow, dataRow, totalRow = null;
    HSSFCell cell;
    HSSFCellStyle cellStyle, boldStyle;
    HSSFFont font;

    ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay();

    getExportData().createFolder(null, themeDisplay, "Savings Deposit Report", "DESCRIPTION");

    if (getExportData().getFilename() == null || getExportData().getFilename().length() == 0) {
        getExportData().setFilename("Default(" + new Date() + ")");
    }
    getExportData().setFilename(getExportData().getFilename().replace(":", ""));

    try {
        getExportData().setFilename(getExportData().getFilename().concat(".xls"));
        workbook = new HSSFWorkbook();

        cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));

        font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);

        boldStyle = workbook.createCellStyle();
        boldStyle.setFont(font);

        for (int i = 0; i < getAccountsWithSubsidiaryData().getAccountCodesFiltered().size(); i++) {
            try {
                sheet = workbook.createSheet(getDataConvert()
                        .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i)));
            } catch (Exception e) {
                sheet = workbook.createSheet(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i));
            }

            headerRow = sheet.createRow((short) 0);
            columnNo = 0;
            cell = headerRow.createCell(columnNo++);
            cell.setCellValue(getDataConvert()
                    .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i)));
            cell.setCellStyle(boldStyle);

            try {
                headerRow = sheet.createRow(headerRow.getRowNum() + 1);
                columnNo = 0;
                cell = headerRow.createCell(columnNo++);
                cell.setCellValue("As of " + getCustomDate()
                        .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY"));
                cell.setCellStyle(boldStyle);
            } catch (Exception e) {
                getAccountsWithSubsidiaryData().setReportDate(getCustomDate().getCurrentDate());

                cell.setCellValue("As of " + getCustomDate()
                        .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY"));
                cell.setCellStyle(boldStyle);
            }

            if (getAccountsWithSubsidiaryData().getAcctCreateDateFrom() != null
                    || getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null) {
                headerRow = sheet.createRow(headerRow.getRowNum() + 1);
                columnNo = 0;
                cell = headerRow.createCell(columnNo++);
                cell.setCellValue(getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null
                        ? "Account Created Date: " + getCustomDate()
                                .formatDate(
                                        getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY")
                                .concat(" - ")
                                .concat(getCustomDate().formatDate(
                                        getAccountsWithSubsidiaryData().getAcctCreateDateTo(), "MM-dd-YYYY"))
                        : "Account Created Date: " + getCustomDate().formatDate(
                                getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY"));
                cell.setCellStyle(boldStyle);
            }

            if ((getAccountsWithSubsidiaryData().getAmountFilter() != null
                    && getAccountsWithSubsidiaryData().getAmountFilter().compareTo(BigDecimal.ZERO) == 1)) {
                headerRow = sheet.createRow(headerRow.getRowNum() + 1);
                columnNo = 0;
                cell = headerRow.createCell(columnNo++);
                cell.setCellValue("Amount Range: " + getDataConvert()
                        .numericConvert(getAccountsWithSubsidiaryData().getAmountFilter().doubleValue()));
                cell.setCellStyle(boldStyle);
            }

            headerRow = sheet.createRow(headerRow.getRowNum() + 1);

            headerRow = sheet.createRow(headerRow.getRowNum() + 1);
            columnNo = 0;
            cell = headerRow.createCell(columnNo++);
            cell.setCellValue("Account No.");
            cell.setCellStyle(boldStyle);

            cell = headerRow.createCell(columnNo++);
            cell.setCellValue("Name");
            cell.setCellStyle(boldStyle);

            cell = headerRow.createCell(columnNo++);
            cell.setCellValue("Account Status");
            cell.setCellStyle(boldStyle);

            cell = headerRow.createCell(columnNo++);
            cell.setCellValue("Balance");
            cell.setCellStyle(boldStyle);

            for (int ii = 0; ii < getClassSdSummary().get(i).size(); ii++) {
                columnNo = 0;

                dataRow = sheet.createRow(headerRow.getRowNum() + ii + 1);

                dataRow.createCell(columnNo++).setCellValue(getClassSdSummary().get(i).get(ii)[2].toString());
                dataRow.createCell(columnNo++).setCellValue(getClassSdSummary().get(i).get(ii)[4].toString());
                dataRow.createCell(columnNo++).setCellValue(getDataConvert()
                        .acctStatusConvert(getClassSdSummary().get(i).get(ii)[6].toString().charAt(0)));

                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);

                cell.setCellValue(((BigDecimal) getClassSdSummary().get(i).get(ii)[5]).doubleValue());
                cell.setCellStyle(cellStyle);

                totalRow = sheet.createRow((short) dataRow.getRowNum() + 2);
            }
            if (getClassSdSummary().get(i).size() > 0) {
                cell = totalRow.createCell(1);
                cell.setCellValue("TOTAL");
                cell.setCellStyle(boldStyle);

                cell = totalRow.createCell(2);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(getAccountsWithSubsidiaryData().getSubtotal().get(i).doubleValue());
                cell.setCellStyle(cellStyle);
            }
        }

        FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename());
        workbook.write(fileOutputStream);
        fileOutputStream.close();

        getExportData().fileUploadByDL(getExportData().getFilename(), "Savings Deposit Report", themeDisplay,
                null);

        File file = new File(getExportData().getFilename());
        if (file.exists()) {
            file.delete();
        }
    } catch (Exception e) {
        System.out.print("classSdData().export0() " + e);
        FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error",
                "An error occurred while generating excel file.");
        FacesContext.getCurrentInstance().addMessage(null, message);
    }
}

---

From source file:bean.ClassTdData.java

public void export0() {
    Integer columnNo;// ww  w.j a  v a  2s  . c om
    HSSFWorkbook workbook;
    HSSFSheet sheet;
    HSSFRow headerRow, dataRow, totalRow = null;
    HSSFCell cell;
    HSSFCellStyle cellStyle, boldStyle;
    HSSFFont font;

    ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay();

    getExportData().createFolder(null, themeDisplay, "Time Deposit Report", "DESCRIPTION");

    if (getExportData().getFilename() == null || getExportData().getFilename().length() == 0) {
        getExportData().setFilename("Default(" + new Date() + ")");
    }
    getExportData().setFilename(getExportData().getFilename().replace(":", ""));

    try {
        getExportData().setFilename(getExportData().getFilename().concat(".xls"));
        workbook = new HSSFWorkbook();

        cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));

        font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);

        boldStyle = workbook.createCellStyle();
        boldStyle.setFont(font);

        for (int i = 0; i < getAccountsWithSubsidiaryData().getAccountCodesFiltered().size(); i++) {
            try {
                sheet = workbook.createSheet(getDataConvert()
                        .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i)));
            } catch (Exception e) {
                sheet = workbook.createSheet(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i));
            }

            headerRow = sheet.createRow((short) 0);
            columnNo = 0;
            cell = headerRow.createCell(columnNo++);
            cell.setCellValue(getDataConvert()
                    .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i)));
            cell.setCellStyle(boldStyle);

            try {
                headerRow = sheet.createRow(headerRow.getRowNum() + 1);
                columnNo = 0;
                cell = headerRow.createCell(columnNo++);
                cell.setCellValue("As of " + getCustomDate()
                        .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY"));
                cell.setCellStyle(boldStyle);
            } catch (Exception e) {
                getAccountsWithSubsidiaryData().setReportDate(getCustomDate().getCurrentDate());

                cell.setCellValue("As of " + getCustomDate()
                        .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY"));
                cell.setCellStyle(boldStyle);
            }

            if (getAccountsWithSubsidiaryData().getAcctCreateDateFrom() != null
                    || getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null) {
                headerRow = sheet.createRow(headerRow.getRowNum() + 1);
                columnNo = 0;
                cell = headerRow.createCell(columnNo++);
                cell.setCellValue(getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null
                        ? "Account Created Date: " + getCustomDate()
                                .formatDate(
                                        getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY")
                                .concat(" - ")
                                .concat(getCustomDate().formatDate(
                                        getAccountsWithSubsidiaryData().getAcctCreateDateTo(), "MM-dd-YYYY"))
                        : "Account Created Date: " + getCustomDate().formatDate(
                                getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY"));
                cell.setCellStyle(boldStyle);
            }

            if ((getAccountsWithSubsidiaryData().getAmountFilter() != null
                    && getAccountsWithSubsidiaryData().getAmountFilter().compareTo(BigDecimal.ZERO) == 1)) {
                headerRow = sheet.createRow(headerRow.getRowNum() + 1);
                columnNo = 0;
                cell = headerRow.createCell(columnNo++);
                cell.setCellValue("Amount Range: " + getDataConvert()
                        .numericConvert(getAccountsWithSubsidiaryData().getAmountFilter().doubleValue()));
                cell.setCellStyle(boldStyle);
            }

            headerRow = sheet.createRow(headerRow.getRowNum() + 1);

            headerRow = sheet.createRow(headerRow.getRowNum() + 1);
            columnNo = 0;
            cell = headerRow.createCell(columnNo++);
            cell.setCellValue("Account No.");
            cell.setCellStyle(boldStyle);

            cell = headerRow.createCell(columnNo++);
            cell.setCellValue("Name");
            cell.setCellStyle(boldStyle);
            //                new below
            cell = headerRow.createCell(columnNo++);
            cell.setCellValue("Certificate No.");
            cell.setCellStyle(boldStyle);
            //                new above

            cell = headerRow.createCell(columnNo++);
            cell.setCellValue("Account Status");
            cell.setCellStyle(boldStyle);

            cell = headerRow.createCell(columnNo++);
            cell.setCellValue("Balance");
            cell.setCellStyle(boldStyle);

            for (int ii = 0; ii < getClassTdSummary().get(i).size(); ii++) {
                columnNo = 0;

                dataRow = sheet.createRow(headerRow.getRowNum() + ii + 1);

                dataRow.createCell(columnNo++).setCellValue(getClassTdSummary().get(i).get(ii)[2].toString());
                dataRow.createCell(columnNo++).setCellValue(getClassTdSummary().get(i).get(ii)[4].toString());
                dataRow.createCell(columnNo++).setCellValue(getClassTdSummary().get(i).get(ii)[9].toString());
                dataRow.createCell(columnNo++).setCellValue(getDataConvert()
                        .acctStatusConvert(getClassTdSummary().get(i).get(ii)[6].toString().charAt(0)));

                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(((BigDecimal) getClassTdSummary().get(i).get(ii)[5]).doubleValue());
                cell.setCellStyle(cellStyle);

                totalRow = sheet.createRow((short) dataRow.getRowNum() + 2);
            }
            if (getClassTdSummary().get(i).size() > 0) {
                cell = totalRow.createCell(1);
                cell.setCellValue("TOTAL");
                cell.setCellStyle(boldStyle);

                cell = totalRow.createCell(2);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(getAccountsWithSubsidiaryData().getSubtotal().get(i).doubleValue());
                cell.setCellStyle(cellStyle);
            }
        }

        FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename());
        workbook.write(fileOutputStream);
        fileOutputStream.close();

        getExportData().fileUploadByDL(getExportData().getFilename(), "Time Deposit Report", themeDisplay,
                null);

        File file = new File(getExportData().getFilename());
        if (file.exists()) {
            file.delete();
        }
    } catch (Exception e) {
        System.out.print("classTdData().export0() " + e);
        FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error",
                "An error occurred while generating excel file.");
        FacesContext.getCurrentInstance().addMessage(null, message);
    }
}

---

From source file:bean.DamayanListingEnrolleeController.java

public void export0() throws FileNotFoundException, IOException {
    Integer columnNo;/*from  w w  w  . j av a  2  s.  c om*/
    HSSFWorkbook workbook;
    HSSFSheet sheet;
    HSSFRow dataRow;
    HSSFCell cell;
    HSSFCellStyle cellStyle, boldStyle;
    HSSFFont font;

    ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay();

    //create folder
    getExportData().createFolder(null, themeDisplay, "Damayan Listing Enrollee", "DESCRIPTION");

    //set filename
    if (getExportData().getFilename() == null || getExportData().getFilename().length() == 0) {
        getExportData().setFilename("Default (" + new Date() + ") ");
    }
    getExportData().setFilename(getExportData().getFilename().replace(":", ""));

    try {
        getExportData().setFilename(getExportData().getFilename().concat(".xls"));
        workbook = new HSSFWorkbook();

        cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));

        font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);

        boldStyle = workbook.createCellStyle();
        boldStyle.setFont(font);

        sheet = workbook.createSheet("Damayan Listing Enrollee "
                + getDamayanListingEnrolleeData().getCustomDate()
                        .formatDate(getDamayanListingEnrolleeData().getDateFrom(), "MMMM dd yyyy")
                + " to " + getDamayanListingEnrolleeData().getCustomDate()
                        .formatDate(getDamayanListingEnrolleeData().getDateTo(), "MMMM dd yyyy"));

        dataRow = sheet.createRow((short) 0);
        columnNo = 0;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("DAMAYAN LISTING ENROLLEE");
        cell.setCellStyle(boldStyle);

        dataRow = sheet.createRow(dataRow.getRowNum() + 1);
        columnNo = 0;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue(getDamayanListingEnrolleeData().getCustomDate()
                .formatDate(getDamayanListingEnrolleeData().getDateFrom(), "MMMM dd yyyy") + " to "
                + getDamayanListingEnrolleeData().getCustomDate()
                        .formatDate(getDamayanListingEnrolleeData().getDateTo(), "MMMM dd yyyy"));
        cell.setCellStyle(boldStyle);
        dataRow = sheet.createRow(dataRow.getRowNum() + 1);
        dataRow = sheet.createRow(dataRow.getRowNum() + 1);

        columnNo = 0;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("DM Account No.");
        cell.setCellStyle(boldStyle);

        columnNo = 1;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("SC Account No.");
        cell.setCellStyle(boldStyle);

        columnNo = 2;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("Account Name");
        cell.setCellStyle(boldStyle);

        columnNo = 3;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("Birthdate");
        cell.setCellStyle(boldStyle);

        columnNo = 4;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("Damayan Enrolled Date");
        cell.setCellStyle(boldStyle);

        columnNo = 5;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("Payment Type");
        cell.setCellStyle(boldStyle);

        //            dataRow.createCell(columnNo++).setCellValue("");

        columnNo = 6;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("Account Status");
        cell.setCellStyle(boldStyle);

        columnNo = 7;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("PD Form");
        cell.setCellStyle(boldStyle);

        for (int i = 0; i < getDamayanListingEnrolleeData().getDamayanList().size(); i++) {
            dataRow = sheet.createRow(dataRow.getRowNum() + 1);
            //DM Account
            columnNo = 0;
            cell = dataRow.createCell(columnNo++);
            cell.setCellValue((String) getDamayanListingEnrolleeData().getDamayanList().get(i)[0]);
            cell.setCellStyle(cellStyle);

            //SC Account
            columnNo = 1;
            cell = dataRow.createCell(columnNo++);
            cell.setCellValue((String) getDamayanListingEnrolleeData().getDataConvert().convertSdToScAcctno(
                    getDamayanListingEnrolleeData().getDamayanList().get(i)[1].toString()));
            cell.setCellStyle(cellStyle);

            //Account Name
            columnNo = 2;
            cell = dataRow.createCell(columnNo++);
            cell.setCellValue((String) getDamayanListingEnrolleeData().getDamayanList().get(i)[2]);
            cell.setCellStyle(cellStyle);

            //Birthdate
            columnNo = 3;
            cell = dataRow.createCell(columnNo++);
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue(getDamayanListingEnrolleeData().getCustomDate().formatDate(
                    (Date) getDamayanListingEnrolleeData().getDamayanList().get(i)[3], "yyyy-MM-dd"));
            cell.setCellStyle(cellStyle);

            //Damayan Enrolled Date
            columnNo = 4;
            cell = dataRow.createCell(columnNo++);
            cell.setCellValue((String) getDamayanListingEnrolleeData().getCustomDate().formatDate(
                    (Date) getDamayanListingEnrolleeData().getDamayanList().get(i)[4], "yyyy-MM-dd"));
            cell.setCellStyle(cellStyle);

            //Payment Type
            columnNo = 5;
            cell = dataRow.createCell(columnNo++);
            cell.setCellValue((String) getDamayanListingEnrolleeData().getDamayanList().get(i)[5].toString()
                    .replace("true", "Auto Deduction").replace("false", "Manual Deduction"));
            cell.setCellStyle(cellStyle);

            //Account Status
            columnNo = 6;
            cell = dataRow.createCell(columnNo++);
            cell.setCellValue((String) getDamayanListingEnrolleeData().getDataConvert().acctStatusConvert(
                    getDamayanListingEnrolleeData().getDamayanList().get(i)[6].toString().charAt(0)));
            cell.setCellStyle(cellStyle);

            //PD Form
            try {
                columnNo = 7;
                cell = dataRow.createCell(columnNo++);
                cell.setCellValue(
                        (String) getDamayanListingEnrolleeData().getDamayanList().get(i)[7].toString());
                cell.setCellStyle(cellStyle);
            } catch (Exception e) {
                System.out.println("PD Form null " + e);
            }
        }

        FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename());
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        getExportData().fileUploadByDL(getExportData().getFilename(), "Damayan Listing Enrollee", themeDisplay,
                null);
        File file = new File(getExportData().getFilename());

        if (file.exists()) {
            file.delete();
        }
    } catch (Exception e) {
        System.out.println("damayanlistingenrolleecontroller.export0 " + e);
        FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error",
                "An error occurred while generating excel file.");
        FacesContext.getCurrentInstance().addMessage(null, message);
    }
}

---

From source file:bean.DamayanListingNoticeController.java

public void export0() {
    Integer columnNo;/*from  w w  w .  j  av a  2  s .  c  o  m*/
    HSSFWorkbook workbook;
    HSSFSheet sheet;
    HSSFRow dataRow;
    HSSFCell cell;
    HSSFCellStyle cellStyle, boldStyle;
    HSSFFont font;

    ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay();

    //create folder
    getExportData().createFolder(null, themeDisplay, "Damayan Listing Notice", "DESCRIPTION");

    //set filename
    if (getExportData().getFilename() == null || getExportData().getFilename().length() == 0) {
        getExportData().setFilename("Default(" + new Date() + ") ");
    }
    getExportData().setFilename(getExportData().getFilename().replace(":", ""));

    try {
        getExportData().setFilename(getExportData().getFilename().concat(".xls"));
        workbook = new HSSFWorkbook();

        cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));

        font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);

        boldStyle = workbook.createCellStyle();
        boldStyle.setFont(font);

        sheet = workbook.createSheet("Damayan Listing Notice "
                + getDamayanListingNoticeData().getCustomDate()
                        .formatDate(getDamayanListingNoticeData().getAcctCreateDateFrom(), "MMMM dd yyyy")
                + " to "
                + getDamayanListingNoticeData().getCustomDate()
                        .formatDate(getDamayanListingNoticeData().getAcctCreateDateTo(), "MMMM dd yyyy")
                + " (Amount Due - " + getDamayanListingNoticeData().getSelectedDue() + ")");

        dataRow = sheet.createRow((short) 0);
        columnNo = 0;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("DAMAYAN LISTING NOTICE");
        cell.setCellStyle(boldStyle);

        dataRow = sheet.createRow(dataRow.getRowNum() + 1);
        columnNo = 0;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue(getDamayanListingNoticeData().getCustomDate()
                .formatDate(getDamayanListingNoticeData().getAcctCreateDateFrom(), "MMMM dd yyyy")
                + " to "
                + getDamayanListingNoticeData().getCustomDate()
                        .formatDate(getDamayanListingNoticeData().getAcctCreateDateTo(), "MMMM dd yyyy")
                + " (Amount Due - " + getDamayanListingNoticeData().getSelectedDue() + ")");
        cell.setCellStyle(boldStyle);

        dataRow = sheet.createRow(dataRow.getRowNum() + 1);

        dataRow = sheet.createRow(dataRow.getRowNum() + 1);
        columnNo = 0;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("DM Account No.");
        cell.setCellStyle(boldStyle);

        columnNo = 1;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("SC Account No.");
        cell.setCellStyle(boldStyle);

        columnNo = 2;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("Account Name");
        cell.setCellStyle(boldStyle);

        columnNo = 3;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("Account Status");
        cell.setCellStyle(boldStyle);

        columnNo = 4;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("Payment Type");
        cell.setCellStyle(boldStyle);

        columnNo = 5;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("PD Form");
        cell.setCellStyle(boldStyle);

        columnNo = 6;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("Balance");
        cell.setCellStyle(boldStyle);

        System.out.println("Start content");

        for (int i = 0; i < getDamayanListingNoticeData().getDamayanList().size(); i++) {
            dataRow = sheet.createRow(dataRow.getRowNum() + 1);

            //DM Account No
            columnNo = 0;
            cell = dataRow.createCell(columnNo++);
            cell.setCellValue((String) getDamayanListingNoticeData().getDamayanList().get(i)[1]);
            cell.setCellStyle(cellStyle);
            System.out.println("DM Account No");

            //SC Account No
            columnNo = 1;
            cell = dataRow.createCell(columnNo++);
            cell.setCellValue((String) getDamayanListingNoticeData().getDataConvert()
                    .convertSdToScAcctno(getDamayanListingNoticeData().getDamayanList().get(i)[2].toString()));
            cell.setCellStyle(cellStyle);
            System.out.println("SC Account No");

            //Account Name
            columnNo = 2;
            cell = dataRow.createCell(columnNo++);
            cell.setCellValue((String) getDamayanListingNoticeData().getDamayanList().get(i)[3]);
            cell.setCellStyle(cellStyle);
            System.out.println("Account Name");

            //Account Status
            columnNo = 3;
            cell = dataRow.createCell(columnNo++);
            cell.setCellValue(getDamayanListingNoticeData().getDataConvert().acctStatusConvert(
                    getDamayanListingNoticeData().getDamayanList().get(i)[4].toString().charAt(0)));
            cell.setCellStyle(cellStyle);
            System.out.println("Account Status");

            //Payment Type
            columnNo = 4;
            cell = dataRow.createCell(columnNo++);
            cell.setCellValue((String) getDamayanListingNoticeData().getDamayanList().get(i)[5].toString()
                    .replace("true", "Auto Deduction").replace("false", "Manual Deduction"));
            cell.setCellStyle(cellStyle);
            System.out.println("Payment Type");

            //PD Form
            try {
                columnNo = 5;
                cell = dataRow.createCell(columnNo++);
                cell.setCellValue((String) getDamayanListingNoticeData().getDamayanList().get(i)[6].toString());
                cell.setCellStyle(cellStyle);
                System.out.println("PD Form");
            } catch (Exception e) {
                System.out.println("PD Form null " + e);
            }

            //Balance
            columnNo = 6;
            cell = dataRow.createCell(columnNo++);
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue(getDamayanListingNoticeData().getDataConvert()
                    .convertAmount((BigDecimal) getDamayanListingNoticeData().getDamayanList().get(i)[7]));
            cell.setCellStyle(cellStyle);
            System.out.println("Balance");

            if (i == getDamayanListingNoticeData().getDamayanList().size() - 1) {
                dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                dataRow = sheet.createRow(dataRow.getRowNum() + 1);

                columnNo = 4;
                cell = dataRow.createCell(columnNo++);
                cell.setCellValue("TOTAL");
                cell.setCellStyle(boldStyle);

                dataRow.createCell(columnNo++).setCellValue("");

                columnNo = 6;
                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(getDamayanListingNoticeData().getDataConvert()
                        .convertAmount(getDamayanListingNoticeData().getGrandTotal()));
                cell.setCellStyle(boldStyle);
            }
            System.out.println("Total");
        }
        FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename());
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        getExportData().fileUploadByDL(getExportData().getFilename(), "Damayan Listing Notice", themeDisplay,
                null);

        File file = new File(getExportData().getFilename());

        if (file.exists()) {
            file.delete();
        }
    } catch (Exception e) {
        System.out.println("damayanlistingnoticecontroller.export0 " + e);
        FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error",
                "An error occurred while generating excel file.");
        FacesContext.getCurrentInstance().addMessage(null, message);
    }
}

---

From source file:bean.ExportData.java

public void memberList(List<CoopMember> memberData, MemberFilterData filterData) {
    String fName = getFilename();
    ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay();
    createFolder(null, themeDisplay, "Membership export", "Description");

    int x = 0, y = 0;
    if (getFilename() == null || getFilename().length() == 0) {
        setFilename("Filtered Data List(" + new Date() + ")");
    }/*w  w w . j a va2  s  .c om*/

    try {
        setFilename("" + getFilename() + ".xls");
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("FirstSheet");

        HSSFRow rowhead = sheet.createRow((short) 0);

        rowhead.createCell(y++).setCellValue("Member No.");
        rowhead.createCell(y++).setCellValue("Full Name");
        rowhead.createCell(y++).setCellValue("PT Number");
        rowhead.createCell(y++).setCellValue("Account No.");

        if (getExportColumns().get(0)) {
            rowhead.createCell(y++).setCellValue("Address");
        }
        if (getExportColumns().get(1)) {
            rowhead.createCell(y++).setCellValue("Gender");
        }
        if (getExportColumns().get(2)) {
            rowhead.createCell(y++).setCellValue("Civil Status");
        }
        if (getExportColumns().get(3)) {
            rowhead.createCell(y++).setCellValue("Birthdate");
        }
        if (getExportColumns().get(4)) {
            rowhead.createCell(y++).setCellValue("TIN");
        }
        if (getExportColumns().get(5)) {
            rowhead.createCell(y++).setCellValue("Pag-Ibig");
        }
        if (getExportColumns().get(6)) {
            rowhead.createCell(y++).setCellValue("Philhealth");
        }
        if (getExportColumns().get(7)) {
            rowhead.createCell(y++).setCellValue("SSS");
        }
        if (getExportColumns().get(8)) {
            rowhead.createCell(y++).setCellValue("GSIS");
        }
        if (getExportColumns().get(9)) {
            rowhead.createCell(y++).setCellValue("Membership Date");
        }
        if (getExportColumns().get(10)) {
            rowhead.createCell(y++).setCellValue("Membership Status");
        }
        if (getExportColumns().get(11)) {
            rowhead.createCell(y++).setCellValue("Skill");
        }
        if (getExportColumns().get(12)) {
            rowhead.createCell(y++).setCellValue("Compensation Bracket");
        }
        if (getExportColumns().get(13)) {
            rowhead.createCell(y++).setCellValue("Rank Position");
        }
        if (getExportColumns().get(14)) {
            rowhead.createCell(y++).setCellValue("Educational Attainment");
        }

        for (int i = 0; i != memberData.size(); i++) {
            HSSFRow row = sheet.createRow((short) i + 1);
            row.createCell(x++).setCellValue(memberData.get(i).getMemNo());
            row.createCell(x++)
                    .setCellValue(getDataConvert().fullname(memberData.get(i).getPPrefix(),
                            memberData.get(i).getLastName(), memberData.get(i).getFirstName(),
                            memberData.get(i).getMiddleName(), memberData.get(i).getSuffix()));
            try {
                row.createCell(x++).setCellValue(memberData.get(i).getOuCode().getOuShortName());
            } catch (Exception e) {
                row.createCell(x++).setCellValue("");
            }
            row.createCell(x++).setCellValue(memberData.get(i).getScAcctno());
            if (getExportColumns().get(0)) {
                row.createCell(x++).setCellValue(memberData.get(i).getStreet() + " "
                        + memberData.get(i).getBarangay() + " " + memberData.get(i).getCityMun() + " "
                        + (memberData.get(i).getProvince() != null ? memberData.get(i).getProvince() : ""));
            }
            if (getExportColumns().get(1)) {
                row.createCell(x++).setCellValue(getDataConvert().genderConv(memberData.get(i).getGender()));
            }
            if (getExportColumns().get(2)) {
                row.createCell(x++)
                        .setCellValue(getDataConvert().civilStatusConv(memberData.get(i).getCivilStatus()));
            }
            if (getExportColumns().get(3)) {
                HSSFCell cell = row.createCell(x++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(getCustomDate().formatDate(memberData.get(i).getBirthdate(), "MM/dd/YYYY"));
                HSSFCellStyle dateCellStyle = workbook.createCellStyle();
                short df = workbook.createDataFormat().getFormat("MM/dd/YYYY");
                dateCellStyle.setDataFormat(df);
                cell.setCellStyle(dateCellStyle);
            }
            if (getExportColumns().get(4)) {
                row.createCell(x++).setCellValue(memberData.get(i).getTaxIdNo());
            }
            if (getExportColumns().get(5)) {
                row.createCell(x++).setCellValue(memberData.get(i).getPagIbig());
            }
            if (getExportColumns().get(6)) {
                row.createCell(x++).setCellValue(memberData.get(i).getPhilhealth());
            }
            if (getExportColumns().get(7)) {
                row.createCell(x++).setCellValue(memberData.get(i).getSss());
            }
            if (getExportColumns().get(8)) {
                row.createCell(x++).setCellValue(memberData.get(i).getGsis());
            }
            if (getExportColumns().get(9)) {
                HSSFCell cell = row.createCell(x++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(getCustomDate().formatDate(memberData.get(i).getMemDate(), "MM/dd/YYYY"));
                HSSFCellStyle dateCellStyle = workbook.createCellStyle();
                short df = workbook.createDataFormat().getFormat("MM/dd/YYYY");
                dateCellStyle.setDataFormat(df);
                cell.setCellStyle(dateCellStyle);
            }
            if (getExportColumns().get(10)) {
                row.createCell(x++).setCellValue(memberData.get(i).getStatusId().getStatusName());
            }
            if (getExportColumns().get(11)) {
                //this is conditional                    
                try {
                    String skl = (String) getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory()
                            .createEntityManager()
                            .createQuery("SELECT c.skillsCode.skillsName FROM CoopSkillsMem c "
                                    + "WHERE UPPER(c.skillsCode.skillsName) LIKE UPPER('%"
                                    + filterData.getSkill() + "%') " + "AND c.memNo.memNo ='"
                                    + memberData.get(i).getMemNo() + "'")
                            .getResultList().get(0);
                    row.createCell(x++).setCellValue(skl);
                } catch (Exception e) {
                    row.createCell(x++).setCellValue("");
                }
            }
            if (getExportColumns().get(12)) {
                //                    if (filterData.getCompensation() != null) {
                //                        row.createCell(x++).setCellValue(filterData.getCompensation());
                //                    } else {
                //                        try {
                //                            String cmpBrckt = (String) getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.emplDtlNum.compBracket FROM CoopEmplDtlMem c "
                //                                    + "WHERE c.memNo.memNo ='" + memberData.get(i).getMemNo() + "'").getResultList().get(0);
                //                            row.createCell(x++).setCellValue(cmpBrckt);
                //                        } catch (Exception e) {
                //                            row.createCell(x++).setCellValue("");
                //                        }
                //                    }
                //new start                    
                if (filterData.getCompensation() != null) {
                    row.createCell(x++).setCellValue(filterData.getCompensation());
                } else {
                    try {
                        String cmpBrckt = (String) getCustomEntityManagerFactory()
                                .getLportalMemOrgEntityManagerFactory().createEntityManager()
                                .createQuery("SELECT c.emplDtlNum.compBracket FROM CoopEmplDtlMem c "
                                        + "WHERE c.memNo.memNo ='" + memberData.get(i).getMemNo() + "'")
                                .getResultList().get(0);
                        row.createCell(x++).setCellValue(cmpBrckt);
                    } catch (Exception e) {
                        row.createCell(x++).setCellValue("");
                    }
                }
                //new end                    
            }
            if (getExportColumns().get(13)) {
                //                    try {
                //                        String rnkPos = (String) getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.emplDtlNum.emplRankPos.unitName FROM CoopEmplDtlMem c "
                //                                + "WHERE UPPER(c.emplDtlNum.emplRankPos.unitName) LIKE UPPER('%" + filterData.getRankPos() + "%') "
                //                                + "AND c.memNo.memNo ='" + memberData.get(i).getMemNo() + "'").getResultList().get(0);
                //                        row.createCell(x++).setCellValue(rnkPos);
                //                    } catch (Exception e) {
                //                        row.createCell(x++).setCellValue("");
                //                    }
                //new start                 
                //this is conditional                    
                try {
                    String rnkPos = (String) getCustomEntityManagerFactory()
                            .getLportalMemOrgEntityManagerFactory().createEntityManager()
                            .createQuery("SELECT c.emplDtlNum.emplRankPos.unitName FROM CoopEmplDtlMem c "
                                    + "WHERE UPPER(c.emplDtlNum.emplRankPos.unitName) LIKE UPPER('%"
                                    + filterData.getRankPos() + "%') " + "AND c.memNo.memNo ='"
                                    + memberData.get(i).getMemNo() + "'")
                            .getResultList().get(0);
                    row.createCell(x++).setCellValue(rnkPos);
                } catch (Exception e) {
                    row.createCell(x++).setCellValue("");
                }
                //new end                    
            }
            if (getExportColumns().get(14)) {
                List<CoopEducInfo> educInfo = new ArrayList<>();
                //new start
                educInfo = getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory()
                        .createEntityManager().createQuery("SELECT c.educInfoNum FROM CoopEducInfoMem c "
                                + "WHERE c.memNo.memNo ='" + memberData.get(i).getMemNo() + "'")
                        .getResultList();
                for (int a = 0; a != educInfo.size(); a++) {
                    rowhead.createCell(x).setCellValue("Educational Attainment " + (a + 1));
                    row.createCell(x++).setCellValue(getDataConvert().educInfo(educInfo.get(a)));
                }
                //new end
                //                    //POST-GRADUATE
                //                    educInfo = getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.educInfoNum FROM CoopEducInfoMem c "
                //                            + "WHERE c.educInfoNum.schoolLevel ='POST-GRADUATE' "
                //                            + "AND c.memNo.memNo ='" + memberData.get(i).getMemNo() + "'").getResultList();
                //                    for (int a = 0; a != educInfo.size(); a++) {
                //                        rowhead.createCell(x).setCellValue("Educational Attainment " + (a + 1));
                //                        row.createCell(x++).setCellValue(getDataConvert().educInfo(educInfo.get(a)));
                //                    }
                //
                //                    //COLLEGE
                //                    if (educInfo.isEmpty()) {
                //                        educInfo = getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.educInfoNum FROM CoopEducInfoMem c "
                //                                + "WHERE c.educInfoNum.schoolLevel ='COLLEGE' "
                //                                + "AND c.memNo.memNo ='" + memberData.get(i).getMemNo() + "'").getResultList();
                //                        for (int a = 0; a != educInfo.size(); a++) {
                //                            rowhead.createCell(x).setCellValue("Educational Attainment " + (a + 1));
                //                            row.createCell(x++).setCellValue(getDataConvert().educInfo(educInfo.get(a)));
                //                        }
                //                    }
                //
                //                    //VOCATIONAL
                //                    if (educInfo.isEmpty()) {
                //                        educInfo = getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.educInfoNum FROM CoopEducInfoMem c "
                //                                + "WHERE c.educInfoNum.schoolLevel ='VOCATIONAL' "
                //                                + "AND c.memNo.memNo ='" + memberData.get(i).getMemNo() + "'").getResultList();
                //                        for (int a = 0; a != educInfo.size(); a++) {
                //                            rowhead.createCell(x).setCellValue("Educational Attainment " + (a + 1));
                //                            row.createCell(x++).setCellValue(getDataConvert().educInfo(educInfo.get(a)));
                //                        }
                //                    }
                //
                //                    //HIGH SCHOOL
                //                    if (educInfo.isEmpty()) {
                //                        educInfo = getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.educInfoNum FROM CoopEducInfoMem c "
                //                                + "WHERE c.educInfoNum.schoolLevel ='HIGH SCHOOL' "
                //                                + "AND c.memNo.memNo ='" + memberData.get(i).getMemNo() + "'").getResultList();
                //                        for (int a = 0; a != educInfo.size(); a++) {
                //                            rowhead.createCell(x).setCellValue("Educational Attainment " + (a + 1));
                //                            row.createCell(x++).setCellValue(getDataConvert().educInfo(educInfo.get(a)));
                //                        }
                //                    }
                //
                //                    //ELEMENTARY
                //                    if (educInfo.isEmpty()) {
                //                        educInfo = getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.educInfoNum FROM CoopEducInfoMem c "
                //                                + "WHERE c.educInfoNum.schoolLevel ='ELEMENTARY' "
                //                                + "AND c.memNo.memNo ='" + memberData.get(i).getMemNo() + "'").getResultList();
                //                        for (int a = 0; a != educInfo.size(); a++) {
                //                            rowhead.createCell(x).setCellValue("Educational Attainment " + (a + 1));
                //                            row.createCell(x++).setCellValue(getDataConvert().educInfo(educInfo.get(a)));
                //                        }
                //                    }

                if (educInfo.isEmpty()) {
                    row.createCell(x++).setCellValue("");
                }
            }
            x = 0;
        }

        FileOutputStream fileOut = new FileOutputStream(getFilename());
        workbook.write(fileOut);
        fileOut.close();

        fileUploadByDL(getFilename(), "Membership export", themeDisplay, null);

        //delete file
        File file = new File(getFilename());
        if (file.exists()) {
            file.delete();
        }
    } catch (Exception ex) {
        System.out.println(ex);
        FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error",
                "An error occurred while generating excel file.");
        FacesContext.getCurrentInstance().addMessage(null, message);
    }
    setFilename("" + fName + "");
}

---

From source file:bean.ExportData.java

public void associateList(List<CoopAssociate> associateData, MemberFilterData filterData) {
    String fName = getFilename();
    ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay();
    createFolder(null, themeDisplay, "Membership export", "Description");

    int x = 0, y = 0;
    if (getFilename() == null || getFilename().length() == 0) {
        setFilename("Filtered Data List(" + new Date() + ")");
    }/*w  ww  . j a v  a 2  s. c o  m*/

    try {
        setFilename("" + getFilename() + ".xls");
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("FirstSheet");

        HSSFRow rowhead = sheet.createRow((short) 0);

        rowhead.createCell(y++).setCellValue("Member No.");
        rowhead.createCell(y++).setCellValue("Full Name");
        rowhead.createCell(y++).setCellValue("PT Number");
        rowhead.createCell(y++).setCellValue("Account No.");

        if (getExportColumns().get(0)) {
            rowhead.createCell(y++).setCellValue("Address");
        }
        if (getExportColumns().get(1)) {
            rowhead.createCell(y++).setCellValue("Gender");
        }
        if (getExportColumns().get(2)) {
            rowhead.createCell(y++).setCellValue("Civil Status");
        }
        if (getExportColumns().get(3)) {
            rowhead.createCell(y++).setCellValue("Birthdate");
        }
        if (getExportColumns().get(4)) {
            rowhead.createCell(y++).setCellValue("TIN");
        }
        if (getExportColumns().get(5)) {
            rowhead.createCell(y++).setCellValue("Pag-Ibig");
        }
        if (getExportColumns().get(6)) {
            rowhead.createCell(y++).setCellValue("Philhealth");
        }
        if (getExportColumns().get(7)) {
            rowhead.createCell(y++).setCellValue("SSS");
        }
        if (getExportColumns().get(8)) {
            rowhead.createCell(y++).setCellValue("GSIS");
        }
        if (getExportColumns().get(9)) {
            rowhead.createCell(y++).setCellValue("Membership Date");
        }
        if (getExportColumns().get(10)) {
            rowhead.createCell(y++).setCellValue("Membership Status");
        }
        if (getExportColumns().get(11)) {
            rowhead.createCell(y++).setCellValue("Skill");
        }
        if (getExportColumns().get(12)) {
            rowhead.createCell(y++).setCellValue("Compensation Bracket");
        }
        if (getExportColumns().get(13)) {
            rowhead.createCell(y++).setCellValue("Rank Position");
        }
        if (getExportColumns().get(14)) {
            rowhead.createCell(y++).setCellValue("Educational Attainment");
        }

        for (int i = 0; i != associateData.size(); i++) {
            HSSFRow row = sheet.createRow((short) i + 1);
            row.createCell(x++).setCellValue(associateData.get(i).getMemNo());
            row.createCell(x++)
                    .setCellValue(getDataConvert().fullname(associateData.get(i).getPPrefix(),
                            associateData.get(i).getLastName(), associateData.get(i).getFirstName(),
                            associateData.get(i).getMiddleName(), associateData.get(i).getSuffix()));
            try {
                row.createCell(x++).setCellValue(associateData.get(i).getOuCode().getOuShortName());
            } catch (Exception e) {
            }
            row.createCell(x++).setCellValue(associateData.get(i).getScAcctno());
            if (getExportColumns().get(0)) {
                row.createCell(x++).setCellValue(associateData.get(i).getStreet() + " "
                        + associateData.get(i).getBarangay() + " " + associateData.get(i).getCityMun() + " "
                        + (associateData.get(i).getProvince() != null ? associateData.get(i).getProvince()
                                : ""));
            }
            if (getExportColumns().get(1)) {
                row.createCell(x++).setCellValue(getDataConvert().genderConv(associateData.get(i).getGender()));
            }
            if (getExportColumns().get(2)) {
                row.createCell(x++)
                        .setCellValue(getDataConvert().civilStatusConv(associateData.get(i).getCivilStatus()));
            }
            if (getExportColumns().get(3)) {
                HSSFCell cell = row.createCell(x++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(
                        getCustomDate().formatDate(associateData.get(i).getBirthdate(), "MM/dd/YYYY"));
                HSSFCellStyle dateCellStyle = workbook.createCellStyle();
                short df = workbook.createDataFormat().getFormat("MM/dd/YYYY");
                dateCellStyle.setDataFormat(df);
                cell.setCellStyle(dateCellStyle);
            }
            if (getExportColumns().get(4)) {
                row.createCell(x++).setCellValue(associateData.get(i).getTaxIdNo());
            }
            if (getExportColumns().get(5)) {
                row.createCell(x++).setCellValue(associateData.get(i).getPagIbig());
            }
            if (getExportColumns().get(6)) {
                row.createCell(x++).setCellValue(associateData.get(i).getPhilhealth());
            }
            if (getExportColumns().get(7)) {
                row.createCell(x++).setCellValue(associateData.get(i).getSss());
            }
            if (getExportColumns().get(8)) {
                row.createCell(x++).setCellValue(associateData.get(i).getGsis());
            }
            if (getExportColumns().get(9)) {
                HSSFCell cell = row.createCell(x++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(getCustomDate().formatDate(associateData.get(i).getMemDate(), "MM/dd/YYYY"));
                HSSFCellStyle dateCellStyle = workbook.createCellStyle();
                short df = workbook.createDataFormat().getFormat("MM/dd/YYYY");
                dateCellStyle.setDataFormat(df);
                cell.setCellStyle(dateCellStyle);
            }
            if (getExportColumns().get(10)) {
                row.createCell(x++).setCellValue(associateData.get(i).getStatusId().getStatusName());
            }
            if (getExportColumns().get(11)) {
                //this is conditional
                try {
                    String skl = (String) getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory()
                            .createEntityManager()
                            .createQuery("SELECT c.skillsCode.skillsName FROM CoopSkillsAssoc c "
                                    + "WHERE UPPER(c.skillsCode.skillsName) LIKE UPPER('%"
                                    + filterData.getSkill() + "%') " + "AND c.memNo.memNo ='"
                                    + associateData.get(i).getMemNo() + "'")
                            .getResultList().get(0);
                    row.createCell(x++).setCellValue(skl);
                } catch (Exception e) {
                    row.createCell(x++).setCellValue("");
                }
            }
            if (getExportColumns().get(12)) {
                //                    if (filterData.getCompensation() != null) {
                //                        row.createCell(x++).setCellValue(filterData.getCompensation());
                //                    } else {
                //                        try {
                //                            String cmpBrckt = (String) getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.emplDtlNum.compBracket FROM CoopEmplDtlAssoc c "
                //                                    + "WHERE c.memNo.memNo ='" + associateData.get(i).getMemNo() + "'").getResultList().get(0);
                //                            row.createCell(x++).setCellValue(cmpBrckt);
                //                        } catch (Exception e) {
                //                            row.createCell(x++).setCellValue("");
                //                        }
                //                    }
                //new start                    
                if (filterData.getCompensation() != null) {
                    row.createCell(x++).setCellValue(filterData.getCompensation());
                } else {
                    try {
                        String cmpBrckt = (String) getCustomEntityManagerFactory()
                                .getLportalMemOrgEntityManagerFactory().createEntityManager()
                                .createQuery("SELECT c.emplDtlNum.compBracket FROM CoopEmplDtlAssoc c "
                                        + "WHERE c.memNo.memNo ='" + associateData.get(i).getMemNo() + "'")
                                .getResultList().get(0);
                        row.createCell(x++).setCellValue(cmpBrckt);
                    } catch (Exception e) {
                        row.createCell(x++).setCellValue("");
                    }
                }
                //new end
            }
            if (getExportColumns().get(13)) {
                //                    try {
                //                        String rnkPos = (String) getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.emplDtlNum.emplRankPos.unitName FROM CoopEmplDtlAssoc c "
                //                                + "WHERE UPPER(c.emplDtlNum.emplRankPos.unitName) LIKE UPPER('%" + filterData.getRankPos() + "%') "
                //                                + "AND c.memNo.memNo ='" + associateData.get(i).getMemNo() + "'").getResultList().get(0);
                //                        row.createCell(x++).setCellValue(rnkPos);
                //                    } catch (Exception e) {
                //                        row.createCell(x++).setCellValue("");
                //                    }
                //new start                 
                //this is conditional                    
                try {
                    String rnkPos = (String) getCustomEntityManagerFactory()
                            .getLportalMemOrgEntityManagerFactory().createEntityManager()
                            .createQuery("SELECT c.emplDtlNum.emplRankPos.unitName FROM CoopEmplDtlAssoc c "
                                    + "WHERE UPPER(c.emplDtlNum.emplRankPos.unitName) LIKE UPPER('%"
                                    + filterData.getRankPos() + "%') " + "AND c.memNo.memNo ='"
                                    + associateData.get(i).getMemNo() + "'")
                            .getResultList().get(0);
                    row.createCell(x++).setCellValue(rnkPos);
                } catch (Exception e) {
                    row.createCell(x++).setCellValue("");
                }
                //new end   
            }
            if (getExportColumns().get(14)) {
                List<CoopEducInfo> educInfo = new ArrayList<>();
                //new start
                educInfo = getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory()
                        .createEntityManager().createQuery("SELECT c.educInfoNum FROM CoopEducInfoAssoc c "
                                + "WHERE c.memNo.memNo ='" + associateData.get(i).getMemNo() + "'")
                        .getResultList();
                for (int a = 0; a != educInfo.size(); a++) {
                    rowhead.createCell(x).setCellValue("Educational Attainment " + (a + 1));
                    row.createCell(x++).setCellValue(getDataConvert().educInfo(educInfo.get(a)));
                }
                //new end
                //                    //POST-GRADUATE
                //                    educInfo = getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.educInfoNum FROM CoopEducInfoAssoc c "
                //                            + "WHERE c.educInfoNum.schoolLevel ='POST-GRADUATE' "
                //                            + "AND c.memNo.memNo ='" + associateData.get(i).getMemNo() + "'").getResultList();
                //                    for (int a = 0; a != educInfo.size(); a++) {
                //                        rowhead.createCell(x).setCellValue("Educational Attainment " + (a + 1));
                //                        row.createCell(x++).setCellValue(getDataConvert().educInfo(educInfo.get(a)));
                //                    }
                //
                //                    //COLLEGE
                //                    if (educInfo.isEmpty()) {
                //                        educInfo = getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.educInfoNum FROM CoopEducInfoAssoc c "
                //                                + "WHERE c.educInfoNum.schoolLevel ='COLLEGE' "
                //                                + "AND c.memNo.memNo ='" + associateData.get(i).getMemNo() + "'").getResultList();
                //                        for (int a = 0; a != educInfo.size(); a++) {
                //                            rowhead.createCell(x).setCellValue("Educational Attainment " + (a + 1));
                //                            row.createCell(x++).setCellValue(getDataConvert().educInfo(educInfo.get(a)));
                //                        }
                //                    }
                //
                //                    //VOCATIONAL
                //                    if (educInfo.isEmpty()) {
                //                        educInfo = getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.educInfoNum FROM CoopEducInfoAssoc c "
                //                                + "WHERE c.educInfoNum.schoolLevel ='VOCATIONAL' "
                //                                + "AND c.memNo.memNo ='" + associateData.get(i).getMemNo() + "'").getResultList();
                //                        for (int a = 0; a != educInfo.size(); a++) {
                //                            rowhead.createCell(x).setCellValue("Educational Attainment " + (a + 1));
                //                            row.createCell(x++).setCellValue(getDataConvert().educInfo(educInfo.get(a)));
                //                        }
                //                    }
                //
                //                    //HIGH SCHOOL
                //                    if (educInfo.isEmpty()) {
                //                        educInfo = getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.educInfoNum FROM CoopEducInfoAssoc c "
                //                                + "WHERE c.educInfoNum.schoolLevel ='HIGH SCHOOL' "
                //                                + "AND c.memNo.memNo ='" + associateData.get(i).getMemNo() + "'").getResultList();
                //                        for (int a = 0; a != educInfo.size(); a++) {
                //                            rowhead.createCell(x).setCellValue("Educational Attainment " + (a + 1));
                //                            row.createCell(x++).setCellValue(getDataConvert().educInfo(educInfo.get(a)));
                //                        }
                //                    }
                //
                //                    //ELEMENTARY
                //                    if (educInfo.isEmpty()) {
                //                        educInfo = getCustomEntityManagerFactory().getLportalMemOrgEntityManagerFactory().createEntityManager().createQuery("SELECT c.educInfoNum FROM CoopEducInfoAssoc c "
                //                                + "WHERE c.educInfoNum.schoolLevel ='ELEMENTARY' "
                //                                + "AND c.memNo.memNo ='" + associateData.get(i).getMemNo() + "'").getResultList();
                //                        for (int a = 0; a != educInfo.size(); a++) {
                //                            rowhead.createCell(x).setCellValue("Educational Attainment " + (a + 1));
                //                            row.createCell(x++).setCellValue(getDataConvert().educInfo(educInfo.get(a)));
                //                        }
                //                    }

                if (educInfo.isEmpty()) {
                    row.createCell(x++).setCellValue("");
                }
            }
            x = 0;
        }

        FileOutputStream fileOut = new FileOutputStream(getFilename());
        workbook.write(fileOut);
        fileOut.close();

        fileUploadByDL(getFilename(), "Membership export", themeDisplay, null);

        //delete file
        File file = new File(getFilename());
        if (file.exists()) {
            file.delete();
        }
    } catch (Exception ex) {
        System.out.println(ex);
        FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error",
                "An error occurred while generating excel file.");
        FacesContext.getCurrentInstance().addMessage(null, message);
    }
    setFilename("" + fName + "");
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »