Skip to main content  
        iSeries home   |   Easy400     |   CGIDEV2     |   MMAIL  
Public-Source
 
 Introduction
XLSGEN
Program development
TABLEXLS
TABLEXLS2
TABLEXLS Banners
Right to left
XLSUPD
Scott's demos
 
Appendixes:
Java products
Alternate COLHDG
 
 PDF of this tutorial
 
 Download
 
 

 
HSSFCGI  
A simple way to create Spreadsheets with HSSFR4
by Giovanni B. Perotti (Italy)
The nasty question

Why using an XML document to create a spreadsheet via HSSF POI Java classes? Excel natively supports XML documents as spreadsheets. Why not create a spreadsheet directly from an XML document, and skip POI altogether?

  • Excel native support for XML documents is simply horrible. One may spend days without obtaining anything. Diagnostic is ridiculous. This is why we'd rather develop a simplifying interface to it. See our utility XLSCGI.
  • Excel native support for XML documents starts with MS Office 2003, previous versions are not supported. HSSF POI is reported to support Microsoft Office from 1997 version on.
  • Excel native support for XML documents does not include any graphic support (pictures). HSSF POI does.

1-About it

From Wikipedia: Apache POI, a project run by the Apache Software Foundation, and previously a sub-project of the Jakarta Project, provides pure Java libraries for reading and writing files in Microsoft Office formats, such as Word, PowerPoint and Excel.

A subcomponent of this project is HSSF.
Wikipedia: HSSF (Horrible SpreadSheet Format) reads and writes Microsoft Excel (XLS) format files. It can read files written by Excel 97 onwards; this file format is known as the BIFF 8 format.
(As the Excel file format is complex and contains a number of tricky characteristics, some of the more advanced features cannot be read. For example, POI is unable to open spreadsheets that have filters or views.)

In the last few years (2004 to 2010), Scott Klement has developed and maintained some free System i ILE-RPG service program, named HSSFR4.
By interfacing HSSF POI Java classes, HSSFR4 offers facilities (ILE-RPG subprocedures) to create even complex Excel spreadsheets. In this way, it is now possible to have - on IBM i - ILE-RPG programs creating Excel spreadsheet.
Information about HSSFR4 can be found

In addition to HSSFR4 subprocedures, you can develop your ones by referring to the Developers' Guide to HSSF and XSSF page. Learn how to do it from Scott's HSSFR4 prototypes.

In April 2009, after trying with success Scott Klement's HSSFR4, I came to the following conclusion:

  • Interfacing HSSFR4 directly from an ILE-RPG program, in order to create a given spreadsheet - though results may be impressive - requires some medium-high programming ability and may need some relevant debugging effort.
  • XLSGEN language
    I then decided to develop an XML-like language able to support spreadsheet creation. It would be interpreted by an ILE-RPG program, providing the necessary interfaces to HSSFR4 subprocedures.
    Though supporting only some of the most common HSSFR4 subprocedures, this language would allow:
    1. To quickly create manually an XML-like script able to generate a prototype spreadhsheet, useful in verifying development assumptions.
    2. Once satisfied by the manual XML-like script, to write a rather conventional program on the IBM i that would generate such XML-like script and would call the script interpreter.
    3. Worth to be noted that such conventional spreadsheet-creating program could be written in any language available on the IBM i, such COBOL, RPG, C and even ... Java.
    Such an XML-like language is now available, it is named XLSGEN and is interpreted through command HSSFCGI/XLSGEN.
    While in the next pages we provide details on this language, you may immediately get some ideas about it by running the following examples:
    spreadsheetcreated from
    this XLSGEN
    XML-like script
    1.xls 1.txt
    2.xls 2.txt
  • TABLEXLS command
    Using the XLSGEN language, a general purpose command, named TABLEXLS, has been developed.
    This command allows to generate an XLS (Microsoft Office 2003) or XLSX (Microsoft Office 2007) Excel spreadsheet from any database file.
    This command can be run in interactive mode, in batch mode and ... even from a WEB browser.

  • XLSUPD and XLSXUPD commands
    With these commands you may update a XLS or a XLSX Excel spreadsheet. A XLSGEN-like stream file is used to provide statements about cells to be updated.

