Thursday, June 28, 2018

Using PeopleCode to Read (and process) Binary Excel Files

At HIUG Interact last week, a member asked one of my favorite questions:

"Does anyone know how to read binary Microsoft Excel files from PeopleSoft?"

Nearly 15 years ago my AP manager asked me the same question, but phrased it a little differently:

"We receive invoices as Excel spreadsheets. Can you convert them into AP vouchers in PeopleSoft?"

Of course my answer was "YES!" How? Well... that was the challenge. I started down the typical CSV/FileLayout path, but that seems to be a temporary band aid, and challenging for the best users. I wanted to read real binary Excel files directly through the Process Scheduler, or basically, with PeopleCode. But here is the reality: PeopleCode is really good with data and text manipulation, but stops short of binary operations. Using PeopleCode's Java interface, however, anything is possible. After a little research, I stumbled upon Apache POI, a Java library that can read and write binary Excel files. With a little extra Java code to interface between PeopleCode and POI's Java classes, I had a solution. Keep in mind this was nearly 15 years ago. PeopleSoft and Java were both a little different back then and today's solution is slightly simpler. Here is a summary of PeopleSoft and Java changes that simplify this solution:

  • As of PeopleTools 8.54, PeopleSoft now includes POI in the App and Process Scheduler server Java class path. This means I no longer have to manage POI as a custom Java library.
  • The standard JRE added support for script engines and included the JavaScript script engine with every deployment. This means I no longer have to write custom Java to interface between POI and PeopleCode, but can leverage the dynamic nature of JavaScript.

How does a solution like this work? The ultimate goal is to process spreadsheet rows through a Component Interface. First we need to get data rows into a format we can process. Each language and operating environment has its strengths:

  • PeopleCode can handle simple Java method invocations,
  • JavaScript can handle complex Java method invocation without compilation,
  • Java is really good at working with binary files, and
  • PeopleCode and Component Interfaces play nicely together.

My preference is to capitalize on these strengths. With this in mind, I put together the following flow:

  1. Use PeopleCode to create an instance of a JavaScript script interpeter,
  2. Use JavaScript to invoke POI and iterate over spreadsheet rows, inserting row data into a temporary table, and
  3. Use PeopleCode to process those rows through a component interface.

The code for this solution is in two parts: JavaScript and PeopleCode. Here is the JavaScript:

Next hurdle: where do we store JavaScript definitions so we can process them with PeopleCode? Normally we place JavaScript in HTML definitions. This works great for online JavaScript as we can use GetHTMLText to access our script content. App Engines, however, are not allowed to use that function. An alternative is to use Message Catalog entries for scripts. The following PeopleCode listing uses an HTML definition, but accesses the JavaScript content directly from the HTML definition Metadata table:

To summarize this PeopleCode listing, it first creates a JavaScript script engine manager, it then evaluates the above JavaScript, and finishes by processing rows through a CI (the CI part identified as a TODO segment).

This example is fully encapsulated in as few technologies as possible: PeopleCode and JavaScript, with a little SQL to fetch the JavaScript. The code will work online as well as from an App Engine. If this were in an App Engine, however, I would likely replace the JavaScript GUID section with the AE's PROCESS_INSTANCE. Likewise, I would probably use an App Engine Do-Select instead of a PeopleCode SQL cursor.

Did you see something on this blog that interests you? Are you ready to take your PeopleTools skills to the next level? We offer a full line of PeopleTools training courses. Learn more at jsmpros.com.

14 comments:

Tom Williams Jr. said...

This is great Jim.
Thanks again and it was nice to see you again at HIUG.

Tom

Gary F said...

Java with its latest version is deprecating the Nashorn scripting engine, so you'd need to come up with some other solution, right?

Ricky said...

Hi Jim, great post as usual! Along the file train of thought, trying to find how to load pgp library into PET for a Linux env in order to build Chain and Profile to encrypt/decrypt. Try to get off the shell script road. We mostly used it because there was an easier for native way. But isnt there? It seems so hard to get this to work. It seems there is a pspetpgp.dll PGP Library file but that is only for Windows. So it seems I need Linux PGP Library file and a glue code for PS to recognize the algorithms?? I am refusing the shell script route, because once the setup is complete it just seems magical lol

Example:
Local object &decrypt = CreateObject("Crypt");
&decrypt.Open("PB_PGP_DECRYPT");
&decrypt.UpdateData("66BSFV3MKcwYq2tbfiIVtQ==");
MessageBox(0, "", 0, 0, "Decrypted: " | &decrypt.Result);

Jim Marion said...

