Apache OpenOffice (AOO) Bugzilla – Issue 72994
Excel filter: 3D settings in Excel charts
Last modified: 2013-02-24 21:19:10 UTC
I'll attache a document, where you can see the error. The document containe'ss a picture of the 3D settings in Excel and a picture how the chart looks in Excel, so that you can easily compare it. The import in the current chart module is entirely wrong. The import in the new chart2 module ignores all Excel 3D settings and uses defaults. That's wrong too. The Excel "elevation" and "rotation" settings have to be converted to x-y-z angles and the perspective settings to 0 as in Excel or to "parallel".
Created attachment 41752 [details] Excel file with example and pictures
I confirm this probleme with Chart2m10
The attached spreadsheet shows, how the Calc rotation angles can be calculated from the Excel angles. I hope that the Excel angles are known. The writer document contains the explanation. The formulas are placed in the range A20:C30. The input of the Excel angles is in C2 and C4. The rest of the sheet is done for my own control. It might not be a very clever solution, I hope it will help solving the issue anyway.
Created attachment 41818 [details] calculation
Created attachment 41819 [details] description
@IHA: Please have a look.
->dr: Please take over. I think issue 71687 is duplicate but this one is much better documented so you might want to keep this.
Hi Regina You are right, the import/export of 3D settings is not implemented in the Excel filters. Thank you for the documentation which will help me to implement this much faster :-) Daniel
The attached PDF shows (1) an original chart in Excel (2) the same chart imported in Calc (wrong 3D settings) (3) the result after manually fixing the 3D settings As one can see, the result is still useless, because Calc does not move chart walls and labels into a position where they do not cover the columns. In addition, after saving this document with Calc to xls-format the 3D settings get lost again.
Created attachment 49078 [details] as mentioned above
I'm going to attach some files: DescriptionCorrected.odt I have improved the description and hope that I now got all special cases right. ElevAziToXYZAngles.cxx I've written a draft of a function, which converts the Excel's elevation and rotation to OOo's rotation angles. It is pure mathematics and doesn't depend on any OOo specific. You can use it where ever you want. Unfortunately I could not test it, because I do not know yet how to integrate it into the source. Testdocument.xlsx It contains a very simple 3D column chart in varying positions. For each position a text describes the settings for elevation and rotation in Excel 2007 and a picture shows how it looks in Excel 2007. When import of 3D settings is implemented, the position of the charts should look the same in OOo than on the pictures.
Created attachment 50608 [details] corrected description
Created attachment 50609 [details] function to convert Excel's elevation/rotation to OOo's XYZ angles
Created attachment 50610 [details] Textdocument for testing import of 3D column chart
set target to 3.x
CWS xmlfilter04 adds new helper properties at the diagram. Once this CWS is integrated, impoprt/export of 3D settings can be done easily.
*** Issue 81885 has been marked as a duplicate of this issue. ***
That is not completely correct. I integrated new properties for horizontal and vertical rotation and for perspective. But the rotation settings aren't correct yet for cases without right angled axes. Also an illumination helper is still missing.
*** Issue 84813 has been marked as a duplicate of this issue. ***
import/export from/to Excel works see following test document: http://sc.openoffice.org/testdocs/chart/chart_3dsettings_import_biff8.xls note that Excel does not use the Y rotation setting in 3d pie charts, but uses this value to change the "first slice angle". This feature is not supported in OOo 2.4.1, but will be implemented in OOo 3.0 (see issue 85166).
whoops, not fixed yet :-)
Now the case when right-angled-axes is off works also. There are three new UNO properties at the diagram (Perspective,RotationHorizontal and RotationVertical) which can be used for more simple access to these 3D settings, as using the D3DTransformMatrix and the D3DCameraGeometry was really a pain. I also added a new interface ::com::sun::star::chart::X3DDefaultSetter which can be used to quickly set a default rotation or illumination dependent on the chart type. ->Regina, thank you very much for the formulas, matrices, description and examples. That helped a lot! I started to use the attached method ElevAziToXYZAngles.cxx. It worked very good. Only as I offer the rotations via API I don't won't to restrict the angles in the same way excel does. So I needed more case differentiations for the correct signs. Hope I get them all correct now, phew. I added also a method to calculate the reverse direction based on the same equation system taken from comparing the elements of matrix M with matrix MS that you provided :-) . There were complains in this issue that walls and labels are not moved to positions where they do not cover the data points. I will have a look what I can do for that in addition and take over. ->dr: Thanks for the filter part! It works very good :-)
Fixed in CWS chart26. Rotation and perspective are imported and exported correctly now. Furthermore walls, grid lines and axes labels are placed automatically in a way that they don't hide the data points. As this bug is fixed now and I feel comfortable with the fix I would strongly suggest to integrate this to OOo 2.4.1. Without this fix working with 3D charts is very limited.
->Thomas, please verify in CWS chart26. I tested on windows the normal pure installation and also a patched 2.4 version.
@iha: What does your export in cases where the z-axis is not upright in the view in OOo? Excel doesn't know skew z-axis.
@regina: The z part gets lost in those cases, as there is no other possibility. I am thinking about removing the z rotation from OOo's UI also somewhere in the future, as I doubt that it is very useful.
Seen ok in CWS chart26 -> verified
@ regina Do you agree that it is fixed? Can you verify it? Mechtilde
I have tested in 2.4.1rc2 and it is not OK in all cases. Some charts are different from Excel: rotation 100°/elevation 20°, 100°/-20°, 90°/20°, 270°/20°, 90°/-20°, and 270°/-20°: The boxes are to small. Some vertical lines have stairs where in Excel they have not. This might be because the charts show perspective. In Excel 2007 a setting "no perspective" does no longer exists, but the setting 0.1° is used instead. This setting should be translated to "no perspective" in OOo or, if set perspective, the angle should be really 0.0° to get the same view as in Excel. In http://sc.openoffice.org/testdocs/chart/chart_3dsettings_import_biff8.xls the settings RelDepth=30% and RelHeight=30% are not correct, and the rotation 280° has wrong box widths. As far as I see, the angles are converted correct. I cannot test m15 because it is not public.
Excel 2007 still supports "no perspective" a.k.a. right-angled axes. There may be minor differences between Excel and OOo Chart in displaying 3D charts. This issue is about the correct import/export of the 3D settings. Differences in dispaying 3D charts are up to the developers of the chart module. Relative depth and height are not supported by OOo chart, therefore these settings cannot be imported from the test documents.
With "no perspective" I meant the situation in OOo, when you do not check the "perspective"-setting. Then you get a parallel perspective. That is very different from "right angles axes". Where do you find that setting in Excel 2007? In older Excel versions you could set perspective to 0, in Excel 2007 I can only find 0.1, which might be the same internally. Relative height can be imported by setting the hight of the chart (the part without title and legend). For the special situation here, with an upright axis, that gives the same look as in Excel. To not be misunderstood, I think, that this issue can be closed, when a 3.0 master is available. The achieved improvement is so great, that the changes should be released anyway in 2.4.1. and 3.0. The remaining problems can be addressed with new issues.
Ok, Regina, now I understand :-) Yes, in the case of "no perspective" in OOo Chart, one can think about a special handling in the export filter, as Excel does not have this setting. About the relative height setting: yes, it may be possible to "fake" this by setting an absolute size to the plot area, but Excel adjusts the height automatically if the chart object size is changed. Anyway, manual position and size of chart objects is not supported at all at this time (issue 30851). And yes, all this should be covered by separate issues ;-)
Closed because fix available in OOo2.4.1 (OOH_m17). For currently unresolved problems please open new issues.