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

8 comments:

  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

    Thanks,
    Saam

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

      Delete
  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,
    Joerg

    ReplyDelete
    Replies
    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.

      Delete
    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");
      &ss.open("/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 */
      &ss.SaveAs(&s_OutFileName);

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

      Thanks and regards
      Joerg

      Delete
    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?

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

    Thanks,
    Naman Jain


    ReplyDelete
    Replies
    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!

      Delete