Question
Commonwealth Bank of Australia
IN
Last activity: 4 Jan 2024 10:35 EST
Generate an excel file with custom columns
HI All, Do we have an OOTB way of generating an excel file with custom columns as how the export to excel functionality in report works. We can use the 'internal' pzRDExportWrapper activity to run the report and load the data on to a excel file in service path. But in a scenario if we have a page list, how can we do the same as above? I have traced the run of the pzRDExportWrapper, system triggers an internal code which again triggers pxRetrieveReportData and post this its internal again. So, what actually happens internally? Thanks,
Sangeeth
Pegasystems Inc.
IN
@Sangeeth you can use OOTB activity MSOGenerateExcelFile for this. And the mapping you can specify in an excel template and it can be passed as a parameter to this activity
Commonwealth Bank of Australia
IN
Thanks for your time on the reply, but if we're going to use that activity we have pass a template in which we have to predefine the column mapping. In this scenario, I want to if there's a logic which just generates the file dynamically by passing a pageList, like how you run a report and you'll have an option to export to excel, which will download the data with the columns specified in the column sources in report
Tekclan Software Solutions
IN
Hi @Sangeeth,
For Dynamic columns instead of using Excel file generation we can achieve by using generating Excel file.
- call Report definition in activity
- Set the required parameters(Properties name, Headers names, PropertyTypes)
- Call the pxConvertResultsToCSV activity to download the excel file
Thanks.
Updated: 30 Dec 2023 12:23 EST
Commonwealth Bank of Australia
IN
@DhanasekarC0202 Thanks for your time on the reply, but if we're going to use pxConvertResultsToCSV, we have pass a headers and properties which again to have be defined somewhere and retrieved. In this scenario, I want to know if there's a logic which just generates the file dynamically by passing a pageList, like how you run a report and you'll have an option to export to excel, which will download the data with the columns specified in the column sources in report
Tekclan Software Solutions
IN
Hi @Sangeeth,
While analysing the code of pzRDExportWrapper activity, which again browse the results from DB and by using Properties names from the pyReportDefintion page and types also.
But using pxConvertResultsToCSV we can achieve this by adding java step by adding below mentioned code
try {
ClipboardPage clipboardPage = tools.getStepPage();
java.util.Iterator iter = clipboardPage.values().iterator();
while(iter.hasNext()){
ClipboardProperty prop = (ClipboardProperty) iter.next();
if (prop.getName() != "pxObjClass")
{
if (CSVProperties != "")
{
CSVProperties = CSVProperties+","+prop.getName();
CSVPropHeaders = CSVPropHeaders+","+prop.getName();
CSVPropertyTypes = CSVPropertyTypes+","+prop.getTypeName();
}
else
{
CSVProperties = prop.getName();
CSVPropHeaders = prop.getName();
CSVPropertyTypes = prop.getTypeName();
}
}
}
}
catch(Exception ex){
oLog.infoForced(ex.getMessage());
}
Thanks.
Commonwealth Bank of Australia
IN
@DhanasekarC0202 Can I know which version of PEGA, you're on since I have looked at the pzRDExportWrapper logic and if you're output type is 'excel', it would just execute 'show-html' step and exit it.
Further more, not sure if this happens during looping but in the above code you have, it says get all the property names which has values only. So, this would eliminate the properties which doesn't have values. And also, not sure if this happens, when processing a pagelist via Java, would the page will have property names which are empty? need to check this scenario once. And if you're using pxConvertResultsToCSV, the output file type is only 'csv', I need to have the filetype as 'xlsx'. Thanks,
Sangeeth
Tekclan Software Solutions
IN
Hi @Sangeeth,
I am using pega 8.7 version. pzRDExportWrapper will have one show html alone, Above piece of logic is constructed for requirement.
Thanks.
Cognizant
IN
There is no OOTB activity which will take care of the column headers generation to the excel file dynamically by passing the page list properties.
For dynamically generating the excel column headers you need to customize the code in java inside the activity to the Local variables .Use the local variable to generate the excel by OOTB activity.
The code customization of column headers is available in "pxDownloadRecordAsCSV(Pega-Desktop)" you can directly use it if the pagelist is generated from report definition else little customization is required.
Hope this helps.
Commonwealth Bank of Australia
IN
@vijayadurgay , thanks for your reply. With report, we can use the above and couple of other rules to generate files but with I am looking something with pagelist to excel with or without headers.