I found how to do this using this post :
viewtopic.php?f=20&t=8991
It's a more general method that also allows separate x values to be used for each series.
Code: Select all
REM ***** BASIC *****
Sub Plot_legend
Dim oDoc As Object, oSheet As Object, Dlg As Object
oDoc = ThisComponent
' Clear sheets
oSheet = oDoc.Sheets(1) ' Refers to Sheet1 as in 0, 1, 2 etc
oSheet.clearContents(1 OR 2 OR 4 OR 8 OR 16 OR 32 OR 64 OR 128 OR 256 OR 512)
Dim Charts As Object, Chart as Object
Dim Rect As New com.sun.star.awt.Rectangle
Dim RangeAddress(1) As New com.sun.star.table.CellRangeAddress
Rect.X = 500
Rect.Y = 500
Rect.Width = 10000
Rect.Height = 10000
RangeAddress(0).Sheet = 0
RangeAddress(0).StartColumn = 1
RangeAddress(0).StartRow = 3
RangeAddress(0).EndColumn = 12
RangeAddress(0).EndRow = 4
Charts = oDoc.Sheets(1).Charts
chart_name = "MyChart"
Charts.addNewByName(chart_name, Rect, RangeAddress(), True, True)
Chart = Charts.getByName(chart_name).EmbeddedObject
Chart.Diagram = Chart.createInstance("com.sun.star.chart.LineDiagram")
Chart.Diagram.DataRowSource = com.sun.star.chart.ChartDataRowSource.ROWS
'Chart.DataSourceLabelsInFirstColumn = False ' change StartColumn = 2
Chart.HasLegend = True
'######## 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(3) As Object ' new data series
Dim cols
cols = Array(17798, 16728590, 16765728, 5741852, 8257569, 8637183,_
3227652, 11456256, 4923247, 16749838, 12910603, 34001)
DataSheet = oDoc.Sheets(0)
ns = 0
For ngc = 0 to 2
' x data range
StartColumn = 2
StartRow = 3
EndColumn = 12
EndRow = 3
xcellr = DataSheet.getCellRangeByPosition(StartColumn,StartRow,EndColumn,EndRow)
xrange_abs = xcellr.AbsoluteName
' y data range
StartColumn = 2
StartRow = 4 + ngc
EndColumn = 12
EndRow = 4 + ngc
ycellr = DataSheet.getCellRangeByPosition(StartColumn,StartRow,EndColumn,EndRow)
yrange_abs = ycellr.AbsoluteName
' label address
clabel = DataSheet.getCellByPosition(0, 11 + ngc)
label_abs = clabel.AbsoluteName
oSeries = CreateDataSeries_XYDiagram(oDataProvider, _
xrange_abs, yrange_abs, label_abs)
' set series properties see com.sun.star.chart2.DataSeries
oSeries.Color = cols(ngc mod 12)
oSeries.LineWidth = 80
Select case ngc
case Ng + Nextra ' all
oSeries.LineStyle = com.sun.star.drawing.LineStyle.DASH
oSeries.lineDashName = "Ultrafine Dashed"
case 1
oSeries.LineStyle = com.sun.star.drawing.LineStyle.DASH
oSeries.lineDashName = "Fine Dashed"
case 2
oSeries.LineStyle = com.sun.star.drawing.LineStyle.DASH
oSeries.lineDashName = "Fine Dotted"
case 3
oSeries.LineStyle = com.sun.star.drawing.LineStyle.DASH
oSeries.lineDashName = "3 Dashes 3 Dots (var)"
case 4
oSeries.LineStyle = com.sun.star.drawing.LineStyle.DASH
oSeries.lineDashName = "2 Dots 1 Dash"
case 5
oSeries.LineStyle = com.sun.star.drawing.LineStyle.DASH
oSeries.lineDashName = "Fine Dashed (var)"
end select
oNewDataSeriesList(ns) = oSeries
ns = ns + 1
Next
' 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