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

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

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

Introduction

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

Prototype

@Override
public HSSFSheet createSheet(String sheetname)

Source Link

Document

Create a new sheet for this Workbook and return the high level representation.

Usage

From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java

License:Open Source License

@Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED)
public void getAlertHardwareReport(Account pAccount, String remoteUser, String lsName, HSSFWorkbook phwb,
        OutputStream pOutputStream) throws HibernateException, Exception {

    AlertType alertType = (AlertType) getEntityManager().createNamedQuery("getAlertTypeByCode")
            .setParameter("code", "HARDWARE").getSingleResult();

    ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate()).createSQLQuery(
            "SELECT CASE WHEN VA.Alert_Age > 90 THEN 'Red' WHEN VA.Alert_Age > 45 THEN 'Yellow' ELSE 'Green' END, H.Serial, MT.Name, VA.Creation_Time, VA.Alert_Age, MT.Type, VA.Remote_User, VA.Comments, VA.Record_Time,  AC.name as ac_name, CC.target_date,CC.owner as cc_owner,CC.record_time as cc_record_time,CC.remote_user as cc_remote_user, CC.id as cc_id FROM EAADMIN.V_Alerts VA, EAADMIN.Hardware H, EAADMIN.Machine_Type MT, EAADMIN.cause_code CC, EAADMIN.alert_cause AC WHERE VA.Customer_Id = :customerId AND VA.Type = 'HARDWARE' AND VA.Open = 1 AND H.Id = VA.FK_Id AND MT.Id = H.Machine_Type_Id and VA.id=CC.alert_id and CC.alert_type_id = :alertTypeId and CC.alert_cause_id=AC.id ORDER BY H.Serial ASC")
            .setLong("customerId", pAccount.getId()).setInteger("alertTypeId", alertType.getId().intValue())
            .scroll(ScrollMode.FORWARD_ONLY);
    HSSFSheet sheet = phwb.createSheet("Alert Hardware Report");
    printHeader(ALERT_HARDWARE_REPORT_NAME, pAccount.getAccount(), ALERT_HARDWARE_REPORT_COLUMN_HEADERS, sheet);
    int i = 3;//from  w ww . j a  va  2 s. com
    while (lsrReport.next()) {
        int k = 1;
        if (i > 65535) {
            k++;
            sheet = phwb.createSheet("Alert HW Report Sheet" + k);
            i = 1;
        }
        HSSFRow row = sheet.createRow((int) i);
        outputData(lsrReport.get(), row);
        i++;
    }

    @SuppressWarnings("unchecked")
    Iterator<Object[]> vCauseCodeSummary = getEntityManager()
            .createNamedQuery("getValidCauseCodesByAlertTypeId").setParameter("alertTypeId", alertType.getId())
            .getResultList().iterator();
    HSSFSheet sheet_2 = phwb.createSheet("Valid Cause Codes");
    HSSFRow rowhead0 = sheet_2.createRow((int) 0);
    outputData(ALERT_VALID_CAUSE_CODE_HEADERS, rowhead0);
    int j = 1;
    while (vCauseCodeSummary.hasNext()) {
        HSSFRow row = sheet_2.createRow((int) j);
        outputData(vCauseCodeSummary.next(), row);
        j++;
    }
    phwb.write(pOutputStream);
}

From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java

License:Open Source License

@Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED)
public void getAlertSoftwareLparReport(Account pAccount, String remoteUser, String lsName, HSSFWorkbook phwb,
        OutputStream pOutputStream) throws HibernateException, Exception {

    AlertType alertType = (AlertType) getEntityManager().createNamedQuery("getAlertTypeByCode")
            .setParameter("code", "SW_LPAR").getSingleResult();

    ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate()).createSQLQuery(SQL_QUERY_SW_LPAR)
            .setLong("customerId", pAccount.getId()).setString("type", "SOFTWARE_LPAR")
            .setInteger("alertTypeId", alertType.getId().intValue()).scroll(ScrollMode.FORWARD_ONLY);

    HSSFSheet sheet = phwb.createSheet("Alert SwLpar Report");
    printHeader(ALERT_SOFTWARE_LPAR_REPORT_NAME, pAccount.getAccount(), ALERT_SW_LPAR_REPORT_COLUMN_HEADERS,
            sheet);/*from  ww  w  .  j  a va  2 s  . c o  m*/
    int i = 3;
    while (lsrReport.next()) {
        int k = 1;
        if (i > 65535) {
            k++;
            sheet = phwb.createSheet("Alert SWLpar Report Sheet" + k);
            i = 1;
        }
        HSSFRow row = sheet.createRow((int) i);
        outputData(lsrReport.get(), row);
        i++;
    }

    @SuppressWarnings("unchecked")
    Iterator<Object[]> vCauseCodeSummary = getEntityManager()
            .createNamedQuery("getValidCauseCodesByAlertTypeId").setParameter("alertTypeId", alertType.getId())
            .getResultList().iterator();
    HSSFSheet sheet_2 = phwb.createSheet("Valid Cause Codes");
    HSSFRow rowhead0 = sheet_2.createRow((int) 0);
    outputData(ALERT_VALID_CAUSE_CODE_HEADERS, rowhead0);
    int j = 1;
    while (vCauseCodeSummary.hasNext()) {
        HSSFRow row = sheet_2.createRow((int) j);
        outputData(vCauseCodeSummary.next(), row);
        j++;
    }
    phwb.write(pOutputStream);
}

From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java

License:Open Source License

@Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED)
public void getAlertUnlicensedIbmSwReport(Account pAccount, String remoteUser, String lsName, HSSFWorkbook phwb,
        OutputStream pOutputStream) throws HibernateException, Exception {
    ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate())
            .createSQLQuery(SQL_QUERY_UNLICENSED_SW).setLong("customerId", pAccount.getId())
            .setString("type", "UNLICENSED_IBM_SW").scroll(ScrollMode.FORWARD_ONLY);
    HSSFSheet sheet = phwb.createSheet("Alert UNLICENSED_IBM_SW Report");
    printHeader(ALERT_UNLICENSED_IBM_SW_REPORT_NAME, pAccount.getAccount(),
            ALERT_UNLICENSED_SW_REPORT_COLUMN_HEADERS, sheet);
    int i = 3;//from w w  w . jav a  2s.co  m
    while (lsrReport.next()) {
        int k = 1;
        if (i > 65535) {
            k++;
            sheet = phwb.createSheet("Alert UIBM SW Report Sheet" + k);
            i = 1;
        }
        HSSFRow row = sheet.createRow((int) i);
        outputData(lsrReport.get(), row);
        i++;
    }

    // lsrReport.close();
    @SuppressWarnings("unchecked")
    Iterator<Object[]> vCauseCodeSummary = getEntityManager()
            .createNamedQuery("getValidCauseCodesByAlertTypeId").setParameter("alertTypeId", new Long(17))
            .getResultList().iterator();
    HSSFSheet sheet_2 = phwb.createSheet("Valid Cause Codes");
    HSSFRow rowhead0 = sheet_2.createRow((int) 0);
    outputData(ALERT_VALID_CAUSE_CODE_HEADERS, rowhead0);
    int j = 1;
    while (vCauseCodeSummary.hasNext()) {
        HSSFRow row = sheet_2.createRow((int) j);
        outputData(vCauseCodeSummary.next(), row);
        j++;
    }
    phwb.write(pOutputStream);
}

From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java

License:Open Source License

@Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED)
public void getAlertUnlicensedIsvSwReport(Account pAccount, String remoteUser, String lsName, HSSFWorkbook phwb,
        OutputStream pOutputStream) throws HibernateException, Exception {
    ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate())
            .createSQLQuery(SQL_QUERY_UNLICENSED_SW).setLong("customerId", pAccount.getId())
            .setString("type", "UNLICENSED_ISV_SW").scroll(ScrollMode.FORWARD_ONLY);
    HSSFSheet sheet = phwb.createSheet("Alert UNLICENSED_ISV_SW Report");
    printHeader(ALERT_UNLICENSED_ISV_SW_REPORT_NAME, pAccount.getAccount(),
            ALERT_UNLICENSED_SW_REPORT_COLUMN_HEADERS, sheet);
    int i = 3;//from   www  .j a  v  a 2  s.c  o  m
    while (lsrReport.next()) {
        int k = 1;
        if (i > 65535) {
            k++;
            sheet = phwb.createSheet("Alert UISV SW Report Sheet" + k);
            i = 1;
        }
        HSSFRow row = sheet.createRow((int) i);
        outputData(lsrReport.get(), row);
        i++;
    }

    // lsrReport.close();
    @SuppressWarnings("unchecked")
    Iterator<Object[]> vCauseCodeSummary = getEntityManager()
            .createNamedQuery("getValidCauseCodesByAlertTypeId").setParameter("alertTypeId", new Long(17))
            .getResultList().iterator();
    HSSFSheet sheet_2 = phwb.createSheet("Valid Cause Codes");
    HSSFRow rowhead0 = sheet_2.createRow((int) 0);
    outputData(ALERT_VALID_CAUSE_CODE_HEADERS, rowhead0);
    int j = 1;
    while (vCauseCodeSummary.hasNext()) {
        HSSFRow row = sheet_2.createRow((int) j);
        outputData(vCauseCodeSummary.next(), row);
        j++;
    }
    phwb.write(pOutputStream);
}

