Re: Excel by XML generation

Darren

I think you're right - the service program Scott wrote uses POI, but it does individual Java calls from RPG for every method - that's why it can be really slow. I believe that is what he referred to in a post earlier in the thread, not where everything is done in POI.

So yes, for performance, the thing is to do all the logic in the language, whether Java with the POI class or with Python or whatever - we certainly don't want to do a bunch of method calls out of RPG to everything you need in Python.

There were a couple old commands by Giuseppe Costagliola - SQL2POI and SQL2JXL - they both use a Java class, POI in the one, I forget right now the other one - the run fairly fast, because you pass a SELECT statement in a command and the CPP does all the work in a single Java call. Trouble right now is, they product only the old XLS format - could be rewritten, and there might have been an effort once, but it seems kind of dropped. The POI version certainly could be updated. Everything is free out there on the Net.

Cheers
Vern

On 12/16/2020 9:53 AM, Darren Strong wrote:

I've seen Python suggested many times, but, isn't that a replacement for RPG? So, wouldn't I have to embed all the report logic in Python? The multiple tabs, selection criteria, variable sorts, would need to be migrated to Python?


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of John Yeung
Sent: Wednesday, December 16, 2020 10:48 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Excel by XML generation

CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.


On Wed, Dec 16, 2020 at 9:56 AM Darren Strong <darren@xxxxxxxxx> wrote:

So, here is my current view of the state of Excel generation:

- There are lots of tools to take a file and create a spreadsheet, but, there isn't much flexibility in file dumps.
- There are languages like C, Python and Java that have pseudo native access to Excel generation through "libraries", but, I'm programming in RPG, so, I have to use inefficient interfaces to access those tools.

There's inefficient, and there's inefficient. The JNI-based POI interface that most RPGers use is *especially* inefficient, because there is overhead repeated for each Java method call.

If you take the approach of delegating ALL the workbook generation to the other language, then you just have to make the one call.

It's true that on an IBM i, most other languages are not going to be as fast as RPG. But they may be fast enough. Scott describes a big POI job taking hours. Well, that's crazy-slow. Using Python (a notoriously slow language), we generate workbooks that are in the 20 MB to 30 MB range in about 15 minutes or so, and we have just about the weakest machine possible to still be running 7.3. (Cache effects seem to be at play, and the first run tends to be slower, later runs faster.)

John Y.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com