RailsでExcel出力しないといけなくなった時の対処法


2014年 04月 07日

みんな大好き、Excelの話題です。

Excelは万能です。本来表計算ソフトであるはずのExcelですが、プロジェクトのドキュメントがExcelなのはもちろん、帳票もExcel, 挙げ句スクリーンショットもExcelに貼付けられて送られてくるなど、ありとあらゆる場面で活用されています。本当やめてほしいです。

これだけ広く使われていると、お客様からExcelで読めるようなデータを出力してほしい! という要望もあるかと思います。業務システムの場合、Excelでのデータ出力は必須ではないか、とさえ思われます。

今回はExcelで開けるファイルを出力してほしいと言われた場合、Railsではどのように実装すればいいのか、について考えてみます。

CSVデータを出力する

カラフルな表を出力して欲しいとかそういうのではなく、単にExcelでデータを見たいだけかもしれません。
そういったケースでは、CSV出力で十分です。

今回は簡単な在庫管理システムを作っているとして、特定の商品の在庫推移をcsv出力してみます。
画面はとてもシンプル。以下のような商品一覧があって、在庫情報ダウンロードボタンを押すと、その商品の在庫情報がCSVで取得可能です。

01_7ef6cc9b-d399-c4af-dd3e-509cc8afb12c.png

DBモデルは以下の図の通り。productsには商品情報が、product_stocksには何月何日時点の特定の商品の在庫数が保存されているとします。

02_d30ac4a5-bc3e-b458-8627-8aa1f373b64a.png

最終的に出力したいのは、下図の通り。例えば3月1日から3月31日までの在庫情報一覧を出力した場合、csvは以下のようになります。

03_7ae0d5db-10b1-8449-51d3-d2cd7f63ebd6.png

このデータを出力するためのrailsコードは、以下のようになります。

class ProductsController < ApplicationController
require ‘csv’

在庫情報ダウンロードボタン押下時に呼び出される

class ProductsController < ApplicationController
  require 'csv'

  # 在庫情報ダウンロードボタン押下時に呼び出される
  def download
    @date_from = Date.new(2014,3,1) # 日時は仮
    @date_to = Date.new(2014,3,31)
    @product = Product.find(params[:id])
    @stocks = ProductStock
      .where(product_id: @product.id)
      .where(date: @date_from..@date_to)
      .order(:date)

    header = ['日時', '商品コード', '在庫数']
    generated_csv = CSV.generate(row_sep: "\r\n") do |csv|
      csv << header
      @stocks.each do |stock|
        csv << [stock.date, @product.sku, stock.quantity]
      end
    end

    send_data generated_csv.encode(Encoding::CP932, invalid: :replace, undef: :replace),
      filename: 'zaiko.csv',
      type: 'text/csv; charset=shift_jis'
  end
end

重要なのは、CSV出力したデータをCP932でエンコードするという点です。
UTF-8で出力しないように。Excelで開けません。

念のためですが、上記はサンプルコードなのでcontroller内にすべてのロジックを書いてます。実際のプロダクトコードではこのような実装はしないように。(処理はmodelの中に書く事!)

XMLスプレッドシートによる出力

「CSV出力じゃない! テーブルとか色付きで表示してほしい。」との要望があった場合。困りました。
excelをrubyから扱うのはなんとなくめんどくさそう。できるだけ手を抜きたい。

そんなときはexcelのXMLスプレッドシートを使う、というのも1つの手です。

最終的に出力したいのは、以下のようなexcelファイルとしましょう。

04_d7448add-2f9d-70d8-ce0d-3cd76ccb657f.png

まずは上記のようなレイアウトのExcelファイルを作ります。その後、Excelの名前を付けて保存 -> XMLスプレッドシートで保存を行います。

05_8903e73e-afb4-b0f1-516f-ff0c61719fc0.png

XMLスプレッドシートは、名前の通りExcelデータがxml形式で保存されています。xmlだったら、railsで簡単に扱えますよね。

作成したxmlスプレッドシートを拡張子xls.erbにしてapp/views/以下に配置します。例えば、app/views/products/download.xls.erb 等に配置します。そして、このxmlファイルに必要な値を埋め込んでいきます。

生成されたXMLは結構長いですが、構造が単純なのでどこに値を埋め込んでいけばいいかはすぐ分かるはずです。例えば以下のようにして商品在庫数情報を指定したカラムに埋め込んでいきます。

<Row ss:Index="8">
 <Cell ss:Index="2" ss:StyleID="s38"><PhoneticText
   xmlns="urn:schemas-microsoft-com:office:excel">ニチジ</PhoneticText><Data
   ss:Type="String">日時</Data></Cell>
 <Cell ss:StyleID="s105"><PhoneticText
   xmlns="urn:schemas-microsoft-com:office:excel">ショウヒン</PhoneticText><Data
   ss:Type="String">商品コード</Data></Cell>
 <Cell ss:StyleID="s105"><PhoneticText
   xmlns="urn:schemas-microsoft-com:office:excel">ザイコスウ</PhoneticText><Data
   ss:Type="String">在庫数</Data></Cell>
