|
|
|
|
HSSFCGI
|
|
TABLEXLS: Create Excel workbooks from IBMi database files
|
|
|
|
|
In the previous pages we have described
- A special XML-like script language that allows command XLSGEN to generate an XLS or XLSX Excel workbook
- How you can produce such an XML-like script from a program.
As the next step, what about a general program that would
- 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. |
|
Is TABLEXLS not enough for your needs?
|
To create Excel spreadsheets, command TABLEXLS generates an intermediate XLSGEN script (see the stream file specified in parameter TOXML),
which is then processed by command XLSGEN. That generated XLSGEN script is based on a small subset of the XLSGEN keywords, driven by some of the parameters in command TABLEXLS.
Should you need more spreadsheet features than provided by commnd TABLEXLS, you have to write your XLSGEN script and process it with command XLSGEN.
We suggest that - for learning purposes - you first look at some intermediate XLSGEN script generated by a TABLEXLS of yours by specifying parameter PRINTXML(*YES),
and compare those script statements with the XLSGEN keywords table.
More examples on using XLSGEN scripts are provided in the section Program development.
|
|
1- The TABLEXLS command
Generate an XLS/XLSX from db files (TABLEXLS)
Type choices, press Enter.
From database files . . . . . . FROMFILE Name
Library . . . . . . . . . . . *LIBL Name, *LIBL
+ for more values
*LIBL
From members . . . . . . . . . . FROMMBR *ALL Name, generic*, *FIRST, *ALL
+ for more values
RecordFormat number to process . RCDFMTNBR 1 1-999
To .xml stream file . . . . . . TOXML *AUTO
To .xls or .xlsx stream file . . TOXLS
Force streamfiles CCSID to 819 FORCE819 *YES *YES, *NO
Include also existing XML stmf: INCLUDEXML
XML file to be included . . . *NONE
before spreadsheet body row . *START Number, *START, *END
XML stream file diagnostics . . XLSGENDIAG *NO *NO, *YES
Edit the .xml stream file . . . EDITXML *NO *YES, *NO
Print the .xml stream file . . . PRINTXML *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 0, 1, 2, 3
ColHdg rotation degrees . . . . HDGROTATE 0 0, 45, 90, -45, -90
ColHdg background color . . . . HDGCOLOR *NONE
Colhdg font size . . . . . . . . HDGSIZE '*' 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...
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
No-thousand-separator columns . NOTHSSEP *NONE 1-999, *ALL, *NONE
+ for more values
Convert all date fields to . . . CVTDATE *ISO *ISO, *USA, *EUR, ...
Convert all time fields to . . . CVTTIME *ISO *ISO, *USA, *EUR, ...
Skip blank cells . . . . . . . . SKPBLANK *NO 1-999, *NO, *YES
+ for more values
Skip fields by field test: SKPFLDS
Field name . . . . . . . . . . Character value
Relational operator . . . . . *EQ, *NE, *LT, *GT
Comparison value . . . . . . .
+ 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
Header: PRTHDR
'Print header' . . . . . . . . *MBRTXT
Show also on worksheet . . . . *YES *YES, *NO
Generate log . . . . . . . . . . LOG *NO *YES, *NO
Generate XLSGEN report . . . . . XLSGENRPT *NO *YES, *NO
Download Excel stmf if CGI job . DOWNLOAD *YES *YES, *NO
Run XLSGEN in a submitted job . SBMJOB *NO *YES, *NO
Job queue . . . . . . . . . . . JOBQ QSYSNOMAX Name
Library . . . . . . . . . . . *LIBL Name, *LIBL
Sizing parameters . . . . . . . SHEETSIZE
Max no. of cells per workbook 2000000 10000-2000000
Max bo. of sheets per workbook *AUTO *AUTO, number
Max no. of rows per sheet 10000 1 - 200000
Run identifier . . . . . . . . . RUNID *NONE
Print-fit: PRTFIT
Sheet fit-width (nbr. pages) 0 0-99999
Sheet fit-height (nbr. pages) 0 0-99999
Print-scale . . . . . . . . . . PRTSCALE 100 10-400
|
| | Figure 5 - Command HSSFCGI/TABLEXLS |
This command generates - from one or more database files - an Excel workbook on the IFS. The workbook is either type XLS or XLSX depending on the extension specified in the TOXLS stream file name.
The generation process goes through three main steps:
- 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.
Restrictions
- A maximum of 256 columns (cells) per row is supported.
Note on date fields
Our utility cannot tell whether a database record field contains a date, unless the field definition supports the DDS keyword DATFMT (date format).
If the DATFMT keyword is found at field level, then we are sure that the field contains a date, and the value of the DATFMT keyword tells us
what is the date format that should be displayed.
DDS data type L is used to define date fields on physical files, and these type of fields
support the DATFMT keyword. Also logical file signed and packed fields based on physical date fields should support the DATFMT keyword, according to the IBM System i DDS Manual.
Only the database fields supporting the DATFMT keyword are date fields for the TABLEXLS utility. They are converted to spreadsheet date cells
and are edited according to their DATFMT values.
Other fields containing date values, such as plain numeric or character fields, are not detected as date fields by the TABLEXLS utility,
and are converted to spreadsheet numeric or text cells without any date formatting.
Should you need to have some character or numeric fields be converted to spreadsheet date cells,
you must use parameter TOXLS(*NONE) to generate just the XML-like XLSGEN script, change the
<data type="..."> declarations to <data type="date" format="..."> declarations,
then process the XML-like script with command XLSGEN or XLSXGEN.
|
Command parameters:
- From database files (FROMFILE) - Qualified names of one or more (up to 50)
database files that will be used to generate the XLS or XLSX Excel workbook.
- From members (FROMMBR) - Up to 50 database file members to be processed.
Each member will be a separate worksheet in the workbook. Allowed single values:
- *FIRST - the first or only database file member
- *ALL - all the database file members.
NOTE- If a list of members is provided, but none of such members is found
on a given database file, member *FIRST is assumed for that database file.
- RecordFormat number to process (RCDFMTNBR) - The columns of an Excel spreadsheet can just map the layout of a single record format.
IBM i physical files and most of IBM i logical files are single formatted. However some logical files may be multiple formatted.
Parameter RCDFMTNBR specifies - for all the database files listed in parameter FROMFILE - the sequence number of the record format that is processed for spreadsheet generation.
You must specify RCDFMTNBR(1) to select the first record format, RCDFMTNBR(2) to select the second record format, and so on.
- When a single database file is specified in parameter FROMFILE, parameter RCDFMTNBR may specify value 1 or a value higher than 1.
- On the other side, when more than one database file is specified in parameter FROMFILE, parameter RCDFMTNBR must mandatorily specify value 1.
In other words, this parameters helps in processing a record format other than the first one, provided that a single input database file is specified in parameter FROMFILE.
- To .xml stream file (TOXML) - This is the name of the XLSGEN script
(an XML-like) stream file that will be generated, then processed.
- If you specify *AUTO, the name of this stream file will be
assigned by the program, and the stream file will be deleted at
the end of the process.
- If you specify a stream file name of your choice, the stream
file will not be deleted at the end of the process. This option
allows you to manually modify the XLSGEN script and to use it as
input to an XLSGEN command under your control. In this way you
may customize the XLS/XLSX spreadsheet according to your needs.
- To .xls or .xlsx stream file (TOXLS) - This is the name of the XLS stream
file (the workbook) that will be generated by the program through the use of an
XLSGEN command.
An estension .xls or .xlsx is required.
| |
Note: You may specify *NONE instead of a stream file name. If you
do so, command XLSGEN/XLSXGEN is not invoked, and the XLS/XLSX stream file
is not generated. In this way you may edit or process the generated
XML-like stream file, update it to better fit your needs and process
it later on with command XLSGEN or XLSXGEN.
|
- 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.
- Include also existing XML stmf (INCLUDEXML) -
This parameter may be used to include a group of rows. An initial <row> and an ending </row> statements are required.
If missing, they are automatically added.
It cannot be used to extend the number of columns in an existing row.
This parameter has two elements:
- The name of the XLSGEN (XML-like) stream file to be included
- The number of the generated XLSGEN row before which the inclusion will take place. Special values are:
- *START or 1 to include this stream file "before" the first generated <row> of a spreadsheet.
In other words: just after the automatically generated XLSTABLE column-headers row (if any).
- *END to include this stream file "after" the last generated <row> of a spreadsheet.
NOTE 1 - This parameter may be used to include a banner or / and a trailer to spreadsheets generated from command TABLEXLS.
NOTE 2A - The columns in the added rows should have the data format of the same columns in the existing rows.
You may replace multiple columns over the existing ones using a HSPAN parameter:
this does not affect the layouts of the existing columns.
NOTE 2B - Included rows may have more columns than the existing rows.
NOTE 3 - More information and examples are available at page TABLEXLS includexml.
- 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 will be interrupted, thus saving useless process and providing the information for a quick repair.
- 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.
- Print the .xml stream file (PRINTXML) -
Specify PRINTXML(*YES) to print the intermediate XML-like XLSGEN script that will be used by XLSGEN to generate the Excel workbook.
The spooled filed is named XMLPRINTF and contains two parts:
- The first part (Phase 1) lists the XML-like XLSGEN script created by program TABLEXLS through one of its automatically generated AD-HOC programs.
- The second part (Phase 2) lists the XML-like XLSGEN script converted to canonical format, as expected by program XLSGEN.
Note - PRINTXML(*YES) may generate huge spool files. It should be used only for investigating the intermediate XLSGEN language used to create the Excel workbook.
- 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 30 uppercase, lowecase or mixed 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:
- 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.
These elements are called "banner properties".
For detail information and examples of using TABLEXLS banners, see this page.
- Alternate Column Headings stmf (ALTCOLHDG) - As a default, a single spreadsheet column header row would be generated from the database file field descriptions.
An alternate column headings structure, made of one or more spreadsheet rows, defined by the user, can be used only for the first database file.
This alternate column headings structure
- must be defined on a stream file using the XLSGEN tags <row>, <cell>, <data>, </data>, </cell> and </row>.
Note: Of course, when an ALTCOLHDG stream file is specified, parameter HDGCOLOR (Column headings background color) no longer applies. To obtain
foreground and background colors, XLSGEN keywords f-color and b-color must be specified in the tags of the stream file (XLSGEN script)
specified in this parameter ALTCOLHDG.
For a detail example about using this parameter, see this page.
- 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 .
- 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:
- 0 - Column headings are not generated.
- 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.
Note. The generated column headers are all contained within the first row.
- ColHdg rotation degrees (HDGROTATE) - Number of degrees the column-header texts will be rotated. Select one of the following:
- 0 to keep them horizontal
- 45 to rotate them 45 degrees right
- 90 to rotate them 90 degrees right
- -45 to rotate them 45 degrees left
- -90 to rotate them 90 degrees left
- ColHdg background color (HDGCOLOR) - Select one of 47 colors to be used as background color for the cells in the first row of the generated spreadsheet
(Column Headers), or select *NONE to avoid using a background color.
The available colors are listed here.
Column Header cells are always assigned borders, regardless of what is specified in parameter CELLBORDER (see below).
- Default cell colors (DFTCELLCOL) - These are the default cell colors to be assigned to all spreadsheet cells. There are two default cell colors:
- The first color is the font color for the text to be written in a cell.
- The second color is the background color of the cell.
Each color can be chosen from a list of 47 colors.
The available colors are listed here.
- Cell border (CELLBORDER) - 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.
Freeze pane columns and rows (FROZEN) - This parameter allows to freeze some initial rows and/or columns of the generated worksheet(s).
Frozen intial rows and/or columns keep steady while you scroll a spreadsheet.
This parameter is made of two elements:
- Number of frozen initial rows.
Frozen rows are repeated on all print pages.
Note. To freeze just the column header row(s) when COLHDGOPT not zero or when ALTCOLHDG specified, ask just 1 initial row to be frozen.
- Number of frozen initial colums.
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) .
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.
Convert date fields to (CVTDATE) - Format to be used in representing EXCEL date values. Select one of the following:
- *ISO (yyyy-mm-dd)
- *USA (mm/dd/yyyy)
- *EUR (dd.mm.yyyy)
- *JIS (yyyy-mm-dd)
- *YYMD (yyyy/mm/dd)
Convert time fields to (CVTTIME) - Format to be used in representing EXCEL time values. Select one of the following:
- *ISO (hh.mm.ss)
- *USA (hh:mm AM or hh:mm PM)
- *EUR (hh.mm.ss)
- *JIS (hh:mm:ss)
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.
Skip fields by field test (SKPFLDS) - Use this parameter to define if some record format fields should not generate a cell
- leaving a hole in the appropriate spreadsheet column - when meeting given conditions.
Up to 50 record format different fields can be defined, provided that their data types are one of the following:
A (character), 5 (binary character), L (date), T (Time), Z (timestamp), B (binary), F (float), P(packed), S (zoned).
For each such field three elements must be defined:
- Field name - The field name as defined in the data base file record format.
It must be within 'quotes' if containing special characters. Example: 'BIRTH_DATE')
- Relational operator - One of the following operators:
*EQ (equal to), *NE (not equal to), *LT (lower than), *GT (greater than).
- Comparison value - The value to be compared to the field value.
Character values must be within 'quotes'. Numeric values, if including decimal point and/or thousands delimiters, must also stay within 'quotes'.
Note. Date comparison values must always be in ISO format yyyy-mm-dd even when the related field is defined with a different format
(examples: *MDY, *DMY, *DMYY, *YMD, *YYMD, *USA, *EUR, *JIS, *JUL).
Example: SKPFLDS(('BIRTH_DATE' *LT '1980-01-01') (BIRTHCNTRY *NE US))
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
- The name of a character field containing one of the following link types:
- an URL link, example: http://www.easy400.net
- a MAIL link, example: mailto:gb_perotti@easy400.net
- a FILE link, example: c:\mydir\mydoc.doc
- The name of a character field containing a description 2. of the link.
You may run a test of this feature by entering command
TABLEXLS FROMFILE(hssfcgi/hyper) TOXLS('/tmp/hyper.xls') PRTHDR(*MBRTXT) LINKFLDS((itmlnk itmdes))
Display pictures (PICTURES) - If the database file contains character fields specifying the names of .PNG or .JPG pictures
(example: /mydir/mypicture1.jpg), you may ask to have these pictures displayed in the spreadsheet instead of just showing their names.
Select:
- *YES to display the pictures instead of their names, or
- *NO to display just the stream file names of the pictures, as for any other character field.
Note- For some information about how pictures are specified in a XLSGEN XML-like script, see this page.
VSPAN for picture cells (VSPAN) - This parameter (vertical span) is required if PICTURES(*YES) is specified.
This parameter is used to specify the height of the pictures and is expressed as the number of spreadsheet rows to be spanned for a picture cell.
The default value is 7 (picture cells are given a height corresponding to 7 spreadsheet rows).
Pictures width in pixel (WIDTH) - This parameter is required if PICTURES(*YES) is specified.
This parameter is used to specify the width in pixel of the pictures cells.
Please note that a picture width is always expanded to the cell width, which is assessed by the largest cell in the same spreadsheet column.
Paper size (PAPERSIZE) - 33 different paper sizes are available. Use the command prompt or run command DSPPFM HSSFCGI/PAPERSIZES to display them.
Note: This parameter works only if the extension of the workbook is xls .
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).
Note: This parameter works only if the extension of the workbook is xls .
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
occurred.
Download Excel stmf if CGI job (DOWNLOAD) - When command TABLEXLS is
executed from a job in subsystem QHTTPSVR (a CGI job), this option
allows to download to the client browser the generated Excel stream
file. Of course, if this is done, no other response from the CGI program
should be sent to the browser.
If command TABLEXLS is issued from a CGI program:
- Leave *YES to have the CGI program sending out the Excel stream file
- Type *NO to avoid downloading the Excel stream file and to have the
CGI program providing its own response to the browser.
Generate XLSGEN report (XLSGENRPT) - Whether a performance report should be generated once the Excel workbook has been created.
This report accounts for all the workbook creation runs in the current job.
For more information about this feature see command RPTXLSGEN.
Run XLSGEN in a submitted job (SBMJOB) - If you specify
SBMJOB(*YES), all the XLSGEN 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.
Sizing parameters (SHEETSIZE) - This parameter is made of three elements:
- Maximum number of cells per Excel workbook
- Maximum number of worksheets per Excel workbook
- 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.
- 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.
- 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.
- 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
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:
- 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.
Note- This parameter, when used, inhibits the use of parameter PRTSCALE
Note: This parameter works only if the extension of the workbook is xls .
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: This parameter works only if the extension of the workbook is xls .
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:
- 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.
- 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
TABLEXLS FROMFILE(HSSFCGIDTA/ADHOCCTL1) TOXLS('/tmp/ADHOCCTL1.xls'):
EXCEL WORKBOOKS GENERATED FROM JOB 229998/GBPER/QPADEV0002
.
=== Run no. 9 ==================================================
Workbook . . . . . . . . . . . . . . /tmp/ADHOCCTL1.xls
Type . . . . . . . . . . . . . . . . XLS
TABLEXLS XML script created in . . .759 seconds
XMLFORMAT XML script reformatted in .000 seconds
.
----------------------------------------------------------------
- Spreadsheet no. 1 . . . . . . . . ADHOCCTL1
No. of columns . . . . . . . . . . . 5
No. of rows . . . . . . . . . . . . 8
No. of cells . . . . . . . . . . . . 40
No. of cells last row . . . . . . . 5
Worksheet created in . . . . . . . . .154 seconds
Date & time created . . . . . . . . 2022-09-24 16.44.34
====================================
This run was performed in . . . . . 4.446 seconds
================================================================ |
| | Figure 5a - Sample RPTXLSGEN report |
3- Running TABLEXLS from a WEB browser
Command TABLEXLS is for a 5250 black-green screen or for execution within a program.
Then, how to run this command from a WEB browser?
If you have installed the Apache HTTP directives in stream file
/hssfcgi/apache/http_directives.txt,
just try one of the following URL's:
| |
http://.../tablexls |
or |
http://.../hssfcgip/webtablexl.pgm |
You will receive the following page:
|
| | Figure 6 - Command TABLEXLS from a WEB browser |
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.
Notes.
- 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.
Note - Username and password are requested. To access this utility with URL https://www.easy400.net/tablexls use the following:
username=guest and password=easy400 .
- 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.
|
|
