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:
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
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
Hi David,
Thanks a lot for the good post, it really helps me a lot.
How about an article on how you can write COM objects in ruby?
Also a follow up on how you can handle events.
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.
Post a Comment