|
1-What
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.
(In TABLEXLS, the spreadsheet "body" starts just after the automatically generated (if any) column-headers row.)
That could be a subbanner, a rows delimiter or a trail row.
2-How
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 EXCEL 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 after the TABLEXLS automated the column headers, if any (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.
3-An example
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:
- 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:
<row row-height="18">
<cell hspan="4">
<data type="LargeTextC" italic wrap b-color="ORANGE" f-color="INDIGO">
Spreadsheet generated on IBM i with command HSSFCGI/TABLEXLS
</data>
</cell>
</row>
|
|
| Figure 2 |
- 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)
INCLUDEXML('/tmp/includexmlexample.txt' *START)
|
|
| Figure 3 |
The result id the following spreadsheet:
Try it yourself!
|
