Re: XML Spreadsheets (CGIDEV2) - Progress and Problem

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

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