Re: Excel by XML generation

Somebody did it:

https://archive.midrange.com/rpg400-l/201901/msg00028.html

HTH
--
Marco Facchinetti

Mr S.r.l.

Tel. 035 962885
Cel. 393 9620498

Skype: facchinettimarco


Il giorno gio 17 dic 2020 alle ore 13:29 Darren Strong <darren@xxxxxxxxx>
ha scritto:

Yes. That's pretty much us. Our spreadsheets range from 100 to maybe
100,000 rows at the most. It works very well. Being that we can't apply
updates anymore (stupid ENUMS) without a major overhaul has me looking at
alternatives when I have time. As to the size of your spreadsheets, I can
only suggest that you make sure to trim character values. Those extra
spaces can take up a lot of room.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Don
Brown via MIDRANGE-L
Sent: Wednesday, December 16, 2020 4:52 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Don Brown <DBrown@xxxxxxxxxx>
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.


Darren,

What is the expected average size of the spreadsheets to be created ?

We use POI Version 3.14 which is the last version before the introduction
of ENUMS for many of the classes.

We use Scott's HSSFR4 which has been modified to add additional functions
as required.

The majority of the spreadsheets that we generate are in the 100 to 2000
rows and these take a few seconds.

Recently some government reporting requirements for some of our clients
resulted in some very large spreadsheets and we started using SXSSF which
solved the problem of the large spreadsheet but introduced a new problem
that it was too big to email. These spreadsheets were well over 50MB and
the largest close to a million records and yes this did take a couple of
hours to produce.

Yes being on POI3.14 does mean we are frozen in time until we make time to
modify the procedure calls for ENUMS but we are quite satisfied with what
we can produce.

Calling each function from RPG may be slow but it is all relative to how
big the spreadsheet is and how many spreadsheets you need to generate.

Nice thing about HSSFR4 is it is easy to learn and easy to expand with
excel functions not originally provided by Scott.

Just my two cents.


Cheers

Don





From: "Darren Strong" <darren@xxxxxxxxx>
To: "Midrange Systems Technical Discussion"
<midrange-l@xxxxxxxxxxxxxxxxxx>
Date: 17/12/2020 12:56 AM
Subject: RE: Excel by XML generation
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx>



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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=ccBdxylExFBHMzbszdOaAGS3TVpr4UFzK7VdqBGzdFw&s=6L3f-1QC5sx27i9C6o6PkTZD28t83wQP3RAINsG_PDQ&e=

or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at

https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=ccBdxylExFBHMzbszdOaAGS3TVpr4UFzK7VdqBGzdFw&s=BDqaIAjaWneeaJs3UvvgPLc7xo1VoFeXzoAF1pChXhw&e=
.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related

questions.

Help support

https://urldefense.proofpoint.com/v2/url?u=http-3A__midrange.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=ccBdxylExFBHMzbszdOaAGS3TVpr4UFzK7VdqBGzdFw&s=laIqQm7UmmtynyEFwdEQOPqqoxffG66P9ynA-i2ClQQ&e=
by shopping at

https://urldefense.proofpoint.com/v2/url?u=http-3A__amazon.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=ccBdxylExFBHMzbszdOaAGS3TVpr4UFzK7VdqBGzdFw&s=m_qWrnRHa0kUmamnSBZaJMdyNQJXy9z-1_Bmq2FR9TQ&e=
with our affiliate

link:

https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=ccBdxylExFBHMzbszdOaAGS3TVpr4UFzK7VdqBGzdFw&s=gqGzZajjZ31QsjGcjWujvitOdDW3R89DYQmFS9FHNJ4&e=


--
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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=ccBdxylExFBHMzbszdOaAGS3TVpr4UFzK7VdqBGzdFw&s=6L3f-1QC5sx27i9C6o6PkTZD28t83wQP3RAINsG_PDQ&e=

or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=ccBdxylExFBHMzbszdOaAGS3TVpr4UFzK7VdqBGzdFw&s=BDqaIAjaWneeaJs3UvvgPLc7xo1VoFeXzoAF1pChXhw&e=
.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support

https://urldefense.proofpoint.com/v2/url?u=http-3A__midrange.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=ccBdxylExFBHMzbszdOaAGS3TVpr4UFzK7VdqBGzdFw&s=laIqQm7UmmtynyEFwdEQOPqqoxffG66P9ynA-i2ClQQ&e=
by shopping at

https://urldefense.proofpoint.com/v2/url?u=http-3A__amazon.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=ccBdxylExFBHMzbszdOaAGS3TVpr4UFzK7VdqBGzdFw&s=m_qWrnRHa0kUmamnSBZaJMdyNQJXy9z-1_Bmq2FR9TQ&e=
with our affiliate link:

https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=ccBdxylExFBHMzbszdOaAGS3TVpr4UFzK7VdqBGzdFw&s=gqGzZajjZ31QsjGcjWujvitOdDW3R89DYQmFS9FHNJ4&e=

--
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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=ccBdxylExFBHMzbszdOaAGS3TVpr4UFzK7VdqBGzdFw&s=6L3f-1QC5sx27i9C6o6PkTZD28t83wQP3RAINsG_PDQ&e=

or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=ccBdxylExFBHMzbszdOaAGS3TVpr4UFzK7VdqBGzdFw&s=BDqaIAjaWneeaJs3UvvgPLc7xo1VoFeXzoAF1pChXhw&e=
.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support

https://urldefense.proofpoint.com/v2/url?u=http-3A__midrange.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=ccBdxylExFBHMzbszdOaAGS3TVpr4UFzK7VdqBGzdFw&s=laIqQm7UmmtynyEFwdEQOPqqoxffG66P9ynA-i2ClQQ&e=
by shopping at

https://urldefense.proofpoint.com/v2/url?u=http-3A__amazon.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=ccBdxylExFBHMzbszdOaAGS3TVpr4UFzK7VdqBGzdFw&s=m_qWrnRHa0kUmamnSBZaJMdyNQJXy9z-1_Bmq2FR9TQ&e=
with our affiliate link:

https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwICAg&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=Kys-lxRCMpPr7up01Pp1FRjOe49ne6imWwi1b-ue8yQ&m=ccBdxylExFBHMzbszdOaAGS3TVpr4UFzK7VdqBGzdFw&s=gqGzZajjZ31QsjGcjWujvitOdDW3R89DYQmFS9FHNJ4&e=





--
This email has been scanned for computer viruses. Although MSD has taken
reasonable precautions to ensure no viruses are present in this email, MSD
cannot accept responsibility for any loss or damage arising from the use of
this email or attachments..
--
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