Hello everybody
I' m asking a question on XY chart realised by macros in calc.
I have multiples series with different X data. I would like to create automaticly a chart with for example 3 series.
I have add in this email an example of calc file with a macro nammed GraphTrial.
An existing graph is showing the desired chart I would like to obtain at least.
When I execute the macro you can see a significative difference between the 2 charts.
The result of the macro is 5 series with the same first X data serie, whereas I would like to have 3 series with differents X data.
Can you help me?
Thanks you in advance and sorry for my english wich could not be as good as yours due to my French origin.
Add multiple series in calc chart using macros
Add multiple series in calc chart using macros
- Attachments
-
- multple X series graph .ods
- (18.82 KiB) Downloaded 1047 times
OOo 2.4.X on Ms Windows XP
Re: Add multiple series in calc chart using macros
Please Help me....
Thank you in advance
Thank you in advance
Re: Add multiple series in calc chart using macros
Hi,
adding series is little bit complicated. But I think re-making all series is easier than modifying them.
Here is an example to make series, the part of code is taken from attached file.
I suppose to make multi series XY diagram like following:
adding series is little bit complicated. But I think re-making all series is easier than modifying them.
Here is an example to make series, the part of code is taken from attached file.
I suppose to make multi series XY diagram like following:
- make a chart with one series
- add additional series or replace all series
Code: Select all
Sub Main2
Dim Doc As Object, Sheet As Object, charts As Object
Dim Rect As new com.sun.star.awt.Rectangle
Dim Source(1) As New com.sun.star.table.CellRangeAddress
Doc = ThisComponent
Sheet = Doc.Sheets
Rect.X = 0 ' position x sur la feuille
Rect.Y = 5000 ' position y sur la feuille
Rect.Width = 15000
Rect.Height = 7500
charts=Doc.Sheets.getByName("Feuille1").Charts
for i = 0 to 0 step 2
'selection of X data
Source(i).Sheet = 0
Source(i).StartColumn = 0 + 2*i
Source(i).StartRow = 5
Source(i).EndColumn = 0 + 2*i
Source(i).EndRow = 9
'selection of y data
Source(i + 1).Sheet = 0
Source(i + 1).StartColumn = 1 + 2 * i
Source(i + 1).StartRow = 5
Source(i + 1).EndColumn = 1 + 2 * i
Source(i + 1).EndRow = 9
next i
charts.addNewByName("DataGraph", Rect ,Source(), false, false)
'place le graphique dans la feuille
chart=charts.getByName("DataGraph").EmbeddedObject
With chart
.Diagram = chart.createInstance("com.sun.star.chart.XYDiagram") 'xy Diagram
.Diagram.SplineType = 0
.Diagram.HasXAxisTitle = True
.Diagram.XAxisTiTle.string = "X"
.Diagram.HasYAxisTitle = True
.Diagram.YAxisTiTle.string = "Y"
.Diagram.XAxisTiTle.CharHeight = 16
.Diagram.YAxisTiTle.CharHeight = 16
.Diagram.YAxis.CharHeight = 16
.Diagram.XAxis.CharHeight = 16
.HasMainTitle = True
.Title.string = "Y versus X"
.Title.CharHeight = 16
.Diagram.SymbolType =-3
End With
' adding new series
oDataProvider = chart.getDataProvider()
oDiagram = chart.getFirstDiagram() ' chart2
oCooSys = oDiagram.getCoordinateSystems()
oCoods = oCooSys(0) ' this chart has only a coordinate system
oChartTypes = oCoods.getChartTypes() ' chart type one by one
oChartType = oChartTypes(0)
' all data series belongs the chart type
oDataSeriesList = oChartType.getDataSeries()
Dim oNewDataSeriesList(2) As Object ' new data series
' create series
oSeries1 = CreateDataSeries_XYDiagram(oDataProvider, _
"Feuille1.A6:A9", "Feuille1.B6:B9", "Feuille1.A3")
oSeries2 = CreateDataSeries_XYDiagram(oDataProvider, _
"Feuille1.E6:E10", "Feuille1.F6:F10", "Feuille1.E3")
oSeries3 = CreateDataSeries_XYDiagram(oDataProvider, _
"Feuille1.I6:I10", "Feuille1.J6:J10", "Feuille1.I3")
' set series properties see com.sun.star.chart2.DataSeries
oSeries1.Color = 17798
oSeries2.Color = 16728590
oSeries3.Color = 16765728
oNewDataSeriesList(0) = oSeries1 ' first one
oNewDataSeriesList(1) = oSeries2
oNewDataSeriesList(2) = oSeries3
' update chart (only the charttype is updated)
oChartType.setDataSeries(oNewDataSeriesList)
End Sub
' create new series for XYDiagram
' a series has two or more LabeledDataSequence that is named by Label DataSequence.
' oDataProvider: com.sun.star.chart2.data.XDataProvider
' sXRangeRepresentation: X
' sYRangeRepresentation: Y
' sLabelRangeRepresentation: range representation for name of the series
Function CreateDataSeries_XYDiagram( _
oDataProvider As Object, _
sXRangeRepresentation As String, _
sYRangeRepresentation As String, _
Optional sLabelRangeRepresentation As String ) As Object
Dim oNewDataSeries As Object
' create new DataSeries
oNewDataSeries = CreateUnoService("com.sun.star.chart2.DataSeries")
Dim oData(1) As Object ' x and y: .chart2.data.XLabeledDataSequence
' Y
oDataY = CreateUnoService("com.sun.star.chart2.data.LabeledDataSequence")
oSequenceY = CreateDataSequence(oDataProvider, _
sYRangeRepresentation, "values-y")
If NOT IsNull(oSequenceY) Then
oDataY.setValues(oSequenceY)
If NOT ((IsMissing(sLabelRangeRepresentation)) AND _
(sLabelRangeRepresentation <> "")) Then
oSequenceLabel = CreateDataSequence(oDataProvider, _
sLabelRangeRepresentation, "")
oDataY.setLabel(oSequenceLabel) ' label is used as name
End If
End If
' X
oDataX = CreateUnoService("com.sun.star.chart2.data.LabeledDataSequence")
oSequenceX = CreateDataSequence(oDataProvider, _
sXRangeRepresentation, "values-x")
If NOT IsNull(oSequenceX) Then
oDataX.setValues(oSequenceX)
End If
' set x and y data to series
aData = Array(oDataY, oDataX)
oNewDataSeries.setData(aData)
CreateDataSeries_XYDiagram = oNewDataSeries
End Function
' creat new DataSequence from range representaion
' that provides real data and its role in the series
' oDataProvider: com.sun.star.chart2.data.XDataProvider
' sRangeRepresentation: range address e.g. Sheet1.A1:B2
' sRole: role is defined in com.sun.star.chart2.data.DataSequenceRole
Function CreateDataSequence( _
oDataProvider As Object, _
sRangeRepresentation As String, sRole As String ) As Object
Dim oDataSequence As Object
On Error GoTo Handler
' create .chart2.data.DataSequence from range representation
oDataSequence = oDataProvider._
createDataSequenceByRangeRepresentation(sRangeRepresentation)
If NOT IsNull(oDataSequence) Then
oDataSequence.Role = sRole
End If
Handler:
CreateDataSequence = oDataSequence
End Function
Please, edit this thread's initial post and add "[Solved]" to the subject line if your problem has been solved.
Apache OpenOffice 4-dev on Xubuntu 14.04
Apache OpenOffice 4-dev on Xubuntu 14.04