Posts roo - Excelファイルの読み込み
Post
Cancel

roo - Excelファイルの読み込み

今回使用する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テーブルに登録あるいは修正します。

  1. アップロードしたスプレッドシート(Excel(xlsx)ファイル、OpenOffice / LibreOfficeファイル、CSVファイルのいずれか)を受け取り、 Roo::Spreadsheet.open メソッドで開きます。
  2. ファイルの1行目の値をチェックし、その値をキーとして2行目以降の値を持つハッシュを作ります。そのハッシュを使って1件のデータを処理します。
  3. データベースに登録する値だけを持つハッシュを作り 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行目にその列の項目名が記述してあります。その項目名が正しいものであるかをチェックするための配列を作ります。正しければ1行目の項目名と下記の配列は一致しています。

1
2
3
def header_attributes
  ["name", "password", "destroy"]
end

また、シートの2行目以降を読み込む時、この配列に一致する列の項目名をキーとし、対応するセルの値をハッシュの値にして利用できるようにします。例えば2行目を読み込んだ時には row[“name”] => “aaa” や row[“password”] => “aaa123” のようなハッシュを作ります。

3列目の destroy は、データを登録したいテーブル(usersテーブル)にそのカラムがないものです。usersテーブルにあるカラム名からなる配列も作ります。

1
2
3
def updatable_attributes
  ["name", "password"]
end

これを利用して、登録する項目のキーとその値だけを持つハッシュを作ります。

スプレッドシートの1行目を読み込む

  • rooで開いたインスタンス( spreadsheet )の row メソッドでシートの1行分を受け取ることができます。1行目を受け取るには引数に「1」を指定します。 spreadsheet.sheet(0).row(1) 、または sheet を省略して spreadsheet.row(1) のようにも書けます。
  • sheet を省略した場合は default_sheet が使われ、初期値は1枚目のシートですが、それを変更するときには spreadsheet.default_sheet = spreadsheet.sheets[3] とすることで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
    # ファイルを間違えているか、1行目の書き方が違っている。
  end
else
 # ファイルの拡張子が異なる。
end

4行目でアップロードされてきたスプレッドシートの1行目が header_attributes と同じかどうかを判定しています。

1件分のユーザー情報を登録する

スプレッドシートの2行目以降をループで読み込み、シートの1行分のセルの値をハッシュに格納する

先ほどのソースの(※1)の部分のコードです。

1
2
3
4
5
(2..spreadsheet.last_row).each do |i|
    row = Hash[[header, spreadsheet.row(i)].transpose]
    ...     # (※2)

end
  • スプレッドシート最後の行番号が .last_row です。これを使ってシートの2行目から最後の行まで1行ずつ読み込むループを作ります。
  • シートの i 行目の値から、1行目の値をキーとするハッシュにします。それが Hash[[header, spreadsheet.row(i)].transpose] です(このハッシュの作り方についてはRuby 配列からHashを作る を参照)。

row[‘name’] のようにして現在の行の各セルの値を取り出すことができるようになりましたので、(※2)でそのハッシュを使って必要な処理を施すことができます。

登録できるデータだけを持つハッシュを作り、モデルの属性にセットする

ハッシュ row のキーには「name」「password」「destroy」の3つがありますが、登録するusersテーブルのカラム名と同じなのは「name」「password」の2つだけです。ハッシュ 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
        # ファイルを間違えているか、1行目の書き方が違っている。
      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
シェア
#内容発言者

2.変数

slice - HashのActiveSupport 拡張