Reading data from excel

Ruby
1

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.

2

On 6/5/06, Parvinder G. [email protected] 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.

3

On 6/5/06, Madan M. [email protected] wrote:

Oops, ‘win23ole’ should have read ‘win32ole’

-Madan.

4

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

5

Parvinder G. 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

6

On 6/5/06, Parvinder G. [email protected] 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. (
Learn to Program, by Chris Pine)

Good luck.

Sean C.
http://sean-carley.blogspot.com/

7

Madan M. wrote:

On 6/5/06, Parvinder G. [email protected] 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
Programming Ruby: The Pragmatic Programmer's Guide

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

8

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

9

Parvinder G. 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

10

I cannot read excel in require ‘fox16’. How can I do to run excel file?

11

Thanks Gary, it seems like a pretty good source of information.
Parvinder

12

On May 22, 6:06 pm, Cool W. [email protected] wrote:

I cannot read excel in require ‘fox16’. How can I do to run excel file?

Posted viahttp://www.ruby-forum.com/.

win32ole

13

Simplier approach, if possible, save the excel in csv format.
Then parse the csv using fastercsv (http://fastercsv.rubyforge.org/)