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
The XLSGEN language


The XLSGEN command

The XLSGEN language is a rather simple XML-like language, that allows to generate XLS or XLSX Excel spreadsheets.

The XLSGEN script must be done in an IFS stream file and input-ed either to command HSSFCGI/XLSGEN:

                             XLS generator (XLSGEN)
        
 Type choices, press Enter.

 Input XML stream file  . . . . . INPSTMF                                                 
      
 Output XLS stream file . . . . . OUTSTMF                                                 
      
 Add Sheets to existing WkBook  . ADDTO       *NO           *NO, *YES 
 Decimal positions  . . . . . . . DECPOS      *AUTO         0-9, *AUTO, *NOC
 Display zero values  . . . . . . ZERODSP     *YES          *YES, *NO
 Format all date cells as . . . . CVTDATE     *NO           *NO, *MDY, *DMY, *YMD...
 Initial rows to be frozen  . . . FROZEN      0             Number
 ColHdg background color  . . . . HDGCOLOR    *NONE                
 Default cell colors:             DFTCELLCOL
   Font color . . . . . . . . . .             BLACK                
   Background color . . . . . . .             WHITE                
 Cell border  . . . . . . . . . . CELLBORDER  *NO           *NO, *YES, *COLOR, *FCOLOR...
 Generate log . . . . . . . . . . LOG         *NO           *YES, *NO
 Generate XLSGEN report . . . . . XLSGENRPT   *YES          *YES, *NO
 Worksheet sizing:                SHEETSIZE
   Max no. of cells per workbook              2300000       10000-2300000
   No. of sheets per workbook . .             1             1-100
 Run identifier . . . . . . . . . RUNID       *NONE          
 Run it in a submitted job  . . . SBMJOB      *NO           *YES, *NO
Figure 1a - Command HSSFCGI/XLSGEN


or to command HSSFCGI/XLSXGEN:

                            XLSX generator (XLSXGEN)
        
 Type choices, press Enter.

 Input XML stream file  . . . . . INPSTMF                                                 
      
 Output XLSX stream file  . . . . OUTSTMF                                                 
      
 Add Sheets to existing WkBook  . ADDTO       *NO           *NO, *YES 
 Decimal positions  . . . . . . . DECPOS      *AUTO         0-9, *AUTO, *NOC
 Display zero values  . . . . . . ZERODSP     *YES          *YES, *NO
 Format all date cells as . . . . CVTDATE     *NO           *NO, *MDY, *DMY, *YMD...
 Initial rows to be frozen  . . . FROZEN      0             Number
 ColHdg background color  . . . . HDGCOLOR    *NONE                
 Default cell colors:             DFTCELLCOL
   Font color . . . . . . . . . .             BLACK                
   Background color . . . . . . .             WHITE                
 Cell border  . . . . . . . . . . CELLBORDER  *NO           *NO, *YES, *COLOR, *FCOLOR....
 Generate log . . . . . . . . . . LOG         *NO           *YES, *NO
 Generate XLSGEN report . . . . . XLSGENRPT   *YES          *YES, *NO
 Worksheet sizing:                SHEETSIZE
   Max no. of cells per workbook              2000000       10000-2000000
   No. of sheets per workbook . .             1             1-100
 Run identifier . . . . . . . . . RUNID       *NONE          
 Run it in a submitted job  . . . SBMJOB      *NO           *YES, *NO
Figure 1b - Command HSSFCGI/XLSXGEN


