How should OOo deal with ods-spreadsheets producedby Excel 2010?
in [Open Office]
|
Prev: Results for the Community Council Elections(2010-03)
Next: [discuss] Removing ODF "Save As" function in MS Office
From: Eike Rathke on 23 Apr 2010 08:14 Hi Regina, On Monday, 2010-04-19 18:52:32 +0200, Regina Henschel wrote: > It seems, that there is consensus to translate all Excel formulas to > OpenFormula, if it is directly possible. If that is possible, yes. > But some questions remain: > > (1) What shall we do, when saving such an Excel-ods document as Calc-ods > document? For example currently > > <table:table-cell office:value-type="float" > office:value="1.7316533037537034" > table:formula="msoxl:=_xlfn.CONFIDENCE.T(0.05,3.7,20)" > table:style-name="ce1"> > <text:p>1,731653304</text:p> > </table:table-cell> > > in Excel-ods becomes > > <table:table-cell table:formula="of:=_xlfn.confidence.t(0.05;3.7;20)" > office:value-type="float" office:value="0"> > <text:p>#NAME?</text:p> > </table:table-cell> > > when opening and then saving as Calc-ods document. Generally, we could replace the prefix '_xlfn.' (btw, is that the only prefix they use?) with the OpenFormula conforming 'com.microsoft.' so the example would be table:formula="of:=com.microsoft.confidence.t(0.05;3.7;20)" > That seems questionable to me. > (a) xlfn.confidence.t belongs surely not to OpenFormula and likely will > never do. So the namespace "of" looks wrong to me. '_xlfn.' in namespace 'of' is wrong. > (b) The original value is totally lost. If recalculated there is no other choice. However, as you mentioned, for read-only documents there might be a possibility to keep the original results. > (2) Shall we support new functions at all, if they have no counter part > in OpenFormula yet? I don't see why we shouldn't, if there exists an exact definition of what a function does. > Implement them and using them with msoxl namespace? No. > Or implement a compatible own function, something like > org.openoffice.confidence.t? Implement and use CONFIDENCE.T in UI and COM.MICROSOFT.CONFIDENCE.T in ODFF. Additionally add an alias CONFIDENCE.T for ODFF that would resolve when reading a file written by later revisions in case CONFIDENCE.T was added to OpenFormula. I plan to add such an alias mechanism for OOo3.3, hopefully I get that ready in time. > (3) Some functions, for example CEILING.PRECISE(number;significance), > are partly compatible. We would get the same result, if we translate it > to CEILING(number; SIGN(number)*ABS(significance)). Gnumeric had used > such translations in former versions. I don't like doing so > automatically. I would prefer to set up a Wiki page, which explains to > the users, how they can translate the Excel solutions to Calc formulas > and leave it to the user to change the imported formulas manually. Valid approach. Before adding workload and implementing yet some other "user convenience functions" or their equivalent transformations I'd prefer to see enough requests for implementation. > (4) What do you think about my proposal to not convert anything, when > opening an Excel-ods document _readonly_, but show the contained values? > As far as I know, there exists no ods-viewer and therefore a Calc user > is currently not able to see, which values the Excel-ods document has > calculated. This should only be done on user request, not automatically. Open file formats are used to generate and edit documents with various tools, it is not guaranteed that whatever tool changes data and/or formulas also updates the results of formula cells. > (5) Should there be a warning, when a user opens an Excel-ods document, > which is not fully compatible to Calc-ods? Define fully compatible, and how to detect. If this is only about unknown functions then we could certainly do so. Btw, I hate pop-ups ;-) Eike -- OOo/SO Calc core developer. Number formatter stricken i18n transpositionizer. SunSign 0x87F8D412 : 2F58 5236 DB02 F335 8304 7D6C 65C9 F9B5 87F8 D412 OpenOffice.org Engineering at Sun: http://blogs.sun.com/GullFOSS Please don't send personal mail to the erl(a)sun.com account, which I use for mailing lists only and don't read from outside Sun. Use erack(a)sun.com Thanks.
From: Eike Rathke on 23 Apr 2010 08:40
Hi Regina, On Friday, 2010-04-23 14:14:46 +0200, Eike Rathke wrote: > On Monday, 2010-04-19 18:52:32 +0200, Regina Henschel wrote: > > > It seems, that there is consensus to translate all Excel formulas to > > OpenFormula, if it is directly possible. > > If that is possible, yes. To prevent misunderstandings and efforts: before we start to implement or treat special any new functions Excel came up with in the mean time, we certainly should implement what is defined in OpenFormula. Eike -- OOo/SO Calc core developer. Number formatter stricken i18n transpositionizer. SunSign 0x87F8D412 : 2F58 5236 DB02 F335 8304 7D6C 65C9 F9B5 87F8 D412 OpenOffice.org Engineering at Sun: http://blogs.sun.com/GullFOSS Please don't send personal mail to the erl(a)sun.com account, which I use for mailing lists only and don't read from outside Sun. Use erack(a)sun.com Thanks. |