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.