Friday, January 27, 2017

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):

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).

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:


The file should show up in Process Monitor as follows:

- 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:

Consider voting for idea to enhance PSSpreadsheet class Open method:

PeopleCode for Reference:

/* Get Process Instance Directory */

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

/* 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);

/* Save */


  1. Hi Sasank,

    I am getting the following error with the above code:

    PSPAL::Abort: Unexpected signal received
    PSPAL::Abort: An exception occurred while generating the process state report


    1. You may want to set a PeopleCode/SQL trace and identify where the code is failing in your case.

  2. Hi Sasank,

    I modified your code to open an existing xlsx and save it with another file name.
    I intend to use the prev existing xlsx as a template, populate it with some data and save with some other file name.
    Now I tried different data set methods in my AE to populate the opened file and none of it writes any data into my xlsx. It is saved as opened, just with another name.

    Do you have a hint what can be the reason for this?

    Thanks and regards,

    1. Hi Joerg - I am not sure what the problem is here. If you share you peoplecode may be we could help provide more direction.

    2. Hi Sasank,

      thanks for your reply.
      My code was very basic:
      /* Set Output File Name */
      Local string &s_OutFileName;

      &s_OutFileName = "HelloWorld.xlsx";

      /* Create Spreadsheet using PSSpreadsheet class */
      Local object &ss;
      &ss = CreateObject("PSSpreadsheet");
      &"/app/pshcm/855/appserv/prcs/DEV/Template1.xlsx", True);

      /*template test */
      &rownum2 = 2;
      &rownum3 = 3;
      &rownum4 = 4;
      &colnum1 = 1;
      &data1 = "Sample";
      &data2 = "Data";
      &data3 = "to be saved in template";
      &ss.SetCellString(&rownum2, &colnum1, &data1);
      &ss.SetCellString(&rownum3, &colnum1, &data2);
      &ss.SetCellString(&rownum4, &colnum1, &data3);

      /* Save */

      Outcome always was template file without data entries (in this code by SetCellString). Just as predefined template.

      Thanks and regards

    3. Hi Joerg - Sorry for the delay in responding.

      How about first doing the SaveAs, then opening the new file and save that as in my code?

      I wonder the SaveAs is taking the original copy and saving it and does not know what you populated in &ss because &ss is not saved?