[Solved] Can't get LineColor to show predictably

misitu · Post by **misitu** » Wed Oct 19, 2016 9:32 pm

Hello!!

I'm trying to produce a chart via macro that reflects an existing spreadsheet.

Edit: No, I have not solved the problem, but I have simplified the description and have more detail on what is happening.

This original post had a lot of detail. I think it unfair to expect other contributors to wade through all that when there is a simpler explanation of the problem.

I'm creating a new post. I'll put a link in here when done.

Apologies for the disturbance and, if anyone's been trying to figure this out, thanks.
David

Edit: Conclusion
This looks like a bug in the Basic Interpreter, or something undercover related to threads or the like.
I have documented the issue in this new post.
Seemingly, it is not possible to assign Line properties such as Color and Style after the first two lines in a chart.
I intend to report this as a bug, depending on whether I find a solution in AOO 4.1.2 or 4.1.3 release info.

The green curves relate to the RH axis (secondary Y). The red ones to the LH (primary Y).

When I attack this with a macro the colours come out wrong. Also, the line width applies itself to the wrong curve (I enlarged the line width to try and identify which curve is which)

Playing about with .LineColor values does not yield results as expected. Sometimes the previous colours remain in place, at other times the curve disappears.

Edit: Have tried hex values and also decimal equivalents of hex: same results as with RGB(r,g,b): sometimes changes, sometimes displays previous colour.

: Charts (L) as wished (R) as produced

on the left - what I want
on the right - what I get.

I'd really like to know what's going wrong as, once I can predict the line colouring accurately, it will be possible to move on to the line type (dashes etc).

Thanks for your help, appreciated as ever.

David

Here are what I think are the relevant pieces of code

Code: Select all

	emptyCellRanges = colA.queryEmptyCells() 'Find all the Empty Cell Ranges
	firstEmptyRange = emptyCellRanges.getByIndex(0)  'Get the first empty range
	emptyRangeAddress = firstEmptyRange.RangeAddress  'Get the RangeAddress of the first empty range
	dim leftCell as object
	dim rightCell as Object
	dim rowNumber, lastRow as integer
	rowNumber =  emptyRangeAddress.StartRow - 1
	lastRow = rowNumber
	dim dataRange as string
	dim oChartDoc
	Dim Charts As Object
	Dim Chart as Object
	Dim Rect As New com.sun.star.awt.Rectangle
	Dim RangeAddress(0) As New com.sun.star.table.CellRangeAddress
	dataRange = "a2:h" & lastRow+1
'	msgbox ("Data Range " + "=" + dataRange)
'	lastRowToFormat = lastRow + 1
	oChartDoc = ThisComponent
	Dim oRect 'How big is the chart
	Dim oAddress 'Address of data to plot
	Dim oTitle 'Chart title object
	oAddress = oSheet.getCellRangeByName(dataRange).getRangeAddress()		
	Charts = oChartDoc.Sheets(0).Charts
	Rect.X = 1000 
	Rect.Y = 500
	Rect.Width = 13660
	Rect.Height = 22800
	with RangeAddress(0)
		.Sheet = 1
		.StartColumn = 0
		.StartRow = 0
		.EndColumn = 7 ' 6 ' 7
		.EndRow = lastRow 
	end with

Code: Select all

	Charts = oChartDoc.Sheets(0).Charts 'liveinto 
	Charts.addNewByName("Forex Chart", Rect, RangeAddress(), True, True)
	Chart = Charts.getByName("Forex Chart")
	ChartDoc = Chart.getEmbeddedObject()
	with ChartDoc
		.hasMainTitle = True
		.Title.String = "FOREX Last 91 Days to " & lastDateLogged
		.HasSubTitle = True
		.Subtitle.String = "(Note: EURUSD is shown 2.5x)"
		.HasLegend = True
		.Legend.Alignment = com.sun.star.chart.ChartLegendPosition.BOTTOM
'		.Legend.FillColor = RGB(204, 255, 153)
		.Legend.CharHeight = 9
	end with
	ChartDoc.Diagram = ChartDoc.createInstance("com.sun.star.chart.LineDiagram")
	oDiagram = ChartDoc.getDiagram()
	aSize = oDiagram.getSize()
	aSize.Width = 10620
	aSize.Height = 15100 '  was 8660
	oDiagram.Size = aSize
	with oDiagram
'

' .Wall.FillStyle = com.sun.star.drawing.FillStyle.SOLID
' .Wall.FillColor = RGB(204, 204, 153)
		.HasXAxisTitle = true
		.XAxisTitle.String = "Transaction Month"
