Using PSSpreadsheet Class in App Engine

Jim Marion brought the PSSpreadsheet class to our notice during Oracle OpenWorld conference last year. If you want a copy of his slide deck, you can find it in the below link (may not work for long, so grab it before they take it down):
https://oracle.rainfocus.com/scripts/catalog/oow16.jsp?search=marion&search.event=oracleopenworld

There is a great delivered example under the following navigation:
Reporting Tools > PS/nVision > Spread Sheet API Sample Page

This page provides a working example of how to use the PSSpreadsheet class to render a spreadsheet to the end user via an IScript (WEBLIB_SSAPI.ISCRIPT1.FieldFormula).

Peoplebooks:
Understanding The PSSpreadsheet Class

Someone asked if we could use the same PeopleCode API to create spreadsheets using an App Engine and post the file to the Report Manager? Here is an example of how we can do just that.

Sample App Engine PeopleCode:

Results:

The file should show up in Process Monitor as follows:

Notes:
- Currently, if we use the Open method of the PSSpreadsheet class to create the spreadsheet, it creates the file in the PS_HOME directory by default. There appears to be no way to pass in a parameter such as %FilePath_Relative to create the file in the process instance directory which would in turn help with posting the file to the Report Manager (Perhaps a good enhancement?). To workaround that, I wrote some code to dynamically derive the process instance directory and pass that as the first parameter (filename) for the Open method.
- The second parameter for the Open method is a boolean. We can enable Rowset processing by setting it to True. This does not seem to be documented in PeopleBooks but we can figure it out based on code in the IScript example (WEBLIB_SSAPI.ISCRIPT1.FieldFormula).

Sample Project in GitHub:
https://github.com/SasankVemana/PSSpreadsheet-in-App-Engine

Consider voting for idea to enhance PSSpreadsheet class Open method:
https://community.oracle.com/ideas/16749

PeopleCode for Reference:

/* Get Process Instance Directory */
SQLExec("SELECT PRCSOUTPUTDIR FROM PS_CDM_LIST WHERE PRCSINSTANCE = :1", SV_AE_TEST_AET.PROCESS_INSTANCE, &path);

/* Get Directory Separator */
&dirSep = "/";
If Substring(GetEnv("PS_SERVDIR"), 1, 1) <> "/" Then
   &dirSep = "\";
End-If;;

/* Set Output File Name */
&outFileName = "HelloWorld.xlsx";

/* Create Spreadsheet using PSSpreadsheet class */
Local object &ss;
&ss = CreateObject("PSSpreadsheet");
&ss.Open(&path | &dirSep | &outFileName, True);

/* Load data using Rowset */
Local Rowset &rs = CreateRowset(Record.PSMSGCATDEFN);
&rs.Fill("where MESSAGE_SET_NBR = :1", 4);
&ss.SetRowSetData(&rs);

/* Save */
&ss.save();