Page 1 of 1
[Solved] Controlling Chart Legend Entries
Posted: Tue Jul 02, 2019 2:20 pm
by 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!
Re: Controlling Chart Legend Entries
Posted: Sat Jul 06, 2019 12:33 am
by 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..
Re: Controlling Chart Legend Entries
Posted: Thu Jul 11, 2019 2:26 pm
by 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
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
but this is read only, and I don't see a setUsedRangeRepresentations. See attached file.
Re: Controlling Chart Legend Entries
Posted: Fri Jul 12, 2019 11:49 pm
by 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
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.
Re: Controlling Chart Legend Entries
Posted: Fri Jul 26, 2019 10:44 am
by 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