[Solved] Controlling Chart Legend Entries

[John9]

I would like to have more control over chart legend entries. This is the script I use :

Code: Select all

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 = 6

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

' make legend entries 'first', 'second', 'third' from 'data' sheet

End Sub

This macro is in Macro_simple.ods->Standard->Module1 attached.

How can I change the legend entry target cells (e.g. 'first', 'second', 'third' from 'data' sheet) or prescribe the text for each line in the plot?

Many Thanks!

[UnklDonald418]

You are using the MRI tool aren't you?
Using the MRI tool on the Chart object I located the needed methods.
Try adding this to your macro code.

Code: Select all

  tstring = Chart.Data.getRowDescriptions()
  tstring(0) = "first"
  tstring(1) = "second"
  tstring(2) = "third"
  Chart.Data.setRowDescriptions(tstring)

I didn't bother retrieving the strings from the data sheet or using a loop to copy them into tstring
I'll leave those details to you..

[John9]

Thanks for the reply! However, this causes the x-axis number format to change to dates, and

Code: Select all

Chart.Diagram.XAxis.NumberFormat = 0 ' Has no effect

It is possible to re-set the axis values (almost manually) using

Code: Select all

Chart.Data.SetColumnDescriptions

But that's a bit ugly. The other problem with this approach is that the plot is no longer linked to the cell values and is instead a self contained 'Data Table'.

I've checked through the chart properties and the original cell locations can be found in

Code: Select all

Chart.UsedRangeRepresentations

but this is read only, and I don't see a setUsedRangeRepresentations. See attached file.

[UnklDonald418]

Andrew Pitonyak devotes 7 pages of his book to charts, but he does admit "only a small portion of the capabilities are shown here" and nothing on your problem is discussed.
Dr. Andew Davison devotes 5 chapters to charts, but alas I didn't find any help there either.
http://fivedots.coe.psu.ac.th/~ad/jlop/
So you it appears you are blazing your own trail.
I believe your original chart might be a bit too narrow so I made an adjustment

Code: Select all

Rect.Width = 12000

Try adding the following code to the end of your macro.

Code: Select all

 oTrowdesc = Chart.Data.getAnyColumnDescriptions()
  tstring = Chart.Data.getRowDescriptions()
  oSheet0 = oDoc.Sheets.getByName("data")
  for i = 0 to uBound(tstring)
    tstring(i) = oSheet0.getCellByPosition(0, 11 + i).string
  next i
  Chart.Data.setComplexColumnDescriptions(oTrowdesc)
  Chart.Data.setRowDescriptions(tstring)

I do see one issue for which have found no reason or solution, the short green plot in the upper left of the screen disappears when

Code: Select all

Chart.Data.setRowDescriptions(tstring)

is executed.

[John9]

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