iSeries home   |   Easy400     |   CGIDEV2     |   MMAIL  
Public-Source
 
Introduction
 XLSGEN
Program development
TABLEXLS
TABLEXLS2
TABLEXLS Banners
TABLEXLS includexml
Right to left
XLSUPD
Scott's demos
 
Appendixes:
Java products
Alternate COLHDG
JVMSTARTUP
Questions & Answers
 
 Display the Change Log
 
 PDF of this tutorial
 
 Download
 
 

 
 
HSSFCGI
The XLSGEN language

 


1- 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 written in an IFS stream file and input-ed

  • either to command HSSFCGI/XLSGEN (XLS Generator):

                                 XLS generator (XLSGEN)
            
     Type choices, press Enter.
    
     Input XML stream file  . . . . . INPSTMF                                                 
          
     Output XLS stream file . . . . . OUTSTMF                                                 
          
     Reformat input XML stream file   XMLREFMT    *NO           *YES, *NO
     XML stream file diagnostics  . . XLSGENDIAG  *NO           *NO, *YES
     Force streamfiles CCSID to 819   FORCE819    *YES          *YES, *NO
     Add Sheets to existing WkBook  . ADDTO       *NO           *NO, *YES 
     Decimal positions  . . . . . . . DECPOS      *AUTO         0-9, *AUTO, *NOC
     Display zero values  . . . . . . ZERODSP     *YES          *YES, *NO
     Negative numbers format  . . . . NEGFMT      *A            *A, *B, *C
     No-thousand-separator columns  . NOTHSSEP    *NONE         1-999, *ALL, *NONE
                    + for more values                
     Format all date cells as . . . . CVTDATE     *NO           *NO, *MDY, *DMY, *YMD...
     Null date display option . . . . NULLDATE    *SAME         *SAME, *BLANK, *SKIP
     Format all time cells as . . . . CVTTIME     *NO           *NO, *HMS, *ISO, ...
     Null time display option . . . . NULLTIME    *SAME         *SAME, *BLANK, *SKIP
     Skip blank cells . . . . . . . . SKPBLANK    *NO           1-999, *NO, *YES
                    + for more values                 
     Initial rows to be frozen  . . . FROZEN      0             Number
     ColHdg background color  . . . . HDGCOLOR    *NONE                
     Colhdg font size . . . . . . . . HDGSIZE     '1'           1, 2, 3 
     Colhdg font bold-weight  . . . . HDGBOLD     *NO           *YES, *NO   
     Default cell colors:             DFTCELLCOL
       Font color . . . . . . . . . .             BLACK                
       Background color . . . . . . .             WHITE                
     Cell border  . . . . . . . . . . CELLBORDER  *THIN         *NONE, *THIN, *MEDIUM...
     Generate log . . . . . . . . . . LOG         *NO           *YES, *NO
     Generate XLSGEN report . . . . . XLSGENRPT   *YES          *YES, *NO
     Sizing parameters  . . . . . . . SHEETSIZE
       Max no. of cells per workbook              2300000       10000-2300000
       Max no. of sheets per workbook             *AUTO         *AUTO, number
       Max no. of rows per sheet                  10000         1 - 200000
     Run identifier . . . . . . . . . RUNID       *NONE                     
     Run it in a submitted job  . . . SBMJOB      *NO           *YES, *NO
     Job queue  . . . . . . . . . . . JOBQ        QSYSNOMAX     Name        
       Library  . . . . . . . . . . .               *LIBL       Name, *LIBL
    Figure 1a - Command HSSFCGI/XLSGEN


  • or to command HSSFCGI/XLSXGEN (XLSX Generator):

                                XLSX generator (XLSXGEN)
            
     Type choices, press Enter.
    
     Input XML stream file  . . . . . INPSTMF                                                 
          
     Output XLSX stream file  . . . . OUTSTMF                                                 
          
     Reformat input XML stream file   XMLREFMT    *NO           *YES, *NO
     XML stream file diagnostics  . . XLSGENDIAG  *NO           *NO, *YES
     Force streamfiles CCSID to 819   FORCE819    *YES          *YES, *NO
     Add Sheets to existing WkBook  . ADDTO       *NO           *NO, *YES 
     Decimal positions  . . . . . . . DECPOS      *AUTO         0-9, *AUTO, *NOC
     Display zero values  . . . . . . ZERODSP     *YES          *YES, *NO
     Negative numbers format  . . . . NEGFMT      *A            *A, *B, *C
     No-thousand-separator columns  . NOTHSSEP    *NONE         1-999, *ALL, *NONE
                    + for more values                
     Format all date cells as . . . . CVTDATE     *NO           *NO, *MDY, *DMY, *YMD...
     Null date display option . . . . NULLDATE    *SAME         *SAME, *BLANK, *SKIP
     Format all time cells as . . . . CVTTIME     *NO           *NO, *HMS, *ISO, ...
     Null time display option . . . . NULLTIME    *SAME         *SAME, *BLANK, *SKIP
     Skip blank cells . . . . . . . . SKPBLANK    *NO           1-999, *NO, *YES
                    + for more values                 
     Initial rows to be frozen  . . . FROZEN      0             Number
     ColHdg background color  . . . . HDGCOLOR    *NONE                
     Colhdg font size . . . . . . . . HDGSIZE     '1'           1, 2, 3 
     Colhdg font bold-weight  . . . . HDGBOLD     *NO           *YES, *NO
     Default cell colors:             DFTCELLCOL
       Font color . . . . . . . . . .             BLACK                
       Background color . . . . . . .             WHITE                
     Cell border  . . . . . . . . . . CELLBORDER  *THIN         *NONE, *THIN, *MEDIUM...
     Generate log . . . . . . . . . . LOG         *NO           *YES, *NO
     Generate XLSGEN report . . . . . XLSGENRPT   *YES          *YES, *NO
     Sizing parameters  . . . . . . . SHEETSIZE
       Max no. of cells per workbook              2000000       10000-2000000
       Max no. of sheets per workbook             *AUTO         *AUTO, number
       Max no. of rows per sheet                  10000         1 - 200000
     Run identifier . . . . . . . . . RUNID       *NONE                     
     Run it in a submitted job  . . . SBMJOB      *NO           *YES, *NO
     Job queue  . . . . . . . . . . . JOBQ        QSYSNOMAX     Name        
       Library  . . . . . . . . . . .               *LIBL       Name, *LIBL
    Figure 1b - Command HSSFCGI/XLSXGEN


