1- About it

XLPARSER4 -a great utility developed by Scott Klement- is a set of procedures for reading an Excel XLS/XLSX spreadsheet with RPG and JAVA. In his package, Scott included some Java classes which refer to some other Java classes called Jakarta POI that the user should install by himself.

• The Jakarta Project creates and maintains open source software for the Java platform. It operates as an umbrella project under the auspices of the Apache Software Foundation.
• POI (a pure Java port of Microsoft's popular file formats) is one of the projects formerly part of Jakarta, but now an independent project within the Apache Software Foundation.

I wanted something more.
I wanted a tool to convert to a standard database file any Excel XLS/XLSX spreadsheet, so that any application program could then process the data collected from a spreadsheet.

The outcome was a library, named XLPARSE2, containing

• An installation procedure that installs the .jar Java classes (both from POI 3.6, from Scott Klement and others), needed by the Scott's XLPARSER4 service program, his sample programs, plus some code of mine.
  This installation procedure runs under the covers and requires no decisions from the installer.
• A command, XLSCONVERT, that converts any Excel XLS/XLSX spreadsheet to a physical file in library QTEMP, that you can process using our XLSGETCELL procedure.
• A command, XLSTABLE, that converts any Excel XLS/XLSX spreadsheet to a physical database file that you can very easily process with your programs.

This page explains how to install and how to run this utility.

Use this link to read some articles from Scott that I have saved into my pages.

XLPARSER4 restriction- This Scott Klement's service program can parse at maximum the first 256 columns (cells) of Excel spreadsheet rows. Subsequent cells are ignored.

Maintenance- As any other utility from the Easy400.net site, XLPARSE2 is maintained as needed (fixes, new features, etc.). In order to know whether a new release is available and what is new there, please refer to the maintenance page.

2-Prerequisites

• OS/400 release V5R3 or subsequent
• Library QSYSINC, product 57xxWDS, opt. 13
• Library QHTTPSVR, product 57xx-DG1
• Compiler ILE RPG IV, product 57xxWDS, opt. 31
  This is just needed to create objects duting the installation of the utility.
• (optional) Library CGIDEV2 (service program CGIDEV2/CGISRVPGM2) downloaded from site www.easy400.net .
  If this is available, you may run some examples of XLPARSE2 CGI programs.
• Developer Kit for Java, product 57xxJV1, *base
• If OS/400 release V5R3:
  • Product 57xxJV1, option 6 -

    Java Developer Kit Classic 4.0 (java version 1.4).

  • On OS/400 release V5R3 only .XLS worksheets can be processed, .XLSX worksheets require at least OS/400 release V5R4.
• If OS/400 release V5R4 or a subsequent one:
  • Product 57xxJV1, opt. 7 -

    Java Developer Kit Classic 5.0 (java version 1.5).

    This component is available from V5R4M0 on.
    On subsequent OS/400 releases, further JDK (Java Developer Kit) versions (options 8, 9, etc.) are available.
    For more information on IBM i 57xxJV1 product options vs OS/400 releases, see this page.
• Warning on 57xxJv1 - Make sure to have installed the last PTF cumulative for 57xxJV1 !!!

3-Installation

1. Download file xlparse2.zip from the Easy400 download page and unzip it.
2. Follow the xlparse2.txt instructions to upload and to restore library XLPARSE2
3. On the IBM System i, logon with a user profile having special authority *JOBCTL and run the following procedure:
   STRREXPRC SRCMBR(INSTALL) SRCFILE(XLPARSE2/QREXSRC)
   It does the following:
   • creates service program GPPARSER4 (some procedures needed to support command XLSCONVERT)
   • creates Scott's service program XLPARSER4 (parsing Excel spreadsheet procedures)
   • creates Scott's sample programs
   • creates utilities XLSCONVERT and XLSTABLE
   • creates library XLPARSE2DT and populates it with some objects that will contain local data
   • restores IFS directory /xlparse2 .
     Note that subdirectory /xlparse2/java contains all the Java classes (from POI, from Scott Klement and others) needed by this utility.
   • if library CGIDEV2 (service program CGIDEV2/CGISRVPGM2 is available, displays the directives of HTTP instance XLSPARSE2 (initial comments tell how you can create such HTTP instance), otherwise
     displays the HTTP directives that you may add to an HTTP instance of yours to make these WEB pages available on your IBM System i.
4. Remove any POI-related and any XLPARSE-related objects from /QIBM/UserData/Java400/ext. Adding such Java objects to this directory is a bad practice and should be discontinued. Read Scott Klement's recommendation!
   Do the following:
   1. After installing XLPARSE2, run command WRKLNK '/xlparse2/java/*'
      and take a note of all the subdirectories there.
   2. Then run command WRKLNK '/QIBM/UserData/Java400/ext'
      and make sure that no such subdirectories exist in the /QIBM/UserData/Java400/ext directory.

   In other words, you must make sure that none of the following IFS objects are left in directory /QIBM/userdata/Java400/ext or in any of its subdirectories:

   /dom4j-1.6.1.jar /poi-contrib-3.2-final-20081019.jar /poi-ooxml-schemas-3.6-20091214.jar /jsr173_1.0_api.jar /poi-scratchpad-3.2-final-20081019.jar /poi-ooxml-3.6-20091214.jar /xlparse.jar /poi-3.2-final-20081019.jar /poi-3.6-20091214.jar xbean.jar

   For more information about positioning Java objects, see Appendix JVMSTARTUP.
5. Last, to validate the installation run the following command:
   XLPARSE2/XLSCONVERT
   The first time this is done, a screen similar to this shows up. Do not do anything, ... just wait!
   The next time you run command XLSCONVERT, it will be much faster.
   Note- If command XLPARSE2/XLSCONVERT fails with Java error message
   Error occurred while parsing spreadsheet after cell (0,0) in *Unknown* , the reason could be one of the following:
   • Directory /QIBM/UserData/Java400/ext still contains some POI-related and / or some XLPARSE-related objects
   • You are missing some PTF(s) for product 57xxJV1. Install the latest PTF CUM for product 57xxJV1.
6. Important note - If this utility is installed on a V5R3 box, when the box is updated to a subsequent OS/400 release, it is necessary to run command xlparse2/compile .

• Re-installing XLPARSE2 on another box

  To re-install XLPARSE2 from one box ("source system") to another box ("target system"), proceed as follow:
  • If the target system has an OS/400 release VxRyMz lower than the source system one, and the source system supports such previous release, on the source system you must re-compile the XLPARSE2 programs by running command
    STRREXPRC SRCMBR(INSTALL) SRCFILE(XLPARSE2/QREXSRC) PARM(VxRyMz)
  • On the source system save library XLPARSE2 (specify parameter TGTRLS if needed)
  • On the target system, remove any POI-related and any XLPARSE-related objects from /QIBM/UserData/Java400/ext
  • On the target system, restore library XLPARSE2
  • On the target system, run command xlparse2/install.

4-A major tip