Re: XML Spreadsheets (CGIDEV2) - Progress and Problem

Hi Arnie

I know that both Jon and I are inclined to recommend going to the XLSX format with or without CGIDEV2 - we use it here, and I give presentations on it, it has served us well - and it matches what we need and skills, mostly.

As for Scott's POI service programs - Jon suggested posting here - there have been some issues around web links for anything at systeminetwork.com - that doesn't exist. The presentations, though, on Scott's page have good links, and his tools usually have good example code.

Cheers
Vern

On 10/31/2020 6:04 PM, Arnie Flangehead wrote:

On 11/1/20, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx> wrote:

Totals and subtotals - when I present my approach to this, I like to
say, "Let Excel do what it excels at!" There are formulas that make like
much easier on the RPG end. If the XML 2003 format supports them is the
question. One of these is SUBTOTAL() with its various parameters - there
can be a CGIDEV2 section for the various subtotals and totals [...]

The total is already an Excel function, but your suggestion of
SUBTOTAL is a valuable one, as that would allow the end user to filter
and get a total of only the visible rows. Thanks for reminding me of
that option.

The RPG has to determine what row the subtotal will be on, so that's
the variable /%NumRows%/, which is a simple counter in the cursor
loop. The example that I followed very closely initially was "Serving
Up Spreadsheets" here:
https://www.itjungle.com/2008/08/13/fhg081308-story01/

Just to digress for a moment, I really appreciate your answering my
questions "as asked" (same to Patrik in the earlier related thread). I
had pretty much given up on help forums because I got sick and tired
of asking how to do A using B only to be told I should be using X, Y
or Z.

I know what I want. My skills, experience, preferences, resources,
support, etc. are not the same as anyone else's. People who tell you
to use X do that because they are experienced with X, because they
have management support to upload and learn (or buy) X, because they
learned X when loads of resources were available (which have since
disappeared). Often they are in a position of authority with a budget
to do whatever they want, but I'm free-lance and have to fit in with
other people's needs and skills: my colleagues, the managers who hire
me, and most importantly of all, the actual end users.

I considered POI initally. No doubt it's a very fine product that
people are happy with. I went to Scott Klement's site and clicked on
all the how-to article links and not a single one was still live.
Presumably copyright issues prevent him from hosting them himself.

I had Jon Paris' excellent Gateway400 seminar presentation, which has
a clear useful overview of GCIDEV2, not to mention an enthusiastic
endorsement of it (not sure if he would resile from that now).

I had already uploaded CGIDEV2 ages ago before outside FTP was banned
on the client's machine, but I hadn't got round to doing anything with
it. I had an example program from the "Serving Up Spreadsheets"
article that I could verify was actually a workable solution within an
hour or so.

And that is the deciding factor when I'm looking to try something new:
can I prove it will work before I expend too much time on it. I
shudder to think how many projects I've launched myself into only to
have to abandon them completely when I found that some essential
factor was not described or not available. And no, I didn't always
learn something anyway, except not to waste my precious time.

In fact, my program doesn't much resemble Paul Tuoy's now, but the
main thing is that I had confidence early on that it could be made to
work; that I could actually get a spreadsheet out on MY client's
machine with MY skills and resources. And that's why it was the right
choice for me.

End of rant.

Another matter is related to how we have to write to the XML file - it
has to be done from top to bottom. If you have something in the header
(or some other item near the top) that you don't know until the whole
thing is written, using SED is a good approach, IMO. In the XLSX format
for a sheet, there is a <dimension> element with a value like A1:P3298 -
the 3298 is the number of the last row of data. I put in a
pseudo-CGIDEV2 marker and use what must be the SUBSTITUTE operation of
SED (s?).

Yes, I think you're describing how it works now. A line with the
(partial) value:
=SUM(R[-1]C:R[-1]C)

is changed by SED into this
=SUM(R[-/%NumRows%/]C:R[-1]C)

Finally, a thought about varying widths, especially in headers - use
Excel merge of the header rows - these can automatically center the text
over whatever set of columns you merge there, in the header - not in the
data, right?

I didn't get you at first. Every column has its own heading, but now I
think I see what you're getting at: I can have an overall heading
above the column headings - say with the selection criteria in it.
Food for thought.