Restrictions

  • A maximum of 256 columns (cells) per row is supported.

Command parameters

  • Input XML stream file (INPSTMF) - The IFS stream file containing XML-like XLSGEN script
  • Output XLS/XLSX stream file (OUTSTMF) - The EXCEL dstream file top be generated. Extension .XLS or XLSX must be specified.
  • Reformat input XML stream file (XMLREFMT) - You may optionally request your XML-like input stream file be reformatted in order to perfectly match the expected format. This could avoid potential problems in processing it.
    However, this reformatting is rather time expensive and, if you have a single spreadsheet in it, the response time is usually twice as much.
    Select one of the following:
    • *NO to skip the reformatting of the XML file
    • *YES to perform the reformatting of the XML file.
    Note. Should you want the XML reformatting always be done regardless what specified in parameter XMLREFMT, execute command HSSFCGI/XMLREFMT FORCE(*YES) .
    To reset this action, execute command HSSFCGI/XMLREFMT FORCE(*NO) .
  • XML stream file diagnostics (XLSGENDIAG) - Specifies whether a syntax check of the generated XLSGEN XML-like script is desired.
    If *YES and any errors found, the process is abnormally terminated and provides information about the errors found.
  • Force streamfiles CCSID to 819 (FORCE819) - In IBM® i 7.3 and prior releases, stream files created in the QSHELL environment are assigned a single-byte CCSID (819). Starting with IBM® i 7.4, stream files created in the QSHELL environment are instead assigned UTF-8 CCSID 1208.
    See http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzaq9/rzaq9osPASEccsid.htm.
    If IBM® i 7.4 or higher, select one of the following:
    • *YES - XLSGEN/XLSXGEN generated stream files are assigned CCSID 819.
    • *NO - XLSGEN/XLSXGEN generated stream files are assigned CCSID 1208 (UTF-8).
    Note. This parameter has no effect if the job Java Virtual Machine (JVM) was already started.
  • Add Sheets to existing WkBook (ADDTO) - 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) - 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.
  • Negative numbers format (NEGFMT) - Format of the negative decimals numbers.
    Three options are provided. The following example is used: negative number -1234.56
    • *A (default value)- The negative number is represented with the minus sign in front of it and in the cell default color (usually black): -1234.56
    • *B - The negative number is represented with the minus sign in front of it and in red color: -1234.56
    • *C - The negative number is represented within parenthesis and in red color: (1234.56) .
  • No-thousand-separator columns (NOTHSSEP) - Numeric columns where the thousand separator must not be used.
    Enter one of the following:
    • *NONE (default value) - No such numeric columns
    • *ALL - All numeric columns
    • Up to 50 column numbers referring to columns where the thousand separator character must not be used.
  • 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. 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)
  • Null date display option (NULLDATE) - How null date fields should by displayed in a generated Excel worksheet.
    A "null date" is the default value for date fields. Its value in *ISO format (YYYY-MM-DD) is 0001-01-01.
    The following display options are available:
    • *SAME - This generates an Excel cell containing exactly that date formatted according to parameter CVTDATE.
    • *BLANK - This generates a text cell containing one blank.
    • SKIP - This skips the generation of that date cell.
    Note: Options *BLANK and SKIP result to the same display, though the cell contents are different.
  • Format all time cells as (CVTTIME) - Format all the time cells of the spreadsheet in the same way. Leave *NO, if you want each time cell in the spreadsheet be formatted according to the related FORMAT parameter specified in the XLSGEN control statements. Available conversion formats are:
    • *HMS (hh:mm:ss)
    • *ISO (hh.mm.ss)
    • *USA (hh:mm AM or hh:mm PM)
    • *EUR (hh.mm.ss)
    • *JIS (hh:mm:ss)
  • Null time display option (NULLTIME) - How null time fields should by displayed in a generated Excel worksheet.
    A "null date" is the default value for date fields. Its value in *ISO format (hh.mm.ss) is 00.00.00 .
    The following display options are available:
    • *SAME - This generates an Excel cell containing exactly that time formatted according to parameter CVTTIME.
    • *BLANK - This generates a text cell containing one blank.
    • SKIP - This skips the generation of that date cell.
    Note: Options *BLANK and SKIP result to the same display, though the cell contents are different.
  • Skip blank cells (SKPBLANK) - Whether blank text cells must not be generated.
    • Select *NO, to keep blank text cells as they are.
    • Select *YES, to skip all blank text cells.
    • Enter up to 50 numbers of columns where blank text cells should not be generated.
  • Initial rows to be frozen (FROZEN) - 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) - 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.
  • Colhdg font size (HDGSIZE) - ColHdg font size (HDGSIZE) - Font size to be assigned to column header text cells.
    Select one of the following:
    • 1 - Small font size (default value)
    • 2 - Regular font size
    • 3 - Large font size
  • Colhdg font bold-weight (HDGBOLD) - Whether column header font should be bold-weight. Default value is *NO .
  • Default cell colors (DFTCELLCOL) - Sets the default colors (font and background) for the cells of the spreadsheet. Default cell colors do not affect column header cells.
  • Cell border (CELLBORDER) - What type of cell borders should be generated. Select one of the following:
    • *NONE - Do not generate cell borders
    • *THIN or *NO - Generate thin cell borders
    • *MEDIUM or *YES - Generate medium size cell borders
    • *DOUBLE - Generate double cell borders.
    • *COLOR - Generate medium size cell borders only for cells with a foreground color other than BLACK or a background color other than WHITE.
    • *FCOLOR - Generate medium size cell borders only for cells with a foreground color other than BLACK.
    • *BCOLOR - Generate medium size cell borders only for cells with a background color other than WHITE.
  • Generate log (LOG) - If 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 a process failure.
  • 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.
    For more information about this feature see command RPTXLSGEN.
  • Sizing parameters (SHEETSIZE) - Three elements:
    1. Maximum number of cells per Excel workbook
    2. Maximum number of worksheets per Excel workbook
    3. Maximum number of rows per worksheet
    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.
    1. The "first element" of this parameter makes sure that the number of cells upper 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.
    2. The "second element" tells the maximum number of spreadsheets that a single workbook can hold. As soon as this number is exceeded, a new workbook is created through a submitted synchronized batch job.
      • If you specify *AUTO for this "second element", this maximum number of spreadsheets per workbook is taken from the number of worksheets declared in the XML-like XLSGEN input stream file specified in parameter INPSTMF.
      • If the total number of cells can fit in a single workbook, all the declared spreadsheets are generated in a single workbook.
      • If the total number of cells cannot fit in a single workbook, multiple workbooks are generated through sincronized batch jobs. In this case each book contains a single spreadsheet.
    3. The "third element" tells the maximum number of rows a spreadsheet can hold As soon as this number is exceeded, a new spreadsheet is created.
      Note: XLS spreadsheets are always generated with a maximum of 65535 rows. This is a Microsoft Excel restriction for XLS spreadsheets.
  • 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 synchronized submitted batch 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.
  • Job queue (JOBQ) - If you specify SBMJOB(*YES), this is the job queue where the synchronized job is submitted.