''		.XAxis.LineColor = RGB(255, 0, 255)
		.HasYAxisTitle = true
		.YAxisTitle.String = "Rates of Exchange from GBP"
		.YAxis.Min = 0.9	
		.YAxis.Max = 1.5 ' 2.4
		.YAxis.LineColor = RGB(255, 0, 0)
		.HasSecondaryYAxis = true
		.HasSecondaryYAxisDescription=true
		.HasSecondaryYAxisTitle=true
		.SecondYAxisTitle.String = "Rates of Exchange into PEN"
		.SecondaryYaxis.Min = 3.1 ' 2.0
		.SecondaryYAxis.Max = 4.6 ' 5.0
		.SecondaryYAxis.LineColor = RGB(0, 153, 0)

	end with

Code: Select all

	firstDiagram = ChartDoc.getfirstDiagram()
	oCoords = firstDiagram.getCoordinateSystems()
	oCoord = oCoords(0)
	oChartTypes = oCoord.getChartTypes()
	oChartType = oChartTypes(0)				'	Provisionally ...
	oDataSeries = oChartType.getDataSeries()
		oSeries = oDataSeries(0)			'	GBPEUR DataRowProp0 red
			oSeries.AttachedAxisIndex = 0
		oSeries = oDataSeries(1)			'	EURPEN light green
			oSeries.AttachedAxisIndex = 1 ' 1
		oSeries = oDataSeries(2)			'	GBPUSD yellow
			oSeries.AttachedAxisIndex = 0
		oSeries = oDataSeries(3)			'	USDPEN green
			oSeries.AttachedAxisIndex = 1 ' 1
		oSeries = oDataSeries(4)			'	GBPPEN brown (under celeste)
			oSeries.AttachedAxisIndex = 1 ' 1
		oSeries = oDataSeries(5)			'	GBPPEN celeste (not visible)
			oSeries.AttachedAxisIndex = 0 ' 1

Code: Select all

with oDiagram
	.DataCaption = com.sun.star.chart.ChartDataCaption.NONE
	.DataRowSource = com.sun.star.chart.ChartDataRowSource.COLUMNS	
	DataRowProp0 = .getDataRowProperties(0)
	with DataRowProp0
		.LineStyle = com.sun.star.drawing.LineStyle.SOLID
'			.LineDashName = "Ultrafine Dotted"' "Fine Dashed (var)"
		.LineColor = RGB(255,0,0)							' GBPEUR red
		.LineWidth = 50
	end with	
	DataRowProp1 = .getDataRowProperties(1)
	with DataRowProp1
'		.LineStyle = com.sun.star.drawing.LineStyle.SOLID
'			.LineDashName = "Ultrafine Dotted"' "Fine Dashed (var)"
		.LineColor = RGB(0,153,153) 							' EURPEN cyan 4
		.LineWidth = 50
	end with
	DataRowProp2 = .getDataRowProperties(2)
	with DataRowProp2			
'		.LineStyle = com.sun.star.drawing.LineStyle.SOLID
'			.LineDashName = "Ultrafine Dotted"
		.LineColor = RGB(0,153,153) ' RGB(255,255,0)							' GBPUSD
		.LineWidth = 500
	end with
	DataRowProp3 = .getDataRowProperties(3)
	with DataRowProp3
'		.LineStyle = com.sun.star.drawing.LineStyle.SOLID
'			.LineDashName = "Ultrafine Dotted"
		.LineColor = RGB(0,0,255) 						' USDPEN
	end with
	DataRowProp4 = .getDataRowProperties(4)
	with DataRowProp4												
'		.LineStyle = com.sun.star.drawing.LineStyle.SOLID
'			.LineDashName = "Ultrafine Dotted"
	.LineColor = RGB(153,153,255)		:					' GBP(EUR)PEN
		.LineWidth = 50
	end with
	DataRowProp5 = .getDataRowProperties(5)
	with DataRowProp5
		.LineStyle = com.sun.star.drawing.LineStyle.SOLID
'			.LineDashName = "Ultrafine Dotted"
		.LineColor = RGB(153,153,255) 						' GBP(USD)PEN
'		.LineWidth = 50
	end with
''	DataRowProp6 = .getDataRowProperties(6)
''	with DataRowProp6		
'		.LineStyle = com.sun.star.drawing.LineStyle.SOLID
'			.LineDashName = "Ultrafine Dotted"
'		.LineColor = RGB(63,63,63) '
'		.LineWidth = 50
''	end with
'''		DataRowProp7 = oDiagram.getDataRowProperties(7)					' EURUSD
'		with DataRowProp7			
'			.LineStyle = com.sun.star.drawing.LineStyle.DASH
''			.LineDashName = "Ultrafine Dotted"
''			.LineColor = RGB(63,63,63) '
'			.LineWidth = 200
'		end with
	end with

(Note: for the whole macro, please see attached text file ).