GNOME Bugzilla – Bug 636968
Error on reading formula referring to cell in line 65537 (ODS format)
Last modified: 2010-12-12 10:18:27 UTC
Created attachment 176181 [details] ODF spreadsheet with function referring to line 65537 Opening an ODS spreadsheet that contains a formula referring to line 65537 (one more than the traditional limit of 2^16 lines) pops the following error message: Sheet1!A2 Unable to parse 'SUM([.B3:.B65537])' ('Invalid expression') and the formula is converted to a value. This does not occur for .gnumeric files.
The problem is that when we are parsing the formula in A2 we don't know the size of the spreadsheet. (ODF in its wisdom does not store the spreadsheet size so when we encounter SUM([.B3:.B65537]) it is larger than the current size.) If you put the same expression in A65538 then we have already enlarged the sheet and everything works.
Yes, that makes sense. Then this is a BUG for ODF and not for Gnumeric, I guess...
Pedro, this is still a bug in Gnumeric; it will just be tricky to fix. (The simple fix would be to read the file twice, but that would be really annoying.)
Another way would be to create sheets as large as what OOo supports, load the file, and then reduce the size according to the actual row and column numbers.
I think it would make more sense to fix this first in the document format so that it benefits all ODF users. Once the ODF file states the spreadsheet size it's just a matter of adding a check... Doesn't it make sense for the Gnumeric project to join this (if you haven't already)? http://www.oasis-open.org/committees/tc_home.php?wg_abbrev=office
Jean, OOo is only one of several implementations creating ODS files so we would need to use Gnumeric's maximum sheet size and it would be very time consuming to determine the shrinked size afterwards. Pedro, changing anything in the ODF file format is a multi-year project. In any case this has been discussed in the OASIS ODF TC and it was declined. The minimum needed size can be inferred from the file content. One just needs to keep track of the references in the formulas... Regarding the Gnumeric Project joining the OASIS ODF TC: Membership in the TC requires paid up membership in OASIS which for an Organisation is quite expensive. So you are unlikely to ever see official Gnumeric representation on that TC. This problem has been fixed in the development version. The fix will be available in the next major software release. Thank you for your bug report.
Thank you for the fast and detailed answer (and obviously for fixing the bug) In my opinion that decision from the TC just shoves the problem to the programmers corner and makes ODS less efficient. I can't see how a simple line (or two) in a XML file could be a disadvantage... BTW I never imagined that joining a board for an open standard would require members to pay more than a symbolic value. I guess I'm too naive :( Thank you again!