Question
Ernst & Young LLP
IN
Last activity: 4 May 2021 23:02 EDT
Dynamic Column Headers for excel and Values from different page list for rows
Hi Everyone,
I have a requirement where the column headers of the excel should be populated dynamically for which the values will be present in one Data Page. The row values of the excel are present in a different Data page. Is there a way to pass two different page lists to the OOTB activity MSOGenerateExcelFile to generate a excel file.
Accepted Solution
Updated: 4 May 2021 23:02 EDT
Ernst & Young LLP
IN
Hi Team,
I was able to achieve the desired functionality by manipulating the data and using the pega OOTB activity "ExportToExcel". To those who do not know about this activity I will explain the working of the same. This activity takes the parameter "pyFileData" which should contain the data stream that needs to be processed and exported to the excel.
If you add "\n" in to the parameter "pyFileData" like "First Line"+"\n"+"Second Line", then the text "First Line" will be displayed in the first row of the excel and "\n" makes the text "Second Line" to move into the second row of the excel.
Similarly if "\t" is given the data will be moved to next cell in the same row. You can manipulate the data and accordingly use "\n" and "\t" to fine tune on how the data should be displayed in the excel.
Aaseya IT Services Pvt LTD
SA
Hi Shiva,
It is definitely possible to dynamically set the excel headers while exporting. As you mentioned, you have different data page for results and headers.
You can call the MSOGenerateExcelFile activity on the row result page. No need for header data page here. You can directly copy the complete page structure in place of column headers in the excel.
Here's what i did.
1 Created a DT where i created a page group and set the column header properties. For Example.
ColHeaderPage(Excel).ColumnProp1 = "Header1" and so on.
2. Run the MSOGenerateExcelFile on the D_Page page.
3. In the Excel binary file, in the column row, i put ColHeaderPage(Excel).ColumnProp1 and so on.
4. In the results row, i put {.pxResults.RowProp1}
For step 1, As you already have a data page, you can skip it.
Ernst & Young LLP
IN
Hi Prakash,
Thanks for your assistance but unfortunately the scenario I am trying to implement is different. For my scenario, for one file there may be 4 columns as header, for another file there may be 10 columns as header and the user has option to change the header numbers in UI and the corresponding values should be exported in excel.
Areteans
IN
@PrakashDeep Hi Prakash, Did you create the page group property in the row result data page context or it is a standalone temp page?
Please could you provide the syntax to refer the header property in the excel . Becoz (ColHeaderPage(Excel).ColumnProp1) didnt work for me.
Accepted Solution
Updated: 4 May 2021 23:02 EDT
Ernst & Young LLP
IN
Hi Team,
I was able to achieve the desired functionality by manipulating the data and using the pega OOTB activity "ExportToExcel". To those who do not know about this activity I will explain the working of the same. This activity takes the parameter "pyFileData" which should contain the data stream that needs to be processed and exported to the excel.
If you add "\n" in to the parameter "pyFileData" like "First Line"+"\n"+"Second Line", then the text "First Line" will be displayed in the first row of the excel and "\n" makes the text "Second Line" to move into the second row of the excel.
Similarly if "\t" is given the data will be moved to next cell in the same row. You can manipulate the data and accordingly use "\n" and "\t" to fine tune on how the data should be displayed in the excel.