× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Hi Paul,

Take a look at iEXL (Commercial Software). RPG,COBOL, JAVA, SQL, Etc
 
Regards,
Mark 


________________________________
From: "Steinmetz, Paul" <PSteinmetz@xxxxxxxxxx>
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxx>
Sent: Wednesday, 11 February 2015, 21:57
Subject: 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-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/

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.