Forum: Ruby Issue with Excel column values read.

Hi,

I stuck in a point in my code. I have an excel which contains values as
below :


Tax code
========

wst-45
tre-2%
12.45%
10
5
ascht5

When my code is trying to read the values only the bad data is coming
from the values like 5,10.. They are being converted to 10.0,5.0. Which
causes my code to fail,as in the select list no such values like 10.0 or
5.0. I tried to fix it by formatting the Excel column as "Text",but
doesn't work.

CODE:

if wbs.cells(rows,8).value != nil

elem = driver.find_element(:name, "defaultAnswerId")
option = Selenium::WebDriver::Support::Select.new(elem)
puts wbs.cells(rows,8).value
default_val = wbs.cells(rows,8).value.strip
option.select_by(:text, default_val)

end


any idea how to fix that.

If you already know it has to be an integer, use ".to_i", or "Integer()" 
in the appropriate places.

Joel Pearson wrote in post #1096311:
> If you already know it has to be an integer, use ".to_i", or "Integer()"
> in the appropriate places.

No! @Joel - there can be anything - that's the problem. So I am looking 
for a way to fetch the value from the Excel cells as it is,no conversion 
during fetching.

If you're using the spreadsheet gem (which I am for one script), then 
you'll
have to use .to_i since values are always brought in as real numbers. If 
the
issue is that you may have a nil value, just do this:

excel_value.to_i if excel_value.!nil?

Wayne


-
Joel Pearson wrote in post #1096311:
> If you already know it has to be an integer, use ".to_i", or "Integer()"
> in the appropriate places.

No! @Joel - there can be anything - that's the problem. So I am looking
for a way to fetch the value from the Excel cells as it is,no conversion
during fetching.

Wayne Brisette wrote in post #1096314:
> If you're using the spreadsheet gem (which I am for one script), then
> you'll
> have to use .to_i since values are always brought in as real numbers. If
> the
> issue is that you may have a nil value, just do this:
>
> excel_value.to_i if excel_value.!nil?
>
> Wayne
>
>

This is the column as an example :

Tax code
========

wst-45
tre-2%
12.45%
10
5
ascht5


where different kind of values can be present. And I am using 
"win32ole". So how could I use the "to_i"?

Am 11.02.2013 16:40, schrieb Love U Ruby:
> tre-2%
>
> end
>
>
> any idea how to fix that.

Not without the information where the `wbs' object came from.

Also, if your problem is not related to Selenium then most of
your provided code seems not to be relevant here.

Please provide a code snippet as self-contained and to the point
as possible.

unknown wrote in post #1096322:
> Am 11.02.2013 16:40, schrieb Love U Ruby:
>> tre-2%
>>
>> end
>>
>>
>> any idea how to fix that.
>
> Not without the information where the `wbs' object came from.
>


I have provided the code, which is the most victim, so the full code is 
doing lot's of operations. So that would confuse the whole pain area. 
And I have told that I am using "win32ole". wbs is the object referring 
to the excel work sheet.

Thanks.

Love U Ruby wrote in post #1096307:
> in the select list no such values like 10.0 or 5.0

You know where to change the value to an integer. Just before you select 
in on a list which takes integers!

Joel Pearson wrote in post #1096348:
> Love U Ruby wrote in post #1096307:
>> in the select list no such values like 10.0 or 5.0
>
> You know where to change the value to an integer. Just before you select
> in on a list which takes integers!


now say a column has value 10 and 15.2. Now ruby fetches 15.2 as 
expected,but for 10, it is 10.0. So if I check for integer,it would say 
NO - 10 is float, as program read it as 10.0.

thanks

And how many select lists do you use which take floats?

Joel Pearson wrote in post #1096351:
> And how many select lists do you use which take floats?


