Gem rubyXL を使用している際に起こったエラーとその対処方法を備忘録として残しておく

環境

  • rails: rails 5.2.2
  • rubyXL: Ver.3.4.0

rubyXL について

拡張子「xlsx」を読み書き可能なライブラリ
使用感としてはテンプレートとなる Excel に対して読み書きする場合には
問題なく使用可能
但し、テンプレートに数式やグラフが含まれている場合に行などを
追加した場合に出力された Excel ファイルが破損して起動不可になる場合が
あるため注意が必要

rubyXL 発生した障害とその対応

  1. テキストの折り返しがsheet.add_cellではできない

sheet.add_cell(col, row, 'text').change_text_wrap によって実装可能
テキストを折り返して表示してくれるようになる

  1. change_contents が使えない

version 3.4.0 以降は convenience_methods を呼び出さないと利用不可

# require が必要なメソッド
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'

もしくは以下で全て使用可

require 'rubyXL/convenience_methods'
  1. シートのコピーができない

Marshal.load なども試してみたが、上手くいかなかったので以下を参考にして対応
参考サイト

  1. シートコピー後、コピーしたシートを編集したら他のシートの内容も一緒に変わってしまう

change_contents の仕様と上記のシートコピーによって生じた現象

  • ① シートの書式等をコピー
  • ② change_contents で書式設定を保持したまま内容を変更
  • ③ 該当するコピーシートすべての内容が書き換わる という動作をしている
    => add_cell による値の挿入で対応
    ※上記の場合は書式設定等は rubyXL 側で行う必要がある
  1. 動的な配置で Excel の数式を設定する

insert_row 等で行の挿入を動的に行った場合に Excel の数式によって値の算出を行いたい
※insert_row は公式でも書かれているが扱いに慎重になるべきメソッドで、結合したセルや数式が挿入した行の下にあった場合に Excel が破損することがある

add_cellの書式を確認したところ数式は第4引数に該当するためそこに Excel の数式を代入する(SUM 関数等)

  1. グラフの作成ができない

rubyXL 側にグラフに関するメソッドは存在しないため対応不可
テンプレートにグラフを挿入することで対応

  1. 印刷方向を縦向きから横向きにしたい

基本的にテンプレートの印刷設定を修正するだけで対応可
「テンプレート内のシートをコピーして作成その後、シートを削除する」といった処理の場合は以下の対応を行う

worksheet.page_setup = template.page_setup

以下page_setup内の属性

  • paperSize: unsigned int型
    • 用紙サイズ
  • scale: unsigned int型
    • 印刷スケール: すべての行を1シート内に含める等の設定
  • firstPageNumber: unsigned int型
    • 最初のページ番号
  • fitToWidth: unsigned int型
    • 幅に合わせる
  • fitToHeight: unsigned int型
    • 高さに合わせる
  • horizonDpi: unsigned int型
    • 水平 DPI
  • verticalDpi: unsigned int型
    • 垂直 DPI
  • copies: unsigned int型
    • 印刷する部数
  • usePrinterDefaults: boolean型
    • デフォルトのプリンタを利用するかどうか
  • blackAndWhite: boolean型
    • 白黒印刷をするかどうか
  • draft: boolean型
    • 簡易印刷するかどうか
      • 以下を省略して印刷
        • 罫線や塗りつぶしの色・網掛けなどの書式
        • グラフや図形などのオブジェクト
  • useFirstPageNumber: boolean型
    • 最初のページ番号を使用するかどうか
  • pageOrder: downThenOver/overThenDown
    • ページの順序: 昇順/降順
  • orientation: default/portrait/landscape
    • 印刷時の向き: デフォルト/縦/横
  • cellComments: none/asDisplayed/atEnd
    • セルコメントを印刷する
  • errors: displayed/blank/dash/NA
    • 印刷エラー処理

rubyXL では実装できないパターンについて

  1. グラフ関連

グラフの描画自体はテンプレートファイルからの読み込みで問題なく実行可能
但し、以下のパターンは実現は難しい

  • ① シート名を動的に変更しつつグラフの描画を行う
    Excel 上だとグラフのデータ範囲はシートに基づいており、シート名を変更してしまうと変更前のシート名を参照してしまいエラーが発生する
    DISTINCT関数をテンプレート側で使用しても、グラフのデータ範囲はファイル読み込み時の Excel の関数よりも早くに動作するためエラーが発生する
    また、シート名の動的な変更は xlsm のマクロを利用するしかなく RubyXL 側では実現不可
  • ② グラフのデータ範囲の変更
    ①と同様の理由で不可
    同様の理由で、グラフのデータ範囲が動的に変化する
    (insert_row などを使用して抽出するデータ範囲が変動する)場合も RubyXL 側では実現不可
  • ③ グラフの生成(グラフ設定の編集含む)
    RubyXL にグラフ描画・サポート機能が対応していないため不可
    テンプレートファイルにてグラフを挿入しておいて対応する