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.