Add multiple series in calc chart using macros

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
greaty
Posts: 2
Joined: Fri Aug 22, 2008 9:57 am

Add multiple series in calc chart using macros

Post 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.
Attachments
multple X series graph .ods
(18.82 KiB) Downloaded 1047 times
OOo 2.4.X on Ms Windows XP
greaty
Posts: 2
Joined: Fri Aug 22, 2008 9:57 am

Re: Add multiple series in calc chart using macros

Post by greaty »

Please Help me....
Thank you in advance
hanya
Volunteer
Posts: 885
Joined: Fri Nov 23, 2007 9:27 am
Location: Japan

Re: Add multiple series in calc chart using macros

Post 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:
  1. make a chart with one series
  2. 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
Post Reply