Another TABLEXLS user need is similar to the banner case, but is somehow different.
While a banner rows must sit on the top of a spreadsheet, this other need would be the ability to inser a row, or a group of rows within the "body" of a spreadsheet.
That could be a subbanner, a rows delimiter or a trail row.
Such a need can be implemented with parameter INCLUDEXML of command TABLEXLS.
This parameter is made of two elements:
- the name of a stream file containing XLSGEN statements for the row or the group of rows to be included
- a parameter telling the row number of the spreadsheet where these XLSGEN statements will be included.
The stream file containing XLSGEN statements to be included in the spreadsheet requires some XLSGEN language knowledge to be built.
It may be built manually, or dynamically by a user program, or picking up a group of XLSGEN statements from an existing XLSGEN (XML-like) stream file
by using command HSSFCGI/GETXMLROWS.
The second parameter tells where in the spreadsheet the inclusion must take place. This is done by specifying the number of the row that will follow the inclusion.
A 1 means that the inclusion will take place before the first row of the spreadsheet, usually before the column headers (rows belonging to a banner
should never be accounted for). Special value *START stays for row 1. Special value *END stays for the very end of the spreadsheet body.
Let us take the case of a spreadsheet featuring a banner, the one created with command
HSSFCGI/TABLEXLS FROMFILE(HSSFCGI/UTILS) TOXLS('/tmp/utils.xls') BANNERID(1)
HDGCOLOR(INDIGO) FROZEN(6) IGNFLDS(UTISEQ UTIURL UTICHARGE):
Now, what we want is to include an orange row containing some text, just before the body of this spreadsheet.
This is how we do it:
The result id the following spreadsheet:
- We need to write a small stream file containing the XLSGEN statements defining the rows to be included.
An example is available in stream file /hssfcgi/examples/INCLUDEXMLexample.txt and is look as follow:
<data type="LargeTextC" italic wrap b-color="ORANGE" f-color="INDIGO">
Spreadsheet generated on IBM i with command HSSFCGI/TABLEXLS
- In the previous TABLEXLS command we must add parameter INCLUDEXML as follow:
HSSFCGI/TABLEXLS FROMFILE(HSSFCGI/UTILS) TOXLS('/tmp/utils.xls') BANNERID(1)|
HDGCOLOR(INDIGO) FROZEN(6) IGNFLDS(UTISEQ UTIURL UTICHARGE)
Try it yourself!