Rob,
Could you point me to Scott Klements examples?
When you convert these, is this a manual or automated process?
Paul
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Thursday, February 12, 2015 8:08 AM
To: Midrange Systems Technical Discussion
Subject: Re: Automated Unattended Convert spoolfile to Excel and emailed as an attachement
I think these are the people formerly known as Monarch. They had a product to extract spool file data and process it.
http://www.datawatch.com/products/datawatch-report-mining-server/
Here we have a person who is going through many existing reports and converting them into Excel email attachments using techniques learned from Scott Klements examples.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: "Steinmetz, Paul" <PSteinmetz@xxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'"
<midrange-l@xxxxxxxxxxxx>
Date: 02/11/2015 04:57 PM
Subject: Automated Unattended Convert spoolfile to Excel and
emailed as an attachement
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
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