Usually, an initial XLSGEN is written manually and input-ed to command XLSGEN or XLSGENX to create a prototype spreadsheet.
You may instead write a program that

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



2- Performance reports

Performance data are collected in library QTEMP for all XLSGEN/XLSXGEN/TABLEXLS/TABLEXLS2 commands run in a job.
To display such data, you have two ways:
  1. Use command HSSFCGI/RPTXLSGEN (Report about Excel workbooks). This command reports -for each XLSGEN/XLSXGEN/TABLEXLS/TABLEXLS2 commands run in a job-
    • The name and the type of a generated Excel Workbook
    • The time used to generate this Excel Workbook
    • The time used to create the XML-like input stream file
    • The time used to rework the XML-like input stream file
    • For each spreadsheet generated in this workbook
      • the spreadsheet name
      • the number of rows, columns and cells generated
      • the time taken to generate it
    • The time taken to generate the workbook.
  2. Have command HSSFCGI/RPTXLSGEN automatically run by specifying parameter XLSGENRPT(*YES) in a XLSGEN/XLSXGEN/TABLEXLS/TABLEXLS2 command.
The following is an example of a RPTXLSGEN report generated after running command
XLSGEN INPSTMF('/tmp/prova.txt') OUTSTMF('/tmp/prova.xls'):
    EXCEL WORKBOOKS GENERATED FROM JOB 229998/GBPER/QPADEV0002
    .                                                                
    === Run no. 8 ================================================== 
    Workbook . . . . . . . . . . . . . . /tmp/prova.xls              
    Type . . . . . . . . . . . . . . . . XLS                         
    TABLEXLS  XML script created in  . .                .000 seconds 
    XMLFORMAT XML script reformatted in               14.515 seconds 
    .                                                                
    ---------------------------------------------------------------- 
    - Spreadsheet no. 1  . . . . . . . . Parametri                   
    No. of columns . . . . . . . . . . .                   2         
    No. of rows  . . . . . . . . . . . .                   7         
    No. of cells . . . . . . . . . . . .                  13         
    No. of cells last row  . . . . . . .                   2         
    Worksheet created in . . . . . . . .                .818 seconds 
    Date & time created  . . . . . . . . 2022-09-24 16.23.04         
    .                                                                
    ----------------------------------------------------------------
    - Spreadsheet no. 2  . . . . . . . . BoM                        
    No. of columns . . . . . . . . . . .                  10         
    No. of rows  . . . . . . . . . . . .                  80        
    No. of cells . . . . . . . . . . . .                 791        
    No. of cells last row  . . . . . . .                  10        
    Worksheet created in . . . . . . . .               2.547 seconds
    Date & time created  . . . . . . . . 2022-09-24 16.23.06        
    ====================================                            
    This run was performed in  . . . . .              24.086 seconds
    ================================================================