From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java

License:Open Source License

@Override
@Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED)
public void getAlertHardwareCfgDataReport(Account pAccount, String remoteUser, String lsName, HSSFWorkbook phwb,
        OutputStream pOutputStream) throws HibernateException, Exception {

    AlertType alertType = (AlertType) getEntityManager().createNamedQuery("getAlertTypeByCode")
            .setParameter("code", "HWCFGDTA").getSingleResult();

    StringBuffer sb = new StringBuffer(
            "SELECT CASE WHEN VA.Alert_Age > 90 THEN 'Red' WHEN VA.Alert_Age > 45 THEN 'Yellow' ELSE 'Green' END, ");
    sb.append(//from w  ww .  j a  v  a2  s .c  o m
            "H.Serial, MT.Name, H.processor_manufacturer, H.MAST_PROCESSOR_TYPE, H.PROCESSOR_MODEL, H.NBR_CORES_PER_CHIP, H.CHIPS, ")
            .append("H.PROCESSOR_COUNT, H.NBR_OF_CHIPS_MAX, H.CPU_GARTNER_MIPS, H.CPU_MIPS, H.CPU_MSU, ")
            .append("VA.Creation_Time, VA.Alert_Age, MT.Type, VA.Remote_User, VA.Comments, VA.Record_Time,AC.name as ac_name, ")
            .append("CC.target_date,CC.owner as cc_owner,CC.record_time as cc_record_time,CC.remote_user as cc_remote_user, CC.id as cc_id ")
            .append("FROM EAADMIN.V_Alerts VA, EAADMIN.Hardware H, EAADMIN.Machine_Type MT, EAADMIN.cause_code CC, EAADMIN.alert_cause AC ")
            .append("WHERE VA.Customer_Id = :customerId AND VA.Type = 'HWCFGDTA' AND VA.Open = 1 AND H.Id = VA.FK_Id AND MT.Id = H.Machine_Type_Id ")
            .append("and VA.id=CC.alert_id and CC.alert_type_id= :alertTypeId and CC.alert_cause_id=AC.id ORDER BY H.Serial ASC ");

    ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate()).createSQLQuery(sb.toString())
            .setLong("customerId", pAccount.getId()).setInteger("alertTypeId", alertType.getId().intValue())
            .scroll(ScrollMode.FORWARD_ONLY);

    HSSFSheet sheet = phwb.createSheet("Alert Hardware Config " + pAccount.getAccount() + " Report");
    printHeader(ALERT_HARDWARE_CFGDATA_REPORT_NAME, pAccount.getAccount(),
            ALERT_HARDWARE_CFGDATA_REPORT_COLUMN_HEADERS, sheet);
    int i = 3;
    while (lsrReport.next()) {
        int k = 1;
        if (i > 65535) {
            k++;
            sheet = phwb.createSheet("Alert Hardware Config " + pAccount.getAccount() + " Report" + k);
            i = 1;
        }
        HSSFRow row = sheet.createRow((int) i);
        outputData(lsrReport.get(), row);
        i++;
    }

    @SuppressWarnings("unchecked")
    Iterator<Object[]> vCauseCodeSummary = getEntityManager()
            .createNamedQuery("getValidCauseCodesByAlertTypeId").setParameter("alertTypeId", alertType.getId())
            .getResultList().iterator();
    HSSFSheet sheet_2 = phwb.createSheet("Valid Cause Codes");
    HSSFRow rowhead0 = sheet_2.createRow((int) 0);
    outputData(ALERT_VALID_CAUSE_CODE_HEADERS, rowhead0);
    int j = 1;
    while (vCauseCodeSummary.hasNext()) {
        HSSFRow row = sheet_2.createRow((int) j);
        outputData(vCauseCodeSummary.next(), row);
        j++;
    }
    phwb.write(pOutputStream);

}

