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.
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,
- 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:
- Use PeopleCode to process those rows through a component interface.
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.
This is great Jim.
Thanks again and it was nice to see you again at HIUG.
Java with its latest version is deprecating the Nashorn scripting engine, so you'd need to come up with some other solution, right?
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
Local object &decrypt = CreateObject("Crypt");
MessageBox(0, "", 0, 0, "Decrypted: " | &decrypt.Result);
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.
It won't work. If I delete a couple of lines (the three "content-" lines) like so..
The message is accepted and works fine. It seems like a configuration issue but I can't find it. Any Ideas?
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?
@Raistlin, I have done the same without issue. What issues are you experiencing?
@Satya, great question. Notice the line in the PeopleCode that says:
Post a Comment