Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook createFont

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createFont

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. createFont

Introduction

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

Prototype


@Override
public HSSFFont createFont()

Source Link

Document

create a new Font and add it to the workbook's font table

Usage

From source file:bean.ClassTdData.java

public void export0() {
    Integer columnNo;/*from w  ww .j  a v  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, "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  2s. 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 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 ww w.j  a  va 2  s . co  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.GlReportExport.java

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

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

    getExportData().createFolder(null, themeDisplay, "GL 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);

        sheet = workbook.createSheet("GL Report " + exportDate());

        dataRow = sheet.createRow((short) 0);
        columnNo = 0;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("GL REPORT");
        cell.setCellStyle(boldStyle);

        dataRow = sheet.createRow(dataRow.getRowNum() + 1);
        columnNo = 0;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue(exportDate());
        cell.setCellStyle(boldStyle);

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

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

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

        columnNo = 2;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("Journal Type");
        cell.setCellStyle(boldStyle);

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

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

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

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

            //DATE
            columnNo = 0;
            cell = dataRow.createCell(columnNo++);
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue(((Date) getGlReportData().getGlReport().get(i)[0]));
            cell.setCellStyle(cellStyle);
            cell.getDateCellValue();
            //                
            dataRow.createCell(columnNo++).setCellValue("");

            //ACCOUNT TITLE
            columnNo = 1;
            cell = dataRow.createCell(columnNo++);
            cell.setCellValue((String) getGlReportData().getGlReport().get(i)[1]);
            cell.setCellStyle(cellStyle);

            //JOURNAL TYPE
            columnNo = 2;
            cell = dataRow.createCell(columnNo++);
            cell.setCellValue((String) getGlReportData().getGlReport().get(i)[2]);
            cell.setCellStyle(cellStyle);

            //DEBIT
            columnNo = 3;
            cell = dataRow.createCell(columnNo++);
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue(((BigDecimal) getGlReportData().getGlReport().get(i)[3]).doubleValue());
            cell.setCellStyle(cellStyle);

            //CREDIT 
            columnNo = 4;
            cell = dataRow.createCell(columnNo++);
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue(((BigDecimal) getGlReportData().getGlReport().get(i)[4]).doubleValue());
            cell.setCellStyle(cellStyle);

            //BALANCE
            columnNo = 5;
            cell = dataRow.createCell(columnNo++);
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue(((BigDecimal) getGlReportData().getGlReport().get(i)[5]).doubleValue());
            cell.setCellStyle(cellStyle);

            //                if (i == getSlReportData().getSlReport().size() - 1) {
            //                    dataRow = sheet.createRow(dataRow.getRowNum() + 1);
            //                    dataRow = sheet.createRow(dataRow.getRowNum() + 1);
            //                    columnNo = 0;
            //
            //                    cell = dataRow.createCell(columnNo++);
            //                    cell.setCellValue("TOTAL:");
            //                    cell.setCellStyle(boldStyle);
            //
            //                    dataRow.createCell(columnNo++).setCellValue("");
            //
            //                    columnNo = 3;
            //
            //                    cell = dataRow.createCell(columnNo++);
            //                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            //                    cell.setCellValue(getDataConvert().convertAmount(getTrialBalanceData().getTrialBalanceDebitTotal()));
            //                    cell.setCellStyle(boldStyle);
            //
            //                    columnNo = 4;
            //
            //                    cell = dataRow.createCell(columnNo++);
            //                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            //                    cell.setCellValue(getDataConvert().convertAmount(getTrialBalanceData().getTrialBalanceCreditTotal()));
            //                    cell.setCellStyle(boldStyle);
            //                }
        }
        FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename());
        workbook.write(fileOutputStream);
        fileOutputStream.close();

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

        File file = new File(getExportData().getFilename());
        if (file.exists()) {
            file.delete();
        }
    } catch (Exception e) {
        System.out.print("glReportExport().export() " + 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.SlReportExport.java

public void export() {
    Integer columnNo;//from  www .j av  a2 s  . co  m
    HSSFWorkbook workbook;
    HSSFSheet sheet;
    HSSFRow dataRow;
    HSSFCell cell;
    HSSFCellStyle cellStyle, boldStyle;
    HSSFFont font;

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

    getExportData().createFolder(null, themeDisplay, "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);

        sheet = workbook.createSheet("SL Report " + exportDate());

        dataRow = sheet.createRow((short) 0);
        columnNo = 0;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("SL REPORT");
        cell.setCellStyle(boldStyle);

        dataRow = sheet.createRow(dataRow.getRowNum() + 1);
        columnNo = 0;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue(exportDate());
        cell.setCellStyle(boldStyle);

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

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

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

        columnNo = 2;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("Journal Type");
        cell.setCellStyle(boldStyle);

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

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

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

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

            //DATE
            columnNo = 0;
            cell = dataRow.createCell(columnNo++);
            //                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue((Date) getSlReportData().getSlReport().get(i)[0]);
            cell.setCellStyle(cellStyle);
            //                
            dataRow.createCell(columnNo++).setCellValue("");

            //ACCOUNT TITLE
            columnNo = 1;
            cell = dataRow.createCell(columnNo++);
            cell.setCellValue((String) getSlReportData().getSlReport().get(i)[1]);
            cell.setCellStyle(cellStyle);

            //JOURNAL TYPE
            columnNo = 2;
            cell = dataRow.createCell(columnNo++);
            cell.setCellValue((String) getSlReportData().getSlReport().get(i)[2]);
            cell.setCellStyle(cellStyle);

            //DEBIT
            columnNo = 3;
            cell = dataRow.createCell(columnNo++);
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue(((BigDecimal) getSlReportData().getSlReport().get(i)[3]).doubleValue());
            cell.setCellStyle(cellStyle);

            //CREDIT 
            columnNo = 4;
            cell = dataRow.createCell(columnNo++);
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue(((BigDecimal) getSlReportData().getSlReport().get(i)[4]).doubleValue());
            cell.setCellStyle(cellStyle);

            //BALANCE
            columnNo = 5;
            cell = dataRow.createCell(columnNo++);
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue(((BigDecimal) getSlReportData().getSlReport().get(i)[5]).doubleValue());
            cell.setCellStyle(cellStyle);

            //                if (i == getSlReportData().getSlReport().size() - 1) {
            //                    dataRow = sheet.createRow(dataRow.getRowNum() + 1);
            //                    dataRow = sheet.createRow(dataRow.getRowNum() + 1);
            //                    columnNo = 0;
            //
            //                    cell = dataRow.createCell(columnNo++);
            //                    cell.setCellValue("TOTAL:");
            //                    cell.setCellStyle(boldStyle);
            //
            //                    dataRow.createCell(columnNo++).setCellValue("");
            //
            //                    columnNo = 3;
            //
            //                    cell = dataRow.createCell(columnNo++);
            //                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            //                    cell.setCellValue(getDataConvert().convertAmount(getTrialBalanceData().getTrialBalanceDebitTotal()));
            //                    cell.setCellStyle(boldStyle);
            //
            //                    columnNo = 4;
            //
            //                    cell = dataRow.createCell(columnNo++);
            //                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            //                    cell.setCellValue(getDataConvert().convertAmount(getTrialBalanceData().getTrialBalanceCreditTotal()));
            //                    cell.setCellStyle(boldStyle);
            //                }
        }
        FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename());
        workbook.write(fileOutputStream);
        fileOutputStream.close();

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

        File file = new File(getExportData().getFilename());
        if (file.exists()) {
            file.delete();
        }
    } catch (Exception e) {
        System.out.print("slReportExport().export() " + 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.StatementOfFinancialConditionExport.java

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

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

    getExportData().createFolder(null, themeDisplay, "Statement of Financial Condition 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);

        sheet = workbook.createSheet(
                "Statement of Financial Condition Report " + getFinancialReportData().getCurrentMonth() != null
                        ? getDataConvert().monthConvert(getFinancialReportData().getCurrentMonth()) + " "
                                + getFinancialReportData().getCurrentYear().toString()
                        : getFinancialReportData().getCurrentYear().toString());

        dataRow = sheet.createRow((short) 0);
        columnNo = 0;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("STATEMENT OF FINANCIAL CONDITION");
        cell.setCellStyle(boldStyle);

        dataRow = sheet.createRow(dataRow.getRowNum() + 1);
        columnNo = 0;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue(getFinancialReportData().getCurrentMonth() != null
                ? getDataConvert().monthConvert(getFinancialReportData().getCurrentMonth()) + " "
                        + getFinancialReportData().getCurrentYear().toString()
                : getFinancialReportData().getCurrentYear().toString());
        cell.setCellStyle(boldStyle);

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

        dataRow = sheet.createRow(dataRow.getRowNum() + 1);
        columnNo = 0;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("ASSETS");
        cell.setCellStyle(boldStyle);
        //                      

        for (int i = 0; i < getStatementOfFinancialConditionData().getAssetsLevel2().size(); i++) {
            dataRow = sheet.createRow(dataRow.getRowNum() + 1);
            columnNo = 0;

            cell = dataRow.createCell(columnNo++);
            cell.setCellValue(getStatementOfFinancialConditionData().getAssetsLevel2().get(i).getAcctTitle());
            cell.setCellStyle(boldStyle);
            //                
            dataRow.createCell(columnNo++).setCellValue("");
            if (getStatementOfFinancialConditionData().getAssetsLevel3().get(i).isEmpty()) {
                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(getStatementOfFinancialConditionData().getAssetsLevel2().get(i).getAmount()
                        .doubleValue());
                cell.setCellStyle(cellStyle);
            }
            //                
            for (int ii = 0; ii < getStatementOfFinancialConditionData().getAssetsLevel3().get(i)
                    .size(); ii++) {
                dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                columnNo = 0;

                dataRow.createCell(columnNo++).setCellValue("");
                dataRow.createCell(columnNo++).setCellValue(
                        getStatementOfFinancialConditionData().getAssetsLevel3().get(i).get(ii).getAcctTitle());

                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(getStatementOfFinancialConditionData().getAssetsLevel3().get(i).get(ii)
                        .getAmount().doubleValue());
                cell.setCellStyle(cellStyle);

                if (ii == getStatementOfFinancialConditionData().getAssetsLevel3().get(i).size() - 1) {
                    dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                    columnNo = 0;

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

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

                    cell = dataRow.createCell(columnNo++);
                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(
                            getStatementOfFinancialConditionData().getAssetsLevel2Total().get(i).doubleValue());
                    cell.setCellStyle(cellStyle);
                }
            }

            if (i == getStatementOfFinancialConditionData().getAssetsLevel2().size() - 1) {
                dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                columnNo = 0;

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

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

                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                //                    cell.setCellValue(getStatementOfFinancialConditionData().getAssetsLevel2Total().get(i).doubleValue());
                cell.setCellValue(getStatementOfFinancialConditionData().getAssetLevel1Total().doubleValue());
                cell.setCellStyle(cellStyle);
            }
        }

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

        dataRow = sheet.createRow(dataRow.getRowNum() + 1);
        columnNo = 0;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("LIABILITIES AND EQUITY");
        cell.setCellStyle(boldStyle);

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

        dataRow = sheet.createRow(dataRow.getRowNum() + 1);
        columnNo = 0;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("LIABILITIES");
        cell.setCellStyle(boldStyle);
        //            

        for (int i = 0; i < getStatementOfFinancialConditionData().getLiabilitiesLevel2().size(); i++) {
            dataRow = sheet.createRow(dataRow.getRowNum() + 1);
            columnNo = 0;

            cell = dataRow.createCell(columnNo++);
            cell.setCellValue(
                    getStatementOfFinancialConditionData().getLiabilitiesLevel2().get(i).getAcctTitle());
            cell.setCellStyle(boldStyle);
            //                
            dataRow.createCell(columnNo++).setCellValue("");
            if (getStatementOfFinancialConditionData().getLiabilitiesLevel3().get(i).isEmpty()) {
                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(getStatementOfFinancialConditionData().getLiabilitiesLevel2().get(i)
                        .getAmount().doubleValue());
                cell.setCellStyle(cellStyle);
            }
            //                             
            for (int ii = 0; ii < getStatementOfFinancialConditionData().getLiabilitiesLevel3().get(i)
                    .size(); ii++) {
                dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                columnNo = 0;

                dataRow.createCell(columnNo++).setCellValue("");
                dataRow.createCell(columnNo++).setCellValue(getStatementOfFinancialConditionData()
                        .getLiabilitiesLevel3().get(i).get(ii).getAcctTitle());

                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(getStatementOfFinancialConditionData().getLiabilitiesLevel3().get(i).get(ii)
                        .getAmount().doubleValue());
                cell.setCellStyle(cellStyle);

                if (ii == getStatementOfFinancialConditionData().getLiabilitiesLevel3().get(i).size() - 1) {
                    dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                    columnNo = 0;

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

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

                    cell = dataRow.createCell(columnNo++);
                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(getStatementOfFinancialConditionData().getLiabilitiesLevel2Total().get(i)
                            .doubleValue());
                    cell.setCellStyle(cellStyle);
                }
            }

            if (i == getStatementOfFinancialConditionData().getLiabilitiesLevel2().size() - 1) {
                dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                columnNo = 0;

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

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

                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                //                    cell.setCellValue(getStatementOfFinancialConditionData().getLiabilitiesLevel2Total().get(i).doubleValue());
                cell.setCellValue(
                        getStatementOfFinancialConditionData().getLiabilityLevel1Total().doubleValue());
                cell.setCellStyle(cellStyle);
            }
        }

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

        dataRow = sheet.createRow(dataRow.getRowNum() + 1);
        columnNo = 0;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("EQUITY");
        cell.setCellStyle(boldStyle);
        //                      

        for (int i = 0; i < getStatementOfFinancialConditionData().getEquitiesLevel2().size(); i++) {
            dataRow = sheet.createRow(dataRow.getRowNum() + 1);
            columnNo = 0;

            cell = dataRow.createCell(columnNo++);
            cell.setCellValue(getStatementOfFinancialConditionData().getEquitiesLevel2().get(i).getAcctTitle());
            cell.setCellStyle(boldStyle);
            //               
            dataRow.createCell(columnNo++).setCellValue("");
            if (getStatementOfFinancialConditionData().getEquitiesLevel3().get(i).isEmpty()) {
                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(getStatementOfFinancialConditionData().getEquitiesLevel2().get(i).getAmount()
                        .doubleValue());
                cell.setCellStyle(cellStyle);
            }
            //                                
            for (int ii = 0; ii < getStatementOfFinancialConditionData().getEquitiesLevel3().get(i)
                    .size(); ii++) {
                dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                columnNo = 0;

                dataRow.createCell(columnNo++).setCellValue("");
                dataRow.createCell(columnNo++).setCellValue(getStatementOfFinancialConditionData()
                        .getEquitiesLevel3().get(i).get(ii).getAcctTitle());

                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(getStatementOfFinancialConditionData().getEquitiesLevel3().get(i).get(ii)
                        .getAmount().doubleValue());
                cell.setCellStyle(cellStyle);

                if (ii == getStatementOfFinancialConditionData().getEquitiesLevel3().get(i).size() - 1) {
                    dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                    columnNo = 0;

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

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

                    cell = dataRow.createCell(columnNo++);
                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(getStatementOfFinancialConditionData().getEquitiesLevel2Total().get(i)
                            .doubleValue());
                    cell.setCellStyle(cellStyle);
                }
            }

            if (i == getStatementOfFinancialConditionData().getEquitiesLevel2().size() - 1) {
                dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                columnNo = 0;

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

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

                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                //                    cell.setCellValue(getStatementOfFinancialConditionData().getEquitiesLevel2Total().get(i).doubleValue());
                cell.setCellValue(getStatementOfFinancialConditionData().getEquityLevel1Total().doubleValue());
                cell.setCellStyle(cellStyle);

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

                cell = dataRow.createCell(columnNo++);
                cell.setCellValue("TOTAL LIABILITIES AND EQUITY");
                cell.setCellStyle(boldStyle);

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

                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(
                        getStatementOfFinancialConditionData().getLiabilityEquityLevel1Total().doubleValue());
                cell.setCellStyle(cellStyle);
            }
        }

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

        getExportData().fileUploadByDL(getExportData().getFilename(), "Statement of Financial Condition Report",
                themeDisplay, null);

        File file = new File(getExportData().getFilename());
        if (file.exists()) {
            file.delete();
        }
    } catch (Exception e) {
        System.out.print("statementOfFinancialConditionExport().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.StatementOfOperationsExport.java

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

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

    getExportData().createFolder(null, themeDisplay, "Statement of Operations 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);

        sheet = workbook.createSheet(
                "Statement of Operations Report " + getFinancialReportData().getCurrentMonth() != null
                        ? getDataConvert().monthConvert(getFinancialReportData().getCurrentMonth()) + " "
                                + getFinancialReportData().getCurrentYear().toString()
                        : getFinancialReportData().getCurrentYear().toString());

        dataRow = sheet.createRow((short) 0);
        columnNo = 0;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("STATEMENT OF OPERATIONS");
        cell.setCellStyle(boldStyle);

        dataRow = sheet.createRow(dataRow.getRowNum() + 1);
        columnNo = 0;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue(getFinancialReportData().getCurrentMonth() != null
                ? getDataConvert().monthConvert(getFinancialReportData().getCurrentMonth()) + " "
                        + getFinancialReportData().getCurrentYear().toString()
                : getFinancialReportData().getCurrentYear().toString());
        cell.setCellStyle(boldStyle);

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

        dataRow = sheet.createRow(dataRow.getRowNum() + 1);
        columnNo = 0;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("INCOME");
        cell.setCellStyle(boldStyle);
        //                      

        for (int i = 0; i < getStatementOfOperationsData().getIncomeLevel2().size(); i++) {
            dataRow = sheet.createRow(dataRow.getRowNum() + 1);
            columnNo = 0;

            cell = dataRow.createCell(columnNo++);
            cell.setCellValue(getStatementOfOperationsData().getIncomeLevel2().get(i).getAcctTitle());
            cell.setCellStyle(boldStyle);
            //                
            dataRow.createCell(columnNo++).setCellValue("");
            if (getStatementOfOperationsData().getIncomeLevel3().get(i).isEmpty()) {
                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(
                        getStatementOfOperationsData().getIncomeLevel2().get(i).getAmount().doubleValue());
                cell.setCellStyle(cellStyle);
            }
            //                
            for (int ii = 0; ii < getStatementOfOperationsData().getIncomeLevel3().get(i).size(); ii++) {
                dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                columnNo = 0;

                dataRow.createCell(columnNo++).setCellValue("");
                dataRow.createCell(columnNo++).setCellValue(
                        getStatementOfOperationsData().getIncomeLevel3().get(i).get(ii).getAcctTitle());

                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(getStatementOfOperationsData().getIncomeLevel3().get(i).get(ii).getAmount()
                        .doubleValue());
                cell.setCellStyle(cellStyle);

                if (ii == getStatementOfOperationsData().getIncomeLevel3().get(i).size() - 1) {
                    dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                    columnNo = 0;

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

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

                    cell = dataRow.createCell(columnNo++);
                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(
                            getStatementOfOperationsData().getIncomeLevel2Total().get(i).doubleValue());
                    cell.setCellStyle(cellStyle);
                }
            }

            if (i == getStatementOfOperationsData().getIncomeLevel2().size() - 1) {
                dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                columnNo = 0;

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

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

                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                //                    cell.setCellValue(getStatementOfOperationsData().getIncomeLevel2Total().get(i).doubleValue());
                cell.setCellValue(getStatementOfOperationsData().getIncomeLevel1Total().doubleValue());
                cell.setCellStyle(cellStyle);
            }
        }

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

        dataRow = sheet.createRow(dataRow.getRowNum() + 1);
        columnNo = 0;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("EXPENSES");
        cell.setCellStyle(boldStyle);
        //            

        for (int i = 0; i < getStatementOfOperationsData().getExpensesLevel2().size(); i++) {
            dataRow = sheet.createRow(dataRow.getRowNum() + 1);
            columnNo = 0;

            cell = dataRow.createCell(columnNo++);
            cell.setCellValue(getStatementOfOperationsData().getExpensesLevel2().get(i).getAcctTitle());
            cell.setCellStyle(boldStyle);
            //                
            dataRow.createCell(columnNo++).setCellValue("");
            if (getStatementOfOperationsData().getExpensesLevel3().get(i).isEmpty()) {
                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(
                        getStatementOfOperationsData().getExpensesLevel2().get(i).getAmount().doubleValue());
                cell.setCellStyle(cellStyle);
            }
            //                             
            for (int ii = 0; ii < getStatementOfOperationsData().getExpensesLevel3().get(i).size(); ii++) {
                dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                columnNo = 0;

                dataRow.createCell(columnNo++).setCellValue("");
                dataRow.createCell(columnNo++).setCellValue(
                        getStatementOfOperationsData().getExpensesLevel3().get(i).get(ii).getAcctTitle());

                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(getStatementOfOperationsData().getExpensesLevel3().get(i).get(ii).getAmount()
                        .doubleValue());
                cell.setCellStyle(cellStyle);

                if (ii == getStatementOfOperationsData().getExpensesLevel3().get(i).size() - 1) {
                    dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                    columnNo = 0;

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

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

                    cell = dataRow.createCell(columnNo++);
                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(
                            getStatementOfOperationsData().getExpensesLevel2Total().get(i).doubleValue());
                    cell.setCellStyle(cellStyle);
                }
            }

            if (i == getStatementOfOperationsData().getExpensesLevel2().size() - 1) {
                dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                columnNo = 0;

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

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

                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                //                    cell.setCellValue(getStatementOfOperationsData().getExpensesLevel2Total().get(i).doubleValue());
                cell.setCellValue(getStatementOfOperationsData().getExpenseLevel1Total().doubleValue());
                cell.setCellStyle(cellStyle);
            }
        }

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

        getExportData().fileUploadByDL(getExportData().getFilename(), "Statement of Operations Report",
                themeDisplay, null);

        File file = new File(getExportData().getFilename());
        if (file.exists()) {
            file.delete();
        }
    } catch (Exception e) {
        System.out.print("statementOfOperationsExport().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.TrialBalanceExport.java

public void export() {
    Integer columnNo;//from   w w  w .  j  a v a  2  s.co  m
    HSSFWorkbook workbook;
    HSSFSheet sheet;
    HSSFRow dataRow;
    HSSFCell cell;
    HSSFCellStyle cellStyle, boldStyle;
    HSSFFont font;

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

    getExportData().createFolder(null, themeDisplay, "Trial Balance 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);

        sheet = workbook
                .createSheet("Trial Balance Report " + getFinancialReportData().getCurrentMonth() != null
                        ? getDataConvert().monthConvert(getFinancialReportData().getCurrentMonth()) + " "
                                + getFinancialReportData().getCurrentYear().toString()
                        : getFinancialReportData().getCurrentYear().toString());

        dataRow = sheet.createRow((short) 0);
        columnNo = 0;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("TRIAL BALANCE");
        cell.setCellStyle(boldStyle);

        dataRow = sheet.createRow(dataRow.getRowNum() + 1);
        columnNo = 0;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue(getFinancialReportData().getCurrentMonth() != null
                ? getDataConvert().monthConvert(getFinancialReportData().getCurrentMonth()) + " "
                        + getFinancialReportData().getCurrentYear().toString()
                : getFinancialReportData().getCurrentYear().toString());
        cell.setCellStyle(boldStyle);

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

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

        columnNo = 1;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("Debit");
        cell.setCellStyle(boldStyle);

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

        if (getTrialBalanceData().getType() == 1) {
            for (int i = 0; i < getTrialBalanceData().getCombotb().size(); i++) {
                for (int ii = 0; ii < getTrialBalanceData().getCombotb().get(i).size(); ii++) {
                    dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                    columnNo = 0;

                    cell = dataRow.createCell(columnNo++);
                    cell.setCellValue(getDataConvert()
                            .accountCodeConvert((String) getTrialBalanceData().getCombotb().get(i).get(ii)[0]));
                    cell.setCellStyle(cellStyle);
                    //                
                    dataRow.createCell(columnNo++).setCellValue("");

                    columnNo = 1;

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

                    columnNo = 2;

                    cell = dataRow.createCell(columnNo++);
                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(
                            ((BigDecimal) getTrialBalanceData().getCombotb().get(i).get(ii)[2]).doubleValue());
                    cell.setCellStyle(cellStyle);
                }

                if (i == getTrialBalanceData().getCombotb().size() - 1) {
                    dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                    dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                    columnNo = 0;

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

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

                    columnNo = 1;

                    cell = dataRow.createCell(columnNo++);
                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(((getTrialBalanceData().getComboDebitTotal()).doubleValue()));
                    cell.setCellStyle(boldStyle);
                    System.out.println("debit total "
                            + getDataConvert().convertAmount(getTrialBalanceData().getComboDebitTotal()));

                    columnNo = 2;

                    cell = dataRow.createCell(columnNo++);
                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(((getTrialBalanceData().getComboCreditTotal()).doubleValue()));
                    cell.setCellStyle(boldStyle);
                    System.out.println("credit total "
                            + getDataConvert().convertAmount(getTrialBalanceData().getComboCreditTotal()));
                }
            }

        } else if (getTrialBalanceData().getType() == 2) {
            for (int i = 0; i < getTrialBalanceData().getTrialBalance().size(); i++) {
                dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                columnNo = 0;

                cell = dataRow.createCell(columnNo++);
                cell.setCellValue((String) getTrialBalanceData().getTrialBalance().get(i).getAcctTitle());
                cell.setCellStyle(cellStyle);
                //                
                dataRow.createCell(columnNo++).setCellValue("");

                columnNo = 1;

                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(getDataConvert()
                        .convertAmount((BigDecimal) getTrialBalanceData().getTrialBalance().get(i).getDebit()));
                cell.setCellStyle(cellStyle);

                columnNo = 2;

                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(getDataConvert().convertAmount(
                        (BigDecimal) getTrialBalanceData().getTrialBalance().get(i).getCredit()));
                cell.setCellStyle(cellStyle);

                if (i == getTrialBalanceData().getTrialBalance().size() - 1) {
                    dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                    dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                    columnNo = 0;

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

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

                    columnNo = 1;

                    cell = dataRow.createCell(columnNo++);
                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(
                            getDataConvert().convertAmount(getTrialBalanceData().getTrialBalanceDebitTotal()));
                    cell.setCellStyle(boldStyle);

                    columnNo = 2;

                    cell = dataRow.createCell(columnNo++);
                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(
                            getDataConvert().convertAmount(getTrialBalanceData().getTrialBalanceCreditTotal()));
                    cell.setCellStyle(boldStyle);
                }
            }
        }

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

        getExportData().fileUploadByDL(getExportData().getFilename(), "Trial Balance Report", themeDisplay,
                null);

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

From source file:br.com.nfsconsultoria.azcontrole.bean.VendaBean.java

public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);//w ww.j a v a2s  . com
    HSSFRow header = sheet.getRow(0);

    HSSFFont font = wb.createFont();
    font.setBold(true);
    font.setColor(HSSFColor.WHITE.index);

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    cellStyle.setWrapText(true);
    cellStyle.setAlignment(CellStyle.ALIGN_JUSTIFY);
    cellStyle.setFont(font);

    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);

        cell.setCellStyle(cellStyle);
    }
}

From source file:br.com.pontocontrol.controleponto.controller.impl.ExportadorXLSController.java

private void formatHeaderRow(HSSFWorkbook workbook, HSSFRow row) {
    HSSFFont headerFont = workbook.createFont();
    headerFont.setFontHeightInPoints((short) 10);
    headerFont.setFontName("Arial");
    headerFont.setColor(IndexedColors.WHITE.getIndex());
    headerFont.setBoldweight((short) 800);
    headerFont.setItalic(false);/*from  w  w  w .j a v  a 2  s  . co  m*/
    HSSFCellStyle headerStyle = workbook.createCellStyle();
    headerStyle.setFont(headerFont);
    headerStyle.setFillBackgroundColor(IndexedColors.BLACK.getIndex());
    headerStyle.setFillForegroundColor(IndexedColors.BLACK.getIndex());
    headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    row.cellIterator().forEachRemaining((cell) -> {
        cell.setCellStyle(headerStyle);
    });
}