how to tell m$ about ods formula behaviour failure

Readers,

Despite the opinion of the original poster
(https://bugs.freedesktop.org/show_bug.cgi?id=38113), the loss in
information is not the fault of LO, but m$, who claim not to support
such compatibility
(http://office.microsoft.com/en-za/excel-help/differences-between-the-opendocument-spreadsheet-ods-format-and-the-excel-xlsx-format-HA010355787.aspx).

In the "differences" web page published by m$, it is also claimed that
formulas are supported.

When a spreadsheet was created in LO (of course to the native ods
format, because we are not using LO as a free m$ clone and saving
documents to m$ formats, but instead using ods to promote odf, aren't
we??? :wink: ), after opening in m$ excel 2010 (yes, m$ can open ods
spreadsheets, so use that format please!!!) , the formulas were lost,
leaving only the result values in the spreadsheet cells.

Instead of reporting another erroneous LO bug, does someone know how
to forward this error with m4 excel 2010 to m$ bug reporting service?

Thanks.

Hi "e-letter",

e-letter schrieb:

Readers,

Despite the opinion of the original poster
(https://bugs.freedesktop.org/show_bug.cgi?id=38113), the loss in
information is not the fault of LO, but m$, who claim not to support
such compatibility
(http://office.microsoft.com/en-za/excel-help/differences-between-the-opendocument-spreadsheet-ods-format-and-the-excel-xlsx-format-HA010355787.aspx).

In the "differences" web page published by m$, it is also claimed that
formulas are supported.

When a spreadsheet was created in LO (of course to the native ods
format, because we are not using LO as a free m$ clone and saving
documents to m$ formats, but instead using ods to promote odf, aren't
we??? :wink: ), after opening in m$ excel 2010 (yes, m$ can open ods
spreadsheets, so use that format please!!!) , the formulas were lost,
leaving only the result values in the spreadsheet cells.

LO saves to ODF1.2, but Excel 2010 does not claim to support that version, but only supports ODF1.1.
Excel 2010 does not claim to support OpenFormula, but supports only its own formula namespace. So it is not a "bug" in Excel 2010 but a missing feature. Showing the result is better than LO does. LO shows an error message when getting an unknown function.

So there is no need to tell Microsoft a "bug".

Instead of reporting another erroneous LO bug, does someone know how
to forward this error with m4 excel 2010 to m$ bug reporting service?

Register for the trial of Excel 2013. It has an icon to report bugs. But be aware, that Excel 2013 supports ODF1.2. If you find, that a document, that has been generated by LO and which has no foreign elements (elements which are not defined in ODF1.2 but are in the OpenOffice.org or LibreOffice namespace) is not treated correctly in Excel 2013, than sent a bug report via this icon.

Kind regards
Regina

Hi :slight_smile:
MS Office uses  ODF 1.1 not 1.2.  The 1.1 had some troubles with retaining formulas.  The answer in LibreOffice and other office programs is to use the 1.2 which they have mostly been using for many years now.  The MSO answer is to buy their new MSO 2013.

I thought the original problem in this question was that saving in XlsX and then re-opening in LibreOffice caused problems.  So the bug-report is about that and the advice was to avoid using that format.  Originals in Ods and temporary copies for sharing in Xls (the older MSO format).  That advice about which format to use for sharing is for sharing is generally applicable even when using MSO and sharing with another MSO user,

Regards from
Tom :slight_smile:

e-letter wrote:

after opening in m$ excel 2010 (yes, m$ can open ods
spreadsheets, so use that format please!!!) , the formulas were lost,
leaving only the result values in the spreadsheet cells.

Instead of reporting another erroneous LO bug, does someone know how
to forward this error with m4 excel 2010 to m$ bug reporting service?

That is a well known "feature" of MS Office. Excel would replace formulas with calculated values. For some reason, MS thought that was appropriate, even though no other spreadsheet app does that. This was back in the days when MS was ramming their OOXML "standard" through ISO.

I believe, though don't know, that this has been corrected in later versions of MS Office. This is one bug that can be laid squarely at the feet of MS and one might even suspect it was deliberate.

Bottom line, there's nothing for LO to fix.

ODF 1.0/1.1 did not specify a standard for spreadsheet formulas. Formulas were left implementation-specific. Microsoft did not support the OpenOffice.org-specific formulas. Instead, they used Excel-specific formulas in ODF 1.1. On input of a not-supported formula expression, Excel in Office 2007 and 2010 drops the formula and preserves the last-calculated value. Whether a wise choice or not, that is what's done.

As Regina says, Office 2013 supports ODF 1.2, including its OpenFormula specification. OpenFormula is also used by current implementations of LibreOffice and Apache OpenOffice when their documents are saved as ODF 1.2, so there is interoperability of formulas shared between ODF 1.2 implementing software.

- Dennis

To clarify (without m$ 2013 to view), when a spreadsheet in LO is
created in the (default) version 12, a user with m$2013 will be able
to see formulae, whereas earlier versions e.g. m$2012 will shown only
the results of the formulae calculations.

Therefore, users should be encouraged to create new spreadsheets in
the native LO odf and encourage recipients to either: (a) use LO or
buy m$2013 in order to view openformula formulae or (b) view formulae
results _only_ in earlier legacy m$ software.

Hi :slight_smile:
Yes.  With regards to paragraph 1 with the amendment that i think you mean MSO 2010 rather than the non-existent MSO 2012.  There is a version for Mac called 2011 but that is really just 2010 redone for Mac and it takes them about a year to do that.  MSO just doesn't develop that fast.  It's typically 3 or 4 years between releases which is one reason they have such problems with security and need such frequent patches.

For the 2nd paragraph neither option is optimal.  It's why almost all of us on almost all lists recommend keeping an original in ODF but use "Save as" to to the older MS formats when sharing with other people.

When people start using LibreOffice there is no need for them to ever buy a new version of MS Office.  They can keep using their ageing one if they feel the need but will probably find they 'need' it less and less as they get used to doing everything with LibreOffice and other OpenSource tools.

It is generally considered extremely foolish to get rid of the old suite at least until after "the end" of the migration process.  It's what MS recommends.

For their own products they mitigate against the problems by offering training and encouraging people to become "Microsoft Certified" in the new version.  It costs quite lot.  The training, books, certificates are all published by MS and each is quite expensive.  It builds-up the "blame the user" culture of the MS world.

During a migration even quite hefty bumps in the road are easy to work-around by using the older tool that you are familiar with.  That gives the user plenty of time to work out how to deal with the bump in LO.  It might be something simple that most people don't use, such as not knowing where "undo" is in the menu ("Ctrl z" or "right-click and undo" are usually faster than moving the mouse up to the top of the screen).

If the user had followed the idiotic "Microsoft-world advice" of getting rid of the old then not knowing how to "undo" could then de-rail the move away from MS products and with an added push of stupid advice form the MS world could rack up extra costs of re-purchasing old versions of MS Office again.

So, the usual advice within the OpenSource world and given to newcomers is to keep the old version near at hand while trying out the new version.  For OpenSource products it's not an issue because all the various options are cheap and easy so you can uninstall, reinstall, test-drive, virtualise, parallel install (alongside), LiveCd it and back again without having to repurchase or re-license anything.  For MS products it means not uninstalling in the first place.

Just in case anyone thinks my example of the "undo" was too idiotic and foolish i have to say i agree completely.  Every time it happens i just shudder or laugh.  The last example was a German (?) city that then went and reported to the press blaming LO but in the course of the article it became obvious that the city had been extremely foolish (and that it wasn't even LO that they had been upgrading too).

So, no don't buy new versions of MSO but also don't entirely switch to always using ODF.  At least, not yet.  Be sensible.  If your organisation has LibreOffice installed for everyone then you can stick to using ODF internally but when sending documents to people outside of your organisation you will have to use an MS format.  The new ones are rubbish (ime) so stick with the older MS formats (for MS Office Xp, 2000, 2003, 98 etc) because everyone can read them.

ODF usage is spreading and becoming more common-place but many people still stick with the whimsical nature of the MS formats that change unpredictably and never quite work the same way on any 2 machines due to random changes of a single company.

The "therefore" in e-letters post is patently absurd.  Again.  Instead try

c)  Give people a link so they can download LibreOffice, perhaps the portable version
d)  Use ODF for yourself, for your own use, for your main copy of the file and then share by first taking a snapshot of your file in MS format = "Save as MS Xp format"

Regards from
Tom :slight_smile:

Tom Davies wrote:

Hi:)
Yes. With regards to paragraph 1 with the amendment that i think you mean MSO 2010 rather than the non-existent MSO 2012. There is a version for Mac called 2011 but that is really just 2010 redone for Mac and it takes them about a year to do that. MSO just doesn't develop that fast. It's typically 3 or 4 years between releases which is one reason they have such problems with security and need such frequent patches.

A minor complaint. Some people here feel it's necessary to send replies to people, as well as the list. This is not necessary, as we receive the list copy and don't need another. Post it to the list and we'll see it.

Without purchasing (subscribing for the Office 365 Home option) for Microsoft Office 2013, there is the option to use the Excel Web Application on Skydrive. That's free and Skydrive will accept ODF 1.2 Spreadsheet documents with their OpenFormula formulas.

As an alternative, can anyone confirm if formulae are kept in xml
formatted spreadsheets, for example of gnumeric or LO calc xml
spreadsheets are imported by m$? For example simple formula from
gnumeric:
...
      <gnm:Cells>
        <gnm:Cell Row="0" Col="0" ValueType="40">3</gnm:Cell>
        <gnm:Cell Row="1" Col="0" ValueType="40">5</gnm:Cell>
        <gnm:Cell Row="2" Col="0" ValueType="40">3</gnm:Cell>
        <gnm:Cell Row="3" Col="0">=(A1*A2)/A3</gnm:Cell>
      </gnm:Cells>
...

from LO calc, saved as fods:
...
<table:table-row table:style-name="ro1">
               <table:table-cell
table:formula="of:=([.A1]*[.A2])/[.A3]" office:value-type="float"
                                 office:value="5">
                  <text:p>5</text:p>
               </table:table-cell>
            </table:table-row>
...

from LO calc, saved as m$ 2003 xml:
...
<Table ss:StyleID="ta1">
      <Column ss:Width="64.2614"/>
      <Row ss:Height="12.8126">
        <Cell>
          <Data ss:Type="Number">3</Data>
        </Cell>
      </Row>
      <Row ss:Height="12.8126">
        <Cell>
          <Data ss:Type="Number">5</Data>
        </Cell>
      </Row>
      <Row ss:Height="12.8126">
        <Cell>
          <Data ss:Type="Number">3</Data>
        </Cell>
      </Row>
      <Row ss:Height="12.8126">
        <Cell ss:Formula="of:=([.A1]*[.A2])/[.A3]">
          <Data ss:Type="Number">5</Data>
        </Cell>
      </Row>
    </Table>
...

Further investigation of the m$ output xml spreadsheet:

...
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="4"
x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">
      <Row>
        <Cell>
          <Data ss:Type="Number">5</Data>
        </Cell>
      </Row>
      <Row>
        <Cell>
          <Data ss:Type="Number">3</Data>
        </Cell>
      </Row>
      <Row>
        <Cell>
          <Data ss:Type="Number">3</Data>
        </Cell>
      </Row>
      <Row>
        <Cell ss:Formula="=(R[-3]C*R[-2]C)/R[-1]C">
          <Data ss:Type="Number">5</Data>
        </Cell>
      </Row>
    </Table>
...

(Un)Surprisingly, m$ displays the value of the formula but discards
the formula from the formula bar within the gui, even though a formula
exists within the xml element.

Hi :slight_smile:
I think they fixed the problem in MS Office 2013. Prior to that for
spreadsheets in ODF formats MS Office would apparently lose the
formula and leave values as a fixed.

Errr, i think there is a lot more there but i only managed to make
sense of the last paragraph. Hopefully others can help with the rest!
Regards from
Tom :slight_smile:

I no longer follow these lists, so I'm of limited help.

If gnumeric produces an ods file, Excel 2013 should attempt to read it.
However, the format you gave as an example is not ODF and I am doubtful. I
don't have gnumeric, so I can't check that case.

Correct, the output provided previously was the native format;
gnumeric was one of the first spreadsheets to use xml as a native file
format. Below is an extract of the gnumeric output when the
spreadsheet is saved as gnumeric ods (without foreign elements, a
gnumeric option):

<table:table table:name="Sheet1" table:style-name="ta-v-lr">
        <table:table-column table:default-cell-style-name="ACE-0x8a4a468"
table:style-name="ACOL-0"/>
        <table:table-column table:default-cell-style-name="ACE-0x8a4a468"
table:style-name="ACOL-0" table:number-columns-repeated="255"/>
        <table:table-row table:style-name="AROW-0">
          <table:table-cell table:style-name="ACE-0x8a4a468"
office:value-type="float" office:value="3">
            <text:p>3</text:p>
          </table:table-cell>
          <table:table-cell table:number-columns-repeated="255"
table:style-name="ACE-0x8a4a468"/>
        </table:table-row>
        <table:table-row table:style-name="AROW-0">
          <table:table-cell table:style-name="ACE-0x8a4a468"
office:value-type="float" office:value="5">
            <text:p>5</text:p>
          </table:table-cell>
          <table:table-cell table:number-columns-repeated="255"
table:style-name="ACE-0x8a4a468"/>
        </table:table-row>
        <table:table-row table:style-name="AROW-0">
          <table:table-cell table:style-name="ACE-0x8a4a468"
office:value-type="float" office:value="3">
            <text:p>3</text:p>
          </table:table-cell>
          <table:table-cell table:number-columns-repeated="255"
table:style-name="ACE-0x8a4a468"/>
        </table:table-row>
        <table:table-row table:style-name="AROW-0">
          <table:table-cell table:style-name="ACE-0x8a4a468"
table:formula="of:=([.A1]*[.A2])/[.A3]" office:value-type="float"
office:value="5">
            <text:p>5</text:p>
          </table:table-cell>
          <table:table-cell table:number-columns-repeated="255"
table:style-name="ACE-0x8a4a468"/>
        </table:table-row>
        <table:table-row table:style-name="AROW-0">
          <table:table-cell table:number-columns-repeated="256"
table:style-name="ACE-0x8a4a468"/>
        </table:table-row>
        <table:table-row table:style-name="AROW-0"
table:number-rows-repeated="65531">
          <table:table-cell table:number-columns-repeated="256"
table:style-name="ACE-0x8a4a468"/>
        </table:table-row>
      </table:table>

Similarly, gnumeric output ods with foreign elements:

<table:table table:name="Sheet1" table:style-name="ta-v-lr">
        <table:table-column table:default-cell-style-name="ACE-0x9882780"
table:style-name="ACOL-0"/>
        <table:table-column table:default-cell-style-name="ACE-0x9882780"
table:style-name="ACOL-0" table:number-columns-repeated="255"/>
        <table:table-row table:style-name="AROW-0">
          <table:table-cell table:style-name="ACE-0x9882780"
office:value-type="float" office:value="3">
            <text:p>3</text:p>
          </table:table-cell>
          <table:table-cell table:number-columns-repeated="255"
table:style-name="ACE-0x9882780"/>
        </table:table-row>
        <table:table-row table:style-name="AROW-0">
          <table:table-cell table:style-name="ACE-0x9882780"
office:value-type="float" office:value="5">
            <text:p>5</text:p>
          </table:table-cell>
          <table:table-cell table:number-columns-repeated="255"
table:style-name="ACE-0x9882780"/>
        </table:table-row>
        <table:table-row table:style-name="AROW-0">
          <table:table-cell table:style-name="ACE-0x9882780"
office:value-type="float" office:value="3">
            <text:p>3</text:p>
          </table:table-cell>
          <table:table-cell table:number-columns-repeated="255"
table:style-name="ACE-0x9882780"/>
        </table:table-row>
        <table:table-row table:style-name="AROW-0">
          <table:table-cell table:style-name="ACE-0x9882780"
table:formula="of:=([.A1]*[.A2])/[.A3]" office:value-type="float"
office:value="5">
            <text:p>5</text:p>
          </table:table-cell>
          <table:table-cell table:number-columns-repeated="255"
table:style-name="ACE-0x9882780"/>
        </table:table-row>

A quick diff comparison shows no significant differences in this context.

When I saved the file from LibreOffice in *.ods format, Excel 2013 opened it
fine and the formulas worked. (B1 and C1 were changed and the formulas
could be seen on the cells.)

Thanks, this is good to know; m$ users can be more confidently advised
to either upgrade to versions >=m$2013 or any other odf compliant
software.