Figure 1c - Sample RPTXLSGEN report



3- 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">
     <data type="skip"></data>
    </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.



4- 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
Must be uniquie within the worksheet and
cannot contain any of the following 7 characters
  \ / * ? : [ ]
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 Assign medium borders to this single cell
BORDER2 Assign double borders to this single cell
NOBORDER Assign no 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 - left aligned 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
  • PCENT - right aligned percentage
  • DATE - centered date
  • TIME - right aligned time
  • 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>
  • SKIP - no cell is generated in this column (the cell appears as containing nothing)
Examples:
  • <data type="largeTextC" wrap>TODAY OFFERINGS</data>
  • <data type="number">3.14</data>
  • <data type="number" dec-pos="3">3.14</data>
  • <data type="pcent" dec-pos="2">0.446</data>
  • <data type="date" format="*mdy">2019-10-27</data>
  • <data type="picture" width="187"> ... </data>
  • <data type="formula" ...> ... </data>
  • <data type="skip"></data>

Formats available for <data type="date" format="..." >:
  • format="*mdy"
  • format="*dmy"
  • format="*ymd"
  • format="*jul"
  • format="*iso"
  • format="*usa"
  • format="*eur"
  • format="*jis"

Formats available for <data type="time" format="..." >:
  • format="*hms"
  • format="*iso"
  • format="*usa"
  • format="*jul"
  • format="*eur"
  • format="*jis"
