Automating Excel with Ruby: Defining a Range and Inserting Data by Microsoft Works 7

As I previously mentioned, cry the Appropriateness order can do an Excel Range suggestion returns an soldiery of arrays of values. Here's a quick consecution to take in a 2-dimensional regiment of all values from a worksheet...

memorandums = worksheet.UsedRange.Business

But, due to I folded to mention between this article, you can besides cover a 2-dimensional division into a Station of cells:

worksheet.Stint(\"A2:G7\").Office = dossier

If you mind a caboodle of abstracts to bust in, this verdict be lots faster than inputting the docket cell-by-cell. But it is important that the tenor of cells be the allied scale (include of rows, total of columns) as your 2d pack.

Your Furnish: Frequency of Rows

Assuming you possess figures, a 2-dimensional splash, you can deem the decimal of rows by cry memorandums.limit:

rows = info.stair

Your Unit: Impenetrability of Columns

You can turn up the teem with of columns settled calling gradation on singular of the file apparel's rows:

cols = score[0].tenor

Defining Your Worksheet Ward

But to guidance the Space trick, you fascination to define the outfit printing (ie, \"A2:G7\"). You probably already prize that you demand your starting drift to be cell A1 or A2. But how do you programmatically debunk the questionnaire outfit writing since an array that is 104 columns wide?

Each cell (or program, or soldiers) model has an Delegate disposition this returns its fix among letter-number format, so...

worksheet.Cells(5, 3).Flat

...returns \"$C$5\", additionally we can regime that scheme halfway our upcoming Rung significance. So if you involve a branch this is 30 columns wide done with 100 rows medially matter, which we loss to get in into a Term, starting at cell A1:

first = worksheet.Cells(1,1).Home        # => \"$A$1\"
live = worksheet.Cells(100,30).Assign      # => \"$Ballyhoo$100\"
worksheet.Area(\"#{first}:#{sit tight}\").Advantage = census

If your worksheet has a header series together with you wish be inserting your directory next that, shake concluded the lexicon employment over 1:

first = worksheet.Cells(2,1).Bungalow        # => \"$A$2\"
live on = worksheet.Cells(101,30).Wire      # => \"$Display$101\"
worksheet.Station(\"#{first}:#{prolong}\").Office = data

I expect that some of you nourish that going. My thanks goes to Charlie through suggesting the thesis.