格式化Excel单元格从数字到Rails中的文本 [英] Formatting Excel cell from number to text in rails

问题描述

我制作了一个应用程序，在该应用程序上提供了从CSV和Excel文件导入记录的功能.我正在使用roo gem.记录已成功添加，但是问题出在从excel导入记录时，它在每个数字字段中都添加了.0.我不想要它，因为我有一些字段，例如enrollment_no，roll_no，contact_no，并且它向每个文件都添加.0，就像将它设置为23到23.0一样.我已经将这些文件转换为数据库中的varchar，现在我想将Excel单元格的格式从数字转换为文本.它将解决我的问题.告诉我如何使用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.

这是我导入文件的代码:

Here is my code for importing the file:

student.rb:

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:

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

推荐答案

我正在我的应用程序中执行类似的操作，但是仅从csv导入使导入更加容易.

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

单元格类型似乎是一个很常见的常见问题Roo ，几乎没有建议使用正则表达式或char包含在您的单元格中的解决方法.

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

• 获取您要采用其他格式的列的索引
• 将从float导入的值转换为整数
• 将整数转换为字符串

这篇关于格式化Excel单元格从数字到Rails中的文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！