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.

Tile Wizard API - Part 4 - Dynamic Target Page

I previously wrote a series of posts on Tile Wizard API which allows us to control the display of the Tile Content using PeopleCode.

Part 1: Dynamic Tile Content
Part 2: Dynamic Tile Images
Part 3: Dynamic Tile Styling

These posts mainly focused on the content of the Tile as expected because the Application Class delivered by Oracle was mainly for the purpose of manipulating 'Tile Content'.

Marcin Tomczak asked the question:
"Can we make the tile point to a different external page than the tile itself?"

This made me wonder if we can dynamically change the target/destination page (internal/external) of a tile using PeopleCode? It is technically not a feature as per the design/implementation of the Tile Class. But there is an error/exception processing option that is available which might enable us to "hack" the Tile Class to do something it was not originally intended for.

Tile Class Properties - hasContent
Tile Class Properties - PreferencesURLLabel
Tile Class Properties - PreferencesURL

The hasContent property allows us to enable the error/exception mode where we can set a different image, destination URL and URL label. While it is technically not an error scenario in this case, we can still set it in error/exception mode to dynamically modify the Tile destination/target page!

Demo

- As you can see in the demo, the Tile 'Dynamic Target' is created using Tile Wizard with a destination page as 'My System Profile'. This destination has no significance because the 'Disable Main Hotspot' is set to 'Yes'.
- Once the role 'SV_ROLE_1' is added to PS user, we can see the new image and 'My Preferences' URL is enabled for the 'Dynamic Target' tile via the Tile App Class.
- When the role is switched to 'SV_ROLE_2', we can see the 'Change My Password' URL is enabled for the 'Dynamic Target' tile via the Tile App Class.
- When the role is switched to 'SV_ROLE_3', we can see the 'Google Search' external URL is enabled for the 'Dynamic Target' tile via the Tile App Class.

 Tile Wizard Settings

App Package PeopleCode

Environment Details
- CS 9.2 PUM Image 4
- PeopleTools 8.55.12

Fluid UI - Tile Wizard API - Part 3 - Dynamic Tile Style

This is a continuation of the Fluid UI - Tile Wizard API series: Part 1 and Part 2. In this part, we will explore the Tile Wizard API further to dynamically style tiles and more importantly find an option to target specific tiles to apply custom style classes.

In the past (refer Fluid UI - Styling Tiles - Part 1 and Part 2), I wrote about the challenges with the way the id attribute is auto-generated in the tile html (div elements).

E.g.: In the screenshot below, the div element for the 'Financial Account' Tile in the Student Homepage has an id attribute value of win0divPTNUI_LAND_REC_GROUPLET$2.

The number 2 in the id attribute value denotes that it is the third (starting with 0) tile on the Homepage. This auto numbered id value is still unique enough to target the elements under this div and apply custom styles using css selectors (e.g.: make the 'Financial Account' tile title appear in red color, etc.). But this assumes that the end users or even the administrators will not change the order of the tiles either via Personalizations or via administrative pages (structure and content). If such events occur, then it would be a bit complex to apply a style class targeted to a specific tile. This was the main reason I created an Idea on My Oracle Support to request this ability as a PeopleTools enhancement.

With this background, I got really excited when I saw that we could potentially use the Tile Wizard API for applying targeted styles to a tile using the TileID and the TileSpecificStyleSheet properties of the Tile Wizard Class.

Environment Details:
- CS 9.2
- PUM Image 4
- PeopleTools 8.55.12

TileID Property Quirk:

But first, I found some quirky behavior with the TileID property. When I tried to set the TileID for the custom tile 'My Team' which I created in Part 2, it did not really update the id attribute on any of the elements that are part of the Tile. Instead the value was added to the class of the Tile Content area. This appears to be a bug from my understanding. If you are finding different results then please leave a comment with your experience!

PeopleCode:

We can see that the id attribute of the tile - where we would have expected the update - is still unchanged, whereas strangely the value is added to the class of the Tile Content area. Bug?

Using the TileSpecificStyleSheet Property:

While it is evident with the name of this property that it is specific to a tile, it does not necessarily mean that it will only be applied to the target tile. Basically, the style sheet is added to the response which may or may not be specific to the target tile depending on the style class selectors in the style sheet.

Let us say we added a custom style sheet to the 'My Team' tile using the TileSpecificStyleSheet property.

Scenario 1:

Let us add the following style to the freeform style sheet to set a different color to the Tile Title.

SV_TILE_CSS Style Sheet:


Results:

We can see that all the tiles on the homepage are affected by this style class. This is because the style class does not have any targeted css selectors and therefore will be applied to all tiles on the homepage.

Scenario 2:

Let us add another style - this time specifically targeting the 'My Team' tile using css selectors - to change the color of the Live Data text.

SV_TILE_CSS Style Sheet:


Results:

Overall, while there are workarounds, it would be great if the TileID property works as expected (perhaps in the future releases of PeopleTools?) which would allow us to easily write custom style classes to accurately and effectively target specific tiles.

Fluid UI - Tile Wizard API - Part 2 - Dynamic Tile Images

This post is a continuation of my previous article on the Tile Wizard API, where I described how we can use the Tile Wizard API to dynamically update the Tile Content.

