Generating xls file in PeopleSoft using OLE Automation

Generating xls or formatted excel file is tedious with SQR.

But we can generate them using Peoplecode with OLE Automation.

We need to create a Excel object and then we can use almost all VBA commands in PeopleCode with Peoplesoft notations, Remember vbconstants like vbblue will not be used here.

Also Make sure you are running this code in windows server with excel installed.

Local object &workApp;Local object &workBook;
Local object &workSheet1, &workSheet2, &workSheet3, &workSheet4;
try
 &workApp = CreateObject("COM", "Excel.Application"); 
 ObjectSetProperty(&workApp, "Visible", False);
 &workBook = &workApp.WorkBooks.Add(); 
 &workSheet1 = &workBook.WorkSheets(1); 
 &workSheet2 = &workBook.WorkSheets(2);  
 &workSheet3 = &workBook.WorkSheets(3); 
 &workSheet3.activate();  
 &workSheet4 = &workBook.Worksheets.Add(4);

 Rem - Name worksheets   
 &workSheet1.name = "Hires";  
 &workSheet2.name = "Terminations"; 
 &workSheet3.name = "Contract Changes"; 
 &workSheet4.name = "New";   

 Rem - Assign values in different worksheets  
 &workSheet1.Cells(1, 1).Value = "Sample1";  
 &workSheet1.Cells(1, 2).Value = "Heading2";    
 &workSheet1.rows(1).Font.Bold = True;  
 &workSheet1.rows(1).Font.colorindex = 5;    
 &workSheet1.rows(1).Interior.colorindex = 3;   
 &workSheet1.rows(1).Font.size = 8;

 &workSheet2.Cells(1, 1).Value = "Sample2 for big text";  
 &workSheet2.Cells(1, 2).Value = "Sample2 for big text, even big";  
 &workSheet2.rows(1).Font.size = 10; 
 &workSheet2.rows(1).Font.Name = "Verdana";  
 &workSheet2.Columns.AutoFit(); 
 &workSheet3.Cells(1, 1).Value = "Sample3"; 
 &workSheet4.Cells(1, 1).Value = "Sample4"; 
 SQLExec("select PRCSOUTPUTDIR from psprcsparms where prcsinstance= 
 (select max(prcsinstance) from psprcsrqst where prcsname='AENAME')", 
 &path); 
 &sFileName = &path | "\ExportData2Excel.xls";   
 &workApp.ActiveWorkBook.SaveAs(&sFileName, 18);
 &workApp.Quit();  
 MessageBox(0, "", 0, 0, "Information has been exported to an Excel file """ |  
 &sFileName | """.");  catch Exception &exp  
throw
 CreateException(1000, 22407, "Error saving MS Excel file." | &exp);
end-try;