Export data from a data page to Excel
Exporting data from a data page to Excel
In order to export data from a data page to an excel spreadsheet, you’ll need to first have a data page set up. You’ll also need to create a template excel file. This template is the structure of the excel file that the data is exported to. The below steps go through the steps of creating a button on a section which calls an activity to export the page to excel.(apologies for some formatting issues)
- Create a template excel file and save this file locally. Note the format of the code in the braces
:
2. Upload the template excel file as a binary file to Pega. In this case, we used “excel” as the App name (Directory), and the file type (extension) as xlsm (there’s a typo in my screen shot, as it is xlsx): 
3. Create an activity to upload the data from the data page to the excel template, which will then be exported locally. Note the Method Parameters on the Page-Copy step, as well as the step page. Step 3 is commented out due to the OOTB method “MSOGenerateExcelFile” being deprecated for Pega 8. The new/current method is called “pxGenerateExcelFile.” Also, keep in mind of the format for the templateRFB. It is directory!Filename!xlsx, for example, “excel!MyExcelSheet!xslx”. Make sure “DownloadFile” is checked:
4. Now that your activity is created, You’ll need to create a button with an on click event to call the activity and generate the export. You’ll need to have the action be an open URL from a new window. This will let you click on your downloaded excel file and view it:
5. After this is done, you should be all set. Launch/Run your case, click on the export to excel button you just created, and you should see your downloaded file in a new window. Click on the file, and it will open up in Excel. The file should match your template. Be sure to validate that your data is correct:
Louis Dreyfus Company
FR
hello,
Do you think it's possible to add the missing images?
If we had existing working template(binary file) on 7.4 should we make any changes to use them with the new method?
I tried to replace the old method by the new and got the following message : The uploaded file template could not be recognized. Please upload a valid file. Refer to template for details.
pegarules log give this additional message :
Invalid template. Could not create property mapping using this template.
Thank you
Virtusa Systems Pvt LTD
AE
HI Pauls,
I configured like as you mentioned . but i was using MSOFileTransferButton for download.But Actually my requirement is to just download the excel. Since the images are not clear i couldn't download the file. But when i ran the activity it works and file is downloaded. I need the same behaviour when i click the button in section.
Thanks,
Dinesha
Evonsys
IN
How to use MSOGenerateExcelFile export DateTime to format dd/MM/yyyy HH:mm:ss?
I want to export data from report definition with OOTB Function "MSOGenerateExcelFile", Then I have problem when I export with DateTime Properties. It's always export DateTime properties in format GMT.
Updated: 20 May 2021 9:57 EDT
Capgemini
IN
Before you pass the page for creating excel , see if you can format the property to datetime as per your requirement and hold it in temp property and use temp property in excel template which you uploaded as binary.
may be it should work.
Refer to following discussion on similar lines:
https://collaborate.pega.com/question/msogenerateexcelfile-export-datetime-format-ddmmyyyy
Capgemini
MX
@pauls1 Hello,
I did the process with the activiy and works fine. the thing is Pega is showing a warning about this:
Maintainability
Activity use
The use of activity rules should be limited. The need for activities can be reduced by using data transforms, as well as techniques such as case management, the Engine API, and declaratives.
In other words, have you went through the same result using a data transform or other different from the activity?
Bpm Company
NL
Thank you for your explanation. For Pega 8.6 I found out that you should use the INS name of the binary file for the excel file: "EXCEL!YOURFILENAME!XSLSX"
otherwise you get an error: (Code-Pega-List)Target workbook instance is null.
Kind regards, Ander
Padmavathy Muthukrishnan
US
I am using pxGenerateExcelFile to generate the excel sheet and I used INS name of the binary file as well.
But sill I am facing "Target workbook instance is null". Any suggestion is appreciated. I used xlsm file.
If I use xlsx file, I get additional error as "Exception in generating Workbook from Template Name"
With 'MSOGenerateExcelFile', No issues faced but this is deprecated so i am looking for option to use pxGenerateExcelFile.
Thanks,
Padma.
Instellars
IN
Hi,
Even I'm facing the same issue, by chance if it is resolved, can you please tell me the solution.
Thanks,
Dilip
Pegasystems Inc.
GB
Please see this post:
pyData for pxGenerateExcelFile
and this post:
Values are not displaying in exported excel sheet in mail
If the file name and file type extension is.xlsm issues will occur. .xlsm files are macro files. Macros can not be saved to a non-macro Excel sheet. Please note the macros are being called through Excel by Pega so we can not change the way Excel stores macros. This is probably more likely an Excel question. As far as I understand with Excel, the macro is part of the file. You wouldn't be able to execute a macro contained in another file
----> .xlsx is the one that is supported in pxGenerateExcelFile as per the below link:
Activities for integrating cases with Excel documents
The activity pxGenerateExcelFile only accepts two rows where one is the header and the other is the property reference.
Please see this post:
pyData for pxGenerateExcelFile
and this post:
Values are not displaying in exported excel sheet in mail
If the file name and file type extension is.xlsm issues will occur. .xlsm files are macro files. Macros can not be saved to a non-macro Excel sheet. Please note the macros are being called through Excel by Pega so we can not change the way Excel stores macros. This is probably more likely an Excel question. As far as I understand with Excel, the macro is part of the file. You wouldn't be able to execute a macro contained in another file
----> .xlsx is the one that is supported in pxGenerateExcelFile as per the below link:
Activities for integrating cases with Excel documents
The activity pxGenerateExcelFile only accepts two rows where one is the header and the other is the property reference.
If you have any additional rows then this errors are Expected. PxGenerateExcelFile is created for efficient performance. See archived support article.
Cathay
CN
@MarijeSchillern How to download in the current page window instead of opening a new window?
Updated: 3 Jun 2024 6:28 EDT
Accenture
IN
@LiXiangTang [Actions-->Open URL in window] Add _self in window name
Cathay
CN
@Muthuvelkumaran Thank you very much. That solves my problem.
Accenture
IN
@Ander van Poppel That helped me resolve the issue. Thanks :-)
Ford Motor Company
IN
@pauls1 i keep receiving The operation completed successfully, but returned no content.
My datapage created using Rest and DT to get the result using API