Skip to main content  
        iSeries home   |   Easy400     |   CGIDEV2     |   MMAIL  
Program development
 TABLEXLS banners
TABLEXLS includexml
Right to Left
Scott's demos
Java products
Alternate COLHDG
Questions & Answers
 Display the Change Log
 PDF of this tutorial


1. Why
2. How
  3. An example
4. Some tips
5. Special cases


A possible problem with TABLEXLS generated Excel spreadsheets is that they, while are rather easily produced, look always the same and in the long run it may become difficult to distinguish one from the others, unless you find something relevant within the spreadsheet data itself.
As a matter of fact, should you be given one, it would pretty difficult to understand at a first glance where it comes from, what it is about and when it was generated.

This is why some users of the tool were asking for a spreadsheet banner which could identify its source, its contents and its date.


Command TABLEXLS has already so many parameters, that adding a few ones for defining a banner was out of discussion.
The idea was then to create a "banner definition file" to add a single parameter - identifying the banner definition to be used - to command TABLEXLS.
So, here is how it works:
  1. Physical file HSSFCGIDTA/BANNERS collects banner definitions. Banner definition records have a unique numeric key
    (In this way you might dynamically create through your programs temporary banner definitions to be subsequently used in a dynamic TABLEXLS command).
  2. Command HSSFCGI/WRKBAN allows to manually maintain banner definitions in file HSSFCGIDTA/BANNERS.
  3. A new parameter - BANNERID - in commands TABLEXLS and TABLEXLS2 is used to identify the banner definition to be used (leave the default value *NONE to skip banner production).
  4. If requested for a spreadsheet banner, command TABLEXLS generates, at the beginning of its XLSGEN script, some extra code used by XLSGEN programs to generate banner rows at the top of the generated spreadsheet.

3-An example

Let's go through an example. You can practice this example yourself, everything is available in your HSSFCGI.
  1. Run command HSSFCGI/WRKBAN.
    The following shows up:
                            WRKBAN - Work with Banners                      FRANKIE 
     No banners so far. Press F6 to add.
     F3=End  F6=Add  F12=Previous
    Figure 1
  2. Press F6:
                            WRKBAN - Work with Banners                     FRANKIE 
     Type data and press the Enter key to add a new banner.                        
     ID ................     0                                                     
     Picture path ......                                                           
     Title .............                                                           
     Subtitle ..........                                                           
     Picture rows ......   0  
     Picture columns ...   0
     Title columns .....   0  (0=automated)                                        
     F3=End  F12=Previous                                                          
    Figure 2
  3. Enter the following data and press the Enter key:
                            WRKBAN - Work with Banners                     FRANKIE 
     Type data and press the Enter key to add a new banner.                        
     ID ................     1                                                     
     Picture path ...... /hssfcgi/graphics/giovanni.png                            
     Title ............. site downloaders                              
     Subtitle .......... up to August 8, 2019                                      
     Picture rows ......   3  
     Picture columns ...   1
     Title columns .....   0  (0=automated)                                        
     F3=End  F12=Previous
    Figure 3
    Meaning of the data entered:
    • ID - The identifier of this banner definition. It will be mentioned in parameter BANNARID of command TABLEXLS.
    • Picture path - path and name of the (optional) picture that will show up in the banner.
      WARNING - Only pictures with extent JPG, JPEG and PNG are supported.
    • Title - An (optional) title for the spreadsheet. Will show up in the banner.
    • Subtitle - A (optional) subtitle for the spreadsheet. Will also show up in the banner.
      Note 1 - The three items are all optional, but at least one of them should be specified.
    • Picture rows and columns - The picture is anchored on the top left corner of the banner and is sized by the number of spreadsheets rows and columns specified. Note 2 - If no picture specified (Picture path is left blank), Picture columns must be set to zero.
      Picture rows can never be zero, because it tells the number of rows taken by the banner.
    • Title columns - Title and subtitle, if existing, occupy a number of spreadsheet columns at the right of the picture.
      This number tells how many columns should be taken.
      If you specify zero, then title and subtitle will take all the remaining columns at the right of the picture.
      Note 3 - Title and subtitle texts are centered in the column space assigned to them.
  4. The initial screen now contains the following:
                            WRKBAN - Work with Banners                      FRANKIE 
     Enter choice and press Enter.                                                  
                                                          Position to:              
     1=Display, 2=Change, 4=Delete, 7=Duplicate                                     
       ID    Properties                                                             
           1 /hssfcgi/graphics/giovanni.png                                         
    site downloaders                                           
             up to August 8, 2019                                                
             -Picture rows:   3 -Picture col.s:   1 -Title col.s:   0 (0=automated)
     F3=End  F6=Add  F12=Previous                                                   
    Figure 4
    WRKBAN allows to easily add new items and to update the existing ones.
    Full help text is available behind the screens (use F1).
  5. Let us now create a bannered spreadsheet.
    Run the following command:

    to obtain the following "bannered" spreadsheet:
    Figure 5

4-Some tips

  • Background color
    Its is highly recommended to use a background color to come up with a solid banner.
    The background color must be specified in parameter HDGCOLOR (ColHdg background color) of command XLSTABLE (or XLSTABLE2).
    Column headers and banner are always given tnhe same background color.
  • Picture distorsion
    As the picture is anchored to a user-specified number of rows and columns, though looking all right at test phase, it may become suffer from distorsion when one of the columns happens to be shrinked or streched because of their contents.
    It is therefore recommended to be very carefull in using pictures representing objects having a well known geometry.
    "Fluid" images, such as landscapes, flowers, clouds, abstract pictures, etc. usually have no problems.
  • Title / Subtitle on multiple lines
    You may break the title and/or the subtitle by inserting the sequence %LF (Line Feed) in their texts, once or more times.
    By doing so, the row height increases and that may cause picture distorsion (you may try to balance it by increasing the number of columns assigned to the picture).

5-Special cases

  1. No picture
    Picture path ...... *NONE        
    Title ............. downloaders
    Subtitle .......... up to August 8, 2019             
    Picture rows ......   3                           
    Picture columns ...   0                           
    Title columns .....   0  (0=automated)
    Figure 6
  2. No subtitle
    Picture path ...... /hssfcgi/images/sestri_levante.jpg
    Title ............. downloaders                             
    Subtitle .......... *NONE                             
    Picture rows ......   2
    Picture columns ...   3
    Title columns .....   0  (0=automated)
    Figure 7
  3. No title, no subtitle
    Picture path ...... /hssfcgi/images/sestri_levante.jpg
    Title ............. *NONE                             
    Subtitle .......... *NONE                             
    Picture rows ......  10
    Picture columns ...   4
    Title columns .....   0  (0=automated)
    Figure 8