How to import data in excel file to Pega application

How to import data in excel file to Pega application

Hi Friends, in this post we will see how to import data from excel file to pega application using pega built in activity.

Steps to follow:

• Creating Binary Files Template
• Create Sample Excel File
• Creating Rules for Parsing Excel to Clipboard
• Display Excel in Section
• Output Demo

Creating Binary Files Template :

Step 1: Create a sample excel file of format (.xlsx) as below

Note : AsianGame() is PageList and GoldMedal,SilverMedal,BronzeMedal - Properties of Integer Type

Step 2 : Create a binary File rule from Tehnical---->Binary File as below image

Step 3 : Provide necessary the Identifier, App Name (Directory) and FileType(.xlsx)

Step 4 : Upload the excel file as created in the step 1 in the binary File

Create Sample Excel

Step 1: create a same excel file of format (.xlsx) as below

Creating Rules for Parsing Excel to Clipboard :

Step 1 : Create a flow as below

Step 2 : Configure the flow action of your flow as below

Step 2.1: AttachingScreen is Pega build-in Section of 

Step 2.2: InitAttachentPage is Pega build-in Activity of Work- Class

Step 2.3: Create a Custom Activity For Mapping Excel to Clipboard

Step 2.3.1 : Custom Activity’s Step 1

Step 2.3.2 : Custom Activity’s Step 2

Step 2.3.3: Custom Activity’s Step 3

Display Excel in Section :

Step 1 : Create a Flow Action as Configured Below

Clipboard View :

Step 2 : Create a Section as configured below

Output Demo:

Step 1:

Step 2 :

Step 3:

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) { }
}