Scott,
I was thinking of the productivity and flexibility gains by being able
to create and format an Excel spreadsheet using Excels built in
formating tool, graphs, formulas, etc instead of writing explicit code.
For example Setting a Font with POI:
<pre>
HSSFCellStyle_setFont( NameStyle: hssf_CreateFont( book
: 'Arial'
: 36
: BOLDWEIGHT_BOLD
: *OMIT
: *OMIT
: *OMIT
: *OMIT
: *OMIT ) );
</pre>
This seems cumbersome and somewhat inflexible to me. Consider the
effort to now graph the data. I am envisioning the following. Create
an Excel web query that will pull the data from an intranet/Internet
securely (use SSL with URL driven credentials perhaps?). Once the data
is in excel, you highlight your column or data and click the "bold"
format icon in excel. Continue formatting using WYSIWYG. Add images,
formulas, charts as required. (requires no POI coding). Make the data
real time and current by specifying the web query to refresh every x
minutes. You can tell web query not to change the formatting. Make the
data flexible and dynamic by adding runtime parameters that are
translated into URL variables. (IE.. End customer is able to select the
type of product... sausage.. and that related data is pulled down)
Once you are happy, you save the Excel on your web server and give the
URL link to authorized folks. They click on the link, the most up to
date version of the spreadsheet is downloaded and the data is updated to
be current. Optionally for end customers, they enter their
userid/password into excel cells for authentication and self service
filtering which are passed along with the web server. The benefits
would be that the excel formatting was accomplished much quicker and
changes can easily be made plus real time data.
Other advantages include the ability to run your web server on alternate
platforms if your AS400 doesn't run Java efficiently. (Linux running
Tomcat with JT400 for SQL and stored procedure calls to DB2 and RPG to
feed data)
Scott Klement wrote:
hi Paul,
Personally, I use HSSF as a means of replacing reports. We have a ton
of reports that are run in daily/weekly/monthly/quarterly batch jobs. I
put them in Excel format and e-mail them to users.
Previously we were using 132-column green bar (or blue bar, whichever
was cheaper) printouts from a line printer.
The users greatly prefer Excel, and indeed, a lot of them had been
taking the data from the old reports and keying it into Excel. But
e-mailing a spreadsheet is much nicer. And I can use fonts, colors,
formulas, pictures, and so forth to make the reports look nice. Many of
these are subsequently sent by our reps to our customers, and I want to
be able to format them well.
As these are batch jobs (not interactive jobs) performance isn't usually
a big deal. Having complete control over the formatting, however, is.
I think this is all formatting is feasible within Excel unless I'm
missing something.
How would you approach the same thing with the Excel Add-in or with ODBC
or something like that? Wouldn't a user have to be sitting in front of
it when it happens? Wouldn't that be a waste of their time when a
computer can do it for them? Wouldn't it be difficult to insert
pictures, format columns, etc?
Excel formatting may be X times faster than hand coding POI? Beyond the
excel formating discussion, we would normally provide most reports on
demand via a self service web portal. There may be value add allowing
customers to access data in real time instead of waiting for an email?
I don't understand why you think POI/HSSF is "fragile" or "cumbersome".
It's true that performance isn't always stellar (though I could say
that about every single Java tool I've ever used) but why is it
cumbersome? And have you had a lot of trouble with it breaking (i.e.
being fragile?) I haven't had any...
"Fragile" is perhaps a poor word. It is the overhead of acquiring the
POI.jar, setting the classpath correctly, dealing with JVM startup and
stutdown, class not found exceptions, getting the latest PTFs, making
sure the correct JVM version is running, configuring garage collection,
reading and writing from the IFS, etc. Then you still have to code all
the logic for each spreadsheet.
This has got me intrigued. The parts I need to further verify are
passing credentials and unique data such customer number with https from
the web query. Also I think we lack a session on the server side so
each interaction will probably require authentication.
Am I missing anything?
Thanks much for your input!
Take care, Paul
midrange.com
