RubyXLで最初のシート（テンプレート）から複数のシートを作成する #Ruby

やりたかったこと

RubyXLを使ってテンプレートとなるエクセルファイルを開き、一つ目のシートをテンプレートとして複数のシートを作成するというものです。具体的にはRails製の業務アプリケーションで住所や顧客名などがデータベースに入っていて、それらの住所を使った送り状が１枚１枚のシートとなってダウンロードできるというものになります。

コントローラに書いたこと

@pages は各送り状のシートに必要な情報のリストです。例えばcompany_nameなどが入っています。excel_renderというヘルパーメソッドの第一引数にテンプレートとなるシート、第二引数に新しく作ったシートを渡しています。

    path = Rails.root.join('tmpl', 'template.xlsx').to_path
    sheet_names = {}
    @wb = RubyXL::Parser.parse(path)
    @pages.each_with_index do |p, i|
      index = i + 1
      @page = p
      sheet_name = @page.company_name
      sheet_names[sheet_name] = 0 if sheet_names[sheet_name].blank?
      sheet_names[sheet_name] += 1
      sheet_name = "#{sheet_name}_#{sheet_names[sheet_name]}" if sheet_names[sheet_name] > 1
      @wb.add_worksheet(sheet_name)
      excel_render(@wb.worksheets[0], @wb.worksheets[index])
    end
    @wb.book_views[0].active_tab = 1
    @wb.worksheets[0].state = 'hidden'
    stream = @wb.stream.string
    filename ="okurijo.xlsx"
    send_data(
      stream,
      filename: filename,
      disposition: 'attachment'
    )

ヘルパーメソッドに書いたこと

こちらのコードは特に以下の２名の記事を参考にさせていただきました。（両名に大変感謝しています！）
https://qiita.com/m-kubo/items/6b5beaaf2a59c0d75bcc
https://qiita.com/t_fujise/items/13a5ed984c359e222b23

require 'rubyXL/convenience_methods/cell'
require 'rubyXL/convenience_methods/color'
require 'rubyXL/convenience_methods/font'
require 'rubyXL/convenience_methods/workbook'
require 'rubyXL/convenience_methods/worksheet'
module ExcelHelper
  def excel_render(template, worksheet)
    @template = template
    @worksheet = worksheet
    max_col_num = 0
    @template.each_with_index do |row, row_num|
      row&.cells&.each_with_index do |cell, col_num|
        max_col_num = [max_col_num, col_num].max
        next if cell.nil?
        cell_render(cell, row_num, col_num)
      end
      row_height_auto(row_num)
    end
    (max_col_num + 1).times do |col_num|
      col_width_auto(col_num)
    end
    merge_cells
    copy_related_files
    copy_settings
  end

  private

  def merge_cells
    @template.merged_cells.each do |merged_cell|
      c = merged_cell.ref.col_range
      r = merged_cell.ref.row_range
      @worksheet.merge_cells(r.first, c.first, r.last, c.last)
    end
  end

  def copy_related_files
    @worksheet.relationship_container = RubyXL::OOXMLRelationshipsFile.new
    @worksheet.relationship_container.related_files = {}
    template_relationships = @template.relationship_container.relationships
    template_relationships.each do |relationship|
      @worksheet.relationship_container.relationships << relationship
    end
    @worksheet.drawing         = @template.drawing
  end

  def copy_settings
    @worksheet.sheet_views     = @template.sheet_views
    @worksheet.sheet_format_pr = @template.sheet_format_pr
    @worksheet.phonetic_pr     = @template.phonetic_pr
    @worksheet.print_options   = @template.print_options
    @worksheet.page_margins    = @template.page_margins
    @worksheet.page_setup      = @template.page_setup
  end

  def content_eval(content)
    return content if content.nil?
    return content if content.is_a?(Integer)
    c =
      if eval_regex.match?(content)
        view_context.instance_eval(%("#{content}"), __FILE__, __LINE__)
      else
        content
      end
    c.gsub(/\R/, "\n")
  end

  def eval_regex
    /\A\#\{@page.evalwhitelist\}\Z/
  end

  def cell_render(template_cell, row_num, col_num)
    cell = @worksheet.add_cell(row_num, col_num, content_eval(template_cell.value))
    cell.style_index = template_cell.style_index
  end

  def row_height_auto(row_num)
    @worksheet.change_row_height(row_num, @template.get_row_height(row_num))
  end

  def col_width_auto(col_num)
    @worksheet.change_column_width_raw(col_num, @template.get_column_width_raw(col_num))
  end
