GNOME Bugzilla – Bug 650625
ODF import fails on $#REF!.$C$18
Last modified: 2011-05-20 21:10:47 UTC
Open the attachment to bug #650125 (the Indian Tax Return). We see many errors of the form Unable to parse '$#REF!.$C$18' ('Invalid expression') Unable to parse '$#REF!.$AM$15' ('Invalid expression') Unable to parse '$#REF!.$J$8' ('Invalid expression') Unable to parse '$#REF!.$C$6' ('Invalid expression') Unable to parse '$#REF!.$AO$19' ('Invalid expression') Unable to parse '$#REF!.$U$14' ('Invalid expression') Unable to parse '$#REF!.$M$6' ('Invalid expression') Unable to parse '$#REF!.$AM$28' ('Invalid expression') Unable to parse '$#REF!.$AL$7' ('Invalid expression') Unable to parse '$#REF!.$AD$22' ('Invalid expression') There references are created by some ODF consumers to indicate that the sheet has been deleted retaining the cell references, supposedly for easier debugging. We should be turning them all into #REF!.
We seem to have code in oo_cellref_parse that claims to handle this: ref->sheet = workbook_sheet_by_name (pp->wb, name); if (ref->sheet == NULL) { if (strcmp (name, "#REF!") == 0) { g_warning ("Ignoring reference to sheet %s", name); ref->sheet = NULL; } else { but we never get there! That code also just ignores the #REF! which isn't correct anyways.
The real problem is that these are simply not valid in this form in that place. If they are range references that should turn into #REF then they should be in named-range elements but they appear in named-expression elements. So there they should be '[$#REF!.$C$18]' or 'of:=[$#REF!.$C$18]' and the above code handles them. Since these undefined names become #REF! anyways. We really should simply not handle them.
Did you file this against localc?
This Indian Tax Return was created by OpenOffice.org/2.4. So to file it as a bug I need to create the same situation with a recent version of LO or OOo. (While opening and then saving the file in localc retains those named expressions. That is technically not a bug since retention is allowed if localc does not understand it.)
If I try to construct the situation in localc 3.3.2 I get: <table:named-expression table:name="ProblemName" table:base-cell-address="$Sheet2.$A$1" table:expression="[$#REF!.$A$1]"/></table:named-expressions> So the reference is correctly enclosed in []. I faintly recall a discussion in the TC about references such as [$#REF!.$A$1] but I cannot find them in the current 1.2 draft. Gnumeric reads this and interprets it currently as [.$A$1]. If [$#REF!.$A$1] is allowed then we are handling it wrong. If [$#REF!.$A$1] is not allowed then it is questionable whether we behave correctly and localc is making a mistake by writing it.
see also bug #650639
We are now handling some of these invalid expressions, for example all the ones listed in the original description. There are some that are even less valid and so we still raise some errors on this file.