How to export from Page List to excel in pega

How to export from Page List to excel in pega

Hi Friends, Today we will see how to export data from Page List to Excel sheet in pega.

Below is the List of steps we will follow to do this.

1. Form Data in Page List
2. Create Binary File Rule and Upload excel file contains page format of property
3. Call Pega built in utility to export data

We have LeaveBalanceDays data Class which have list of Records. Now we will try to export these data to excel.

Below is the list of steps we will use in activity.

Pages and Class Tab:

Form Data in Page List:

 Before exporting data to excel we are forming the data in page list. In the Obj-Browse we are retrieving list of records and keeping in the Page List named EmpList.

Create Binary File Rule and Upload excel file contains page format of property:

We have to create a binary file rule which contains sample structure of data that we are going to export. Below screenshot is the excel file that we are going to upload in binary file.

Each EmployeeID in page will print in EmployeeID Column , Each FirstName in page will print in FirstName Column .

Save this file in your local machine in format .xlsx

Create binary file with Directory name as excel  and  file type as xlsx 

After creation of binary file upload the excel file template that we stored in local machine.

Save the binary rule.

Lets come to activity steps. MSOGenerateExcelFile is the built in activity to export data to excel we have to provide some required parameters to that activity.

For MSOGenerateExcelFile activity we have to pass 3 parameters.

1. Param.FSFileName - Provide excel file name that we uploaded.
2. Param.TemplateRFB - Have to provide binary file template name.
3. Param.DownloadFile - true if we want to download file.

Check Pass Current parameter in method parameters.

Lets test our steps by run the activity.

Excel file got downloaded open the file and check whether data exported or not.

Data in data table exported to excel file. Hope this concept understood . If any queries ask in comment friends😊

Note: In some pega version MSOGenerateExcelFile activity may through nullpointer exception. If so replace java step in MSOGenerateExcelFile with Below code and try:

String xlFileName = tools.getParamValue("FSFileName");
PRFile xlFile = new PRFile(xlFileName);

java.io.OutputStream xlOS = null;
java.io.InputStream tempIS = null;
byte[] tempBytes = null;
boolean projectSizingPresent = false;
String strWorkObjectRef = "";
ClipboardPage objRefPage = null;
java.util.ArrayList sizingList = new java.util.ArrayList();
try {

  
        tempBytes = new com.pega.pegarules.pub.util.Base64Util().decodeToByteArray(tools.findPage("TemplateFile").getString("pyFileSource"));
  
  
    tempIS = new java.io.ByteArrayInputStream(tempBytes);
    java.util.Map xlZipEntryMap = null;
  
        xlZipEntryMap = com.pegarules.generated.pega_appdefinition_excelintegration.DCOXLGenerateZipEntryMap(tempIS);
  
    xlOS = new PROutputStream(xlFile);
    com.pegarules.generated.pega_appdefinition_excelintegration.DCOzipEntryMapToStream(xlZipEntryMap, xlOS);
    if (tools.getParamAsBoolean(PropertyInfo.TYPE_TRUEFALSE, "DownloadFile")) {
        pega_rules_default.downloadFile(xlFile.toString(), tools, true);
    }
}
catch (Exception e) {
    oLog.error("Error in parsing Excel file " + xlFileName, e);
    //throw new RuntimeException(e);
    tools.getStepPage().addMessage(e.toString());
}
finally {
    if (xlOS != null) try { xlOS.close(); } catch (Exception e) { }
    if (tempIS != null) try { tempIS.close(); } catch (Exception e) { }
}