1- What

After talking to some users about the XLSCONVERT utility, I realized that they needed something more.
It was not enough to provide a way to access an image of an XLS spreadsheet (as the one created in library QTEMP from the XLSCONVERT utility) with their traditional ILE-RPG programming, via subprocedure xlsgetcell.
They simply wanted to have the an Excel XLS spreadsheet transformed into a table: an obvious OS/400 physical file that can be read by any programming language for the System i.
This is what command XLSTABLE is about.

2- Command XLSTABLE

Command XLPARSE/XLSTABLE generates and fills a physical file (target file) with the data from an Excel XLS spreadsheet.

• The Excel XLS spreadsheet must be on the IFS and must be coded in ASCII characters (CCSID 819-ASCII ISO Latin 1, 1212-PC USA, or 1252-IBM PC).
• Though the spreadsheet may contain several spreadsheets, just one spreadsheet is processed.

                        Generate PF from XLS (XLSTABLE)

 Type choices, press Enter.

 .xls stream file . . . . . . . . XLS                                             
                                                                                  
 Spreadsheet sequence number  . . spreadsheet       1             1-999
 Target file  . . . . . . . . . . FILE                      Name
   Library  . . . . . . . . . . .                           Name
 (Re)create target file . . . . . CRTFILE     *YES          *YES, *NO
 Source file  . . . . . . . . . . SRCFILE     QDDSSRC       Name
   Library  . . . . . . . . . . .               *FILELIB    Name, *FILELIB
 Source member  . . . . . . . . . SRCMBR      *FILE         Name, *FILE
 Header lines . . . . . . . . . . HLINES      0             0-99
 Date format  . . . . . . . . . . DATEFMT     *YMD          *YMD, *MDY, *DMY
 Date columns . . . . . . . . . . DATECOL     *AUTO         Number, *AUTO, *NONE
                + for more values                  
 Display target file  . . . . . . DSPFILE     *YES          *YES, *NO

                           Additional Parameters                      

Classpath for java tools . . . . CLASSPATH    *DTAARA       *NONE, *DTAARA       
Java version (e.g. 1.4)  . . . . JAVAVER      1.4           Character value, *DFT

• .xls stream file (XLS) - This is the qualified name of the stream file (CCSID 819-ASCII ISO Latin 1, 1212-PC USA, or 1252-IBM PC) containing the Excel XLS spreadsheet to be transformed into the physical file specified in parameter FILE.
  IFS directory '/xlparse/samples' contains a number of spreadsheet stream files that can be used for testing command XLSTABLE.
• Spreadsheet sequence number (SHEET) - The sequence number (1 to 99) of the spreadsheet to be processed.
• Target file (FILE) - Qualified name of the physical file output from the process.
• (Re)create target file (CRTFILE) - Select one of the following:
  1. *YES
     • if the target file does not yet exist, or
     • if the target file exists already, but you want its record format layout be re-computed from the XLS spreadsheet.
  2. *NO
     if the target file already exists and you want to keep its record format layout that way it currently is. If you select *NO, you must be aware that possible format changes to the Excel XLS spreadsheet may impair the ability to correctly load the data into the existing target file.
• Source file (SRCFILE) (only for CRTFILE(*YES)) - The source file to contain the DDS generated for the target file. If not yet existing, the source file is automatically generated.
• Source member (SRCMBR) (only for CRTFILE(*YES)) - The name of the source member to contain the DDS generated for the target file. You may use *FILE to mean the same name as the target file name in parameter FILE.
• Header lines (HLINES) - Number of header lines that should be ignored.
  Sometimes one or more initial rows of a sheet contain headers. Header columns must not be processed as they do not contain valid record data. This parameter allows to tell how many initial rows should be skipped.
• Date format (DATEFMT) - Date fields are generated as 10 bytes character strings. The date separator character is "-" .
  Select one of the available formats:
  • *YMD - Date representation is yyyy-mm-dd
  • *MDY - Date representation is mm-dd-yyyy
  • *DMY - Date representation is dd-mm-yyyy
• Date columns (DATECOL) - In Excel spreadsheets, a date is stored as a floating point number. This number represents the number of days elapsed since January 1, 1900. In order to have numeric values converted back to dates, do one of the following:
  • Type *NONE to avoid converting any numeric value to a date value.
  • Type *AUTO to let the program establish which columns contain numeric values to be converted to date values.
  • Enter up to 50 column numbers to identify the columns where numeric values must be converted to date values.
