Created attachment 138146 [details] Example file made with Excel 2013 If we create a combined chart (Column and Line) in Microsoft Excel and we set the column chart as the secondary Y axis, the X category axis will be positioned by the primary Y axis (Line chart). That means the X axis will cross the primary Y axis (Line chart) at the zero value. But if we open the file in LibreOffice Calc, the X axis will cross the secondary Y axis (Column chart) at the zero value. Another problem in this file that the Y axis major grid disappears when we open it with LibreOffice Calc. Steps to reproduce: 1. Create a new spreadsheet in Microsoft Excel. 2. Add values to a few cells as in the attached file. (There should be at least two data series, one for the column chart and one for the line chart) 3. Select the cell range that contains the data. 4. Create a simple Combined chart with a Line chart and a Column chart type. 5. Set the Column chart as the secondary Y axis. 6. Save the file and Open the file in LibreOffice Calc and compare it. Actual results: The Y axis major grid disappears. The X category axis will be positioned by the secondary Y axis (Column chart). That means the X axis will cross the secondary Y axis (Column chart) at the zero value. Expected results: The Y axis major grid should not disappear. The X category axis should be positioned by the primary Y axis (Line chart).
Created attachment 138147 [details] The example file saved by LO6 alpha
Created attachment 138148 [details] The two files in Calc and Excel side by side
Hi Gabor, I reproduce with LO 6.0.0.0.beta1 Build ID: 97471ab4eb4db4c487195658631696bb3238656c Threads CPU : 2; OS : Windows 6.1; UI Render : par défaut; Locale : en-US (fr_FR); Calc: CL
I write my results so far, maybe if somebody can continue to fix this bug. In this file: https://opengrok.libreoffice.org/xref/core/oox/source/drawingml/chart/typegroupconverter.cxx#562 If you write this: if (nAxesSetIdx == 0) nAxesSetIdx = 1; else //if (nAxesSetIdx == 1) nAxesSetIdx = 0; Right before this line: aSeriesProp.setProperty( PROP_AttachedAxisIndex, nAxesSetIdx ); Then the problem seems solved, but the Y axes will be switched and the export will be crap. In this file: https://opengrok.libreoffice.org/xref/core/oox/source/drawingml/chart/axisconverter.cxx#377 If you write this: if (nAxesSetIdx == 0) nAxesSetIdx = 1; else if (nAxesSetIdx == 1) nAxesSetIdx = 0; Right before this line: rxCoordSystem->setAxisByDimension( nAxisIdx, xAxis, nAxesSetIdx ); The Y axes will be switched and the appereance will be correct, but the export still will be crap. The export about this is maybe here: https://opengrok.libreoffice.org/xref/core/oox/source/export/chartexport.cxx#227 And here is some related stuff also: https://opengrok.libreoffice.org/xref/core/chart2/source/view/axes/VCartesianAxis.cxx#1059
Maybe this is also related stuff: https://opengrok.libreoffice.org/xref/core/chart2/source/view/axes/VCartesianAxis.cxx#1194
If I write in this file: https://opengrok.libreoffice.org/xref/core/chart2/source/view/axes/VCartesianAxis.cxx#1194 This: if( m_nDimensionIndex==0 ) //x-axis fCrossesOtherAxis = fMinY; Instead of this: if( m_nDimensionIndex==0 ) //x-axis { if( fCrossesOtherAxis < fMinY ) fCrossesOtherAxis = fMinY; else if( fCrossesOtherAxis > fMaxY ) fCrossesOtherAxis = fMaxY; The problem seems solved.
This only solves the fileopen of files created in Excel. If I want to create a diagram in LO, the export is still crap.
But if i make the LO diagram with the Excel settings (the setting of primary and secondary axes on the series are switched) then the export will be good and only the Y axes will be switched in the diagram.
So I guess, there are (at least) two problems: - The X axis appears at wrong place after the import - If a dataseries is a column, then changing the state of primary or secondary axis alignment will not be saved after the export
And I guess, this is why barcharts starts from zero, instead of top or bottom: https://opengrok.libreoffice.org/xref/core/chart2/source/model/main/Axis.cxx#213 ::chart::PropertyHelper::setPropertyValueDefault( rOutMap, PROP_AXIS_CROSSOVER_POSITION, css::chart::ChartAxisPosition_ZERO ); This is not relevant in the case of linecharts.
If I comment out the if and return statement here, the result seems to be same. https://opengrok.libreoffice.org/xref/core/chart2/source/view/axes/VCartesianAxis.cxx#1037 (In reply to Adam Kovacs from comment #6) > If I write in this file: > https://opengrok.libreoffice.org/xref/core/chart2/source/view/axes/ > VCartesianAxis.cxx#1194 > > This: > if( m_nDimensionIndex==0 ) //x-axis > fCrossesOtherAxis = fMinY; > > Instead of this: > > if( m_nDimensionIndex==0 ) //x-axis > { > if( fCrossesOtherAxis < fMinY ) > fCrossesOtherAxis = fMinY; > else if( fCrossesOtherAxis > fMaxY ) > fCrossesOtherAxis = fMaxY; > > The problem seems solved.
Created attachment 148394 [details] screenshot_114181_excel_vs_calc The results so far: If I apply the modification in comment 6, LO import and export will be good in the case of an existing chart in docx or xlsx file, but if you make a new chart with LO, save and reload, the X axis will still be at wrong place even in MSO (as you can see on the screenshot). If you want to make a new combined chart with LO have to follow these steps: 1. insert menu, chart, column and line, enter a value for the number of lines 2. check if it is needed to check first row or first column as label 3. reorder your dataseries if it is needed (the top ones will be column charts and the bottom ones will be line charts) 4. Excel make the line charts aligned to secondary Y axis, so you should change this also (double click on a linechart in LO) 5. Excel put the secondary Y axis to the beginning of the chart, so to switch the two Y axes in LO, double click to the Y axis on the left and select positioning and end
Some related stuff here: https://opengrok.libreoffice.org/xref/core/chart2/source/view/axes/VAxisProperties.cxx#224
One more note: When you create a new combined chart in LO, the problem is that all dataseries is aligned to primary axis (due to this, there is only one axis in a combined chart created by LO by default), so certainly you want to change some of them aligned to secondary axis (like MSO do it by default). If you make a barchart aligned to secondary axis, that wont be saves, after a reload it will reset to primary axis. But there is no such problem in the case of linecharts. If you make linecharts aligned to secondary axis, that will be saved well.
This is actually an import problem in the first place. I'm adding bugs related to the export.
Balazs Varga committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/+/11473832b5717cb3222ce72baee885bc9e8e2386%5E%21 tdf#114181 XLSX combined chart: fix swapped primary and secondary axes etc. It will be available in 6.3.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.