Re: XML Spreadsheets (CGIDEV2) - Progress and Problem

Decided to press ahead and "finish" this task by enhancing the SED
script to handle the multiple sheets in my sample template and - what
the heck - by adding a column total to each sheet as well (something
provided in the original sample, but which I didn't bother with
initially).

A few Interesting Challenges, but nothing too horrifying.

A SED INSERT will insert as many times as it finds your search
argument (unlike SUBSTITUE, which only does the first one). I googled
a bit but couldn't find a simple way to limit to the first result, at
least not in POSIX. This was a problem because I was hanging the
/$line insert on the last column's heading text, which is the same in
every sheet. Anyway, I fudged it and put a unique value in a dummy
heading at the end, then made it white text (on white background) to
make it invisible.

For the column total the challenge was that you get an error if there
happens to be no data for that sheet (because the NumRows value is
zero, which causes a circular reference). So I just set that to 1 in
the RPG if no rows are fetched by the cursor, and now everything in
the garden is lovely.

So, what do we have now? The following set of objects: A sample RPG
program, an Excel template, and a SED script that is fully automatic
(no manual editing required for minor formatting changes) that
together do the following:

A quick template can be set up with headings and a sample line, and
the customer can be given that template to refine to his tastes
(column order & width/fonts/numeric formats, etc.) and then return to
me. I change the row of sample values to /%variables%/ and upload the
XML Spreasheet to the IFS.

I've already got the SQL I used to figure out the joins, group by,
etc. so I slot that into the cursor in the program and finish off that
and the CL and prompt (if any). I already had a slick email utility
that was easily adapted from .csv to .xls - in fact it's slightly
simpler because I no longer need a PF to .csv conversion stage since
the RPG is now writing directly to the .xls

Then I run the SED script to insert the control characters in the xml,
set up the run schedule or menu (or both) and we're ready to rock.

Customer decides that 'Customer Name' is gettting cut off too often
and he wants it a bit wider. Oh, and can he have the headings bold and
if it's not too much trouble take the commas (thousands separators)
out of ONE of the numeric columns but leave it in the rest.

No drama. I just open the template in Excel, make the cosmetic
changes, re-save it, re-run the SED script and we're back in business.
No hunting round in XML trying to twiddle style IDs and character
formats with the very real risk of screwing it all up and rendering it
non-functional.

When I embarked on this task I really didn't think I'd end up with
something so good.