Apache OpenOffice (AOO) Bugzilla – Full Text Issue Listing
|Summary:||Excel filter: 3D settings in Excel charts|
|Product:||General||Reporter:||Regina Henschel <rb.henschel>|
|Status:||CLOSED FIXED||QA Contact:||issues@graphics <issues>|
|Priority:||P3||CC:||daniel.rentz, eva-email, IngridvdM, issues, pagalmes.lists, tony.galmiche.ooo|
|Version:||3.3.0 or older (OOo)||Keywords:||ms_interoperability, usability|
|Issue Type:||DEFECT||Latest Confirmation in:||---|
Description Regina Henschel 2006-12-29 15:25:33 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".
Comment 1 Regina Henschel 2006-12-29 15:28:47 UTC
Created attachment 41752 [details] Excel file with example and pictures
Comment 2 tony.galmiche.ooo 2006-12-29 18:57:54 UTC
I confirm this probleme with Chart2m10
Comment 3 Regina Henschel 2007-01-01 14:43:56 UTC
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.
Comment 6 kla 2007-01-02 09:04:17 UTC
@IHA: Please have a look.
Comment 7 IngridvdM 2007-01-04 16:54:34 UTC
->dr: Please take over. I think issue 71687 is duplicate but this one is much better documented so you might want to keep this.
Comment 8 daniel.rentz 2007-01-10 13:29:39 UTC
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
Comment 9 niederbayern 2007-10-22 20:34:15 UTC
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.
Comment 10 niederbayern 2007-10-22 20:35:52 UTC
Created attachment 49078 [details] as mentioned above
Comment 11 Regina Henschel 2007-12-31 17:12:11 UTC
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.
Comment 12 Regina Henschel 2007-12-31 17:14:19 UTC
Created attachment 50608 [details] corrected description
Comment 13 Regina Henschel 2007-12-31 17:15:59 UTC
Created attachment 50609 [details] function to convert Excel's elevation/rotation to OOo's XYZ angles
Comment 14 Regina Henschel 2007-12-31 17:18:13 UTC
Created attachment 50610 [details] Textdocument for testing import of 3D column chart
Comment 15 Martin Hollmichel 2008-01-27 07:42:45 UTC
set target to 3.x
Comment 16 daniel.rentz 2008-04-04 11:05:59 UTC
CWS xmlfilter04 adds new helper properties at the diagram. Once this CWS is integrated, impoprt/export of 3D settings can be done easily.
Comment 17 daniel.rentz 2008-04-04 11:06:49 UTC
*** Issue 81885 has been marked as a duplicate of this issue. ***
Comment 18 IngridvdM 2008-04-04 11:22:02 UTC
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.
Comment 19 daniel.rentz 2008-04-04 14:24:25 UTC
*** Issue 84813 has been marked as a duplicate of this issue. ***
Comment 20 daniel.rentz 2008-04-15 15:11:00 UTC
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).
Comment 21 daniel.rentz 2008-04-16 08:00:31 UTC
whoops, not fixed yet :-)
Comment 22 IngridvdM 2008-04-17 21:00:40 UTC
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 :-)
Comment 23 IngridvdM 2008-04-24 12:14:59 UTC
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.
Comment 24 IngridvdM 2008-04-24 12:18:20 UTC
->Thomas, please verify in CWS chart26. I tested on windows the normal pure installation and also a patched 2.4 version.
Comment 25 Regina Henschel 2008-04-25 17:43:28 UTC
@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.
Comment 26 IngridvdM 2008-04-25 19:24:46 UTC
@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.
Comment 27 kla 2008-04-29 11:51:49 UTC
Seen ok in CWS chart26 -> verified
Comment 28 Mechtilde 2008-06-01 10:10:22 UTC
@ regina Do you agree that it is fixed? Can you verify it? Mechtilde
Comment 29 Regina Henschel 2008-06-01 14:52:36 UTC
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.
Comment 30 daniel.rentz 2008-06-01 16:30:45 UTC
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.
Comment 31 Regina Henschel 2008-06-01 18:25:33 UTC
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.
Comment 32 daniel.rentz 2008-06-02 08:40:29 UTC
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 ;-)
Comment 33 oc 2008-06-03 13:12:58 UTC
Closed because fix available in OOo2.4.1 (OOH_m17). For currently unresolved problems please open new issues.