Formatting Excel cell from number to text in rails

Question

I have made an application on which I have provide the feature to import the records from CSV and Excel file. I am using roo gem for it. The record added successfully but the problem is at the time of importing records from excel, it adds .0 to every field which is number. I don't want it because i have some fields like enrollment_no, roll_no, contact_no and it adds .0 to every filed like it made 23 to 23.0. I already had converted these filed to varchar in database and now i want to format the excel cell from number to text. It will solve my problem. Tell me how i will format the excel cell from number to string using rails.

Here is my code for importing the file:

student.rb :

def self.import(file, current_organization_id)
  spreadsheet = open_spreadsheet(file)
  header = spreadsheet.row(1)
  (2..spreadsheet.last_row).each do |i|
    row = Hash[[header, spreadsheet.row(i)].transpose]
    record = Student.find_by(:organization_id => current_organization_id,:enrollment_no => row["enrollment_no"]) || new
    record.organization_id= current_organization_id
    record.attributes = row.to_hash.slice(*row.to_hash.keys)
    record.save!
  end
end


def self.open_spreadsheet(file)
  case File.extname(file.original_filename)
  when ".csv" then Roo::CSV.new(file.path)
  when ".xls" then Roo::Excel.new(file.path)
  when ".xlsx" then Roo::Excelx.new(file.path)
  else raise "Unknown file type: #{file.original_filename}"
  end
end

students_controller.rb :

def import
    Student.import(params[:file], session[:current_organization_id])
    #puts @session[:current_organization_id].inspect
    redirect_to students_path, notice: "Record imported Successfully."
  end

new.html.erb :

<%= form_tag import_students_path, multipart: true do %>
    <%= file_field_tag :file , :required=> true%> <br/>
    <%= submit_tag "Import" , :class => "btn btn-primary btn-block" %>
<% end %>

score 0 · Answer 1 · answered Jan 15 '16 at 14:31

I am doing something similar in my application but the import is made easier by importing only from csv.

It seems that cell type is a pretty common problem in Roo and there are few workaround suggested using regex or char to include in your cell.

My solution it would be much easier:

# student.rb

COLUMNS_TO_STRING = ["organization_id", "enrollment_no", "contact_no"] # and so on


def self.import(file, current_organization_id)
  spreadsheet = open_spreadsheet(file)
  header = spreadsheet.row(1)
  (2..spreadsheet.last_row).each do |i|
    row = Hash[[header, spreadsheet.row(i)].transpose]
    row = clean_for row, COLUMNS_TO_STRING
    record = Student.find_by(:organization_id => current_organization_id,:enrollment_no => row["enrollment_no"]) || new
    record.organization_id= current_organization_id
    record.attributes = row.to_hash.slice(*row.to_hash.keys)
    record.save!
  end
end

def self.clean_for row_as_hash, string_columns_array
  row_as_hash.each do |key, value|
    if string_columns_array.include?key
      row_as_hash[key] = value.to_i.to_s
    end
  end
end

def self.open_spreadsheet(file)
  case File.extname(file.original_filename)
  when ".csv" then Roo::CSV.new(file.path)
  when ".xls" then Roo::Excel.new(file.path)
  when ".xlsx" then Roo::Excelx.new(file.path)
  else raise "Unknown file type: #{file.original_filename}"
  end
end

get the index of the columns you want to format differently
convert the value imported from float to integer
convert the integer to string

Formatting Excel cell from number to text in rails

1 Answers1