rubyXlでエクセルを壊さないように丁重に扱いながら、内容を変えていただくための注意事項

rubyXlを業務で利用してみてわかった注意事項

今日はRubyのgemの一つであるrubyXlを利用してみて、分かってきた利用のコツ、その他について記載していきたいと思います。

呼び出し

まずは、gemのインストール

gem 'rubyxl'

bundle install

次に利用するコントローラーでrubyXlのメソッド群を呼び出します。

#コントローラーです
#ver3.4.0からリソースの無駄を抑えるように、利用用途に応じて、メソッド群を分けて呼び出せるようになっている様子。
#今回はとりあえずメモリの消費を恐れず、次のように呼び出しを行います。

require 'rubyXL/convenience_methods'
# require 'rubyXL/convenience_methods/cell'
# require 'rubyXL/convenience_methods/workbook'
# require 'rubyXL/convenience_methods/worksheet'

#既存ワークブックの読み込み
workbook = RubyXL::Parser.parse("path/test.xlsx")
#ワークシーとの指定(sheet_nameにはシート名'Sheet1'等)
work_sheet = workbook[sheet_name]

※筆者の場合シートのコピーはうまくできませんでした。他のサイト様で上手くいったという事例を参考にしましたが、中々上手くいきません。

行の操作・挿入

#例えば次のようにして行を指定（行・列を二次元配列として捉えているためか、indexは０から始まる）
#したがって、A1セルはwork_sheet[0][0],B2はwork_sheet[1][1]となる
row = work_sheet[0] #これは最初の行
#row全体に罫線を引くこともできる（実際するかどうかは別として）
work_sheet.change_row_border(0,:top,'thin')
#rowの高さを変更
work_sheet.cahnge_row_height(0,30)
#指定した行に真っさらな行を追加（注意！行の挿入ではなく、行の上書きのような形）
work_sheet.add_row(1)
#指定した行に、行を挿入（要注意！！あとで説明します）
work_sheet.insert_row(1)

行の挿入(insert_row)に関する注意事項

例えばコントローラー側でDBの何らかの情報を取得し、１レコード１行として動的に行を生成するとします。

この時に、

• 挿入する行以下に、結合したセルがあれば、結合状態が壊れ、エクセル自体が破損する可能性が大
• 挿入する行以下のセルに、数式等を埋め込んでいた場合、エクセル自体が破損する可能性が大
  • 実際にエクセルを開いて行を挿入するのとは訳が違い、数式の中で参照しているセル情報との整合性が取れなくなったりするためだと思われます。公式のドキュメントにもしっかりと注意書きが書かれています。

inserts row at row_index, pushes down, copies style from below (row previously at that index) USE OF THIS METHOD will break formulas which reference cells which are being “pushed down”

breakという不吉なワード

セルの操作

#セルのvalue値（表示する文字列等）を変更する時には以下の２つの方法が使える。ここも注意点を後述。

#①指定したセルに真っさらなセルを上書きし、そこに指定したvalue(文字列等)を追加する。当然セルの書式なども真っさらになる・・・
work_sheet.add_cell(0,0,m_"テストですよ")
#②指定したセルのvalue値だけ変更する。セルの書式などは変わらないが、valueがnullだとエラーになる・・・
work_sheet.change_contents(0,0,"テストですよ")

#セルの結合（A1からC2まで結合）
work_sheet.merge_cells(0,0,2,1)

#セルの背景色変更(color = 'FFFFFF'のように指定。16真数だけどCSSみたいに#は不要)
work_sheet[0][0].change_fill(color)

#セル内文字の右寄せ、左寄せ
work_sheet[0][0].change_horizontal_alignment("right")#center,left指定可能

#セル内文字を折り返して表示(falseで折り返しなし)
work_sheet[0][0].change_text_wrap(true)

セルのvalue値変更に伴う注意事項

• add_cellはvalueがnilのセルに対しても使えるが、真っさらなセルを上書きするイメージなので、書式設定などが真っさらに塗り替えられる
• change_contentsはvalueがnilのセルに対して使うとエラーになる。でも書式設定などは壊れない。

↑を踏まえた上で利用するならば、
静的にvalueを塗り替えるセルが決まっているならば、change_contentsを使う。（半角スペースでも何でも良いので、valueを入れておけば、nilエラーにならない）

動的に追加した行などのセルにvalueを詰めるならば、add_cellを使う。

ちなみに、私はまだ実行できていませんが、change_contensメソッドをオーバーライドして、valueがnilの場合でも使えるようにする方法もあるかと思われます

xlsmファイルに関する注意事項

筆者が利用してみた場合、以下に注意すれば、xlsmファイルも組み込んだマクロを壊さず利用できました。（自己責任でお願いします）

• 例えばバーコードなどのActivexを利用したオブジェクトが入っているブックは壊れる
• 見た感じただの画像データや、エクセルで作ったグラフなどは壊れていなかった

その他自作メソッド

自作と言っておきながら、こちらを参考にさせていただきました。

動的に挿入していった行の高さを調整するためのメソッドです。

参考にさせていただいた先では、改行コードの数によって、高さを調整していますが、私の場合、テキストを折り返して表示しているセルがあったため、改行コードがないため、文字数で制限をかけています。（セルの横幅、フォントサイズで調整しないといけないので、あまり参考にならず申し訳ありません。）

#メソッドの呼び出し方(行では行を指定)
row_height_auto(work_sheet,gyou)

def row_height_auto(worksheet,row_num)
    mojisuu = []
    worksheet[row_num]&.cells.each do |cell|
        if cell.class != NilClass
            if cell.value != "" 
                mojisuu.push(cell.value.size) if cell.value
            end
        end
    end
    mojisuu = mojisuu.max
    if mojisuu >= 13#この辺はセルの横幅、およびフォントサイズで調整を
        num = (mojisuu / 13.0).ceil
    else
        num = 1
    end
    worksheet.change_row_height(row_num, 15 * num )
end

以上、rubyXlを利用して気をつけなければいけないと思ったところでした