Apache OpenOffice (AOO) Bugzilla – Issue 95039
XML Import, Formula with Range-def, Relative Adressing.
Last modified: 2013-01-29 21:47:08 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
Created attachment 57186 [details] Aha, there it is, Example XML file.
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.
Yes, this is a duplicate of issue 94261. (me newby, cq: not toughing/changing tracker settings.)
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.
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.
The Z-problem seems to be already tracked in issue 81233 and issue 90108.
*** Issue 95039 has been confirmed by votes. ***