Re: Create Excel spreadsheet (including formulas, color, comments, etc) on iSeries unattended in batch mode

John

The RPG2SQL product from RJS Software Systems that Richard mentioned gives you programmatic control of several and ultimately all (with some scripting) features available in Excel. It does take advantage of the OLE object support on a Windows machine - I probably have the terminology wrong, I'm not worried about it, since people will likely know what I mean.

This control and support is read/write.

Things like SQL2JXL and SQL2POI are write-only and don't have any formatting - nice tools, fast, but quite rudimentary - results of a SELECT statement end up in a spreadsheet. nice if it's all you need.

We have needed 2-way use of a spreadsheet, so we put up Scott's POI wrapper, and that's been very cool for the occasional read and update of certain cells. It's still slow for mass operations in some situations.

Details of the underlying technology of Excel don't interest me enough to worry about it - I guess I'm a high-level, here's what it looks like, kind of guy. Sure it's kind of interesting to know that Excel updating is basically a rewrite - and it doesn't help me much in doing my work. And getting something out to a user.

Cheers
Vern

On 12/19/2013 3:20 PM, John Yeung wrote:

On Thu, Dec 19, 2013 at 3:11 PM, <rob@xxxxxxxxx> wrote:

This stuff's been around for a LONG time. I think we looked at something
on the S/36 you called from RPG2 and sent it three parameters: row,
column, value. Granted, it would have to have been late in that machines
life (after VASP?) to support parameters. That allowed you to post into
an existing spreadsheet.

Interesting. Not interesting that it's so old, but interesting that
it allowed apparent update of an existing spreadsheet. You see, there
really is no such thing. Not even Microsoft Excel can do it. What
Excel does is give the user the *impression* that they are updating an
existing spreadsheet, but behind the scenes, the file is blown away
completely and rewritten from scratch. Every. Single. Time.

This is inherent in the Excel file format, particularly the old binary
versions (anything ending in .xls). You can't just "append some rows
at the bottom" by throwing data at the "end" of the file; the effect
of changing data in one "cell" can (and typically does) have effects
in various places in the file. It is a monstrous and hairy beast,
that BIFF specification. And until BIFF8, there were new versions
every so often. You can still find BIFF5-writers in the wild, and
perhaps even some BIFF4.

And that's a school of philosophy there too. Do you generate your whole
spreadsheet from scratch? Or do you copy a template and update it? Thus
allowing easier changes. Nice philosophy, but the first time the user
changes the cell to update by inserting a row or column and changing the
text in the adjoining cell it get's to be a hoot.

If you are copying a template and updating it, you have to be able to
read a template. Given the complexity (and intrinsic
little-endianness!) of the BIFF specification, this is a highly
nontrivial task. You're saying that some folks made BIFF-reading
software on the S/36?! If they could read AND write Excel, how come
that software isn't better known? Perhaps it only handled older BIFF
versions and became obsolete as end users upgraded to newer Excel
versions?

John