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.

--
Posted via http://www.ruby-forum.com/.

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

···

--
Posted via http://www.ruby-forum.com/.

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.

···

Am 11.02.2013 16:40, schrieb Love U Ruby:

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.

--
<https://github.com/stomar/>

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!

···

--
Posted via http://www.ruby-forum.com/.

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

···

--
Posted via http://www.ruby-forum.com/.

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

···

--
Posted via http://www.ruby-forum.com/.

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.

···

--
Posted via http://www.ruby-forum.com/.

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

···

--
Posted via http://www.ruby-forum.com/.

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.

···

--
Posted via http://www.ruby-forum.com/.

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.

--
Posted via http://www.ruby-forum.com/.

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

···

--
Posted via http://www.ruby-forum.com/.

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. :frowning:

Thanks

···

--
Posted via http://www.ruby-forum.com/.

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!!!

···

--
Posted via http://www.ruby-forum.com/.

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

--
Posted via http://www.ruby-forum.com/.

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.

--
Posted via http://www.ruby-forum.com/.

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. :frowning:

Thanks

--
Posted via http://www.ruby-forum.com/.

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.

···

Am 11.02.2013 19:01, schrieb Love U Ruby:

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.

--
<https://github.com/stomar/>

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"?

--
Posted via http://www.ruby-forum.com/.

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

···

--
Posted via http://www.ruby-forum.com/.

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