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=
midrange.com
