Issue 101471 - Import formulas in msoxl: namespace from ODS
Summary: Import formulas in msoxl: namespace from ODS
Alias: None
Product: Calc
Classification: Application
Component: open-import (show other issues)
Version: DEV300m44
Hardware: PC Windows XP
: P3 Trivial with 2 votes (vote)
Target Milestone: ---
Assignee: oc
QA Contact: issues@sc
: 102325 102362 (view as issue list)
Depends on:
Blocks: 103315
  Show dependency tree
Reported: 2009-05-01 14:05 UTC by Regina Henschel
Modified: 2013-08-07 15:14 UTC (History)
5 users (show)

See Also:
Latest Confirmation in: ---
Developer Difficulty: ---

Excel 2007 SP2 export to ODF (3.05 KB, application/vnd.oasis.opendocument.spreadsheet)
2009-05-01 14:06 UTC, Regina Henschel
no flags Details
ODF 1.0 test document (11.05 KB, application/vnd.sun.xml.calc)
2009-07-03 15:16 UTC, daniel.rentz
no flags Details
ODF 1.1 test document (22.59 KB, application/vnd.oasis.opendocument.formula)
2009-07-03 15:17 UTC, daniel.rentz
no flags Details
ODF 1.2 test document (23.29 KB, application/vnd.oasis.opendocument.formula)
2009-07-03 15:17 UTC, daniel.rentz
no flags Details
ODF 1.1 test document created by XL 2007 (9.51 KB, application/vnd.oasis.opendocument.formula)
2009-07-03 15:18 UTC, daniel.rentz
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description Regina Henschel 2009-05-01 14:05:18 UTC
Excel2007 is able to save in ODF-Format, when SP2 is applied. I'll attach such a
file. OOo can open the file, but the formulas have got a msoxl-namespace. OOo
should handle this namespace in a "proper" way.
Proposal: If "Load and convert the object" "Excel to OOo Calc" in Tool > Options
> Load/Save is checked, then the formulas are converted to oooc-namespace, if
the formula will be valid in OOo.
Comment 1 Regina Henschel 2009-05-01 14:06:42 UTC
Created attachment 61942 [details]
Excel 2007 SP2 export to ODF
Comment 2 daniel.rentz 2009-05-01 16:21:54 UTC
Excel writes the formulas in its own notation used in the OOXML file format. The
formulas should always be converted, regardless of some config settings. For
that, the formula parser implemented in the OOXML import filter of OOo could be
Comment 3 kyoshida 2009-05-01 16:52:42 UTC
Yup, this should be a simple process, of re-using the xlsx formula parser
already used in the xlsx import filter.  I believe the formula syntax used in
their ODF formula is the same as the OOXML one.
Comment 4 kyoshida 2009-05-28 17:12:07 UTC
*** Issue 102325 has been marked as a duplicate of this issue. ***
Comment 5 daniel.rentz 2009-05-29 12:50:51 UTC
CCed: er
Comment 6 daniel.rentz 2009-05-29 12:51:32 UTC
*** Issue 102362 has been marked as a duplicate of this issue. ***
Comment 7 daniel.rentz 2009-05-29 12:52:35 UTC
will be implemented in CWS dr71
Comment 8 daniel.rentz 2009-05-29 12:59:18 UTC
well, it is not *that* simple. :-) For example, external references are stored
diffently (not indexed as in OOXML, but with explicit URLs in the formula). And,
to be able to offer a UNO service, the implementation has to be made independent
from some internal base classes such as the omnipresent WorkbookHelper... But
then, we have all the advantages of the import parser, such as postprocessing of
function parameters etc.
Comment 9 kyoshida 2009-05-29 13:49:00 UTC
@dr: I figured it would not be *that* simple. :-)  I meant to say it should be
*conceptually* simple (of re-using the parser written for the ooxml import), but
of course there is always tons of road blocks in the implementation.

We developers all know that nothing is not quite as simple as it appears. ;-)
Comment 10 philhibbs 2009-06-02 11:58:05 UTC
The reason that the formulae are in a different namespace is that Excel uses
different semantics for formula evaluation - that is what namespaces are for, to
indicate different syntax and semantics. Specifically, one of the differences is
that direct references (as against range references) to text cells are converted
to numbers for expression evaluation. e.g. =A1+A2 will work even if A1 or A2 are
a text cell that contains something that can be converted to a number in the
current locale settings, whereas =SUM(A1,A2) will treat all text values as 0.

