Error when trying to read from excel file

Ruby
1

Hi,

Am new to ruby. I just started working on it from yesterday.

I have been given a huge task to finish.

One of my scenario is :

  1. Access an excel file.
  2. I already have a value say “abc” and “xyz”.
  3. In that excel file, I need to check column A for “abc” and column B
    for “xyz” and get the value from the column P and Q.
  4. Am lost to how to write this in ruby.

I have started with the following code using Aptana IDE. And I got an
error.
Please help me.

My code :

require ‘rubygems’
require ‘roo’
HOURLY_RATE = 123.45
oo = Openoffice.new(‘F:\StatusInterface\Code_Report_Genial.ods’)
oo.default_sheet = oo.sheets.first
upto(12) do |line|
Code Situation = oo.cell(line,‘A’)
Code Justification = oo.cell(line,‘B’)
Code = oo.cell(line,‘P’)
Description = oo.cell(line,‘Q’)
puts “#{Code Situation}\t#{Code Justification}\t#{Code}\t#{Description}”
end

Error message :

project_samples.rb:16:in <main>': undefined methodupto’ for
main:Object (NoMethodError)

2

If you read the error message closely, it says that upto does not
exist. What you are actually looking for is Integer#upto. For example:

1.upto(12) { ... } # 1..12
5.upto(17) { ... } # 5..17
2.upto(3)  { ... } # 2 and 3

Since you trying to execute the code in the loop 12 times, I suggest you
use the Integer#times method to make your intentions more clear. For
example, this is how you would write the above code snippets with
Integer#times:

12.times { ... } # 1..12
13.times { ... } # 5..17
2.times  { ... } # 2 and 3
3

Hi,

Thanks for the reply. But like I said am totally a newbie in ruby. Where
should I exactly include the suggestion that you have given here. And
how should I write it in ruby?

Thanks for your patience. I really need all the help I can get for Ruby.

Thanks.

Bryan D. wrote in post #1049901:

If you read the error message closely, it says that upto does not
exist. What you are actually looking for is Integer#upto. For example:

1.upto(12) { ... } # 1..12
5.upto(17) { ... } # 5..17
2.upto(3)  { ... } # 2 and 3

Since you trying to execute the code in the loop 12 times, I suggest you
use the Integer#times method to make your intentions more clear. For
example, this is how you would write the above code snippets with
Integer#times:

12.times { ... } # 1..12
13.times { ... } # 5..17
2.times  { ... } # 2 and 3
4

You mean like this?
I’ve never used this gem too.
But the results are looked succeed.

on:

  • ruby 1.9.3p125 (2012-02-16) [i386-mingw32]
  • roo-1.10.1

=begin
require ‘roo’

spreadsheet = Excel.new ‘example.xls’
spreadsheet.default_sheet = spreadsheet.sheets.first

1.upto 12 do |row_number|
situation, justification, code, description =

  • %w[A B P Q].map{|line_symbol|spreadsheet.cell row_number,
    line_symbol}

if situation == ‘abc’ and justification == ‘xyz’
puts [situation, justification, code, description].join("\t")
end
end
=end

Lekha Selvaraj wrote in post #1050301:

Any help here pls.

5

Ok I changed the code to be :

require ‘spreadsheet’
Spreadsheet.open(‘F:\testing.xls’) do |book|
book.worksheet(‘A’).each do |row|
break if row[0].nil?
puts row.join(’,’)
end
end

  1. Its reading everything from my excel file.
  2. What I really need is :
    a. I have pass value to the excel file. Lets assume the gvalue to be
    “abc” and “xyz”.
    b. These two values will be there in the column 1 and 2 of the excel
    file “testing.xls”.
    c. I need to check that particular row which has the value in column1 as
    “abc” and column2 as “xyz” and collect the data from column P and column
    Q.

Please somebody modify the code I have written here. To do that.

6

Any help here pls.

7

Thanks Kenichi. Your code worked well. I need a little information

1.upto 12 do |row_number|

But the excel file am reading will sometimes have 20 rows or 80 rows

so instead of:

1.upto 12 do |row_number|

How should I write

1.upto n do |row_number|

8

I think we could do with this excell sheet and explanation on what you
want to get from that sheet.

Cheers.