Apache OpenOffice (AOO) Bugzilla – Issue 15555
Assignment of data to axes not correct when importing xls file
Last modified: 2013-02-24 21:21:57 UTC
When opening a spreadsheet created in Excel for Office 2000 (9.0.2720) the series were assigned incorrectly to the two different X axis scales. It's relatively easily corrected by changing the series assignment once opened but it appears to be a bug. In this instance there were five data series, the first three were assigned to the left X-axis and the last two were assigned to the right x-axis. It appears that the import process mixed this up, perhaps assigning them alternately to each axis (i.e. first series-first axis, second series-second axis, third series-first axis, etc...). Not totally sure if this is a chart or a spreadsheet issue but I'll take a shot at entering it here first.
Created attachment 6822 [details] XLS File with chart that doesn't import properly
Hi, thank you for using and supporting OOo. I have to add a little correction to your report: what you are calling "X-axis" are the "Y-axis" When I open the document, I only see 2 data-series in the diagram. after correction I see 5 ones, and the right y-axix is from -30% to 100%. My correction was (all: translation from german menue-text): 1. rightclick: change data-area 2. enable checkbox "1rst row as text" 3. enable checkbox "1rst col. as text" 4. complete after that all data-series had disappeared 5. UNDO after that: 5 data-series, and the right y-axix is from -30% to 100%. Could you pls. att. a screenshot how the diagram looks in EXCEL? Rainer
Created attachment 6830 [details] screenshot shows the diagram after my "autocorrections"
My first test was with 1.0.3 german version WIN98SE. All the same with 1.1beta2 WIN98SE Rainer
Sorry about the axis confusion, I'm embarrassed by my carelessness there... I've enclosed a screen shot of the graph as it looks when the file is first opened in Excel. Also, although I'm not sure it's relevant in this discussion, the labels in the legend are lost in the OO import process.
Created attachment 6831 [details] Graph in Excel
in_excel.jpg shows a very different graph from that I can see in OOo. It's not only a problem of x/Y-axis, there is som more data disranged from the import. I suggest 1.1 as target milestone.
After further examination of the chart after import I have a few details that I'd like to add. In OOo the order of the data series as listed in the legend is column order. That is, they are listed alphabetically by column (H I J K L). In Excel the series are listed in the order in which I placed them into the chart (H J L I K). In Excel, series H,J,L are on the primary y axis and I,K on the secondary y axis. When initially imported into OOo the axis assignment is H,I,J on the primary and K,L on the secondary. By simply changing series L to the primary from the secondary axis and then series I to the secondary from the primary the graph was back to approximately where it was in Excel (ignoring differences in autoscaling and legend labels). I'll include a screen shot of that graph. This would appear to be an ordering problem in the series list during the import process but, of course, I'm completely ignorant of the underlying data structures. Please let me know if I can do any further comparison to help with this problem.
Created attachment 6844 [details] Graph after changing series axis assignments in OOo to fix problem
There are two problems. First, the data ranges used by data series are merged together by the Calc. This includes a reordering, hence you get H I J K L instead of H J L I K. As a consequence, instead of I and K, the columns K and L are attached to the second axis, which results in the weird scaling. This behaviour cannot be changed for OOo 1.1, but we will change this for OOo 2.0. Second, when setting the first column as Labels, you get one series less than before. When you do an undo, a new series is appended to the original set of series. As default it is attached to the first y-axis. Thus, you only have K attached to the second axis, which is more like what you want, but I is still on the first axis. This is a bug, because an undo should restore the state from before, but I don't see this as a show-stopper or big issue for OOo 1.1, therefore I would suggest to retarget this bug to OOo 2.0
retarget to OOo 2.0
HI Bjoern, one for you. tk
no further comment
*** Issue 19791 has been marked as a duplicate of this issue. ***
according to http://www.openoffice.org/servlets/ReadMsg?list=releases&msgNo=7690 this issue will be set to OOoLater
changed target to OOo 3.0
*** Issue 58595 has been marked as a duplicate of this issue. ***
removed keyword new_implementation
Changed Target to 2.x
I am seeing what looks like a related issue. overlaying two sets of data with identical x and y ranges produces axes that do not overlay perfectly as would be expected. y data range 0.000 - 7.500 only the second of every three lines maches, the other two have a small offset. in fact it is impossible to even overlay the base lines perfectly. Even entering the same numerical value for y posistion produces a one pixel error. Using cm or inch units has same result. There is also a variable error in placing y labels on the grid. In the above, 0.000 and 5.000 have a 2 px offset , 1.000 and 6.000 seem to be the only perfect overlay. In short it is impossible to overlay data for direct comparison. This is a severe limit to the usefulness of Calc. Seen using 2.02 on Gentoo linux. Thx.
->oooer: I don't see any relation to this issue. This issue is about an imported file, where the assoziation of data is wrong, i.e. data series have wrong values. What you describe seems like a layout problem, which sounds graphics engine-related to me. If I understand you correctly, two lines which should have the same pixel positions on the screen differ by 1 or 2 pixels. If you have an issue about this, please write a separate issue for that.
appologies if this in unrelated, I have opened a new bug report for this issue. #67350
->DR: There are some differences on import. The missing names in the legend are probably due to constant values as ranges. But the colors also differ in the new chart. And the second y-axis does not have the percent number format. Please have a look what can be fixed by the import filter.
This issue is fixed by the complete re-implementation of the chart module starting with SRC680m213. The series have the correct values and color now and are attached to the correct axis. Only the names of the series shown in the legend are still not imported correctly because of a missing feature in the chart which is addressed by the separate issue 11890.
verified in SRC680m222
dr->kla: please verify in master
seen ok in master -> closed