Sunday, June 15, 2008

Automating Excel: Chart, Axis, and Legend Titles

A reader writes, "I need to use Ruby to automate Excel... How can I set the chart title, axes title, or legend name, etc in the chart by using Ruby?" I confess that my knowledge of Excel charts is limited, but let's dive in and see what we can learn...

As usual, you'll start by using the win32ole library. For our example, we'll connect to a running instance of Excel:


require 'win32ole'
xl = WIN32OLE.connect('Excel.Application')

Referencing a Chart Object

Let's assume that ws is your worksheet object. You can obtain a reference to a Chart object either...

...via the Charts collection, if it is a chart sheet...

chart = ws.Charts(1).Chart

or via the ChartObjects collection, if it is an embedded chart (not a chart sheet)...

chart = ws.ChartObjects(1).Chart

...or via the ActiveChart method if the chart is the currently active object:

chart = xl.ActiveChart

Setting the Chart Title

In order to set the value of a chart's title, you must ensure that the HasTitle property is enabled:

chart.HasTitle = true

Then set the value of the ChartTitle.Text property:

chart.ChartTitle.Text = "2008 Sales"

Setting the Axes Title

Select one of the chart's axes by calling the Chart object's Axes method and passing it an integer representing either the category, value, or series axis:

XL_CATEGORY = 1
XL_VALUE = 2
XL_SERIESAXIS = 3
axis = chart.Axes(XL_CATEGORY)

Much like with the chart title above, in order to set the value of an axis' title, you must ensure that the HasTitle property is enabled:

axis.HasTitle = true

Then set the value of the ChartTitle.Text property:

axis.AxisTitle.Text = "July Sales"

Setting the Legend Names

You can reference a legend via the Chart object's SeriesCollection method, passing it the (1-based) index of the item you wish to reference:

chart.SeriesCollection(1).Name = "Jan"
chart.SeriesCollection(2).Name = "Feb"
chart.SeriesCollection(3).Name = "Mar"

And there you have it. If you found this to be useful, you may want to check out this Microsoft MSDN Chart object reference.

Got a question, comment, or suggestion? Post your comment here or send me an email.

Thanks for stopping by!

8 comments:

ajasja said...

Hello!

There is probably some mistake here:
"You can obtain a reference to a Chart object either..."
And then it says you can get the chart object in two ways (Charts collection and ChartObject Collection), but the line of code is the same in both examples.
"chart = ws.ChartObjects(1).Chart"

I’d also very much like to know how I could create a chart from scratch via ruby (aka OLE automation).

Thank you for the good posts.

Best regards,
Ajasja

David Mullet said...

Thanks for catching my typo, Ajasja!

I have now corrected it in the article.

I'll post a follow-up article this week discussing how to create new Excel charts with Ruby.

David

Anonymous said...

Hi David,

Thanks a lot for the good post, it really helps me a lot.

Anonymous said...

How about an article on how you can write COM objects in ruby?

Also a follow up on how you can handle events.

Anonymous said...

Hi David,

It's me again. Recently I am using Ruby to create a table in excel. i have been search from internet on how to draw border of a range of cell and this is the example of code that i get, however, I don't really understand how it works.

sheet1.Range("q1:s8").Borders(4).Linestyle = ExcelConst::XlContinuous #this is to set horizontal borders
sheet1.Range("p2:s8").Borders(2).Linestyle = ExcelConst::XlContinuous #this is to set vertical borders

Do you have any idea on it? Or may I know is there any other way to draw all borders of the cell?

Thanks a lot.

Website Hosting India said...
This comment has been removed by a blog administrator.
website registration india said...
This comment has been removed by a blog administrator.
Unknown said...
This comment has been removed by a blog administrator.