Re: Formatting an EXCEL Worksheet Generated From The 400

Hi Rick,

> The problem we are experiencing occurs within EXCEL itself. Because of
> varying field sizes, which cannot be altered, when the CSV file is opened,
> multiple formatting issues occur...#'s representing some date fields,
> numeric fields not zero suppressing, columns showing as truncated although
> when you click on them the data is fine and other stuff of this ilk.

Yes, that's the big pitfall of creating a document as a CSV instead of an
XLS, there's no formatting. Unfortunately, creating the document in XLS
(native Excel) format is a lot more complicated than simply running
CPYTOIMPF.

I can't remember if you're an RPG programmer or not, but my solution has
been to write an ILE RPG program that calls the routines from the open
source Jakarta-POI Java classes to generate a spreadsheet in true XLS
format.  Doing it that way, I can set the width of each column, I
can tell it how to format numbers, I can even change fonts, boldness,
colors, etc.

Geert Van Landeghem has a web page that can help you get started:
http://www.jasservices.com/articles/as400/001_genexcel.htm

There's lots of good information about calling Java from RPG in the
WDS ILE RPG Programmers Guide which is in the Information Center here:
http://publib.boulder.ibm.com/iseries/v5r2/ic2924/books/c0925074.pdf

I did an article on Parsing Excel spreadsheets in the January 8th issue of
Club Tech iSeries Programming Tips newsletter.  This is the opposite of
what you're trying to do, but if you're interested, here's a link:
(This link requires an account on the iSeries Network, but you can get an
"associate" membership for free if you don't have one)
http://www.iseriesnetwork.com/resources/clubtech/index.cfm?fuseaction=ShowNewsletterIssue&ID=17836

If you decide to go this route, and have any questions, I can help you out
on the RPG400-L mailing list...