Forum: Ruby Open a MS Excel file from within Ruby

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
RichardOnRails (Guest)
on 2008-10-25 21:28
(Received via mailing list)
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
Axel E. (Guest)
on 2008-10-25 22:21
(Received via mailing list)
-------- Original-Nachricht --------
> Datum: Sun, 26 Oct 2008 02:24:53 +0900
> Von: RichardOnRails <removed_email_address@domain.invalid>
> An: removed_email_address@domain.invalid
> 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:

http://rubyonwindows.blogspot.com/2007/04/ruby-win...

Best regards,

Axel
Chris H. (Guest)
on 2008-10-27 20:01
(Received via mailing list)
On Oct 25, 1:20 pm, RichardOnRails
<removed_email_address@domain.invalid> 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
RichardOnRails (Guest)
on 2008-10-29 06:15
(Received via mailing list)
On Oct 27, 1:57 pm, Chris H. <removed_email_address@domain.invalid> 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
Peña, Botp (Guest)
on 2008-10-29 07:06
(Received via mailing list)
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"]]
James H. (Guest)
on 2008-10-29 16:51
(Received via mailing list)
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
This topic is locked and can not be replied to.