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

OK, made the SED change and it works great. So happy with this.

The SED is just to automate all the revisions required to the XML to
make it work with the RPG. I want the customer (and me) to be able to
make any changes to the template in Excel itself (i.e. WYSIWYG), but
every time you save the XML spreadsheet the template is replaced and
the control codes are wiped out.

I could make minor changes directly in the xml stream file in the IFS,
but if I screw it up I'll have no idea how to fix it, and something
major like adding a column (and all its formatting) is far beyong my
ability.

With SED called by CL I can replace the control codes and make
necessary changes (e.g. maximum rows) with a single call. And other
RPG programmers can maintain and understand the programs.

Here's the script for the one I've been testing with:

# Insert the CGIDEV2 section name as first line
1i \
/$start
# Increase the file size as required
s/RowCount="2"/RowCount="30000"/
# Insert section LINE after the last heading
/Currency/ {
n
n
i \
/$line
}
# Change String to Numeric where required
/APCAMP/s/String/Number/
# Insert section END after the last field name
/t_VCURR/ {
n
n
i \
/$end
}

On 10/25/20, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx> wrote:

Hi Arnie

Since you are using CGIDEV2, I think you want to put the data type in
the template - the Excel file is actually an XML file, you can open it
in a text editor and make the template whatever you need.

I don't know why you should need a SED script - I use SED in the things
I create only when the value is not known at the time I write to the
file - in the XLSX format there is a dimension element that specified
the size of the active data block, with the last row in it - sometimes
there are ways to calculate that at the beginning, sometimes not too
easily.

If the cell you're working with has different data types, you can use
different CGIDEV2 sections for that - I'm talking also about column
headings and the like.

You could also have a section for rows, and you could have a CGIDEV2
variable for the data type.

Of course, I don't know everything you're doing, so maybe this doesn't
apply for your needs. But maybe it does.

Regards
Vern

On 10/24/2020 5:00 PM, Arnie Flangehead wrote:

Vern, you're a genius!

I had low hopes when I tried it, because it seemed too simple. But
sometimes the best solutions are simple. Not only does Excel now "see"
the numbers, but the thousands separators have turned up as well.

So satisfying to fit the final piece in the puzzle.

I can always use my handy-dandy SED script that Patrik helped me with
to change the line from "String" to "Number", but you wouldn't happen
to also know if there's a way to pre-set it in Excel while I'm
designing the template would you? The cells are already defined as
Number there, with decimal places and thousand separator, but somehow
they become "String" when saved.

To answer your questions: I only have Office 2003 (it was good enough
for my Grandpappy and it's good enough for me). I don't know that the
technique I'm using works with later versions.

Regarding xml vs. xls: When you save the template you get an xml, but
when you run the program it writes an xls.

On 10/25/20, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx> wrote:

Hello Arnie

I haven't used the XML 2003 Spreadsheet format for years, I use the Open
Office XML format (XLSX extension).

But there have been lots of examples early on by Jon Paris and others of
the XML format. I think there are some on the easy400 dot net site.

First, have you been able to use the XLS extension? That was one my
issues - thought it had to have the XML extension. But that's history
here, now.

As to numbers, I looked up about the format - it seems there is a data
type attribute to use in the <Data> child of the <Cell> element, here's
an example I found -

<Cell><Data ss:Type="Number">0000033</Data></Cell>

Have you tried that? There might be further attributes or styles for
cells, I'd have to look at the result of saving a spreadsheet in this
format to see.

HTH
Vern

On 10/23/2020 9:33 PM, Arnie Flangehead wrote:

Very good progress with this. Having the SED script to reinsert the
control
codes every time the template is enhanced/overwritten has made this a
practical technique.

I've written several reports now and an XML Spreadsheet (xls type) has
several advantages over the .csv files I've been producing hitherto:
The
ability to preset the column widths; being able to define a column as
text
and thereby stop Excel from "helping" me by changing codes it thinks
are
dates or decimal values, but really are just codes (lots of those in
the
database); and the ability to "stack" headings two or three words deep
where the code is short but the description is long.

However, there is one fly in the ointment: Numeric fields.

I've figured out that I need to explicitly right-align the column and
set
the number of decimals in the template, then use %char() with
updHTMLvar()
in the program. The numbers look great, but Excel in a sense can't
"see"
them. Summing a column using Autosum produces zero. Selecting several
figures and looking at the status bar there's no sum/avg/max/min
available.
It seems that when you type or paste a value into a cell it goes
somewhere
that these imported values aren't going.

Has anyone encountered this problem? It's not a show-stopper: If
manipulation of figures is a priority I can still produce a .csv (whose
numbers are fully functional, if ugly), and if presentation is more
important I now have an alternative, but undoubtedly someone is going
to
eventually demand both.

Thanks

Arnie

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com