Skip to main content

This content is closed to future replies and is no longer being maintained or updated.
Links may no longer function. If you have a similar request, please write a new post.

Question

 Sushil Thakur (SUSHILTHAKUR)
Capgemini

Capgemini
IN
SUSHILTHAKUR Member since 2013 2 posts
Capgemini
Posted: Sep 19, 2016
Last activity: Jul 12, 2017
Posted: 19 Sep 2016 9:20 EDT
Last activity: 12 Jul 2017 12:32 EDT
Closed
Solved

Generate excel with multiple tab in PRPC 6.1 SP2

Hi ,

I followed the approach given in the article : https://pdn.pega.com/community/pega-product-support/question/creating-excel-report-multiple-tabs

It is working fine in 7.1.9 version. But while implementing it in PRPC 6.1 SP2 , a corrupt excel file is getting generated and when open that excel file getting the error :

Can anyone help ?

***Updated by moderator: Lochan to add Categories***

To see attachments, please log in.
Reporting

Accepted Solution

Posted: 8 years ago
Posted: 20 Sep 2016 9:48 EDT
John Pritchard-Williams (JOHNPW_GCS) Client Support Team Lead
Pegasystems Inc.
GB

So it appears that the Activity response *itself* has been streamed to the client , rather than the Excel sheet.

Since the 'Excel' file which is opening basically contains the Activity 'Status: good' ; not sure why.

You need to post your Activity here so we can take a look.


Can you get to this link or not ? https://mesh.pega.com/docs/DOC-89784

