Skip to main content  
        iSeries home   |   Easy400     |   CGIDEV2     |   MMAIL  
Freeware
 
Introduction
XLSGEN
Program development
 TABLEXLS
 
 Download
 
 

 
HSSFCGI
TABLEXLS: Create Excel workbooks from IBMi database files

In the previous pages we have described

  • A special XML-like script language that allows command XLSGEN to generate an XLS or XLSX Excel workbook
  • How you can produce such an XML-like script from a program.

As the next step, what about a general program that would

  1. read whatever database files
  2. generate from them an XLSGEN XML-like script
  3. invoke command XLSGEN or XLSXGEN to create an Excel workbook containing one or more spreadsheets ?

We have developed such a program for you in library HSSFCGI, and you can run it through command TABLEXLS.

              Generate an XLS/XLSX from db files (TABLEXLS)
        
 Type choices, press Enter.

 From database files  . . . . . . FROMFILE                  Name
   Library  . . . . . . . . . . .               *LIBL        Name, *LIBL
                         + for more values              
                                                *LIBL                      
 From members . . . . . . . . . . FROMMBR     *ALL          Name, generic*, *FIRST, *ALL 
                         + for more values                               
 To .xml stream file  . . . . . . TOXML       *AUTO                                       
      
 To .xls or .xlsx stream file . . TOXLS                                                   
         
 Add Sheets to existing WkBook  . ADDTO       *NO           *NO, *YES
 Sheet names  . . . . . . . . . . SHEETNAME   *MBRNAME      Name, *MBRNAME
 Edit the .xml stream file  . . . EDITXML     *NO           *YES, *NO
 Alternate Column Headings stmf   ALTCOLHDG                                               
      
 Decimal positions  . . . . . . . DECPOS      *auto         0-9, *AUTO, *NOC
 Convert all date fields to . . . CVTDATE     *NO           *NO, *MDY, *DMY, *YMD...
 Ignore fields  . . . . . . . . . IGNFLDS     *NONE         Name, *NONE
                         + for more values     
 HyperLink fields:                LINKFLDS     
   Link field . . . . . . . . . .                           Name
   Text field . . . . . . . . . .                           Name
                         + for more values     
 Header:                          PRTHDR
   'Print header' . . . . . . . .             *MBRTXT                                     
      
   Show also on worksheet . . . .             *YES          *YES, *NO
 Print-orientation  . . . . . . . PRTORIENT   *PORTRAIT     *PORTRAIT, *LANDSCAPE
 Print-scale  . . . . . . . . . . PRTSCALE    100           10-400               
 ColHdg background color  . . . . HDGCOLOR    *NONE            
 Default cell colors:             DFTCELLCOL
   Font color . . . . . . . . . .             BLACK            
   Background color . . . . . . .             WHITE            
 Cell border  . . . . . . . . . . CELLBORDER  *NO           *NO, *YES, *COLOR, *FCOLOR...
 Display pictures . . . . . . . . PICTURES    *NO           *YES, *NO
 VSPAN for picture cells  . . . . VSPAN       7             Number
 Pictures width in pixel  . . . . WIDTH       187           Number
 Failure recovery:                RECOVERY
   Try to recover . . . . . . . .             *NO           *YES, *NO
   Max. cells per worksheet . . .             10000000      100000-99999999
 Generate log . . . . . . . . . . LOG         *NO           *YES, *NO  
 Run XLSGEN in a submitted job  . SBMJOB      *NO           *YES, *NO
Figure 5 - Command HSSFCGI/TABLEXLS

