Re: XML Spreadsheets (CGIDEV2) - Progress and Problem

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