[Solved] Controlling Chart Legend Entries

Creating a macro - Writing a Script - Using the API

[Solved] Controlling Chart Legend Entries

Postby John9 » Tue Jul 02, 2019 2:20 pm

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

Code: Select all   Expand viewCollapse view
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!
Attachments
Macro_simple.ods
(11.5 KiB) Downloaded 28 times
Last edited by Hagar Delest on Sat Jul 27, 2019 8:09 pm, edited 2 times in total.
Reason: tagged solved
LibreOffice 6.0.7.3 on Ubuntu
John9
 
Posts: 3
Joined: Tue Jul 02, 2019 2:14 pm

Re: Controlling Chart Legend Entries

Postby UnklDonald418 » Sat Jul 06, 2019 12:33 am

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   Expand viewCollapse view
  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..
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1224
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Controlling Chart Legend Entries

Postby John9 » Thu Jul 11, 2019 2:26 pm

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

Code: Select all   Expand viewCollapse view
Chart.Diagram.XAxis.NumberFormat = 0 ' Has no effect


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

Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
Chart.UsedRangeRepresentations


but this is read only, and I don't see a setUsedRangeRepresentations. See attached file.
Attachments
Macro_simple2.ods
(18.29 KiB) Downloaded 20 times
LibreOffice 6.0.7.3 on Ubuntu
John9
 
Posts: 3
Joined: Tue Jul 02, 2019 2:14 pm

Re: Controlling Chart Legend Entries

Postby UnklDonald418 » Fri Jul 12, 2019 11:49 pm

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   Expand viewCollapse view
Rect.Width = 12000

Try adding the following code to the end of your macro.
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
Chart.Data.setRowDescriptions(tstring)

is executed.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1224
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Controlling Chart Legend Entries

Postby John9 » Fri Jul 26, 2019 10:44 am

I found how to do this using this post :

https://forum.openoffice.org/en/forum/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   Expand viewCollapse view
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
Attachments
Macro_simple3.ods
(11.35 KiB) Downloaded 14 times
LibreOffice 6.0.7.3 on Ubuntu
John9
 
Posts: 3
Joined: Tue Jul 02, 2019 2:14 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 4 guests