Example usage for org.apache.poi.hssf.usermodel HSSFSheet createRow

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet createRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. createRow

Introduction

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

Prototype

@Override
public HSSFRow createRow(int rownum)

Source Link

Document

Create a new row within the sheet 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;/* w w  w  . j  a v a2 s.c om*/
    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 w w  w.ja  v  a2s.  com
    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 ww  .j  a v  a2  s. com
    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  w w w  . jav  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   ww w.  jav  a 2s . 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  . j a va  2s  . com*/
    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.asset.trails.service.impl.ReportServiceImpl.java

License:Open Source License

private void printHeader(String psReportName, Long plAccountNumber, String[] psaColumnHeader, HSSFSheet sheet) {
    HSSFRow rowhead0 = sheet.createRow((int) 0);
    SimpleDateFormat lsdfNow = new SimpleDateFormat("MM-dd-yyyy HH:mm:ss");
    Object[] loaReportHeader = null;

    if (plAccountNumber != null) {
        loaReportHeader = new Object[] { psReportName, plAccountNumber,
                lsdfNow.format(Calendar.getInstance().getTime()) };
    } else {// ww w  .  j  a v  a2s  . c o  m
        loaReportHeader = new Object[] { psReportName, lsdfNow.format(Calendar.getInstance().getTime()) };
    }

    rowhead0.createCell((int) 0).setCellValue("IBM Confidential");
    HSSFRow rowhead1 = sheet.createRow((int) 1);
    outputData(loaReportHeader, rowhead1);
    HSSFRow rowhead2 = sheet.createRow((int) 2);
    outputData(psaColumnHeader, rowhead2);
}

From source file:com.ibm.ioes.bulkupload.utilities.ErrorLogServiceImpl.java

