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.

Dynamic Java in PeopleCode

The PeopleCode language has a lot of features and functions. But sometimes, it seems there are tasks we need to accomplish that are just out of reach of the PeopleCode language. It is at these times that I reach for Java. I have written a lot about Java, so I'm sure many of you already know how to mix Java with PeopleCode. While certainly a rational solution, one of the pain points of a Java solution is managing custom Java on the app and process scheduler servers. Each time you update your compiled class (or jar) files, you have to restart the server. That might be OK in a stable production environment, where you don't intend to change code often, but in development, it is a real pain! Likewise, maintaining custom Java class and jar files through upgrades can be a little sketchy. Specifically, if you redeploy PeopleTools or rewrite psconfig, then it is possible you may miss some of your custom Java code. PeopleBooks tells us how to setup psconfig for custom Java classes, but again, that is just one more thing to manage through upgrades. Now, imagine being able to update your custom Java code with a Data Mover script. Further, imagine being able to run custom Java without making any changes to your application server. Imagine what it would be like to run Java without having to beg (or bribe) your admin for a "no customization" exception. It is possible today. The answer: Use JavaScript to interface between PeopleCode and the delivered Java Runtime Environment. Through the embedded Mozilla Rhino JavaScript script engine of Java, we have full, dynamic access to the JRE. When and how would you use this? Let's review some examples.

Custom HTTP Connections

For various reasons, some customers choose not to implement Integration Broker. These customers find themselves requiring integration, but without IB's networking features. An alternative to %IntBroker.ConnectorRequestURL is to use Java's HttpURLConnection.I strongly discourage this approach, but the question arises. The JRE is there, well integrated with PeopleCode, and ready for use. From PeopleCode, it is possible to create a Java URLConnection using CreateJavaObject("java.net.URL", "http...").openConnection(). A problem arises when we try to invoke methods of a HttpURLConnection, the real return value of URL.openConnection. Unfortunately, PeopleCode doesn't see it that way, which leads down the reflection path (we don't want to go there). This is where JavaScript can help us. JavaScript doesn't mind that URL.openConnection returned an HttpURLConnection even though it said it would just return a URLConnection. Here is an example:

var result = (function() {
    // declare pointers to Java methods to make it easier to invoke the methods
    // by name later
    var URL = Packages.java.net.URL;
    var InputStreamReader = Packages.java.io.InputStreamReader;
    var BufferedReader = Packages.java.io.BufferedReader;
    var StringBuilder = Packages.java.lang.StringBuilder;
    
    var serverAddress = new URL(
            "http://hcm.oraclecloud.com/hcmCoreApi/atomservlet/employee/newhire"
        );
    
    
    // Creates an HttpURLConnection, but returns URLConnection. If I was using
    // PeopleCode, PeopleCode would see this as a URLConnection. To invoke
    // HttpURLConnection methods, I would need to resort to reflection. This is
    // the power of JavaScript in this scenario...
    var connection = serverAddress.openConnection();

    // ... for example, setRequestMethod is NOT a method of URLConnection. It is
    // a method of HttpURLConnection. PeopleCode would throw an error, but
    // JavaScript recognizes this is an HttpURLConnection and allows the method
    // invocation
    connection.setRequestMethod("GET");
    
    // Timeout in milliseconds
    connection.setReadTimeout(10*1000);
    
    // Finally, make the connection
    connection.connect();

    // Read the response
    var reader  = new BufferedReader(
        new InputStreamReader(connection.getInputStream()));
    var sb = new StringBuilder();
    var line;
    
    while ((line = reader.readLine()) !== null) {
      sb.append(line + '\n');
    }
    
    // Return the response to PeopleCode. In this case, the response is an XML
    // string
    return sb;
}());

Excel Spreadsheets

PeopleTools 8.55+ has a PeopleCode API for Excel, which means this solution is now irrelevant. I'm listing it because not everyone is up to PeopleTools 8.55 (yet). If you use this idea to build a solution for 8.54 and later upgrade, Oracle recommends that you switch to the PeopleCode Excel API. The solution will still work with 8.55+, but just isn't recommended post 8.54.

This solution uses the Apache POI library that is distributed with PeopleTools 8.54+ to read and write binary Microsoft Excel files. As with the networking solution above, it is possible to use POI directly from PeopleCode, but a little difficult because POI uses method overloading in a manner that PeopleCode can't resolve. Furthermore, POI uses methods that return superclasses and interfaces that PeopleCode can't cast to subclasses, leading to awful reflection code. Here is an example that reads a spreadsheet row by row, inserting each row into a staging table for later processing.

// endsWith polyfill
if (!String.prototype.endsWith) {
  String.prototype.endsWith = function(searchString, position) {
      var subjectString = this.toString();
      if (typeof position !== 'number' || !isFinite(position) ||
            Math.floor(position) !== position ||
            position > subjectString.length) {
        position = subjectString.length;
      }
      position -= searchString.length;
      var lastIndex = subjectString.indexOf(searchString, position);
      return lastIndex !== -1 && lastIndex === position;
  };
}

