Rowset to Excel - PeopleCode Friday #2

We all know that PeopleSoft grids can be exported to Excel using the button on the grid status bar.











But what if the data you want to export to Excel is not in a grid?  What if you need to programatically export data using PeopleCode.

I'm sure there are many ways to do this but I recently came across this useful technique for exporting RowSet record data to Excel.   Here's a working code sample.

The magic is in the use of the FormatResultString method from the Query class.  This accepts a rowset as input and will produce a string of HTML which when opened in Excel does just nicely.

HTML produced by the FormatResultString method

HTML opened in Excel

I've repeated the code below if you want to copy it.

 Local string &strExcel, &fileName;  
 Local File &exportFile;  
 /*Create a rowset. This can be a standalone or buffer rowset */  
 Local Rowset &rs = CreateRowset(Record.PSPRCSRQST);  
 &rs.Fill();  
 Local object &qryObject = %Session.GetQuery();  
 /* Create a file on the application server */  
 &fileName = "Output_" | %UserId | %Date | %Time | ".XLS";  
 &exportFile = GetFile(&fileName, "W");  
 If &exportFile.IsOpen Then  
   /*Use the FormatResultString method of the Query object to generate Excel string */  
   &strExcel = &qryObject.FormatResultString(&rs, %Query_XLS, 1, &rs.RowCount);  
   &exportFile.WriteLine(&strExcel);  
   &exportFile.Close();  
 End-If;  
 /* Add attachment and view */  
 &putFileName = "files/" | &fileName;  
 /* Pickup the app server file, attach and view */  
 /* CommitWork is required as ViewAttachment is a thinktime function.  
   Using an iScript to view the attachment is another option here.   
   See Jim Marion here http://jjmpsj.blogspot.co.uk/2009/01/exporting-attachments-part-2.html  
 */  
 &res = PutAttachment("record://PSFILE_ATTDET", &fileName, &putFileName, "PS_SERVDIR");  
 CommitWork();  
 &res = ViewAttachment("record://PSFILE_ATTDET", &fileName, &fileName);  

Please feel free to discuss other methods you know of in the comments below.