This command generates - from one or more database files - an Excel workbook on the IFS. The workbook is either type XLS or XLSX depending on the extension specified in the TOXLS stream file name. The generation process goes through three main steps:

  1. For each database file a so-called AD-HOC program is generated in library HSSFCGIDTA. These programs are used to read the database files. An XLSGEN script (an XML-like script, input to command XLSGEN or XLSXGEN) is then generated while reading the database files.
  2. If you specify TOXLS(*NONE), the process stops here. You can then display, edit or update with some program of yours the XML-like XLSGEN script, that you will later on process with command XLSGEN or XLSXGEN under your control.
  3. If you specify for TOXLS a stream file name, then the TABLEXLS program goes on and processes the XML-like XLSGEN script through command XLSGEN or command XLSXGEN (according to the extension of the TOXLS stream file), thus generating an XLS or XLSX stream file ready for Excel.

Note on date fields
Our utility cannot tell whether a database record field contains a date, unless the field definition supports the DDS keyword DATFMT (date format). If the DATFMT keyword is found at field level, then we are sure that the field contains a date, and the value of the DATFMT keyword tells us what is the date format that should be displayed.
DDS data type L is used to define date fields on physical files, and these type of fields support the DATFMT keyword. Also logical file signed and packed fields based on physical date fields should support the DATFMT keyword, according to the IBM System i DDS Manual.
Only the database fields supporting the DATFMT keyword are date fields for the TABLEXLS utility. They are converted to spreadsheet date cells and are edited according to their DATFMT values.
Other fields containing date values, such as plain numeric or character fields, are not detected as date fields by the TABLEXLS utility, and are converted to spreadsheet numeric or text cells without any date formatting.

Should you need to have some character or numeric fields be converted to spreadsheet date cells, you must use parameter TOXLS(*NONE) to generate just the XML-like XLSGEN script, change the <data type="..."> declarations to <data type="date" format="..."> declarations, then process the XML-like script with command XLSGEN or XLSXGEN.

