[Solved] Can't get LineColor to show predictably

Keyboard macros or custom scripts

[Solved] Can't get LineColor to show predictably

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


Forex Charts to fix.jpg
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   Expand viewCollapse view
   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   Expand viewCollapse view
   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   Expand viewCollapse view
   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   Expand viewCollapse view
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 ).
Attachments
Forex_Last_91_Days (dev).txt
Basic macro, complete
(11.63 KiB) Downloaded 57 times
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
User avatar
misitu
 
Posts: 89
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 3 guests