Skip to main content  
        iSeries home   |   Easy400     |   CGIDEV2     |   MMAIL  
Public-Source
 
Introduction
XLSGEN
Program development
 TABLEXLS
TABLEXLS2
TABLEXLS Banners
Right to left
XLSUPD
Scott's demos
 
Appendixes:
Java products
Alternate COLHDG
 
 PDF of this tutorial
 
 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                                                   
      
 Edit the .xml stream file  . . . EDITXML     *NO           *YES, *NO   
 Add Sheets to existing WkBook  . ADDTO       *NO           *NO, *YES
 Sheet names  . . . . . . . . . . SHEETNAME   *MBRNAME      Name, *MBRNAME
 Display sheets right to left . . SETRTL      *NO           *YES, *NO
 Banner identifier  . . . . . . . BANNERID    *NONE         0-99999, *NONE
 Alternate Column Headings stmf   ALTCOLHDG                                               
      
 Column headings option . . . . . COLHDGOPT   1             1, 2, 3
 ColHdg background color  . . . . HDGCOLOR    *NONE            
 Default cell colors:             DFTCELLCOL
   Font color . . . . . . . . . .             BLACK            
   Background color . . . . . . .             WHITE            
 Cell border  . . . . . . . . . . CELLBORDER  *NO           *NO, *YES, *COLOR, *FCOLOR...
 Frozen pane:                     FROZEN
   no. of frozen initial rows . .             1             0-99999
   no. of frozen initial columns              0             0-99999
 Display zero values  . . . . . . ZERODSP     *YES          *YES, *NO
 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     
 Display pictures . . . . . . . . PICTURES    *NO           *YES, *NO
 VSPAN for picture cells  . . . . VSPAN       7             Number
 Pictures width in pixel  . . . . WIDTH       187           Number
 Paper size . . . . . . . . . . . PAPERSIZE   A4_PAPERSIZE 
 Print-orientation  . . . . . . . PRTORIENT   *PORTRAIT     *PORTRAIT, *LANDSCAPE
 Header:                          PRTHDR
   'Print header' . . . . . . . .             *MBRTXT                                     
      
   Show also on worksheet . . . .             *YES          *YES, *NO
 Generate log . . . . . . . . . . LOG         *NO           *YES, *NO
 Generate XLSGEN report . . . . . XLSGENRPT   *NO           *YES, *NO  
 Download Excel stmf if CGI job . DOWNLOAD    *YES          *YES, *NO
 Run XLSGEN in a submitted job  . SBMJOB      *NO           *YES, *NO
 Worksheet sizing:                SHEETSIZE
   Max no. of cells per workbook              2300000       100000-2300000
   No. of sheets per workbook . .             1             1-100
 Run identifier . . . . . . . . . RUNID       *NONE                         
 Print-fit:                       PRTFIT
   Sheet fit-width (nbr. pages)               0             0-99999
   Sheet fit-height (nbr. pages)              0             0-99999
 Print-scale  . . . . . . . . . . PRTSCALE    100           10-400
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 ILE-RPG program is generated in library HSSFCGIDTA
    (This is why to run command TABLEXLS on an IBMi box you need the ILE-RPG compiler).
    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.
  • 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.
  • 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 a. 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.
  • Display sheets right to left (SETRTL) - Whether the worksheets should be displayed from right to left instead of left to right.
    Note: This parameter works only from release V5R4 on and if the extension of the workbook is xls .
  • Banner identifier (BANNERID) - Numeric identifier of a banner to be added at the beginning of each generated worksheet.
    When no banner is desired, BANNERDID(*NONE) or BANNERID(0) must be specified.
    Banners are defined through command HSSFCGI/WRKBAN .
    A banner is a strip of spreadsheet cells, that takes all the columns in some initial rows and is made of the following three elements:
    1. A picture anchored in the first columns of the spreadsheet
    2. A title sitting in the remaining columns of the spreadsheet
    3. A subtitle below the title, in the remaining columns of the spreadsheet.
    These elements are called "banner properties".
    For detail information and examples of using TABLEXLS banners, see this page.
  • 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 tags of the stream file (XLSGEN script) specified in this parameter ALTCOLHDG.
    For a detail example about using this parameter, see this page.
  • Column headings option (COLHDGOPT) - Use this option to specify the way the column headings of an Excel spreadsheet are filled in.
    Select one of the following:
    • 1 - Database field column headings are used if available. Otherwise field text descriptions are used if available. If not, field names are used.
    • 2 - Database field text descriptions are used if available. Otherwise field column headings are used if available. If not, field names are used.
    • 3 - Field names are used.
  • 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.
  • Freeze pane columns and rows (FROZEN) - Number of initial rows and columns to be frozen.
    Frozen intial rows and/or columns keep steady while you scroll the spreadsheet.
    Note- The generated column headers are all contained within the first row. This is why the default value of the first element is set to 1.
  • Display zero values (ZERODSP) - Whether the numeric cells containing a value zero should display a zero or should display nothing. Select:
    • *YES (default value) to have the numeric cells containing a zero value displaying a zero
    • *NO to have the numeric cells containing a zero value displaying nothing. This option is recommended when a large number of numeric cells may contain zero values, because this makes user reading much easier.
  • 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 2. 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))
  • 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.
  • Paper size (PAPERSIZE) - 33 different paper sizes are available. Use the command prompt or run command DSPPFM HSSFCGI/PAPERSIZES to display them.
  • 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).
  • 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.
  • 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.
  • Download Excel stmf if CGI job (DOWNLOAD) - When command TABLEXLS is executed from a job in subsystem QHTTPSVR (a CGI job), this option allows to download to the client browser the generated Excel stream file. Of course, if this is done, no other response from the CGI program should be sent to the browser.
    If command TABLEXLS is issued from a CGI program:
    • Leave *YES to have the CGI program sending out the Excel stream file
    • Type *NO to avoid downloading the Excel stream file and to have the CGI program providing its own response to the browser.
  • Generate XLSGEN report (XLSGENRPT) - Whether a performance report should be generated once the Excel workbook has been created.
    This report accounts for all the workbook creation runs in the current job.
  • 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.
  • Worksheet sizing (SHEETSIZE) - This parameter is made of two elements:
    1. Maximum number of worksheet cells per Excel workbook
    2. Number of worksheets per workbook
    When the number of cells in a workbook exceeds some POI internal limit, java memory management starts failing clearing the internal garbage collection until it enters a non-ending thread-wait status.
    For .xls workbooks, the maximum manageable number of cells was found to be a little over 2.3 millions. For .xlsx this number was detected to be a little over 2.0 millions.
    The first element of this parameter makes sure that the number of cellsupper limit for a workbook is never exceeded. As soon as it is reached, the workbook is closed and a new job is submitted to create another workbook.
    The second element of this parameter tells in how many worksheets the maximum number of workbook cells should be splitted.
  • Run identifier (RUNID) - After creating one or more workbooks from this command, you may want to compress it or them into a zip stream file to make their delivery easier (for instance, by e-mail).
    If you have this need, in this parameter you should enter a case sensitive character string (30 char.s max) that would later on help you in recalling the generated workbook(s). Example: '1st Quarter Sales' .
    To zip that / those workbook(s), you must run command ZIPWKB (Zip Excel workbooks) by specifying the same identifying character string in its RUNID parameter.
    Example: ZIPWKB RUNID('1st Quarter Sales') ... .
  • Run in a submitted job (SBMJOB) - If you specify SBMJOB(*YES), all the 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.
  • Print-fit (PRTFIT) - This parameter may be used to fit the printout of a spreadsheet in a given number of pages
    . This parameter has two elements:
    1. The first element (Sheet fit-width) tells into how many pages the width of the spreadsheet should be compressed.
      For instance, if the width of the spreadsheet usually requires two pages in order to print all the columns of a given spreadsheet row, by specifying 1 in this element you force all the columns of a row to fit in a single page.
    2. The second element (Sheet fit-height) tells into how many pages the height of the spreadsheet should be compressed.
      By leaving both elements set to 0, you are not asking any print compression.
      By setting both elements to 1, you are asking the printout to fit in a single page.
    Note- This parameter, when used, inhibits the use of parameter PRTSCALE.
  • 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%.

Note- Command TABLEXLS provides, for any generated worksheet, a record in file QTEMP/XLSGENRPT. For the record layout, see this page.
Command HSSFCGI/RPTXLSGEN reads this file and provides a printout listing all the workbooks, with information about their sheets, generated from 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.
    You may try now, just use the "demo" button. Press this link to go.
  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.