今回使用するExcelファイルのデータの読み込みを行うGemは roo です。
- rooを使って、多人数のユーザー情報を記述したエクセルファイル(など)を読み込み、複数のデータを一気に登録修正をします。
インストール
roo gem ‘roo’, ‘2.0.0beta1’ 2015/04/12時点での最新版です。一つ前の 1.13.2 にはバグがありました(excelx.rb 545行目)ので最新版を使ってください。
roo では、Excel(xlsx)ファイル、OpenOffice / LibreOfficeファイル、CSVファイルを読み込むことができます。Excel(xls)ファイルとGoogle spreadsheetファイルを扱うには、別のGem( roo-xls、roo-google)をインストールする必要があります(今回のバージョンからの変更です)。 なお、確かめてはいませんが、Google spreadsheetファイルは読み/書きができるようです。
Controller
概観
アップロードしたファイルに記述されたユーザー情報をusersテーブルに登録あるいは修正します。
- アップロードしたスプレッドシート(Excel(xlsx)ファイル、OpenOffice / LibreOfficeファイル、CSVファイルのいずれか)を受け取り、 Roo::Spreadsheet.open メソッドで開きます。
- ファイルの１行目の値をチェックし、その値をキーとして２行目以降の値を持つハッシュを作ります。そのハッシュを使って１件のデータを処理します。
- データベースに登録する値だけを持つハッシュを作り user.attributes にセットして save します。
roo でファイルを開く
標準で開けるファイルを扱います。下のメソッドではアップロードしたファイルを引数にしています。具体的には「 params[“upload_file”] 」としてファイルフィールドタグで送信されたファイルを受け取った Tempfile オブジェクトです(params についてを参照)。
1
2
3
4
5
6
7
8
9
10
11
12
def open_spreadsheet_roo(uploaded_file)
case File.extname(uploaded_file.original_filename)
when '.xlsx' then
Roo::Spreadsheet.open(uploaded_file.path, extension: :xlsx)
when '.ods' then
Roo::Spreadsheet.open(uploaded_file.path, extension: :ods)
when '.csv' then
Roo::Spreadsheet.open(uploaded_file.path, extension: :csv)
else
return false
end
end
このメソッドから返るオブジェクトはすべて共通のSpreadsheetオブジェクトなのでそれを受け取った後はファイルの種類に関係なく同じ処理を行うことができます。
表の列項目をチェックする
今回取り扱うシートの例です。
開いたシートの１行目にその列の項目名が記述してあります。その項目名が正しいものであるかをチェックするための配列を作ります。正しければ１行目の項目名と下記の配列は一致しています。
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
これを利用して、登録する項目のキーとその値だけを持つハッシュを作ります。
スプレッドシートの１行目を読み込む
- rooで開いたインスタンス( spreadsheet )の row メソッドでシートの１行分を受け取ることができます。１行目を受け取るには引数に「1」を指定します。 spreadsheet.sheet(0).row(1) 、または sheet を省略して spreadsheet.row(1) のようにも書けます。
- sheet を省略した場合は default_sheet が使われ、初期値は１枚目のシートですが、それを変更するときには spreadsheet.default_sheet = spreadsheet.sheets[3] とすることで３枚目のシートに変更できます。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
spreadsheet = open_spreadsheet(file)
if spreadsheet # falseでなければインスタンスが入っている。
spreadsheet_header = spreadsheet.row(1)
header = header_attributes
if header == spreadsheet_header
# 正しいファイルが送られてきた。
... # (※1)
else
# ファイルを間違えているか、１行目の書き方が違っている。
end
else
# ファイルの拡張子が異なる。
end
４行目でアップロードされてきたスプレッドシートの１行目が header_attributes と同じかどうかを判定しています。
１件分のユーザー情報を登録する
スプレッドシートの２行目以降をループで読み込み、シートの１行分のセルの値をハッシュに格納する
先ほどのソースの(※1)の部分のコードです。
1
2
3
4
5
(2..spreadsheet.last_row).each do |i|
row = Hash[[header, spreadsheet.row(i)].transpose]
... # (※2)
end
- スプレッドシート最後の行番号が .last_row です。これを使ってシートの２行目から最後の行まで１行ずつ読み込むループを作ります。
- シートの i 行目の値から、１行目の値をキーとするハッシュにします。それが 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を更新します。
フロート型にされてしまう
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
# アップロードファイルを受け取るアクション
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)
spreadsheet = open_spreadsheet(file)
if spreadsheet
spreadsheet_header = spreadsheet.row(1)
header = header_attributes
if header == spreadsheet_header
(2..spreadsheet.last_row).each do |i|
row = Hash[[header, spreadsheet.row(i)].transpose]
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)
case File.extname(file.original_filename)
when '.xlsx' then
Roo::Spreadsheet.open(file.path, extension: :xlsx)
when '.ods' then
Roo::Spreadsheet.open(file.path, extension: :ods)
when '.csv' then
Roo::Spreadsheet.open(file.path, extension: :csv)
else
return false
end
end
def header_attributes
["name", "password", "destroy"]
end
def updatable_attributes
["name", "password"]
end