ruby-on-rails - 使用 roo gem 将从 xlsx 提取的哈希存储在 ruby 中的数据库中
问题描述
抱歉我的英语不好和任何愚蠢的错误,但我从几个月开始就在学习 Ruby。我正在尝试从 .xlsx 文件中读取 usign roo gem,然后将我的行存储在数据库中,在一个已经存在的模型中。这是我的法院模型:
class Court < ApplicationRecord
belongs_to :type
validates :type_id, presence: true
validates :name, presence: true
validates :email, presence: true, uniqueness: true
validates :responsible, presence: true
validates :address, presence: true
validates :telephone, presence: true, format: { with: /\A([0-9]*\-?\ ?\/?[0-9]*)\Z/ }
geocoded_by :address
after_validation :geocode, :if => :address_changed?
end
# == Schema Information
#
# Table name: courts
#
# id :integer not null, primary key
# type_id :integer
# name :string(255)
# email :string(255)
# email_type :string(255)
# responsible :string(255)
# address :string(255)
# telephone :string(255)
# latitude :decimal
# longitude :decimal
# created_at :datetime not null
# updated_at :datetime not null
#
我创建了一个任务如下,excel.rake,用 roo 读取所有文件:
namespace :excel do
desc "Import Courts from Excel"
task import_courts: :environment do
xlsx = Roo::Excelx.new(Rails.root.join('app', 'assets', 'excel', 'Tribunali.xlsx'))
xlsx.parse(headers: true) do |hash|
puts hash.inspect # Array of Excelx::Cell objects
@court = Court.new
@court.hash
@court.save
end
end
end
在文件中,我有相同的模型标题,实际上,当我启动任务时,我获得以下输出:
{"Tipo"=>"Tipo", "Nome"=>"Nome", "Indirizzo e-mail"=>"Indirizzo e-mail", "Tipo email"=>"Tipo email", "Responsabile"=>"Responsabile", "Indirizzo"=>"Indirizzo", "Numero Telefonico"=>"Numero Telefonico"}
{"Tipo"=>"AMM", "Nome"=>"Ministero della Giustizia", "Indirizzo e-mail"=>"gabinetto.ministro@giustiziacert.it", "Tipo email"=>"PEC", "Responsabile"=>"Alfonso Bonafede", "Indirizzo"=>"Via Arenula, 70 - 00186 Roma (RM)", "Numero Telefonico"=>"06 68851"}
{"Tipo"=>"AOO", "Nome"=>"Archivio Notarile Distrettuale di Agrigento", "Indirizzo e-mail"=>"archivionotarile.agrigento@giustiziacert.it", "Tipo email"=>"PEC", "Responsabile"=>"Daniela Portera", "Indirizzo"=>"Via S. Vito, 97/103 - 92100 Agrigento (AG)", "Numero Telefonico"=>"09 2220290"}
{"Tipo"=>"AOO", "Nome"=>"Archivio Notarile Distrettuale di Alessandria", "Indirizzo e-mail"=>"archivionotarile.alessandria@giustiziacert.it", "Tipo email"=>"PEC", "Responsabile"=>"Susanna Cesarone Bongiorno", "Indirizzo"=>"Via Ghilini, 42 - 15121 Alessandria (AL)", "Numero Telefonico"=>"01 31254163"}
{"Tipo"=>"AOO", "Nome"=>"Archivio Notarile Distrettuale di Ancona", "Indirizzo e-mail"=>"archivionotarile.ancona@giustiziacert.it", "Tipo email"=>"PEC", "Responsabile"=>"Margherita Regini Santojanni", "Indirizzo"=>"Piazzale Europa, 7 - 60125 Ancona (AN)", "Numero Telefonico"=>"07 12804055"}
{"Tipo"=>"AOO", "Nome"=>"Archivio Notarile Distrettuale di Aosta", "Indirizzo e-mail"=>"archivionotarile.aosta@giustiziacert.it", "Tipo email"=>"PEC", "Responsabile"=>"Antonio Santoro", "Indirizzo"=>"Via Monsignor De Sales, 3 - 11100 Aosta (AO)", "Numero Telefonico"=>"01 65361395"}
{"Tipo"=>"AOO", "Nome"=>"Archivio Notarile Distrettuale di Arezzo", "Indirizzo e-mail"=>"archivionotarile.arezzo@giustiziacert.it", "Tipo email"=>"PEC", "Responsabile"=>"Gianna Baroni Pedone", "Indirizzo"=>"Via Francesco Crispi, 58/4 - 52100 Arezzo (AR)", "Numero Telefonico"=>"05 7523243"}
但是在数据库中我无法存储这些数据。有人可以帮助我吗?如果我忘记了要告诉你的事情,请提前道歉。
解决方案
我从来没有使用过“roo”gem,但是看了一下他们的文档和代码。像这样的东西应该可以完成这项工作:
# header_names and attribute_names must be provided in the same order
# since we work with them based on index later on (#fetch_values and #zip)
header_names = ['Tipo', 'Nome', '...']
attribute_names = [:type, :name, :'...']
Court.transaction do
# use headers: false to not return the headers
xlsx.parse(headers: false).each do |row|
values = row.fetch_values(*header_names)
attributes = attribute_names.zip(values).to_h
Court.create!(attributes)
end
end
由于工作表中的标题名称和模型的属性名称不同,您需要转换返回的行,这应该是一个核心Hash实例。
fetch_values
您可以使用(在缺少键时引发异常)或values_at
(在缺少键时返回默认值,未设置时为)检索字段的值nil
。然后zip
模型的标头带有值并将整个事物转换回散列。
然后用于create!
创建Court
实例并将其保存到数据库中。当出现问题回滚时,这会引发异常transaction
。您也可以使用create
,但这不会引发异常。这意味着无效记录被默默吞下,根本不保存。
需要指出的一件事是,您当前的输出显示"Tipo"=>"AMM"
,"AMM"
我猜的类型在哪里。然而,这不是一个 id,所以在尝试保存之前,您可能需要先获取类型 id 和其他 id。
像这样的东西:
attributes_list = xlsx.parse(headers: false).map do |row|
values = row.fetch_values(*header_names)
attributes = attribute_names.zip(values).to_h
end
type_ids = Type.where(name: attributes_list.pluck(:type).uniq).pluck(:name, :id).to_h
# fetch other ids if needed.
attributes_list.each do |attributes|
# remove :type and replace with :type_id (using #fetch to detect missing types)
attributes[:type_id] = type_ids.fetch(attributes.delete(:type))
end
# create and save Court instances from the attributes
Court.transaction do
attributes_list.each { |attributes| Court.create!(attributes) }
end
推荐阅读
- python - R:大熊猫(Python)的df.info()是否有R等价物?
- python - 如何使用networkx查找强连接组件的子图
- spartacus-storefront - 覆盖页面模板,子组件从活动路由中丢失
- chart.js - 页面上的两个chartjs图表抛出数据未定义错误
- python - TypeError:“NoneType”对象不能用 bs4 下标
- python - 比较python中的两个列表
- c# - 允许文本换行和平滑滚动的 Winforms 控件
- javascript - VueJS - 羽毛图标的无效渲染
- laravel - Laravel 混合夹板块供应商
- google-apps-script - 触发谷歌表格