RubyXLでオートフィルタ付きでカラム幅とか整形済のExcelファイルを出力したい

RubyXL ?

Open Sourceの.xlsxファイルを扱えるRuby Gem.ぐぐるとQiita内でも記事がいっぱいでてくる．
https://github.com/weshatheleopard/rubyXL

TL;DR (長い3行で)

Rubyから.csvで結果やログを出力することはよくありますが，そのたびに.csvをExcelで開いて色をつけたり，カラム幅を調整したり，オートフィルタを設定したり，してから.xlsxとして保存，をするのがめんどうなので，RubyXL使って.xlsxの基本的なスタイリングまで自動化します．

出力する.xlsx

サンプルとして ↓ のようなExcelを書き出してみます．

・1行目のヘッダ行は背景色とフォント色を変更
・1行目のヘッダ行にオートフィルタを設定
・各カラム幅は各カラムの値の長さに合わせて可変，ただし幅が大き過ぎる場合は任意の最大値まで

Code

↓ のようなScriptで ↑ のExcelが出力されます．

#! ruby
# encoding: utf-8

require 'rubyXL'

workbook = RubyXL::Workbook.new
worksheet = workbook[0]

# Contents.
contents = [
    ['column_A', 'column_B', 'column_C'],
    [100, 200, 300],
    ['value_a', 'too long string', 'long long string over than default column width'],
]

column_width = [0, 0, 0]

# Add cells.
contents.each.with_index { |values, row|
  values.each.with_index { |value, column|
    # Add.
    cell = worksheet.add_cell(row, column, value)

    # Header line.
    if row == 0
      cell.change_fill('808080')
      cell.change_font_color('FFFFFF')
    end

    # Column width.
    column_width[column] = value.to_s.size if column_width[column] < value.to_s.size
  }
}

FILTER_BUTTON_WIDTH = 2
MAX_COL_WIDTH = 30

# Column width.
column_width.each.with_index { |width, column|
  rounded = width + FILTER_BUTTON_WIDTH
  rounded = MAX_COL_WIDTH if rounded > MAX_COL_WIDTH
  worksheet.change_column_width(column, rounded)
}

# Set filter.
range = RubyXL::Reference.new(0, 0, 0, 2) # row from/to, col from/to
filter = RubyXL:: AutoFilter.new
filter.ref = range
worksheet.auto_filter = filter

# Output.
workbook.write("filtered.xlsx")

背景色，フォント色変更

cell.change_fill('808080')
cell.change_font_color('FFFFFF')

普通にRGBで指定する感じ．文字列の先頭に#をつけて#808080みたいにすると動かない．

カラム幅の設定

worksheet.change_column_width(column, rounded)

カラム番号と幅の値を指定するだけ．
設定する幅の値はだいたい 1 = 1文字分 になっている気がするけど，正確なところはよくわからない．

各カラムの値の文字数の最大値をそのままカラム幅に設定するだけでもOKですが，このサンプルはオートフィルタのボタンぶん(2文字ぶん)幅を広げています．

値の型が文字列だったり数値だったり混在するときはcolumn_widthの取得のところにもっと工夫が必要．

オートフィルタの設定

range = RubyXL::Reference.new(0, 0, 0, 2) # row from/to, col from/to
filter = RubyXL:: AutoFilter.new
filter.ref = range
worksheet.auto_filter = filter

Referenceというclassでどのcellにオートフィルタを設定するか，を指定します．
この場合は[0, 0]から[0, 2]まで (A1, B1, C1)にオートフィルタを設定．

まとめ

RubyXLはREADME.rdocに書かれているよりもはるかに高機能なようです．
人間がやってるExcelの整形作業とかほとんど機械化できてもっとはかどりそう．

不具合?

RubyXLでもaxlsxでも，書き出した.xlsxをExcelで読み込んだあと，オートフィルタでSort等の処理をするとExcelが強制終了するようです．(100%再現)

一度，オートフィルタの▼をClickしてリスト表示 → 何もせずOK とした後なら問題なく動きました．

いまのところ原因不明のままで，ぐぐっても同件ぽい問題は出てこなかったので環境依存かもしれません．
RubyXL, axlsxで書き出した直後の.xlsxと，オートフィルタで何もせずOKして保存しただけの.xlsxの中身が変わっていることは確認しましたが，その違いが何を意味するところなのかまではわからず．．．

---///