Re: Excel Question

[Scott Klement <midrange-l@xxxxxxxxxxxxxxxx>]

Hi,

> I ve frequently seen users ask for reports to be delivered in "excel
> format". How is this done? My first instinct is to modify my
> report-generating program to output information to an
> externally-described physical file instead of (or in addition to) to a
> spool file, then to use EMAIL to send the physical file to the user with
> parameter DELIMITER(*CSV). But the resulting .CSV file doesn't really
> look like the report as I drop all headings line (including column
> headings) and total lines. Is there a way to deliver the report in
> "excel format" that actually looks like the original report?


CPYTOIMPF and CVTTOXLS and similar utilities actually create CSV or TAB
delimited files.  Those files are okay for containing Excel data, but
they're *not* XLS files and they do not allow all of the functionality
that Excel allows (column widths, fonts, cell borders, colors, etc.)

I wrote an article recently about creating genuine Excel files using ILE
RPG with the open source (free) POI/HSSF Java classes.  This article
appeared in the Sept 16, 2004 issue of Club Tech iSeries Programming Tips
but unfortunately is not yet posted to the web page, so I can't provide a
link.  If you contact me off-line, I can send you a copy of this issue of
the newsletter, however.

Also, Geert Van Landeghem of JAS Services has a web page that explains
creating XLS files using these same Java classes.  His can be found at

http://www.jasservices.com

Once on that page, click "Learning Center", then "Nr001: RPG2EXCEL Using
POI"

I believe there was a missing prototype in Geert's example, though.  Try
searching the RPG400-L archives, you should be able to find messages about
the problem and how they fixed them.

Good Luck