Skip to main content  
        iSeries home   |   Easy400     |   CGIDEV2     |   MMAIL  
Freeware
 
 Introduction
 
 
About it
Prerequisites
Installation
XLSCONVERT utility
XLSTABLE utility
 
 Download
 
 

 
XLPARSE
Use RPG to read an Excel XLS spreadsheet
by Giovanni B. Perotti (Italy)
XLPARSE successor

This utility has a successor, XLPARSE2.

XLPARSE2 is very similar to XLPARSE, but it supports both XLS and XLSX Excel spreadsheets. XLPARSE2 prerequisites are a little more demanding than those of XLPARSE:

  • minimum OS/400 release V5R4 (instead of V5R3)
  • minimum Java Development Kit version 5.0 (instead of 1.4)

XLPARSE2 is based on the latest version (March 2010) of Scott Klement's XLPARSER4 service program and on POI 3.6, which are both bundled in the package.

1- About it

XLPARSER4 is a great utility developed by Scott Klement, that provides the tools to read an Excel XLS spreadsheet with RPG and JAVA. In his package, Scott includes 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 provides a few sample RPG programs to print specific Excel XLS spreadsheets.

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

As a result, I provide a library, named XLPARSE, containing

  • An installation procedure that installs the .jar Java classes (both from POI 3.2 and from Scott Klement), 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 spreadsheet to a physical file in library QTEMP, that you can process using our XLSGETCELL procedure.
  • A command, XLSTABLE, that converts any Excel XLS 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, XLPARSE 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 5722WDS, opt. 31
    This is needed not only to install the utility (compile ILE-RPG code), but also to run command XLSTABLE.
  • Library QSYSINC, product 5722-SS1, opt. 13
  • Library QJAVA, product 5722-JV1, opt. 5, feature code 5106 - Java Developer Kit 1.4.
    This component is available for any OS/400 release, starting with V5R2M0.

3-Installation

  • Remove any POI-related and any XLPARSE-related objects from /QIBM/UserData/Java/ext. Adding such Java objects to this directory is a bad practice and should be discontinued.
  • Download file xlparse.zip from the Easy400 download page and unzip it.
  • Follow the Readme.txt instructions to upload and to restore library XLPARSE
  • On the iSeries run the following procedure:
    STRREXPRC SRCMBR(INSTALL) SRCFILE(XLPARSE/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 XLPARSEDTA and populates it with some objects that will contain local data
    • restores IFS directory /xlparse .
      Note that subdirectory /xlparse/java contains all the Java classes (from POI and from Scott Klement) needed by this utility.
    • displays the HTTP directives that optionally allow to make these WEB pages available on your iSeries.
  • Last, to validate the installation run the following command:
    XLPARSE/XLSCONVERT
    The first time this is done, this screen shows up. Do not do anything, ... just wait!
    The next time you run command XLSCONVERT, it will be much faster.

4-XLSCONVERT utility

Go for it.

5-XLSTABLE utility

Go for it.