Sunday, June 15, 2008

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:

8 comments:

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

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 borderssheet1.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?