On Mon, Dec 21, 2015 at 10:08 AM, Buck Calabro <kc2hiz@xxxxxxxxx> wrote:
Once you have done this in Java, you will need to write some wrapper
functions and prototypes in order to call it from RPG. You may find it
easier to simply copy your (by now) working Java right up to IBM i and
update the spreadsheet using Java - no RPG at all.
This would be my recommendation. Why introduce extra steps and
possibly extra overhead?
An alternative to POI / Java is Python:
http://xlsxwriter.readthedocs.org/en/latest/example_autofilter.html#ex-autofilter
I very much appreciate the attempt to put or keep Python in the
discussion, particularly in conjunction with the XlsxWriter library.
Unfortunately, for the original task described (updating an existing
workbook), XlsxWriter is insufficient on its own as it's purely a
writer.
There are two main choices if you're using Python to update existing
Excel files, and you can't run Excel itself:
1. Use an Excel-reading library to extract the *data* from the
existing workbook, then use an Excel-writing library to write a brand
new workbook which incorporates the data you wish to carry over, plus
any changes that you need to make. You may lose preexisting
formatting, formulas, charts, macros, etc.
2a. If dealing with .xlsx files, use OpenPyXL. You may lose
preexisting formatting, formulas, charts, macros, etc.
2b. If dealing with .xls files, ask your customers and/or your
superiors whether switching to .xlsx is an option. If not, use
xlutils. You may lose preexisting formatting, formulas, charts,
macros, etc.
There isn't a self-contained package for Python that is as
comprehensive as POI for Java. The closest is definitely OpenPyXL.
For *writing only*, XlsxWriter is basically in a league of its own,
surpassing both OpenPyXL and POI. It is especially adept at charts,
relative to the competition.
John Y.
midrange.com
