Open a MS Excel file from within Ruby

Ruby
1

Hi.

I’m writing a small Ruby pgm to demonstrate that FasterCVS is a good
way to handle CSV data.

I put up a small CSV file and demonstrated the FasterCSV aspect. I’d
like to precede that step by automatically bringing up the CSV file in
Excel. (I know I could bring it up manually, but I’d like a self-
contained demo for a computer-illiterate person.)

I’ve got “fn” set to a string which is the fully qualified name of the
CSV file. I concocted the (failing) command:

SystemCommand.start(“Excel”, fn)

based on my interpretation of the RDoc for Shell::SystemCommand. I
took a wild stabs to no avail at adding “require ‘system’” and
“require ‘shel’”

I’m running:
ruby 1.8.6 (2007-09-24 patchlevel 111) [i386-mswin32]
WinXP-Pro/SP3

TIA,
Richard

2

-------- Original-Nachricht --------

Datum: Sun, 26 Oct 2008 02:24:53 +0900
Von: RichardOnRails [email protected]
An: [email protected]
Betreff: Open a MS Excel file from within Ruby

I’ve got “fn” set to a string which is the fully qualified name of the
WinXP-Pro/SP3

TIA,
Richard

Dear Richard,

look here:

Best regards,

Axel

3

On Oct 25, 1:20 pm, RichardOnRails
[email protected] wrote:

I’ve got “fn” set to a string which is the fully qualified name of the
WinXP-Pro/SP3

TIA,
Richard

You probably should look at system (its in kernel so no require
needed)
something like:
system “start excel c:\pathto\my\file.csv”

cheers

4

On Oct 27, 1:57 pm, Chris H. [email protected] wrote:

I put up a small CSV file and demonstrated the FasterCSV aspect. I’d
took a wild stabs to no avail at adding “require ‘system’” and
needed)
something like:
system “start excel c:\pathto\my\file.csv”

cheers

Hi Axel & Chris,

Thanks for your responses. I had some success, but was unable to get
at the content of my Excel file.

Axel, I was able the get an Excel instance and find it’s methods.
What I didn’t find but think I need it to access the content of a
worksheet.

Chris, I was unable to use “system” to open an Excel file.

Maybe I little dense (or two dense to foll around with Win32OLE.)
Below is the code I fooled around with. If you have any other ideas,
I’d be grateful to receive them.

Best wishes,
Richard

LoadDataTest.rb

K:_Projects\Ruby_Rails_Apps\PayrollLoader

require ‘win32ole’
pgm = DATA.read.chomp
fn = DATA.read.chomp
puts pgm, fn, “”

excel = WIN32OLE.new(‘Excel.Application’)

puts excel.methods.sort --worked fine

puts excel.ole_get_methods–worked fine also, I think

Try to find an “open” method:

excel.ole_methods.each { |meth| puts meth if meth.to_s =~ /Op/i }
#system( “pgm”, fn ) # Couldn’t open the document

puts “”, “EOJ”
END
F:\Docume~1\AllUse~1\StartM~1\Programs_Microsoft
\Micros~3\Micros~2.lnk
K:_Projects\Ruby_Rails_Apps\PayrollLoader\TestSheet.csv

AceI fooled around with

5

From: RichardOnRails
…

Axel, I was able the get an Excel instance and

find it’s methods. What I didn’t find but think I

need it to access the content of a worksheet.

pls read more on what Axel pointed out…

i just tried the ff now eg, and it works

require ‘win32ole’
#=> false
xl = WIN32OLE.new(‘Excel.Application’)
#=> #WIN32OLE:0x2835674
wb = xl.Workbooks.Open(’/family/ruby/Book1.xls’)
#=> #WIN32OLE:0x2820ff8
worksheet = wb.Worksheets(‘Sheet1’)
#=> #WIN32OLE:0x28f94ac
worksheet.Range(‘a1’)[‘Value’]
#=> 1.0
worksheet.Range(‘a1:b4’)[‘Value’]
#=> [[1.0, “this”], [2.0, “is”], [3.0, “a”], [4.0, “test”]]

6

Barf! Avoid OLE like the plague. When you’re handling large numbers of
Excel files with Win32OLE you more or less need to monopolize Excel’s
usage
with Ruby. I find I also have large amounts of data stored in Window’s
paste board.

Give Spreadsheet gem a shot:
http://rubyforge.org/forum/forum.php?forum_id=28069. It’s
OS-independent,
and you avoid the crap I talked about above.

James