Important notes
  1. All the following are loaded on the IBM i during installation of library HSSFCGI:
    • POI Java classes
    • HSSFR4 service programs, both the version for POI 3.2 (Excel 2003) and the version for POI 3.6 (Excel 2007).
    • XLSGEN interpreter
  2. HSSF allows to imbed pictures in a spreadsheet. Once imbedded, the images are "hard-coded" in the spreadsheet, and do not need to reference the original images. HSSFR4 supports this feature. So does XLSGEN.


2-Prerequisites

  • Compiler ILE RPG IV, product 57xxWDS, opt. 31 - Necessary to install the product and to run command XLSTABLE
  • Library QSYSINC, product 57xx-SS1, opt. 13 - Necessary to build Scott Klement's service program HSSFR4.
  • Library QSHELL, product 57xx-SS1, opt. 30
  • Library QPASE, product 57xx-SS1, opt. 33
  • If you want to create just XLS (Office 2003) Excel spreadsheets, you need
    • Microsoft Office 2003 or subsequent
    • OS/400 release V5R3 or subsequent
    • Library QJAVA, product 57xx-JV1, opt. 6 (Java 1.4) or opt. 7 (Java 1.5) or subsequent.
  • If you want to create also XLSX (Office 2007) Excel spreadsheets, you need
    • Microsoft Office 2007 or subsequent
    • OS/400 release V5R4 or subsequent
    • Library QJAVA, product 57xx-JV1, opt. 7 (Java 1.5) or subsequent.
  • For more information on IBM i 57xxJV1 product options vs OS/400 releases, see this page.
  • Optional prerequisite - HSSFCGI includes a command (ZIPWKB) to zip Excel workbooks into a stream file. This command requires installation of the Easy400.net distributed utility ZIP.


3-Installation

  • 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.
    Leaving such objects in /QIBM/userdata/Java400/ext causes RPG programs, trying to use POI, to receive a nasty Java exception
    ... java/lang/NoSuchMethodError: ...
    Read Scott Klement's recommendation!
    Do the following:
    1. After installing HSSFCGI, run command WRKLNK '/hssfcgi/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.
  • If you already installed a previous version of library HSSFCGI, rename it HSSFCGIOLD.
    You may delete library HSSFCGIOLD upon successful installation of the new version of library HSSFCGI.
  • Download file hssfcgi.zip from the Easy400 download page and unzip it.
  • Follow the HSSFCGI.txt instructions to upload and to restore library HSSFCGI.
  • On the IBMi run the following procedure:
    STRREXPRC SRCMBR(INSTALL) SRCFILE(HSSFCGI/QREXSRC) .
    It does the following:
    • creates Scott Klement's service program HSSFR4_1 (POI 3.2), service program HSSFR4_2 (POI 3.6) and related demos in library HSSFCGI.
      Sources are stored in source files HSSFCGI/QRPGLESRC1 and HSSFCGI/QRPGLESRC2.
    • creates programs XLSGEN (the XLSGEN interpreter) and TABLEXLS
    • creates XLSGEN demo programs GUB and FAMACC
    • creates and populates library HSSFCGIDTA (used to store your TABLEXLS data)
    • restores IFS directory /hssfcgi, which includes Java classes for POI 3.2, POI 3.6, etc.
    • displays the HTTP directives that you may install on an Apache HTTP instance of yours.
      [You may later on display again this HTTP directives with command
      DSPF STMF('/HSSFCGI/apache/http_directives.txt') ]
  • To validate the installation, run command span class="code">HSSFCGI/VALIDATE .
    If the validation is successful, message "HSSFCGI installation was validated" is displayed.