From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java

License:Open Source License

@Override
@Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED)
public void getAlertUnlicensed(Account pAccount, String remoteUser, String lsName, HSSFWorkbook phwb,
        OutputStream pOutputStream, String type, String code, String reportName, String sheetName)
        throws HibernateException, Exception {

    AlertType alertType = (AlertType) getEntityManager().createNamedQuery("getAlertTypeByCode")
            .setParameter("code", code).getSingleResult();

    StringBuffer sb = new StringBuffer(unlicensedAlertQuery);

    ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate()).createSQLQuery(sb.toString())
            .setLong("customerId", pAccount.getId()).setParameter("type", type).setParameter("code", code)
            .scroll(ScrollMode.FORWARD_ONLY);

    HSSFSheet sheet = phwb.createSheet(sheetName + " " + pAccount.getAccount() + " Report");
    printHeader(reportName, pAccount.getAccount(), UNLICENSED_GROUP_COLUMN_HEADERS, sheet);
    int i = 3;/*from   w w w.  ja  v a 2 s . c o m*/
    while (lsrReport.next()) {
        int k = 1;
        if (i > 65535) {
            k++;
            sheet = phwb.createSheet("Alert Contract Scope " + pAccount.getAccount() + " Report" + k);
            i = 1;
        }
        HSSFRow row = sheet.createRow((int) i);
        outputData(lsrReport.get(), row);
        i++;
    }

    @SuppressWarnings("unchecked")
    Iterator<Object[]> vCauseCodeSummary = getEntityManager()
            .createNamedQuery("getValidCauseCodesByAlertTypeId").setParameter("alertTypeId", alertType.getId())
            .getResultList().iterator();

    HSSFSheet sheet_2 = phwb.createSheet("Valid Cause Codes");
    HSSFRow rowhead0 = sheet_2.createRow((int) 0);
    outputData(ALERT_VALID_CAUSE_CODE_HEADERS, rowhead0);
    int j = 1;
    while (vCauseCodeSummary.hasNext()) {
        HSSFRow row = sheet_2.createRow((int) j);
        outputData(vCauseCodeSummary.next(), row);
        j++;
    }
    phwb.write(pOutputStream);

}

From source file:com.ibm.ioes.dao.NewOrderDaoExt.java

/**
 * Method to get all data for Masters Download
 * @param productID/*from   www  .java 2  s .  com*/
 * @return
 */