end

このコードを参考にしていただく前の注意点その１

多分コピペでは全然動かないと思います。使用するテンプレートによってはエラーが出たり、不具合があると思います。私が使ったテンプレートはお客さん情報が入っているので公開できませんが、だいたい以下のようなものです。

セルに入っているデータは文字か数字。
式や凝ったスタイルなどはない。
一つだけ画像（会社のロゴ）が入っている。

1,2に関してはcontent_eval（とその中で使っているeval_regex）の中身を適宜編集すればいいと思います。

このコードを参考にしていただく前の注意点その２

本当はヘルパーメソッドにインスタンス変数は作らない方がいいと思います。代わりにこのモジュールにクラスを作りexcel_renderメソッドでインスタンス化すればいいと思います。

追記　２０２１/03/16

上記コードでは計算式がコピーできないので追加の記事を書いてます。
https://qiita.com/DYO/items/ed76a26f48cc6b78714b

全体の流れ

@pagesを作り、各シートに必要な情報を格納しておきます。
テンプレートとなるExcelファイルを読み込みます。
@pagesをループで回してexcel_renderメソッドにテンプレートとなるシートと新しく作ったシートを渡して中身の調整をしてもらいます。
1. テンプレートの一つ一つのセルを、新しいシートにコピーしていきます。その時セルに入っている文字列にevalを使って@pageに入っている情報に差し替えています。テンプレートをユーザーによって差し替えられるようなものにはこの手法がセキュリティーホールになるかもしれません。
2. 一つ一つのセルに文字の大きさなどの参照であるstyle_indexがあるので、それも一つ一つコピーしています。
3. 列の幅をと行の高さもそれぞれループさせ、row_height_autoとcol_width_autoで一列一行設定をコピーしています。
4. マージしているデータはセルのデータとは別なのでmerge_cellsメソッドでそれらも一つ一つコピーしています。
5. 画像はworksheet.relationship_container.relationshipsとworksheet.drawingをコピーすると同じ画像を参照して同じ位置に貼り付けてくれるようです。私の使ったテンプレートにはrelationshipには画像以外にもプリンタ設定情報などがありましたので一緒にコピーしています。それ以外の情報があったら何か不具合があるかもしれません。
6. 最後にcopy_settingsでテンプレートとなるシートの設定をコピーします。
アクティブなタブを新しく作ったシートに変更して、テンプレートとなるシートを非表示にします。

最終的なコードまでに試したこと

最終的にはテンプレートの設定やセルを一つ一つコピーする手法になっています。最初は多くのサイトでも書かれているようにsheet自体のデータをクローンする方法をとりました。しかしその方法ですとエクセルを開くときにエラーになりました。重複している参照先が原因と考えました。全てを修正すればエラーは解消されると思ったのですが、結局新しいシートを作って一つ一つの情報をコピーする方がシンプルだと考えました。
新しいシートにテンプレートにある画像を再び貼り付けようとdrawing情報も新しく作ろうとしていましたが、RubyXLで画像などを扱うのはちょっとまだ難しいようで、うまくいきませんでした。今回は幸いにもテンプレートにある画像をそのまま新しいシートに貼り付けるだけですので画像への参照だけをコピーすれば実現できました。画像がテンプレートにない状態から画像を貼り付けていくのは大変そうでした。
テンプレートとなったシートは必要ないので、シートを消すこともやってみました。シートを消すにはworkbook.worksheetsが配列なのでそれをdelete_atなどで消せば消えるには消えます。しかしそのシートに何らかの参照が残っていてファイルが壊れることがありました。テンプレートとなるシートが表に出ても構わなかったので、シートを隠すという方法を選択しました。

RubyXLを使ってExcelを操作すること

非常に多くのことができるgemではありますが、簡単に扱える部分ととても難しい部分があるなという印象です。しかしそういう状況も仕方ないと思います。そもそもExcelファイルの構造からしてこれ以上ライブラリに機能を持たせること（例えばadd_worksheetメソッドがあるのにremove_worksheetがないなど）は非常に難しいというのは理解できます。そういうわけで汎用的に使えるようなラッパーコードを書くのも難しそうに思うので、適宜要件に合うコードをコツコツ書くしかないかなと思います。

RubyXLで最初のシート（テンプレート）から複数のシートを作成する