Forum: Ruby reading data from excel

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.
520d0b178a769fdffe5cd42b7132cdcc?d=identicon&s=25 Parv G. (parvg)
on 2006-06-05 18:19
Hi,
I'm brand new to Ruby.
Can somebody share their expertise on how to open and read data from
excel using ruby?
Thanks in advance.

And also any suggestions on where/how to learn Ruby would be very
helpful and appreciated.
508bca331a21d64d298561f1079b4113?d=identicon&s=25 Madan Manoharan (Guest)
on 2006-06-05 18:44
(Received via mailing list)
On 6/5/06, Parvinder Ghotra <ghotrapa@yahoo.com> wrote:
> Hi,
> I'm brand new to Ruby.
> Can somebody share their expertise on how to open and read data from
> excel using ruby?
> Thanks in advance.

A very rudimentary version that shows the logic:

require 'win23ole'

        application = WIN32OLE.new('Excel.Application')
        worksheet
=application.Workbooks.Open(excelFileName).Worksheets(workSheetName)
        worksheet.Activate

        contLoop = true           # Dummy counter for the loop

        while contLoop do
          colVal = worksheet.Cells(row, column).Value

          if (colVal) then
            # This field is not NIL, this means that this row contains a
entry
            # Get the SecurityObject corresponding to the cusip, and
get the coupon
            do processing ....
          else
            # This signifies the end - no cash column value, no short
name value.
            # End the loop
            contLoop = false
          end
          # go to the next Row
          row +=  1
        end

        # we are done
        application.Workbooks.Close



> And also any suggestions on where/how to learn Ruby would be very
> helpful and appreciated.
>

Pickaxe is a great book. You can find an online version at
http://www.ruby-doc.org/docs/ProgrammingRuby/html/index.html

All the best,
-Madan.
508bca331a21d64d298561f1079b4113?d=identicon&s=25 Madan Manoharan (Guest)
on 2006-06-05 18:48
(Received via mailing list)
On 6/5/06, Madan Manoharan <madan.manoharan@gmail.com> wrote:
>
Oops, 'win23ole' should have read 'win32ole'


-Madan.
520d0b178a769fdffe5cd42b7132cdcc?d=identicon&s=25 Parv G. (parvg)
on 2006-06-05 22:12
Madan Thanks for your quick response.

I'm trying to just open the excel application using the code below:

require 'win32ole'

excel = WIN32OLE.new('Excel.Application')
excel['Visible'] = true;

Something happens but i excel doesn't open up.
do i need to add something to the above code?
Thanks
Aee77dba395ece0a04c688b05b07cd63?d=identicon&s=25 Daniel Berger (Guest)
on 2006-06-05 22:47
(Received via mailing list)
Parvinder Ghotra wrote:
> Hi,
> I'm brand new to Ruby.
> Can somebody share their expertise on how to open and read data from
> excel using ruby?
> Thanks in advance.
>
> And also any suggestions on where/how to learn Ruby would be very
> helpful and appreciated.
>

In addition to using win32ole, also see the "parseexcel" package.

http://raa.ruby-lang.org/project/parseexcel/

Regards,

Dan
C2479d02d5795b2186ed86547dcba098?d=identicon&s=25 Sean Carley (Guest)
on 2006-06-05 23:05
(Received via mailing list)
On 6/5/06, Parvinder Ghotra <ghotrapa@yahoo.com> wrote:
>
> Can somebody share their expertise on how to open and read data from
> excel using ruby?


I would also recommend parseexcel.  The maintainer is very friendly and
the
package works quite well.

require "parseexcel/parseexcel"
file_name = "/your/file/name/here.xls"
workbook = Spreadsheet::ParseExcel.parse(file_name)
worksheet = workbook.worksheet(0) # the first sheet in the book
worksheet.each { |row| puts row.at(0) } # print the first column of each
row

And also any suggestions on where/how to learn Ruby would be very
> helpful and appreciated.
>

