In the previous pages we have described
- A special XML-like script language that allows command XLSGEN to generate an XLS or XLSX Excel workbook
- How you can produce such an XML-like script from a program.
As the next step, what about a general program that would
- read whatever database files
- generate from them an XLSGEN XML-like script
- invoke command XLSGEN or XLSXGEN to create an Excel workbook containing one or more spreadsheets ?
We have developed such a program for you in library HSSFCGI, and you can run it through command TABLEXLS.
Generate an XLS/XLSX from db files (TABLEXLS)
Type choices, press Enter.
From database files . . . . . . FROMFILE Name
Library . . . . . . . . . . . *LIBL Name, *LIBL
+ for more values
From members . . . . . . . . . . FROMMBR *ALL Name, generic*, *FIRST, *ALL
+ for more values
To .xml stream file . . . . . . TOXML *AUTO
To .xls or .xlsx stream file . . TOXLS
Edit the .xml stream file . . . EDITXML *NO *YES, *NO
Add Sheets to existing WkBook . ADDTO *NO *NO, *YES
Sheet names . . . . . . . . . . SHEETNAME *MBRNAME Name, *MBRNAME
Display sheets right to left . . SETRTL *NO *YES, *NO
Banner identifier . . . . . . . BANNERID *NONE 0-99999, *NONE
Alternate Column Headings stmf ALTCOLHDG
Column headings option . . . . . COLHDGOPT 1 1, 2, 3
ColHdg background color . . . . HDGCOLOR *NONE
Default cell colors: DFTCELLCOL
Font color . . . . . . . . . . BLACK
Background color . . . . . . . WHITE
Cell border . . . . . . . . . . CELLBORDER *NO *NO, *YES, *COLOR, *FCOLOR...
Frozen pane: FROZEN
no. of frozen initial rows . . 1 0-99999
no. of frozen initial columns 0 0-99999
Display zero values . . . . . . ZERODSP *YES *YES, *NO
Decimal positions . . . . . . . DECPOS *AUTO 0-9, *AUTO, *NOC
Negative numbers format . . . . NEGFMT *A *A, *B, *C
Convert all date fields to . . . CVTDATE *NO *NO, *MDY, *DMY, *YMD...
Skip blank cells . . . . . . . . SKPBLANK *NO 1-999, *NO, *YES
+ for more values
Ignore fields . . . . . . . . . IGNFLDS *NONE Name, *NONE
+ for more values
HyperLink fields: LINKFLDS
Link field . . . . . . . . . . Name
Text field . . . . . . . . . . Name
+ for more values
Display pictures . . . . . . . . PICTURES *NO *YES, *NO
VSPAN for picture cells . . . . VSPAN 7 Number
Pictures width in pixel . . . . WIDTH 187 Number
Paper size . . . . . . . . . . . PAPERSIZE A4_PAPERSIZE
Print-orientation . . . . . . . PRTORIENT *PORTRAIT *PORTRAIT, *LANDSCAPE
'Print header' . . . . . . . . *MBRTXT
Show also on worksheet . . . . *YES *YES, *NO
Generate log . . . . . . . . . . LOG *NO *YES, *NO
Generate XLSGEN report . . . . . XLSGENRPT *NO *YES, *NO
Download Excel stmf if CGI job . DOWNLOAD *YES *YES, *NO
Run XLSGEN in a submitted job . SBMJOB *NO *YES, *NO
Worksheet sizing: SHEETSIZE
Max no. of cells per workbook 2300000 100000-2300000
No. of sheets per workbook . . 1 1-100
Run identifier . . . . . . . . . RUNID *NONE
Sheet fit-width (nbr. pages) 0 0-99999
Sheet fit-height (nbr. pages) 0 0-99999
Print-scale . . . . . . . . . . PRTSCALE 100 10-400
|Figure 5 - Command HSSFCGI/TABLEXLS|
This command generates - from one or more database files - an Excel workbook on the IFS. The workbook is either type XLS or XLSX depending on the extension specified in the TOXLS stream file name.
The generation process goes through three main steps:
- For each database file a so-called AD-HOC ILE-RPG program is generated in library HSSFCGIDTA
(This is why to run command TABLEXLS on an IBMi box you need the ILE-RPG compiler).
These programs are used to read the database files. An XLSGEN
script (an XML-like script, input to command XLSGEN or XLSXGEN) is then
generated while reading the database files.
- If you specify TOXLS(*NONE), the process stops here. You can then display,
edit or update with some program of yours the XML-like XLSGEN script,
that you will later on process with command XLSGEN or XLSXGEN under your control.
- If you specify for TOXLS a stream file name, then
the TABLEXLS program goes on and processes the XML-like XLSGEN script through command XLSGEN or command XLSXGEN
(according to the extension of the TOXLS stream file), thus generating an XLS or XLSX stream file ready for Excel.
|Note on date fields|
Our utility cannot tell whether a database record field contains a date, unless the field definition supports the DDS keyword DATFMT (date format).
If the DATFMT keyword is found at field level, then we are sure that the field contains a date, and the value of the DATFMT keyword tells us
what is the date format that should be displayed.
DDS data type L is used to define date fields on physical files, and these type of fields
support the DATFMT keyword. Also logical file signed and packed fields based on physical date fields should support the DATFMT keyword, according to the IBM System i DDS Manual.
Only the database fields supporting the DATFMT keyword are date fields for the TABLEXLS utility. They are converted to spreadsheet date cells
and are edited according to their DATFMT values.
Other fields containing date values, such as plain numeric or character fields, are not detected as date fields by the TABLEXLS utility,
and are converted to spreadsheet numeric or text cells without any date formatting.
Should you need to have some character or numeric fields be converted to spreadsheet date cells,
you must use parameter TOXLS(*NONE) to generate just the XML-like XLSGEN script, change the
<data type="..."> declarations to <data type="date" format="..."> declarations,
then process the XML-like script with command XLSGEN or XLSXGEN.
- From database files (FROMFILE) - Qualified names of one or more (up to 50)
database files that will be used to generate the XLS or XLSX Excel workbook.
- From members (FROMMBR) - Up to 50 database file members to be processed.
Each member will be a separate worksheet in the workbook. Allowed single values:
NOTE- If a list of members is provided, but none of such members is found
on a given database file, member *FIRST is assumed for that database file.
- *FIRST - the first or only database file member
- *ALL - all the database file members.
- To .xml stream file (TOXML) - This is the name of the XLSGEN script
(an XML-like) stream file that will be generated, then processed.
- If you specify *AUTO, the name of this stream file will be
assigned by the program, and the stream file will be deleted at
the end of the process.
- If you specify a stream file name of your choice, the stream
file will not be deleted at the end of the process. This option
allows you to manually modify the XLSGEN script and to use it as
input to an XLSGEN command under your control. In this way you
may customize the XLS/XLSX spreadsheet according to your needs.
- To .xls or .xlsx stream file (TOXLS) - This is the name of the XLS stream
file (the workbook) that will be generated by the program through the use of an
An estension .xls or .xlsx is required.
Note: You may specify *NONE instead of a stream file name. If you
do so, command XLSGEN/XLSXGEN is not invoked, and the XLS/XLSX stream file
is not generated. In this way you may edit or process the generated
XML-like stream file, update it to better fit your needs and process
it later on with command XLSGEN or XLSXGEN.
- Edit the .xml stream file (EDITXML) -
If you specified TOXLS(*NONE), and you are running an interactive job, you may ask to edit the XML-like stream file
as soon as it is generated.
- Add Sheets to existing WkBook (ADDTO) -
This parameter specifies whether generated worksheets should be added to an existing workbook or to a new workbook. Enter:
- *NO when the workbook does not already exist or must be re-created from scratch
- *YES to add the worksheet(s) to a workbook already existing.
- Sheet names (SHEETNAME) - Name to be assigned to the generated worksheets. You have two choices:
- Leave the default value *MBRNAME. By doing so, each sheet is assigned the name of the related database a. file member.
- Enter a name of up to 8 characters. By doing so, each sheet is assigned this name followed by a sequence number.
- Display sheets right to left (SETRTL) - Whether the worksheets should be displayed from right to left instead
of left to right.
Note: This parameter works only from release V5R4 on and if the extension of the workbook is xls .
- Banner identifier (BANNERID) - Numeric identifier of a banner to be added at the beginning of each generated worksheet.
When no banner is desired, BANNERDID(*NONE) or BANNERID(0) must be specified.
Banners are defined through command HSSFCGI/WRKBAN .
A banner is a strip of spreadsheet cells, that takes all the columns in some initial rows and is made of the following three elements:
These elements are called "banner properties".
- A picture anchored in the first columns of the spreadsheet
- A title sitting in the remaining columns of the spreadsheet
- A subtitle below the title, in the remaining columns of the spreadsheet.
For detail information and examples of using TABLEXLS banners, see this page.
- Alternate Column Headings stmf (ALTCOLHDG) - As a default, a single spreadsheet column header row would be generated from the database file field descriptions.
An alternate column headings structure, made of one or more spreadsheet rows, defined by the user, can be used only for the first database file.
This alternate column headings structure
Note: Of course, when an ALTCOLHDG stream file is specified, parameter HDGCOLOR (Column headings background color) no longer applies. To obtain
foreground and background colors, XLSGEN keywords f-color and b-color must be specified in the tags of the stream file (XLSGEN script)
specified in this parameter ALTCOLHDG.
- must be defined on a stream file using the XLSGEN tags <row>, <cell>, <data>, </data>, </cell> and </row>.
For a detail example about using this parameter, see this page.
- Column headings option (COLHDGOPT) - Use this option to specify the way the column headings of an Excel spreadsheet are filled in.
Select one of the following:
- 1 - Database field column headings are used if available. Otherwise field text descriptions are used if available. If not, field names are used.
- 2 - Database field text descriptions are used if available. Otherwise field column headings are used if available. If not, field names are used.
- 3 - Field names are used.
- ColHdg background color (HDGCOLOR) - Select one of 47 colors to be used as background color for the cells in the first row of the generated spreadsheet
(Column Headers), or select *NONE to avoid using a background color.
The available colors are listed here.
Column Header cells are always assigned borders, regardless of what is specified in parameter CELLBORDER (see below).
- Default cell colors (DFTCELLCOL) - These are the default cell colors to be assigned to all spreadsheet cells. There are two default cell colors:
Each color can be chosen from a list of 47 colors.
- The first color is the font color for the text to be written in a cell.
- The second color is the background color of the cell.
The available colors are listed here.
- Cell border (CELLBORDER) - Specifies which cells should be given borders. Enter:
- *NO not to generate borders for any cells
- *YES to generate borders for all cells
- *COLOR to generate borders for cells having a background color other than WHITE or a foreground color other than BLACK
- *FCOLOR to generate borders for cells having a foreground color other than BLACK
- *BCOLOR to generate borders for cells having a background color other than WHITE.
- Freeze pane columns and rows (FROZEN) - Number of initial rows and columns to be frozen.
Frozen intial rows and/or columns keep steady while you scroll the spreadsheet.
Note- The generated column headers are all contained within the first row. This is why the default value of the first element is set to 1.
- Display zero values (ZERODSP) - Whether the numeric cells containing a value zero should display a zero or should display nothing. Select:
- *YES (default value) to have the numeric cells containing a zero value displaying a zero
- *NO to have the numeric cells containing a zero value displaying nothing. This option is recommended when a large number of numeric cells may
contain zero values, because this makes user reading much easier.
- Decimal positions (DECPOS) - Number of decimal positions for the numeric cells of the spreadsheet.
- If you specify a number from 0 to 9, all the numeric cells display that number of decimal positions. Thousand delimiters are displayed.
- If you specify *AUTO, each numeric cell displays the number of decimal positions specified in the input XML stream file (parameter INPSTMF). Thousand delimiters are displayed.
- *NOC (no commas) is the same as *AUTO, except that thousand delimiters are not displayed.
- 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) .
- Convert date fields to (CVTDATE) - Whether all database date fields should be converted to a given format. Leave *NO if date field formats are to be
maintained as they are. Available conversion formats are:
- *MDY (mm/dd/yy)
- *DMY (dd/mm/yy)
- *DMYY (dd/mm/yyyy)
- *YMD (yy/mm/dd)
- *YYMD (yyyy/mm/dd)
- *ISO (yyyy-mm-dd)
- *USA (mm/dd/yyyy)
- *EUR (dd.mm.yyyy)
- *JIS (yyyy-mm-dd)
- *JUL (yyyy-ddd)
- Skip blank cells (SKPBLANK) - Use this parameter to establish 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.
- Ignore fields (IGNFLDS) - You may specify up to 50 field names that are to be excluded from the generated Excel spreadsheet.
- Hyperlink fields (LINKFLDS) - You may specify up to 50 pairs of field names to be used for creating hyperlink spreadsheet cells.
Each pair of field names is made up of
You may run a test of this feature by entering command
- The name of a character field containing one of the following link types:
- an URL link, example: http://www.easy400.net
- a MAIL link, example: mailto:email@example.com
- a FILE link, example: c:\mydir\mydoc.doc
- The name of a character field containing a description 2. of the link.
TABLEXLS FROMFILE(hssfcgi/hyper) TOXLS('/tmp/hyper.xls') PRTHDR(*MBRTXT) LINKFLDS((itmlnk itmdes))
- Display pictures (PICTURES) - If the database file contains character fields specifying the names of .PNG or .JPG pictures
(example: /mydir/mypicture1.jpg), you may ask to have these pictures displayed in the spreadsheet instead of just showing their names.
Note- For some information about how pictures are specified in a XLSGEN XML-like script, see this page.
- *YES to display the pictures instead of their names, or
- *NO to display just the stream file names of the pictures, as for any other character field.
- VSPAN for picture cells (VSPAN) - This parameter (vertical span) is required if PICTURES(*YES) is specified.
This parameter is used to specify the height of the pictures and is expressed as the number of spreadsheet rows to be spanned for a picture cell.
The default value is 7 (picture cells are given a height corresponding to 7 spreadsheet rows).
- Pictures width in pixel (WIDTH) - This parameter is required if PICTURES(*YES) is specified.
This parameter is used to specify the width in pixel of the pictures cells.
Please note that a picture width is always expanded to the cell width, which is assessed by the largest cell in the same spreadsheet column.
- Paper size (PAPERSIZE) - 33 different paper sizes are available. Use the command prompt or run command DSPPFM HSSFCGI/PAPERSIZES to display them.
- Print-orientation (PRTORIENT) - Select
- *PORTRAIT to print the worksheet in Portrait mode (no page rotation)
- *LANDSCAPE to print the worksheet in Landscape mode (90 degree rotated page).
- Header (HEADER) - This parameter is used to specify the print header. Two elements are needed:
- Print header - The allowed values are:
- *DFT - The print header displays the qualified database file name
- *FILETXT - The print header displays the database file text description
- *MBRTXT - The print header displays the member text description
- a text description of your own that will apply to all members.
- Show also on the worksheet - Select
- *YES, to display the print header in the first row of the worksheets. Note that *YES is disregarded when *DFT is selected for the print header.
- *NO, not to display the print header on the worksheets.
- Generate log (LOG) - If you specify LOG(*YES), log file XLSGENLOG is generated in library
QTEMP. This log contains an entry for each generated worksheet cell. In case of a failure, by displaying this
log file you can easily establish which was the last spreadsheet cell generated successfully before the failure
- Download Excel stmf if CGI job (DOWNLOAD) - When command TABLEXLS is
executed from a job in subsystem QHTTPSVR (a CGI job), this option
allows to download to the client browser the generated Excel stream
file. Of course, if this is done, no other response from the CGI program
should be sent to the browser.
If command TABLEXLS is issued from a CGI program:
- Leave *YES to have the CGI program sending out the Excel stream file
- Type *NO to avoid downloading the Excel stream file and to have the
CGI program providing its own response to the browser.
- Generate XLSGEN report (XLSGENRPT) - Whether a performance report should be generated once the Excel workbook has been created.
This report accounts for all the workbook creation runs in the current job.
- Run XLSGEN in a submitted job (SBMJOB) - If you specify
SBMJOB(*YES), all the XLSGEN Java process is executed in a batch submitted job.
In this way the settings of the Java Virtual Machine of the bach job cannot conflict with the
JVM settings of the current job.
- Worksheet sizing (SHEETSIZE) - This parameter is made of two elements:
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.
- Maximum number of worksheet cells per Excel workbook
- Number of worksheets per workbook
For .xls workbooks, the maximum manageable number of cells was found to be a little over 2.3 millions.
For .xlsx this number was detected to be a little over 2.0 millions.
The first element of this parameter makes sure that the number of cellsupper limit for a workbook is never exceeded.
As soon as it is reached, the workbook is closed and a new job is submitted to create another workbook.
The second element of this parameter tells in how many worksheets the maximum number of workbook cells should be splitted.
- Run identifier (RUNID) - After creating one or more workbooks from this command, you may want to compress it or them into a zip stream file to
make their delivery easier (for instance, by e-mail).
If you have this need, in this parameter you should enter a case sensitive character string (30 char.s max) that would later on help you
in recalling the generated workbook(s). Example: '1st Quarter Sales' .
To zip that / those workbook(s), you must run command ZIPWKB (Zip Excel workbooks) by specifying the same identifying character string in its RUNID parameter.
Example: ZIPWKB RUNID('1st Quarter Sales') ... .
- Run in a submitted job (SBMJOB) - If you specify SBMJOB(*YES), all the Java process is executed in a batch submitted job.
In this way the settings of the Java Virtual Machine of the bach job cannot conflict with the JVM settings of the current job.
- Print-fit (PRTFIT) - This parameter may be used to fit the printout of a spreadsheet in a given number of pages
This parameter has two elements:
Note- This parameter, when used, inhibits the use of parameter PRTSCALE.
- The first element (Sheet fit-width) tells into how many pages the width of the spreadsheet should be compressed.
For instance, if the width of the spreadsheet usually requires two pages in order to print all the columns of a given spreadsheet row, by specifying 1
in this element you force all the columns of a row to fit in a single page.
- The second element (Sheet fit-height) tells into how many pages the height of the spreadsheet should be compressed.
By leaving both elements set to 0, you are not asking any print compression.
By setting both elements to 1, you are asking the printout to fit in a single page.
- Print-scale (PRTSCALE) - Percent scale to be used for printing. For instance, a value of 75 means that the print scale should be reduced to 75%.
Note- Command TABLEXLS provides, for any generated worksheet, a record in file QTEMP/XLSGENRPT. For the record layout,
see this page.
Command HSSFCGI/RPTXLSGEN reads this file and provides a printout listing all the workbooks, with information about their sheets, generated from the current job.
Command TABLEXLS is for a 5250 black-green screen or for execution within a program.
Then, how to run this command from a WEB browser?
If you have installed the Apache HTTP directives in stream file
just try one of the following URL's:
You will receive the following page:
|Figure 6 - Command TABLEXLS from a WEB browser|
Two options are provided for submitting the TABLEXLS command:
- Press the button "create spreasheet" without checking the check-box "run in a separate batch job".
In this way, the TABLEXLS command is executed from the HTTP server job.
As soon as the spreadsheet has been created, it is sent to the browser for download then it is deleted.
- Press the button "create spreasheet" after checking the check-box "run in a separate batch job".
In this way the HTTP server job submits the execution of the TABLEXLS to a batch job.
Buttons are made available to inquiry about the status of the submitted job.
Once the submitted job completes, a download button shows up so that the spreadsheet can be sent for download to the browser and then deleted.
- The WEB page includes a yellow "demo" button in the bottom left corner. A cycle of three demos is available.
This can be used to get some familiarity with the tool.
You may try now, just use the "demo" button. Press this link to go.
- If, after pressing the "submit" button, command TABLEXLS - launched from the CGI program - fails,
the command in error is displayed at the bottom of the page.
You are suggested to copy, paste and run it from a green screen session in order to receive some diagnostic messages.