public String getFilledTemplate(String filePath, int templateId, int flag, String logicalLSI)
        throws IOESException, ParseException {
    //logger.info(" Entered into getErrorExcel method of " + this.getClass().getSimpleName());

    String fileName;//from  w  w  w. ja v  a 2 s  . c  o m
    HSSFWorkbook wb;
    HSSFSheet ws;
    HSSFRow wr;
    HSSFCell wc;
    ArrayList filledTemplateData = new ArrayList();
    ArrayList<ViewOrderDto> filledTemplateDataLineDetails = new ArrayList<ViewOrderDto>();
    ArrayList<ViewOrderDto> filledTemplateDataChargeDetails = new ArrayList<ViewOrderDto>();
    ErrorFileDaoImpl objDao = new ErrorFileDaoImpl();
    ViewOrderModel objviewmodel = new ViewOrderModel();
    //ErrorLogDto dtoObj ;
    TransactionTemplateDto dtoObj;
    ViewOrderDto dtoObj1;
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    int totalRowsOfSheet = 0;
    ViewOrderDto objdto = null;
    String str = null;
    BillingTriggerValidation validateDto = null;
    try {
        ResourceBundle bundle = ResourceBundle.getBundle("ApplicationResources");

        fileName = filePath.substring((filePath.lastIndexOf("/") + 1), filePath.length());
        String newFile = bundle.getString("excel.filledTemplate") + "/" + fileName;
        FileOutputStream fileOut = new FileOutputStream(newFile);
        wb = new HSSFWorkbook(new FileInputStream(filePath));
        HSSFCellStyle whiteFG_yellow = wb.createCellStyle();

        HSSFColor yellow = setColor(wb, (byte) 0xFF, (byte) 0xFF, (byte) 0x00);
        //whiteFG_yellow.setFillBackgroundColor(HSSFColor.YELLOW.index);
        whiteFG_yellow.setFillBackgroundColor(yellow.getIndex());

        HSSFCellStyle whiteFG_green = wb.createCellStyle();
        HSSFColor green = setColor(wb, (byte) 0x00, (byte) 0xFF, (byte) 0x00);
        //whiteFG_green.setFillBackgroundColor(HSSFColor.GREEN.index);
        whiteFG_green.setFillBackgroundColor(green.getIndex());

        //System.out.println(filledTemplateData.toString());
        //System.out.println(wb.getNumberOfSheets());

        for (int s = 0; s < wb.getNumberOfSheets(); s++) {
            ws = wb.getSheetAt(s);
            if (s == 0 && (templateId == 1 || templateId == 22 || templateId == 21 || templateId == 41)) {
                int ctr = 0, totalRowsOfSheetMain = 0, totalRowsOfSheetAtt = 0;
                totalRowsOfSheetMain = objDao.getTotalRowsOfSheet(s + 1, templateId, flag, logicalLSI);
                filledTemplateData = objDao.getFilledTemplate(s + 1, templateId, flag, logicalLSI);
                for (int r = 2; r <= (totalRowsOfSheetMain + 1); r++) {
                    dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr);
                    wr = ws.createRow(r);
                    if (wr != null) {
                        wr.createCell(1).setCellValue(dtoObj.getOrderNo());
                        wr.createCell(2).setCellValue(new HSSFRichTextString((dtoObj.getAccountID())));
                        wr.createCell(3).setCellValue(new HSSFRichTextString((dtoObj.getSource())));
                        wr.createCell(4).setCellValue(new HSSFRichTextString((dtoObj.getCurrencyID())));
                        wr.createCell(5).setCellValue(new HSSFRichTextString((dtoObj.getOpportunityId())));
                        wr.createCell(6).setCellValue(new HSSFRichTextString((dtoObj.getQuoteNo())));
                        wr.createCell(7).setCellValue(new HSSFRichTextString((dtoObj.getProjectMangerID())));
                        wr.createCell(8).setCellValue(new HSSFRichTextString((dtoObj.getZoneId())));
                    }
                    ctr++;
                }
                totalRowsOfSheetAtt = objDao.getTotalRowsOfSheet(11, templateId, flag, logicalLSI);
                filledTemplateData = objDao.getFilledTemplate(11, templateId, flag, logicalLSI);
                ctr = 0;
                for (int r = 2; r <= (totalRowsOfSheetMain + 1); r++) {
                    wr = ws.getRow(r);
                    dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr);
                    if (wr != null) {
                        wr.createCell(9).setCellValue(new HSSFRichTextString((dtoObj.getRFSDate())));
                        wr.createCell(10).setCellValue(new HSSFRichTextString((dtoObj.getActMngrPhoneNo())));
                        wr.createCell(11).setCellValue(new HSSFRichTextString((dtoObj.getActMngrEmailID())));
                        wr.createCell(12).setCellValue(new HSSFRichTextString((dtoObj.getIRUOrderYN())));
                        wr.createCell(13).setCellValue(new HSSFRichTextString((dtoObj.getFreePeriodYN())));
                        wr.createCell(14).setCellValue(new HSSFRichTextString((dtoObj.getOrdExclusiveTax())));
                        wr.createCell(15).setCellValue(new HSSFRichTextString((dtoObj.getCAFDate())));
                    }
                    ctr++;
                }
            }
            //GAM Sheet
            if (s == 1 && (templateId == 1 || templateId == 22 || templateId == 21 || templateId == 41)) {
                //to do nothing
            }
            //Contact Sheet
            if (s == 2 && (templateId == 1 || templateId == 22 || templateId == 21 || templateId == 41)) {
                int ctr = 0, rownum = 1;
                totalRowsOfSheet = objDao.getTotalRowsOfSheet(s + 1, templateId, flag, logicalLSI);
                filledTemplateData = objDao.getFilledTemplate(s + 1, templateId, flag, logicalLSI);

                for (int r = 2; r <= (totalRowsOfSheet + 1); r++) {
                    dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr);
                    wr = ws.createRow(r);
                    if (wr != null) {
                        wr.createCell(1).setCellValue(rownum);
                        wr.createCell(2).setCellValue(dtoObj.getOrderNo());
                        wr.createCell(3).setCellValue(dtoObj.getContactType());
                        wr.createCell(4).setCellValue(new HSSFRichTextString((dtoObj.getSalutation())));
                        wr.createCell(5).setCellValue(new HSSFRichTextString((dtoObj.getFirstName())));
                        wr.createCell(6).setCellValue(new HSSFRichTextString((dtoObj.getLastName())));
                        wr.createCell(7).setCellValue(new HSSFRichTextString((dtoObj.getEmail())));
                        wr.createCell(8).setCellValue(new HSSFRichTextString((dtoObj.getCellno())));
                        wr.createCell(9).setCellValue(new HSSFRichTextString((dtoObj.getFaxno())));
                        wr.createCell(10).setCellValue(rownum);
                        wr.createCell(11).setCellValue(new HSSFRichTextString((dtoObj.getAddress1())));
                        wr.createCell(12).setCellValue(new HSSFRichTextString((dtoObj.getAddress2())));
                        wr.createCell(13).setCellValue(new HSSFRichTextString((dtoObj.getAddress3())));
                        wr.createCell(14).setCellValue(new HSSFRichTextString((dtoObj.getCountrycode())));
                        wr.createCell(15).setCellValue(new HSSFRichTextString((dtoObj.getStateid())));
                        wr.createCell(16).setCellValue(new HSSFRichTextString((dtoObj.getCityid())));
                        wr.createCell(17).setCellValue(new HSSFRichTextString((dtoObj.getPincode())));
                    }
                    ctr++;
                    rownum++;
                }
            }
            if (s == 3 && (templateId == 1 || templateId == 22 || templateId == 21)) {
                int ctr = 0, rownum = 1;
                if (templateId == 21) {
                    totalRowsOfSheet = objDao.getTotalRowsOfSheet(s + 1, templateId, flag, logicalLSI);
                    filledTemplateData = objDao.getFilledTemplate(s + 1, templateId, flag, logicalLSI);
                    for (int r = 2; r <= (totalRowsOfSheet + 1); r++) {
                        dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr);
                        wr = ws.createRow(r);
                        if (wr != null) {
                            wr.createCell(1).setCellValue(rownum);
                            wr.createCell(2).setCellValue(dtoObj.getOrderNo());
                            wr.createCell(3).setCellValue(dtoObj.getLogicalsiNo());
                            wr.createCell(4).setCellValue(dtoObj.getServiceid());
                            wr.createCell(5).setCellValue(new HSSFRichTextString((dtoObj.getServiceName())));
                            wr.createCell(6).setCellValue(dtoObj.getLineItemID());
                            wr.createCell(7).setCellValue(new HSSFRichTextString((dtoObj.getLineItemName())));

                        }
                        ctr++;
                        rownum++;
                    }
                } else {
                    totalRowsOfSheet = objDao.getTotalRowsOfSheet(s + 1, templateId, flag, logicalLSI);
                    filledTemplateData = objDao.getFilledTemplate(s + 1, templateId, flag, logicalLSI);
                    for (int r = 2; r <= (totalRowsOfSheet + 1); r++) {
                        dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr);
                        wr = ws.createRow(r);
                        if (wr != null) {
                            wr.createCell(1).setCellValue(rownum);
                            wr.createCell(2).setCellValue(dtoObj.getOrderNo());
                            wr.createCell(3).setCellValue(dtoObj.getLogicalsiNo());
                            wr.createCell(4).setCellValue(dtoObj.getServiceid());
                            wr.createCell(5).setCellValue(dtoObj.getLineItemID());
                        }
                        ctr++;
                        rownum++;
                    }
                }
            }
            if (s == 3 && templateId == 41) {
                /*int  ctr=0,rownum=1;
                totalRowsOfSheet=objDao.getTotalRowsOfSheet(s+1,templateId,flag,logicalLSI);   
                filledTemplateData = objDao.getFilledTemplate(s+1,templateId,flag,logicalLSI);   
                for(int r = 2; r <= (totalRowsOfSheet+1); r++)
                {
                   dtoObj = (TransactionTemplateDto)filledTemplateData.get(ctr);               
                   wr=ws.createRow(r);
                   if(wr!=null)
                   {         
                      wr.createCell(1).setCellValue(rownum);
                      wr.createCell(2).setCellValue(dtoObj.getOrderNo());
                      wr.createCell(3).setCellValue(new HSSFRichTextString((dtoObj.getCustPONumber())));
                      wr.createCell(4).setCellValue(new HSSFRichTextString((dtoObj.getCustPODate())));                     
                      wr.createCell(5).setCellValue(new HSSFRichTextString((dtoObj.getIsDefaultPO())));
                      wr.createCell(6).setCellValue(new HSSFRichTextString((dtoObj.getLegalEntity())));
                      wr.createCell(7).setCellValue(new HSSFRichTextString((dtoObj.getTotalPOAmount())));
                      wr.createCell(8).setCellValue(new HSSFRichTextString((dtoObj.getPeriodInMonths())));
                      wr.createCell(9).setCellValue(new HSSFRichTextString((dtoObj.getContractStartDate())));
                      wr.createCell(10).setCellValue(new HSSFRichTextString((dtoObj.getContractEndDate())));
                      wr.createCell(11).setCellValue(new HSSFRichTextString((dtoObj.getPoRemarks())));
                      wr.createCell(12).setCellValue(new HSSFRichTextString((dtoObj.getPoEmailId())));
                   }
                   ctr++;rownum++;
                }*/
            }
            if (s == 4 && templateId == 41) {
                int ctr = 0, rownum = 1;
                totalRowsOfSheet = objDao.getTotalRowsOfSheet(s + 1, templateId, flag, logicalLSI);
                filledTemplateData = objDao.getFilledTemplate(s + 1, templateId, flag, logicalLSI);
                for (int r = 2; r <= (totalRowsOfSheet + 1); r++) {
                    dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr);
                    wr = ws.createRow(r);
                    if (wr != null) {
                        wr.createCell(1).setCellValue(rownum);
                        wr.createCell(2).setCellValue(dtoObj.getOrderNo());
                        wr.createCell(3).setCellValue(dtoObj.getLogicalsiNo());
                        wr.createCell(4).setCellValue(dtoObj.getServiceid());
                        wr.createCell(5).setCellValue(dtoObj.getLineItemID());
                        wr.createCell(6).setCellValue(dtoObj.getChargeID());
                        wr.createCell(7).setCellValue(dtoObj.getChargeAmount());
                        wr.createCell(8).setCellValue(new HSSFRichTextString((dtoObj.getChargeFrequency())));
                        wr.createCell(9).setCellValue(new HSSFRichTextString((dtoObj.getStrChargeType())));
                        wr.createCell(10).setCellValue(new HSSFRichTextString((dtoObj.getStrChargeName())));
                        wr.createCell(11).setCellValue(dtoObj.getFrequncyAmount());
                        wr.createCell(12).setCellValue(new HSSFRichTextString((dtoObj.getAnnotation())));
                    }
                    ctr++;
                    rownum++;
                }
            }
            if (s == 5 && templateId == 41) {
                int ctr = 0, rownum = 1;
                totalRowsOfSheet = objDao.getTotalRowsOfSheet(s + 1, templateId, flag, logicalLSI);
                filledTemplateData = objDao.getFilledTemplate(s + 1, templateId, flag, logicalLSI);
                for (int r = 2; r <= (totalRowsOfSheet + 1); r++) {
                    dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr);
                    wr = ws.createRow(r);
                    if (wr != null) {
                        wr.createCell(1).setCellValue(rownum);
                        wr.createCell(2).setCellValue(new HSSFRichTextString(""));
                        wr.createCell(3).setCellValue(dtoObj.getLineItemID());
                        wr.createCell(4).setCellValue(dtoObj.getCreditPeriodID());
                        wr.createCell(5).setCellValue(new HSSFRichTextString((dtoObj.getCreditPeriodName())));
                        wr.createCell(6).setCellValue(dtoObj.getLegealEntityID());
                        wr.createCell(7).setCellValue(new HSSFRichTextString((dtoObj.getLegalEntityName())));
                        wr.createCell(8).setCellValue(dtoObj.getLicenseCompanyID());
                        wr.createCell(9).setCellValue(new HSSFRichTextString((dtoObj.getLicenseCompanyName())));
                        wr.createCell(10).setCellValue(dtoObj.getBillingModeID());
                        wr.createCell(11).setCellValue(new HSSFRichTextString((dtoObj.getBillingModeName())));
                        wr.createCell(12).setCellValue(dtoObj.getBillingFormatID());
                        wr.createCell(13).setCellValue(new HSSFRichTextString((dtoObj.getBillingFormatName())));
                        wr.createCell(14).setCellValue(dtoObj.getBillingTypeID());
                        wr.createCell(15).setCellValue(new HSSFRichTextString((dtoObj.getBillingTypeName())));
                        wr.createCell(16).setCellValue(dtoObj.getTaxationID());
                        wr.createCell(17).setCellValue(new HSSFRichTextString(dtoObj.getTaxationName()));
                        wr.createCell(18).setCellValue(dtoObj.getBillingLevelID());
                        wr.createCell(19).setCellValue(new HSSFRichTextString(dtoObj.getBillingLevelName()));
                        wr.createCell(20).setCellValue(dtoObj.getNoticePeriod());
                        wr.createCell(21).setCellValue(new HSSFRichTextString(dtoObj.getPenaltyClause()));
                        wr.createCell(22).setCellValue(dtoObj.getCommitPeriod());
                        wr.createCell(23).setCellValue(dtoObj.getIsNfa());
                        wr.createCell(24).setCellValue(dtoObj.getBcpID());
                        wr.createCell(25).setCellValue(new HSSFRichTextString(dtoObj.getBcpName()));
                        wr.createCell(26).setCellValue(new HSSFRichTextString(dtoObj.getStandardReasonId()));
                        wr.createCell(27).setCellValue(new HSSFRichTextString(dtoObj.getStandardReasonName()));

                    }
                    ctr++;
                    rownum++;
                }
            }

            // billing trigger bulkupload  sheet 1 start

            if (s == 0 && templateId == 61) {
                int ctr = 0, rownum = 1;

                filledTemplateDataLineDetails = objviewmodel.getFilledTemplateforBillingLineSectionBulkUpload();
                for (int r = 2; r <= (filledTemplateDataLineDetails.size() + 1); r++) {
                    dtoObj1 = (ViewOrderDto) filledTemplateDataLineDetails.get(ctr);
                    wr = ws.createRow(r);
                    if (wr != null) {
                        HSSFCell cell0 = wr.createCell(0);
                        cell0.setCellValue(rownum);

                        HSSFCell cell1 = wr.createCell(1);
                        cell1.setCellValue(new HSSFRichTextString(dtoObj1.getLineNumber()));
                        HSSFCellStyle sty1 = ws.getRow(1).getCell(1).getCellStyle();

                        wr.createCell(2).setCellValue(new HSSFRichTextString(dtoObj1.getLineName()));
                        wr.createCell(3).setCellValue(new HSSFRichTextString(dtoObj1.getLogicalSino()));
                        wr.createCell(4).setCellValue(new HSSFRichTextString(dtoObj1.getCustLogicalSino()));
                        wr.createCell(5).setCellValue(dtoObj1.getOrderno());
                        wr.createCell(6).setCellValue(new HSSFRichTextString(dtoObj1.getOrdertype()));
                        wr.createCell(7).setCellValue(new HSSFRichTextString(dtoObj1.getOrder_subtype()));
                        wr.createCell(8).setCellValue(new HSSFRichTextString(dtoObj1.getSiid()));
                        wr.createCell(9).setCellValue(new HSSFRichTextString(dtoObj1.getAccountid()));
                        wr.createCell(10).setCellValue(new HSSFRichTextString(dtoObj1.getFx_status()));
                        wr.createCell(11).setCellValue(new HSSFRichTextString(dtoObj1.getLine_status()));
                        wr.createCell(12).setCellValue(new HSSFRichTextString(dtoObj1.getChallen_No()));
                        wr.createCell(13).setCellValue(new HSSFRichTextString(dtoObj1.getChallen_date()));
                        validateDto = dtoObj1.getBillingTriggerAllowDenyLogic();

                        HSSFCell cell14 = wr.createCell(14);
                        cell14.setCellValue(new HSSFRichTextString(dtoObj1.getLocNo()));
                        HSSFCellStyle sty = ws.getRow(1).getCell(14).getCellStyle();
                        if ("allow".equals(validateDto.getLocNoForEdit())) {
                            cell14.setCellStyle(sty);

                        } else {
                            cell14.setCellStyle(sty1);
                        }

                        HSSFCell cell15 = wr.createCell(15);
                        if (!(dtoObj1.getLocDate() == null || "".equals(dtoObj1.getLocDate()))) {
                            cell15.setCellValue(new HSSFRichTextString(
                                    Utility.showDate_Report4(df.parse(dtoObj1.getLocDate()))));
                        } else {
                            cell15.setCellValue(new HSSFRichTextString(dtoObj1.getLocDate()));
                        }
                        if ("allow".equals(validateDto.getLocDateForEdit())) {
                            cell15.setCellStyle(sty);
                        } else {
                            cell15.setCellStyle(sty1);
                        }

                        HSSFCell cell16 = wr.createCell(16);
                        if (!(dtoObj1.getLocRecDate() == null || "".equals(dtoObj1.getLocRecDate()))) {
                            cell16.setCellValue(new HSSFRichTextString(
                                    Utility.showDate_Report4(df.parse(dtoObj1.getLocRecDate()))));
                        } else {
                            cell16.setCellValue(new HSSFRichTextString(dtoObj1.getLocRecDate()));
                        }

                        if ("allow".equals(validateDto.getLocRecDateForEdit())) {
                            cell16.setCellStyle(sty);
                        } else {
                            cell16.setCellStyle(sty1);
                        }

                        HSSFCell cell17 = wr.createCell(17);
                        if (!(dtoObj1.getBillingTriggerDate() == null
                                || "".equals(dtoObj1.getBillingTriggerDate()))) {
                            cell17.setCellValue(new HSSFRichTextString(
                                    Utility.showDate_Report4(df.parse(dtoObj1.getBillingTriggerDate()))));
                        } else {
                            cell17.setCellValue(new HSSFRichTextString(dtoObj1.getBillingTriggerDate()));
                        }

                        if ("allow".equals(validateDto.getBtdForEdit())) {
                            cell17.setCellStyle(sty);
                        } else {
                            cell17.setCellStyle(sty1);
                        }

                        wr.createCell(18)
                                .setCellValue(new HSSFRichTextString(dtoObj1.getBillingTriggerProcess()));

                    }
                    ctr++;
                    rownum++;
                }
            }

            // billing trigger bulkupload  sheet 1 end

            //billing trigger bulkupload  sheet 2 start
            if (s == 1 && templateId == 61) {
                int ctr = 0, rownum = 1;
                if (filledTemplateDataLineDetails.size() > 0) {

                    filledTemplateDataChargeDetails = objviewmodel
                            .getFilledTemplateforBillingChargeSectionBulkUpload();
                    for (int r = 2; r <= (filledTemplateDataChargeDetails.size() + 1); r++) {
                        dtoObj1 = (ViewOrderDto) filledTemplateDataChargeDetails.get(ctr);
                        wr = ws.createRow(r);
                        if (wr != null) {
                            wr.createCell(0).setCellValue(rownum);
                            HSSFCell cell1 = wr.createCell(1);
                            cell1.setCellValue(dtoObj1.getChargeInfoId());
                            HSSFCellStyle sty1 = ws.getRow(1).getCell(1).getCellStyle();
                            wr.createCell(2).setCellValue(new HSSFRichTextString(dtoObj1.getLineNumber()));
                            wr.createCell(3).setCellValue(new HSSFRichTextString(dtoObj1.getChargeType()));
                            wr.createCell(4).setCellValue(new HSSFRichTextString(dtoObj1.getChargeName()));
                            wr.createCell(5).setCellValue(dtoObj1.getChargePeriod());
                            wr.createCell(6).setCellValue(dtoObj1.getChargeAmt());
                            wr.createCell(7).setCellValue(new HSSFRichTextString(dtoObj1.getChargeStatus()));
                            HSSFCell cell8 = wr.createCell(8);
                            HSSFCellStyle sty8 = ws.getRow(1).getCell(8).getCellStyle();
                            if (!(dtoObj1.getDisconnectiondate() == null
                                    || "".equals(dtoObj1.getDisconnectiondate()))) {
                                cell8.setCellValue(new HSSFRichTextString(
                                        Utility.showDate_Report4(df.parse(dtoObj1.getDisconnectiondate()))));
                            } else {
                                cell8.setCellValue(new HSSFRichTextString(dtoObj1.getDisconnectiondate()));
                            }
                            if ("Changed".equalsIgnoreCase(dtoObj1.getChargeStatus())) {
                                cell8.setCellStyle(sty8);

                            }

                            wr.createCell(9)
                                    .setCellValue(new HSSFRichTextString(dtoObj1.getChargeCreatedOnOrder()));
                            wr.createCell(10)
                                    .setCellValue(new HSSFRichTextString(dtoObj1.getChargeEndedOnOrder()));
                            wr.createCell(11).setCellValue(new HSSFRichTextString(dtoObj1.getBillPeriod()));
                            wr.createCell(12).setCellValue(new HSSFRichTextString(dtoObj1.getStartdatelogic()));
                            wr.createCell(13).setCellValue(dtoObj1.getStart_date_days());
                            wr.createCell(14).setCellValue(dtoObj1.getStart_date_month());
                            wr.createCell(15).setCellValue(new HSSFRichTextString(dtoObj1.getEnddatelogic()));
                            wr.createCell(16).setCellValue(dtoObj1.getEnd_date_days());
                            wr.createCell(17).setCellValue(dtoObj1.getEnd_date_month());
                            wr.createCell(18)
                                    .setCellValue(new HSSFRichTextString(dtoObj1.getChargeEndDate_String()));
                            wr.createCell(19).setCellValue(new HSSFRichTextString(dtoObj1.getAnnualRate()));
                            wr.createCell(20).setCellValue(new HSSFRichTextString(dtoObj1.getAnnotation()));
                            wr.createCell(21).setCellValue(new HSSFRichTextString(dtoObj1.getStartTokenNo()));
                            wr.createCell(22).setCellValue(new HSSFRichTextString(dtoObj1.getStartFxStatus()));
                            wr.createCell(23).setCellValue(new HSSFRichTextString(dtoObj1.getStartFxNo()));
                            wr.createCell(24).setCellValue(new HSSFRichTextString(dtoObj1.getEndTokenNo()));
                            wr.createCell(25).setCellValue(new HSSFRichTextString(dtoObj1.getEndFxStatus()));
                            wr.createCell(26).setCellValue(new HSSFRichTextString(dtoObj1.getEndFxNo()));
                            wr.createCell(27)
                                    .setCellValue(new HSSFRichTextString(dtoObj1.getChargeStartStatus()));
                            wr.createCell(28)
                                    .setCellValue(new HSSFRichTextString(dtoObj1.getChargeEndStatus()));
                            wr.createCell(29)
                                    .setCellValue(new HSSFRichTextString(dtoObj1.getChargefrequency()));
                            wr.createCell(30).setCellValue(new HSSFRichTextString(dtoObj1.getFxViewId()));
                        }
                        ctr++;
                        rownum++;
                    }
                }
            }
            //         billing trigger bulkupload  sheet 2 end   
        }
        wb.write(fileOut);
        fileOut.close();
        filePath = newFile;
    } catch (IOESException ex) {
        logger.error(ex.getMessage() + "::BULKUPLOAD_ERROR:: Exception occured in getFilledTemplate method of "
                + this.getClass().getSimpleName());
        throw new IOESException("SQL Exception : " + ex.getMessage(), ex);
    } catch (IOException ioExp) {
        //logger.error(ioExp.getMessage() + " Exception occured in getErrorExcel method of " + this.getClass().getSimpleName());
        //throw new IOESException("SQL Exception : "+ ioExp.getMessage(), ioExp);
        Utility.LOG(true, false, ioExp, "::BULKUPLOAD_ERROR:: Exception occured in getFilledTemplate method of "
                + this.getClass().getSimpleName());
        return filePath = "NOTFOUND";
    }
    return filePath;
}