I would recommend "Why's (Poignant) Guide to Ruby" if your tolerance for
the
absurd is high.  (http://poignantguide.net/ruby/)  Otherwise, I would
recommend "Learn to Program" as a place to start online.  (
http://pine.fm/LearnToProgram/)

Good luck.

Sean Carley
http://sean-carley.blogspot.com/
Bfa670ef2303deb7dec5a8027367b30b?d=identicon&s=25 Mully (Guest)
on 2006-06-06 03:36
Parvinder Ghotra wrote:
> Madan Thanks for your quick response.
>
> I'm trying to just open the excel application using the code below:
>
> require 'win32ole'
>
> excel = WIN32OLE.new('Excel.Application')
> excel['Visible'] = true;
>
> Something happens but i excel doesn't open up.
> do i need to add something to the above code?
> Thanks

Parvinder-

Add a line of code to create a new workbook or open an existing one:

wb = excel.Workbooks.Add

  OR

wb = excel.Workbooks.Open(filename)

Mully
520d0b178a769fdffe5cd42b7132cdcc?d=identicon&s=25 Parv G. (parvg)
on 2006-06-13 15:28
Madan Manoharan wrote:
> On 6/5/06, Parvinder Ghotra <ghotrapa@yahoo.com> wrote:
>> Hi,
>> I'm brand new to Ruby.
>> Can somebody share their expertise on how to open and read data from
>> excel using ruby?
>> Thanks in advance.
>
> A very rudimentary version that shows the logic:
>
> require 'win23ole'
>
>         application = WIN32OLE.new('Excel.Application')
>         worksheet
> =application.Workbooks.Open(excelFileName).Worksheets(workSheetName)
>         worksheet.Activate
>
>         contLoop = true           # Dummy counter for the loop
>
>         while contLoop do
>           colVal = worksheet.Cells(row, column).Value
>
>           if (colVal) then
>             # This field is not NIL, this means that this row contains a
> entry
>             # Get the SecurityObject corresponding to the cusip, and
> get the coupon
>             do processing ....
>           else
>             # This signifies the end - no cash column value, no short
> name value.
>             # End the loop
>             contLoop = false
>           end
>           # go to the next Row
>           row +=  1
>         end
>
>         # we are done
>         application.Workbooks.Close
>
>
>
>> And also any suggestions on where/how to learn Ruby would be very
>> helpful and appreciated.
>>
>
> Pickaxe is a great book. You can find an online version at
> http://www.ruby-doc.org/docs/ProgrammingRuby/html/index.html
>
> All the best,
> -Madan.

Thanks everybody for your time and help.

Issue:
I'm getting an error "undefined local variable row" at line
>           colVal = worksheet.Cells(row, column).Value

Thanks for your help,
-Parvinder
93a09dc298db7b3c9da828b8e6072597?d=identicon&s=25 Gary Watson (Guest)
on 2006-06-14 06:14
(Received via mailing list)
Try this link, it helped me greatly when I was trying to wrap my head
around
how to script excel

http://wiki.rubygarden.org/Ruby/page/show/ScriptingExcel
520d0b178a769fdffe5cd42b7132cdcc?d=identicon&s=25 Parv G. (parvg)
on 2006-06-15 04:04
Thanks Gary, it seems like a pretty good source of information.
Parvinder
645cd831f3b2eee7ba15fd8256796c57?d=identicon&s=25 Cool Wong (coolwong)
on 2008-05-22 12:06
I cannot read excel in require 'fox16'. How can I do to run excel file?
76b2a90aeb642295f8e4b0478aaf370d?d=identicon&s=25 nnnnon@gmail.com (Guest)
on 2008-05-23 10:26
(Received via mailing list)
On May 22, 6:06 pm, Cool Wong <coolwon...@yahoo.com> wrote:
> I cannot read excel in require 'fox16'. How can I do to run excel file?
> --
> Posted viahttp://www.ruby-forum.com/.

win32ole
46fe0eb9a5b22f775db2ea0711d5b142?d=identicon&s=25 Kenny Lee (shevken)
on 2008-05-23 10:30
Simplier approach, if possible, save the excel in csv format.
Then parse the csv using fastercsv (http://fastercsv.rubyforge.org/)
This topic is locked and can not be replied to.