今回使用するExcelファイルのデータの読み込みを行うGemは rubyXL です。
- rubyXLを使って、多人数のユーザー情報を記述したエクセルファイル(.xlsx)を読み込み、複数のデータを一気に登録修正をします。
インストール
rubyXl
gem 'rubyXL'rubyXL では、Excelファイル(.xlsx)の読み書きができます。Excelファイル(.xls)には対応していません。
Excelファイル(.xlsx)を読み込むには workbook = RubyXL::Parser.parse(“path/to/Excel/file.xlsx”)
新規ワークシートを作成するには workbook = RubyXL::Workbook.new
とします。
Controller
概観
アップロードしたファイルに記述されたユーザー情報をusersテーブルに登録あるいは修正します。
- アップロードしたExcel(xlsx)ファイルを受け取り、
RubyXL::Parser.parseメソッドで開きます。 - ファイルの１行目の値をチェックし、その値をキーとして２行目以降の値を持つハッシュを作ります。そのハッシュを使って１件のデータを処理します。
- データベースに登録する値だけを持つハッシュを作り
user.attributesにセットしてsaveします。
rubyXL でファイルを開く
Excel(xlsx)ファイルを扱います。下のメソッドではアップロードしたファイルを引数にしています。具体的には「params["upload_file"]」としてファイルフィールドタグで送信されたファイルを受け取ったTempfileオブジェクトです(params についてを参照)。
1
2
3
4
5
6
7
def open_spreadsheet(uploaded_file)
if File.extname(uploaded_file.original_filename) == '.xlsx'
return RubyXL::Parser.parse(uploaded_file.path)
else
return false
end
end
表の列項目をチェックする
今回取り扱うシートの例です。
開いたシートの１行目にその列の項目名が記述してあります。その項目名が正しいものであるかをチェックするための配列を作ります。正しければ１行目の項目名と下記の配列は一致しています。
1
2
3
def header_attributes
["name", "password", "destroy"]
end
また、シートの２行目以降を読み込む時、この配列に一致する列の項目名をキーとし、対応するセルの値をハッシュの値にして利用できるようにします。例えば２行目を読み込んだ時には row["name"] => "aaa" や row["password"] => "aaa123" のようなハッシュを作ります。
３列目の destroy は、データを登録したいテーブル(usersテーブル)にそのカラムがないものです。usersテーブルにあるカラム名からなる配列も作ります。
1
2
3
def updatable_attributes
["name", "password"]
end
これを利用して、登録する項目のキーとその値だけを持つハッシュを作ります。
スプレッドシートの１行目を読み込む
- rubyXLで開いたワークブック(
workbook)の1枚目のスプレッドシート(workbook[0])を扱います。 - 1枚目のスプレッドシート(
spreadsheet)の１行目は、spreadsheet[0]で受け取ることができます。２行目を受け取るには引数に「1」を指定します。spreadsheet.sheet_data[0]、のようにも書けます。 - 表示用に使われるような過剰なセルオブジェクトが
spreadsheetオブジェクトに含まれていますが、読み込んで処理をするのに必要なのはセルの値だけですのでspreadsheet.extract_dataで必要な情報だけにできます。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
workbook = open_spreadsheet(file)
if workbook # falseでなければインスタンスが入っている。
spreadsheet = workbook[0].extract_data # 1ページ目のシートのセルの値だけ取得する
spreadsheet_header = spreadsheet[0] # 1行目を取得
header = header_attributes
if header == spreadsheet_header
# 正しいファイルが送られてきた。
... # (※1)
else
# ファイルを間違えているか、１行目の書き方が違っている。
end
else
# ファイルの拡張子が異なる。
end
６行目でアップロードされてきたスプレッドシートの１行目がheader_attributesと同じかどうかを判定しています。
１件分のユーザー情報を登録する
スプレッドシートの２行目以降をループで読み込み、シートの１行分のセルの値をハッシュに格納する
先ほどのソースの(※1)の部分のコードです。
1
2
3
4
5
6
(1..(spreadsheet.length - 1)).each do |i|
ary = [header, spreadsheet[i]].transpose
row = Hash[*ary.flatten]
... # (※2)
end
- スプレッドシート最後の行のインデックスが
spreadsheet.length - 1です。これを使ってシートの２行目(インデックスは1)から最後の行まで１行ずつ読み込むループを作ります。 - シートの
i + 1行目の値から、１行目の値をキーとするハッシュにします。それがHash[[header, spreadsheet.row(i)].transpose]です(このハッシュの作り方についてはRuby 配列からHashを作る を参照)。
row['name']のようにして現在の行の各セルの値を取り出すことができるようになりましたので、(※2)でそのハッシュを使って必要な処理を施すことができます。
登録できるデータだけを持つハッシュを作り、モデルの属性にセットする
ハッシュrowのキーには「name」「password」「destroy」の３つがありますが、登録するusersテーブルのカラム名と同じなのは「name」「password」の２つだけです。ハッシュrowのキーのうちupdatable_attributesと同じキーだけからなるハッシュを作ります。
1
attr = row.slice(*updatable_attributes)
sliceについはslice - HashのActiveSupport 拡張を参照してください。
既に登録されているユーザーがいたら読み込み、そうでなければ新規ユーザーとして登録します。
1
2
3
4
5
6
user = User.find_by(name: row['name']) || User.new
...
user.attributes = attr
user.save
saveは、新規データの登録と、既存データの更新の双方に使うことが出来るメソッドとなっていて、更新では変更された属性の内容でDBを更新します。
Float を Integer へ変換
これはフロート型にされてしまうで記述した内容ですが、ことはMS Excelの問題のためここでも発生しますので、再録します。 Excelファイルでセル内には整数で入力した値、たとえば123であったものが、受け取ったspreadsheetでは123.0と余計な.0がついていることが判明しました。この問題についてはGitHub zdavatz/spreadsheetを参照してください。結論を括ってしまえば、「MS Excelがやっていることだから、出力形式をいじるのはgemのSpreadsheetの役割ではない。」ということのようです。
だからといって、余計な.0を放っておくこともできないので、.0を取り除くチップです。
1
2
3
4
5
6
row.each do |key, value|
if value.instance_of?(Float)
i, f = value.to_i, value.to_f
row[key] = i == f ? i : f
end
end
これは
1
2
3
puts 123.0.to_i # => 123
puts 123.0.to_f # => 123.0
puts 123.0.to_i == 123.0.to_f # => true
となることを使ったものです。
全コード
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
# アップロードファイルを受け取るアクション
def collective_registration
if params['upload_file'].present? && params['upload_file'].original_filename.present?
import_excel(params['upload_file'])
...
redirect_to users_path
end
end
private
def import_excel(file)
workbook = open_spreadsheet(file)
if workbook # falseでなければインスタンスが入っている。
spreadsheet = workbook[0].extract_data # 1ページ目のシートのセルの値だけ取得する
spreadsheet_header = spreadsheet[0] # 1行目を取得
header = header_attributes
if header == spreadsheet_header
(1..(spreadsheet.length - 1)).each do |i|
ary = [header, spreadsheet[i]].transpose
row = Hash[*ary.flatten]
convert_row(row)
attr = row.slice(*updatable_attributes)
user = User.find_by(name: row['name']) || User.new
if row["destroy"] == "DELETE"
... # ユーザーを削除または論理削除のような特定ユーザーに必要な処理
end
... # 全てのユーザーに対し必要な処理
user.attributes = attr
user.save
end
else
# ファイルを間違えているか、１行目の書き方が違っている。
end
else
# ファイルの拡張子が異なる。
end
end
def open_spreadsheet(file)
if File.extname(file.original_filename) == '.xlsx'
return RubyXL::Parser.parse(file.path)
else
return false
end
end
def header_attributes
["name", "password", "destroy"]
end
def updatable_attributes
["name", "password"]
end
def convert_row row
row.each do |key, value|
if value.instance_of?(Float)
i, f = value.to_i, value.to_f
v = i == f ? i : f
row[key] = v.to_s
else
row[key] = value.to_s
end
end
end