Asp Forum - Open a MS Excel file from within Ruby

RichardOnRails

10/25/2008 5:21:00 PM

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

5 Answers

Axel Etzold

10/25/2008 6:20:00 PM

-------- Original-Nachricht --------
> Datum: Sun, 26 Oct 2008 02:24:53 +0900
> Von: RichardOnRails <RichardDummyMailbox58407@uscomputergurus.com>
> An: ruby-talk@ruby-lang.org
> Betreff: Open a MS Excel file from within Ruby

> 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

Dear Richard,

look here:

http://rubyonwindows.blogspot.com/2007/04/ruby-win32ole-inspecting-ob...

Best regards,

Axel
--
Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!
Ideal für Modem und ISDN: http://www.gmx.net/de/go/s...

Chris Hulan

10/27/2008 5:57:00 PM

On Oct 25, 1:20 pm, RichardOnRails
<RichardDummyMailbox58...@uscomputergurus.com> wrote:
> 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

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

10/29/2008 4:10:00 AM

On Oct 27, 1:57 pm, Chris Hulan <chris.hu...@gmail.com> wrote:
> On Oct 25, 1:20 pm, RichardOnRails
>
>
>
> <RichardDummyMailbox58...@uscomputergurus.com> wrote:
> > 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
>
> You probably should look at system (its in kernel so no require
> 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

10/29/2008 5:05:00 AM

From: RichardOnRails=20
...=20
# Axel, I was able the get an Excel instance and=20
# find it's methods. What I didn't find but think I=20
# 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'
#=3D> false
xl =3D WIN32OLE.new('Excel.Application')
#=3D> #<WIN32OLE:0x2835674>
wb =3D xl.Workbooks.Open('/family/ruby/Book1.xls')
#=3D> #<WIN32OLE:0x2820ff8>
worksheet =3D wb.Worksheets('Sheet1')
#=3D> #<WIN32OLE:0x28f94ac>
worksheet.Range('a1')['Value']
#=3D> 1.0
worksheet.Range('a1:b4')['Value']
#=3D> [[1.0, "this"], [2.0, "is"], [3.0, "a"], [4.0, "test"]]

RichardOnRails

10/31/2008 5:24:00 AM

On Oct 29, 10:49 am, James Herdman <james.herd...@gmail.com> wrote:
> 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?foru.... It's OS-independent,
> and you avoid the crap I talked about above.
>
> James
>
> On Sat, Oct 25, 2008 at 1:20 PM, Axel Etzold <AEtz...@gmx.de> wrote:
>
> > -------- Original-Nachricht --------
> > > Datum: Sun, 26 Oct 2008 02:24:53 +0900
> > > Von: RichardOnRails <RichardDummyMailbox58...@uscomputergurus.com>
> > > An: ruby-t...@ruby-lang.org
> > > Betreff: Open a MS Excel file from within Ruby
>
> > > 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
>
> > Dear Richard,
>
> > look here:
>
> >http://rubyonwindows.blogspot.com/2007/04/ruby-win32ole-ins......
>
> > Best regards,
>
> > Axel
> > --
> > Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!
> > Ideal für Modem und ISDN:http://www.gmx.net/de/go/s...
>
>

Hi All,

Thanks for your responses. I posted what I tried so far on
http://www.pastie....

(Anonymous): I did check out http://rubyonwindows.blogspot.com/2007/04/ruby-win32ole-ins...jects.html
but didn’t see (or perhaps didn’t recognize) an answer to my
question. But your example worked fine for my simple needs. Thank
you very much.

James: OLE has lots of people using it for Windows applications.. I
have a user who uses Excel spreadsheets and I wanted to be able to
manipulate that data through a Rails app. This little solution will
work fine for my purposes. Mainly, I’ll be using a .csv extract of
that data and a “cvs reader” gem to access that data ...which BTW
works fine.

Again, thanks to all for posting responses.

Best wishes,
Richard

comp.lang.ruby

Open a MS Excel file from within Ruby

RichardOnRails

Axel Etzold

Chris Hulan

RichardOnRails

Peña, Botp

RichardOnRails

x Login to ForumsZone