Skip to main content  
        iSeries home   |   Easy400     |   CGIDEV2     |   MMAIL  
Public-Source
 
 Introduction
 
XLSCONVERT utility
XLSTABLE utility
XLSTABLE2 utility
Some CGI programs
 
 PDF of this tutorial
 
 Download
 
 

 
XLPARSE2  November 9, 2016 release
Use RPG to read an Excel XLS/XLSX spreadsheet
by Giovanni B. Perotti (Italy)

XLPARSE2 is based on the March 2010 Scott Klement's XLPARSER4 service program, POI 3.6 and other Java supports, which are all bundled in the package.

XLPARSE2 supports both XLS and XLSX Excel spreadsheets.

1- About it   3- Installation
2- Prerequisites   4- A major tip

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.
Scott also provided a few sample RPG programs to print specific Excel XLS/XLSX spreadsheets.

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.

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
  • Compiler ILE RPG IV, product 57xxWDS, opt. 31
    This is needed not only to install the utility (compile ILE-RPG code), but also to run command XLSTABLE.
  • (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.
  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 you plan to run command XLSTABLE,
        make sure that the target system is equipped with the ILE-RPG compiler 57xxWDS, opt. 31
      • If you plan to use instead command XLSTABLE2, the target system does not need to be equipped with the ILE-RPG compiler.
    • 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

For a correct operation of this tool, it is mandatory that library XLPARSE2 is in the job library list before the Java Virtual Machine (JVM) is started.
Therefore, make sure to run command ADDLIBLE XLPARSE2 as soon as the job is started.