Issue 72994 - Excel filter: 3D settings in Excel charts
Summary: Excel filter: 3D settings in Excel charts
Alias: None
Product: General
Classification: Code
Component: chart (show other issues)
Version: 3.3.0 or older (OOo)
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: kla
QA Contact: issues@graphics
Keywords: ms_interoperability, usability
: 81885 84813 (view as issue list)
Depends on:
Reported: 2006-12-29 15:25 UTC by Regina Henschel
Modified: 2013-02-24 21:19 UTC (History)
6 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Excel file with example and pictures (34.00 KB, application/
2006-12-29 15:28 UTC, Regina Henschel
no flags Details
calculation (12.76 KB, application/vnd.oasis.opendocument.spreadsheet)
2007-01-01 14:57 UTC, Regina Henschel
no flags Details
description (61.14 KB, application/vnd.oasis.opendocument.text)
2007-01-01 14:58 UTC, Regina Henschel
no flags Details
as mentioned above (106.03 KB, application/pdf)
2007-10-22 20:35 UTC, niederbayern
no flags Details
corrected description (230.72 KB, application/vnd.oasis.opendocument.text)
2007-12-31 17:14 UTC, Regina Henschel
no flags Details
function to convert Excel's elevation/rotation to OOo's XYZ angles (3.59 KB, text/plain)
2007-12-31 17:15 UTC, Regina Henschel
no flags Details
Textdocument for testing import of 3D column chart (477.33 KB, application/
2007-12-31 17:18 UTC, Regina Henschel
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
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 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
Comment 4 Regina Henschel 2007-01-01 14:57:44 UTC
Created attachment 41818 [details]
Comment 5 Regina Henschel 2007-01-01 14:58:14 UTC
Created attachment 41819 [details]
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 :-)

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:

I have improved the description and hope that I now got all special cases right.

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.

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
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
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:

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?

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 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

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.