1-Objective of this utility

• The main objective of this utility is to import data from Excel spreadsheets into IBMi database files, and to export data from IBMi database file into Excel spreadsheets. Excel spreadsheets must be manually uploaded/downloaded to/from IBMi.
• Maintaining Excel spreadsheets on the IBMi by exploiting Excel features is NOT an objective of this tool.
• Excel cell formats supported are only those matching one of the available IBMi field data types:

  	supported	not supported
  general		X
  number	X
  currency		X
  accounting		X
  date	X
  time	X
  percentage	X
  fraction		X
  scientific		X
  text	X
  special		X
  custom		X

2-Hilights

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

• No more than 256 columns can be created in Excel spreadsheet rows.
• Character fields: only the first 1024 characters are taken.

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.

3-Prerequisites

• 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
• Library QHTTPSVR, product 57xx-DG1
• Compiler ILE RPG IV, product 57xxWDS, opt. 31 - Necessary to install the product and to run command XLSTABLE
• 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 V6R1 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.

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

  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.
• 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- HSSFCGI selection of an IBM i Java product
Your IBM i may have more than one Java product installed, each one with a different feature code (Use command DSPSFWRSC for a complete display of the installed products).
Java products available for a given OS/release are documented in file HSSFCGI/JAVATABLE, and this table is used by HSSFCGI to automatically select the Java product to be run.
As a rule, HSSFCGI selects for use the first 51xx Java product available and compatible with POI.
However, you may force the use of your preferred Java product with command HSSFCGI/JAVASEL.
It may also happen that you just installed a NEW OS/release containing a Java product whivh is not mentioned in file HSSFCGI/JAVATABLE. In such a case you may run command HSSFCGI/JAVATABUPD to document this Java product in file HSSFCGI/JAVATABLE. The help text of this command will support you quite a lot.

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

5-Maintenance

6-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)

7-The XLSGEN language

8-Right To Left