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

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

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFRow
  6. getRowNum

Introduction

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

Prototype

@Override
public int getRowNum()

Source Link

Document

get row number this row represents

Usage

From source file:bean.DamayanListingNoticeController.java

public void export0() {
    Integer columnNo;/*from  www  .ja v 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 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   www .  ja 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, "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 ww. j  a va  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, "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;// ww  w  .j a va2 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 ww w.  j  a v  a2  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 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.  jav  a 2s .com*/
    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.eti.rogerioaguilar.minhasclasses.util.excel.leitor.LeitorExcel.java

License:Open Source License

/**
 * Realiza o processamento de leitura seguindo o fluxo:
 * /*  w  w w  .  j a v  a  2s. c om*/
 * 1 - O leitor abre a planilha                                                            <br>
 * 2 - o leitor comea a ler as linhas e colunas da planilha                                    <br>
 * 3 - Para cada linha encontrada:                                                         <br>
 *          3.1 - Caso a linha esteja na lista de linhas que devem ser processadas passada ao construtor:         <br>
 *                  3.1.1 - O leitor monta uma lista de vos contendos os dados das colunas para a linha atual que      <br>
 *                        estejam dentro do padro de leitura passado ao construtor                           <br>
 *                 3.1.2 - O leitor chama o mtodo LinhaListener.lendoLinha passando o mapa com os dados da linha      <br>
 *                       e aguarda o final do processamento.                                          <br>
 *                       3.1.2.1 - Caso o mtodo do listener retorne true, o leitor continua o processamento da     <br>
 *                               da planilha. Caso contrrio, o processamento da planilha  interrompido         <br>
 *                                 Se o processamento da planilha continuar (de acordo com o parmetro de retorno   <br>
 *                               do listener para a linha descrito anteriormente), o leitor chama o listener para <br>
 *                               a coluna para cada coluna da linha atual. O comportamento deste listener  o mesmo <br>
 *                               do listener para a linha, ou seja, se o listener retornar false o processamento da <br>
 *                               planilha  interrompido.   
 * 
 * @throws ParseException
 * @throws PlanilhaNaoEncontradaException caso o ndice da planilha no seja encontrado no arquivo
 * @throws FileNotFoundException caso o arquivo passado como parmetro no exista
 * @throws ListenerException caso ocorra algum erro na chamada de algum dos listeners
 * @throws IOException caso ocorra algum problema de io
 * */
public void processarLeituraPlanilha() throws ParseException, PlanilhaNaoEncontradaException,
        FileNotFoundException, IOException, ListenerException {

    try {
        log.debug("Inicializando o processamento da leitura do arquivo...");
        log.debug("Dados para o processamento --> " + this);
        POIFSFileSystem fs = null;
        if (this.streamArquivo != null) {
            fs = new POIFSFileSystem(streamArquivo);
        } else if (this.caminhoArquivoExcel != null) {
            fs = new POIFSFileSystem(new FileInputStream(this.caminhoArquivoExcel));
        } else {
            throw new IllegalArgumentException(
                    "No foi definido um stream para o arquivo nem um caminho para o arquivo!");
        }
        log.debug("Processando a string de entrada --> " + this.strPadraoLeitura);
        Map mapaLinhasAProcessar = LeitorExcelReader
                .getMapaEntradas(new ByteArrayInputStream(this.strPadraoLeitura.getBytes()));
        log.debug("A string de entrada --> " + this.strPadraoLeitura + " foi processada com sucesso.");
        log.debug("Mapa retornado --> " + mapaLinhasAProcessar);

        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet planilha = wb.getSheetAt(this.indicePlanilha - 1);
        if (planilha == null) {
            log.error("Planilha no encontrada no ndice -->" + this.indicePlanilha);
            throw new PlanilhaNaoEncontradaException(
                    "Planilha no encontrada no ndice -->" + this.indicePlanilha);
        } else {
            log.debug("A string de entrada --> " + this.strPadraoLeitura + " foi processada com sucesso.");
            boolean processarTodasAsLinhas = (mapaLinhasAProcessar.get("*") != null);
            boolean processarTodasAsColunas = false;
            boolean continuarProcessamentoLinha = true;
            Map propriedadesListenerLinha = new HashMap();
            Map propriedadesListenerColuna = new HashMap();
            List listaColunas = null;
            List listaVosColunas = new LinkedList();
            if (processarTodasAsLinhas) {
                log.debug("Processando todas as linhas...");
            }
            Iterator itLinhas = planilha.rowIterator();
            while (itLinhas.hasNext() && continuarProcessamentoLinha) {
                HSSFRow linha = (HSSFRow) itLinhas.next();
                propriedadesListenerLinha.clear();
                listaVosColunas.clear();
                propriedadesListenerLinha.put(LinhaListener.CHAVE_LINHA_OBJETO_ORIGINAL_POI, linha);
                int intLinhaAtual = linha.getRowNum() + 1;

                log.debug("Processando linha --> " + intLinhaAtual);
                if (!processarTodasAsLinhas) {
                    listaColunas = getListaColunasLinha("" + intLinhaAtual, mapaLinhasAProcessar);
                } else {
                    listaColunas = getListaColunasLinha("*", mapaLinhasAProcessar);
                }
                boolean processarLinhaAtual = processarTodasAsLinhas || (listaColunas != null);
                if (processarLinhaAtual) {
                    Iterator itColunas = linha.cellIterator();
                    processarTodasAsColunas = (listaColunas != null) && (listaColunas.size() > 0)
                            && ("" + listaColunas.get(0)).equals("*");
                    while (itColunas.hasNext()) {
                        HSSFCell celula = (HSSFCell) itColunas.next();
                        int intCelulaAtual = celula.getCellNum() + 1;
                        boolean processarColunaAtual = processarTodasAsColunas
                                || ((listaColunas != null) && (listaColunas.size() > 0)
                                        && listaColunas.indexOf(new Long(intCelulaAtual)) != -1);
                        LinhaColunaListenerVo linhaColunaListenerVo = new LinhaColunaListenerVo();
                        linhaColunaListenerVo.setLinha(intLinhaAtual);
                        linhaColunaListenerVo.setColuna(intCelulaAtual);
                        linhaColunaListenerVo.setCelulaAtual(celula);
                        if (processarColunaAtual) {
                            if (celula != null) {
                                log.debug("Coluna --> " + intCelulaAtual + " para a linha --> " + intLinhaAtual
                                        + " deve ser processada...");
                                switch (celula.getCellType()) {
                                case HSSFCell.CELL_TYPE_STRING:
                                    linhaColunaListenerVo.setValorStr(celula.getStringCellValue());
                                    break;
                                case HSSFCell.CELL_TYPE_NUMERIC:
                                    linhaColunaListenerVo
                                            .setValorNumerico(new Double(celula.getNumericCellValue()));
                                    break;
                                case HSSFCell.CELL_TYPE_FORMULA:
                                    linhaColunaListenerVo.setCelulaFormula(true);
                                    linhaColunaListenerVo.setValorStrFormula(celula.getCellFormula());
                                    break;
                                case HSSFCell.CELL_TYPE_ERROR:
                                    linhaColunaListenerVo.setCelulaContemErro(true);
                                    linhaColunaListenerVo.setErro(new Byte(celula.getErrorCellValue()));
                                    break;
                                case HSSFCell.CELL_TYPE_BOOLEAN:
                                    linhaColunaListenerVo
                                            .setValorBoolean(new Boolean(celula.getBooleanCellValue()));
                                    break;
                                case HSSFCell.CELL_TYPE_BLANK:
                                    linhaColunaListenerVo.setCelulaBranca(true);
                                    linhaColunaListenerVo.setValorStr("");
                                    break;
                                }
                            } else {
                                log.warn("Clula  nula!");
                                linhaColunaListenerVo.setCelulaNula(true);
                            }
                            listaVosColunas.add(linhaColunaListenerVo);
                        } else {
                            log.debug("Coluna --> " + intCelulaAtual + " para a linha --> " + intLinhaAtual
                                    + " no deve ser processada...");
                        }
                    }
                    if (this.linhaListener != null) {
                        log.debug("Chamando o listener para a linha --> " + intLinhaAtual);
                        Collections.sort(listaVosColunas, new Comparator() {
                            public int compare(Object arg0, Object arg1) {
                                int colunaUm = ((LinhaColunaListenerVo) arg0).getColuna();
                                int colunaDois = ((LinhaColunaListenerVo) arg1).getColuna();
                                if (colunaUm < colunaDois) {
                                    return -1;
                                } else if (colunaUm > colunaDois) {
                                    return 1;
                                }
                                return 0;
                            }
                        });
                        propriedadesListenerLinha.put(LinhaListener.LISTA_VOS_LINHA, listaVosColunas);
                        continuarProcessamentoLinha = this.linhaListener.lendoLinha(intLinhaAtual,
                                propriedadesListenerLinha);
                        if (!continuarProcessamentoLinha) {
                            log.debug(
                                    "Listener retornou boolean false indicando que o processamento deve ser interrompido!");
                        }
                    } else {
                        log.debug("Listener no configurado para a linha --> " + intLinhaAtual);
                    }
                    if (this.colunaListener != null) {
                        Iterator itColunasVoListener = listaVosColunas.iterator();
                        boolean continuarProcessamentoColunasnaLinha = true;
                        while (itColunasVoListener.hasNext() && continuarProcessamentoColunasnaLinha) {
                            propriedadesListenerColuna.clear();
                            LinhaColunaListenerVo voAtual = (LinhaColunaListenerVo) itColunasVoListener.next();
                            propriedadesListenerColuna.put(ColunaListener.CHAVE_VO_COLUNA, voAtual);
                            propriedadesListenerColuna.put(ColunaListener.CHAVE_COLUNA_OBJETO_ORIGINAL_POI,
                                    voAtual.getCelulaAtual());
                            log.debug("Chamando o listener para a coluna --> " + voAtual.getColuna()
                                    + " na linha " + voAtual.getLinha());
                            continuarProcessamentoColunasnaLinha = this.colunaListener.lendoColuna(
                                    voAtual.getLinha(), voAtual.getColuna(), propriedadesListenerColuna);
                            if (!continuarProcessamentoColunasnaLinha) {
                                log.debug(
                                        "Listener de coluna retornou boolean false indicando que o processamento das colunas na linha "
                                                + voAtual.getLinha() + " deve ser interrompido!");
                            }
                        }
                    } else {
                        log.debug("Listener no configurado para processamento das colunas");
                    }
                } else {
                    log.debug("Linha --> " + intLinhaAtual + " no ser processada!");
                }
            }
        }
        log.debug("Processamento da planilha realizado com sucesso!");
    } catch (ParseException e) {
        e.printStackTrace();
        log.error("Erro ao processar a string de entrada ", e);
        throw e;
    } catch (FileNotFoundException e) {
        e.printStackTrace();
        log.error("Arquivo " + this.caminhoArquivoExcel + " no encontrado", e);
        throw e;
    } catch (IOException e) {
        e.printStackTrace();
        log.error("Erro ao abrir o arquivo " + this.caminhoArquivoExcel, e);
        throw e;
    } catch (ListenerException e) {
        e.printStackTrace();
        log.error("Erro ao processar o listener ", e);
        throw e;
    }
}

From source file:cn.trymore.core.util.excel.PoiExcelParser.java

License:Open Source License

@SuppressWarnings("rawtypes")
public String xlsToHtml(int sheetIdx) throws Exception {
    if ((this.book == null) && (this.sheet == null)) {
        open(sheetIdx);//from  ww w .j av  a2  s  .  co m
    }
    StringBuilder sb = new StringBuilder();
    sb.append(new StringBuilder().append("<table cellspacing=\"0\" style=\"width:").append(this.htmlTbWidth)
            .append("px;table-layout:fixed\">").toString());

    Iterator itor = this.sheet.rowIterator();
    while (itor.hasNext()) {
        HSSFRow row = (HSSFRow) itor.next();
        sb.append("<tr>");
        int i = 0;
        for (int size = row.getLastCellNum() - row.getFirstCellNum(); i < size; ++i) {
            HSSFCell cell = (HSSFCell) getCell(row.getRowNum(), i);
            sb.append(new StringBuilder().append("<td ").append(getCellStyle(cell)).append(">").toString());
            sb.append(getCellContent(cell));
            sb.append("</td>");
        }
        sb.append("</tr>");
    }

    sb.append("</table>");
    return sb.toString();
}

From source file:com.allinfinance.bo.impl.risk.T40201BOTarget.java

License:Open Source License

public String importFile(List<File> fileList, List<String> fileNameList, Operator operator) throws Exception {
    HSSFWorkbook workbook = null;/*from  w  w  w  . j  ava 2  s.  c o  m*/
    HSSFSheet sheet = null;
    HSSFRow row = null;
    // ?
    String returnMsg = "";
    // ??
    int fileNameIndex = 0;
    // ??
    String fileName = null;

    // ??
    String saCardNo = null;
    // ??
    String saLimitAmt = null;
    // ?
    String saAction = null;
    // 
    String saBrhId = operator.getOprBrhId();
    // ?
    String saOprId = operator.getOprId();
    // 
    String saInitTime = CommonFunction.getCurrentDateTime();

    TblCtlCardInf tblCtlCardInf = null;

    FileInputStream fileInputStream = null;

    for (File file : fileList) {

        fileInputStream = new FileInputStream(file);

        workbook = new HSSFWorkbook(fileInputStream);

        sheet = workbook.getSheetAt(0);

        fileName = fileNameList.get(fileNameIndex);

        for (int rowIndex = sheet.getFirstRowNum(); rowIndex <= sheet.getLastRowNum(); rowIndex++) {

            row = sheet.getRow(rowIndex);

            for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++)
                if (row.getCell(i).getCellType() != HSSFCell.CELL_TYPE_STRING)
                    returnMsg += "[ " + fileName + " ]" + (row.getRowNum() + 1) + "" + ""
                            + (i + 1) + "???<br>";

            if (!"".equals(returnMsg))
                return returnMsg;

            saCardNo = row.getCell(0).getStringCellValue();
            // ??
            if (saCardNo.getBytes().length > 19)
                return "[ " + fileName + " ]" + (row.getRowNum() + 1)
                        + "???<br>";

            saLimitAmt = row.getCell(1).getStringCellValue();
            //            saLimitAmt = CommonFunction.transYuanToFen(saLimitAmt);

            // ??
            if (!CommonFunction.isAllDigit(saLimitAmt))
                return "[ " + fileName + " ]" + (row.getRowNum() + 1)
                        + "????<br>";

            if (saLimitAmt.getBytes().length > 12)
                return "[ " + fileName + " ]" + (row.getRowNum() + 1)
                        + "???<br>";

            saAction = row.getCell(2).getStringCellValue();
            // ?
            if (!("1".equals(saAction) || "2".equals(saAction) || "3".equals(saAction) || "4".equals(saAction)))
                return "[ " + fileName + " ]" + (row.getRowNum() + 1)
                        + "???<br>";

            // ???????
            tblCtlCardInf = new TblCtlCardInf();
            tblCtlCardInf.setId(saCardNo);
            tblCtlCardInf.setSaLimitAmt(saLimitAmt);
            tblCtlCardInf.setSaAction(saAction);
            tblCtlCardInf.setSaInitZoneNo(saBrhId);
            tblCtlCardInf.setSaInitOprId(saOprId);
            tblCtlCardInf.setSaInitTime(saInitTime);
            tblCtlCardInfDAO.saveOrUpdate(tblCtlCardInf);
        }
        fileInputStream.close();
        fileNameIndex++;
    }
    return Constants.SUCCESS_CODE;
}

