[Solved] Change the bar diagram's color? (Calc Automation)

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
pu8y
Posts: 19
Joined: Wed Nov 17, 2010 6:56 am

[Solved] Change the bar diagram's color? (Calc Automation)

Post by pu8y »

Hi all,

From my VB6 program, I create a stackable bar diagram.

Code: Select all

Dim Charts As Object
Dim chart As Object
Dim rect As Object
Dim rangeAddress(0) As Object
    
Set rect = oSM.Bridge_GetStruct("com.sun.star.awt.Rectangle")
Set rangeAddress(0) = oSM.Bridge_GetStruct("com.sun.star.table.CellRangeAddress")

Set Charts = oSheet.getCharts
rect.x = 2200
rect.Y = 4000
rect.Width = 35000
rect.Height = 15000
rangeAddress(0).Sheet = 0
rangeAddress(0).StartColumn = 1
rangeAddress(0).StartRow = 3
rangeAddress(0).EndColumn = x + 1
rangeAddress(0).EndRow = 5

Call Charts.addNewByName("LineChart", rect, rangeAddress(), True, True)
Set chart = Charts.getByName("LineChart").EmbeddedObject

'set as stackable bar
chart.Diagram = chart.CreateInstance("com.sun.star.chart.StackableDiagram")
chart.Diagram.Stacked = True
chart.Diagram.Percent = False

'set data series in rows
chart.Diagram.DataRowSource = Rows
This chart composed of 2 bars (see image below). Is it possible to change the colors of the data series from my program? By default it will give me orange and blue color. I found that there are something to be done with "DataSeries", but I have no idea on how to do, anyone can help me? or give me a hint?
stackable.jpg
Last edited by pu8y on Mon Nov 22, 2010 7:13 am, edited 1 time in total.
OpenOffice 3.1 on Windows XP
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: To change the bar diagram's color? (Calc Automation)

Post by Charlie Young »

It's probably neater to get the objects in steps, but with chart as the Embedded Object

Code: Select all

chart.FirstDiagram.CoordinateSystems(0).ChartTypes(0).DataSeries(0).Color = &HFF0000
Sets the first series to red, and just vary the index on DataSeries for others.
Apache OpenOffice 4.1.1
Windows XP
pu8y
Posts: 19
Joined: Wed Nov 17, 2010 6:56 am

Re: To change the bar diagram's color? (Calc Automation)

Post by pu8y »

An error saying that "Object required". Am I missing something?
OpenOffice 3.1 on Windows XP
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: To change the bar diagram's color? (Calc Automation)

Post by Charlie Young »

There may be something peculiar to VB, but I am largely able to reproduce your chart with OOo Basic, doing the necessary modifications to your code, and then change the series colors, to green and blue.

Code: Select all

Sub StackedChart
	Dim Charts As Object
	Dim chart As Object
	Dim oSheet As Object
	Dim rect As new com.sun.star.awt.Rectangle
	Dim rangeAddress(0) As new com.sun.star.table.CellRangeAddress
	   
	oSheet = ThisComponent.Sheets(0)
	Set Charts = oSheet.getCharts
	rect.x = 2200
	rect.Y = 4000
	rect.Width = 35000
	rect.Height = 15000
	rangeAddress(0).Sheet = 0
	rangeAddress(0).StartColumn = 1
	rangeAddress(0).StartRow = 3
	rangeAddress(0).EndColumn = 13
	rangeAddress(0).EndRow = 5
	
	oSheet.Charts.addNewByName("LineChart", rect, rangeAddress(), True, True)
	chart = Charts.getByName("LineChart").EmbeddedObject
	
	'set as stackable bar
	chart.Diagram = chart.CreateInstance("com.sun.star.chart.StackableDiagram")
	chart.Diagram.Stacked = True
	chart.Diagram.Percent = False
	
	'set data series in rows
	chart.Diagram.DataRowSource = com.sun.star.chart.ChartDataRowSource.ROWS
	chart.FirstDiagram.CoordinateSystems(0).ChartTypes(0).DataSeries(0).Color = &H00FF00
	chart.FirstDiagram.CoordinateSystems(0).ChartTypes(0).DataSeries(1).Color = &H0000FF
