This section shows how a spooled file can be converted into Excel.
The example relates to a spooled file, that has a format like below:
The spooled file is a multi-page spooled file with a header, some details lines (in the middle blue frame), which are described with the column headers in the green frame.
The goal is to convert this spooled in this manner:
In the header we have information, that we want to extract from page 1 and only want to include once in the Excel output. That includes the name of the customer (Herring Marine Research) and the document number (1004).
The column headers (in the green frame) should also be extracted only from page 1 of the spooled file and placed once in the Excel file - in bold.
The detail lines (in the middle blue frame) should be extracted from all pages of the spooled file. The normal formatting of a detail line should be a simple mapping, but if we find the text 'Inter' on a detail line, then the detail line should be formatted in blue. This is to illustrate a condition for detail lines.
The footer lines (which could be totals) below the detail lines should be extracted from the last page of the spooled file only.
In the Excel designer we first load the spooled file from the Library documents in the Excel designer with this icon and choose sample data file:
Then we select the spooled file and now we are ready to start.
1. First we add three styles: One for bold texts, another for normal texts and one for blue texts. We name them Bold, Normal and Blue.
2. Now we insert a repeat for spooled file pages and then add [1] to restrict the page repeat to only cover page 1 of the spooled file:
3. Now we insert a row and insert 4 cells into that row:
3. Inside the first cell we insert a fixed text, Customer in bold and in the third cell we insert the fixed text, Document No:
(and refer to the style, Bold)
and then we map spooled file data from the current page into the second and fourth cell, and we trim the selected text and select style, Normal:
Now the output looks like below:
5. Now it is time to add a new row and map the column headers - again we select data from the current page:
(Style Bold is selected)
6. Next it time to scan all the pages of the spooled file for detail lines.
So we add a repeat for all pages and within that we add a line repeat for the spooled file lines, where the detail lines are found:
7. Inside the line repeat we can now add a condition to identify the detail lines, that contains 'Inter' and inside of it we insert a row and inside of that we map spooled file text from the current line.
Inside the conditon we select relative mode and highlight one of the spooled file lines and then select position for the condition inside of that line:
The condition looks like below:
(This is only true is 'Inter' is found on the current spooled file line).
This is how this section looks in the template tree:
Where each cell use the Blue style.
8. Now we insert a similar condition and mapping (with the normal style) for the detail lines, where the line does not contain 'Inter' and the line is also not empty:
Above we use the Normal style.
The current output looks like this:
9. The final thing missing is to insert some footer/total lines from the last page of the spooled file.
This is done with this setup:
Here you see a page repeat which refers to the spooled file attribute //@endPage, which is the last page to be output (page range). Alternatively we could have used //@totalPages for all the pages of the spooled file independently of the page range of the input spooled file.
The //page[number(//@endPage)] expression repeats just a single time for the page number, which match the last page of the spooled file. The number() function is needed as the //@endPage attribute is a string (here with the value 4), which does not select the 4th page - unless it is converted into a number.
Inside this page repeat we have a line repeat (starting in line 43) with a condition to verify that the spooled file line is not empty. If not, then the spooled file line is inserted in the first cell of a new row.
With the settings above the final output looks like this:
10. The final thing is to use this Excel template to convert the input spooled file into Excel and save that as a file.
That can be done like below:
The used workflow components are:
This monitors and output queue on the IBM i.
The Create Excel document component currently only supports XML files as input, so we force InterFormNG2 to consider the spooled file as an XML file.
Converts the spooled file in the payload into an Excel file, which is now stored into the payload.
The Excel file in the payload is stored to a file in the file system.
