Excel を Ruby で操る: CountIf を使いたい

Excel の便利な関数 CountIf をRubyから操る方法について調べました。

便利な関数 CountIf

CountIfはExcelで特定の条件のデータ(セル数)をカウントする関数です。

文字列の場合は「"」(ダブルクオーテーション)囲う必要があります。

セルで

=CountIf(E2:E20, "hoge")

とすれば、”hoge”を含むセルの数をカウントしてくれます。

定形作業は楽がしたい! そうだRubyでやっちゃえ!

「複数の文字列のデータをカウントしたい」とか複数のエクセルファイルで同じ文字列のデータ数をカウントした場合は楽がしたいです。

VBAの場合は、それぞれのエクセルファイルに保存されるので、複数のファイルを処理したい場合には向いていません。

そこでRubyです。Rubyはwin32oleでExcelを操作できます。( Win32OLE 活用法【第 2 回】Excel Rubyist Magazine)

Win32OLEというのは次のようなものです:( Win32OLE 活用法【第 1 回】 Win32OLE ことはじめからの引用です)

Win32OLE は、COM とか ActiveX などと呼ばれたりする技術を扱うためのライブラリです。この記事の中では OLE/COM/ActiveX といった用語をあまり区別せずに COM という言葉でまとめておきます。Windows アプリケーションは一般にいくつかの部品を組み合わせてできています。この部品化する技術の中に Microsoft 社が開発した技術に Component Object Model (COM) があります。
COM には言語に非依存であるという特徴があります。つまり、COM で作られた部品であれば C++ でも VB でも Delphi でも、そして Ruby でもプログラムの中で扱うことができます。
こう言ってもピンとこないかもしれません。具体的に言うと、Ruby で書いたプログラムで Internet Explorer を制御したり、Excel 表の値を取得、変更したりすることが可能だということです。

Rubyが使えるなら、使わない手はありません!

RubyでCountIfを使う

標準入力でファイルを受け取って処理するプログラムを考えます。

CountIf は WorksheetFunction のメソッドです。 WorksheetFunction はWIN32OLEのオートメーションサーバー ‘Excel.Application’のメソッドです。

CountIfの最初の引数は範囲指定です。範囲指定はシートの指定が必要ですので、Worksheetsメソッドでシートを参照しています。

ちょっと工夫: CurrentRegion を使う

範囲指定は決め打ちでも良いですが、決め打ちを確認する手間を省きたいところです。

そこで、大まかな範囲が分かれば全部範囲に指定するようにしました。そのために CurrentRegion を使います。

CurrentRegion はそのセルを含む空白のセルで囲まれた範囲を読み取り、参照します。( CurrentRegion (Excelマクロ・VBA塾) )

サンプルコード

以下がサンプルコードです。これを例えばexcel_sample.rbと名前で保存して、 ruby excel_sample.rb fuga.xls という風に実行します。

#!/usr/bin/env ruby

# -*- coding: utf-8 -*-

require 'nkf'
require 'win32ole'

def getAbsolutePath filename
  fso = WIN32OLE.new('Scripting.FileSystemObject')
  return fso.GetAbsolutePathName(filename)
end

def read_xls(xls)
  filename = getAbsolutePath(xls)
  xl = WIN32OLE.new('Excel.Application')
  book = xl.Workbooks.Open(filename)

  begin 
    book.Worksheets.each{|sheet|
      name = NKF.nkf("-w", sheet.name)
      printf("sheet: %s\n", name)
      count = xl.WorksheetFunction.CountIf(sheet.Range("A3").CurrentRegion, "*その他*")
      printf("その他: %d\n", count)
    }
  ensure
    book.Close
    xl.Quit
  end
end

def main
  ARGV.each{ |xls|
    read_xls(xls)
  }
end
main