Formulas in ODF-supporting applications

by jlundstocholm 27. April 2008 03:24

Some time ago I noticed that Fredrik e. Nielsen had posted a link in a Norwegian debate to a website comparing spreadsheet formula interop using ODF. The article is from 2005 comparing formula interop between OOo Calc 1.9.117 and KSpread from KOffice 1.4.1. The article is interesting since it highlights one of the more serious problems with lacking spreadsheet formula definitions in ODF. Some of the pictures in the article are missing and because the article is 2.5 years old, I thought it'd be interesting to take it for a spin again and see what has happened since 2005 in terms of interop between the two major ODF-implementations. I have done exactly the same as in the original article and have additionally added a bit of research to see where the problem really lies.

What did I do?

Well, on my brand new ubuntu 8.0.4 installation I installed KSpread 1.6.3 in addition to OOo 2.4 that came pre-installed with the system.

I created a spreadsheet using OOo 2.4 Calc with the data from the original article (formula OOo.ods (7.58 kb)

 



I then tried to open it using KSpread. This is what it looked like:

 



As in the original article I modified the formula to fit in KSpread and the result was:

The file s available here: formula KSpread.ods (5.44 kb)

When saving this file and opening it in OOo again, this was the result:

So there has actually (pheew) been some improvement in spreadsheet formula interop for applications using ODF Spreadsheets since 2005 ... thank God! At least now OOo is able to show the formula created by KSpread.

To take a more deep look into what was the cause of the problems, I added some information to the original spreadshee. The result is here: Since OOo can read the formulas from KSpread, I have opened the file using KSpread to demonstrate the problem:



The file is available here:(formula OOo exp.ods (9.30 kb)

So what should we conclude from this very basic test? Well, you tell me ... but at least, when someone next time tells you, that lacking formula spec in ODF is not a practical problem but only a theoretical problem ... please tell them that they are wrong.

Comments

4/28/2008 4:40:32 AM #

Doug Mahugh

Very interesting, thanks for sharing.  I'd say one conclusion is that rich interoperability between different implementations of the same editable document format is something we're all still working on.  There is much to do.

Hey, speaking of interop tests, I just tred to go back to the University of Central Florida interop tests that were published by the OpenDocument Fellowship (http://testsuite.opendocumentfellowship.org/), and the page isn't there any longer.  Anybody know why that was taken down?

Doug Mahugh United States |

4/28/2008 5:16:40 AM #

hAl

@doug
Try the .com domain

hAl |

4/28/2008 12:15:57 PM #

Rob Brown

Hi Jesper,

It's pretty obvious that the CEILING function implementation in KSpread is not correct according to the OpenFormula spec. See www.openmalaysiablog.com/.../mathematically-.html for a long description of the issue.

Google Docs makes the same error. I'll try Symphony later.

It looks, in your last screenshot, as though KSpread is also having an issue with the LOG function. Is that the case? If so, it is another implementation problem for KSpread. Google Docs handles it fine.

I think I've read that KOffice struggles for developers. That's a real pity IMHO, but a fact of life. Personally, I can't think of many things more tedious than coding on an office suite!

Rob Brown New Zealand |

4/28/2008 5:47:59 PM #

jlundstocholm

Rob,

The aim of my article was not to throw (any more) mud into the debate around OOXML and ODF and claim that OOXML is somehow better than ODF in regards to this. I think the time has passed on these games and I hope that we are now moving on to a more productive phase.

Whether the CEILING function is calculating the correct values or not was also not the reason of the article. The basic thing I tried to see was simply if the problem in the original article had been dealt with. It hadn't. When I split up the formula I could see that there was also an issue with the LOG-method.

The problem is that there is no interop between OOo Calc and KSpread since the latter does not understand the signature of the variant of CEILING that OOo Calc uses.

And yes - there is also an issue with the LOG-method (see the referenced ODS-file for details)

Smile

jlundstocholm Denmark |

4/28/2008 9:29:14 PM #

Rob Brown

Hi Jesper,

I wasn't suggesting anyone was throwing mud. Did you find the link to openmalaysia inflammatory? It wasn't supposed to be, I just thought that article was about as thorough a discussion on CEILING that anyone was likely to find.

In any case, I've done a couple more tests: KSpread 2.0alpha6 handles LOG properly but still doesn't handle CEILING. Lotus Symphony handles both just fine.

Your point that interop between OOo Calc and KSpread is limited by this issue is perfectly valid, but IMHO it's not a good illustration of the general situation regarding ODF. KSpread has implementation flaws, and flaws like that will kill interoperability no matter how good the spec is.

Perhaps the KSpread people have been waiting for OpenFormula to be properly added to ODF before implementing it fully (at www.koffice.org/developer/releasegoals2.0.php they say they aim for "OpenFormula support if the standard is ready").

And this is where I fully agree with you: "it's time to move on to a more productive phase". I really do hope that now the OOXML kerfuffle is over with, the ODF people will really make some progress on tying down ODF 1.2 with OpenFormula. From wiki.oasis-open.org/office/About_OpenFormula there's a comment that OpenFormula will really, really be complete before the end of 2006; yet the latest OpenFormula document from December 2007 still says that its status is "Pre-Draft". How does one define "complete"?

Rob Brown New Zealand |

4/28/2008 10:04:26 PM #

jlundstocholm

Hi Rob,

Yes - I did read most of the article from Yon Kit (isn't he the owner of the Malaysia blog?). But the aim of the article was to clearify why they think the CEILING implementation in OOXML sucked and not about how different implementations chose to implement it. The only reason I tested KSpread against OOo Calc was that this was what was done in the original article. KSpread was also in the default ubunto package repository, and since I have not yet figured out to install applications in ubuntu - this was what I used.

About OpenFormula: I have heard a lot of rumors about the lack of resources to complete it but nothing definitive yet. Maybe if some of the OpenFormula guys read this, they can help out with a bit of information regarding this?

jlundstocholm Denmark |

4/29/2008 5:02:42 AM #

pingback

Pingback from blogs.msdn.com

Doug Mahugh : Open XML links for 04-28-2008

blogs.msdn.com |

4/29/2008 1:49:22 PM #

Yoon-Kit Yong

Hi Jesper,

> Yon Kit (isn't he the owner of the Malaysia blog?)

Yes, I (Yoon Kit) am a contributor to OpenMalaysiaBlog, if thats of any relevance.

> The problem is that there is no interop between
> OOo Calc and KSpread since the latter does not
> understand the signature of the variant of CEILING that OOo Calc uses.

Like what Rob says, I think its clear that its not a failing of OpenFormula per se, but rather the KOffice team not implementing the intricacies of formulas in their apps at this point in time.

What is interesting is that we can see a path with the ODF/OpenFormula roadmap which makes things clearer. OOXML's path however is slightly more convoluted.

I tried to fix the OOXML issue with CEILING (as highlighted by the blog post) at the BRM, with a suggestion that was similar to the OpenFormula definition. However that was rejected, as Ecma preferred to have a different name to represent the function, so as not to break compat with old documents. Rick Jeliffe and I were wondering why defining optional parameters would be so difficult to implement, but we were merely participants in Ecma's party. It seems we had to look out for third party application developers in this instance.

Anyway, now implementors of OOXML will have to write functions for: CEILING, iso.CEILING and ecma.CEILING. Depending on the type of document, it shall default CEILING with ecma.CEILING for old docs, and iso.CEILING for newly created docs. [The naming convention is also interesting, and that is a  story for another blog post, which I may write when Ive fully caught up with work.]

So until we have Service Packs for MSOffice 2007, 2003, etc, we also face the same "interop" problem as we currently have with OOo and KOffice, until they fully implement OpenFormula.

Much regards, and keep up with your investigations in ODF/(OpenOffice.org <-> KOffice) and OOXML/(Microsoft Office <-> OpenOffice.org)! I think its great that more people are digging deeper into the marketing fluff.

yk.

Yoon-Kit Yong Malaysia |

4/29/2008 5:21:32 PM #

jlundstocholm

Yoon-kit,

Your connection to OpenMalaysiablog is not of any relevanse - it was a simple question. I am sorry we didn't talk in Geneva - but I was not told you were there until it was all over Friday afternoon (we couldn't see you guys in plain sight from where the Danish delegation was located because of the delegation sitting in the row in front of you.

Smile

Please also note that I would never say that the OpenOffice.org/KOffice bug is a failing of OpenFormula - on the contrary I would argue that having specs available for any area greatly diminishes the existance of bugs like the one in KOffice or OpenOffice.org - at least then you will have a spec to work on and not the implementation of another application.

So if anything, this is a big "thumbs-up" for the OpenFormula guys and an illustration of the fact that we really need their contributions in finishing up the work on OpenFormula.

IBM, Sun, Novell - please pour some more bucks/resources into OpenFormula TC and make sure that they can finish their work as soon as possible.

Smile

jlundstocholm Denmark |

4/29/2008 6:01:45 PM #

Yoon-Kit Yong

Hi Jesper,

> on the contrary I would argue that having specs available
> for any area greatly diminishes the existance of bugs like
> the one in KOffice or OpenOffice.org

Agreed. Its a matter of KOffice devs to implement the spec,
and we look forward for that day when/if it comes.

> at least then you will have a spec to work on and not
> the implementation of another application.

What makes me question the quality of the OOXML "Function Definition" is that it appears to be a reprint of the the Microsoft Office Formula Help files. It provides little more information than we already know. I hate to be negative, but when I hear Microsofties shout that OOXML has "Formula Definitions" while ODF doesnt, the plain fact is that the OOXML definitions dont bring any more value than the Help files which have been around for over 10 years.

Whereas the OpenFormula information appears to be a more complete document, in that it documents the nuances of other independent applications which have implemented their version of Formulas.

> IBM, Sun, Novell - please pour some more bucks/resources
> into OpenFormula TC and make sure that they can finish
> their work as soon as possible.

Hear Hear.

I wonder if its possible to harmonise these formula definitions with OOXML. That would really solve that problem once and for all.


More importantly, I think we need Microsoft's Macro definitions to be formalised. This is a major issue which needs to be resolved. There are many users which use macros and cannot interop with other apps because of this. Additionally the realm of "the Macro" is riddled with scary things like patents and stuff, which any independent developer wouldnt touch with a 10' pole. I hope Microsoft can open this up at least with the OSP, and help liberate the Novell work. (Also it could be the solution to the MS Office Mac dilemma).


yk

Yoon-Kit Yong Malaysia |

4/30/2008 6:20:01 PM #

hAl

The OpenFormula could spec could have been ready if IBM had spent the millions in resources they spent on OOXML in development of ODF.

hAl |

7/8/2011 2:35:09 AM #

pingback

Pingback from blogs.msdn.com

MSDN Blogs

blogs.msdn.com |

Comments are closed