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

List of usage examples for org.apache.poi.hssf.usermodel HSSFRow createCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFRow
  6. 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.ExportData.java

public void applicantList(List<CoopApplicant> applicantData, ApplicantFilterData filterData) {
    String fName = getFilename();
    ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay();
    createFolder(null, themeDisplay, "Applicant export", "Description");

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

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

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

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

        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("Contact Number");
        }
        if (getExportColumns().get(5)) {
            rowhead.createCell(y++).setCellValue("Educational Attainment");
        }
        if (getExportColumns().get(6)) {
            rowhead.createCell(y++).setCellValue("Occupation");
        }
        if (getExportColumns().get(7)) {
            rowhead.createCell(y++).setCellValue("Application Date");
        }
        if (getExportColumns().get(8)) {
            rowhead.createCell(y++).setCellValue("Application Status");
        }
        if (getExportColumns().get(9)) {
            rowhead.createCell(y++).setCellValue("Applicant Type");
        }

        for (int i = 0; i != applicantData.size(); i++) {
            HSSFRow row = sheet.createRow((short) i + 1);
            row.createCell(x++).setCellValue(applicantData.get(i).getApplicantNo());
            row.createCell(x++).setCellValue(getDataConvert().fullname("", applicantData.get(i).getLastName(),
                    applicantData.get(i).getFirstName(), applicantData.get(i).getMiddleName(), ""));
            try {
                row.createCell(x++).setCellValue(applicantData.get(i).getOuCode().getOuShortName());
            } catch (Exception e) {
                row.createCell(x++).setCellValue("");
            }
            if (getExportColumns().get(0)) {
                row.createCell(x++).setCellValue(applicantData.get(i).getStreet() + " "
                        + applicantData.get(i).getBarangay() + " " + applicantData.get(i).getCityMun() + " "
                        + (applicantData.get(i).getProvince() != null ? applicantData.get(i).getProvince()
                                : ""));
            }
            if (getExportColumns().get(1)) {
                row.createCell(x++).setCellValue(getDataConvert().genderConv(applicantData.get(i).getGender()));
            }
            if (getExportColumns().get(2)) {
                row.createCell(x++).setCellValue(
                        getDataConvert().civilStatusConv(applicantData.get(i).getCivilStatus().toString()));
            }
            if (getExportColumns().get(3)) {
                HSSFCell cell = row.createCell(x++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(
                        getCustomDate().formatDate(applicantData.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(applicantData.get(i).getContactNumber());
            }
            if (getExportColumns().get(5)) {
                row.createCell(x++)
                        .setCellValue(getDataConvert().educConv(applicantData.get(i).getEducation()));
            }
            if (getExportColumns().get(6)) {
                row.createCell(x++).setCellValue(applicantData.get(i).getOccupation());
            }
            if (getExportColumns().get(7)) {
                HSSFCell cell = row.createCell(x++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(
                        getCustomDate().formatDate(applicantData.get(i).getApplicationDate(), "MM/dd/YYYY"));
                HSSFCellStyle dateCellStyle = workbook.createCellStyle();
                short df = workbook.createDataFormat().getFormat("MM/dd/YYYY");
                dateCellStyle.setDataFormat(df);
                cell.setCellStyle(dateCellStyle);
            }
            if (getExportColumns().get(8)) {
                row.createCell(x++)
                        .setCellValue(getDataConvert().appStatConv(applicantData.get(i).getApplicationStat()));
            }
            if (getExportColumns().get(9)) {
                row.createCell(x++).setCellValue(applicantData.get(i).getApplicantType());
            }
            x = 0;
        }

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

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

        //delete file
        File file = new File(getFilename());
        if (file.exists()) {
            file.delete();
        }
    } catch (Exception e) {
        System.out.println(e);
        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.GlReportExport.java

public void export() {
    Integer columnNo;/*  ww w .ja v  a  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 w  w  w  . j  av  a  2s .c  om*/
    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  ww .ja v a2s. 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;// w w w  .j a  va2 s. com
    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;/*  w w  w  . j  a v  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, "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:binky.reportrunner.engine.renderers.exporters.TabbedXLSExporter.java

License:Open Source License

@SuppressWarnings("deprecation")
@Override//from  w w  w.  j a  v  a 2s .  co  m
public void export(ResultSet resultSet, String label, OutputStream outputStream) throws ExportException {

    if (this.outputStream == null)
        this.outputStream = outputStream;

    try {
        if (wb == null) {
            logger.trace("creating new workbook");
            wb = new HSSFWorkbook();
        }

        logger.trace("creaing worksheet " + label);
        HSSFSheet sheet = wb.createSheet(label);
        ResultSetMetaData metaData;

        metaData = resultSet.getMetaData();

        short rowCount = 0;
        // logger.debug("writing header");
        HSSFRow headerRow = sheet.createRow(rowCount);
        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            // TODO:fix
            HSSFCell cell = headerRow.createCell((short) (i - 1));
            HSSFRichTextString string = new HSSFRichTextString(metaData.getColumnName(i));
            string.applyFont(HSSFFont.BOLDWEIGHT_BOLD);
            cell.setCellValue(string);
        }

        while (resultSet.next()) {
            rowCount++;
            HSSFRow row = sheet.createRow(rowCount);
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                // TODO:fix
                HSSFCell cell = row.createCell((short) (i - 1));

                // TODO:make this better by using types
                HSSFRichTextString string = new HSSFRichTextString("" + resultSet.getObject(i));
                cell.setCellValue(string);

            }
        }

    } catch (SQLException e) {
        throw new ExportException(e.getMessage(), e);
    }
}

From source file:binky.reportrunner.engine.renderers.exporters.XLSExporter.java

License:Open Source License

@SuppressWarnings("deprecation")
@Override//from   w  w  w  .jav a  2 s  .  c  om
public void export(ResultSet resultSet, String label, OutputStream outputStream) throws ExportException {
    try {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("Report");
        ResultSetMetaData metaData;

        metaData = resultSet.getMetaData();

        short rowCount = 0;
        // logger.debug("writing header");
        HSSFRow headerRow = sheet.createRow(rowCount);
        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            //TODO:fix
            HSSFCell cell = headerRow.createCell((short) (i - 1));
            HSSFRichTextString string = new HSSFRichTextString(metaData.getColumnName(i));
            string.applyFont(HSSFFont.BOLDWEIGHT_BOLD);
            cell.setCellValue(string);
        }

        while (resultSet.next()) {
            rowCount++;
            HSSFRow row = sheet.createRow(rowCount);
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                //TODO:fix
                HSSFCell cell = row.createCell((short) (i - 1));

                // TODO:make this better by using types
                HSSFRichTextString string = new HSSFRichTextString("" + resultSet.getObject(i));
                cell.setCellValue(string);

            }
        }

        // Write the output to the stream file
        wb.write(outputStream);
        outputStream.flush();

    } catch (SQLException e) {
        throw new ExportException(e.getMessage(), e);
    } catch (IOException e) {
        throw new ExportException(e.getMessage(), e);
    }
}

From source file:br.com.deltex.sqlexecutor.core.controller.SqlExecutorController.java

public void exportExcel() throws FileNotFoundException, IOException {
    // create a new workbook       
    HSSFWorkbook workBook = new HSSFWorkbook();
    // create a new sheet
    HSSFSheet sheet = workBook.createSheet();
    // declare a row object reference
    HSSFRow row = null;
    // declare a cell object reference
    HSSFCell cell = null;//  w  w  w  .j a  v a  2 s .c o m

    int rowIndex = 0;
    int cellIndex = 0;

    try {
        for (List<String> rowDataQuery : this.dataQuery) {
            row = sheet.createRow(rowIndex);

            for (String column : rowDataQuery) {
                cell = row.createCell(cellIndex);
                cell.setCellValue(column);

                cellIndex++;
            }
            cellIndex = 0;
            rowIndex++;
        }

        // create a new file
        FileOutputStream out = new FileOutputStream(
                new File("C:/Users/harlan.bruno.santos/Desktop/workbook.xls"));

        workBook.write(out);
        out.close();
    } catch (FileNotFoundException e) {
        logger.error(e.getMessage(), e);
        throw e;
    } catch (IOException e) {
        logger.error(e.getMessage(), e);
        throw e;
    }
}

From source file:br.com.hslife.orcamento.controller.LancamentoContaController.java

License:Open Source License

@SuppressWarnings("resource")
public void exportarLancamentos() {
    if (listEntity == null || listEntity.isEmpty()) {
        warnMessage("Listagem vazio. Nada a exportar.");
    }//from ww  w  .  java  2 s  .  c o  m

    try {

        HSSFWorkbook excel = new HSSFWorkbook();
        HSSFSheet planilha = excel.createSheet("lancamentoConta");

        HSSFRow linha = planilha.createRow(0);

        HSSFCell celula = linha.createCell(0);
        celula.setCellValue("Data");
        celula = linha.createCell(1);
        celula.setCellValue("Histrico");
        celula = linha.createCell(2);
        celula.setCellValue("Valor");

        int linhaIndex = 1;
        for (LancamentoConta l : listEntity) {
            linha = planilha.createRow(linhaIndex);

            celula = linha.createCell(0);
            celula.setCellValue(Util.formataDataHora(l.getDataPagamento(), Util.DATA));

            celula = linha.createCell(1);
            celula.setCellValue(l.getDescricao());

            celula = linha.createCell(2);
            celula.setCellValue(l.getValorPago());

            linhaIndex++;
        }

        HttpServletResponse response = (HttpServletResponse) FacesContext.getCurrentInstance()
                .getExternalContext().getResponse();
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=lancamentoConta.xls");
        response.setContentLength(excel.getBytes().length);
        ServletOutputStream output = response.getOutputStream();
        output.write(excel.getBytes(), 0, excel.getBytes().length);
        FacesContext.getCurrentInstance().responseComplete();

    } catch (IOException e) {
        errorMessage(e.getMessage());
    }
}