Over the past three years or so, I've provided quite a few articles about how to use the HSSF Java classes in your RPG programs. The HSSF classes are part of an open-source project known as POI, which provides Java classes you can use to read and write Microsoft Office documents. Specifically, HSSF provides access to read and write the .XLS documents used by Microsoft Excel.
On July 5, 2007, the POI project released version 3.0.1 of their software. This version, as well as the previous one released in May, require some minor changes to my RPG sample programs.
This article provides updated RPG code that has been tested with version 3.0.1 of POI, and provides information about how to download and install the new version of POI on i5/OS. It also provides links to the previous articles that explain how to use HSSF in your programs.
Writing Excel Spreadsheets
The HSSF classes work nicely for creating .XLS files that you can download and view on a PC that's running Excel. I find them to be an attractive way of writing report programs. Instead of writing an RPG program that prints a report to a System i output queue, I write them to an Excel file stored in the IFS and e-mail them to the person who requested the report. That user can then open the document in Excel. They can print it if they like, or they can use Excel's many tools to sort it, change formulas, create charts, and so forth.
Because HSSF is relatively slow, I use it primarily for shorter (less than 20 pages) reports. On occasion, I've used it for longer reports, but only when I can run the report as a long-running batch job where it doesn't matter if it takes a long time to complete.
To make it as easy as possible to write HSSF code from RPG, I've created a service program full of routines. I named my service program HSSFR4 (the R4 stands for "RPG IV").
Updated HSSFR4 Code
You can download the updated HSSFR4 code (tested with POI version 3.0.1) from the following link:
Links to Previous Articles
Note: When reading the following articles, please use the source code from this article (see the above link). The articles below contain older versions of the code that might not be compatible with version 3.0.1. The source code from this article also contains bug fixes.
I have written the following articles that describe how to use HSSFR4 from your RPG programs.
Reading Excel Spreadsheets
The HSSF classes provide two different ways to read an RPG spreadsheet.
In my experience, the code for the event model is more mature, uses less resources, and runs faster. However, if you want to update an existing spreadsheet instead of only reading it, then you have to use the user model. The user model doesn't require you to loop through all cells in the spreadsheet, and is therefore more efficient if you need to read only a few cells.
Updated XLPARSER4 Code
My RPG service program that implements the event model is called XLPARSER4. It also requires a Java JAR file named xlparse.jar. You can download the updated RPG and Java code from the following link:
Warning: The updated xlparse.jar file included in the preceding link is not compatible with versions of POI prior to 3.0.
The sample code for reading and updating a spreadsheet using the "user model" is included in the HSSFR4 service program download. You can download that code in the section titled "Updated HSSFR4 Code", above.
Links to Previous Articles
Note: When reading the following articles, please use the source code from this article (see the link, above). The articles below contain older versions of the code that are not be compatible with version 3.0.1. The source code from this article also contains bug fixes.
I have written the following articles that describe how to read Excel spreadsheets using the event model:
The following article describes how to read and update a spreadsheet using the user model:
Downloading and Installing POI 3.0.1
In order to use my RPG sample code and tools, you'll need the POI Java classes. They're free tools developed by Apache and released under the (open source) Apache License. Here are the steps needed to install them:
If you'd like to use my event model code (the XLPARSER4 service program) you should upload the xlparse.jar file using the same method you use for the POI jar files.
Installing the POI JAR Files in i5/OS
Personally, I prefer to put the POI JAR files in the Java "extensions" (/qibm/userdata/java400/ext) IFS directory. Once that's done, they're installed and there's no need for me to monkey with my CLASSPATH, because any JAR file in the extensions directory will automatically be found and used by the JVM. Basically, any JAR file in that directory is considered a "public tool" available to any Java software on the system.
Thus, in the FTP example above, I uploaded the files directly to /qibm/userdata/java400/ext, and that's it! No further installation is necessary. Basically, I do it because it's convenient for me.
There are many others who disagree with me on this point. They feel that considering POI an "extension" is not proper. Extensions should be reserved for things that'll be used throughout the whole system, and only a small percentage of Java code will use POI. It therefore doesn't make sense for every Java application to load POI as an "extension".
If you prefer not to use the extensions directory, then I suggest creating a directory named "/poi" or "/java/poi" or whatever makes sense in your shop. Upload the JAR files to that directory, and add them to your CLASSPATH.
Java's CLASSPATH is very much like a library list or binding directory. It's a list of places where Java should look for compiled objects. You see, when you compile Java source code, it creates a "class" file, for example, if I compile a Java source file named "scott.java" the object that it would compile to would be named "scott.class". An application can be made up of dozens or even hundreds of different classes, so the CLASSPATH is a list of directories that contain Java .CLASS files.
The part that often confuses people is "compressed directories". You see, since there are so many Java .CLASS files used in a typical application, it's often desirable to use an archive tool (like the ZIP tool used so often on Windows systems) to compress a whole directory full of class files into a single disk object. Indeed, Java comes with it's own archiving tool that does exactly that. This tool is called JAR which is short for "Java Archive".
That means that a JAR file is really a "compressed directory". It's a directory full of Java .CLASS objects.
When you create a CLASSPATH, you must list every directory that you want Java to search for .CLASS files. That includes JAR files, since they really are "directories" and not just single objects. So each JAR file must be explicitly listed in the CLASSPATH
Here's a sample CL command that sets a CLASSPATH for my system:
ADDENVVAR ENVVAR(CLASSPATH) + VALUE('/scott-test/classes:+ /poi/poi-3.0.1-FINAL-20070705.jar:+ /poi/poi-contrib-3.0.1-FINAL-20070705.jar:+ /poi/poi-scratchpad-3.0.1-FINAL-20070705.jar:+ .')
In this example, my CLASSPATH contains one normal directory named "/scott-test/classes" followed by three compressed directories (the three JAR files included with POI). At the end, I have ".", which means "the current directory". Note that each directory name is separated from the others by a colon.
Note: It's important to understand that the Java Virtual Machine (JVM) only checks the CLASSPATH environment variable when it's started, which only happens once in each job. Make sure you set the CLASSPATH before running anything that uses Java.
If you've placed the POI JAR files in the "extensions" directory, make sure they are not in your CLASSPATH. It can cause problems to have them listed in both places. You only need to use the CLASSPATH if you decided not to install the JAR files in /qibm/userdata/java400/ext.
If you plan to use the "event model" sample code (XLPARSER4) you will also need to install the xlparse.jar file the same way you installed the POI jar files. In other words, you'll either need to add it to your CLASSPATH, or you'll have to install it into the extensions directory.
Removing Old Copies
If you have any old copies of POI installed on your system, they can cause conflicts with the new version.
Simply delete any old copies that you don't need, and Java will not try to load them! The easiest environment to maintain is an environment that only uses one version.
Now you're ready to try out the RPG code (discussed above)
Formula Evaluation (Experimental)
Thanks to System i guru Giuseppe Costagliola, the new version of XLPARSER4 has support for evalulating Excel formulas. It doesn't handle every possible formula, so I'd consider this support "experimental" at this point, but it's pretty neat!
The XLPARSER4 code download contains a sample program named XLPDEMOF and a spreadsheet named TestFormula.xls that you can experiment with if you'd like to experiment with this formula support.
If you view TestFormula.xls with Excel, you'll see that cell C1 does not contain the literal value 300, but rather is a formula that adds cells A1 and B1. If you run the XLPDEMOF program, you'll note that the program is able to come up with the value 300 because POI evaluates the formula on the fly! I think that's pretty cool.