Skip to main content  
        iSeries home   |   Easy400     |   CGIDEV2     |   MMAIL  
Public-Source
 
Introduction
 
XLSCONVERT utility
 xlstable utility
XLSTABLE2 utility
Some CGI programs
 
Appendixes:
JVMSTARTUP
 
 PDF of this tutorial
 
 
 Download
 
 

 
 >
XLSTABLE utility
Convert an Excel XLS/XLSX spreadsheet to a db file
  1. What
  2. Command XLSTABLE
  3. An XLSTABLE failure
  4. The process of command XLSTABLE
  5. Spreadsheets containing formulas
  6. About the target file
  7. Suggestions for a recursive use of the same XLS/XLSX spreadsheet model

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/XLSX 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/XLSX 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 XLPARSE2/XLSTABLE generates and fills a physical file (target file) with the data from an Excel XLS/XLSX spreadsheet.

  • The Java job execution environment must not have been already set (through the command ADDENVVAR ENVVAR(CLASSPATH) ...). If a classpath other than the expected one was set, you receive a Java escape message saying that a class was not found.
  • The user profile running command XLSTABLE must have special authority *JOBCTL. This is required by command ADDENVVAR which is executed by the program.
  • The Excel XLS/XLSX 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/XLSX (XLSTABLE)

 Type choices, press Enter.

 .xls/xlsx stream file  . . . . . XLS                                             
                                                                                  
 Spreadsheet sequence number  . . SHEET       1             1-999
 Target file  . . . . . . . . . . FILE                      Name
   Library  . . . . . . . . . . .                           Name
 Target member  . . . . . . . . . FILEMBR     *FIRST        Name, *FIRST
 Replace or add records . . . . . MBROPT      *REPLACE      *ADD, *REPLACE
 (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 columns . . . . . . . . . . DATECOL     *AUTO         Number, *AUTO, *NONE
                + for more values                  
 Date format  . . . . . . . . . . DATEFMT     *YMD          *YMD, *MDY, *DMY
 Time columns . . . . . . . . . . TIMECOL     *NONE         Number, *NONE
                + for more values                  
 Time separator . . . . . . . . . TIMESEP     *             *, :, ., ,, -
 Display target file  . . . . . . DSPFILE     *YES          *YES, *NO
 Run in a sync submitted job  . . SBMJOB      *YES          *YES, *NO     
 Type of failure message  . . . . FAILMSG     *ESCAPE       *ESCAPE, *DIAG
  • .xls/xlsx 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/XLSX spreadsheet to be transformed into the physical file specified in parameter FILE.
    IFS directory '/xlparse2/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.
    If the physical files does not exist, and CRTFILE(*YES) specified, a DDS source member is generated and is used to create the physical file. The physical file is created with MAXMBRS(*NOMAX).
  • Target member (FILEMBR) - Physical file member output from the process. If the member specified does not exist,
    • if the file is defined as MAXMBRS(*NOMAX), the member is added to the file
    • otherwise a program exception is generated.
  • Replace or add records (MBROPT) - Specifies whether the new records replace or are added to the existing records. Select one of the following:
    • *REPLACE - The program clears the existing member and adds the new records.
    • *ADD - The program adds the new records to the end of the existing records.
  • (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/XLSX 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/XLSX 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 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.
      This is the recommended choice unless you know that the spreadsheet contains at least one date column.
    • Enter up to 50 column numbers to identify the columns where numeric values must be converted to date values.
      This is the recommended choice when you know that the spreadsheet contains at least one date column.
    • Type *AUTO to let the program establish which columns contain numeric values to be converted to date values.
      This choice may provide unexpected results on numeric columns and is suggested only for test purposes, in order to find out if any date columns exist in the spreadsheet.
  • 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
    Note. This parameter is not made available when *NONE is specified for parameter DATECOL..
  • Time columns (TIMECOL) - In Excel spreadsheets, the internal representation of a time value (example: 16:48:56) is a number (example: 700648). This number measures the time in millionths of a day (example of computation: there are 84,600 seconds in 24 hours; time 16:48:56 is 69,536 seconds; therefore the Excel internal representation of time 16:48:56 is (69,536/84,600)*1,000,000=700648 .
    In order to have Excel time numeric values converted back to a conventional time format (example: hh.mm.ss), do one of the following:
    • Type *NONE to avoid converting any numeric value to a time value. This is the recommended choice unless you know that the spreadsheet contains at least one time column.
    • Enter up to 50 column numbers to identify the columns where numeric values must be converted to time values. This is the recommended choice when you know that the spreadsheet contains at least one time column.
  • Time separator (TIMESEP) - This parameter is not made available when *NONE is specified for parameter TIMECOL.
    Select one of the following:
    *  to use the time separator defined in system value QTIMSEP.
    :  to use a colon (:) as a time separator (example of result: 16:48:56)
    .  to use a period (.) as a time separator (example of result: 16.48.56)
    ,  to use a comma (,) as a time separator (example of result: 16,48,56)
    -  to use a blank ( ) as a time separator (example of result: 16 48 56) .
  • Display target file (DSPFILE) - whether the target file should be displayed at the end of the process.
  • Run in a sync submitted job (SBMJOB) - If you specify SBMJOB(*YES), the XLSTABLE command is executed in a separate submitted job. The current job waits until the submitted job completes, then it resumes execution.
    This can be useful in two cases:
    • you do not want XLSTABLE to start a Java Virtual Machine in the current job, OR
    • you do not want XLSTABLE to run in the current job because it may conflict with a Java Virtual Machine already active in this job.
    Should you absolutely need to run this command in the current job, though the JVM has already been started by a previous Java application, read about command JVMSTARTUP in Appendix JVMSTARTUP.
  • Type of failure message (FAILMSG) - When you specify SBMJOB(*YES), the XLSTABLE command is executed in a synchronized submitted job.
    It may however happen that the XLSTABLE command fails during its execution. In such a case a message is sent to the current program in the current job. You may choose whether this message would be an *ESCAPE or a *DIAG (diagnostic) one.

3- An XLSTABLE failure

In some cases, XLSTABLE may send the escape message "No significant cells detected in this sheet".
This happens when a spreadsheet has no cells with format text, number or formula. In such as case, do the following:
  • Open the spreadsheet with Excel (2003 or subsequent)
  • Save the spreadsheet; Excel will add the missing properties.
Should you need that be done in a procedure, you may use command
HSSFCGI/CLONEBOOK('bookname')
where bookname if the path&name of the IFS object containing that troubled spreadsheet.
This command uses POI to do what you hould manually do to generate data properties for the spreadsheet cells. It sends an escape message if unable to perform the request, and your program should monitor for that.
Of course, utility HSSFCGI is required.

4- 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 XLPARSE2/XLSCONVERT is executed, thus creating file QTEMP/XLSOUTF from the Excel XLS/XLSX spreadsheet.
    Command XLPARSE2/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/XLSX 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)".
      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.
Warning- As an "ad-hoc" ILE-RPG program must be dynamically created from XLSTABLE command, the ILE RPG compiler (product 57xxWDS, opt. 31) is a prerequisite for the execution of the XLSTABLE command.

5- Spreadsheets containing formulas

Values resulting from formulas may have conversion problems. A simple way to bypass these problems is documented in this topic.

6- About the target file

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

  • Numeric fields - A spreadsheet column containing only numeric data (formulas are considered be numeric) generates a numeric field.
    A numeric field is always generated as zoned, 30 digits, of which 6 are decimal digits.
  • Character fields - A spreadsheet column not containing only numeric data generates a character field.
    Character fields have a critical factor, their sizes. The size of a character field is computed from the largest cell data in a sheet column.
    This means that, if you process via command XLSTABLE an XLS/XLSX 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.

7- Suggestions for a recursive use of the same XLS/XLSX spreadsheet model

In most cases you will be planning for a recurrent use of command XLSTABLE for some XLS/XLSX spreadsheet models.
What you must expect is that a worksheet of a given model is uploaded via XLSTABLE to its appropriate target file member, so that the target file can then be processed by a given program of yours, without the need of re-compile it to match the record format of the target file.
In order for this to happen without troubles, you must make sure that
  1. A given worksheet has always the same number of columns
  2. All columns in the spreadsheet maintain their data type (Character/Numeric; however, see NOTE 1 below.)
  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 - in the following way:
    1. review the DDS:
      1. You CAN assign all fields the names that best fit your process.
      2. You CAN add field level keywords.
      3. You CAN add keys.
      4. You are strongly recommended to increase the lengths of the character fields in order to fit the largest case.
      5. You MUST NOT change data type, digits and decimal positions of the numeric fields. They must always specify 30S 6 .
        See however NOTE 1 below.
      6. You MUST NOT resequence fields.
    2. recreate the target file
    3. run command clrlib qtemp
    4. run command xlstable ... crtfile(*no) ...
  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 !!!
NOTE 1- There might be cases where some columns of a spreadsheet may some times show up as numeric and some other time show up as character. In such cases, the best thing to do is to
  1. redefine via DDS the corresponding target database fields as 30 char fields
  2. run command clrlib qtemp
  3. run command xlstable ... crtfile(*no) ...