Automating Excel with Ruby: Defining a Range and Inserting Data

As I previously mentioned, calling the Value method on an Excel Range object returns an array of arrays of values. Here's a quick way to get a 2-dimensional array of all values from a worksheet...

data = worksheet.UsedRange.Value

But, as I failed to mention in that article, you can also insert a 2-dimensional array into a Range of cells:

worksheet.Range("A2:G7").Value = data

If you have a lot of data to insert, this will be much faster than inputting the data cell-by-cell. But it is important that the range of cells be the same size (number of rows, number of columns) as your 2d array.

Your Array: Number of Rows

Assuming you have data, a 2-dimensional array, you can determine the number of rows by calling data.size:

rows = data.size

Your Array: Number of Columns

You can determine the number of columns by calling size on one of the data array's rows:

cols = data[0].size

Defining Your Worksheet Range

But to use the Range method, you need to define the column letters (ie, "A2:G7"). You probably already know that you want your starting point to be cell A1 or A2. But how do you programmatically determine the final column letter for an array that is 104 columns wide?

Each cell (or row, or column) object has an Address method that returns its location in letter-number format, so...

worksheet.Cells(5, 3).Address

...returns "$C$5", and we can use that string in our upcoming Range definition. So if you have a range that is 30 columns wide by 100 rows in length, which we want to insert into a Range, starting at cell A1:

first = worksheet.Cells(1,1).Address        # => "$A$1"
last = worksheet.Cells(100,30).Address      # => "$AD$100"
worksheet.Range("#{first}:#{last}").Value = data

If your worksheet has a header row and you will be inserting your data after that, bump up the row value by 1:

first = worksheet.Cells(2,1).Address        # => "$A$2"
last = worksheet.Cells(101,30).Address      # => "$AD$101"
worksheet.Range("#{first}:#{last}").Value = data

I hope that some of you find this useful. My thanks goes to Charlie for suggesting the topic.