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?

    4. Hi Joerg,
      Were you able to open excel and copy data into new excel file using PSSpreadsheet?

  3. Hi Sasank,

    I am using AE process to create excel workbook on a windows directory but getting an error as "Could not create/open workbook for (Open)".

    The directory permissions are set correctly for PeopleSoft process scheduler to create new files. Appreciate your thoughts on this.

    Naman Jain

    1. Hi Naman - It may be related to either the construction of the filepath/filename and/or directory permissions. Can you test permissions by creating a simple file on that directory using File Class?

      If you can share you code somewhere (here or My Oracle Support Community), we may be able to assist further. Thanks!

  4. Hi Sasank - Do you know of any function we use to delete sheet within excel file using PeopleCode?


    1. Hi Durai - I am not aware of one. I don't see any related peoplecode functions that can delete a sheet within an excel file.

      You could use Java with PeopleCode as demonstrated by Jim Marion in this post:

      The Apache POI library might provide a lot more flexibility than PeopleCode to manipulate excel.

    2. Thanks Sasank.
      Do you know why it keeps on failing with 'File is already in open state!' , when we use PSSpreadSheet Open

    3. @Durai - I am not sure. I have not run into that issue.

  5. Hi Sasank -

    Using ("PSSpreadsheet"), can we read the excel file data?? I couldn't find the read functions. say, GetCellString

    1. I don't know if we can use this class for reading an excel file. You are right, there are no read functions.

  6. Hi Sasank,
    Do we need to install MS Excel for this api to work ? like the requirement we had if we had to use createobject for Excel in previous tools versions?