RE: New SXSSF spreadsheet generation

Vern,

That for the info.

We are not using POI now, we are using our own program but
we are trying to see if there is something easier then
writing it ourselves.
We would not be using Scotts wrapper, we would prefer to do
our own calls to java classes


Thanks for the feedback

John

-----Original Message-----
From: Vernon Hamberg [mailto:vhamberg@xxxxxxxxxxxxxxx]
Sent: Friday, December 19, 2014 3:15 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: New SXSSF spreadsheet generation

John

Are you using POI now to create the XLS files? Then I
believe that the time difference should not be too great. I
would anticipate a slight increase, as XLSX files are really
a collection of XML files, while XLS is a single file in
some kind of binary layout. So writing to the several XML
stuff might take a little longer - but, again, not much, as
I think about it.

Also, are you using Scott's wrapper for creating your XLS
files? Then I still think the difference will be minimal.

The problem with Scott's wrapper, for me, is that every
action is a separate call to a Java method - this is fairly
expensive, compared to using a Java class that does it all.
But then you Java class has to do it all, and it will likely
be written for each specific purpose. There is a set of
tools by Giuseppe Costagliola, which includes a command,
SQL2XLS - this goes very fast, but is pretty generic - you
pass it the SQL SELECT statement, then it puts out an XLS
with certain things set for you.

Giuseppe has a single Java class that is called - so only
one such call, as opposed to many with Scott's wrapper. This
helps the speed.

But we are stuck with the things Giuseppe decided to put
into the resulting spreadsheet - column headings, all that -
you CAN use a kind of template or model file, to get
headings. And source IS included, so one could make the
changes one wanted.

HTH
Vern

On 12/19/2014 11:04 AM, John Allen wrote:

I see a lot of references to using POI and SXSSF to create

XLSX files.

But they almost all refer to speed in generic terms

"Slow", "Faster",

"twice as fast"

Can you or anyone else give me an idea what Faster or Slow

means in

terms of x minutes to create Y rows with Z columns

I know that it depends on the number of columns and rows

and

formatting and system etc. etc.

I am looking for some better defined (but still a little
generic) performance numbers
How long would it take to create an average spreadsheet
(5-10 columns with 10,000 rows)
Is this 3 minutes (which is slow to some people and fast

to

others) or 15 minutes or whatever

We are already creating some rather large XLS files and

would like to

switch to XLSX but I can't if the users that are used to

getting the

XLS file in less than a minute will have to wait 5 or 10

minutes.

I really don't want to take all the time to download

everything and

program a test just to find out the speed is unacceptable.
If I have at least some general performance numbers I know

if I should

take the time for writing my own test on our own data.

If several of you guys that are using SXSSF could give me

times that

you are experiencing and what size spreadsheet I can make

a good WAG

to see if I would be completely wasting my time or if I

should move

forward with my own test.

Thanks in advance

John


-----Original Message-----
From: darren@xxxxxxxxx [mailto:darren@xxxxxxxxx]
Sent: Wednesday, November 26, 2014 10:37 AM
To: midrange-RPG RPG message board
Subject: New SXSSF spreadsheet generation


We installed the newest Apache POI 3.10.1 API's for

generating

spreadsheets that are called from Scott Klements'
service program series. The upgrade from 3.6 was pretty

painless, and

I've found a great new feature in the "Buffered Streaming"

SXSSF

workbook generations. I'm finding I'm able to generate

huge

spreadsheets with virtually no growth in heap memory,

which means we

can generate even bigger spreadsheets without java dumps,

and the

actual generation is about twice as fast.

http://poi.apache.org/spreadsheet/index.html


--
This is the RPG programming on the IBM i (AS/400 and
iSeries) (RPG400-L) mailing list To post a message email:
RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change

list

options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting,

please take a

moment to review the archives at

http://archive.midrange.com/rpg400-l.

-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2014.0.4765 / Virus Database: 4189/8633 - Release
Date: 11/26/14

--
This is the RPG programming on the IBM i (AS/400 and
iSeries) (RPG400-L) mailing list To post a message email:
RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change
list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.