|
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.
|
