みんな大好き、Excelの話題です。
Excelは万能です。本来表計算ソフトであるはずのExcelですが、プロジェクトのドキュメントがExcelなのはもちろん、帳票もExcel, 挙げ句スクリーンショットもExcelに貼付けられて送られてくるなど、ありとあらゆる場面で活用されています。本当やめてほしいです。
これだけ広く使われていると、お客様からExcelで読めるようなデータを出力してほしい! という要望もあるかと思います。業務システムの場合、Excelでのデータ出力は必須ではないか、とさえ思われます。
今回はExcelで開けるファイルを出力してほしいと言われた場合、Railsではどのように実装すればいいのか、について考えてみます。
CSVデータを出力する
カラフルな表を出力して欲しいとかそういうのではなく、単にExcelでデータを見たいだけかもしれません。
そういったケースでは、CSV出力で十分です。
今回は簡単な在庫管理システムを作っているとして、特定の商品の在庫推移をcsv出力してみます。
画面はとてもシンプル。以下のような商品一覧があって、在庫情報ダウンロードボタンを押すと、その商品の在庫情報がCSVで取得可能です。
DBモデルは以下の図の通り。productsには商品情報が、product_stocksには何月何日時点の特定の商品の在庫数が保存されているとします。
最終的に出力したいのは、下図の通り。例えば3月1日から3月31日までの在庫情報一覧を出力した場合、csvは以下のようになります。
このデータを出力するための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スプレッドシートを使う、というのも１つの手です。
最終的に出力したいのは、以下のようなexcelファイルとしましょう。
まずは上記のようなレイアウトのExcelファイルを作ります。その後、Excelの名前を付けて保存 -> XMLスプレッドシートで保存を行います。
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では警告がでることかと思います。
xls形式なのにexcelの形式じゃないよ！という警告です。それは、XMLスプレッドシートなのでしょうがないです。
しかしこういう警告が出るとなると、印象はよくありませんよね。
axlsxによる出力
凝ったExcelデータを出力したい場合は、専用のGemを利用するしかありません。
axlsxというGemを使えば、以下のようなグラフ付きの(昔のexcelフォーマットであるxls形式ではなく)xlsx形式のデータを出力できます。
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つらい
＃人気の記事
＃タグ
