Using an Excel Template to generate Report in PeopleSoft

All of us have used people-code to generate custom csv/xls report. But most of the time the report is in a tabular format. Is there a way to use a template to generate report without resorting to BI Publisher? Of course there is. In this post let's discussed one of the way we can use an existing template for report generation.

Sample Report Template:

Create your Template first. I have created a simple sample template with only Date and 3 other values to be shown for the report.

Save it to the ftp location with file name as "template.xlsx".

Now with the code, we will access this file, create a copy at the destination and generate the values at the proper cells.

 Here is the sample Code:

/*****************************************************************/
/* Code Added By: Avishek Das                                    */
/* Code Added on: 10-Sep-2018                                    */
/* Code Added for: Access a Template and generate Report         */
/*****************************************************************/


/*Worksheet columns value*/
Local string &Value_1 = "Value 1";
Local string &Value_2 = "Value 2";
Local string &Value_3 = "Value 3";
Local string &Date = "10/17/2018";

/*Set the required paths and file name*/
Local string &templateFolderPath = "\\ftp\Templates\";
Local string &outputFolderPath = "\\ftp\Reports\";
Local string &templateFileName = "template.xlsx";
Local string &outputFileName = "Report.xlsx";

/* Open Excel file using the blank template */
/* save the blank Excel file as the Report file */
try
   /*Get the template file path*/
   Local string &qualifiedPathForTemplate = &templateFolderPath | &templateFileName;
   /*Get the output file path*/
   Local string &qualifiedPathForOutput = &outputFolderPath | &outputFileName;
  
   /*Get the COM Object for Excel*/
   &oWorkAppRep1 = CreateObject("COM", "Excel.Application");
   /*Supress the alerts*/
   &oWorkAppRep1.DisplayAlerts = "False";
   /*Get the Workbooks object*/
   &oWorkBookRep = ObjectGetProperty(&oWorkAppRep1, "Workbooks");
   /*Open the main Worksheet file*/
   &oWorkBookRep.Open(&qualifiedPathForTemplate);
   /*Get the main Worksheet*/
   &oWorkSheetRep = &oWorkAppRep1.Worksheets(&worksheetName);
   /* save the workbook in the output directory*/
   &oWorkAppRep1.ActiveWorkBook.SaveAs(&qualifiedPathForOutput);
  
   /* exceptions */
catch Exception &c1
   /* display the exception */
   MessageBox(0, "", 0, 0, " Error:  Unable to create " | &qualifiedPathForOutput | ". " | &c1.ToString());
   Return; /*Don't continute with the process*/
end-try;

/*Write your Report Logic here*/

/*Assign the values to proper Cells*/
   &oWorkSheetRep.cells(2, 2).value = &Date;
   &oWorkSheetRep.cells(4, 2).value = &Value_1;
   &oWorkSheetRep.cells(4, 5).value = &Value_1;
   &oWorkSheetRep.cells(6, 3).value = &Value_1;
  
/* Save Excel file*/
&oWorkAppRep1.ActiveWorkBook.Save();

/* Close Excel file*/
try
   &oWorkAppRep1.ActiveWorkBook.Close();
   &oWorkAppRep1.DisplayAlerts = "True";
   &oWorkAppRep1.Quit();
catch Exception &c3
   /* display a message */
   MessageBox(0, "", 0, 0, " Error:  Unable to Close Excel File. " | &c3.ToString());
end-try;

Once you Execute this code, the file "Report.xlsx" will be generated at the destination folder.

Let me know in comments if this helps or any other way of using template for report generation.