From source file:com.allinfinance.bo.impl.risk.T40202BOTarget.java

License:Open Source License

@SuppressWarnings("unchecked")
public String importFile(List<File> fileList, List<String> fileNameList, Operator operator) throws Exception {
    HSSFWorkbook workbook = null;/*from  w w w. j  a v  a2s .c  om*/
    HSSFSheet sheet = null;
    HSSFRow row = null;
    // ?
    String returnMsg = "";
    // ??
    int fileNameIndex = 0;
    // ??
    String fileName = null;
    String sql = null;
    // 
    List<Object[]> dataList = null;

    // ??
    String saMerNo = null;
    // ??
    String saMerChName = null;
    // ??
    String saMerEnName = null;
    // ?
    String saZoneNo = null;
    // ??
    String saLimitAmt = null;
    // ?
    String saAction = null;
    // 
    String saBrhId = operator.getOprBrhId();
    // ?
    String saOprId = operator.getOprId();
    // 
    String saInitTime = CommonFunction.getCurrentDateTime();

    TblCtlMchtInf tblCtlMchtInf = null;

    FileInputStream fileInputStream = null;

    for (File file : fileList) {

        fileInputStream = new FileInputStream(file);

        workbook = new HSSFWorkbook(fileInputStream);

        sheet = workbook.getSheetAt(0);

        fileName = fileNameList.get(fileNameIndex);

        for (int rowIndex = sheet.getFirstRowNum(); rowIndex <= sheet.getLastRowNum(); rowIndex++) {

            row = sheet.getRow(rowIndex);

            for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++)
                if (row.getCell(i).getCellType() != HSSFCell.CELL_TYPE_STRING)
                    returnMsg += "[ " + fileName + " ]" + (row.getRowNum() + 1) + "" + ""
                            + (i + 1) + "???<br>";

            if (!"".equals(returnMsg))
                return returnMsg;

            saMerNo = row.getCell(0).getStringCellValue();
            // ?
            if (saMerNo.getBytes().length > 15)
                return "[ " + fileName + " ]" + (row.getRowNum() + 1)
                        + "???<br>";
            sql = "select  mcht_nm,eng_name,bank_no from TBL_MCHT_BASE_INF where MCHT_NO = '" + saMerNo + "'";

            dataList = CommonFunction.getCommQueryDAO().findBySQLQuery(sql);
            if (dataList.size() == 0)
                return "[ " + fileName + " ]" + (row.getRowNum() + 1)
                        + "??<br>";
            // ??
            saMerChName = dataList.get(0)[0].toString();
            // ??
            saMerEnName = dataList.get(0)[1].toString();
            saZoneNo = dataList.get(0)[2].toString();

            saLimitAmt = row.getCell(1).getStringCellValue();
            //            saLimitAmt = CommonFunction.transYuanToFen(saLimitAmt);

            // ??
            if (!CommonFunction.isAllDigit(saLimitAmt))
                return "[ " + fileName + " ]" + (row.getRowNum() + 1)
                        + "????<br>";

            if (saLimitAmt.getBytes().length > 12)
                return "[ " + fileName + " ]" + (row.getRowNum() + 1)
                        + "???<br>";

            saAction = row.getCell(2).getStringCellValue();
            // ?
            if (!("1".equals(saAction) || "2".equals(saAction) || "3".equals(saAction) || "4".equals(saAction)))
                return "[ " + fileName + " ]" + (row.getRowNum() + 1)
                        + "???<br>";

            // ??????
            tblCtlMchtInf = new TblCtlMchtInf();
            tblCtlMchtInf.setId(saMerNo);
            tblCtlMchtInf.setSaMerChName(saMerChName);
            tblCtlMchtInf.setSaMerEnName(saMerEnName);
            tblCtlMchtInf.setSaZoneNo(saZoneNo);
            tblCtlMchtInf.setSaLimitAmt(saLimitAmt);
            tblCtlMchtInf.setSaAction(saAction);
            tblCtlMchtInf.setSaInitZoneNo(saBrhId);
            tblCtlMchtInf.setSaInitOprId(saOprId);
            tblCtlMchtInf.setSaInitTime(saInitTime);
            tblCtlMchtInfDAO.saveOrUpdate(tblCtlMchtInf);
        }
        fileInputStream.close();
        fileNameIndex++;
    }
    return Constants.SUCCESS_CODE;
}