Add multiple series in calc chart using macros

Creating a macro - Writing a Script - Using the API

Add multiple series in calc chart using macros

Postby greaty » Fri Aug 22, 2008 10:06 am

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

Re: Add multiple series in calc chart using macros

Postby greaty » Mon Sep 15, 2008 2:44 pm

Please Help me....
Thank you in advance
Posts: 2
Joined: Fri Aug 22, 2008 9:57 am

Re: Add multiple series in calc chart using macros

Postby hanya » Fri Sep 19, 2008 8:22 pm


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   Expand viewCollapse view
Sub Main2

   Dim Doc As Object, Sheet As Object, charts As Object
    Dim Rect As new
   Dim Source(1) As New   
   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
    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
    With chart
       .Diagram = chart.createInstance("") '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
  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)
End Sub

' create new series for XYDiagram
' a series has two or more LabeledDataSequence that is named by Label DataSequence.
' oDataProvider:
' 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("")
  Dim oData(1) As Object ' x and y:
  ' Y
  oDataY = CreateUnoService("")
  oSequenceY = CreateDataSequence(oDataProvider, _
      sYRangeRepresentation, "values-y")
  If NOT IsNull(oSequenceY) Then
    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("")
  oSequenceX = CreateDataSequence(oDataProvider, _
      sXRangeRepresentation, "values-x")
  If NOT IsNull(oSequenceX) Then
  End If
  ' set x and y data to series
  aData = Array(oDataY, oDataX)
  CreateDataSeries_XYDiagram = oNewDataSeries
End Function

' creat new DataSequence from range representaion
' that provides real data and its role in the series
' oDataProvider:
' sRangeRepresentation: range address e.g. Sheet1.A1:B2
' sRole: role is defined in
Function CreateDataSequence( _
    oDataProvider As Object, _
    sRangeRepresentation As String, sRole As String ) As Object
  Dim oDataSequence As Object
  On Error GoTo Handler
  ' create from range representation
  oDataSequence = oDataProvider._
  If NOT IsNull(oDataSequence) Then
    oDataSequence.Role = sRole
  End If
    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
Posts: 885
Joined: Fri Nov 23, 2007 9:27 am
Location: Japan

Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 8 guests