From: Sam Smoot <ssmoot@...>
Date: 2006-08-10T08:05:21+09:00
Subject: Re: Entering data into Excel, in specific iterated rows/columns

I'd probably recommend FasterCSV instead. You can use Excel through
WIN32OLE (google both terms for tutorials on how), but it's much Much
MUCH simpler to just use FasterCSV.

$ gem install fastercsv
$ irb
>> items = []
>> items << [ 'text1', 'link1' ]
>> items << [ 'text2', 'link2' ]
>> require 'faster_csv'
>> FasterCSV::open('links.csv', 'wb+') do |csv|
>> items.each { |item| csv << item }
>> end
>> exit
$ type links.csv
text1, link1
text2, link2

(It will handle quoting, escaping, etc for you).

You can open a CSV file with Excel just by double-clicking it usually
(Excel should be the default extension handler for CSV files unless
you've registered something else to the extension), and you can save
the file in Excel with "Save As..." if you need a true Excel file with
all the formatting options (CSV files can't remember column widths for
example if you want the widths auto-expanded to the size of the
content).

Even if you still wanted to go with a real Excel file, I'd probably
still use FasterCSV to generate an intermediary file since it's much
easier to do than messing with Excel.Application, Excel.Workbook,
Excel.Worksheet, Excel.Range, etc. Then, you can just instantiate an
Excel.Application, open the CSV as a workbook, and do a "Save As..."
programmatically. With a lot less code, and a lot less debugging effort
than trying to use Excel directly for everything. For example, if
anything goes wrong in your process while working with an
Excel.Application, the Excel process gets orphaned. So you have to wrap
the entire process in a rescue basically so you can make sure and
"ensure" "excel.Quit()" gets called.

If you want to do it the hard way though, then there's an excellent
starter at RubyGarden:
http://wiki.rubygarden.org/Ruby/page/show/ScriptingExcel

Good luck!