A Formula cell can have different data-type formats, depending on the expected result of its formula. Supported data types are TEXT, NUMBER, DATE and TIME. Besides, numbers, dates and time values can have a different format.
This is why a type="formula" needs
  1. a second data type parameter, FTYPE which defines the data type of the resulting value. It can be:
    TEXT, NUMBER, PCENT, DATE or TIME. Example: <data TYPE="formula FTYPE="date" ...>
    All the 8 ...TEXT.. possibilities are supported, from TEXT to SMALLTEXTR.
  2. When ftype="number" or ftype="pcent" specified, then you also may specify the number of decimal positions through parameter DEC-POS.
    Example: <data TYPE="formula FTYP="number" dec-pos="3">
    Example: <data TYPE="formula FTYP="pcent" dec-pos="2">
  3. When ftype="date", you must also specify its FORMAT parameter.
    Example: <data type="date" format="*mdy">
  4. When ftype="time", you must also specify its FORMAT parameter.
    Example: <data type="time" format="*eur">
Note. Hyperlinks are supported as formula special cases.
Examples:
  • <data type="formula" ftype="largeTextC" wrap>...</data>
  • <data type="formula" ftype="number" dec-pos="0" f-color="red">...</data>
  • <data type="formula" ftype="pcent" dec-pos="2" bold>...</data>
  • <data type="formula" ftype="date" format="*iso">...</data>
  • <data type="formula" ftype="time" format="*usa">...</data>
HALIGN="value" Overrides the horizontal alignement implicit in the TYPE keyword.
Possible values:
  • LEFT
    left-aligned horizontal alignment
  • CENTER
    center-aligned horizontal alignment
  • RIGHT
    right-aligned horizontal alignment
Example:
  • <data type="number" halign="left">
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">
ROTATE="value" Defines the degrees of rotation for the text in the cell. Example:
  • <data type="Column-Header" rotate="90" halign="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">
NEGFMT="value" Applies only to TYPE="NUMBER" and TYPE="LARGENUMBER".
Defines the format of a potential negative number.
Overrides - on a single cell - the XLSGEN / XLSXGEN parameter NEGFMT.
Possible values:
*Aexample: -1,234.56
*Bexample: -1,234.56keyword f-color has no effect
*Cexample: (1,234.56)keyword f-color has no effect
Example:
  • <data type="number" negfmt="*C">
FORMAT="value" Defines the format of a date or time spreadsheet cell.
Possible values:
type="date"
format="..."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
type="time"
format="..."cell format
*HMShh:mm:ss
*ISOhh.mm.ss
*USAhh:mm AM or
hh:mm PM
*EURhh.mm.ss
*JIShh:mm:ss
Examples:
  • <data type="date" format="*MDY">
     2019.02.28
    </data>
  • <data type="time" format="*USA">
     15.45.23
    </data>
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>
HIDE Applies only to the text data types NUMBER, LARGENUMBER and FORMULA. It tells whether the numeric value of a cell must be hidden. Example:
  • <data type="number" hide>
(data value) Data value must be written between the tag <data ... > and the tag </data> .
Figure 4 - Tags and keywords
without Overlay with Overlay
Figure 5A - Without OVERLAY keyword
(<cell hspan="3" vspan="7">)
See the sample XLSGEN stream file /hssfcgi/tmp/zzznov.txt
Figure 5B - With OVERLAY keyword
(<cell hspan="3" vspan="7" overlay>)
See the sample XLSGEN stream file /hssfcgi/tmp/zzzov.txt