public HSSFWorkbook downloadMasters(long productID) {

    String methodName = "downloadMasters", className = this.getClass().getName(), msg = "";
    boolean logToFile = true, logToConsole = true;
    AppConstants.IOES_LOGGER.info(methodName + " method of " + className + " class have been called");

    Connection connection = null;
    ArrayList<String> allowedSections = new ArrayList<String>();
    HSSFWorkbook wb = new HSSFWorkbook();
    CallableStatement getAllDropDownAttributes = null;
    CallableStatement getDetailForEachdropDown = null;
    ResultSet rsForAllDropDowns = null;
    ResultSet rsForEachDropDown = null;
    CallableStatement getAllMasters = null;
    ResultSet rsAllMasters = null;
    try {
        connection = DbConnection.getConnectionObject();
        connection.setAutoCommit(false);
        allowedSections = getAllowedSections(productID);

        //for Service Summary
        if (allowedSections.contains(new String("SERVICE_SUMMARY"))) {
            getAllDropDownAttributes = connection.prepareCall(sp_getAllDropDownAttributes);
            getAllDropDownAttributes.setLong(1, productID);
            rsForAllDropDowns = getAllDropDownAttributes.executeQuery();
            while (rsForAllDropDowns.next()) {

                //makeSheetForServiceSummary(wb,rsForAllDropDowns.getInt("ATTMASTERID"));
                HSSFSheet sheet = wb.createSheet(rsForAllDropDowns.getString("ATTDESCRIPTION"));
                HSSFCellStyle headerCellStyle = wb.createCellStyle();
                HSSFFont boldFont = wb.createFont();
                boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                headerCellStyle.setFont(boldFont);
                HSSFRow excelRow = null;
                HSSFCell excelCell = null;
                excelRow = sheet.createRow(0);
                excelCell = excelRow.createCell(0);
                excelCell.setCellStyle(headerCellStyle);
                excelCell.setCellValue(new HSSFRichTextString(rsForAllDropDowns.getString("ATTDESCRIPTION")));

                excelRow = sheet.createRow(1);

                excelCell = excelRow.createCell(0);
                excelCell.setCellStyle(headerCellStyle);
                excelCell.setCellValue(new HSSFRichTextString("ID"));

                excelCell = excelRow.createCell(1);
                excelCell.setCellStyle(headerCellStyle);
                excelCell.setCellValue(new HSSFRichTextString("VALUE"));

                getDetailForEachdropDown = connection.prepareCall(sp_getDataForEachDropDown);
                getDetailForEachdropDown.setLong(1, rsForAllDropDowns.getInt("ATTMASTERID"));
                rsForEachDropDown = getDetailForEachdropDown.executeQuery();
                int rowNo = 2;
                while (rsForEachDropDown.next()) {
                    //create a sheet
                    excelRow = sheet.createRow(rowNo);
                    for (int cell = 0, col = 1; cell < 2; cell++, col++) {
                        excelCell = excelRow.createCell(cell);
                        excelCell.setCellValue(new HSSFRichTextString(rsForEachDropDown.getString(col)));
                    }
                    rowNo = rowNo + 1;

                }
            }
        }

        //for all other Sections

        getAllMasters = connection.prepareCall(sqlSp_getAllMasters);
        rsAllMasters = getAllMasters.executeQuery();
        while (rsAllMasters.next()) {
            String sectionName = rsAllMasters.getString("SECTION_NAME");
            if (allowedSections.contains(sectionName)) {

                String columns = rsAllMasters.getString("COLUMN_NAMES");
                String[] columnNames = columns.split(",");

                HSSFSheet sheet = wb.createSheet(rsAllMasters.getString("MASTER_NAME"));
                HSSFCellStyle headerCellStyle = wb.createCellStyle();
                HSSFFont boldFont = wb.createFont();
                boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                headerCellStyle.setFont(boldFont);
                HSSFRow excelRow = null;
                HSSFCell excelCell = null;
                excelRow = sheet.createRow(0);
                for (int count = 0; count < columnNames.length; count++) {
                    excelCell = excelRow.createCell(count);
                    excelCell.setCellStyle(headerCellStyle);
                    excelCell.setCellValue(new HSSFRichTextString(columnNames[count]));
                }
                PreparedStatement getMasterData = null;
                ResultSet rsMasterData = null;
                getMasterData = connection.prepareStatement(rsAllMasters.getString("QUERY"));
                rsMasterData = getMasterData.executeQuery();
                int rowNo = 1;
                while (rsMasterData.next()) {
                    excelRow = sheet.createRow(rowNo);
                    for (int cell = 0, col = 1; cell < columnNames.length; cell++, col++) {
                        excelCell = excelRow.createCell(cell);
                        excelCell.setCellValue(new HSSFRichTextString(rsMasterData.getString(col)));
                    }
                    rowNo = rowNo + 1;
                }
            }

        }

    } catch (Exception e) {
        try {
            connection.rollback();
        } catch (SQLException e1) {
            Utility.onEx_LOG_RET_NEW_EX(e1, methodName, className, msg, logToFile, logToConsole);
        }
    } finally {
        try {
            DbConnection.closeResultset(rsAllMasters);
            DbConnection.closeResultset(rsForAllDropDowns);
            DbConnection.closeCallableStatement(getAllDropDownAttributes);
            DbConnection.closeCallableStatement(getAllMasters);
            DbConnection.closeCallableStatement(getDetailForEachdropDown);
            DbConnection.closeCallableStatement(getAllMasters);
            DbConnection.freeConnection(connection);
        } catch (Exception e) {
        }
    }

    return wb;
}

From source file:com.ibm.ioes.utilities.MailForDisConnectProvisioning.java

