Page 1 of 1
Add multiple series in calc chart using macros
Posted: Fri Aug 22, 2008 10:06 am
by greaty
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.
Re: Add multiple series in calc chart using macros
Posted: Mon Sep 15, 2008 2:44 pm
by greaty
Please Help me....
Thank you in advance
Re: Add multiple series in calc chart using macros
Posted: Fri Sep 19, 2008 8:22 pm
by hanya
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:
- 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