Re: Excel by XML generation

I looked at that one about 6 months ago but decided against using it as there was no sign of movement in the project for 12 months and not much in the year before that.

Maybe time to revisit.

On Dec 16, 2020, at 10:34 AM, Darren Strong <darren@xxxxxxxxx> wrote:

Well...just stumbled on the following. Looks like there may be a C library that might be interesting. I hope someone didn't already point this out to me...sorry if you did.

http://blog.rpgnextgen.com/blog/2018/03/21/libxlsxwriter-creating-spreadsheets-natively


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Darren Strong
Sent: Wednesday, December 16, 2020 9:56 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Excel by XML generation

Thank you Vern,

I anticipated that the XML extension was going to be an issue, and I hadn't found a resolution for that yet. Also, knowing that it's an older format means I'll probably reject that method.

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.

In conclusion, what I want does not exist, and that is a way to generate flexible Excel output from RPG without using an inefficient interface to another language. There isn't much documentation around the Excel XML format, even if I was to generate my own methods, because it's complicated, and most people would say, why re-invent the wheel when C, Python, and Java have tools already.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Vernon Hamberg
Sent: Wednesday, December 16, 2020 9:23 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.


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

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