AS/400 file to Excel with Headers and Footers
I need to attach an Excel s/sheet to an email. I currently do that via the CPYTOIMPF as a CSV file but this doesnt give give me the true Excel Header row, which is a requirement. Is there any easy way to do this?
Software/Hardware used:
Software/Hardware used:
ASKED:
July 15, 2010 12:08 PM
UPDATED:
July 16, 2010 8:26 PM
Answer Wiki:
Tom, Thanks for your repsonse-- but I need a true Excel Header row, where it shows summary info, where you can select or omit values, sort values, etc. I've created column names, but that's insuffiicent for the user. He needs a complete, true, Excel spreadsheet.
Thanks Sloopy-- I'll take a look and give it a try and let you know. Thanks Again
To see all answers submitted to the Answer Wiki: View Answer History.
The CPYTOIMPF command is provided to perform DB2 exports. (CPYFRMIMPF does imports.) Since Excel isn’t DB2, the command isn’t directly compatible.
Although the CPYxxxIMPF commands have some flexibility, they do have limits.
The IBM product that performs data transfer in Excel format is iSeries Access File Transfer. Choose BIFF8 as the PC file type. When the resulting PC file is opened in Excel, column names will be in row 1.
Otherwise, create a procedure to build a streamfile with a single line of comma-separated values consisting of the column names that you want. When you run CPYTOIMPF, include the MBROPT(*ADD) parameter to add your lines after your column headings.
Your proc might be written in ILE RPG or any ILE language (including CL if you are at or above V5R4). There are also possibilities such as the Qshell db2 utility, although you might use the db2 utility to perform the entire procedure and skip CPYTOIMPF entirely.
Tom
See these articles – but writing Excel from RPG is quite tricky. There are packages around which you’ll have to pay for, but they may be the best way if you will have to do this quite often.
http://systeminetwork.com/node/60954
http://www.mcpressonline.com/tips-techniques/microsoft/techtip-excel-flies-higher-with-jexcelapi.html
http://www.easy400.net/easy400p/maindown.html
Sloopy
- but writing Excel from RPG is quite tricky.
Not only is that tricky, but then comes another fun part — e-mailing it.
The question started from the perspective of a .CSV file even though it mentioned “an Excel s/sheet” which is a very different kind of file. The latest clarification drops .CSV entirely from consideration. Now, the result needs to be a true Excel spreadsheet file.
But that won’t e-mail over SMTP without encoding.
Either an e-mail product must be bought/installed or routines to generate an encoded attachment must be obtained or written. Then the e-mail becomes possible either through the purchased product or with the QtmmSendMail API (or perhaps a javamail function).
Quite a ways to go if it’s not already available on the system.
Tom