This knowledge sharing article shows step-by-step configurations of how to upload an Excel file, parse the Excel data, and display them in a table using Constellation UI.

Pega platform version used: 24.2.2

Configurations

Step 1 - Create a data model.

We created an Employee data object and the related fields.

Step 2 - Create an Excel template file (.xlsx) and the corresponding Binary File rule.

Notice the .pxResults() notation to import more than one record and the keyword "input" at the end of each cell.

Create a Binary File rule and upload the Excel file.

Step 3 - Create a new DocumentAttached field (type=Attachment) in the case type.

Step 4 - Add the new DocumentAttached field to the view in the case type.

This will allow users to upload an Excel file to the case.

Step 5 - In the post processing of the 'Attach Excel file' flow action, call an activity to fetch the Excel data.

This will be executed when user submits after uploading an Excel file.

Activity steps:

Pages & Classes:

In Step 1, we open the case attachment object (Data-WorkAttach-File).  This is the Excel file uploaded by the user to the case.

Parameter
Primary.DocumentAttached.pzInsKey

In Step 2, pxCreateFile activity creates a file in the service export directory.  In my example, the location is "file://web:/StaticContent/global/ServiceExport/".

Parameter
AttachmentFile.pyAttachStream
AttachmentFile.pxAttachName
AttachmentFile.pyStyle

In Step 3, we are setting the FSFileName parameter, and in Step 4, we are calling OOTB pxParseExcelFile activity to parse the Excel data.

Parameter set (Step 3)
param.FSFileName = pxProcess.pxServiceExportPath + AttachmentFile.pxAttachName

Parameter (Step 4)
param.FSFileName
webwb!EmployeeTemplate!xlsx

In Step 5, we're looping thru the parsed Excel data and mapping to the EmployeeInfo case property (PageList). 

Here is the EmployeeInfo property (type=PageList) defined under the work class.  

Step 6 - Configure a view to display the parsed Excel data in a table.

Add the EmployeeInfo field (Embedded Data).

Drill down to edit the EmployeeInfo field and add the columns.

Testing

Step 1 - Create a new case and attach an Excel file.

Here is the sample Excel file used.

Upon submitting, system will execute the FetchExcelData activity (in the flow action post processing) to parse the Excel data and map to the EmployeeInfo case property.

Step 2 - The Excel data is shown in the table.

Additional information

• In order to further ensure the data quality entered in the Excel, we can leverage the validation logics in the Data > Data Validation feature in Excel itself.  This can minimize user from importing 'bad data' into Pega.
• Refer to this article about step-by-step configurations of exporting data to an Excel file.
• Refer to this article about step-by-step configurations of attaching an Excel file to email.

Please leave any feedback or question.

Constellation 101 Series: 

Enjoyed this article? See more similar articles in Constellation 101 series.