Re: XML Spreadsheets (CGIDEV2) - Progress and Problem -- MIDRANGE-L

Nice work, Arnie! If I may, let me offer a couple ideas, although I know they might not fit your scenario.
.
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, and you can use level-break processing in RPG (whether cycle or DIY doesn't matter). This might also manage the no-data issue you mention here.

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?).

But there are times we can do a SELECT COUNT(*) kind of SQL statement to get that last-row value - this might be an "interesting" combination of WHERE clauses and CTEs, of course.

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?

That's it! Thanks for sharing the journey!

Cheers
Vern

On 10/31/2020 2:00 AM, Arnie Flangehead wrote:

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.