5- More on ...

  • Null cells
    A null cell is a cell is a cell containing no data.
    The following creates a null cell:
    <cell><data type="skip"></data></cell>
    A null cell is not generated. In other words, the cell in that position (column number, row number) of the spreadsheet is left vacant (missing).
  • Null rows
    A null row is a row is containing no cells.
    The following creates a null row:
    <row><cell><data type="skip"></data></cell></row>.
  • 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)) and a formula cell, 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>
  • DYNAMIC DATE AND TIME for TEXT data types
    Text data (data types TEXT, TEXTC, ..., SMALLTEXTR) may contain the following dynamic variables, which are filled in at execution time:
    • &&DATE; that is replaced by the current ISO date in format YYYY-MM-DD
    • &&TIME; that is replaced by the current time in format HH.MM
    A useful example would be an ALTCOLHDG stream file like the following:
    <row>
    <data type="LargeTextC" b-color="SEA_GREEN" f-color="WHITE">
       Example of TABLEXLS column headers%LF customized via parameter ALTCOLHDG%LF &&DATE; &&TIME;
    </data>
    </row>
  • NUMBERS
    Numeric values are represented as follow:
    <data type="number" dec-pos="...">
     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.
  • PERCENTAGES
    In Excel, when you format a cell as percent, any numeric value set in that cell is displayed as multiplied by 100 and followed by the percent sign %.
    For instance, if you set in that cell value 0.2512, it shows up as 25.12% .
    An Excel percent cell is just a number cell with a special editing.
    In XLSGEN you may create / update percent cells in the same way as you would do for numeric cells, by just specifying data type "PCENT" instead of "NUMBER".
    Example: the following XLSGEN code
    <cell><data type="pcent" dec-pos="2">0.2758</data></cell>
    creates a numeric cell showing value 27.58% right aligned.
    Note 1. Also formula cells may be defined as containing a percentage value. Example:
    <cell><data type="formula" ftype="pcent" dec-pos="2">B1/A1</data></cell>
    Note 2. For a living example of generating an Excel spreadsheet generating Formula cells containing percentages, run command
    HSSFCGI/XLSGEN INPSTMF('/hssfcgi/examples/percent.txt') OUTSTMF('/tmp/percent.xls') .
  • DATE VALUES
    Date values 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>
  • TIME VALUES
    Time values must always be written in the ISO format hh.mm.ss .
    The desired time format for the resulting spreadsheet time cell must be specified by the keyword FORMAT=*xxx .
    Example:
    <data type="time" format="*usa">
     21.53.37
    </data>
  • FORMULAS
    • Any formulas supported by Excel 2003 can be used. Examples:
      1. <data type="formula" ftype="number" dec-pos="2">
         AVERAGE(B34:J34)
        </data>
      2. <data type="formula" ftype="pcent" dec-pos="2">
         B34/J34
        </data>
      Warnings:
      1. If for any reason POI finds out that a formula cannot be supported, the following diagnostic message is sent to the joblog:
        FORMULA cell failed to be created in row no. xxx, cell no. yyy, sheet sss, workbook www.
      2. For Excel formulas see this page.
      3. For Excel formulas referring to other worksheets, see this page.
      4. Excel formulas must reference cells that have been already created !!!
    • 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) must 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>
    • Formulas referring to cells of other spreadsheets
      • The spreadsheets referred to in a formula ( example: Sheet1!D5) must already exist in the workbook currently processed.
      • Example: in you are creating a workbook with two spreadsheets, Sheet1 and Sheet2, the attemp to create in Sheet2 a formula cell specifying Sheet1!D5 will fail because the workbook does not yet exist, it is currently being created.
        This problem can be resolved in the following way:
        1. Create a workbook containing just the spreadsheet Sheet1
        2. Then add to the workbook (see parameters ADDTO in commands XLSGEN and TABLEXLS) the spreadsheet Sheet2 containing the formula referring to spreadsheet Sheet1.
    • HyperLinks are supported 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.
  • HSPAN / VSPAN and borders
    When HSPAN and/or VSPAN cell keywords are used to group in a single virtual cell (tecnically an Excel region), there is a problem with bordering the entire group virtual cell: only the topmost and leftmost cell is partially bordered. See Figure 5C.
    Click here to display the XLSGEN script used to create the spreadsheet in Figure 5C.
    We suggest two ways for overcoming this flaw:
    • Use the cell group to display a bordered picture, see Figure 5D. Obviously, this is worth doing only if the text of the group virtual cell is static.
      Click here to display the XLSGEN script used to create the spreadsheet in Figure 5D.
    • Assign to the text of the group virtual cell a dark background color and a light foregound color. See figure 5E.
      Click here to display the XLSGEN script used to create the spreadsheet in Figure 5E.
    Figure 5C - The cell group border is only on the first cell Figure 5D - Using a bordered picture Figure 5E - Using background and foreground colors


