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

52 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
    4. Hi Joerg,
      Were you able to open excel and copy data into new excel file using PSSpreadsheet?

      Delete
    5. Sorry for the late reply to this thread but for the benefit of others. The issue here appears to be with how you are opening the spreadsheet. We should only set the second parameter to True if you are using Rowset processing. Otherwise, we must set it to False as shown below.

      &ss.open("/app/pshcm/855/appserv/prcs/DEV/Template1.xlsx", false);

      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
  4. Hi Sasank - Do you know of any function we use to delete sheet within excel file using PeopleCode?

    Thanks

    ReplyDelete
    Replies
    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:
      http://jjmpsj.blogspot.com/2016/07/dynamic-java-in-peoplecode.html

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

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

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

      Delete
    4. @Durai - This may be old and you might know this already but it appears to be a known/potential issue with 8.55.

      Refer My Oracle Support:
      Bug 29384267 : ERRORRETURN-> 255 - FILE IS ALREADY IN OPEN STATE! WITH PSSPREADSHEET

      https://support.oracle.com/epmos/faces/BugDisplay?id=29384267

      Delete
    5. Hi Sasank,
      I am getting the same error with PT 8.56.05. Do you know if there is a workaround? Thanks!

      Delete
    6. Please refer the Bug and Doc Id in my previous comment (March 21, 2019 at 12:47 PM).

      I am not aware of a workaround.

      Delete
  5. Hi Sasank -

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

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

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

    ReplyDelete
    Replies
    1. I don't think you need to install MS Excel for this api to work. I ran this process on PSUNX so I doubt it is dependent on MS Excel installation.

      P.S.: Sorry for the delay in responding to your comment. I realized that between May-August, 2018, blogger comment notifications were not working. I am reviewing all the comments from that period and responding as soon as I can.

      Delete
  7. Sasank, I want to use SetCellFormula. But not able to formulate 3rd parameter properly. i want 3rd column will be just sum of 1st column and second column. Could you please help me ?

    ReplyDelete
    Replies
    1. Hi Chandrika - I am also having difficulty using that method. When I invoke it, the excel spreadsheet does not even get generated. Are you experiencing the same?

      You may want to raise a Oracle Support Request and they should be able to guide you on usage and potentially confirm if this is a bug.

      Alternatively, can you not do that calculation in PeopleCode instead of moving the calculation out to excel?

      Delete
  8. Gidday Sasank
    I notice that in your file screenshot the field LAST_UPDATE_DTTM does not look to have a date value in it. I have encountered issues whereby the PSPSPREADSHEET class does not copy all rowset data to the file if the underlying record has a date field. The file is created but the rowset data stops copying when it encounters the first date field.
    Did you encounter the same issue?
    PeopleTools 8.56.10
    Thanks

    ReplyDelete
    Replies
    1. @Pat - This is interesting. I have not run into this issue. I will let you know if I can replicate this and/or if I have any ideas.

      Unless there is a formatting issue with the date/time field, I would assume this may be a bug. I would encourage you to log a case with Oracle Support to see if they have any ideas.

      Delete
  9. Hi, I had the same issue with a date column. I remembered from my many years in a MS shop that even MS processes had an issue with exporting date data type columns to excel. You should convert and format your date field to a character data type and then export. Once I had done that I had all rows showing in the excel sheet.

    ReplyDelete
    Replies
    1. Great! That makes total sense. Thanks for sharing your experience.

      Delete
  10. Hi Following your example code I have all my data showing in the excel sheet. I wanted to change the column headings and tried using the .SetCellString Method but what I'm experiencing is that change seems to be getting set prior to the .SetRowSetData no matter what order the code is in and I cannot modify the headings which are the table column headings. Any thoughts or ideas how to correct this?

    ReplyDelete
    Replies
    1. If you are using rowset processing, then I believe you cannot use the non-rowset based set methods. This is probably the reason why the SetCellString has no effect.

      I am writing a follow post which covers this topic. Stay tuned!

      Delete
  11. Hi Sasank,
    I used the same code and tried to generate multiple sheets in same excel file. But for the first sheet I am not able to change the sheet name. For all sheets I used CreateSheet and SetActiveSheetName functions and that works fine. But for the first sheet the sheet name remains "1" even if I used SetActiveSheetName(). Any idea's on how can we change first sheet name? Thanks in advance

    ReplyDelete
    Replies
    1. It appears to be a known bug/issue? Hopefully there will be a fix for this soon.

      E-PC: PeopleCode API PSSpreadSheet SetActiveSheetName method is not working (Doc ID 2511065.1)

      Delete
  12. Sasank
    Could you please confirm your example and the use of the Open method with the property enable_rowsetprocessing = True still works after moving to 8.57?
    Thanks Pat

    ReplyDelete
    Replies
    1. Hi Pat - Firstly, I apologize for the delay in this response and I hope it is still useful.

      To answer your question. Yes and No.

      Yes - The code with enable_rowsetprocessing=true works in 8.57 provided there is no date or datetime field in the rowset.

      No - The enable_rowsetprocessing functionality appears to break when we encounter a date or datetime field in the rowset. This was not the case when I originally wrote this post (as you can see in my example). It appears that something was introduced in the later PeopleTools patches/release that has broken this behavior.

      I wrote a follow up post with an example of how to make this work in 8.57.
      Review: https://pe0ples0ft.blogspot.com/2019/03/psspreadsheet-class-part-2.html

      Hope this helps!

      Delete
  13. Howdy just a quick update
    We found after adjusting our Kernel parameters to the be the Oracle recommended values the issue went away and the Open method with the property enable_rowsetprocessing = True works - after 8.57.

    ReplyDelete
    Replies
    1. Hi Pat - Thank you for taking the time to provide an update. That is great news!

      Could you please share the Oracle recommended Kernel parameter values? Do you have a MOS Doc Id that references this issue? It will be good to know for future!

      Delete
  14. Hi Sasank,

    Am able to create sheet 1 and brought data. Also able to create sheet 2 and rename it as well.
    But unable to print data in sheet 2. Tried making sheet 2 as active but got he below error.

    "Process Request shows status of 'INITIATED' or 'PROCESSING' but no longer running "

    But with single sheet, my AE process going to success with correct data as well.

    Could you pls provide your suggestion on this. If possible could you pls provide the code for multiple sheets.

    Thanks in Advance.

    Prabu

    ReplyDelete
  15. Does it work on People Tools 8.52 Release?

    ReplyDelete
    Replies
    1. I don't think so. I believe this class was introduced in 8.55 or 8.54.

      Delete
  16. Hello Sasank,
    I have a basic question --reading Excel spreadsheet from AE peopleocde, not using File Layout. Do you have a pseudo code( or template) that you could share?

    ReplyDelete
  17. Hello Sasank, Is there a way to copy the excel file generated to a different folder? I used your code, it works perfect. Our requirement is to generate the file to a ftp folder.

    Thanks in advance.
    Raj

    ReplyDelete
    Replies
    1. &ss.Open(&path | &dirSep | &outFileName, True);

      The path above determines where the file will be created. So, as long as the folder is accessible from the server where the app engine is running, you can adjust the path+filename as per your needs.

      Delete
  18. Hi Sasank, Is there a way to copy the excel file generated on Process Monitor to a different ftp folder? Please advise.

    Thank you,
    Raj

    ReplyDelete
  19. Hi All,

    Please can you help me to use SetCellHeaderStyle I'm able to set all the values but bot setting the color of the header.

    ReplyDelete
    Replies

    1. Local RNA_COMMON:UTIL:File &oRNACommonObj = create RNA_COMMON:UTIL:File();
      &FilePath = &oRNACommonObj.getOutputPath();

      &outFileName = "Perm_Placement_Activity_Report_" | DateTimeToLocalizedString(%Date, &date_mask) | ".xlsx";

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

      &ss.SetActiveSheetName("Last Week");

      rem SetCellHeaderStyle(rownum, colnum, redvalue, greenvalue, bluevalue, boldweight, color, fontheight, fontname);
      &ss.SetCellString(1, 1, "Perm Placement Activity Report");
      &ss.SetCellString(1, 2, "Filled & Closed without Perm Plc");
      &ss.SetCellHeaderStyle(1, 1, 255, 0, 255, 20, 50, 12, "Calibri");

      Local number &idx = 3;
      Local number &idx2 = 2;

      &ss.SetCellString(&idx2, 1, "Region");
      &ss.SetCellString(&idx2, 2, "District");
      &ss.SetCellString(&idx2, 3, "Branch Name");

      Delete
    2. @RajeshwarRaj - You are right, this is not working for me either. I think it did earlier when I wrote these posts. But there is a bug that is targeted to get fixed in 8.59.

      BUG:31243736 - PSSPREADSHEET METHOD SETCELLHEADERSTYLE NOT WORKING PROPERLY
      E-PC: RGB Parameters for SetCellHeaderStyle Method are not Changing the Cell Background Color (Doc ID 2680036.1)

      Delete
  20. Hi Sasank,

    My requirement is to read an excel file with multiple worksheets.
    Each worsheet data must be loaded into its corresponding reocrd.
    E.g. 5 sheet into 5 records.
    Can you please provide me some code for it.

    Regards.

    ReplyDelete
  21. Hi Sasank,

    I want to change the name of Sheet "1" to another name, what should I do?

    Thank you,
    Arm

    ReplyDelete
    Replies
    1. You can try SetActiveSheetName method.

      https://docs.oracle.com/cd/F30998_01/pt858pbr2/eng/pt/tpcr/langref_PSSpreadsheetClassMethods.html#ubfd2ba70-ad31-4004-803c-ae210e4d3f11

      Delete
  22. Hi Sasank, We are on Peopletools 8.58.18 version. PS Spreadsheet Class is generating Excel file but we cannot access/view the data. Excel file is blank. Prior to upgrade, with tools 8.58.11 it worked fine. Any help is much appreciated. Thanks

    ReplyDelete
  23. Just as an FYI to all, note the issue where only one row is returned when the rowset contains a date field. Oracle is aware and will be fixed in a future release: https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=446009194140093&id=2612464.1&displayIndex=5&_afrWindowMode=0&_adf.ctrl-state=ylj9o14ca_201

    ReplyDelete
  24. Hi Sasank,

    When you see the output of the sample code you posted, does it give you more than a row of data or just a single row?

    Thanks!

    ReplyDelete