在rails中将Excel单元格从数字格式化为文本

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.

我做了一个应用程序，我提供了从CSV和Excel文件导入记录的功能。我正在使用roo gem。记录添加成功，但问题是在从excel导入记录时，它将.0添加到每个字段中。我不想要它，因为我有一些字段，比如注册_no roll_no contact_no，它给每个字段都加上0。0就像23到23。0一样。我已经在数据库中将这些文件转换为varchar，现在我想将excel单元格从数字格式转换为文本格式。这将解决我的问题。告诉我如何使用rails从数字到字符串格式化excel单元格。

Here is my code for importing the file:

以下是我导入文件的代码:

student.rb :

学生。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 :

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 :

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

1 个解决方案

#1

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

我在我的应用程序中做了一些类似的事情，但是导入只是从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.

在Roo中，单元格类型似乎是一个非常常见的问题，在您的单元格中几乎没有建议使用regex或char来包含。

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
将从float导入的值转换为integer
convert the integer to string
将整数转换为字符串。

#1