</Row>
<%= @stocks.each do |stock| %>
<Row>
 <Cell ss:Index="2" ss:StyleID="s106"><Data ss:Type="DateTime"><%= stock.date %></Data></Cell>
 <Cell ss:StyleID="s107"><Data ss:Type="String"><%= @product.sku %></Data></Cell>
 <Cell ss:StyleID="s107"><Data ss:Type="Number"><%= stock.quantity %></Data></Cell>
</Row>
<% end %>

あとはこのファイルをrenderしてあげればよいだけです。

class ProductsController < ApplicationController
  def download
    response.headers["Content-Type"] = "application/excel"
    response.headers["Content-Disposition"] = "attachment; filename=\"在庫.xls\""
    @date_from = Date.new(2014,3,1)
    @date_to = Date.new(2014,3,31)
    @product = Product.find(params[:id])
    @stocks = ProductStock
      .where(product_id: @product.id)
      .where(date: @date_from..@date_to)
      .order(:date)
    render 'download.xls.erb'
  end
end

しかしこの方法ではいろいろと問題があります。

まず、凝ったものはXMLスプレッドシートでは作成できません。表は一部点線にしてほしいとか、グラフを出力する等はできません。

そういった制約よりも問題なのは、ダウンロード後にwindowsのexcelでは警告がでることかと思います。

06_1fa4548b-3026-83b4-86df-4948cdf81612.png

xls形式なのにexcelの形式じゃないよ!という警告です。それは、XMLスプレッドシートなのでしょうがないです。
しかしこういう警告が出るとなると、印象はよくありませんよね。

axlsxによる出力

凝ったExcelデータを出力したい場合は、専用のGemを利用するしかありません。
axlsxというGemを使えば、以下のようなグラフ付きの(昔のexcelフォーマットであるxls形式ではなく)xlsx形式のデータを出力できます。

07_835e90a8-9869-31a7-2991-1c623670a9ad.png

Windowsでの警告はでませんし、凝ったレイアウトも作成できます。すばらしい。

と思いきや、扱いはかなりめんどくさいです。
rubyコードで上記レイアウトを組み立てていくのは至難の業です。

例えば、上図のexcelファイルを出力するためのコードは以下のようになります。

def download
  @date_from = Date.new(2014,3,1)
  @date_to = Date.new(2014,3,31)
  @product = Product.find(params[:id])
  @stocks = ProductStock
    .where(product_id: @product.id)
    .where(date: @date_from..@date_to)
    .order(:date)

  package = Axlsx::Package.new
  workbook = package.workbook
  workbook.styles.fonts.first.name = 'MS Pゴシック'
  workbook.styles do |style|
    title_cell = style.add_style sz: 16
    table_header_cell = style.add_style bg_color: '222222',
                                        fg_color: 'FFFFFF',
                                        border: { style: :thin, color: '00' }
    table_cell = style.add_style border: { style: :thin, color: '00' }, 
                                               alignment: { horizontal: :left }

    workbook.add_worksheet(name: '在庫一覧') do |sheet|
      sheet.add_row ["#{@product.name} 在庫推移"], style: title_cell
      sheet.column_info.first.width = 5 # 1列目の幅は小さく

      sheet.add_row []

      product_head_table_styles = [nil, table_header_cell, table_cell, table_cell, table_cell]
      sheet.add_row ['', '商品名', @product.name, '', ''], style: product_head_table_styles
      sheet.add_row ['', '商品コード', @product.sku, '', ''], style: product_head_table_styles
      sheet.add_row ['', '在庫数', @stocks.last.quantity, '', ''], style: product_head_table_styles

      sheet.merge_cells("C3:E3")
      sheet.merge_cells("C4:E4")
      sheet.merge_cells("C5:E5")

      sheet.add_row []
      sheet.add_row []

      sheet.add_row ['', '日時', '商品コード', '在庫数'],
        style: [nil, table_header_cell,table_header_cell,table_header_cell]
      @stocks.each do |stock|
        sheet.add_row ['', stock.date.to_s, @product.sku, stock.quantity],
          style: [nil, table_cell, table_cell, table_cell]
      end
     sheet.add_chart(
       Axlsx::LineChart, title: "#{@product.name}在庫推移", rotX: 30, rotY: 20) do |chart|
        chart.start_at 6, 3
        chart.end_at 12, 20
        chart.add_series data: sheet["D9:D#{8 + @stocks.count}"],
                        #  labels: sheet["B9:B#{8 + @stocks.count}"], # 横軸を指定できる
                         title: '在庫数',
                         color: '00FF00'
        chart.catAxis.title = '日時'
      end
    end
  end

  begin
    file = Tempfile.new('zaiko.xlsx', "#{Rails.root.to_s}/tmp/")
    package.serialize file.path
    send_file file.path,
      filename: "posts.xlsx",
      type:"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
  ensure
    file.close
  end
end

これは相当辛い作業です。頭の中でexcelのセルを思い浮かべながら、ここはスタイルがこうなる、セルはここをマージする等を考えながら作業しなければなりません。

axlsxを使えば、大抵のExcel出力は行えるかと思います。どんなことができるのか、詳しくは https://github.com/randym/axlsx のドキュメントを参照してください。

まとめ

Excelつらい