public HSSFWorkbook generateExcel_ACS(ArrayList<ACSMailTemplateDto> mailDataList) {
    HSSFWorkbook wb = null;
    try {//  w  w  w . j  av  a2  s .co  m
        ACSMailTemplateDto mailObjDto = null;
        wb = new HSSFWorkbook();
        HSSFSheet mailProvisionSheet = wb.createSheet("Mail Disconnect");
        HSSFCellStyle headerCellStyle = wb.createCellStyle();
        HSSFFont boldFont = wb.createFont();
        boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerCellStyle.setFont(boldFont);

        HSSFRow excelRow = null;
        HSSFCell excelCell = null;

        excelRow = mailProvisionSheet.createRow(0);
        excelRow = mailProvisionSheet.createRow(0);

        int i_cell = 0;

        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Date of Creation"));
        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Quote No./ISS Order No."));

        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Region"));

        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Parent A/C ID(Billed)"));

        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Child ID (Unbilled)"));

        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Premium ACS ID"));

        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Type of ID"));
        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Customer Name"));
        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Address"));
        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("City State"));
        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Pin"));
        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Moderator Name"));
        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Designation"));
        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Email"));
        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Phone No."));
        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Contact Person"));
        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Designation"));
        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Email"));

        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Phone No."));

        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Account Manager Name"));

        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Designation"));

        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Email ID/ Phone No"));

        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Web Conf (Y/N)"));

        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Web Conf Rental"));

        int iCount = 1;
        for (int i = 0; i < mailDataList.size(); i++) {
            mailObjDto = mailDataList.get(i);
            excelRow = mailProvisionSheet.createRow(iCount++);

            i_cell = 0;

            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(
                    new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getDateOfCreation())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getOrderNo())));

            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getRegion())));

            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getParentId())));

            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChildId())));

            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAcsId())));
            /*excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue( new HSSFRichTextString(""+Utility.fnCheckNull(mailObjDto.getServiceType()));*/
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getTypeOfId())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(
                    new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getCustomerName())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAddress())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getCity())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getPinNo())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(
                    new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonName())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(
                    new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonDesignation())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(
                    new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonEmailId())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(
                    new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonPhoneNo())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpName())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(
                    new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpDesignation())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpEmail())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpPhone())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell
                    .setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccMgrName())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(
                    new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccMgrDesignation())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccMgrphone())
                    + "/" + Utility.fnCheckNull(mailObjDto.getAccMgrEmailId())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getWebConf())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(
                    new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getWebConfRental())));
        }
    } catch (Exception ex) {
        Utility.LOG(true, true, "Error while fetching accounts for provisiong : " + new Date());
    }
    return wb;
}

From source file:com.ibm.ioes.utilities.MailForDisConnectProvisioning.java

public HSSFWorkbook generateExcel_VCS(ArrayList<ACSMailTemplateDto> mailDataList, String mailProvisioningFlag) {
    HSSFWorkbook wb = null;
    try {//from ww w .ja v  a2 s  . c  o m
        ACSMailTemplateDto mailObjDto = null;
        wb = new HSSFWorkbook();
        HSSFSheet mailProvisionSheet = wb.createSheet("Mail Disconnect");
        HSSFCellStyle headerCellStyle = wb.createCellStyle();
        HSSFFont boldFont = wb.createFont();
        boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerCellStyle.setFont(boldFont);

        HSSFRow excelRow = null;
        HSSFCell excelCell = null;

        excelRow = mailProvisionSheet.createRow(0);
        excelRow = mailProvisionSheet.createRow(0);

        excelCell = excelRow.createCell(0);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Date of Creation"));
        excelCell = excelRow.createCell(1);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Account name"));
        excelCell = excelRow.createCell(2);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("ISS Order no"));
        excelCell = excelRow.createCell(3);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Product"));
        excelCell = excelRow.createCell(4);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Parent ID"));
        excelCell = excelRow.createCell(5);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Child ID"));
        excelCell = excelRow.createCell(6);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("VCS ID"));
        excelCell = excelRow.createCell(7);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Bundled VCS plan"));
        excelCell = excelRow.createCell(8);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Chairperson Name"));
        excelCell = excelRow.createCell(9);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Billing Person Name"));
        excelCell = excelRow.createCell(10);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Billing Address"));
        excelCell = excelRow.createCell(11);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("ID/HW address"));
        excelCell = excelRow.createCell(12);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Status"));

        int iCount = 1;
        for (int i = 0; i < mailDataList.size(); i++) {
            mailObjDto = mailDataList.get(i);
            excelRow = mailProvisionSheet.createRow(iCount++);
            excelCell = excelRow.createCell(0);
            excelCell.setCellValue(
                    new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getDateOfCreation())));
            excelCell = excelRow.createCell(1);
            excelCell.setCellValue(
                    new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccountName())));
            excelCell = excelRow.createCell(2);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getOrderNo())));
            excelCell = excelRow.createCell(3);
            excelCell.setCellValue(
                    new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getProductName())));
            excelCell = excelRow.createCell(4);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getParentId())));
            excelCell = excelRow.createCell(5);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChildId())));
            excelCell = excelRow.createCell(6);
            excelCell
                    .setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getMultipleId())));
            excelCell = excelRow.createCell(7);
            excelCell.setCellValue(
                    new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getPackageName())));
            excelCell = excelRow.createCell(8);
            excelCell.setCellValue(
                    new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonName())));
            excelCell = excelRow.createCell(9);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpName())));
            excelCell = excelRow.createCell(10);
            excelCell.setCellValue(
                    new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBillingAddress())));
            excelCell = excelRow.createCell(11);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getHwAddress())));
            excelCell = excelRow.createCell(12);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getStatus())));

        }
    } catch (Exception ex) {
        Utility.LOG(true, true, "Error while fetching accounts for provisiong : " + new Date());
    }
    return wb;
}

