Re: gnumeric XML doesn't show result of formula

[John Machin <sjmachin lexicon net>]

Andreas J. Guelzow wrote:
> On Sun, 2008-02-03 at 07:22 +1100, John Machin wrote:
>   
> > Andreas J. Guelzow wrote:
> >     
> > > On Sat, 2008-02-02 at 12:55 +1100, John Machin wrote:
> > >   
> > >       
> > > > Hello,
> > > > Here's the results of a little experiment (start up the UI, type 
> > > > =sqrt(2) into cell A1, save as ...) for a few varieties of XML 
> > > > spreadsheet file formats:
> > > >
> > > > [my comments in [], whitespace adjusted by me]
> > > >
> > > > Excel 2007 .xlsx file:
> > > > <c r="A1">
> > > >     <f>SQRT(2)</f>
> > > >     <v>1.4142135623730951</v> [OK]
> > > > </c>
> > > >
> > > > Excel 2003 "Save as XML Spreadsheet":
> > > > <Cell ss:Formula="=SQRT(2)">
> > > >     <Data ss:Type="Number">1.4142135623730951</Data> [OK]
> > > > </Cell>
> > > >
> > > > OpenOffice.org Calc 2.x .ods file:
> > > > <table:table-cell table:formula="oooc:=SQRT(2)" 
> > > > office:value-type="float" office:value="1.4142135623731"> [only 14 
> > > > digits of precision]
> > > >     <text:p>1.41</text:p>
> > > > </table:table-cell>
> > > >
> > > > Gnumeric [1.6.3, Windows] .gnumeric file:
> > > > <gnm:Cell Col="0" Row="0">=sqrt(2)</gnm:Cell> [no result at all]
> > > >
> > > > Bug or feature? Is it fixed in the 1.8.1 release?
> > > >     
> > > >         
> > > I don't see a bug or feature...
> > >
> > > Excel 2007, 2003 and OO 2.x appear to give 2 values for each cell (and
> > > those values are even the same since none of the given floating point
> > > values are equal to the square root of 2.)
> > >
> > > So Gnumeric is more consistent by giving just one value.
> > >
> > >   
> > >       
> > I think that what you are trying to say is that it is impossible to 
> > represent the square root of 2 exactly as a floating-point number. This 
> > is true/well-known/irrelevant. What is of *practical* interest is the 
> > actual FP result of evaluation of any formula, irrespective of whether 
> > the result can be represented exactly or not. The two Excel outputs give 
> > in this case what appears to be the nearest possible FP result.  OO 2.x 
> > records an inaccurate result. Gnumeric gives *ZERO* results.
> >     
>
> You are comparing apples and oranges:  
>
> Gnumeric's xml file contains all information necessary to obtain the
> values you are looking for, you apparently would liek to have the value
> also stored. This is completely useless for the majority of users. 
>
> I doubt that you would be able to find those values easily in Excel's
> default file format either. Interestingly enough there you chose a file
> format that happens to include those values.
The formats were chosen because they were XML formats. The XLSX format 
*IS* the default format for Excel 2007. The default format for earlier 
Excels is documented both by Microsoft and by OpenOffice.org 
(http://sc.openoffice.org/excelfileformat.pdf). Finding "those values" 
easily in the earlier Excel files can be done by using (e.g.)  a perl 
package, Spreadsheet::ParseExcel or a Python package, xlrd 
(http://pypi.python.org/pypi/xlrd). Consideration is being given to 
expanding the latter to handle all those XML formats.

>  You could have also chosen
> such a format for Gnumeric. For example any text export can include any
> number of digits you may desire. Or you could export as an OpenDocument
> file which would also include both formulas and values if you prefer an
> xml format. But you probably wouldn't be happy with the number of digits
> provided:
>           <table:table-cell table:formula="oooc:=sqrt(2)"
> office:value-type="float" office:value="1.414213562">
>             <text:p>1.4142135623731</text:p>
>           </table:table-cell>
>
>   
That looks like a bug to me. It has exchanged the positions of the cart 
and the horse. The "value" attribute should show the precise value, 
while the contents of the "text" element should show the displayed value 
(e.g. 1.41 in the OOo-produced example I gave).

Higher-level considerations:

The object of the exercise is to be able to extract data from whatever 
spreadsheet format is presented:
* without requiring the originator to save it as a particular format
* without requiring the extractor to open it and save it as another format
* without requiring the presence of Gnumeric/OOo Calc/Excel on the 
extracting machine.

Sample use case: Organisation X is sufficiently enlightened to start 
allowing employees to use open source software. Department managers 
prepare budgets, periodical reports, etc using a standard template and 
the spreadsheet program of their choice. Some of the numerical values 
will be typed in, others will be generated by formulas. Head office will 
use an extraction software library to check and summarise the submitted 
files. Will work fine with Excel and OOo Calc [14 digits are enough for 
this app]. Gnumeric?

Cheers,
John