Skip to main content  
        iSeries home   |   Easy400     |   CGIDEV2     |   MMAIL  
Public-Source
 
 Introduction
 
XLSCONVERT utility
XLSTABLE utility
Some CGI programs
 
Appendixes:
JVMSTARTUP
Questions & Answers
Java products table
 
 Display the Change Log
 
 PDF of this tutorial
 
 Download
 
 

 
 
XLPARSE2  September 13, 2022 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

  • XLS spreasheets generated from MS Excel 2003 and subsequent releases, including O365
  • XLSX spreasheets generated from MS Excel 2007 and subsequent releases, including O365
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.

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

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.