This one is doing all you want.
http://www.coolspools.com/
We have used it for years w/o any errors.
Yes, it costs, but worth the money.
Brgds
Helge
-----Oprindelig meddelelse-----
Fra: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] På vegne af
Steinmetz, Paul
Sendt: 11. februar 2015 22:57
Til: 'Midrange Systems Technical Discussion'
Emne: Automated Unattended Convert spoolfile to Excel and emailed as an
attachement
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-jexce
lapi.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/
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