In this post, we will see how we can use the Tile Wizard API for another similar and more common use case to display a dynamic image based on certain conditions.

For the purposes of this post, let us assume that we want to create a Tile called 'My Team' that displays a different image and live data based on the user's security role assignment.

Demo:

Tile Wizard Setup:

NavBar > PeopleTools > Portal > Tile Wizard > Create Tile

App Package PeopleCode Implementation:

PeopleCode for Reference:

import PTGP_APPCLASS_TILE:Tiles:Tile;

class TILE_2 extends PTGP_APPCLASS_TILE:Tiles:Tile
   method TILE_2();
   method getTileLiveData();
end-class;

method TILE_2
   %Super = create PTGP_APPCLASS_TILE:Tiles:Tile();
end-method;

method getTileLiveData
   /+ Extends/implements PTGP_APPCLASS_TILE:Tiles:Tile.getTileLiveData +/
  
   If IsUserInRole("SV_CSK") Then
      rem %This.setTileImageRef("SV_CSK"); /* Method equivalent */
      %This.ImageReferenceField.Value = Image.SV_CSK;
      /* Live Data */
      %This.TileLiveData_1 = "Chennai Super Kings";
   End-If;
  
   If IsUserInRole("SV_RCB") Then
      rem %This.setTileImageRef("SV_RCB"); /* Method equivalent */
      %This.ImageReferenceField.Value = Image.SV_RCB;
      /* Live Data */
      %This.TileLiveData_1 = "Royal Challengers Bangalore";
   End-If;
  
end-method;

Fluid UI - Tile Wizard API for Dynamic Tile Content

PeopleTools 8.55 introduced the Tile Wizard App Classes which enable us to use the API to dynamically modify Tile Content.

PeopleBooks: Tile Wizard Classes

There are three main areas in the Tile where we can add dynamic content - Tile Content, Live Data and Badge.

Here is an example of how we can use the Tile Wizard Classes to dynamically update the content of a Tile as shown in the example below.

Step 1: Implement a custom App Class that extends PTGP_APPCLASS_TILE:Tiles:Tile

Notes:
- Make sure the App Package contains a Sub Package. Otherwise, we might run into errors as stated in this HEUG Technical Forum discussion.
- We can use the %This.SetTileContentAs... methods - %This.SetTileContentAsTwoKPIs() in this case - to override the "Tile Content Type" configured using the Tile Wizard (which we will perform in step 2).
- We can also use the various Tile Class Properties to set the Tile Content (e.g.: KPI values and labels), Live Data (values, trend image and metrics formatting) and Badge Data.

Step 2: Create Tile using Tile Wizard

NavBar > PeopleTools > Portal > Tile Wizard > Create Tile

Notes:
- We can see that the App Class created in step 1 is referenced as the Data Source.
- Also, the "Tile Content Type" is set to Image in the Wizard configuration level (although it will be overwritten using PeopleCode).
- Similarly, the "Tile Badge" is set to "No" in the Wizard configuration but it can be overwritten using PeopleCode.

Note:
- For the purposes of this example, I set the Target Page to "PS Unit". This can be set to anything as per our requirement.

Note:
- The Tile image is set to PTFP_DEFAULT_CLOUD as a dummy. It will not be used in this case, because we will be overriding the "Tile Content Type" via PeopleCode.

Step 3: Add Tile to a Fluid Homepage

Fluid Home > Action Menu > Personalize Homepage

Results:

Environment Details:

- CS 9.2
- PUM Image 4
- PeopleTools 8.55.12

Related Posts:
Part 2
Part 3

PeopleCode for Reference:

import PTGP_APPCLASS_TILE:Tiles:Tile;

class TILE_1 extends PTGP_APPCLASS_TILE:Tiles:Tile
   method TILE_1();
   method getTileLiveData();
end-class;

method TILE_1
   %Super = create PTGP_APPCLASS_TILE:Tiles:Tile();
end-method;

method getTileLiveData
   /+ Extends/implements PTGP_APPCLASS_TILE:Tiles:Tile.getTileLiveData +/
  
   %This.SetTileContentAsTwoKPIs();
   /* 1 KPI tile content */
   %This.TileKPI_1 = %This.getAmountFormattedValue(1.23456789, "USD");
   %This.TileKPI_1_Label = "KPI1_1 Label";
   %This.TileKPI_2 = "KPI_2";
   %This.TileKPI_2_Label = "KPI_2 Label";
  
   /* Live Data */
   %This.TileLiveData_1 = "LD_1";
   %This.TileLiveData_2 = "LD_2";
   %This.TileLiveData_3 = "LD_3";
  
   /* A trend arrow is an optional element of live data. */
   %This.hasLivedataTrendImage = True;
   %This.TrendImage = %This.k_strTrendDownImage;
  
   /* Metrics Formatting */
   %This.isTileLiveData_1_Metrics = True;
   rem %This.isTileLiveData_2_Metrics = True;
   rem %This.isTileLiveData_3_Metrics = True;
  
   /* Badge */
   %This.hasLiveDataCount = True;
   %This.TileTransCount = 7;
  
end-method;

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