Command parameters:

  • From database files (FROMFILE) - Qualified names of one or more (up to 50) database files that will be used to generate the XLS or XLSX Excel workbook.
  • From members (FROMMBR) - Up to 50 database file members to be processed. Each member will be a separate worksheet in the workbook. Allowed single values:
    • *FIRST - the first or only database file member
    • *ALL - all the database file members.
    NOTE- If a list of members is provided, but none of such members is found on a given database file, member *FIRST is assumed for that database file.
  • To .xml stream file (TOXML) - This is the name of the XLSGEN script (an XML-like) stream file that will be generated, then processed.
    • If you specify *AUTO, the name of this stream file will be assigned by the program, and the stream file will be deleted at the end of the process.
    • If you specify a stream file name of your choice, the stream file will not be deleted at the end of the process. This option allows you to manually modify the XLSGEN script and to use it as input to an XLSGEN command under your control. In this way you may customize the XLS/XLSX spreadsheet according to your needs.
  • To .xls or .xlsx stream file (TOXLS) - This is the name of the XLS stream file (the workbook) that will be generated by the program through the use of an XLSGEN command.
    An estension .xls or .xlsx is required.
      Note: You may specify *NONE instead of a stream file name. If you do so, command XLSGEN/XLSXGEN is not invoked, and the XLS/XLSX stream file is not generated. In this way you may edit or process the generated XML-like stream file, update it to better fit your needs and process it later on with command XLSGEN or XLSXGEN.
  • Add Sheets to existing WkBook (ADDTO) - This parameter specifies whether generated worksheets should be added to an existing workbook or to a new workbook. Enter:
    • *NO when the workbook does not already exist or must be re-created from scratch
    • *YES to add the worksheet(s) to a workbook already existing.
  • Sheet names (SHEETNAME) - Name to be assigned to the generated worksheets. You have two choices:
    1. Leave the default value *MBRNAME. By doing so, each sheet is assigned the name of the related database file member.
    2. Enter a name of up to 8 characters. By doing so, each sheet is assigned this name followed by a sequence number.
  • Edit the .xml stream file (EDITXML) - If you specified TOXLS(*NONE), and you are running an interactive job, you may ask to edit the XML-like stream file as soon as it is generated.
  • Alternate Column Headings stmf (ALTCOLHDG) - As a default, a single spreadsheet column header row would be generated from the database file field descriptions.
    An alternate column headings structure, made of one or more spreadsheet rows, defined by the user, can be used only for the first database file. This alternate column headings structure
    • must be defined on a stream file using the XLSGEN tags <row>, <cell>, <data>, </data>, </cell> and </row>.
    Note: Of course, when an ALTCOLHDG stream file is specified, parameter HDGCOLOR (Column headings background color) no longer applies. To obtain foreground and background colors, XLSGEN keywords f-color and b-color must be specified in the <DATA ...> tags of the stream file (XLSGEN script) specified in this parameter ALTCOLHDG.
    For a detail example about using this parameter, see this page.
  • Decimal positions (DECPOS) - Number of decimal positions for the numeric cells of the spreadsheet.
    If you specify a number from 0 to 9, all the numeric cells display that number of decimal positions. Thousand delimiters are displayed. If you specify *AUTO, each numeric cell displays the number of decimal positions specified in the input XML stream file (parameter INPSTMF). Thousand delimiters are displayed.
    *NOC (no commas) is the same as *AUTO, except that thousand delimiters are not displayed.
  • Convert date fields to (CVTDATE) - Whether all database date fields should be converted to a given format. Leave *NO if date field formats are to be maintained as they are. Available conversion formats are:
    • *MDY (mm/dd/yy)
    • *DMY (dd/mm/yy)
    • *DMYY (dd/mm/yyyy)
    • *YMD (yy/mm/dd)
    • *YYMD (yyyy/mm/dd)
    • *ISO (yyyy-mm-dd)
    • *USA (mm/dd/yyyy)
    • *EUR (dd.mm.yyyy)
    • *JIS (yyyy-mm-dd)
    • *JUL (yyyy-ddd)
  • Ignore fields (IGNFLDS) - You may specify up to 50 field names that are to be excluded from the generated Excel spreadsheet.
  • Hyperlink fields (LINKFLDS) - You may specify up to 50 pairs of field names to be used for creating hyperlink spreadsheet cells.
    Each pair of field names is made up of
    1. The name of a character field containing one of the following link types:
      • an URL link, example: http://www.easy400.net
      • a MAIL link, example: mailto:gb_perotti@easy400.net
      • a FILE link, example: c:\mydir\mydoc.doc .
    2. The name of a character field containing a description of the link.
    You may run a test of this feature by entering command
    TABLEXLS FROMFILE(hssfcgi/hyper) TOXLS('/tmp/hyper.xls') PRTHDR(*MBRTXT) LINKFLDS((itmlnk itmdes))
  • Header (HEADER) - This parameter is used to specify the print header. Two elements are needed:
    1. Print header - The allowed values are:
      • *DFT - The print header displays the qualified database file name
      • *FILETXT - The print header displays the database file text description
      • *MBRTXT - The print header displays the member text description
      • a text description of your own that will apply to all members.
    2. Show also on the worksheet - Select
      • *YES, to display the print header in the first row of the worksheets. Note that *YES is disregarded when *DFT is selected for the print header.
      • *NO, not to display the print header on the worksheets.
  • Print-orientation (PRTORIENT) - Select
    • *PORTRAIT to print the worksheet in Portrait mode (no page rotation)
    • *LANDSCAPE to print the worksheet in Landscape mode (90 degree rotated page).
  • Print-scale (PRTSCALE) - Percent scale to be used for printing. For instance, a value of 75 means that the print scale should be reduced to 75%.
  • ColHdg background color (HDGCOLOR) - Select one of 47 colors to be used as background color for the cells in the first row of the generated spreadsheet (Column Headers), or select *NONE to avoid using a background color.
    The available colors are listed here.
    Column Header cells are always assigned borders, regardless of what is specified in parameter CELLBORDER (see below).
  • Default cell colors (DFTCELLCOL) - These are the default cell colors to be assigned to all spreadsheet cells. There are two default cell colors:
    • The first color is the font color for the text to be written in a cell.
    • The second color is the background color of the cell.
    Each color can be chosen from a list of 47 colors.
    The available colors are listed here.
  • Cell border (CELLBORDER) - Specifies which cells should be given borders. Enter:
    • *NO not to generate borders for any cells
    • *YES to generate borders for all cells
    • *COLOR to generate borders for cells having a background color other than WHITE or a foreground color other than BLACK
    • *FCOLOR to generate borders for cells having a foreground color other than BLACK
    • *BCOLOR to generate borders for cells having a background color other than WHITE.
  • Display pictures (PICTURES) - If the database file contains character fields specifying the names of .PNG or .JPG pictures (example: /mydir/mypicture1.jpg ), you may ask to have these pictures displayed in the spreadsheet instead of just showing their names. Select:
    • *YES to display the pictures instead of their names, or
    • *NO to display just the stream file names of the pictures, as for any other character field.
    Note- For some information about how pictures are specified in a XLSGEN XML-like script, see this page.
  • VSPAN for picture cells (VSPAN) - This parameter (vertical span) is required if PICTURES(*YES) is specified. This parameter is used to specify the height of the pictures and is expressed as the number of spreadsheet rows to be spanned for a picture cell. The default value is 7 (picture cells are given a height corresponding to 7 spreadsheet rows).
  • Pictures width in pixel (WIDTH) - This parameter is required if PICTURES(*YES) is specified. This parameter is used to specify the width in pixel of the pictures cells. Please note that a picture width is always expanded to the cell width, which is assessed by the largest cell in the same spreadsheet column.
  • Failure recovery (RECOVERY) - This parameter tells how the program behaves in case of failure.
    Program failures may be caused by improper XLSGEN script tags or by an excessive memory demand to the JVM when large worksheets are generated. This parameter is made of two elements:
    1. Try to recover - Specify *YES if you require the program to recover from a possible failure.
    2. Max. cells per worksheet - When *YES is specified for Recover, the program splits the workbook in multiple worksheets. Each worksheet contains the number of cells specified in Max. cells per worksheet, rounded up to fill up the last worksheet row.
      Should then a failure occurr,
      • a workbook containing a number of worksheets - excluding the last one where the failure occurred - is made available, and
      • a submitted batch job resumes the process with a new workbook starting with the worksheet where the failure occurred.
        Should a failure occur in the submitted batch job, the recovery scheme applies again.
      The ultimate result could be a number of workbooks generated by several jobs.
      Please note that the original process does not end until the last job in the chain completes. The original process stays in place collecting and displaying (if running in interactive mode) the messages sent from the jobs in the batch chain.
  • Generate log (LOG) - If you specify LOG(*YES), log file XLSGENLOG is generated in library QTEMP. This log contains an entry for each generated worksheet cell. In case of a failure, by displaying this log file you can easily establish which was the last spreadsheet cell generated successfully before the failure occurred.
  • Run XLSGEN in a submitted job (SBMJOB) - If you specify SBMJOB(*YES), all the XLSGEN Java process is executed in a batch submitted job. In this way the settings of the Java Virtual Machine of the bach job cannot conflict with the JVM settings of the current job.



Command TABLEXLS is for a 5250 black-green screen or for execution within a program.
Then, how to run this command from a WEB browser?
If you have installed the Apache HTTP directives in stream file /hssfcgi/apache/http_directives.txt, just try one of the following URL's:
  http://.../tablexls or http://.../hssfcgip/webtablexl.pgm

You will receive the following page:
Figure 6 - Command TABLEXLS from a WEB browser

After submitting the XLSTABLE command, the XLS or XLSX Excel spreadsheet is generated and sent to your browser.

Notes.

  1. The WEB page includes a yellow "demo" button in the bottom left corner. A cycle of three demos is available. This can be used to get some familiarity with the tool.
  2. If, after pressing the "submit" button, command TABLEXLS - launched from the CGI program - fails, the command in error is displayed at the bottom of the page. You are suggested to copy, paste and run it from a green screen session in order to receive some diagnostic messages.