(There is a worked example using the XML Stream Rules to generate Multi-tabbed Excel Sheets [in Office 2003 XML format there).

 

 

 

To see attachments, please log in.

Accepted Solution

Posted: 8 years ago
Posted: 20 Sep 2016 9:48 EDT
John Pritchard-Williams (JOHNPW_GCS) Client Support Team Lead
Pegasystems Inc.
GB

So it appears that the Activity response *itself* has been streamed to the client , rather than the Excel sheet.

Since the 'Excel' file which is opening basically contains the Activity 'Status: good' ; not sure why.

You need to post your Activity here so we can take a look.


Can you get to this link or not ? https://mesh.pega.com/docs/DOC-89784

(There is a worked example using the XML Stream Rules to generate Multi-tabbed Excel Sheets [in Office 2003 XML format there).

 

 

 

To see attachments, please log in.
Posted: 8 years ago
Posted: 20 Sep 2016 10:10 EDT
John Pritchard-Williams (JOHNPW_GCS) Client Support Team Lead
Pegasystems Inc.
GB

And if you can't get to the link: here's some PDFs of that (and two related posts) that give details of how to build multi-sheet Excel sheets from PRPC - using two methods - XML method and Apache POI.

The original post contains RAP files - which are not present in the PDFs; but the screenshots should be enough to rebuild - there are also some minor formatting issues in the PDFs - but you should be able to rescue it with a bit of copy/paste work.

 

 

To see attachments, please log in.
Posted: 7 years ago
Posted: 12 Jul 2017 12:32 EDT
Sudheer Nimmagadda (SudheerNimmagadda)
Cognizant

Cognizant
US

Just FYI,we can aechive this functionality by using below java code too.

-------------------

try{
    //String excelFileName = "/usr/pega/temp/PCS/pcs7devb_temp/StaticContent/global/ServiceExport/BKTestFile.xls";
    //name of excel file
    String excelFileName=tools.getParamValue("CCFFilePath");
    int MNCDH=tools.findPage("MSGRNonCDH").getProperty("pxResults").size();
    int MCDH=tools.findPage("MSGRCDH").getProperty("pxResults").size();
    int NCDH51=tools.findPage("NONCDH51").getProperty("pxResults").size();
    int CDH=tools.findPage("CDH51").getProperty("pxResults").size();
    
    int EXP1=tools.findPage("EXCP1").getProperty("pxResults").size();
    int EXP2=tools.findPage("EXCP2").getProperty("pxResults").size();
    int SGX=tools.findPage("SGEX").getProperty("pxResults").size();

    int PageIndex=0;
    int ColIndex;
    String ColumnValue="";
    org.apache.poi.hssf.usermodel.HSSFWorkbook wb = new org.apache.poi.hssf.usermodel.HSSFWorkbook();
    org.apache.poi.hssf.usermodel.HSSFFont font = wb.createFont();
    font.setBoldweight(org.apache.poi.hssf.usermodel.HSSFFont.BOLDWEIGHT_BOLD);
    org.apache.poi.hssf.usermodel.HSSFCellStyle styleBold = wb.createCellStyle();
    org.apache.poi.hssf.usermodel.HSSFCellStyle styleWrap = wb.createCellStyle();
    
    styleBold.setFont(font);

Show More

Just FYI,we can aechive this functionality by using below java code too.

-------------------

try{
    //String excelFileName = "/usr/pega/temp/PCS/pcs7devb_temp/StaticContent/global/ServiceExport/BKTestFile.xls";
    //name of excel file
    String excelFileName=tools.getParamValue("CCFFilePath");
    int MNCDH=tools.findPage("MSGRNonCDH").getProperty("pxResults").size();
    int MCDH=tools.findPage("MSGRCDH").getProperty("pxResults").size();
    int NCDH51=tools.findPage("NONCDH51").getProperty("pxResults").size();
    int CDH=tools.findPage("CDH51").getProperty("pxResults").size();
    
    int EXP1=tools.findPage("EXCP1").getProperty("pxResults").size();
    int EXP2=tools.findPage("EXCP2").getProperty("pxResults").size();
    int SGX=tools.findPage("SGEX").getProperty("pxResults").size();

    int PageIndex=0;
    int ColIndex;
    String ColumnValue="";
    org.apache.poi.hssf.usermodel.HSSFWorkbook wb = new org.apache.poi.hssf.usermodel.HSSFWorkbook();
    org.apache.poi.hssf.usermodel.HSSFFont font = wb.createFont();
    font.setBoldweight(org.apache.poi.hssf.usermodel.HSSFFont.BOLDWEIGHT_BOLD);
    org.apache.poi.hssf.usermodel.HSSFCellStyle styleBold = wb.createCellStyle();
    org.apache.poi.hssf.usermodel.HSSFCellStyle styleWrap = wb.createCellStyle();
    
    styleBold.setFont(font);

    for (int SheetNo=0;SheetNo < 7; SheetNo++ ) {
        String sheetName="";
        
        if (SheetNo==0) {sheetName = "MSGR-NON CDH";}
        else if (SheetNo==1){sheetName = "MSGR-CDH";}
        else if (SheetNo==2){sheetName = "51+ NON CDH";}    //name of sheet
        
        org.apache.poi.hssf.usermodel.HSSFSheet sheet = wb.createSheet(sheetName) ;

        //iterating r number of rows
        if (SheetNo==0) 
        {
            //MSGR-NON CDH
            for (int RowNo=0;RowNo < MNCDH+1; RowNo++ )
            {
                org.apache.poi.hssf.usermodel.HSSFRow row = sheet.createRow(RowNo);
                if (RowNo>0)
                {
                    PageIndex=RowNo;
                    ClipboardPage MSGRNCDHPage = tools.findPage("MSGRNonCDH").getProperty("pxResults").getPageValue(PageIndex);
                    for (int ColNo=0;ColNo < 13; ColNo++ )
                    {
                        org.apache.poi.hssf.usermodel.HSSFCell cell = row.createCell(ColNo);
                        if (ColNo==0){ColumnValue=MSGRNCDHPage.getString("CFProductID");}
                        else if (ColNo==1){ColumnValue=MSGRNCDHPage.getString("INDIVIDUALDEDUCTIBLE");}
                        else if (ColNo==2){ColumnValue=MSGRNCDHPage.getString("FAMILYDEDUCTIBLE");}
                        else if (ColNo==3){ColumnValue=MSGRNCDHPage.getString("NONMAINTGENERIC");}
                        else if (ColNo==4){ColumnValue=MSGRNCDHPage.getString("NONMAINTPREFERRED");}
                        else if (ColNo==5){ColumnValue=MSGRNCDHPage.getString("NONMAINTNONPREFERRED");}
                        else if (ColNo==6){ColumnValue=MSGRNCDHPage.getString("NONMAINTSELFINJSPECS");}
                        else if (ColNo==7){ColumnValue=MSGRNCDHPage.getString("NONMAINTMAXDAYSSUPPLY");}
                        else if (ColNo==8){ColumnValue=MSGRNCDHPage.getString("MAINTGENERIC");}
                        else if (ColNo==9){ColumnValue=MSGRNCDHPage.getString("MAINTPREFERRED");}
                        else if (ColNo==10){ColumnValue=MSGRNCDHPage.getString("MAINTNONPREFERRED");}
                        else if (ColNo==11){ColumnValue=MSGRNCDHPage.getString("MAINTSELFINJSPECS");}
                        else if (ColNo==12){ColumnValue=MSGRNCDHPage.getString("MAINTMAXDAYSSUPPLY");}        
                        cell.setCellValue(ColumnValue);
                    }    
                    
                }
                else
                {
                    for (int ColNo=0;ColNo < 13; ColNo++ )
                    {
                        org.apache.poi.hssf.usermodel.HSSFCell cell = row.createCell(ColNo);
                        if (ColNo==0){ColumnValue="RX PRODUCT ID";}
                        else if (ColNo==1){ColumnValue="IND DEDUCT";}
                        else if (ColNo==2){ColumnValue="FAM DEDUCT";}
                        else if (ColNo==3){ColumnValue="GENERIC";}
                        else if (ColNo==4){ColumnValue="PREFERRED";}
                        else if (ColNo==5){ColumnValue="NON PREFERRED";}
                        else if (ColNo==6){ColumnValue="SELF INJ - SPECIALTY";}
                        else if (ColNo==7){ColumnValue="DAYS SUPPLY";}
                        else if (ColNo==8){ColumnValue="MAINT GENERIC";}
                        else if (ColNo==9){ColumnValue="MAINT  PREFERRED";}
                        else if (ColNo==10){ColumnValue="MAINT NON PREFERRED";}
                        else if (ColNo==11){ColumnValue="SELF INJ - SPECIALTY";}
                        else if (ColNo==12){ColumnValue="DAYS SUPPLY";}
                        cell.setCellValue(ColumnValue);
                        cell.setCellStyle(styleBold);
                    }
                }
            }
            for (int ColNo=0;ColNo < 13; ColNo++ )
            {
                sheet.autoSizeColumn(ColNo); 
            }
        }
    
    
        else if (SheetNo==1) 
        {
            //SmallGrp Exchange
            for (int RowNo=0;RowNo < SGX+1; RowNo++ )
            {
                org.apache.poi.hssf.usermodel.HSSFRow row = sheet.createRow(RowNo);
                if (RowNo>0)
                {
                    PageIndex=RowNo;
                    ClipboardPage SGEXPage = tools.findPage("SGEX").getProperty("pxResults").getPageValue(PageIndex);
                    for (int ColNo=0;ColNo < 18; ColNo++ )
                    {
                        org.apache.poi.hssf.usermodel.HSSFCell cell = row.createCell(ColNo);
                        if (ColNo==0){ColumnValue=SGEXPage.getString("CFProductID");}
                        else if (ColNo==1){ColumnValue=SGEXPage.getString("INTEGRATEDOOP");}
                        else if (ColNo==2){ColumnValue=SGEXPage.getString("DEDACCUMMETHOD");}
                        else if (ColNo==3){ColumnValue=SGEXPage.getString("INDIVIDUALDEDUCTIBLE");}
                        else if (ColNo==4){ColumnValue=SGEXPage.getString("FAMILYDEDUCTIBLE");}
                        else if (ColNo==5){ColumnValue=SGEXPage.getString("INDIVIDUALOOP");}
                        else if (ColNo==6){ColumnValue=SGEXPage.getString("FAMILYOOP");}
                        else if (ColNo==7){ColumnValue=SGEXPage.getString("HBSELECTGENERIC");}
                        else if (ColNo==8){ColumnValue=SGEXPage.getString("NONMAINTGENERIC");}
                        else if (ColNo==9){ColumnValue=SGEXPage.getString("NONMAINTPREFERRED");}
                        else if (ColNo==10){ColumnValue=SGEXPage.getString("NONMAINTNONPREFERRED");}
                        else if (ColNo==11){ColumnValue=SGEXPage.getString("NONMAINTSELFINJSPECS");}
                        else if (ColNo==12){ColumnValue=SGEXPage.getString("NONMAINTMAXDAYSSUPPLY");}
                        else if (ColNo==13){ColumnValue=SGEXPage.getString("MAINTGENERIC");}
                        else if (ColNo==14){ColumnValue=SGEXPage.getString("MAINTPREFERRED");}
                        else if (ColNo==15){ColumnValue=SGEXPage.getString("MAINTNONPREFERRED");}
                        else if (ColNo==16){ColumnValue=SGEXPage.getString("MAINTSELFINJSPECS");}
                        else if (ColNo==17){ColumnValue=SGEXPage.getString("MAINTMAXDAYSSUPPLY");}        
                        cell.setCellValue(ColumnValue);
                        if (ColNo==1 || ColNo==2)
                        {
                            styleWrap.setWrapText(true); 
                            cell.setCellStyle(styleWrap);
                        }
                    }    
                    
                }
                else
                {
                    for (int ColNo=0;ColNo < 18; ColNo++ )
                    {
                        org.apache.poi.hssf.usermodel.HSSFCell cell = row.createCell(ColNo);
                        if (ColNo==0){ColumnValue="RX PRODUCT ID";}
                        else if (ColNo==1){ColumnValue="INTEGRATED BENEFITS";}
                        else if (ColNo==2){ColumnValue="STACKED OR UNSTACKED";}
                        else if (ColNo==3){ColumnValue="IND DED";}
                        else if (ColNo==4){ColumnValue="FAM DED";}
                        else if (ColNo==5){ColumnValue="IND OOP";}
                        else if (ColNo==6){ColumnValue="FAM OOP";}
                        else if (ColNo==7){ColumnValue="HEALTHYBLUE SELECT GENERICS";}
                        else if (ColNo==8){ColumnValue="GENERICS";}
                        else if (ColNo==9){ColumnValue="PREFERRED BRAND";}
                        else if (ColNo==10){ColumnValue="NON PREFERRED BRAND";}
                        else if (ColNo==11){ColumnValue="SPECIALTY DRUGS";}
                        else if (ColNo==12){ColumnValue="DAYS SUPPLY";}
                        else if (ColNo==13){ColumnValue="MAINT GENERICS";}
                        else if (ColNo==14){ColumnValue="MAINT  PREFERRED BRAND";}
                        else if (ColNo==15){ColumnValue="MAINT NON PREFERRED BRAND";}
                        else if (ColNo==16){ColumnValue="SPECIALTY DRUGS";}
                        else if (ColNo==17){ColumnValue="DAYS SUPPLY";}
                        cell.setCellValue(ColumnValue);
                        cell.setCellStyle(styleBold);
                    }
                }
            }
        }
        for (int ColNo=0;ColNo < 18; ColNo++ )
            {
                sheet.autoSizeColumn(ColNo); 
            }
    }
    java.io.FileOutputStream fileOut = new java.io.FileOutputStream(excelFileName);
    java.util.Map xlZipEntryMap = null;
    //write this workbook to an Outputstream.
    wb.write(fileOut);
    com.pegarules.generated.pega_appdefinition_excelintegration.DCOzipEntryMapToStream(xlZipEntryMap, fileOut);
    pega_rules_default.downloadFile(excelFileName.toString(), tools, true);
                                //fileOut.flush();
                                //fileOut.close();
                oLog.infoForced("Test CC  file Generated  "+excelFileName);
}catch(Exception e){oLog.error("Error in Generation CC file");}

----------------


Show Less
To see attachments, please log in.

We'd prefer it if you saw us at our best.

Pega Collaboration Center has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice