前回は作成したExcelがダウンロードできるところまで実装しました。今回はそのExcelの内容を動的に変更して出力します。
目次
Viewの編集
とりあえず、ダウンロードしたExcelを見てみましょう。(ダウンロード ※拡張子はxmlに変更してください)
各値はこのExcelを作ったとき入力した固定値のままです。というわけで、これらの値を動的な値にしていきましょう。
行(日付)を動的に
まずは、日付を動的に出力することにしましょう。app/views/report/output.xls.erb
を開きます。そして以下のような箇所を探し出しましょう。
📄output.xls.erb
... <Row ss:Index="3" ss:AutoFitHeight="0" ss:StyleID="s65"> <Cell ss:Index="2" ss:StyleID="s66"><PhoneticText xmlns="urn:schemas-microsoft-com:office:excel">ヒヅケ</PhoneticText><Data ss:Type="String">日付</Data></Cell> <Cell ss:StyleID="s67"><Data ss:Type="String">アイスクリーム</Data></Cell> <Cell ss:StyleID="s67"><Data ss:Type="String">ガム</Data></Cell> <Cell ss:StyleID="s67"><Data ss:Type="String">弁当</Data></Cell> <Cell ss:StyleID="s67"><Data ss:Type="String">おにぎり</Data></Cell> <Cell ss:StyleID="s67"><Data ss:Type="String">お菓子</Data></Cell> <Cell ss:StyleID="s67"><Data ss:Type="String">雑誌</Data></Cell> <Cell ss:StyleID="s67"><Data ss:Type="String">タバコ</Data></Cell> <Cell ss:StyleID="s67"><PhoneticText xmlns="urn:schemas-microsoft-com:office:excel">ニチケイ</PhoneticText><Data ss:Type="String">日計</Data></Cell> </Row> <Row ss:AutoFitHeight="0"> <Cell ss:Index="2" ss:StyleID="s68"><Data ss:Type="DateTime">2014-02-01T00:00:00.000</Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number">1000</Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number">2000</Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number">3000</Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number">4000</Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number">5000</Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number">6000</Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number">7000</Data></Cell> <Cell ss:StyleID="s70" ss:Formula="=SUM(RC[-7]:RC[-1])"><Data ss:Type="Number">28000</Data></Cell> </Row> <Row ss:AutoFitHeight="0"> <Cell ss:Index="2" ss:StyleID="s68"><Data ss:Type="DateTime">2014-02-02T00:00:00.000</Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number">1000</Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number">2000</Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number">3000</Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number">4000</Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number">5000</Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number">6000</Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number">7000</Data></Cell> <Cell ss:StyleID="s70" ss:Formula="=SUM(RC[-7]:RC[-1])"><Data ss:Type="Number">28000</Data></Cell> </Row> ...
日付が出力され、その後に各金額(1000, 2000, 3000…)が出力されているブロックがあるはずです。これが2/1から2/28まで繰り返されています。まずはこれを以下のように変更してみましょう。
📄output.xls.erb
... <Row ss:Index="3" ss:AutoFitHeight="0" ss:StyleID="s65"> <Cell ss:Index="2" ss:StyleID="s66"><PhoneticText xmlns="urn:schemas-microsoft-com:office:excel">ヒヅケ</PhoneticText><Data ss:Type="String">日付</Data></Cell> <Cell ss:StyleID="s67"><Data ss:Type="String">アイスクリーム</Data></Cell> <Cell ss:StyleID="s67"><Data ss:Type="String">ガム</Data></Cell> <Cell ss:StyleID="s67"><Data ss:Type="String">弁当</Data></Cell> <Cell ss:StyleID="s67"><Data ss:Type="String">おにぎり</Data></Cell> <Cell ss:StyleID="s67"><Data ss:Type="String">お菓子</Data></Cell> <Cell ss:StyleID="s67"><Data ss:Type="String">雑誌</Data></Cell> <Cell ss:StyleID="s67"><Data ss:Type="String">タバコ</Data></Cell> <Cell ss:StyleID="s67"><PhoneticText xmlns="urn:schemas-microsoft-com:office:excel">ニチケイ</PhoneticText><Data ss:Type="String">日計</Data></Cell> </Row> <% ("2014-02-01".."2014-02-28").each do |date| %> <Row ss:AutoFitHeight="0"> <Cell ss:Index="2" ss:StyleID="s68"><Data ss:Type="DateTime"><%= date %>T00:00:00.000</Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number"></Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number"></Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number"></Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number"></Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number"></Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number"></Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number"></Data></Cell> <Cell ss:StyleID="s70" ss:Formula="=SUM(RC[-7]:RC[-1])"><Data ss:Type="Number"></Data></Cell> </Row> <% end %> <Row ss:AutoFitHeight="0"> <Cell ss:Index="2" ss:StyleID="s68"/> <Cell ss:StyleID="s70"/> <Cell ss:StyleID="s70"/> <Cell ss:StyleID="s70"/> <Cell ss:StyleID="s70"/> <Cell ss:StyleID="s70"/> <Cell ss:StyleID="s70"/> <Cell ss:StyleID="s70"/> <Cell ss:StyleID="s70"/> </Row> ...
まずは動くかどうかの確認なので、erbの中にハードコーディングしていますが、日付をループさせて行を出力するようにしました。また、金額も空に変更しました。この状態でレポートを出力してExcelで開くと、金額が全てゼロの表が開くはずです。
列(商品)を動的に
一旦、列(商品)を動的に出力する作業へ移りましょう。Excel(xml)とはいえ結局のところは erb で出力してるだけです。何も恐れることはありません。output.xls.erb
の以下のようなコードを探し出します。
📄output.xls.erb
... <Row ss:Index="3" ss:AutoFitHeight="0" ss:StyleID="s65"> <Cell ss:Index="2" ss:StyleID="s66"><PhoneticText xmlns="urn:schemas-microsoft-com:office:excel">ヒヅケ</PhoneticText><Data ss:Type="String">日付</Data></Cell> <Cell ss:StyleID="s67"><Data ss:Type="String">アイスクリーム</Data></Cell> <Cell ss:StyleID="s67"><Data ss:Type="String">ガム</Data></Cell> <Cell ss:StyleID="s67"><Data ss:Type="String">弁当</Data></Cell> <Cell ss:StyleID="s67"><Data ss:Type="String">おにぎり</Data></Cell> <Cell ss:StyleID="s67"><Data ss:Type="String">お菓子</Data></Cell> <Cell ss:StyleID="s67"><Data ss:Type="String">雑誌</Data></Cell> <Cell ss:StyleID="s67"><Data ss:Type="String">タバコ</Data></Cell> <Cell ss:StyleID="s67"><PhoneticText xmlns="urn:schemas-microsoft-com:office:excel">ニチケイ</PhoneticText><Data ss:Type="String">日計</Data></Cell> </Row> <% ("2014-02-01".."2014-02-28").each do |date| %> <Row ss:AutoFitHeight="0"> <Cell ss:Index="2" ss:StyleID="s68"><Data ss:Type="DateTime"><%= date %>T00:00:00.000</Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number"></Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number"></Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number"></Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number"></Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number"></Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number"></Data></Cell> <Cell ss:StyleID="s70"><Data ss:Type="Number"></Data></Cell> <Cell ss:StyleID="s70" ss:Formula="=SUM(RC[-7]:RC[-1])"><Data ss:Type="Number"></Data></Cell> </Row> <% end %> ...
今、固定値で出力されている商品名をrubyコードに置き換えます。とりあえず、全商品名を横にズラッと並べてみましょう。以下のように変更します。
📄output.xls.erb
... <Row ss:Index="3" ss:AutoFitHeight="0" ss:StyleID="s65"> <Cell ss:Index="2" ss:StyleID="s66"><PhoneticText xmlns="urn:schemas-microsoft-com:office:excel">ヒヅケ</PhoneticText><Data ss:Type="String">日付</Data></Cell> <% Product.all.each do |p| %> <Cell ss:StyleID="s67"><Data ss:Type="String"><%= p.name %></Data></Cell> <% end %> <Cell ss:StyleID="s67"><PhoneticText xmlns="urn:schemas-microsoft-com:office:excel">ニチケイ</PhoneticText><Data ss:Type="String">日計</Data></Cell> </Row> <% ("2014-02-01".."2014-02-28").each do |date| %> <Row ss:AutoFitHeight="0"> <Cell ss:Index="2" ss:StyleID="s68"><Data ss:Type="DateTime"><%= date %>T00:00:00.000</Data></Cell> <% Product.count.times do %> <Cell ss:StyleID="s70"><Data ss:Type="Number"></Data></Cell> <% end %> <Cell ss:StyleID="s70" ss:Formula="=SUM(RC[-7]:RC[-1])"><Data ss:Type="Number"></Data></Cell> </Row> ...
ヘッダー行だけでなく、金額行も変更するのを忘れずに。変更したら実際に出力してExcelで開いてみましょう。マメに確認しておくのがポイントです。
では試しに、商品を追加してレポートを出力してみましょうか。http://localhost:3000/products にアクセスし、何か商品を追加してみましょう。追加が終わったら http://localhost:3000/report/ に戻り、レポートを出力してExcelで開いてみます。ちゃんと列は増えているでしょうか??
な、なんと。 エラーが表示されて開くことができません・・・。 しかも筆者の環境では、記載されている場所にログファイルは作成されていませんでした! あなおそろしやマイクロソフトめ。
色々試行錯誤のすえ、原因を突き止めることができました。原因の箇所は以下にあります。
📄output.xls.erb
... <Worksheet ss:Name="月間売上"> <Table ss:ExpandedColumnCount="10" ss:ExpandedRowCount="35" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="81.75" ss:DefaultRowHeight="13.5"> <Column ss:AutoFitWidth="0" ss:Width="5.25"/> <Column ss:StyleID="s62" ss:Width="61.5"/> <Row ss:AutoFitHeight="0" ss:Height="21"> <Cell ss:Index="2" ss:StyleID="s63"><PhoneticText xmlns="urn:schemas-microsoft-com:office:excel">ゲツカンウリアゲ</PhoneticText><Data ss:Type="String">月間売上</Data></Cell> </Row> ...
ss:ExpandedColumnCount="10"
と ss:ExpandedRowCount="35"
という属性が原因です。これは使用している最終セルを示すための属性なのですが、列を動的に追加したことでこの属性値と矛盾が生じたのですね。この属性はわざわざ指定しなくても問題ないので、いっそのこと削除してしましましょう。2つの属性を削除してレポートを出力し、Excelで開くと・・・
先程追加した商品列が出力されています。上手く行きました。
ちゃんと実装する
正しくレポートを出力する道筋は見えたので、ちゃんと実装しておきましょう。まずは、この月間売上専用のモデルを作りましょう。app/models/report.rb
を作成します。
📄report.rb
class Report def initialize(year, month) @start_date = Date.new(year, month, 1) @end_date = @start_date + 1.month - 1.day end def dates @start_date..@end_date end def products @products ||= Product.all.to_a end def sales_amount_on(date, product) Sale.where(sales_date: date) .where(product_id: product.id) .sum(:amount) end end
(sales_amount_onメソッドで集計SQLを発行するので、『日付 × 商品数』回だけSQLを発行してしまうことになります。今回はサンプルなのでこのままにしておきますが、必要に応じ書き換えてください。)
続いて、コントローラです。
📄report_controller.rb
class ReportController < ApplicationController def index end def output @report = Report.new(params[:year].to_i, params[:month].to_i) end end
そして最後にビューを編集します。ポイントだけ抜粋しています。
📄output.xls.erb
... <Table x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="81.75" ss:DefaultRowHeight="13.5"> <Column ss:AutoFitWidth="0" ss:Width="5.25"/> <Column ss:StyleID="s62" ss:Width="61.5"/> <Row ss:AutoFitHeight="0" ss:Height="21"> <Cell ss:Index="2" ss:StyleID="s63"><PhoneticText xmlns="urn:schemas-microsoft-com:office:excel">ゲツカンウリアゲ</PhoneticText><Data ss:Type="String">月間売上</Data></Cell> </Row> <!-- 商品名行 --> <Row ss:Index="3" ss:AutoFitHeight="0" ss:StyleID="s65"> <Cell ss:Index="2" ss:StyleID="s66"><PhoneticText xmlns="urn:schemas-microsoft-com:office:excel">ヒヅケ</PhoneticText><Data ss:Type="String">日付</Data></Cell> <% @report.products.each do |p| %> <Cell ss:StyleID="s67"><Data ss:Type="String"><%= p.name %></Data></Cell> <% end %> <Cell ss:StyleID="s67"><PhoneticText xmlns="urn:schemas-microsoft-com:office:excel">ニチケイ</PhoneticText><Data ss:Type="String">日計</Data></Cell> </Row> <!-- 各日の金額行 --> <% @report.dates.each do |date| %> <Row ss:AutoFitHeight="0"> <Cell ss:Index="2" ss:StyleID="s68"><Data ss:Type="DateTime"><%= date %>T00:00:00.000</Data></Cell> <% @report.products.each do |p| %> <Cell ss:StyleID="s70"><Data ss:Type="Number"><%= @report.sales_amount_on(date, p) %></Data></Cell> <% end %> <Cell ss:StyleID="s70" ss:Formula="=SUM(RC[-7]:RC[-1])"><Data ss:Type="Number"></Data></Cell> </Row> <% end %> <!-- 31日までない月は空行を出力する --> <% (31 - @report.dates.count).times do %> <Row ss:AutoFitHeight="0"> <Cell ss:Index="2" ss:StyleID="s68"/> <% (@report.products.count + 1).times do |p| %> <Cell ss:StyleID="s70"/> <% end %> </Row> <% end %> <!-- 合計行 --> <Row ss:AutoFitHeight="0"> <Cell ss:Index="2" ss:StyleID="s71"><PhoneticText xmlns="urn:schemas-microsoft-com:office:excel">カモクケイ</PhoneticText><Data ss:Type="String">科目計</Data></Cell> <% @report.products.count.times do %> <Cell ss:StyleID="s70" ss:Formula="=SUM(R[-31]C:R[-1]C)"><Data ss:Type="Number"></Data></Cell> <% end %> <Cell ss:StyleID="s72" ss:Formula="=SUM(R[-31]C:R[-1]C)"><Data ss:Type="Number"></Data></Cell> </Row> </Table> ...
@reportから必要なデータを取得しつつ出力しています。
変更を保存したら、レポートを出力し、Excelで開いて確認します。
ばっちり!! 何だか一回り成長した気分。口笛でも吹くか。
完成したソースは、GitHubに公開しておきます。参考になれば幸いです。
それでは、ナイスなRails&Excelライフを!
関連する記事
- 【実践】RailsでExcelレポート出力(その1)
- ViewPager + TabLayout + AdMob コード例
- 【実践】Railsにて検索結果をそのままCSV出力する(やや手抜きで)
- 【Excel@Mac】ファイルをインポートする際にデータ形式のプレビューがスクロールできない
- Android StudioからAndroid-PullToRefreshを使用する