Change the color of a frame

[GSchweeger]

I am experimenting to use the R graphics facilities in calc - and I am a newbe to open office macros, still having lots of problems not to confound properties, services etc. I managed to get the data from calc to R and the graph back into calc via the clipboard. Now I have this chart, but it is transparent, so you can see all the grid of a typical calc sheet. I wanted to change the background colour to white in an automated way.
While using the macro recorder I end up with:

Code: Select all   Expand viewCollapse view

rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:FormatArea", "", 0, Array())

which, when running the macro, obviously opens the color table to chose from and the rest has to be done manually (I did not expect anything else with the macro recorders property not to record, what's happening in another window).
I was unable to find parameters to hand over to uno:FormatArea which would result in an automatic change. I tried e.g.

Code: Select all   Expand viewCollapse view

dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "BackgroundColor"
args4(0).Value = RGB(250,0,0)
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:FormatArea", "", 0, args4())

but it also only opens the color panel to continue manually with.

The drawing is an object "com.sun.star.comp.framework.Frame". Using uno:BackgroundColor did not work (nothing happens). I have tried to find properties to change, but was not able (maybe lack of experience with openoffice programming).

Is there anybody out there who knows, whether I can force FormatArea to do the job automatically, or whether I could try anything else?

[ms777]

Hi,

forget the macro recorder and the dispatcher, if you want to do something serious ...

Code: Select all   Expand viewCollapse view

Sub Main
oSheet = ThisComponent.sheets.getByIndex(0)
oCellRange = oSheet.getCellRangeByPosition(3,5,4,8) 'D6:E9
oCellRange.CellBackColor = RGB(255,0,0)
End Sub

Good luck,

ms777

[JohnV]

And here's how you change the color of a frame inserted in Writer. If you know how to get your actual frame object then it should be somewhat the same.

If you don't have the Xray utility you should get it.
http://ooomacros.org/dev.php#101416

Code: Select all   Expand viewCollapse view

Sub Main
oDoc = ThisComponent
Frames = oDoc.getTextFrames
Frame = Frames.getByName("Frame1")
'xray frame
Frame.BackColorRGB = RGB(255,0,0)
End Sub

[GSchweeger]

Thanks ms777, it's not a range of cells which I want to give a different color, but a drawing on a calc sheet, so your method did not work.
Thanks JohnV, getTextFrames obviously does not work in calc sheets
I got Xray and managed to get it working (in a previous attempt it did not install itself correctly).
With ThisComponent.CurrentController I get an object "com.sun.star.comp.framework.Frame" (as mentioned before), but from xraying the properties, I think this is wrong. Using ThisComponent.CurrentSelection I get a "com.sun.star.drawing.SvxShapeCollection" with very few properties and very few methods, but supported by the service "con.sun.star.drawing.ShapeCollection" - supported but not yet available.

Does anyone know how I can make this service available for an already existing SvxShapeCollection? I tried already to create a new ShapeCollection and add the the SvxShapeCollection, but the macro thinks I am too reflective with this.

And then: Can I use FillStyle and FillColor on a ShapeCollection?

[JohnV]

it's not a range of cells which I want to give a different color, but a drawing on a calc sheet

I inserted a rectangle into a Calc sheet using the Drawing toolbar. This following code changes its background color.

Code: Select all   Expand viewCollapse view

Sub Main
oSheet =ThisComponent.Sheets(0)
dp = oSheet.getDrawpage
drawing = dp.getByIndex(0)
drawing.FillColor = RGB(255,0,0)
End Sub

[GSchweeger]

Thanks JV,

I will play with that. At the moment "drawing = dp.getbyIndex(0)" gives back com.sun.star.lang.IndexOutOfBoundsException, but dp allows the method getbyIndex. The problem is that dp.hasElements is false. Can you check on your side? What do you need to select before starting the macro?

The funny side of the story is, that in the mean time I found out, how to solve the issue in R and get a nice background from there, so the problem does not hurt my application any more, but it is still somewhat intriguing.

[JohnV]

"drawing = dp.getbyIndex(0)" gives back com.sun.star.lang.IndexOutOfBoundsException,

I got that error too before I put a drawing in Sheet1.

Do View > Toolbars and check Drawing then insert a rectangle.