Command parameters

  • Input XML stream file (INPSTMF) - Specifies the XML-like XLSGEN script
  • Output XLS/XLSX stream file (OUTSTMF) - Provides the path and the name of the generated Excel spreadsheet. Extension .XLS or XLSX must be specified.
  • Add Sheets to existing WkBook (ADDTO) - 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.
  • Decimal positions (DECPOS) - Specifies the number of decimal positions to be assigned to numeric cells.
    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.
  • 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.
  • Format all date cells as (CVTDATE) - Use this parameter to format all the date cells of the spreadsheet in the same way. Leave *NO, if you want each date cell in the spreadsheet be formatted according to the related FORMAT parameter specified in the XLSGEN control statements.
  • Initial rows to be frozen (FROZEN) - Use this parameter to establish the number of initial rows to be frozen.
    This parameter implements a "freeze pane".
    This tecnique may be used to keep steady the heading rows, while the data rows are scrolled. Very useful when handling spreadsheets with a large number of rows.
    Frozen rows are repeated in all print pages, thus providing a better understanding of column meanings.
  • ColHdg background color (HDGCOLOR) - Use this parameter to select one from 47 colors (see Figure 6) to be used as background color of the text cells with type "column-header", or select *NONE to avoid using such a background color.
  • Default cell colors (DFTCELLCOL) - Use this parameter to set the default colors (font and background) for the cells of the spreadsheet. Default cell colors do not affect column header cells.
  • Cell border (CELLBORDER) - Use this parameter to specify which cells should have 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.
  • 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.
  • 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.
  • 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
    As soon as one of these two values is exceeded, a workbook is ended and a job is submitted to create another workbook.
    This parameter is extremely helpful when generating a worksheet with a very large number of cells (rows). The worksheet will be automatically splitted into a number of workbooks, each containing a number of worksheets.
    This is necessary because POI has problems when the number of cells in a worksheet or in a workbook exceeds some internal objects capacity. Usually in such a case either some java code becomes unable to manage memory, or enters a non-ending thread-wait status.
    These parameter prevents the above POI problems.
  • 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 ease their delivery (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 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.

Note- Commands XLSGEN and XLSXGEN provide, 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 listingall the workbooks, with information about their sheets, generated from the current job.

Usually, an initial XLSGEN is written manually and input-ed to command XLSGEN or XLSGENX to create a prototype spreadsheet.

Subsequently, one may write a program that

  1. generates the XLGEN script as an IFS stream file
  2. invokes comand XLSGEN or XLSGENX to generate an XLS or XLSX stream file from the XLGEN script
  3. if a CGI program, sends the XLS / XLSX stream file to the browser.
For details on such a program see the next page.



1-A prototype

Take a look at this spreadsheet prototype:
Figure 2 - A prototype

The following XLSGEN script was used for generating it:
<XML>
 <workbook>
  <worksheet name="gumb" print-header="Giovanni's Used Boats" print-orientation="portrait"
                print-scale="110" paper-size="A4_PAPERSIZE">
   <row row-height="40">
    <cell hspan="2">
     <data type="LargeTextC" valign="center">
      GUMB - Giovanni's Used Motor Boats
     </data>
    </cell>
   </row>
   <row>
    <cell hspan="2">
     <data type="SmallTextC" valign="center">
      available in April 2016
     </data>
    </cell>
   </row>
   <row>
    <cell hspan="2">
    </cell>
   </row>
   <row row-height="100">
    <cell>
     <data type="picture" width="187">
      /hssfcgi/images/boatM01.jpg
     </data>
    </cell>
    <cell>
     <data type="text" valign="center">
      36' Solent Spear %LF Year: 2005%LF Current Price:  155,000 ... Single Diesel%LF
     </data>
    </cell>
   </row>
   <row row-height="100">
    <cell>
     <data type="picture" width="187">
      /hssfcgi/images/boatM02.jpg
     </data>
    </cell>
    <cell>
     <data type="text" valign="center">
      75' Riva Venere%LF Year: 2006%LF Current Price: EUR 2,850,000 ... Twin Diesel%LF
     </data>
    </cell>
   </row>
  </worksheet>
 </workbook>
</XML>
        
Figure 3 - A script prototype

The following is what the interpreter (command HSSFCGI/XLSGEN or HSSFCGI/XLSXGEN) does:

  • on tag <workbook> - starts a workbook
  • on tag <worksheet name="gumb"> - starts a worksheet named "gumb"
  • on tag <header>Giovanni's Used Boats</header> defines a print header
  • on tag <row> - starts row 1
  • on tag <cell hspan="2"> - creates logical cell 1AB, spanning the size of two physical cells (hspan means "horizontal span")
  • on tag <data type="LargeTextC"> - establishes a large centered font for the contents of cell 1AB
    then writes the text "GUMB - Giovanni's Used Motor Boats" to cell 1AB
  • on tag </data> ends writing to the cell
  • on tag </cell> ends cell 1AB
  • on tag </row> ends row 1.
  • Row 2 is similar to row 1. The font is now a small centered one.
  • Row 3 is similar to rows 1 and 2, but has no text.
  • The next logical row is made of two cells, both spanning 7 physical rows (row 4 to 10). This is done by specifying a "vertical span" keyword vspan="7" at cell level. The first cell contains a picture with an horizontal size (width) of 187 pixel, containing the image of a boat. The name of the IFS stream file containing the picture must be given in the data portion.
    The second cell contains a plain text, the description of a boat. Within the text you can see some %LF sequences. They are used to generate line feeds.
  • The next logical row is similar to the previous one.



2-The XLSGEN syntax

XLSGEN is a tag language similar to XML:

  • A <tag> must always have a closing </tag>
  • A tag may contain keywords. The syntax of a keyword is
    keyword="value"
    The pair of double quotes (") is mandatory.
  • Tag, keywords and keyword values are case insensitive.
The following table lists the keywords available for each tag.

tagkeywordcomments
XML Used to be consistent with XML syntax The interpreter ignores it
WORKBOOK Starts a workbook A workbook is a set of worksheets.
An XLS / XLSX file can contain just one workbook.
WORKSHEET Starts a worksheet
NAME="value" Name assigned to the worksheet Example:
  • <worksheet name="2016">
PRINT-HEADER=
"page header text"
Worksheet print page header Example:
  • <worksheet name="2016" print-header="Sales this year">
PRINT-ORIENTATION=
"PORTRAIT" or "VERTICAL" OR
"LANDSCAPE" or "HORIZONTAL"
Worksheet print page orientation (♣) Example:
  • <worksheet name="2016" print-header="Sales this year"
    print-orientation="landscape">
FIT-WIDTH=
"number of pages
Number of pages the width of the spreadsheet should
be compressed into. (♣)
A value of 1 forces all columns to fit in the width of a
single page.
Example:
  • <worksheet name="2016" print-header="Sales this year"
    print-orientation="landscape" fit-width="1">
FIT-HEIGHT=
"number of pages
Number of pages the heigth of the spreadsheet should
be compressed into. (♣)
A value of 1 forces all rows to fit in the width of a
single page.
Example:
  • <worksheet name="2016" print-header="Sales this year"
    print-orientation="landscape" fit-width="1" fit-height="1">

This will fit the printout in a single page.
PRINT-SCALE=
values from "10" to "400"
Worksheet print page scaling percentage (♣)
This keyword is ignored when a FIT-... keyword is
specified.
Example:
  • <worksheet name="2016" print-header="Sales this year"
    print-orientation="landscape" print-scale="120">
PAPER-SIZE=
"one of 33 possible values"
(DSPPFM HSSFCGI/PAPERSIZES)
Worksheet page size (♣) Example:
  • <worksheet name="2016" print-header="Sales this year"
    print-orientation="landscape" print-scale="120"
    paper-size="A4_PAPERSIZE">
ROW Starts a row Examples:
  • <row>
  • <row row-height="90">
  • <row page-break>
ROW-HEIGHT="number of points" Sets the height (in points) of this row
PAGE-BREAK Generates a print page break before this row (♣)
CELL Starts a cell Examples:
  • <cell>
  • <cell border>
  • <cell hspan="2" vspan="7">
  • <cell hspan="2" vspan="7" overlay>
  • <cell cell-width="150">
BORDER Add borders to this single cell
HSPAN="value" Number of cells that should be horizontally spanned (merged into this cell)
VSPAN="value" Number of cells that should be vertically spanned (merged into this cell)
OVERLAY To be used only when VSPAN="value greater than 1"
to allow the spanned rows to contain also other cells.
See Figure 5.
CELL-WIDTH="value" Allows to size the width of a cell to a given number of pixels. See the example on your right. Maximum width is 889 pixels. It does not work when HSPAN is also specified.
DATA Defines type and format of cell data
TYPE="value" Possible values:
  • COLUMN-HEADER - centered small text
  • TEXT - left aligned standard text
  • TEXTC - centered standard text
  • TEXTR - right aligned standard text
  • LARGETEXT - left aligned large characters text
  • LARGETEXTC - centered large characters text
  • LARGETEXTR - right aligned large characters text
  • SMALLTEXT - left aligned small characters text
  • SMALLTEXTC - centered small characters text
  • SMALLTEXTR - right aligned small characters text
  • NUMBER - right aligned number
  • LARGENUMBER - right aligned large characters number
  • DATE - centered date
  • PICTURE - image
  • FORMULA - Excel valid formula
    Defines a formula to be computed by Excel.
    The expression defining the formula must be given between <data ... > and </data> . Example:
    <data type="formula">
      sum(a1:b1)
    </data>
    Note. Hyperlinks are supported as formula special cases.
Examples:
  • <data type="largeTextC" wrap>
  • <data type="number">
  • <data type="date" format="mdy">
  • <data type="picture" width="187">
  • <data type="formula">
VALIGN="value" Defines the vertical alignment.
Possible values:
  • TOP
    top-aligned vertical alignment
  • CENTER
    center-aligned vertical alignment
  • BOTTOM
    bottom-aligned vertical alignment
  • JUSTIFY
    vertically justified vertical alignment
Example:
  • <data type="largeTextC" valign="center">
F-COLOR="value" Defines the font color of a cell
(except for <data type="column-header" ...>).
The available font colors are listed in Figure 6.
Example:
  • <data type="number" f-color="blue">
B-COLOR="value" Defines the background color of a cell.
(except for <data type="column-header" ...>).
The available background colors are listed in Figure 6.
Example:
  • <data type="number" f-color="blue" b-color="gold">
FORMAT="value" Defines the format of a date spreadsheet cell.
Possible values:
 cell format
*MDYmm/dd/yy
*DMYdd/mm/yy
*DMYYdd/mm/yyyy
*YMDyy/mm/dd
*YYMDyyyy/mm/dd
*ISOyyyy-mm-dd
*USAmm/dd/yyyy
*EURdd.mm.yyyy
*JISyyyy-mm-dd
*JULyyyy-nnn
Example:
  • <data type="date" format="mdy">
WIDTH="value" Defines the width of a picture, in pixel. Example:
  • <data type="picture" width="187">
BOLD Applies to text, date, number and formula data types. It assigns a bold weight to the font. Examples:
  • <data type="text" bold>
  • <data type="largeTextC" bold>
  • <data type="number" bold>
UNDERLINE Applies to text, date, number and formula data types. It underlines the cell data. Examples:
  • <data type="text" underline>
  • <data type="text" bold underline>
ITALIC Applies to text, date, number and formula data types. It sets the cell data to italic types. Examples:
  • <data type="text" italic>
  • <data type="text" underline italic>
WRAP Applies only to the text data types. It tells whether the text string can be folded (wrapped). Example:
  • <data type="largeTextC" wrap>
(data value) Data value must be written between the tag <data ... > and the tag </data> .
Figure 4 - Tags and keywords

Figure 5 - Effect of the OVERLAY keyword
(<cell hspan="3" vspan="7" overlay>)
See the sample XLSGEN stream file /hssfcgi/tmp/zzz.txt .



3-More on ...

  • Character set restriction
    Workbook stream files are always generated with CCSID 819 (Latin 1 ASCII ISO/IEC 8859-1). Characters supported by this CCSID are documented on this WikipediA page. Characters not supported by this character set ( example: the euro symbol ) show up as character .
  • Some special characters
    Characters < and > cannot be used within a text cell (COLUMN-HEADER, TEXT, LARGETEXT(C), SMALLTEXT(C)), as these two characters are used as delimiters.
    Specifying these characters whithin a text cell will cause XLSGEN to provide unpredictable results.
    Should you, however, need these characters within a text string, you may use their corresponding HTML entities:
     &lt;  (less than)   instead of character  <
     &gt;  (greater than instead of character  >
  • Folding (wrapping) character strings
    There are two ways to fold character strings:
    1. Using keyword WRAP. In this case Excel decides where the folding should take place, depending on the column width.
    2. Inserting line-feed sequences %LF in the text string. In this case Excel adjusts the column width on the length of the longest text segment.
    These two techniques may be combined.
    Example:
    <data type="text" wrap>
    Like as the waves make towards the pebbled shore,%LF
    So do our minutes hasten to their end

    </data>
  • NUMBERS
    Numeric values are represented as follow:
    <data type="number">
     numeric_value
    </data>

    and must have been edited with edit code J, L, N or P.
    Examples of numeric_values:
     edit
    code
     english
    language
     italian
    language
    J  1,234,567.89-  1.2334.567,89-
    L  1234567.89-  12334567,89-
    N  -1,234,567.89  -1.2334.567,89
    P  -1234567.89  -12334567,89
    Note on decimal point and thousand separator characters:
    • Decimal point and thousand separator characters (if used) must be consistent with system value QDECFMT.
    • Inconsistent decimal points and/or thousand separator characters result to wrong value interpretation.
    • Thousand separator characters can be used, but are not required.
  • DATES
    Dates must always be written in the ISO format YYYY-MM-DD.
    The desired date format for the resulting spreadsheet date cell must be specified by the keyword FORMAT=xxx .
    Example:
    <data type="date" format="mdy">
     2009-04-23
    </data>
  • FORMULAS
    • Any formulas supported by Excel 2003 can be used. Example:
      <data type="formula">
       AVERAGE(B34:J34)
      </data>

      Warnings:
      1. If for any reason POI finds out that a formula cannot be supported, you will recreceive the following message when running XLSGEN:
        The call to HSSF_FORMU ended in error (C G D F)
      2. For Excel formulas see this page.
    • Besides, XLSGEN includes the following commands to generate Excel formulas:
      1. VSUM - Tells Excel to compute the sum of the numbers from the preceding numeric cells in the same column.
      2. HSUM - Tells Excel to compute the sum of the numbers from the preceding numeric cells in the same row.
      3. VAVG - Tells Excel to compute the average of the numbers from the preceding numeric cells in the same column.
      4. HAVG - Tells Excel to compute the average of the numbers from the preceding numeric cells in the same row.
      5. XVSUM - Tells Excel to compute the sum of the preceding VSUM cells in the same column.
        This allows to obtain a grand total from the preceding subtotals in the same column.
        This technique is demonstrated in the HSSFCGI CGI program XVSUMTRY.
    • Parameter dec-pos="n" (n = 0 to 9) can be used to establish the number of decimal positions in the result of a formula. Example:
      <data type="formula" dec-pos="3">
         sum(b1:b6)
      </data>

      In absence on this parameter, the number of decimal positions is inherited from the previous numeric cells.
    • HyperLinks are supported as formula special cases.The syntax to be used is as follow:
      <data type="formula">
         hyperlink("link","text")
      </data>

      where
      • link can be
        • an url link, example: http://www.easy400.net
        • a mail link, example: mailto:jay.williams@gmail.com
        • a file link, example: c:\i386\blank.txt
      • text is the text to be linked, example:
        <data type="formula">
           hyperlink("http://www.easy400.net","Easy400 home page")
        </data>
    • To set the width of a column containing formulas, use
      <cell cell-width="nnn"> (where nnn is the desired number of pixels)
      in at least one cell of that column (for instance, in the column header cell).
  • PICTURES
    Picture support is an uniqueness within HSSF. Pictures are copied into the workbook. In this way, they can be exported with the XLS / XLSX file itself.
    Warning - Only PNG and JPEG (jpg) pictures are supported.
    Excel shrinks or stretches your picture, as needed, to make it fit into the assigned cell(s) area.
    Some care should be paid in order to avoid picture distorsion. In the following text
    • by image we mean the image file before it appears on an Excel worksheet
    • by picture we mean the image how it appears in an Excel worksheet.
    Some tips:
    1. Image resolution
      Image sizes are measured in Pixels (dots) and image resolution is measured in Pixels per inch (PPI).
      XLSGEN cell width and row height are instead measured in Points (1 point = 1/72 inch).
      Regardless how measurements are made, it is very important that all images in a given spreadsheet column have the same resolution.
    2. Working with picture height and picture width.
      Picture distorsion occurs when the picture_height/picture_width has a value differen from image_height/image_width.
      You should therefore try to maintain in the picture the same proportion for image height and width.
      For instance, if a picture has a height of 1000 px and a width of 500 px, you must make sure that the cell height is twice the cell width.
      • Which should be cared first? The cell width or the cell height?
        It depends.
        If all the images stay in a single row, you should first establish the desired row height (all the cells in a row have the same height), then compute each cell width in order to maintain for its picture the same proportion width/height existing in the image.
        If all the images stay in a single column, you should first establish the desired column width (all the cells in a column have the same width), then compute each cell height in order to maintain for its picture the same proportion width/height existing in the image.
      • How do I set the picture height?
        In the <row ...> tag specify a ROW-HEIGHT value (in points) for the desired height of all the pictures in a row. Example:
        <row row-height="158">
      • How do I set the picture width?
        Make sure that a column containing pictures does not contain anything else. Column widths are set by the largest contents, and you need the width of this be the exact width you ask for.
        Then, in all the pictures of a given column specify the same picture width. Example:
        <cell>
         <data type="picture" width="250">
          /hssfcgi/images/boatM01.jpg
         </data>
        </cell>
      A useful example is provided by a sample "catalog" demo available within HSSFCGI.
      RPG program CATALOG (HSSFCGI/QRPGLESRC mbr GOCATALOG)
    3. Forcing the original proportions
      To force a picture to its original dimensions, thus avoiding distorsions, you may use the keyword resize="*yes".
      In such a case, however, the picture may not properly fit into its cell. Example:
      <XML>
       <workbook>
        <worksheet name="pictures">
         <row>
          <cell vspan="3">
            <data type="picture" anchor="move" resize="*yes">
             /hssfcgi/graphics/giovanni.png
            </data>
          </cell>
          <cell vspan="3">
            <data type="picture" anchor="move" resize="*no">
             /hssfcgi/graphics/SKlogo.png 
            </data>
          </cell>
          <cell vspan="3" hspan="2">
            <data type="picture" anchor="move" resize="*yes">
             /hssfcgi/images/boatm01.jpg
            </data>
          </cell>
          <cell vspan="3">
           <data type="text">Description of this row %LF</data>
          </cell>
         </row>
        </worksheet>
       </workbook>
      </XML> 
              
    4. Picture resize and relocate permissions.
      You may tell how a picture should behave when the user changes its related column width, or when the user inserts a new row or column.
      The anchor parameter establishes the picture behavior:
      • <data type="picture" anchor="move">
        This is the default behavior: if the column size is changed, or a new column or a new row is added,
        the picture maintains its size and the picture row and column are relocated.
      • <data type="picture" anchor="movesize">
        If the column size is changed, or a new column or a new row is added,
        the picture size is changed to fit the cell size and the picture row and column are relocated.
      • <data type="picture" anchor="fixed">
        If the column size is changed, or a new column or a new row is added,
        the picture maintains its absolute position and size.
  • CELL COLORS
    The are four different ways to assign colors (font colors and background colors) to spreasheet cells:
    1. Automated fonts:
      • Cells with <data type="Formula">HSUM</data> (horizontal sum) and
        cells with <data type="Formula">HAVG</data> (horizontal average)
        are assigned a brown font.
      • Cells with <data type="Formula">VSUM</data> (vertical sum) and
        cells with <data type="Formula">VAVG</data> (vertical average)
        are assigned a royal blue font.
      • Cells with <data type="Formula">formula_expression</data>
        are assigned an indigo font.
      If - in commands XLSGEN, XLSXGEN or TABLEXLS - parameter DFTCELLCOL specifies other than (BLACK WHITE), these cells are handled as any other cell in the spreadsheet (also they do receive the default font color and the background color specified in parameter DFTCELLCOL).
    2. Column headers
      In the XLSGEN language, column headers are identified from the <data type="Column-Header">.
      In commands XLSGEN, XLSXGEN and TABLEXLS, parameter HDGCOLOR allows to specify one out of 47 (see Figure 6) background colors to be assigned to the column header cells. The font color is automatically assigned: black for light background colors, white for dark background colors.
    3. Banner
      If a spreadsheet banner is requested in command XLSTABLE, it is always given the same background and font colors as the column headers.
    4. Default cell colors
      In commands XLSGEN, XLSXGEN and TABLEXLS, parameter DFTCELLCOL allows to specify a given font color and a given background color to be used for all spreahsheet cells, except the column heading ones. If default values (BLACK WHITE) are selected, no special action is taken.
    5. XLSGEN language parameters f-color and b-color
      These parameters can be used in a XLSGEN script to set the font color (f-color) and the background color (b-color) of a given cell.
      As an example
      <cell>
        <data type="number" f-color="blue" b-color="gold">
          2982
        </data>
      </cell>

      generates the following spreadsheet cell:
        2982
      Note that cells like this retain their colors also when default cell colors are assigned through parameter DFTCELLCOL in command XLSGEN or XLSXGEN.
     Aqua   Dark_red   Lavender   Olive_green   Sea_green 
     Black   Dark_teal   Lemon_chiffon   Orange   Sky_blue 
     Blue   Dark_yellow   Light_blue   Orchid   Tan 
     Blue_grey   Gold   Light_cornflower_blue   Pale_blue   Teal 
     Blue_green   Green   Light_green   Turquoise   Violet 
     Brown   Grey_25_percent   Light_orange   Pink   White 
     Coral   Grey_40_percent   Light_turquoise   Plum   Yellow 
     Cornflower_blue   Grey_50_percent   Light_yellow   Red 
     Dark_blue   Grey_80_percent   Lime   Rose 
     Dark_green   Indigo   Maroon   Royal_blue 
    Figure 6 - Supported cell colors
  • CELL STYLES
    Cell styles are established from DATA parameters TYPE, F-COLOR, B-COLOR, BOLD and UNDERLINE.
    The number of supported cell styles for a workbook are 10,064:
    • 64 of these styles are program-defined values and are related to different values of the TYPE parameter.
    • The remaining 10,000 styles are left for user definition in the XML-like stream file input to XLSGEN.
    A user-defined style can be implemented combining a pre-defined style with foreground colors, background-colors and optionally the "bold" and/or the "underline" keywords.
    Example of a user-defined style: <data type="largenumber" f-color="blue" b-color="gold" bold>
    where:
    • type defines a pre-defined style
    • f-color defines a foreground color
    • b-color defines a background color
    • bold provides a bold font weight.
    When the input stream file contains more than 10,000 user-defined cell styles, a warning message appears in the joblog, and only the first 10,000 user-defined styles are honored.


4-Large worksheets

Spreadsheets containing more than 2.3 million cells have problems to be generated. That does not depend from HSSFCGI. It looks like there are some internal POI/Java internal object limits. Once they are exceeded, the spreadsheet creation process becomes slower and slower, until a java memory management exception is raised or a non-ending thread-waiting status is entered.
In order to avoid this problem, HSSFCGI commands XLSGEN and XLSXGEN feature parameter SHEETSIZE. This parameter contains two elements: the maximum number of cells in a workbook, and the desired number of spreadsheets per workbook.
The spreasheet generation process goes in this way:
  • The maximum number of cells in a workbook, divided by the number of worksheets in a book, provides the maximum number of cells in a worksheet.
  • When a worksheet reaches its maximum cells capacity, a new worksheet is automatically generated.
  • When the maximum number of worksheets in a book is reached, the worbook is ended and a batch job is submitted to carry on the process.
  • This batch job generates another workbook (with a name similar to that of the previous workbook) then, if needed, submits another batch job to carry on the generation.
  • This process goes on, through multiple submitted jobs, until all the desired cells have been generated.
  • The program initially started by command XLSGEN or XLSXGEN stays in a wait status until the last batch job, before ending, sends an end-message to it.
  Sample large worksheet
It is suggested that you get familiar with large spreadsheets processing by running a sample case. You will also have the opportunity to test the XLSGEN performance on your IBM i.
  1. Run command HSSFCGI/CRTBIGXML. It creates a XLSGEN stream file, /tmp/bigXml.xml intended to generate a single spreadsheet workbook containing 25,001 rows, 100 columns each, for a total of 2,500,100 cells.
  2. Execute command HSSFCGI/XLSGEN INPSTMF('/tmp/bigXml.xml') OUTSTMF('/tmp/bigXml.xls') SHEETSIZE(100000)
    • The job issuing the command creates a workbook (named /tmp/bigXml.xls ) containing a single sheet of 100,000 cells.
      A chain of jobs automatically submitted creates other 24 workbooks (named /tmp/bigXml_NBR1.xls to /tmp/bigXml_NBR25.xls), each one containing 100,000 cells splitted in two sheets.
We have run this test on an IBM Power 500 Express and the test took about 48 minutes.
The resulting performance figures are:
  • about 52,000 cells created in a minute
  • about 870 cells created in a second.


5-Command ZIPWKB

You may use this command to zip (compress) the Excel workbooks generated from one of the HSSFCGI commands (XLSGEN, XLSXGEN, XLSGEN2, XLSXGEN2, TABLEXLS and TABLEXLS2) provided that:
  1. You have installed the ZIP utility provided by site http://www.easy400.net
  2. In the HSSFCGI command used to generate one or more workbooks, in parameter RUNID you have specified a unique character string identifying the generated workbooks.
                          Zip Excel workbooks (ZIPWKB)                          
                                                                                
 Type choices, press Enter.                                                     
                                                                                
 Run identifier . . . . . . . . . RUNID                                                   
 Zip stream file to be created  . ZIPFILE                                                 
                                                                               
 Display result . . . . . . . . . DSP         *NO           *YES, *NO            
                
Figure 7 - Command HSSFCGI/ZIPWKB
  • Run identifier (RUNID) - This must be exactly the same (case sensitive) character string that you have specified in parameter RUNID of the command (XLSGEN, XLSXGEN, XLSGEN2, XLSXGEN2, TABLEXLS or TABLEXLS2) to create the Excel workbooks that you now want to zip.
    Note: If you do not remember what this string was, you can find it out by browsing file HSSFCGIDTA/RUNWKB.
  • Zip stream file to be created (ZIPFILE) - Path and name of the IFS stream file where you want the Excel workbooks be zipped.
    Note: If that stream file already exists, your user profile must have full authorities over it, as it will be replaced.
  • Display result (DSP) - Whether you want the compression results be displayed (interactive jobs only).


6-Error recording

When an XLSGEN or XLSXGEN process is interrupted by an error condition, understanding what happened is not a simple job. It may require to investigate joblog and dumps, the XLSGEN script and the program source code.

There are three type of exceptions:

  • Program errors
  • Java exceptions due to wrong tags in the XLSGEN script
  • Java exceptions due to memory problems.
XLGEN and XLSGEN program code is clean enough and you should not expect any program errors from it.
Java exceptions may be caused by incorrect XLSGEN tags, such as a formula that Excel would not accept.
Java exceptions due to memory are caused by attempts to generate a too large workbook.

In order to provide the user with a quick problem determination tool, both XLGEN and XLSGEN have been added an error routine that records some basic error information on a database file.
To display the error collection you just run command hssfcgi/dsperrs.

  1. You receive a first screen listing all the errors collected in a time descending sequence (the most recent error is listed first). For each error you are displayed:
    1. The time when the error occurred
    2. The name of the workbook being created (parameter OUTSTMF)
    3. The name of the last worksheet saved in the workbook
    4. The total number of worksheet rows in the workbook
    5. The name of the worksheet being processed when the error occurred
    6. The number of the worksheet row being written when the error occurred (this is a row sequence number within the worksheet)
    7. The number of row-cell being written when the error occurred (this is a cell sequence number within the worksheet row)
    8. The absolute number of the row being written when the error occurred (this is a row sequence number within the workbook)
    9. The absolute number of the cell being written when the error occurred (this is a cell sequence number within the workbook)
    - When items 1f and 1g are different from zero, most likely the error occurred while trying to write that specific cell. Either the cell contains an unsupported value, or a memory constraint did not allow the cell to be added.
    - When items 1f and 1g are both zero, most likely the error occurred while trying to add the worksheet to the workbook, and that is usually a memory problem (the workbook is too big).
  2. If you select the error with a 9, you are displayed a second screen telling you more:
    1. The procedure, RPG program and RPG module names. XLSGEN_1 and XLSGEN_2 are the programs run by command XLSGEN. XLSXGEN is the program run by command XLSXGEN.
    2. The number of the source statement in the RPG program module that was tried to be executed when the error occurred. This piece of information is generally for the developer, unless you are willing to check the source code.
    3. The RPG IV routine that was in execution when the error occurred. This is generally the name of the Scott Klements HSSFR4 subprocedure invoked by the source statement 2b (Scott Klements subprocedures are the ones interfacing Java). For instance, the name write refers to a subprocedure which writes the spreadsheet into the workbook.
      The names hssf_text and ss_text refer to subprocedures that write a text cell into a spreadsheet row.
      The names hssf_num and ss_num refer to subprocedures that write a numeric cell into a spreadsheet row.
      The names hssf_date and ss_date refer to subprocedures that write a date cell into a spreadsheet row.
      The names hssf_formula and ss_formula refer to subprocedures that write a formula cell into a spreadsheet row.

7-Creating an XLS / XLSX stream file from a program

That is documented in the next page.


Footnotes
 (♣)  Unluckily, keywords PRINT-ORIENTATION, FIT-WIDTH, FIT-HEIGHT, PRINTS-SCALE, PAPER-SIZE and BREAK work only for spreasheets with extension XLS (MS Office 2003), they do not work on spreadsheets with extension XLSX (MS Office 2007).
 (♣♣)  Keyword SETRTL (Set display from right to left) requires at least V5R4 (POI 3.6) and works only for spreasheets with extension XLS (MS Office 2003).