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. |
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. |
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 ).