Friday, 2 October 2015

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);  
 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);  
 /* 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  
 &res = PutAttachment("record://PSFILE_ATTDET", &fileName, &putFileName, "PS_SERVDIR");  
 &res = ViewAttachment("record://PSFILE_ATTDET", &fileName, &fileName);  

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


Kevin Weaver said...

Very interesting post! I did not know that the Query API would accept any rowset, very cool. I had written my own rowset to excel function using David Vandiver's SQR2XLS iScript function as my foundation. I also incorporated my own stlyes that I associate to the column by field type. So I can format the currency and dates. Here is my blog post on how to stream the excel in binary.

We are using this in combination with my own Rowset to cells function that can be found on my blog as well in Related Content and Actions to deliver reports directly to the end user without customization.

Thanks for sharing!

Graham said...

@Kevin. Thanks. I like what you're doing with the Query API and related content framework. Thanks for sharing.

I tried using FormatBinaryResultsString method in my example but the Excel document just contains the word "False". Switching the file write to use WriteRaw method resulted in the application server crashing. Investigating......

Thanks again Kevin.

Jim Marion said...

I will be sharing another alternative during my OpenWorld session: Using Apache POI, which is delivered in 8.54. POI is a bit complicated for creating simple spreadsheets, but highly valuable for complex worksheet creation (calculations, formatting, etc). My primary motivation for using POI is to read and process spreadsheets.