Issue 95039 - XML Import, Formula with Range-def, Relative Adressing.
Summary: XML Import, Formula with Range-def, Relative Adressing.
Alias: None
Product: Calc
Classification: Application
Component: open-import (show other issues)
Version: OOO300m9
Hardware: PC Windows XP
: P3 Trivial with 5 votes (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2008-10-16 22:48 UTC by yd7fz5x7
Modified: 2013-01-29 21:47 UTC (History)
3 users (show)

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

Aha, there it is, Example XML file. (8.71 KB, text/xml)
2008-10-16 22:50 UTC, yd7fz5x7
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description yd7fz5x7 2008-10-16 22:48:05 UTC
When: importing a excel-2003 (or compatible home made) XML-spreatsheet file.
Effected: formulas that have a Area-Ranges in them. (relative addressing)
What: imported formula string has incorect/additonal characters.
example1,source: "=SUM(R[-4]C:R[-2]C)"
example1,result: "=SUM(A1:A3]))" ... has additional '])'
example2,source: "=SUM(R[4]C:R[6]C,R[4]C[1]:R[6]C[1],R[4]C[2]:R[6]C[2])"
example2,result: "=SUM(A1:A3];[.B1:B3];[.C1:C3]))"
(target cell-addresses are correctly converted)

? can't find attachment option here,
XML example file availeble at Forum topic,
Comment 1 yd7fz5x7 2008-10-16 22:50:56 UTC
Created attachment 57186 [details]
Aha, there it is, Example XML file.
Comment 2 bz922x 2008-10-23 00:48:25 UTC
Is this a duplicate of issue 94261?

This version of the issue has a clearer description of the problem.
Comment 3 yd7fz5x7 2008-11-03 23:19:58 UTC
Yes, this is a duplicate of issue 94261.
(me newby, cq: not toughing/changing tracker settings.)
Comment 4 openofficeiscool 2008-12-16 22:28:13 UTC
This isn't exactly a duplicate of issue 94261.
That issue is related to an extra ] in the formula, which is easily fixable.

I've seen this bug in some important documents, where important calculations
actually reference the wrong cells, without any error at all! This could
possibly cause big problems!
Please mark as confirmed, also on Linux OS with latest Openoffice 3.
Comment 5 telehead 2009-01-29 00:19:10 UTC
I have also found the problem, however, I could find some consistency.  It
appears that employing the same formula

XML line: "=R[11]C+R[14]C+R[17]C+R[20]C" will correctly be set to
"=X13+X16+X19+X22", unless the column is in multiple of 26 (column Z, AZ, BZ...)
where the column reference changes

Col Z: should be "=Z13+Z16+Z19+Z22" but instead it's "=A13+A16+A19+A22"
Col AZ does not subtract 25, but rather show "=B13+B16+B19+B22"
Col BZ show "=C13+C16+C19+C22" 

Note that the column reference in the above case is always to the same column,
ie it only collect figures from different rows in the same column. 

A formula that refers to a column to the right or left of the current column is
always right,  however, there is an issue with the formula in the next visible
column following that col*26 (ie AB,BB,CB), where the formula should have been:

XML: "=RC[-2]+1", and imported should have read "=Z+1", instead it reads:
Col AB : "=A3+1" 
Col BB : "=B3+1"
Col CB : "=C3+1"

No error is generated, it just masses up the spreadsheet.  This is a BIG problem
in large spreadsheets where a miscalculation cannot be easily found, and can
have huge repercussions.
I hope this helps clearing the issue so it can be fixed.  If needs be, I can
provide the XML source to this problem.

Comment 6 Regina Henschel 2009-04-09 16:10:01 UTC
The Z-problem seems to be already tracked in issue 81233 and issue 90108.
Comment 7 slacka 2009-06-24 04:08:53 UTC
*** Issue 95039 has been confirmed by votes. ***