6- XLSGEN diagnostic tool

If you are new with XLSGEN language, or when some new XLSGEN script of yours does not provide the expected result, you may need some diagnostic tool to find out if your XLSGEN script contains some severe errors.
You can do that by using command XLSGENDIAG:
                      XLSGEN diagnostic report (XLSGENDIAG)                     
                                                                                
 Type choices, press Enter.                                                     
                                                                                
 Input XML stream file  . . . . . INPSTMF      > '/hssfcgi/examples/1.txt'      
                   
 Display report . . . . . . . . . DISPLAY      > *YES        *YES, *NO
                                                                                
                
Command XLSGENDIAG
  • Input XML stream file (INPSTMF) - The XML-like XLSGEN script stream file that would be used as input to the XLSGEN ot to the XLSXGEN command
  • Display report (DISPLAY) - Whether the generated diagnostic report should be displayed.
The diagnostic report is always generated. If some severe errors reported, in order to stop any further processing of the XLSGEN script, an escape message is issued.
If the report is not displayed, you may still display it - in the current job - by entering command DSPDIAGRPT. Use command - in the current job - DLTDIAGRPT to delete the report.

Should you need to automatically run this diagnostic tool when executing command XLSGEN, XLSXGEN, TABLEXLS or TABLEXLS2, just specify *YES in the XLSGENDIAG parameter of that command.

7- Large worksheets

POI 3.2 (xls) and POI 3.6 (xlsx) perform Excel workbook creation in memory (no disk swap supported). This may create problems with large worksheets: when the number of created elements (rows and cells) becomes too large, some more and more performance degradation takes place (research for free memory slots becomes more and more difficult), and the job may enter a disabled Java wait status. When no such a wait status, the saving of the workbook from memory to an IFS stream file may then take quite a number of minutes.
Large worksheets are the ones with a number of rows greater than 65535 or with more than 2.3 million cells (This value was experimentally estalished by running XLSGEN command with parameter SBMJOB(*YES), in such a way to have the maximum job memory availability).
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 maximum number of spreadsheets per workbook.
The spreasheet generation process goes in this way:
  • When the maximum number of allowed cells per workbook is exceeded, or
    when the maximum number of allowed worksheets per book is exceeded,
    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 requested 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 3,000 rows, 100 columns each, for a total of 300,000 cells.
  2. Execute command HSSFCGI/XLSGEN INPSTMF('/tmp/bigXml.xml') OUTSTMF('/tmp/bigXml.xls') SHEETSIZE(30000)
    • The job issuing the command creates a workbook (named /tmp/bigXml.xls ) containing a single 300 rows worksheet.
      A chain of jobs automatically submitted creates other 9 workbooks (named /tmp/bigXml.xls and /tmp/bigXml_NBR1.xls to /tmp/bigXml_NBR10.xls), each one containing a single 300 rows worksheet.
We have run this test on an IBM Power System Model M25 and the test took about 13 minutes.
The resulting performance figures are:
  • 22,388 cells created in a minute
  • 373 cells created in a second.
A sample quick solution
If your application cannot just stand such a heavy and long time responsive process, you may settle for something quicker by using a Comma-Separated Values (CSV) solution.
Command DB2XTOOLS/DB2CSV can easily do that for you.


8- 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).


9- 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.

10- 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, PRINT-SCALE, PAPER-SIZE and BREAK work only for spreadsheets 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).