PSSpreadsheet Class Part 2 | Documentation Update, Date Format (converting to epoch) and more

This is a continuation of 'Using PSSpreadsheet Class in App Engine' where I demonstrated how we can use this new API to generate Excel spreadsheets using PeopleCode. While there were and still are a lot of gaps in the documentation of this API, it provides an easier method to create spreadsheets from PeopleCode.

Documentation Update

Previously, in the notes section, I explained the significance of the second parameter (rowset processing) in the Open method which was not documented in PeopleBooks at that point in time. This has been updated.

PSSpreadSheet - Open Method PeopleBook

This is also documented in the following My Oracle Support - Doc Id which clarifies the RangeApplyStyle method as well.
E-PC: There Is Missing Documentation In PeopleBooks For PeopleCode PSSpreadsheet Class - Methods Open And RangeApplyStyle (Doc ID 2357922.1)

Other useful documents on My Oracle Support are listed below.
E-PC: PSSpreadsheet SetColumnWidth Method Is Not Working (Doc ID 2354586.1)
E-NV: What is RangeApplyStyle() in PSSpreadsheet API ? (Doc ID 2288870.1)
E-PC: PeopleCode API PSSpreadSheet SetActiveSheetName method is not working (Doc ID 2511065.1)
E-PC/NV - Method RangeApplyStyle Of Class PSSpreadsheet Is Not Available On PT8.55 And PT8.56 (Doc ID 2516382.1)
Bug 29384267 : ERRORRETURN-> 255 - FILE IS ALREADY IN OPEN STATE! WITH PSSPREADSHEET

Needless to say, that while some of the reported issues are fixed/clarified, there are still improvements to be made which I hope will follow suit in subsequent releases/patches of PeopleTools.

Date Format for SetCellDate

After reviewing a comment thread on my previous post, I was curious to find out more about the SetCellDate method.

Comment thread
PSSpreadsheet - SetCellDate PeopleBook

If we see the example provided in PeopleBooks, a value (string) of 120223323 produces a cell (date format) with the following value.
1/2/1970  9:23:43 AM

So what is this mystery number/value 120223323 that magically converts to a date of 1/2/1970  9:23:43 AM? After many days of spinning my wheels, I finally found (thanks to Oracle Support) that this is actually the date in epoch format (date/time in milliseconds since epoch). Wow! Why? 😂

One reason I can think of is that behind the scenes PSSpreadSheet API is implemented in Java (Apache POI) and therefore may require this format to pass into appropriate java methods? My guess is as good as yours. In any case, there is potential for this to be improved where this date calculation/conversion can be masked from the API user (perhaps with an internal conversion) for the date parameter in the SetCellDate method.

Anyway, now how do we calculate the date in epoch timestamp format if we ever want to use this method successfully under the current circumstances? There are some online converters which we can use to get ourselves familiarized with the calculation.

https://www.epochconverter.com/
https://www.freeformatter.com/epoch-timestamp-to-date-converter.html

Note: Keep in mind that the SetCellDate method expects the epoch timestamp to be in milliseconds (some converts provide the timestamp value in seconds which might throw us off when used in PeopleCode).

That said, how do we do this calculation/conversion in PeopleCode? I have not found a delivered function and/or API that helps with this calculation/conversion. So, I resorted to using javascript and executing it on the server side using a very handy technique demonstrated by Jim Marion on his blog here.

JavaScript to convert date to epoch timestamp format (milliseconds)

Here is a simple JavaScript that converts a human readable date to epoch timestamp format. You can follow this link to test the code directly in jsFiddle.

https://jsfiddle.net/SasankVemana/bgLk8sez/



Sample PeopleCode Snippet with SetCellDate example

JavaScript in Message Catalog (29999, 1)

Message Catalog (29000, 1) for reference

GitHub Project Update

In my previous post, I shared a sample App Engine program that will simply generate Excel spreadsheet using data from a rowset. It was brought to my attention that if a column contains a date/datetime field, then the spreadsheet is not generated as expected. This is definitely a limiting factor when it comes to using the rowset processing option in the PSSpreadSheet class.

I created another GitHub project which generates a spreadsheet via an App Engine which uses non-rowset processing option. If you are looking for examples, the App Designer project can be downloaded and used evaluation purposes.

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

This sample App Engine will produce a spreadsheet as follows.

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