Geek Answers Handbook

Format Excel cell from number to text in rails

I created an application on which I provided a function to import records from a CSV file and Excel. I use a pearl for this. The record was successfully added, but the problem is when importing records from excel, it adds .0 to each field, which is a number. I do not want this because I have some fields, such as enrollment_no, roll_no, contact_no, and it adds .0 to each filed, as it did 23-23,0. I have already converted these files in varchar to a database, and now I want to format the excel cell from number to text. This will solve my problem. Tell me how I will format the excel cell from number to row using rails.

Here is my code to import 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 %>
+4
1

- , csv.

, Roo, , char .

:

# 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
  • , -.
  • , float integer
0

More articles:


All Articles