// open a workbook, iterate over rows/cells, and then insert them into a
// staging table
var result = (function() {
    // declare pointers to Java methods to make it easier to invoke the methods
    // by name
    var FileInputStream = Packages.java.io.FileInputStream;
    
    var HSSFWorkbook = Packages.org.apache.poi.hssf.usermodel.HSSFWorkbook;
    var Workbook = Packages.org.apache.poi.ss.usermodel.Workbook;
    var XSSFWorkbook = Packages.org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    // declare a PeopleCode function
    var SQLExec = Packages.PeopleSoft.PeopleCode.Func.SQLExec;

    // internal "helper" function that will identify rows inserted into 
    var guid = 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g,
        function(c) {
            var r = Math.random()*16|0, v = c == 'x' ? r : (r&0x3|0x8);
            return v.toString(16);
        }
    );
    
    // open a binary Microsoft Excel file
    var fis = new FileInputStream(fileName);
    
    var workbook;
    
    if(fileName.toLowerCase().endsWith("xlsx")) {
        workbook = new XSSFWorkbook(fis);
    } else if(fileName.toLowerCase().endsWith("xls")) {
        workbook = new HSSFWorkbook(fis);
    }
    
    var sheet = workbook.getSheetAt(0);
    var rowIterator = sheet.iterator();
    var roleName,
        descr,
        row;

    // iterate over each row, inserting those rows into a staging table
    while (rowIterator.hasNext()) {
        row = rowIterator.next();
        roleName = row.getCell(0).getStringCellValue();
        descr = row.getCell(1).getStringCellValue();
        
        // TODO: turn this into a stored SQL definition, not hard coded SQL
        SQLExec("INSERT INTO PS_JM_XLROLE_STAGE VALUES(:1, :2, :3, SYSTIMESTAMP)",
            // notice that the SQLExec parameters are wrapped in an array
            [guid, roleName, descr]
        );
    }
    
    // return the unique identifier that can later be used to select the rows
    // inserted by this process
    return guid;

}());

Here is an example of writing/creating a Microsoft Excel spreadsheet:

var result = (function() {
    // import statements
    var XSSFWorkbook = Packages.org.apache.poi.xssf.usermodel.XSSFWorkbook;
    var FileOutputStream = Packages.java.io.FileOutputStream;

    // variable declarations
    var workbook = new XSSFWorkbook();
    var sheet = workbook.createSheet("Countries");
    var fileName = "c:/temp/countries.xlsx";
    
    var row = sheet.createRow(0);
    var cell = row.createCell(0);

    cell.setCellValue("United States of America");
    cell = row.createCell(1);
    cell.setCellValue("USA");

    row = sheet.createRow(1);
    cell = row.createCell(0);
    cell.setCellValue("India");
    cell = row.createCell(1);
    cell.setCellValue("IND");

    row = sheet.createRow(1);
    cell = row.createCell(0);
    cell.setCellValue("Denmark");
    cell = row.createCell(1);
    cell.setCellValue("DNK");

    var fos = new FileOutputStream(fileName);
    workbook.write(fos);
    fos.close();
    
    return "Created workbook " + fileName;

}());

JSON Parsing

If your goal is to convert a JSON string into SQL insert statements, then this is a very painless alternative:

/* Sample JSON data that will be selected from a record definition
[
    {"emplid": "KU0001", "oprid": "HCRUSA_KU0001"},
    {"emplid": "KU0002", "oprid": "HCRUSA_KU0002"},
    {"emplid": "KU0003", "oprid": "HCRUSA_KU0003"}
];*/

var result = (function() {
    var CreateRecord = Packages.PeopleSoft.PeopleCode.Func.CreateRecord;
    var Name = Packages.PeopleSoft.PeopleCode.Name;
    var SQLExec = Packages.PeopleSoft.PeopleCode.Func.SQLExec;
    
    // example of how to reference a PeopleCode record definition from
    // JavaScript. Later we will select JSON_DATA from this table
    var rec = CreateRecord(new Name('RECORD', 'NAA_SCRIPT_TBL'));

    var count = 0;
    var json_string;
    var json;

    var guid = 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, function(c) {
        var r = Math.random()*16|0, v = c == 'x' ? r : (r&0x3|0x8);
        return v.toString(16);
    });
    
    // Select JSON string from a table. Normally this would come from a variable,
    // a service, etc. Here it makes a great example of how to select rows from
    // a record definition
    rec.GetField(new Name('FIELD', 'PM_SCRIPT_NAME')).setValue('JSON_TEST_DATA');
    rec.SelectByKey();
    json_string = rec.GetField(new Name('FIELD', 'HTMLAREA')).getValue();
    
    // now convert that received string into an object.
    json = JSON.parse(json_string);

    // Iterate over json data and...
    json.forEach(function(item, idx) {
        // ... insert into a staging table
        SQLExec("INSERT INTO PS_NAA_TEST_TBL VALUES(:1, :2, :3, SYSTIMESTAMP)",
            // notice the array wrapper around SQLExec bind values
            [guid, item.emplid, item.oprid]
        );
        count += 1;
    });
    
    return "Inserted " + count + " rows";
    
}());

I could go on and on with examples of creating zip files, encrypting information, base64 encoding binary data, manipulating graphics using Java 2D, etc, but I think you get the idea.