@Gary, good thing to keep in mind. Even though Nashorn is deprecated, it is still in Java 11 and may be in future releases. It just isn't actively developed... at least at this time. It is true, for future proofing, we should avoid deprecated features, but I think there is a lot more to consider than Oracle no longer maintaining Nashorn. Prior to Nashorn, we had Rhino. JavaScript has been in Java for a long time (for no good reason, of course). The ultimate future of a JavaScript engine delivered with Java is not set yet. Even if Oracle drops all JavaScript engines from the JDK, we can still use a JavaScript ScriptEngine because the ScriptEngineManager will still be in the API. We just might have to deploy the script engine ourselves. In fact, you can use several different scripting engines with PeopleSoft today, it is just that JavaScript is the only one delivered. Another consideration is that few customers are on the latest version of PeopleTools and PeopleTools doesn't necessarily run the latest version of Java.

And, if Oracle ever drops JavaScript from the JDK, we can still use Apache POI either from our own custom Java in PeopleCode (documented in my PeopleTools book) or through PeopleCode directly using CreateJavaObject.

trustno1 said...
This comment has been removed by a blog administrator.
Bob Godwin said...

Hi Jim, I'm trying to processes binary attachments in a MIME format from a 3rd party through integration broker. I can't seem to get the messages to come through. They are being rejected with a "500 internal server error" message. I've enabled MTOM messages in the gateway.properties file. I've enable MTOM messages for the node involved. Here's the first part of the message that's arriving.

------=Mime1561659979443.-1167993606214201155
Content-Type: application/xop+xml;
charset="UTF-8";
type="text/xml"
Content-Transfer-Encoding: 8bit
Content-ID: <1561659979443.-1167993606214201155@sciquest.com>





It won't work. If I delete a couple of lines (the three "content-" lines) like so..

------=Mime1561659979443.-1167993606214201155






The message is accepted and works fine. It seems like a configuration issue but I can't find it. Any Ideas?

Andy said...

Jim,
I've used this post a few times. I'm using it now to write a file, where last time I used it to read.
Interesting question from psadmin.io other day... couldn't I just use PeopleCode directly with Java to use Apache POI?

So, I messed around with it. It seems to me like I'd need a lot of references to Java objects, and do extra stuff for overloaded methods...

For example, just from the block below,
`Local JavaObject &Workbook = CreateJavaObject("org.apache.poi.xssf.usermodel.XSSFWorkbook");`
`&sheet = &Workbook.createSheet("Countries");`

Does the &sheet variable have to be declared as a JavaObject then? And every time I create a new row, or new cell, etc. I'd need to declare it as a JavaObject as well?

And since createSheet is overloaded, I'd need another JavaObject for the arg type, and then get a reference to the method with those arg types to invoke it.

It seems like it'd be cleaner with JS using the Java API. But I'm not really sure, I only messed around with it for an hour. What are your thoughts?

Jim Marion said...

I completely agree with you. Yes, absolutely it could be done in PeopleCode w/o JavaScript, but it would be the ugliest PeopleCode reflection ever. I initially started with PeopleCode, but quickly shifted to custom Java class files as a wrapper to avoid reflection. When the JRE added a JavaScript script engine, I realized I could use that as "glue" to avoid reflection.

Raistlin Majere said...

Jim, I was looking to use this method to read excel files from an online page and it worked for string fields, but having issues with Date and Numeric fields. Any direction on how I would read date and numeric fields using Javascript method. Our App Server is on Unix, so I will be moving the file that the user supplies to the temp directory on Unix before accessing it. Thx

Surinder

Jim Marion said...

@Raistlin, I have done the same without issue. What issues are you experiencing?

Satya said...

Hello Jim,
I am trying to use some variables which are calculated outside of the Javascript. Can I use those variables in the javascript in part of the SQLExec? If yes, How can I use those variables. Please advise.
Satya

Jim Marion said...

@Satya, great question. Notice the line in the PeopleCode that says:

&engine.put("fileName", "c:/temp/roles.xlsx");

That creates a JavaScript variable named fileName from a value in PeopleCode. You would do this for each value you want to pass from PeopleCode to JavaScript.

Myron Wintonyk said...

This is an old post, but also a very useful one. This is the first time I've touched JavaScript, so I really have no idea what I'm doing. But I used it to write some code in an app engine to load data into a file. It works perfectly (as expected).

However, if I use the same code online with a button, it fails. The error is:

Calling Java javax.script.ScriptEngine.eval: no overload matches. (2,743) S3_IRCC_VERIFY.GBL.S3_IRCC_WRK.SAD_PB_UPLOAD.FieldChange PCPC:635 Statement:5


While calling the given Java method, no overload was found with a parameter signature that matches the supplied parameter types.


Any thoughts on where to start?

Jim Marion said...

@Myron,

It appears to be failing on the eval method, which is overloaded. Strange that the process scheduler would be fine, but app server wouldn't. They should be the same Java version, etc. I hate to think that it would require reflection, but it might. Usually PeopleSoft can identify methods that use strings. Another idea is to try this:

Local JavaObject &jScript = CreateJavaObject("java.lang.String", &script);

&engine.eval(&jScript);