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 a Scott Klement's presentation (PDF)
• going through several articles by Scott, that I have collected.

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:

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

1. All the following are free of charge and 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
• Library QSYSINC, product 57xx-SS1, opt. 13
• 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.
• Warning on 57xxJv1 - Make sure to have installed the last PTF cumulative for 57xxJV1 !!!

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.
• 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 Readme.txt instructions to upload and to restore library HSSFCGI.
• On the iSeries 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/QRPGLESRC1
  • 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.
    
• To validate the installation, run command
  HSSFCGI/VALIDATE.
  If the validation is successful, message "HSSFCGI installation was validated" is displayed.

4-Maintenance

See the HSSFCGI 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 valdation 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)
      • 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
      <LocationMatch (^/hssfcgip/(.*)$|^/tablexls$)>
      AuthType Basic
      AuthName "HSSFCGI reserved tools"
      PasswdFile QGPL/XXX
      UserID YYY
      Require valid-user
      </LocationMatch>
      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

   That is documented in the next page.