RE: [opendocument-users] Guidance for using cells in tables (ODFSpreadsheets)

[robert_weir@us.ibm.com]

Hi Bruce,

Good questions.

Think of this as a form of data caching.  To my knowledge all spreadsheets 
have been doing variations of this for the last 20 years.  When a 
spreadsheet is saved, it stores two values: the user input for the cell, 
whether a literal or a formula, the value of the cell in some canonical 
format, such as ISO-8061 date format, as well as the last calculated and 
formatted value of the cell. 

Full spreadsheet applications typically operate entirely on the original 
input for the cell, whether a formula or a literal, and then recalculate 
and reformat the cell as needed.  However, there are classes of 
applications which are lightweight and have no ability to recalculate and 
reformat cell contents.  (That's what makes them lightweight.)  For 
example, a viewer/plugin in a browser, or a search engine looking to 
full-text index a spreadsheet, or a library used by a programmer to create 
or manipulate a document.  These lightweight applications tend to work 
from the cached (last-calculated) values.   Conversion of a ODF 
spreadsheet to an HTML table is almost a trivial operation if you operate 
on the cached values.  But it would require the guts of a full spreadsheet 
application to do it otherwise.

An example.  Suppose I am German and I enter the value of Pi (truncated) 
into a cell A1.  The ODF generated might be:

<table:table-cell office:value-type="float" office:value="3.1415">
   <text:p>3,14</text:p>
</table:table-cell>

And that cell would also be associated with a numeric format defined as:

  <number:number-style style:name="N5000" number:language="de" 
number:country="DE">
            <number:number number:min-integer-digits="1"/>
   </number:number-style>

So there is the goodness of the style-format/content separation, but also 
the goodness of caching the last calculated and formatted result for the 
benefit of lightweight viewers and such.

Suppose then I enter the formula "=A1+1" into a cell.  The markup in the 
saved document looks like this:

<table:table-cell table:formula="oooc:=[.A1]+1" office:value-type="float" 
office:value="4.1415">
        <text:p>4,14</text:p>
</table:table-cell>

In that case we have three versions of the cell's value:

1) the original formula
2) the last calculated value in a canonical form.  Different locales could 
be applied to this value by apps which have that ability.
3) the last formatted version of that value (1-decimal, German locale)

Three versions for three different uses. Different apps will use different 
slices of the values.  Does this redundancy offer room for introducing 
inconsistency?  Certainly.  But I'm not aware of an alternative that would 
give the same ability to allow information extraction from applications 
ranging from lightweight scripts to mobile phone viewers, up to full GUI 
spreadsheet applications.

Regards,

-Rob