Friday, April 6, 2007

Automating Excel with Ruby: Rows, Columns, Ranges, and Cells

An Excel worksheet object contains rows, columns, ranges, and cells. Let's look at some of the most common methods for working with these objects.

The Cells method of the Worksheet object returns a single cell. It is called with the row number and column number...


cell = worksheet.Cells(2, 5)

The Range method (of either the Application or Worksheet object) returns a collection of cells. It is called with a string value of First Cell and Last Cell, separate by a colon. The following code returns a collection of 20 cells in the rectangle from cell A1 to cell D5....

range = worksheet.Range("A1:D5")

The worksheet.UsedRange method returns a collection of cells, from the upper-left-most used cell to the lower-right-most used cell. This is useful, because the used range is usually much smaller then the entire range of cells in a worksheet.

A Worksheet or Range object also contains a (1-based index) collection of Rows and a (1-based index) collection of Columns, which you can iterate over...

for row in worksheet.Rows
# ...code...
end

for column in worksheet.Columns
# ...code...
end

for row in worksheet.Range("A1:D5").Rows
# ...code...
end

for row in worksheet.UsedRange.Rows
# ...code...
end

To get the number of rows or columns in a range, call the range's Count method...

(1..worksheet.UsedRange.Rows.Count).each do |row|
#...code...
end

You can reference a single Row or Column by index. For example, to apply a bold font to the first row of the worksheet...

worksheet.Rows(1).Font.Bold = true

To set the width of column 4 (D) to 25 pixels...

worksheet.Columns(4).ColumnWidth = 25.0

A cell object has numerous child objects and methods. The Value (read/write) method returns a Cell's value as it is stored internally. A cell's Text (read-only) method returns the value as it is currently displayed. For example, if cell B4 has the value "1234.56789" formatted as Currency, the Value and Text methods return different values...

worksheet.Cells(4, 2).Value # => "1234.56789"
worksheet.Cells(4, 2).Text # => "$1234.57"

Calling the Value method on a Range object returns an array of arrays of values. Here's a quick way to get a 2-dimensional array of all values in a worksheet...

data = worksheet.UsedRange.Value

You cannot call the Text method on a Range object.

As previously mentioned, one of the best hands-on ways of learning the various Excel objects and methods/properties is to record a macro in Excel, then review the macro's VBA code and translate it to Ruby.

As always, feel free to post comments or send email if you have questions, comments, or suggestions for future topics.

4 comments:

Dan Woodward said...

On thing about range().value and arrays. If the range you are pulling from turns out to be a single cell, you will get a single value not an Array. I learned this the hard way when working with ActiveSheet.UsedRange.value. I was expecting the sheets to always have array data, but someone threw a single value in A1 and it was all over.

David Mullet said...

Thanks for the tip, Woodybrood!

Anonymous said...

Tip for change the orientation of the page to horizontal, I'll assume that you have the WIN32OLE constants loaded on RAM:

ws.PageSetup.Orientation = ExcelConstants::XlLandscape

See ya' :)

Ragu said...

when i including
usedrange = wrksheet.Rows(1)
throwing Error as unintialized Constant
libraries i have included
watir-webdriver,rubygems,roo,win32ole
Kindly Any Suggestion