In the May 15, 2003, issue of Club Tech iSeries Programming Tips, some information was presented on how to use the Jakarta-POI/HSSF Java classes together with an RPG program to create an Excel spreadsheet. In this tip, the same open-source Java classes will be used to do the opposite: to extract data from an existing Excel spreadsheet.

The downloadable source code for this tip contains an RPG service program and Java .jar file that work together with the HSSF classes to make parsing an Excel document a snap. All you have to do is provide a subprocedure for character cells and a subprocedure for numeric cells, and the service program will call them back for each cell in the workbook.

This code snippet shows the prototypes for the subprocedures that you'll provide as well as the code that tells the service program to call them:

    D charcell        PR             1N
    D   Sheet                     1024A   varying const
    D   Row                         10I 0 value
    D   Column                       5I 0 value
    D   Value                    32767A   varying const

    D numbercell      PR             1N
    D   Sheet                     1024A   varying const
    D   Row                         10I 0 value
    D   Column                       5I 0 value
    D   Value                        8F   value

     /free
        xlparse_workbook('/path/to/my_workbook.xls':
                          %paddr('NUMBERCELL'):
                          %paddr('CHARCELL'));
     /end-free

A complete sample program is included with the source code for this article. To try it out, you'll first need to do some setup:

a) Get the Jakarta-POI Java classes from their Web site. Make sure you get the latest version by clicking "download," then choosing a mirror, and then clicking on the "dev" and "bin" directories. This link will take you to the Jakarta-POI Web site: http://jakarta.apache.org/poi/

b) Unzip the file that you downloaded from the Jakarta-POI Web site and upload the .jar files to the /QIBM/UserData/Java400/ext directory in your IFS. If you use FTP to transfer this file, make sure you use BINARY mode.

c) Retrieve the source code for this tip from the iSeries Network's Web site. http://www.iseriesnetwork.com/noderesources/code/clubtechcode/ParseExcel.zip

d) Upload the xlparse.jar file to the /QIBM/UserData/Java400/ext directory in your IFS. Again, if you use FTP, use BINARY mode.

e) Upload the xlpdemo.xls file to the /tmp directory in the IFS. Again, if you use FTP, use BINARY mode.

f) Create the XLPARSER4 service program and the XLPDEMO program by following the instructions in their respective source members.

The XLPDEMO program will print the contents of some of the cells in the xlpdemo.xls spreadsheet as a demonstration of using the XLPARSER4 service program.

Naturally, you're not limited to printing the contents. Once they've been loaded into variables in your RPG program, you can use them for any purpose you wish.

The May 15, 2003, tip on how to create an Excel spreadsheet is available at: http://www.iseriesnetwork.com/resources/clubtech/index.cfm?fuseaction=ShowNewsletterIssue&ID=16595