前回は作成した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" という属性が原因です。これは使用している最終セルを示すための属性なのですが、列を動的に追加したことでこの属性値と矛盾が生じたのですね。この属性はわざわざ指定しなくても問題ないので、いっそのこと削除してしましましょう。２つの属性を削除してレポートを出力し、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に公開しておきます。参考になれば幸いです。

• https://github.com/itmammoth/ome-excel_report

それでは、ナイスなRails＆Excelライフを！

関連する記事

• 【実践】RailsでExcelレポート出力（その１）
• ViewPager + TabLayout + AdMob コード例
• 【実践】Railsにて検索結果をそのままCSV出力する（やや手抜きで）
• 【Excel@Mac】ファイルをインポートする際にデータ形式のプレビューがスクロールできない
• Android StudioからAndroid-PullToRefreshを使用する