Issue 95039

Summary: XML Import, Formula with Range-def, Relative Adressing.
Product: Calc Reporter: yd7fz5x7 <mvgulik>
Component: open-importAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: issues, rb.henschel, tdgoodman
Version: OOO300m9   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---
Attachments:
Description Flags
Aha, there it is, Example XML file. none

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,
http://user.services.openoffice.org/en/forum/viewtopic.php?f=9&t=10637
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?
http://www.openoffice.org/issues/show_bug.cgi?id=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. ***