railsでテンプレートからエクセル出力する方法

railsでテンプレートからエクセル出力する方法をご紹介します。

条件など

• テンプレートの.xlsxファイルをサーバーに配置してある
• ボタンが押されたらテンプレートにデータを書き込みダウンロードする
• 商品テーブル（product）の情報を書き込む
• ruby: 2.5.1
• Rails: 5.2.0

xlsxファイルの読み書きができる「rubyXL」というgemを使用します。

モデル

以下のような商品テーブルにレコードが存在するものとします。

db/migrate/*********_create_products.rb

class CreateProducts < ActiveRecord::Migration[5.2]
  def change
    create_table :products do |t|
      t.integer :product_id
      t.string :name
      t.string :size
      t.string :weight

      t.timestamps
    end
  end
end

Gemfile

rubyXLを追加します。

gem 'rubyXL'

ルータ

productsにindexを追加します。
ボタンが押された際、コントローラで商品情報一覧を取得しテンプレートxlsxに書き込むようにします。

config/routes.rb

resources :products, only: %i[index new create edit update destroy]

ビュー

エクセル出力するためのボタンを配置します。

app/views/home/index.html.erb

<%= link_to 'Exel出力', products_path(format: :xlsx), data: {confirm: "Exel出力しますか？"}, class: 'btn btn-primary' %>

コントローラ

rubyXLの読み込みを行い、indexに以下のような処理を記述します。

app/controllers/products_controller.rb

require 'rubyXL'

class ProductsController < ApplicationController
  def index
    @products = Product.all

    # テンプレートの読み込み
    workbook = RubyXL::Parser.parse('app/assets/template.xlsx')  # app/assetsの下にtemplate.xlsxを配置した場合
    
    # エクセルの数式を動作されるための設定
    workbook.calc_pr.full_calc_on_load = true
    workbook.calc_pr.calc_completed = true
    workbook.calc_pr.calc_on_save = true
    workbook.calc_pr.force_full_calc = true

    # 一番目のワークシート読み込み
    worksheet = workbook[0]

    # データ書き込み
    num = 1
    @products.each{|product|
      worksheet[num][0].change_contents(product.name)    # 商品名
      worksheet[num][1].change_contents(product.size)    # サイズ
      worksheet[num][2].change_contents(product.weight)  # 重さ	
      num += 1
    }

    respond_to do |format|
      format.html
      format.xlsx do
       send_data workbook.stream.read,
         filename: "Product.xlsx".encode(Encoding::Windows_31J)
      end
    end
  ensure
    workbook.stream.close  # streamを閉じる
  end
end

MIME Typeの追加

xlsxを追加します。

config/initializers/mime_types.rb

Mime::Type.register "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", :xlsx

参考

タイプの一覧は以下で確認できます。
https://docs.microsoft.com/ja-jp/previous-versions/office/office-2007-resource-kit/ee309278(v=office.12)

エクセルテンプレート

Excelブック(*.xlsx)形式でテンプレートを作成します。
このテンプレートはサーバー側にアップロードしてプログラムで読み込むようにします。
（詳細はコントローラのソースを参照）

今回はデータシートと商品情報シートの2つを作成し、データシートに商品データを書き込む形式とします。

出力結果

「Exel出力」ボタンを押すと以下のようなxlsxファイルがダウンロードされます。
テンプレートに対して商品情報が書き込まれていることがわかります。

各種エラーとその対応

RubyXL::Parser.parse()でエラー

以下のようなエラーが出力される。

WARNING: RubyXL::WorkbookRoot is not aware what to do with RubyXL::GenericStorageObject

原因

テンプレートファイルのフォーマット違い。
拡張子が*.xlsxには２通りある。

• NGケース：Strict Open XMLスプレッドシート(*.xlsx)
• OKケース：Excelブック(*.xlsx)

テンプレートファイル読み込みに失敗

以下のようなエラーが出力される。

XLSX file format error: File assets/template.xlsx not found

原因

テンプレートファイルへのパス指定がおかしい。

以下の２通りのいずれかの設定を行う。

1.appからのパス
RubyXL::Parser.parse(‘app/assets/template.xlsx’)
2.フルパス
RubyXL::Parser.parse(‘/home/user/projectName/app/assets/template.xlsx’)

エクセルファイル出力後にセルの参照などがうまく動作しない

原因

設定が不足している。

以下のようなコードを記述する必要がある。

workbook = RubyXL::Parser.parse(workbook_path)
workbook.calc_pr.full_calc_on_load = true
workbook.calc_pr.calc_completed = true
workbook.calc_pr.calc_on_save = true
workbook.calc_pr.force_full_calc = true

テンプレートに値を設定する際にエラー

以下のようなエラーが出力される。

undefined method `change_contents’ for nil:NilClass

原因

テンプレートファイルの対象セルに文字列などが未設定。

テンプレートファイルの対象セルに、任意の文字列などを設定しておけばOK。
Ruby on Rails
Ruby on Rails