これは Supership株式会社 Advent Calendar 2016 14日目のエントリです。

Supership の @shao1555 です。もとはビットセラーという会社でカメラアプリの開発などをやっていましたが、nanapiさん、スケールアウトさんと合流してSupershipという会社になり1年が過ぎました(参考)。最近はCTO室に所属し、会社として技術を生かして成長していくための基盤づくりを担当しています。

今日はWebサービスの管理画面によくある「レポートのダウンロード」機能について。

大抵の管理画面では上記のように「CSVでレポートを取得する」機能がついてますが、この CSV が割と曲者で

出力時に Shift_JIS 範囲外の文字 (絵文字など) が使えない
- 何も考えないと ArgumentError: invalid byte sequence エラーが発生する
- 事前に String#encode の undef オプションを設定して〓などに置き換えることで回避
CSV を Unicode (UTF-8 とか) で出力すると、文字コードエラーは収まるが、今度は Mac 版の Excel でうまく読み込めない
- UTF16 にしてテキストファイルウイザードを使ってもらえばなんとかなるが、利用者に負担を強いるのでやりたくない
日時型を正しく扱ってもらうために、セルの書式設定をユーザー側で弄ってもらう必要がある

など、けっこう悩ましい話があります。Excel で処理したいユーザが多いのであれば、いっそ Excel ファイルを出力すればいいのでは、、、という話です。

axlsx_rails のセットアップ

アプリを Rails で作っているのであれば、 axlsx_rails という gem がおすすめです。

セットアップはいつものように Gemfile に以下を追記し、 bundle install します。

Gemfile

gem 'rubyzip', '~> 1.1.0'
gem 'axlsx', '2.1.0.pre'
gem 'axlsx_rails'

「裏でExcelを叩く」みたいな実装ではなく、Open XML Spreadsheetフォーマットを Pure Ruby 実装で力強く生成しているだけなので、ネイティブライブラリのコンパイルは不要です。Linux環境でもmacOSでも安心して動かせます。

コントローラ

ユーザの一覧を返すコントローラに Excel 形式でのダウンロード機能をつけてみましょう。

app/controllers/users_controller.rb

class UsersController < ApplicationController
  def index
    @users = User.all
    respond_to do |format|
      format.html { pagination(@users) }
      format.xlsx do
        response.headers['Content-Disposition'] = 'attachment; filename="users.xlsx"'
      end
    end
  end
end

まるで html や json を返すかのように、 xlsx フォーマットを定義するだけで準備ができてしまいます。簡単です。

なお、上記の例の通り Content-Disposition ヘッダを用意しないと、Internet Explorer など OLE に対応したブラウザで、ダウンロードされずにブラウザ内に直接 Excel ファイルが表示されてしまうことがありますので、必ず指定しましょう。

Excel 出力ビュー

あとは Excel の出力を定義するビューを用意するだけです。以下の様に書いていきます。

app/views/users/index.xlsx.axlsx

# ワークブックをつくる
workbook = xlsx_package.workbook

# ワークブックにシートを入れる
workbook.add_worksheet(name: 'ユーザ一覧') do |worksheet|
  # スタイルの定義
  styles = {
    # title: メイリオ, 16pt, 太字
    title: worksheet.styles.add_style(font_name: 'メイリオ', sz: 16, b: true),

    # header: メイリオ, 太字, 背景色は #ACCACF (最初の00は透明度)
    header: worksheet.styles.add_style(font_name: 'メイリオ', b: true, bg_color: '00ACCAF'),

    # text: メイリオ
    text: worksheet.styles.add_style(font_name: 'メイリオ'),

    # value: Arial
    text: worksheet.styles.add_style(font_name: 'Arial'),

    # hyperlink: Arial, 文字色は #0000FF, 下線
    hyperlink: worksheet.styles.add_style(font_name: 'Arial', u: true, fg_color: '000000FF'),

    # date: Arial, yyyy/mm/dd 表記
    date: worksheet.styles.add_style(font_name: 'Arial', format_code: 'yyyy/mm/dd')
  }

  # ワークシートに列を挿入する
  worksheet.add_row(
    ["ユーザ一覧: #{l(Time.now, format: :long)} 現在"],  # 列内の各セルの値を配列で
    height: 24, # セルの高さ
    style: styles[:title] # 適用するスタイル
  )

  worksheet.add_row [] # 空行

  # ヘッダ行
  worksheet.add_row(%w(ID 氏名 登録日 Twitter ログイン回数), style: styles[:header])

  # データ行
  @users.each do |user|
    worksheet.add_row do |row|
      # ID
      row.add_cell(user.id, style: styles[:value])

      # 氏名
      row.add_cell(user.name, style: styles[:text])

      # 登録日
      row.add_cell(
        Axlsx::DateTimeConverter.time_to_serial(user.created_at),
        style: styles[:date]
      )

      # Twitter へのリンク (ハイパーリンクを作成)
      twitter_cell = row.add_cell(user.twitter_id, style: styles[:hyperlink])
      worksheet.add_hyperlink(
        location: "https://twitter.com/#{user.twitter_id}",
        ref: twitter_cell
      )

      # ログイン回数
      row.add_cell(user.login_count, style: styles[:value])
    end
  end
end

このように、コントローラと分離してビューを作成することができ、ビュー内では Excel のフォーマットや書式を細やかに設定することができます。もちろん Unicode 文字列はすべて利用可能で、Windows / Mac 問わず文字化けの心配はいりません。

ユーザ入力値に絵文字やキモい顔文字が含まれるケースでも、(Excelを開くOSにフォントがインストールされている限り)問題なく再現されました

躓きやすい点は、日付型でしょうか。Time型を直接渡すことはできず、上の例のように Alxs::DateTimeConverter.time_to_serialメソッドで変換したシリアル値を入れる必要があります。

ほかにも Axlsx ライブラリではグラフやピボットテーブル、セルのバリデーション、シェイプの描画など Excel の幅広い機能を扱うこともできるですが、筆者は試していません。

興味がある方はドキュメントを見てみましょう。

File: README — Documentation for axlsx (2.0.1)

まとめ

管理画面から直接 Excel のスプレッドシートで出力されるようになったことで、インポート時の型変換が不要になったほか、クライアント様にお出しするレポートをそのまま出力することも可能になるなど、捗るようになりました。

また、Tableau などの BI ツールも Excel ファイルをネイティブに扱うことができますので、データ分析のワークフローも改善できます。 LTSV や JSON などの形式はエンジニアにとって扱いやすい一方、デスクトップでの集計、分析作業において Excel ファイルは引き続き欠かすことができません。Axlsxはその一助としてよいものです。

明日の Supership株式会社 Advent Calendar 2016 は @tanjo です。お楽しみに！

axlsx_rails を用いて Excel のスプレッドシートを生成する

axlsx_rails のセットアップ

コントローラ

Excel 出力ビュー

まとめ