• Display target file (DSPFILE) - whether the target file should be displayed at the end of the process.
• Classpath for java tools (CLASSPATH) - Specifies the classpath to locate the Java tools used. These are the 'POI' tools, created by the Apache Jacarta Open source, and xlparse.jar, created by Scott Klement.
  The classes are located in directory /xlparse/java. The default is to use them, however if you have the POI classes installed, you may prefer to use them instead.
  The classpath is set using the *JOB level environment variable 'CLASSPATH'.
  The classpath, if one is used, is stored in the data area XLSCONVERT. This will be located using the library list.
  • *NONE - No classpath environment variable is created.
  • *DTAARA - The contents of the data area XLSCONVERT are set for the job level environment variable CLASSPATH. If this environment variable already exists, it's value is replaced.
• Java version (JAVAVER) - Specifies the Java version to be used when running the Java component of the conversion tools. The minimum Java version required is 1.4 .
  • *DFT - The default version of Java will be used.
  • character-value - Specify the version of Java to be used.

3- The process of command XLSTABLE

It may be helpfull to understand what goes on under the covers:

1. Some initial checks are done.
2. Command XLPARSE/XLSCONVERT is executed, thus creating file QTEMP/XLSOUTF from the Excel XLS spreadsheet.
   Command XLPARSE/XLSCONVERT really reads all the spreadsheet cells, using the Scott Klements XLPARSER4 utility (Scott's xlparse Java class and POI Java classes).
   As a result from this process, file QTEMP/XLSOUTF contains a database representation of all the spreadsheets in the XLS workbook.
3. If CRTFILE(*YES) specified
   1. Subprocedure xlsgetcell is used to receive from file QTEMP/XLSOUTF all the cells from the requested sheet (see parameter SHEET).
      At the end of this process, the data type and the size of each column in the sheet are known, a DDS member is generated (any previous version of the target file member is replaced by the new one (!!!)) and the target file is created.
   2. An "ad-hoc" program is created in library QTEMP. This program will be used to add records to the target file.
4. If CRTFILE(*NO) specified
   1. DDS are not regenerated, and the target file is not rebuilt.
   2. The "ad-hoc" program in library QTEMP is recreated if it cannot be found.
5. Next, the load of records to the target file takes place:
   • The record format field descriptions of the target file are retrieved. They are used to map the input buffer to be passed to the "ad-hoc" program.
   • Subprocedure xlsgetcell receives from file QTEMP/XLSOUTF - for the requested sheet, see parameter SHEET - all the cells, one at a time. Each cell data is formatted into the input buffer
     • character data are padded right with blanks to fit the the field length of the receiving database file field
     • numeric data are converted to the format "zoned (30 6)".
       Worth to be noted that if some non-numeric data are found in a numeric column, they are replaced by a zero value.
     As soon as a record is complete, the input buffer is passed to the "ad-hoc" program, which cares for writing it to the target file.
6. Last, if DSPFILE(*YES), the contents of the target file are displayed.
   A far better insight of the target file can be displayed by another open-source Easy400.net utility: CGI_WRKDBF.

4- About the target file

In the target file, each field corresponds to a column in the XLS sheet.
There are two type of fields:

• Numeric fields - Numeric fields come from numeric columns (formulas are considered be numeric). They are represented as zoned fields, 30 digits, of which 6 are decimal digits.
• Character fields - Character fields have a critical factor, their sizes. The size of a character field is computed from the largest cell in a sheet column.
  This means that, if you process via command XLSTABLE an XLS sheet similar (same type and number of columns) the resulting re-created target file record format may result different simply because the largest character cell in a given column has a size different from the largest character cell in the same column of the previous similar sheet.

Field names are assigned computed names, such as FLD1, FLD2, FLD3, ..., FLDn.

5- Suggestions for a recursive use of the same XLS spreadsheet model

1. A given worksheet has always the same number of columns
2. All columns maintain their data type (Character/Numeric)
3. The character fields in the record format of the target file are large enough to account for the largest case. This can simply be done - once for ever - by
   1. reviewing the DDS:
      • You CAN assign all fields the names that best fit your process.
      • You CAN add field level keywords.
      • You CAN add keys.
      • You are strongly recommended to increase the lengths of the character fields in order to fit the largest case.
      • You MUST NOT change data type, digits and decimal positions of the numeric fields. They must always specify 30S 6 .
      • You MUST NOT resequence fields.
   2. and recreating the target file.
4. Make sure that CRTFILE(*NO) is specified in command XLSTABLE. If you specify CRTFILE(*YES) your DDS member is cleared and new DDS are generated from the program !!!