In the web-page only one "select list" which contains all mixture of 
data? And the integer is the pain of mine. :(


Thanks

Looking at your previous example list, you're right you have a mixture. 
But the
question I have is how do you know *what* is suppose to be there? Is 
every 6th
item in your list suppose to be an integer? You have to do a bit of 
critical
thinking here. How is the program suppose to know what to use? Here is 
the one
know I can provide. You know that numbers will always be represented as 
real
numbers. The question is knowing that, what can you trigger off of to 
determine
when to make it an integer? Once you determine that bit of logic, 
figuring out
the code will be a piece of cake.

Wayne


----- Original Message ----
From: Love U Ruby <lists@ruby-forum.com>
To: ruby-talk ML <ruby-talk@ruby-lang.org>
Sent: Mon, February 11, 2013 1:51:48 PM
Subject: Re: Issue with Excel column values read.

Joel Pearson wrote in post #1096351:
> And how many select lists do you use which take floats?


In the web-page only one "select list" which contains all mixture of
data? And the integer is the pain of mine. :(


Thanks

Wayne Brisette wrote in post #1096356:
> Looking at your previous example list, you're right you have a mixture.
> But the
> question I have is how do you know *what* is suppose to be there? Is
> every 6th
> item in your list suppose to be an integer? You have to do a bit of
> critical
> thinking here. How is the program suppose to know what to use? Here is
> the one
> know I can provide. You know that numbers will always be represented as
> real
> numbers. The question is knowing that, what can you trigger off of to
> determine
> when to make it an integer? Once you determine that bit of logic,

There presence is dynamic in the column, but some times couldn't be 
there.


Thanks

You could write a routine to check the value if its a real number, make 
it an
integer. That's about the best I think you can hope for doing it the way 
you are
now.

Wayne

Wayne Brisette wrote in post #1096362:
> You could write a routine to check the value if its a real number, make
> it an
> integer. That's about the best I think you can hope for doing it the way
> you are
> now.
>
> Wayne


Okay, when the original real number comes into that column say 15.2 
then, converting it to integer will again would cause a fatal error as 
no such element or values found.. :(

I don't know whether Selenium-Webdriver is easily capable of this kind 
of interaction, but this is the sort of thing I'd do when faced with an 
unknown select list in Watir-Webdriver:

val = #Your current value

elem = driver.select_list(:name, "defaultAnswerId")

options = elem.options.map(&:text)

if options.include?(val)
  elem.select(val)
elsif val.class == Float && options.include?(val.to_i)
  elem.select(val.to_i)
else
  puts "#{val} not found in list"
end

Joel Pearson wrote in post #1096372:

> val = #Your current value
>
> elem = driver.select_list(:name, "defaultAnswerId")
>
> options = elem.options.map(&:text)
>
> if options.include?(val)
>   elem.select(val)
> elsif val.class == Float && options.include?(val.to_i)
>   elem.select(val.to_i)
> else
>   puts "#{val} not found in list"
> end

Wooowwww!!!

Right, but again, this is where critical thinking comes into play (and 
rescue).

Go look here: There are some good examples of things to look at.

http://stackoverflow.com/questions/1235863/test-if...

I'd rather avoid using rescue unless it's unavoidable. Testing the 
values before committing leaves room to capture genuine exceptions 
rather than using them for flow control.

Am 11.02.2013 19:01, schrieb Love U Ruby:
>>
>
>
> I have provided the code, which is the most victim, so the full code is
> doing lot's of operations. So that would confuse the whole pain area.
> And I have told that I am using "win32ole". wbs is the object referring
> to the excel work sheet.

First, in your original post you did *not* state that you use win32ole.

Second, you asked how to read the values from the work sheet without
conversions, and to answer that question the crucial part of the code
would be how you got the wbs object, with which gem, etc.

Love U Ruby wrote in post #1096307:

>
> When my code is trying to read the values only the bad data is coming
> from the values like 5,10.. They are being converted to 10.0,5.0. Which
> causes my code to fail,as in the select list no such values like 10.0 or
> 5.0. I tried to fix it by formatting the Excel column as "Text",but
> doesn't work.
>
> CODE:
>
> if wbs.cells(rows,8).value != nil
>
> elem = driver.find_element(:name, "defaultAnswerId")
> option = Selenium::WebDriver::Support::Select.new(elem)
> puts wbs.cells(rows,8).value
> default_val = wbs.cells(rows,8).value.strip
> option.select_by(:text, default_val)
>
> end
>
>
> any idea how to fix that.

Use the Text property of a cell, rather than the Value property.

For example, change...

    wbs.cells(rows,8).value

...to...

    wbs.cells(rows,8).Text

The Value property returns the underlying value as stored by Excel (for
numbers this is a decimal).

The Text property returns the value as it is displayed.

David

http://rubyonwindows.blogspot.com/search/label/excel

David Mullet wrote in post #1096585:

Wowwww... Really nice, I never have thought about the things,you pointed 
out here. Definitely I will give it a try.

> Use the Text property of a cell, rather than the Value property.
>
> For example, change...
>
>     wbs.cells(rows,8).value
>
> ...to...
>
>     wbs.cells(rows,8).Text
>
> The Value property returns the underlying value as stored by Excel (for
> numbers this is a decimal).
>
> The Text property returns the value as it is displayed.
>
> David
>
> http://rubyonwindows.blogspot.com/search/label/excel