From source file:com.ibm.ioes.bulkupload.utilities.ErrorLogServiceImpl.java

public String getTemplate(String filePath, int templateId) throws IOESException, ParseException {
    //logger.info(" Entered into getErrorExcel method of " + this.getClass().getSimpleName());

    String fileName;//from w  ww .j a va  2s .c o m
    HSSFWorkbook wb;
    HSSFSheet ws;
    HSSFRow wr;
    HSSFCell wc;
    ArrayList filledTemplateData = new ArrayList();
    ErrorFileDaoImpl objDao = new ErrorFileDaoImpl();
    ViewOrderModel objviewmodel = new ViewOrderModel();
    //ErrorLogDto dtoObj ;
    TransactionTemplateDto dtoObj;
    ViewOrderDto dtoObj1;
    DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    int totalRowsOfSheet = 0;
    ViewOrderDto objdto = null;
    String str = null;
    try {
        ResourceBundle bundle = ResourceBundle.getBundle("ApplicationResources");

        fileName = filePath.substring((filePath.lastIndexOf("/") + 1), filePath.length());
        String newFile = bundle.getString("excel.filledTemplate") + "/" + fileName;
        FileOutputStream fileOut = new FileOutputStream(newFile);
        wb = new HSSFWorkbook(new FileInputStream(filePath));
        HSSFCellStyle whiteFG_yellow = wb.createCellStyle();

        HSSFColor yellow = setColor(wb, (byte) 0xFF, (byte) 0xFF, (byte) 0x00);
        //whiteFG_yellow.setFillBackgroundColor(HSSFColor.YELLOW.index);
        whiteFG_yellow.setFillBackgroundColor(yellow.getIndex());

        HSSFCellStyle whiteFG_green = wb.createCellStyle();
        HSSFColor green = setColor(wb, (byte) 0x00, (byte) 0xFF, (byte) 0x00);
        //whiteFG_green.setFillBackgroundColor(HSSFColor.GREEN.index);
        whiteFG_green.setFillBackgroundColor(green.getIndex());

        //System.out.println(filledTemplateData.toString());
        //System.out.println(wb.getNumberOfSheets());

        for (int s = 0; s < wb.getNumberOfSheets(); s++) {
            ws = wb.getSheetAt(s);
            if (s == 0 && (templateId == 1)) {
                int ctr = 0, totalRowsOfSheetMain = 0, totalRowsOfSheetAtt = 0;
                //filledTemplateData = objDao.getFilledTemplate(s+1,templateId,flag);
                for (int r = 2; r <= (totalRowsOfSheetMain + 1); r++) {
                    dtoObj = (TransactionTemplateDto) filledTemplateData.get(ctr);
                    wr = ws.createRow(r);
                    if (wr != null) {
                        wr.createCell(1).setCellValue(dtoObj.getOrderNo());
                        wr.createCell(2).setCellValue(new HSSFRichTextString((dtoObj.getAccountID())));
                        wr.createCell(3).setCellValue(new HSSFRichTextString((dtoObj.getSource())));
                        wr.createCell(4).setCellValue(new HSSFRichTextString((dtoObj.getCurrencyID())));
                        wr.createCell(5).setCellValue(new HSSFRichTextString((dtoObj.getOpportunityId())));
                        wr.createCell(6).setCellValue(new HSSFRichTextString((dtoObj.getQuoteNo())));
                        wr.createCell(7).setCellValue(new HSSFRichTextString((dtoObj.getProjectMangerID())));
                        wr.createCell(8).setCellValue(new HSSFRichTextString((dtoObj.getZoneId())));
                    }
                    ctr++;
                }
            }
            wb.write(fileOut);
            fileOut.close();
            filePath = newFile;
        }
    } catch (IOException ioExp) {
        //logger.error(ioExp.getMessage() + " Exception occured in getErrorExcel method of " + this.getClass().getSimpleName());
        //throw new IOESException("SQL Exception : "+ ioExp.getMessage(), ioExp);
        Utility.LOG(true, false, ioExp, "::BULKUPLOAD_ERROR::Exception occured in getTemplate method of "
                + this.getClass().getSimpleName());
        return filePath = "NOTFOUND";
    }
    return filePath;
}

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

/**
 * Method to get all data for Masters Download
 * @param productID/*from   w ww  .  j av  a 2  s  . c  o m*/
 * @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;
}