Note 1- Replicating HSSFCGI installation on another box

  1. If the target system runs an OS/400 release VxRyMz lower that the source system one, on the source system you must first re-create the HSSFCGI programs with command
    STRREXPRC SRCMBR(INSTALL) SRCFILE(HSSFCGI/QREXSRC) PARM(VxRyMz)
  2. On the source system save libraries HSSFCGI and HSSFCGIDTA (specify parameter TGTRLS if needed)
  3. On the target system restore libraries HSSFCGI and HSSFCGIDTA
  4. On the target system run command HSSFCGI/INSTALL to complete the setup.


4-Maintenance

This utility is maintained on request, both for applying fixes and for adding enhancements
. Send your requests to the author. To know what is new, just take a look at its Change Log.

5-HTTP instance

  1. HTTP INSTANCE
    In case where
    • you do not want to modify an exsiting HTTP instance, or
    • you prefer to have a separate HTTP instance to run this utility, or
    • you have no experience in handling HTTP instances
    • you may follow our instructions to create an HTTP instance named HSSFCGI.
    It listens on port 8016.
    Its configuration directives are in the IFS stream file /hssfcgi/conf/httpd.conf .
    This is how you create and use this HTTP instance:
    1. run the following command to create the HTTP instance HSSFCGI (which listens on port 8016):
      CPYF FROMFILE(HSSFCGI/QATMHINSTC) TOFILE(QUSRSYS/QATMHINSTC)
      FROMMBR(HSSFCGI) TOMBR(HSSFCGI) MBROPT(*REPLACE) CRTFILE(*YES)
    2. run the following command to start the HTTP instance HSSFCGI:
      STRTCPSVR *HTTP HTTPSVR(HSSFCGI)
    3. to run TABLEXLS, use the following URL:
      http://...:8016/tablexls

  2. RESTRICTING THE ACCESS TO THIS UTILITY The last group of HTTP directives in IFS stream file /hssfcgi/conf/httpd.conf performs user validation vs the system user profiles.
    This is a convenient way of restricting the access to the user profiles having the proper rights for accessing a given database file.
    However, if you do not like to enable all user profiles to access TABLEXLS, you may decide to use instead a validation list in order to restrict the access to a given set of user names.
    This is how you do it:
    1. Create a validation list
      CRTVLDL VLDL(QGPL/xxx) AUT(*EXCLUDE)
      GRTOBJAUT OBJ(QGPL/xxx) OBJTYPE(*VLDL) USER(QTMHHTTP QTMHHTP1) AUT(*USE)

      where xxx is the name of your validation list.
    2. Use HTTP instance ADMIN to add/update entries to this validation list:
      • STRTCPSVR SERVER(*HTTP) HTTPSVR(*ADMIN)
      • span class="code">http://...:2001
      • click link IBM Web Administration for iSeries
      • press the Advanced tab
      • press the Internet Users and Groups tab
      • select one of the following:
        • Add Internet User
        • Change Internet User Password
        • Delete Internet User
      • specify your validation list as library_name/validation_list_name.
    3. From a 5250 session enter command
      EDTF '/hssfcgi/conf/httpd.conf'
      to update the HSSFCGI HTTP instance configuration file, and replace the last group of directives as follow:
      # The following group of directives is for user validation vs the system user profiles

      AuthType Basic
      AuthName "HSSFCGI reserved tools"
      PasswdFile QGPL/XXX
      UserID YYY
      Require valid-user

      where
      • XXX is the name of your validation list
      • YYY is the name of an user profile of your choice.
        User profiles QTMHHTTP and QTMHHTP1 should be granted for its *USE.
    4. Restart the HSSFCGI HTTP instance:
      STRTCPSVR SERVER(*HTTP) RESTART(*HTTP) HTTPSVR(HSSFCGI)

6-The XLSGEN language

The XLSGEN language is the core of HSSFCGI. It is a rather simple XML-like language, that allows to generate XLS or XLSX Excel spreadsheets.
It is explained in the next page.

7-Right To Left

HSSFCGI includes a feature that forces generated worksheets to display their columns from right to left, instead of from left to right.
Read about it at this page.