CODEWITHSUNDEEP
rubyexcelruby-2.0roo-gem
Dan Garman
Dan Garman

Reputation: 656

Trim a trailing .0

I have an Excel column containing part numbers. Here is a sample

As you can see, it can be many different datatypes: Float, Int, and String. I am using roo gem to read the file. The problem is that roo interprets integer cells as Float, adding a trailing zero to them (16431 => 16431.0). I want to trim this trailing zero. I cannot use to_i because it will trim all the trailing numbers of the cells that require a decimal in them (the first row in the above example) and will cut everything after a string char in the String rows (the last row in the above example).

Currently, I have a a method that checks the last two characters of the cell and trims them if they are ".0"

def trim(row)
    if row[0].to_s[-2..-1] == ".0"
        row[0] = row[0].to_s[0..-3]
    end
end

This works, but it feels terrible and hacky. What is the proper way of getting my Excel file contents into a Ruby data structure?

Upvotes: 17

Views: 16654

Answers (4)

David Hempy
David Hempy

Reputation: 6287

This should cover your needs in most cases: some_value.gsub(/(\.)0+$/, '').

It trims all trailing zeroes and a decimal point followed only by zeroes. Otherwise, it leaves the string alone.

It's also very performant, as it is entirely string-based, requiring no floating point or integer conversions, assuming your input value is already a string:

Loading development environment (Rails 3.2.19)
irb(main):001:0> '123.0'.gsub(/(\.)0+$/, '')
=> "123"
irb(main):002:0> '123.000'.gsub(/(\.)0+$/, '')
=> "123"
irb(main):003:0> '123.560'.gsub(/(\.)0+$/, '')
=> "123.560"
irb(main):004:0> '123.'.gsub(/(\.)0+$/, '')
=> "123."
irb(main):005:0> '123'.gsub(/(\.)0+$/, '')
=> "123"
irb(main):006:0> '100'.gsub(/(\.)0+$/, '')
=> "100"
irb(main):007:0> '127.0.0.1'.gsub(/(\.)0+$/, '')
=> "127.0.0.1"
irb(main):008:0> '123xzy45'.gsub(/(\.)0+$/, '')
=> "123xzy45"
irb(main):009:0> '123xzy45.0'.gsub(/(\.)0+$/, '')
=> "123xzy45"
irb(main):010:0> 'Bobby McGee'.gsub(/(\.)0+$/, '')
=> "Bobby McGee"
irb(main):011:0>

Upvotes: 4

rdaniels
rdaniels

Reputation: 1009

For those using Rails, ActionView has the number_with_precision method that takes a strip_insignificant_zeros: true argument to handle this.

number_with_precision(13.00, precision: 2,  strip_insignificant_zeros: true)
# => 13
number_with_precision(13.25, precision: 2,  strip_insignificant_zeros: true)
# => 13.25

See the number_with_precision documentation for more information.

Upvotes: 38

jomo
jomo

Reputation: 14931

Numeric values are returned as type :float

def convert_cell(cell)
  if cell.is_a?(Float)
    i = cell.to_i
    cell == i.to_f ? i : cell
  else
    cell
  end
end

convert_cell("foobar") # => "foobar"
convert_cell(123) # => 123
convert_cell(123.4) # => 123.4

Upvotes: -1

sawa
sawa

Reputation: 168269

def trim num
  i, f = num.to_i, num.to_f
  i == f ? i : f
end

trim(2.5) # => 2.5
trim(23) # => 23

or, from string:

def convert x
  Float(x)
  i, f = x.to_i, x.to_f
  i == f ? i : f
rescue ArgumentError
  x
end

convert("fjf") # => "fjf"
convert("2.5") # => 2.5
convert("23") # => 23
convert("2.0") # => 2
convert("1.00") # => 1
convert("1.10") # => 1.1

Upvotes: 44

Related Questions