【2021/5/20 一部内容を訂正】
RubyXLでは実装できないパターンについて の内容を現在確認できた状況に合わせて更新いたしました
はじめに
本記事は基本的にRubyXL ver.3.4.0を想定して記載を行っています
ver3.4.0からリソースの無駄を抑えられるように用途に応じたメソッドが呼び出せるようになりました
どちらかと言えば、呼び出さないと使えなくなったのでundefined method Errorが出る場合は確認してください
基本的には以下の通りです
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'
発生した課題とその対応
テキストの折り返しがsheet.add_cellではできない
sheet.add_cell(col, row, 'text').change_text_wrapによって実装可能
テキストを折り返して表示してくれるようになる
change_contentsが使えない
3.4.0以降はconvenience_methodsを呼び出さないと利用できないため、要確認
シートのコピーができない
参考サイト
Marshal.loadなども試したが、どうにも上手くいかなかったところ上記サイトの方法で解決
かなり力技だと思うのでより良い案があったら教えてください。
シートコピー後、コピーしたシートを編集したら他のシートの内容も一緒に変わってしまう
シートの書式等を丸々コピー
↓
change_contentsで書式設定を保持したまま内容を変更
↓
該当するコピーシートすべての中身が書き換わる
という流れ
恐らくchange_contents()の仕様と上記のシートコピーによって生じている現象だと思われる
add_cell()による値の挿入だとそういった現象は発生しなかった
この場合には書式設定等はRubyXL側で再現するしかない
動的な配置でエクセル数式を設定したい
正確には、「insert_rowによる行の挿入量が動的に変化する中、エクセルの数式によって値の算出が行いたい」
insert_row()は公式でも書かれているが扱いに慎重になるべきメソッドで
結合したセルや数式が挿入した行の下にあった場合にエクセルを破損させることが頻発する
解決策として
add_cell()の書式が
add_cell(row_index = 0, column_index = 0, data = '', formula = nil, overwrite = true)となっていたことを確認
data部分に数式を書くのではなく、formula部分に数式を記述することで解決した
グラフの作成ができない
xlsx、xlsmともに不可能
RubyXL側にグラフに関するメソッドは存在しないため、こればかりはどうしようもない（はず
テンプレートシートに人数分のシートとグラフを用意して
マクロを用いてファイルを開いた際にグラフを描画するようにして対応した
用意した人数未満であれば、シートの状態を非表示にすることで一見正常に動作しているようにした
読み込み時に数式を再計算させるようにしても動いてくれない
下記の「基本的な動作」に記載された
エクセルで強制的に再計算させるプログラムを書いてもグラフの表示が変わらない問題が発生した
結論から書くとエクセル側の仕様であることが判明
ネット上からダウンロードしてきたファイルは保護ビュー関連で再計算前に必ず止まってしまう
そのため、エクセルの設定から保護ビューなどの設定を無効化することで対処できる
印刷方向を縦向きから横向きにしたい
基本はテンプレートファイルの印刷設定を修正するだけで問題ない
テンプレートファイル内のテンプレートシートをコピーして作成、作成完了後テンプレートシートを削除する
といった処理の場合は要注意(上記参照)
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の2択) ## ページの順序．昇順か降順か
orientaion(default・portrait・landscapeの3択) ##オリエンテーション(印刷時の向き．デフォルト・縦・横)
cellComments(none・asDisplayed・atEndの3択) ##セルコメントを印刷する
errors(displayed・blank・dash・NAの4択) ## 印刷エラー処理
上記のような形で印刷時のページ設定をコピーしてくれば問題ない(もしくはpage_setupに直接値を入力するか)
RubyDocによると、RubyXL::PrintOptionsというのが存在し、
Worksheet内にも同様のものが存在するが基本的にnilになっている
クラスとして存在し、属性に値を入れることもできるので何かしらできることがあると思われるが...
もしできるのであればそちらで設定するのが適切かもしれない
RubyXLでは実装できないパターンについて
グラフの生成(グラフ設定の編集含む)
RubyXL側にグラフ描画・サポート機能がないためそもそも不可能
テンプレートファイルに直接描画してのがベスト
シート名を動的に変更しつつグラフの描画を行う
エクセル上だとグラフのデータ範囲はシートに基づいており
シート名を変更してしまうと変更前のシート名を参照してしまいエラーが発生してしまう
DISTINCT()などを用いてもグラフのデータ範囲はファイル読み込み時の
エクセルの関数よりも早くに行われるためどうしてもエラーが発生する
また、シート名の動的な取得はxlsmでマクロを利用するしかなく
RubyXL側では何もすることができないこととなる
同様の理由で、グラフのデータ範囲が動的に変化する(insert_row()などを用いて抽出するデータ範囲が変動する)場合もRubyXL側では何もできないことになる
こちらについては行の挿入を行っても位置が変化しない部分を非表示にし
それぞれの値を非表示セル内に記載してデータ範囲は非表示セル内のデータとすれば解決が可能だと思われる
【2021/5/20 訂正】
正確な表現ではありませんでした
「グラフ描画の際に行われるデータ範囲指定時に、RubyXL側で変更されたシート名を取得してデータ範囲を決定、描画することはできないため、
グラフ描画におけるデータ範囲指定にはシート名変更はマクロで行わざるをえず、RubyXL側では何もすることができないこととなる」
当時の自分が確認していた環境が良くなかったようで、現在試してみると普通に動作します
実際には下記に記載したシート名変更でもグラフ描画ができることを確認しました
(RubyXL側でシート名を変更してもグラフ描画ができました)
workbook = RubyXL::Parser.parse('test.xlsm')
sheet = workbook.first
sheet.sheet_name = 'シート名'
その代わり、画像のようなエラーメッセージが必ず出現しました(が、一応グラフの描画自体はできています)
「insert_row()などを用いて抽出するデータ範囲が変更前と変更後で異なる場合に関しては、マクロを使用しても変更後のセル取得ができないためRubyXL側でできることはない」
「ただし、COUNTA()とROW()などを組み合わせてデータ範囲を見つけ出すことは可能」
基本的な動作
主に公式のGithubページやQiita、RubyDocを基に結構使いそうなものを列挙してみました
ファイル(ブック)の新規作成
workbook = RubyXL::Workbook.newテンプレート等のファイル(ブック)読み込み
xlsx、xlsm以外の読み書きは不可
workbook = RubyXL::Parser.parse('app/assets/template/template.xlsx')見つからなかったらZip::Error: XLSX file format errorが返ってくるため注意
ファイルの書き出し
workbook.write('data/sen/template.xlsx')File.openなどを利用する必要はない
数式の再計算
4つすべて必要
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
シート指定
1枚目のシートはworkbook[0]、2枚目はworkbook[1]
2枚目のシートが存在しない状態でworkbook[1]を指定した場合はnilが返ってくる
sheet = workbook[0]
sheet = workbook.first
sheet = workbook['sheet_name']
シートの名前を変更
日本語名可
sheet.sheet_name = 'sheet_name'シートの追加(新規作成)
sheet = workbook.add_worksheet('sheet_name2')などと
変数に代入して作成したシートの参照ができる
workbook.add_worksheet('sheet_name2')シートの削除
workbookからの指定で行わないとできない
指定したシートが存在しなかったらnilが返ってくる
workbook.worksheets.delete(workbook[0])行指定
指定した位置の行全体を取得できる
row = sheet[sheet_row]
row = sheet.sheet_data[sheet_row]
セル指定
cell = sheet[row][col]
cell = sheet.sheet_data[row][col]
下記コードでもセルの指定が可能
値が存在したら元データを、存在しなかったら上書きする
値の抽出はcell.valueで抽出できる他、sheet[row][col].value、add_cell(row, col, 'hoge').valueでも可能
(上記の場合はhogeをvalueとして取得する)
cell = sheet.add_cell(row, col, '', nil, false)以下書式
data部分を空にしてformula部分に'SUM(A1, B1)'などと書くとエクセルの数式を作ってくれる
この時、=(イコール)は不要なので注意
add_cell(row_index = 0, column_index = 0, data = '', formula = nil, overwrite = true)値の挿入(新規追加・書式設定の保持は不可)
sheet.add_cell(row, col, 'text')値の挿入(上書き・書式設定を保持する)
sheet[row][col].change_contents('text')ただし、指定したセルに値が存在しない(nilであった)場合はエラーが発生するため注意
以下書式
data部分を空にしてformula部分に'SUM(A1, B1)'などと書くとエクセルの数式を作ってくれる
この時、=(イコール)は不要なので注意
change_contents(data, formula_expression = nil)行の挿入
挿入箇所よりも下に結合したセルや数式が存在する場合、エクセルが破損するため注意
挿入箇所よりも上にある場合は問題無い
sheet.insert_row(row)下記のように書くと値を勝手に入れてくれる(らしい？動作未確認)
sheet.insert_row(row, array) //arrayは値が格納された配列行の削除
挿入箇所よりも下に結合したセルや数式が存在する場合、エクセルが破損するため注意
挿入箇所よりも上にある場合は問題無い
worksheet.delete_row(1)列の挿入
挿入箇所よりも右に結合したセルや数式が存在する場合、エクセルが破損するため注意
挿入箇所よりも左にある場合は問題無い
sheet.insert_column(col)下記のように書くと値を勝手に入れてくれる(らしい？動作未確認)
sheet.insert_row(row, array) //arrayは値が格納された配列列の削除
挿入箇所よりも右に結合したセルや数式が存在する場合、エクセルが破損するため注意
挿入箇所よりも左にある場合は問題無い
sheet.delete_column(col)セルの挿入
ずらす方向はright/left/top/bottomの4種類を設定可能
worksheet.insert_cell(row, col, 'text')と書いた場合はadd_cellと同じ動きになる(セルはずれない)
ずらした方向に結合したセルや数式が存在する場合、エクセルが破損するため注意
worksheet.insert_cell(row, col, 'text', nil, :right)セルの削除
削除後にずらす方向はright/left/top/bottomの4種類を設定可能
worksheet.delete_cell(row, col, 'text')と書いた場合はadd_cellと同じ動きになる
ずらした方向に結合したセルや数式が存在する場合、エクセルが破損するため注意
実際のところ、add_cellやchange_contentsで空文字を挿入しても変わらないので
安全に利用できるadd_cellなどで上書きした方が良い
sheet.delete_cell(row, col, :right)セルの文字色の変更
16進数で指定する #(シャープ)は不要
行・列指定のようにsheet.change_row_font_color(row, 'FFFFFF')とすれば
指定した行全体を変えることができる
sheet.add_cell(～).change_font_color('FFFFFF')セル指定の場合、cell = sheet.add_cell(～)後にcell.change_font_color('FFFFFF')
change_contents()の後ろにつけても実装可能
セルの背景色を変更
16進数で指定する #(シャープ)は不要
行・列指定のようにsheet.change_row_fill(row, 'FFFFFF')とすれば
指定した行全体を変えることができる
sheet.add_cell(～).change_fill('FFFFFF')セル指定の場合、cell = sheet.add_cell(～)後にcell.change_fill('FFFFFF')
change_contents()の後ろにつけても実装可能
セルの結合
選択した範囲内のセルを結合する(A1からB2まで、とするとA1・A2・B1・B2セルを結合する)
sheet.merge_cells(row1, col1, row2, col2)罫線の付与
線の太さはhairline/thin/medium/thickの4種類が選択可能
sheet.add_cell(～).change_border(:left, 'medium')セル指定の場合、cell = sheet.add_cell(～)後にcell.change_border(:left, 'medium')
change_contents()の後ろにつけても実装可能
複数指定の場合は以下のように行う
%i[left right bottom top].each do |e|
cell_index.change_border(e, 'medium')
end
その他、可能なことはGithubやRubyDocのご確認を
参考サイト
- 公式Github
-
RubyDoc
- 利用できるメソッドや型の説明が細かく載っているので迷ったら確認するべき場所
- 参考Qiita1：rubyXlでエクセルを壊さないように丁重に扱いながら、内容を変えていただくための注意事項
- 参考Qiita2：rubyXL で Excel 生成したいときの Tips 集
- 参考Qiita3：Railsで既存のエクセルファイルをテンプレートにできる魔法のヘルパー