End Sub
See attached file (I entered the data from your picture). Just delete the existing chart before running the above code, changing the colors as you like.
Attachments
StackedBarColors.ods
Change series colors
(17.04 KiB) Downloaded 402 times
Apache OpenOffice 4.1.1
Windows XP
pu8y
Posts: 19
Joined: Wed Nov 17, 2010 6:56 am

Re: To change the bar diagram's color? (Calc Automation)

Post by pu8y »

I need to make my vb6 program to generate the .ods file with the chart, so I need to modify the code and put inside VB6 coding.
Everything goes fine, but this two rows:

Code: Select all

chart.FirstDiagram.CoordinateSystems(0).ChartTypes(0).DataSeries(0).Color = &H00FF00
chart.FirstDiagram.CoordinateSystems(0).ChartTypes(0).DataSeries(1).Color = &H0000FF
gave me error message : "Object required", but I cannot figure out what does it means and what is missing out there. :shock:
OpenOffice 3.1 on Windows XP
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: To change the bar diagram's color? (Calc Automation)

Post by Charlie Young »

pu8y wrote:I need to make my vb6 program to generate the .ods file with the chart, so I need to modify the code and put inside VB6 coding.
Everything goes fine, but this two rows:

Code: Select all

chart.FirstDiagram.CoordinateSystems(0).ChartTypes(0).DataSeries(0).Color = &H00FF00
chart.FirstDiagram.CoordinateSystems(0).ChartTypes(0).DataSeries(1).Color = &H0000FF
gave me error message : "Object required", but I cannot figure out what does it means and what is missing out there. :shock:
Though it gives an error in OOo in this case, VB is often fussy about wanting Set in front of object assignments. This is a shot in the dark on my part, but try

Code: Select all

Set chart.FirstDiagram.CoordinateSystems(0).ChartTypes(0).DataSeries(0).Color = &H00FF00
Set chart.FirstDiagram.CoordinateSystems(0).ChartTypes(0).DataSeries(1).Color = &H0000FF
Apache OpenOffice 4.1.1
Windows XP
pu8y
Posts: 19
Joined: Wed Nov 17, 2010 6:56 am

Re: To change the bar diagram's color? (Calc Automation)

Post by pu8y »

Yes I also tried with using "set" before, but the error is still the same.
OpenOffice 3.1 on Windows XP
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: To change the bar diagram's color? (Calc Automation)

Post by Charlie Young »

If you do try to get the objects in steps, such as

Step1 = chart.FirstDiagram
Step2 = Step1.CoordinateSystems(0).ChartTypes(0)
Step3 = Step2.DataSeries(0).Color

At what point does the error occur?
Apache OpenOffice 4.1.1
Windows XP
pu8y
Posts: 19
Joined: Wed Nov 17, 2010 6:56 am

Re: To change the bar diagram's color? (Calc Automation)

Post by pu8y »

I tried:

Code: Select all

Set oFirstDiagram = chart.FirstDiagram
Set oCharttypes = oFirstDiagram.CoordinateSystems(0).ChartTypes(0)
The error occurs at "Set oCharttypes = oFirstDiagram.CoordinateSystems(0).ChartTypes(0)"
OpenOffice 3.1 on Windows XP
pu8y
Posts: 19
Joined: Wed Nov 17, 2010 6:56 am

Re: To change the bar diagram's color? (Calc Automation)

Post by pu8y »

Hi Charlie,

Finally I am able to set the color of bars in my chart!
I used "getFirstDiagram" instead of "FirstDiagram" and "getCoordinateSystems" instead of "CoordinateSystems".

Code: Select all

Set oDiagram = chart.getFirstDiagram()
oCoords = oDiagram.getCoordinateSystems()
Set oCoord = oCoords(0)

oChartTypes = oCoord.getChartTypes()
Set oChartType = oChartTypes(0)
 
oseries = oChartType.getDataSeries()
oseries(0).Color = RGB(0, 128, 0)
oseries(1).Color = RGB(0, 0, 255)
Thanks for your help! :D
OpenOffice 3.1 on Windows XP
Post Reply