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

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

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

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

  • rubyXLで開いたワークブック(workbook)の1枚目のスプレッドシート(workbook[0])を扱います。
  • 1枚目のスプレッドシート(spreadsheet)の1行目は、spreadsheet[0]で受け取ることができます。2行目を受け取るには引数に「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
    # ファイルを間違えているか、1行目の書き方が違っている。
  end
else
 # ファイルの拡張子が異なる。
end

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

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

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

先ほどのソースの(※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です。これを使ってシートの2行目(インデックスは1)から最後の行まで1行ずつ読み込むループを作ります。
  • シートのi + 1行目の値から、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を更新します。

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
        # ファイルを間違えているか、1行目の書き方が違っている。
      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
シェア
#内容発言者

Jekyll Plugin パンくずリストを作ろう

Rspecのインストールとテストの基本