Apache OpenOffice (AOO) Bugzilla – Issue 103861
Shared formula reference wrap issue with different grid size
Last modified: 2013-08-07 15:15:15 UTC
When importing an xls document that uses shared formulas, Calc uses range name mechanism to emulate it. The problem arises when a relative reference stored in the shared formula tokens wraps beyond column/row boundaries because the reference then wraps from column 0 to column max and vise versa. The same with row-direction wrapping. At that point, the maximum grid size inconsistency leads to resolving the reference in an inconsistent way. For instance, when importing an xls document, created by a version of xls with 256 column size limit, into Calc which now allows 1024 columns, the referenced cell addresses change. I have a patch to resolve this problem, by setting explicit column/row size limit to use per range name instance. So far it seems to work well.
Created attachment 63824 [details] proposed patch
Created attachment 63825 [details] test document
In the test document, formula results in AP5:AP7 differ between Excel and Calc because of the column size difference.
Double to issue 95256, but keeping this open because of PATCH. dr->kohei: in sc/source/filter/excel/namebuff.cxx, replace the hard sheet limits by BIFF dependent limits (in BIFF2-BIFF5, 16K rows are used instead of 64K) by using the XclRoot::GetXclMaxPos() function. dr->er: as the patch mostly changes ScCompiler and ScRangeData, please review.
*** Issue 95256 has been marked as a duplicate of this issue. ***
Created attachment 63828 [details] revised patch per Daniel's comment
MD: As discussed with UL and NN setting target to 3.1.1
oops, forgotten to cc me
Added issue to CWS calcooo311.
Thanks! In cws calcooo311: revision 274467 sc/inc/compiler.hxx sc/inc/rangenam.hxx sc/source/core/data/cell2.cxx sc/source/core/inc/refupdat.hxx sc/source/core/tool/compiler.cxx sc/source/core/tool/rangenam.cxx sc/source/core/tool/refupdat.cxx sc/source/filter/excel/namebuff.cxx sc/source/filter/excel/xeformula.cxx Note: patch needs to be applied using --ignore-whitespace because in the go-oo repository all tabs are replaced with spaces. Further, the go-oo sc/source/filter/xlsx/ subdirectory doesn't exist in OOo, so the patch to sc/source/filter/xlsx/xlsx-xeformula.cxx is to be ignored. Side note: the attached test case document contains a named range 'E21000.' that evaluates to $Evals.$#REF!$#REF! in OOo, but also in Excel to Evals!#REF Note the dot in E21000. so it's not a cell address, such a name currently would be invalid in OOo and could not be created via UI dialog, though usage in formulas would be fine. The result values in AP5:AP7 are fine with this patch. Test case of issue 95256 also fixed http://www.openoffice.org/nonav/issues/showattachment.cgi/57352/Urenplanning2008_RvS.xls
Setting fixed.
Reassigning to QA for verification.
verified in internal build cws_calcooo31
This issue is closed automatically. It is in state 'verified/fixed' since 2 releases (OOo 3.1.1 and OOo 3.2). The policy [1] indicates that such older issues should be closed. If this issue still occur in a current build (OOo 3.2.1 or >DEV300m80) please reopen the issue and set the target accordingly. [1] : http://wiki.services.openoffice.org/wiki/Handle_fixed_verified_issues