Re: Excel by XML generation

Hi Darren

I don't know which Scott went with but can speak to the difference - the XML-only version is the 2003 XML Spreadsheet format that is pretty much not in use anymore - several of Jon and Susan's early examples used this, since it's easy to work with just that one XML file. It has several limitations, like, so far as I know, not allowing for images. You CAN do multiple sheets, though.

It does usually have the XML extension, which, we found, was confusing to our users. I need tried changing the extension to XLS - or maybe I did - the result might have been a message about content not matching the file type - I am not sure, though - memory leaks regularly around here.

Regards
Vern

On 12/16/2020 7:49 AM, Darren Strong wrote:

Scott,

Looks like there are two methods to generate XML that Excel will open. One is to generate one XML which is opened directly, or to generate the xlsx format, which is basically a bunch of XML files zipped into a container. Do you recall which method you chose and if you/they looked at both options?

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Scott Klement
Sent: Tuesday, December 15, 2020 4:55 PM
To: 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.


Hi,

Yes, I did that at my previous employer (it was about 9ish years ago).
It was much, much, much faster than POI, used less memory, etc. We had a big Excel project, and POI took hours to generate the spreadsheet, whereas writing XML took only about 30 seconds.

It might even be easier nowadays since we have tools like DATA-GEN.

Unfortunately, since I no longer work there, I don't have access to the code. And, I don't do anything with spreadsheets these days, so haven't tried to write any equivalent in RPG.

You might consider using the tools in other languages.. just about any open source language (not just Java) has tools for making Excel documents. I've used ExcelJs for Node.js -- this works really nicely.
In fact, if I were to write my own RPG Excel tool today, i'd probably model it on ExcelJs, much nicer to work with than POI.

-SK

On 12/15/2020 2:34 PM, Darren Strong wrote:

We've gotten pretty good at generating spreadsheets with POI HSSF and the JNI interface, but, the JNI interface is not efficient and the POI HSSF versions march on while we've frozen our updates due to the move to enums in that tool. Now, IBM has come a long way in helping us generate XML and writing it to the IFS, which Excel is based on. Has anyone had success generating the .xlsx file structure directly as XML files and then zipping it up in the format that Excel likes? Is there some documentation on the web I'm not able to find that might give some detail and examples on this? I would guess that this method would be very fast compared to what we're using now.

--
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