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.