If OOo is to support the msoxl namespace, it should support its semantics as
well, and should leave the formula in that namespace so that the user knows that
the semantics are different to normal OOo operation and can manually debug the
conversion to OOo.
Comment 11 philhibbs 2009-06-02 13:09:57 UTC
Minor correction to myself - it's not that it's a direct reference, since
=SUM(A1,A2) is direct references as against =SUM(A1:A2) being a range reference,
yet it still does not do automatic number conversion. I am unsure as to the
exact rules that are used to determine whether or not conversion should be applied.
Comment 12 philhibbs 2009-06-02 16:10:49 UTC
Another thing to bear in mind is that my proposal of handling the msoxl
namespace with Excel-compatible semantics does not answer the question of
interoperability with other ODF implementations that already work in an
Excel-like way. Implementing this would give OOo interoperability with Excel,
but other spreadsheet applications e.g. Google Docs would evaluate OOo formulae
differently to OOo (in respect of string conversion).

In my ideal world, OOo should be the gold standard for behaviour in the default
ODF namespace that OOo puts its formulae in, and other implementations should
change their behaviour in that namespace, and all legacy Excel-compatible
behaviour should be shunted out into the msoxl namespace. To me, this is the
holy grail situation - Excel spreadsheets continue to work as they always have,
and OOo spreadsheets continue to work as they always have, and the end user can
migrate formulae from "old" behaviour to "new" if they want, or vice versa.

The thorny cases would be down to ODF spreadsheets created with other tools
whose default behaviour is Excel-like. Is there a way to tell what tool created
the document, and pop up a dialog box asking the user if they want to shunt all
formulae into the msoxl namespace the first time a Google Docs ODF file is
opened in OOo?
Comment 13 barthanssens 2009-06-03 08:28:08 UTC
"Is there a way to tell what tool created the document".

Yes there is: the meta:generator element in meta.xml. Although it is actually not 
a required element, it seems to be set by most (all ?) implementations.
Comment 14 daniel.rentz 2009-07-03 15:10:00 UTC
Fixed in CWS dr71. After this fix, Calc is able to import formulas from msoxl:
- cell formulas
- matrix formulas
- conditions of conditional formatting
- conditions of data validation

Note that this fix creates plain Calc formulas which do not retain the original
formula namespace as suggested by philhibbs. This needs a lot more work in the
Calc core. You may write an enhancement request for this.

This fix does not include support of external references (references to cells or
ranges in other spreadsheet documents). See issue 103315 for that.
Comment 15 daniel.rentz 2009-07-03 15:14:11 UTC
I will attach a bunch of test documents in ODF 1.0 (generated by StarOffice 7),
ODF 1.1 (generated by StarOffice 8), ODF 1.2 (generated by StarOffice 9), and
finally the document in ODF 1.1 with msoxl: formulas (generated by MS Excel 2007

The SO documents are useful to check that the regular ODF import has not been
broken by this fix.
Comment 16 daniel.rentz 2009-07-03 15:16:27 UTC
Created attachment 63348 [details]
ODF 1.0 test document
Comment 17 daniel.rentz 2009-07-03 15:17:02 UTC
Created attachment 63349 [details]
ODF 1.1 test document
Comment 18 daniel.rentz 2009-07-03 15:17:27 UTC
Created attachment 63350 [details]
ODF 1.2 test document
Comment 19 daniel.rentz 2009-07-03 15:18:20 UTC
Created attachment 63351 [details]
ODF 1.1 test document created by XL 2007
Comment 20 daniel.rentz 2009-07-09 13:27:37 UTC
back to QA
Comment 21 philhibbs 2009-07-09 15:35:36 UTC
Issue 103318 created to propose handling of alternative semantics in different
namespaces (e.g. msoxl).
Comment 22 oc 2009-07-23 14:48:25 UTC
verified in internal build cws_dr71
Comment 23 amy2008 2009-08-07 07:01:21 UTC
Verified in DEV300m54 on WinXP
Comment 24 walterwerner 2010-02-20 17:06:36 UTC
I have been sent an ods document created by excel 7 and do get
"msoxl:=MONTH(B2)" instead of =MONTH(B2) and other alike entries when opening
it. There is no troublesome fancy reference stuff, just pure simple cell references.
--> Is this the standard behaviour or did I miss the right option enabled?
--> Can I do anythoing about it (beside manual "guess, find and replace")
--> Are there any plans to increase the compatibility?
I am using oO 3.1.1

Comment 25 ooo 2010-02-22 10:39:41 UTC
@walterwerner: This issue should be fixed for OOo3.2, please install the new