I need an automated, unattended process that will convert an AS/400 spoolfile to Excel, strip headings, maybe some custom cells, and email as an attachment.
I've researched this before, never came up with a final solution.
We currently use IBM Info Print Server to auto email 100% of our spoolfiles/reports.
Two issues.
1) Info Print Server does not have an Excel option
2) The PDF that is emailed cannot be imported into Excel.
Back on the priority list.
Below is my compilation from the archives and other sources.
Some of the solutions are for PF only, may not handle spoolfiles.
1) For simple spreadsheets, IBM's CPYTOIMPF or TAATOOL CPYTOCSV
2) For more complicated spreadsheets (including formulas, color, comments, etc) use Scott Klements POI stuff (Java) or Python (using XlsxWriter) are your best bets, and both are free. I am not familiar with commercial products.
I don't know how much of the POI functionality is exposed in Scott Klement's RPG wrappers, but if you can work directly in Java, it's probably simpler and definitely exposes all available functionality.
Personally, I find Python easier to work with than Java, and from what I've seen, XlsxWriter is easier to work with than POI. So if you are starting from zero knowledge and experience with either, I strongly recommend Python. If you already are comfortable with Java, then Java is probably your best choice.
If you do decide to try Python, I can provide some tips. Some rudimentary starter info is available here:
http://iseriespython.blogspot.ca/
There's about a billion ways to generate simple column lists into Excel. I think there's a command out there somewhere called SQL2XLS or some such thing.
However, your users are going to quickly tire of simple column lists, or they are going to be wasting time constantly reformatting these simple column lists. And then there will come a point where it's not "email them to everyone in the world" but instead, post it on the website for those interested, probably in a log in only area.
What I am saying is they will want extra formatting, built in formulas, etc. So I would skip the simple columnar output and bite the bullet right away and learn the ways to generate your own formatting complete with pretty lines, built in formulas and whatnot - like the Scott Klement examples noted or some of the other alternatives.
http://www.scottklement.com/poi/
3) New POI Release Brings Excel's XLSX Support to RPG
http://iprodeveloper.com/system-i-network/new-poi-release-brings-excels-xlsx-support-rpg#!
4) Solutions that Download iSeries Data to MS Excel
http://iprodeveloper.com/systems-management/solutions-download-iseries-data-ms-excel
5) A popular choice is SQL2POI or SQL2JXL:
http://www.mcpressonline.com/microsoft/techtip-excel-flies-higher-with-jexcelapi.html
Probably most of the Java-only or RPG-and-Java solutions are based on POI. I can't name them all. Others will chime in I'm sure.
My favorite way to make Excel files on the i, because I'm a Python programmer, is iSeriesPython plus either xlwt (if .xls is required) or XlsxWriter (if .xlsx is acceptable).
http://www.iseriespython.com
https://pypi.python.org/pypi/XlsxWriter
https://pypi.python.org/pypi/xlwt
I've built a program to automatically copy data from a physical file to an Excel file using xlwt (callable from CL, no Python knowledge
required):
https://bitbucket.org/jky/cpytoxlsf.py
It has several niceties that most tools don't have, such as fairly accurate automatic column sizing and a slew of potential customization options based on "hints" provided by the field edit codes and description text. (I will be posting a corresponding cpytoxlsx.py in the near future for making .xlsx files using the same interface.)
There are similar Excel libraries for PHP, so if you can't be sold on Python but are still open to dynamic languages, there's definitely PHP.
6) We use Gumbo's Excel-erator .
it is simple and works fine.
$1,695 annual maintenance of $255.
http://www.gumbo.com/Licpgm/Excelerator.html
7) SEQUEL from Help Systems
http://www.sequel-software.com/products/sequel
8) A Few Excel Export to CSV Tips
http://www.itjungle.com/fhg/fhg012710-story02.html
9) Exporting DB2/400 Dates to Excel
http://www.itjungle.com/fhg/fhg011007-story01.html
10) Just Push a Button to Get AS/400 Query Results in Excel
http://www.itjungle.com/mpo/mpo041003-story02.html
11) iDataDevelopment Ltd is the owner of the iEXL product. I am technical director of iDataDevelopment Ltd.
iEXL is a product that will create Excel spreadsheets on the AS400/Power. All processing and setup is carried out on the AS400.
The product has now been fully tested at V7R1. It has already been in use at V5R4 and 6.1 for a number of years.
Options include.
* Fonts
* Colors
* Background and Text
* Text rotation
* Text underline
* Text alignment both horizontal and vertical
* Edit codes
* Column headings
* Functions/Formula
* Merge cells
* Hide columns
* Sort functionality
* Password
* Images
* Freeze Pane
* Page Heading & Footers
* Text insertion
* 50 DB files per workbook
* UNICODE data processing
* Emails and much more.
All the above options can be specified at Cell, Row, Column, Sheet or Workbook level.
Border and grid generation are currently under test at a client site.
For more information information please see. WWW.IEXLSOFTWARE.COM or iEXLSUPPORT.COM
Thank You
_____
Paul Steinmetz
IBM i Systems Administrator
Pencor Services, Inc.
462 Delaware Ave
Palmerton Pa 18071
610-826-9117 work
610-826-9188 fax
610-349-0913 cell
610-377-6012 home
psteinmetz@xxxxxxxxxx
http://www.pencor.com/
midrange.com
