POI Updated to Version 3.0.1
by Scott Klement - July 12th, 2007
(Index of Scott's articles on Excel, RPG and Java)
 

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:
http://www.pentontech.com/IBMContent/Documents/article/55032_244_Hssf.zip

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.

  1. event model - Java reads the entire Excel workbook in order, one cell at a time, and calls your RPG subprocedures for each cell that has data. This method can only read the workbook; it can't change it.

  2. user model - The entire Excel workbook is loaded into memory, and you call Java routines to access the contents of each cell. You can update or add to the workbook as well as read it. You use the same routines you use when writing spreadsheets.

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:
http://www.pentontech.com/IBMContent/Documents/article/55032_245_XlParse.zip

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:

  1. Point your web browser at http://poi.apache.org

  2. Click "download" (it's in the navigation bar on the left, under "Project")

  3. You'll be given a list of different sites that you can download it from. Apache will suggest a site that they think will be the best for your connection, go ahead and click on that site. (Or on a different link, if you prefer.)

  4. You'll have a choice between two directories to download from.

    • "dev" is for copies of POI still in development, it's intended for those who want to help with beta testing.
    • "release" is for the current release of POI, this is the one you probably want. (Unless you want to help test, of course.)

  5. You'll now have a choice between "bin" and "src". Bin is short for "binary" and means that the Java classes are precompiled and ready to go. Src will give you the source code so you can compile it yourself. I always choose "bin" because I'm not really interested in compiling POI from source!

  6. The next screen will list the files you can download. At the time of this writing, the list looks like this:

    • The files ending in "asc" are PGP signatures that you can (optionally) use to verify that the file is genuine.
    • The other two files (.ZIP and .TAR.GZ) both contain the same data, but they're compressed using two different tools.
    • The .TAR.GZ file is archived with the Unix Tape Archiver (TAR) software, and compressed with the GNU Zip utility. This type of archive is ubiquitous on Unix and Linux systems.
    • The .ZIP file is archived using one of the Zip tools (PkZip, WinZip, Info-Zip, et al.) that are ubiquitous on Windows systems.
    • You need to download either the ZIP file or TAR.GZ file. Use whichever one your more comfortable with. If you are a Windows user, that'll most likely be .ZIP.

  7. When the download is complete, open the .ZIP or .TAR file. Inside it will be a directory named (at the time of this writing) poi-3.0.1-FINAL. Open that directory.

  8. Inside the main directory, you'll find the following files:

    • docs a subdirectory containing the Java documentation for POI.
    • NOTICE and LICENSE are files containing information about the copyright of the POI project, and the legal info about how you can use the product, etc. If you'd like to read them from Windows, right-click them and choose "open", then select Wordpad from the list.
    • Three Java Archive (.JAR) files that contain the compiled Java code. These files all start with "poi".

  9. Extract the three JAR files to your PC. (From Windows, this can be done by dragging and dropping them to a normal Windows folder).

  10. Transfer them to your System i, either by using Windows Networking to save the files directly to your IFS, or with FTP in binary mode.

    For example, if you extracted the JAR files to the \Temp folder on your PC, you might type the following commands to use FTP to transfer the files to your System i:

    cd \Temp
ftp systemi.example.com(replace this with the TCP/IP name of your System i)
(-- sign in with userid and password --)
ftp> cd /qibm/userdata/java400/ext(This is the IFS directory you wish to upload into)
ftp> binary
ftp> mput *.jar

ftp> quit

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.

  • Make sure there are no old versions in your /qibm/userdata/java400/ext directory. Any old copies in this directory (even if you use CLASSPATH for the new version) will cause conflicts.
  • If you use CLASSPATH, you can potentially use different versions of POI for different programs. But, make sure that only one copy of POI is in your CLASSPATH at any given time. Don't try to load both versions into the same JVM at the same time.

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.