xlawk.rb: Excel 用の awk を Ruby で作る

私が Ruby を使う機会はもっぱら Win32OLE モジュールを使って Excel ブックを定型処理するような場合が多くなっています。
以前はこういう用途に Perl を使っていたのですが、よく使う「～の条件を満たすすべてのシートに対して～」というような処理はブロックを使った構文が書きやすいようです。

しかし構文がいくら書きやすくても結局いつも似たようなコードを書いているのに気づきます。Excel データに定型的な処理を行う場合に書くコードというのは大抵以下のようなものです。

1. 引数で与えられたすべてのブックに対して、
2. 中に入っているすべてのシートについて（何かの条件付きでフィルタして）、
3. シート中の最後の行までを（やはり何かの条件付きでフィルタして、データを変換したりしながら）走査する。

こうした処理というのは実は要するに awk であって、Excel に対する awk のようなものがあれば済む話なのです。
そんなわけで Excel ブックを対象に awk のような処理をするためのプログラムを Ruby で書きました。名前は xlawk.rb です。

コード

require 'win32ole'

class WIN32OLE
  include Enumerable
  def each_line()
    lastcell =  Cells().SpecialCells(Excel::XlLastCell)
    (1..lastcell.Row).each{|row|
      $ROW = row
      line = Range(Cells(row, 1), Cells(row, lastcell.Column))
      yield line.map{|cell| cell.Value}
    }
  end
end

module Excel; end

app = WIN32OLE.new('Excel.Application')
WIN32OLE.const_load(app, Excel)

app.visible = true
books = app.Workbooks;

prog = ARGV.shift
files = ARGV.inject([]){|product,pattern| product += Dir.glob(pattern).map{|file| File.expand_path(file)}}
files.each{|filename|
  book = books.Open(filename)
  $BOOK = book.Name
  worksheets = book.Worksheets
  worksheets.each{|sheet|
    $SHEET = sheet.Name
    sheet.each_line{|line|
      $LINE = line
      eval(prog)
    }
  }
  book.Close()
}

app.Quit

解説

上から解説していきます。

require 'win32ole'

これは Win32OLE モジュールを読み込んでいます。

class WIN32OLE
  include Enumerable
  def each_line()
    lastcell =  Cells().SpecialCells(Excel::XlLastCell)
    (1..lastcell.Row).each{|row|
      $ROW = row
      line = Range(Cells(row, 1), Cells(row, lastcell.Column))
      yield line.map{|cell| cell.Value}
    }
  end
end

前述の3つの定型処理のうち最初の2つは既にある道具立てで出来るのですが、Excel の API には行単位のコレクションというのはありません。
そこで WIN32OLE クラスに each_line メソッドというのを自家定義しています。ライブラリが提供する既存クラスに新たにメソッドを付け足せるというのが Ruby っぽくて面白いと思います。
行単位で繰り返すのに「どこで止めたらいいか」を知っておく必要があるため、 xlLastCell という特殊セルの場所を SpecialCells メソッドで取得しています。これは Excel で Ctrl+End を押したときに移動する先のセルのことです。
なお WIN32OLE クラスは each メソッドを提供しているので Enumerable モジュールもついでに include しています。

module Excel; end

app = WIN32OLE.new('Excel.Application')
WIN32OLE.const_load(app, Excel)

app.visible = true
books = app.Workbooks;

この辺りは Excel を使う上でのお決まりの前準備です。const_load は先の xlLastCell のような Excel の定数を使えるようにするためのものです。（Exceｌ モジュールもそのためのものです）
アプリの Visible プロパティを true にするかどうかは好みがあると思いますが、途中で異常終了した場合に見えないけどプロセスが残っているという事態になりがちなので見えていたほうがいいと思います。

prog = ARGV.shift
files = ARGV.inject([]){|product,pattern| product += Dir.glob(pattern).map{|file| File.expand_path(file)}}

プログラムの第1引数は xlawk 用のコードで、第2引数以降が対象となる Excel ブック（ワイルドカード可）になります。
後でブックを開く時にはファイル名をフルパスを与える必要があるので、ファイル名パターンを glob で展開した後で File.expand_path を使ってフルパスを取得しています。

files.each{|filename|
  book = books.Open(filename)
  $BOOK = book.Name
  worksheets = book.Worksheets
  worksheets.each{|sheet|
    $SHEET = sheet.Name
    sheet.each_line{|line|
      $LINE = line
      eval(prog)
    }
  }
  book.Close()
}

「すべてのファイルに対して（前述の1）～」が「files.each{|filename|」以降、
ファイルの「すべてのシートに対して（前述の2）～」が「worksheets.each{|sheet|」以降となります。
「すべての行に対して～」は先ほど定義した each_line メソッドを呼んでいます。このブロックの中で第1引数で与えられたプログラム片を eval します。

xlawk に与えるコードから便利に使えるようにいくつかのグローバル変数が定義されています。

$BOOK: 現在のブックの名前
$SHEET: 現在のシートの名前
$LINE: 現在の行のセルの内容が入った配列
$ROW: 現在の行番号

awk っぽく使うといっても評価されるコードは Ruby なので書き方には若干工夫が要ります。
awk で '$1 == "Foo" {print "Hit"}' と書くところを "($LINE[0] == 'Foo') and (puts 'Hit');" などと書くとまあまあそれっぽいと思います。

現実的な問題を解いてみましょう。例えば

・シート名が「目次」以外のシートにデータが入っている
・各シートの3行目からデータが開始する。
・2列目の内容候補は「A,B,C」のいずれかで他の値は入力ミス

というファイルについて入力ミスデータの箇所を探すコマンドは以下のように書けます。

ruby xlawk.rb "($SHEET != '目次') and ($ROW >= 3) and !(['A','B','C'].include? $LINE[1]) and (puts \"sheet:#{$SHEET}, row:#{$ROW}\")" data.xls