RubyのAxlsxでExcelワークシートを作る(書式設定とか)

はじめに

ちょっと前にRubyでExcelワークシートを作ることがあったので、そのまとめ。
書式設定とかその辺を中心に。

基本

axlsxを使う。
MacでExcelワークシートを作るライブラリとしては、一番高性能?

require 'axlsx'
p = Axlsx::Pacpage.new
wb = p.workbook
wb.insert_worksheet(0, :name => "Sheet1") do |sheet|
  sheet.add_row ['name', 'species', 'age']
  sheet.add_row ['pochi', 'dog', '10']
  sheet.add_row ['tama', 'cat', '10']
end
p.serialize 'hoge.xlsx'

ワークシート関数

普通に文字列として入力すればいいみたいです。

sheet.add_row ['pochi', '=VLOOKUP(A1,Sheet1!A2:B10,2,FALSE)']

色の設定

ワークシートにスタイルを登録してから、セルを指定して適用。
多分、行とか列に対してもできるはず。

cyan = sheet.styles.add_style(bg_color: 'D9E1F2')
sheet['A1:C1'].each { |c| c.style = cyan }

列を追加するときにオプションで追加することもできるらしい。

sheet.add_row ['pochi', 'dog', '10'], :style => cyan

最初の行を固定

多分こんな感じ。

sheet.sheet_view.pane do |pane|
  pane.top_left_cell = "A2"
  pane.state = :frozen_split
  pane.y_split = 1
  pane.active_pane = :bottom_left
end

条件付き書式

数式で指定する場合。数式の頭に = はつけない。
数式内のセル参照は範囲の一番最初のセル基準でいいはず？

range = 'C2:C100'
params = {
  type: :expression,
  formula: 'ISNUMBER(C2)',
  dxfId: cyan,
  priority: 1,
}
sheet.add_conditional_formatting range, params

名前の定義

セル範囲に名前をつけるやつ。

wb.add_defined_name 'Sheet2!A2:A10', name: 'species'

入力規則

セルに入力できる値を制限するやつ。
リストから入力する値を選択させる場合。

range = 'B2:B100'
params = {
  type: :list,
  formula1: '=INDIRECT("specied")',  # リスト項目を記述したセル範囲
  showInputMessage: 1,
  showErrorMessage: 1,
  allowBlank: 1  # 空白を許す
}
sheet.add_data_validation range, params

列を非表示にする

1列目を非表示にする。

sheet.column_info[1].hidden