From source file:com.ibm.ioes.utilities.MailForProvisioning.java

public HSSFWorkbook generateExcel(ArrayList<ACSMailTemplateDto> mailDataList) {
    HSSFWorkbook wb = null;
    try {/*w  ww  .  j  a v  a  2  s  .c  o  m*/
        ACSMailTemplateDto mailObjDto = null;
        wb = new HSSFWorkbook();
        HSSFSheet mailProvisionSheet = wb.createSheet("Mail Provision");
        HSSFCellStyle headerCellStyle = wb.createCellStyle();
        HSSFFont boldFont = wb.createFont();
        boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerCellStyle.setFont(boldFont);

        HSSFRow excelRow = null;
        HSSFCell excelCell = null;

        excelRow = mailProvisionSheet.createRow(0);
        excelRow = mailProvisionSheet.createRow(0);

        int i_cell = 0;

        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Date of Creation"));
        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Quote No./ISS Order No."));

        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Region"));

        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Parent A/C ID(Billed)"));

        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Child ID (Unbilled)"));

        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Premium ACS ID"));

        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Type of ID"));
        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Customer Name"));
        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Address"));
        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("City State"));
        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Pin"));
        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Moderator Name"));
        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Designation"));
        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Email"));
        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Phone No."));
        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Contact Person"));
        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Designation"));
        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Email"));

        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Phone No."));

        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Account Manager Name"));

        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Designation"));

        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Email ID/ Phone No"));

        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Web Conf (Y/N)"));

        excelCell = excelRow.createCell(i_cell++);
        excelCell.setCellStyle(headerCellStyle);
        excelCell.setCellValue(new HSSFRichTextString("Web Conf Rental"));

        int iCount = 1;
        for (int i = 0; i < mailDataList.size(); i++) {
            mailObjDto = mailDataList.get(i);
            excelRow = mailProvisionSheet.createRow(iCount++);

            i_cell = 0;

            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(
                    new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getDateOfCreation())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getOrderNo())));

            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getRegion())));

            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getParentId())));

            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChildId())));

            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAcsId())));
            /*excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue( new HSSFRichTextString(""+Utility.fnCheckNull(mailObjDto.getServiceType()));*/
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getTypeOfId())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(
                    new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getCustomerName())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAddress())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getCity())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getPinNo())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(
                    new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonName())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(
                    new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonDesignation())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(
                    new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonEmailId())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(
                    new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonPhoneNo())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpName())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(
                    new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpDesignation())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpEmail())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpPhone())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell
                    .setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccMgrName())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(
                    new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccMgrDesignation())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccMgrphone())
                    + "/" + Utility.fnCheckNull(mailObjDto.getAccMgrEmailId())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getWebConf())));
            excelCell = excelRow.createCell(i_cell++);
            excelCell.setCellValue(
                    new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getWebConfRental())));
        }
    } catch (Exception ex) {
        Utility.LOG(true, true, "Error while fetching accounts for provisiong : " + new Date());
    }
    return wb;
}