Wednesday, October 20, 2021

Generating excel output with multiple sheets using RTF Template

So as the title suggests, in this exercise I am testing PeopleSoft's BI Publisher reporting engine to output an excel file, while using a RTF template. In addition to producing the output in excel format, I have to split the result into multiple sheets within the same excel workbook. So lets get started.

For this POC I am using Oracle BI Publisher Desktop 12.2.1.4.0. 

I am displaying a simple table with two columns on each sheet within the workbook. In order to name the sheet within the workbook I am using the following form field tag

<?spreadsheet-sheet-name: {.//N_SHEET_NAME}?> where N_SHEET_NAME is a data element in my XML data source.

My XML data source is structured as shown below.




 












In my RTF template, I have the following two tables separated by a page break. So the first table is on page 1 of the RTF file and the second table is on page 2.





Table 1 form field definitions have been defined as below.

F = <?for-each:N_PERSON_DATA?> 

E = <?end for-each?>

Below the table I have defined the following tags.

<?for-each-group:N_PERSON;./N_SHEET_NAME?>

<?spreadsheet-sheet-name: {.//N_SHEET_NAME}?>

<?end for-each-group?>

After this there is page-break which was created by Ctrl + Enter.

Table 2 follows a similar construct as explained below.

F = <?for-each:N_ADDRESS_DATA?>

E = <?end for-each-group?>

<?for-each-group:N_ADDRESS;./N_SHEET_NAME?>

<?spreadsheet-sheet-name: {.//N_SHEET_NAME}?>

<?end for-each-group?>

Excel output below.

Sheet 1






Sheet 2







No comments:

Post a Comment