OpenFormula

What's OpenFormula?

"OpenFormula" is a draft specification defining how to exchange formulas, primarily for spreadsheets and word processor documents (though it may grow to other uses too). I started the original OpenFormula project with a simple goal: I want to be able to exchange spreadsheets and word processing documents with other people without being locked into any particular vendor's format. People should own their data - not the program's vendor.

OpenFormula will be a part of the excellent OpenDocument specification. OpenDocument defines how to exchange office documents, and its specification states that there is an attribute table:formula where spreadsheet formulas go. OpenDocument is in general a good specification, but unfortunately, OpenDocument doesn't provide much as much detail on the syntax and semantics of formulas as I'd like. So, I founded the "OpenFormula" project to create a draft specification to address this weakness. Microsoft at one time complained about this "weakness" in OpenDocument, without mentioning that at the time (at least through Feb. 2006) they had they same weakness. Pot, meet kettle.

See "About OpenFormula" to get a summary about OpenFormula, including its current status.

History of OpenFormula

The original OpenFormula project's work went on via the openformula-discuss mailing list on SourceForge and the OpenFormula Wiki (no longer available). (with some early work going on in a previous Wiki). You can find the current draft at the original OpenFormula Wiki.

The OpenFormula project succeeded in creating a draft specification. That draft specification has now been contributed to OASIS. OASIS has, in turn, decided to use OpenFormula as a base document for its own work in creating a formula specification. I now chair the subcommittee that is taking OpenFormula and other material, and developing a full specification that will hopefully resolve specifying spreadsheet formulas.

At this point, the original OpenFormula project has been superceded. The original project successfully finished what it was meant to do -- it created a draft document for submission to a standards body. Standards bodies usually find it easier to start with a base document, so I think this has been a win for everyone.

Work continues, but now as part of the OASIS standardization process. If you're interested in standardizing formulas, please join the OASIS office-formula subcommittee which is actively working to complete a formula specification.

Originally, I thought the name would change, but since new names are hard to come up with, people have decided to keep calling the specification "OpenFormula". It's a simple and descriptive name, and that's a good thing.

Lex and Yacc files

I've posted here some work-in-progress on defining OpenFormula using bison (yacc) and flex. See these files: of.y, of.l, makefile, dotest, tests.

Older OpenFormula and related info

If you're looking for some historical documents about OpenFormula, here are some. Here is the current draft OpenFormula specification, OOo format (or PDF). Early discussions took place in the OpenDocument comments archive; that made sense as a starting point for an offshoot effort, but that's not a reasonable long-term location.

In October 2005 I created a test spreadsheet to see what happened in Microsoft Excel and OpenOffice 2's Calc. Here is formula-test2 in Excel .xls format, the formula-test2 in OpenDocument format as created by OpenOffice.org, the formula-test2 in HTML, and formula-test2 in text.

In Feburary 2005 I created an older test spreadsheet to see what really happens in various spreadsheets; here's the current test spreadsheet in Microsoft Excel format and OpenOffice 1 Calc format. Here's the extracted XML from the OOo format (the basis of OpenDocument, and the best "canonically correct" information I have). I've done experimenting with Microsoft Excel, OpenOffice.org, KSpread, Gnumeric, and the Palm PDA-based DocumentsToGo. Where Excel has features OOo does not, I've proposed what I think should be a good way to exchange that information. Note that the OpenOffice.org todo of March 22, 2005 identified "Validation of spreadsheet functions" as a to-do.

I also created a simple lex/yacc program to test out early versions of the syntax of OpenFormula. It's not the same as the current spec, but you might find it useful; patches to update it are welcome. Here it is: calc.l, calc.y and a trivial makefile.

Information on the Excel file format is at sc.openoffice.org.

Wikipedia's OpenFormula article has some information, as does the related Wikipedia OpenDocument article.