Issue 103861 - Shared formula reference wrap issue with different grid size
Summary: Shared formula reference wrap issue with different grid size
Alias: None
Product: Calc
Classification: Application
Component: open-import (show other issues)
Version: DEV300m52
Hardware: All All
: P3 Trivial with 2 votes (vote)
Target Milestone: ---
Assignee: oc
QA Contact: issues@sc
: 95256 (view as issue list)
Depends on:
Blocks: 101565
  Show dependency tree
Reported: 2009-07-28 16:46 UTC by kyoshida
Modified: 2013-08-07 15:15 UTC (History)
2 users (show)

See Also:
Issue Type: PATCH
Latest Confirmation in: ---
Developer Difficulty: ---

proposed patch (15.10 KB, patch)
2009-07-28 16:47 UTC, kyoshida
no flags Details | Diff
test document (10.50 KB, application/
2009-07-28 16:51 UTC, kyoshida
no flags Details
revised patch per Daniel's comment (15.18 KB, patch)
2009-07-28 21:33 UTC, kyoshida
no flags Details | Diff

Note You need to log in before you can comment on or make changes to this issue.
Description kyoshida 2009-07-28 16:46:45 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.
Comment 1 kyoshida 2009-07-28 16:47:35 UTC
Created attachment 63824 [details]
proposed patch
Comment 2 kyoshida 2009-07-28 16:51:27 UTC
Created attachment 63825 [details]
test document
Comment 3 kyoshida 2009-07-28 16:52:36 UTC
In the test document, formula results in AP5:AP7 differ between Excel and Calc
because of the column size difference.
Comment 4 daniel.rentz 2009-07-28 17:10:18 UTC
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.
Comment 5 daniel.rentz 2009-07-28 17:11:47 UTC
*** Issue 95256 has been marked as a duplicate of this issue. ***
Comment 6 kyoshida 2009-07-28 21:33:56 UTC
Created attachment 63828 [details]
revised patch per Daniel's comment
Comment 7 mdxonefour 2009-07-29 13:49:58 UTC
MD: As discussed with UL and NN setting target to 3.1.1
Comment 8 daniel.rentz 2009-07-29 14:01:27 UTC
oops, forgotten to cc me
Comment 9 ooo 2009-07-29 20:13:49 UTC
Added issue to CWS calcooo311.
Comment 10 ooo 2009-07-29 22:28:37 UTC

In cws calcooo311:

revision 274467

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
Comment 11 ooo 2009-07-29 22:29:42 UTC
Setting fixed.
Comment 12 ooo 2009-07-30 21:19:29 UTC
Reassigning to QA for verification.
Comment 13 oc 2009-08-05 08:04:44 UTC
verified in internal build cws_calcooo31
Comment 14 thorsten.ziehm 2010-06-07 11:49:23 UTC
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] :