[ホームへもどる] [Ruby による Win32 プログラミング へもどる]

Ruby による Win32OLE プログラミング -
Excel プログラミング（文書作成編）

はじめに

ここでは，Excel プログラミング（初級編） で作った Excel モジュールを用いて文書を作成するテクニックを紹介しましょう．

Excel 文書作成プログラムは，ともすればシートのセル番号などが入り乱れ，複雑で管理しにくいものになってしまいがちです．ここに書かれた方法やパターンを使えば，それほど時間を割かなくても Excel 文書作成プログラムが比較的簡単に Ruby で書けるようになると思います．

ところで，なぜここまでして Excel ファイルの自動生成をしなければならないのか，と疑問に思う人もいるかもしれません．程度の問題もありますが，そうした方がよい一番の理由は，モデルとビューの分離 です．文書のネタを XML ファイルなどのモデルとして作成し，提出しなければならない Excel ファイルをビューとみなせば，その利点を理解してもらえるでしょうか．

オブジェクト指向でよく話題になる モデルとビューを分離する，という話は，ここでも有効です．内容とみてくれを分離するメリットは，いまさら言うまでもありません．モデルを作成しているときは，必要最低限の内容だけに集中でき，きれいに書いて見栄えをよくしなければならない，ということを考えなくてすみます．また，みてくれを分離しておけば，Excel ファイルのフォーマットが変わった場合や Word 形式で文書を書くことになっても柔軟に対応できます．こういうメリットがある場合は，Ruby でプログラムを書いてみてはいかがでしょう．そうすれば，開発文書などの管理コストを下げることができ，Excel ファイルのフォーマット形式を完全に統一することが可能になります．

テンプレートファイル

まず，生成する文書のテンプレートファイルを用意しましょう．テンプレートファイルとは，拡張子 *.xlt の Excel ファイルのことです．テンプレートファイルで Excel 文書を作成してから Ruby でデータを書きこむプログラムを作成します．こうすることで Ruby に余計なプログラムを書きこむ手間が省け，後で Excel フォーマットを容易に変えることができます．

このセクションでは，例として次のようなテンプレートファイルを使うことにします．

このテンプレートファイル ( book.xlt ) を開いて新規文書として開くプログラムは，次のようになります．

  def test_new_template
    xlt_file_name = FileSystemObject.instance.getAbsolutePathName('book.xlt') # (A)
    Excel.runDuring do |excel|
      book = excel.workbooks.add(xlt_file_name) # (B)
      assert_equals('文献リスト', book.worksheets(1).name)
    end
  end

最初に FileSystemObject を使って book.xlt の絶対パス名を求めます( A )．その後，ブックを新規作成します（ Workbooks コレクションの Add メソッド）．Add メソッドに テンプレートファイルのパス名を渡せば，そのテンプレートに従うブックが新規作成されます( B )．

Lookup Cache パターン

さて，テンプレートファイルを使って新規作成した Excel シートにデータを書きこむプログラムを考えましょう．ここでは，素直にシートをクラスにしてしまうのが良さそうです．上記の例で，BookListSheet クラスということにしましょう．

class BookListSheet
end

本来なら Excel の Worksheet オブジェクトから継承を行ってクラスを定義すると簡単なのですが，そうすることはできません（ひょっとすると Ruby なら delegation を使ってうまくできるのかもしれませんが，不勉強のためよくわかりません）．

さて，こういった定型文書の場合，特定のプロパティがどのセルに対応しているのか判断する必要があります．例えば，著者のプロパティは B2 セルが対応し，ISBN のプロパティは B4 セルが対応しています（上図参照）．これをこのままハードコーディングしてしまうと テンプレートファイルを修正したときにプログラムも変更しなくてはなりません．そこで，テンプレートファイルのセルにプロパティに対応した名前をつけます．名前をつけると プログラムからもその名前でアクセスできます．例えば，

book.worksheets(1).range('著者').value = 'Kent Beck'

というように Range プロパティに名前を指定すればよいのです．ここでは，名前付けされたセルにアクセスできるようにするため CellAccessor モジュール を定義しましょう．

module Excel
  module CellAccessor
    def initialize(range)
      @range = range
      @cell_cache = Hash.new  # (A)
    end

    def lookup_cell_named(name)
      return @range.range(name)
    end

    def cell_named(name)
      cell =  @cell_cache[name]
      if cell == nil then
        cell = lookup_cell_named(name)
        @cell_cache[name] = cell
      end
      return cell
    end

    def []=(name, value)
      cell = cell_named(name)
      cell.value = value
    end

    def [](name)
      cell = cell_named(name)
      return cell.value
    end
  end
end

上のプログラムでは，単純にセルにアクセスするのではなく 効率化のため Lookup Cache パターンを使っています．このパターンは，ハッシュを使ってキャッシュを行うパターンです（Kent Beck）．初期化時にキャッシュのための Hash オブジェクト， cell_cache インスタンス変数を定義しています( A )．lookup_cell_named メソッドで実際にプロパティに対応するセルを検索しますが，外から呼ばれるのは cell_named メソッドです．このメソッドが対応するセルがキャッシュにないかどうか判定し，あればキャッシュにあるセルオブジェクトを，なければ実際にセルオブジェクトを検索する仕組みになっています．

実際に BookListSheet クラスに使ってみましょう．まず， CellAccessor モジュールをインクルードします．

class BookListSheet
  include Excel::CellAccessor
end

そして BookListSheet オブジェクトから次のようにして各プロパティにアクセスします．

  def test_cell_accessor
    xlt_file_name = FileSystemObject.instance.getAbsolutePathName('book.xlt')

    Excel.runDuring(true, true) do |excel|

      book = excel.workbooks.add(xlt_file_name)
      sheet = BookListSheet.new(book.worksheets(1))

      sheet['名前'] = 'Smalltalk Best Practice Patterns'
      assert_equals('Smalltalk Best Practice Patterns', sheet['名前'])

      sheet['著者'] = 'Kent Beck'
      assert_equals('Kent Beck', sheet['著者'])

      sheet['ISBN'] = '0-13-476904-X'
      assert_equals('0-13-476904-X', sheet['ISBN'])

      sheet['出版社'] = 'Prentice Hall'
      assert_equals('Prentice Hall', sheet['出版社'])

    end
  end

このプログラム実行時にできる シートのイメージは，以下のようになります．

Type Object パターン

今度は書きこむ項目数が可変の場合を考えましょう．例えば，文献が複数ありそれを同じシートに何個も書きこむ必要がある場合を考えます．

この場合，一つの文献について書かれた範囲（Range オブジェクト）を一つのクラスとして扱うのが自然でしょう．そこで，このクラスを BookRange クラスとします．ところが，シートにつけられる名前は一意なので，前の節のようにテンプレートファイルをつくって名前をつけ，CellAccessor から書きこむということはできません．そこで，BookRange のイメージを書いたテンプレートファイルをオープンし，その部分を何回もコピーして使いまわしするという方法をとることにします．

そのためには BookRange クラスを作る前準備として，テンプレートファイルをオープンして BookRange クラスそのものを定義する必要があります．その役割を表すクラスを， BookRangeType クラスとすることにしましょう（TypeObject パターン）．

シート全体をあらわすテンプレートファイルを books.xlt， BookRange を表すテンプレートファイルを book.xlt とした場合，コードのイメージは以下のようになります．

  def test_book_range
    file_system = FileSystemObject.instance	

    xlt_file_name = file_system.getAbsolutePathName('books.xlt')
    book_range_xlt_file_name = file_system.getAbsolutePathName('book.xlt')

    Excel.runDuring do |excel|

      sheet = excel.workbooks.add(xlt_file_name).worksheets(1) # --- (A)
      book_range_type = BookRangeType.new(excel, book_range_xlt_file_name) # --- (B)

      book_range1 = book_range_type.create(sheet.cells(1,1)) # --- (C)
      book_range1['名前'] = 'Smalltalk Best Practice Patterns'
      book_range1['著者'] = 'Kent Beck'
      book_range1['ISBN'] = '0-13-476904-X'
      book_range1['出版社'] = 'Prentice Hall'
      
      book_range2 = book_range_type.create(sheet.cells(7,1))
      book_range2['名前'] = 'Multi-Paradigm Design for C++'
      book_range2['著者'] = 'James O.Coplien'
      book_range2['ISBN'] = '0-201-82467-1'
      book_range2['出版社'] = 'Addison Wesley'

      assert_equals('Smalltalk Best Practice Patterns', book_range1['名前'])
      assert_equals('Multi-Paradigm Design for C++', book_range2['名前'])
    end
  end

まず文献リストを書きこむシート本体をテンプレートファイル books.xlt を元に新規作成します( A ) ．次にBookRangeType オブジェクトを作り， BookRange の元になるテンプレートファイル book.xlt を指定しておきます( B )． ( C ) が実際の BookRange オブジェクトを create メソッドを使って作成しているところです，引数 sheet.cells(1,1) は，シートの1行1列目に BookRange オブジェクトを定義する，という意味です．

実際の BookRange クラスは，以下のようになっています．

class BookRange
  include Excel::CellAccessor
  def lookup_cell_named(name) 
    case name
    when '名前'
      @range.cells(2, 2)
    when '著者'
      @range.cells(3, 2)
    when 'ISBN'
      @range.cells(4, 2)
    when '出版社'
      @range.cells(5, 2)
    end
  end
end

CellAccessor の lookup_cell_named をオーバーライドすることでシートに名前がつけられなくても大丈夫なようになっています（中身がハードコーディングになっているのでもう少しマシな方法がないか考え中です）．

BookRangeType クラスは以下のようになります．

class BookRangeType
  def initialize(excel, xlt_file_name)
    @xlt_book = excel.workbooks.open('Filename'=>xlt_file_name,
                                     'ReadOnly'=>true)
    @range_type = @xlt_book.worksheets(1).range('本') # ---(A)
  end

  def create(top_left_cell)
    select_cell(@range_type)
    @range_type.copy
    select_cell(top_left_cell)
    top_left_cell.parent.paste # --- (B)

    bottom_right_cell = top_left_cell.offset(@range_type.rows.count,
                                             @range_type.columns.count)
    range = top_left_cell.parent.range(top_left_cell,
                                       bottom_right_cell)
    return BookRange.new(range)
  end

  def select_cell(cell)
    cell.parent.parent.activate
    cell.parent.select
    cell.select
  end

end

initialize メソッドでテンプレートファイルをリードオンリーでオープンし，@range_type インスタンス変数にコピー元となる Range オブジェクトをセットします( A )．create メソッドの最初では，これから作る BookRange オブジェクトのために，テンプレートファイルから作成するファイルへコピー＆ペーストを行っています( B )． select_cell というメソッドは，コピー＆ペーストするための範囲選択を Win32OLE で行っているというわけです．

以上ですが，この方法はハードコードの部分も多くまだまだだと思います．もう少し洗練されたものにできないか考え中です．

分割コンパイル

今度は，複数のワークシートからなる Excel ファイルを自動生成することを考えましょう．この場合，最初からすべてのワークシートを自動生成していくと効率が悪くなります．最後に Excel ファイルを作ればそれで終わりというようなものなら問題になりませんが，文書は，後から何度も修正・メンテナンスされることが普通です．本来なら一つのワークシートの修正だけで済むのに，毎回すべてのワークシートを自動生成するのは効率が悪いです．そこで，メイクファイルを使った 分割コンパイル の手法を用います．

元ファイルを a.xml, b.xml とし，それぞれがワークシート一つに対応しているとします．最終的に作る文書は all.xls という Excel ファイルで，2 つのワークシートから構成されるとしましょう．通常分割コンパイルはメイクファイルで行うのですが，まずは説明のためにコマンドラインから実行するサンプルを書いてみます．最初に XMLファイル a.xml, b.xmlから 中間ファイルとして1シートのみからなる2つのブック a.xls, b.xls をスクリプト (xml2xls.rb) で自動生成します．

c:\> ruby xml2xls.rb -o a.xls a.xml
c:\> ruby xml2xls.rb -o b.xls b.xml

ここで， xml2xls.rb は 引数に指定した XML 文書を解析し，o オプションで指定した Excel ファイルを出力するとします．最後に，複数あるExcelブックを１つのブックに統合するスクリプト (xlsjoin.rb) で a.xls と b.xls をジョインします．

c:\> ruby xlsjoin.rb -o all.xls a.xls b.xls

xlsjoin.rb は，引数に指定した Excel ファイルのワークシートをまとめ， o オプションで指定した Excel ファイルとして出力するものとします．これまでの過程を図で表すと，以下のようになります．

C 言語で開発をしたことがある人なら，元ファイルが C言語で書かれたソースファイル，中間ファイルがオブジェクトファイル，最終ファイルが実行プログラムに対応していることがわかると思います．また， xml2xls.rb はCコンパイラ，xlsjoin.rb はリンカに相当するということもわかるでしょう．さて，以上の操作をメイクファイルで表しましょう．

.SUFFIXES: .xml .xls

XLS_FILES = \
	a.xls \
	b.xls \

.xml.xls:
	ruby xml2xls.rb -o $@ $<

all.xls:$(XLS_FILES)
	ruby xlsjoin.rb -o $@ $(XLS_FILES)

このようにすると，一番最初に make を実行した場合次の3つのコマンドが実行されることになります．

c:\> ruby xml2xls.rb -o a.xls a.xml
c:\> ruby xml2xls.rb -o b.xls b.xml
c:\> ruby xlsjoin.rb -o all.xls a.xls b.xls

けれども次に b.xml だけ修正した後で make を実行すると，次の2つのコマンドしか実行されません．

c:\> ruby xml2xls.rb -o b.xls b.xml
c:\> ruby xlsjoin.rb -o all.xls a.xls b.xls

この例では，2つのワークシートのみでありがたみがわかりませんが，ワークシートがかなり多い Excel 文書や，一つのワークシートを作る処理が重いものに対してはかなり有効な方法です．また，自動生成するための Ruby スクリプトは中間ファイルの作成のみに専念できるため，開発も楽になります．

RubyUnit で Acceptance Test

先ほど分割コンパイルで使った xlsjoin.rb を RubyUnit を使ってテストすることを考えましょう．今までは，RubyUnit でクラス単位の単体テストを行ってきましたが，バッチ処理程度のプログラムなら， RubyUnit を使って実際にプログラムを実行しテストすることができます（こういうテストを XP では Acceptance Test （受け入れテスト）と呼びます）．今回は，xlsjoin.rb を実際に実行し，その出力結果を見るだけなので簡単ですね．

Ruby で プログラムをコマンドラインから起動するには，system 関数 を用います．あらかじめ テスト用に 2つの Excel ファイル xlsjoin-test-a.xls, xlsjoin-test-b.xls を作っておきましょう．以下は， xlsjoin-test.rb からの抜粋です．

  def setup
    @xls_file = "xlsjoin-test.xls"
    @excel = Excel.new(false)
  end
  
  def teardown
    File.delete(@xls_file) if File.exist?(@xls_file)
    @excel.quit
  end

  def open_xls(filename)
    path = FileSystemObject.instance.getAbsolutePathName(filename)
    @excel.workbooks.open({'Filename'=>path, 'ReadOnly'=>true})
  end

  def test_ab
    system <<XXX
ruby xlsjoin.rb -o #{@xls_file} xlsjoin-test-a.xls xlsjoin-test-b.xls
XXX
    worksheets = open_xls(@xls_file).worksheets
    assert_equals(3, worksheets.count)
    assert_equals('a-1', worksheets.item(1).name)
    assert_equals('a-1', worksheets.item(1).cells(1,1).value)
    assert_equals('a-2', worksheets.item(2).name)
    assert_equals('a-2', worksheets.item(2).cells(1,1).value)
    assert_equals('b-1', worksheets.item(3).name)
    assert_equals('b-1', worksheets.item(3).cells(1,1).value)
  end

まず，setup メソッドで出力ファイル名と excel オブジェクトを初期化しています． teardown メソッドでは，出力ファイル名の削除と excel の終了処理を行いゴミが出るのを防ぎます．実際の テストを行うメソッドは test_ab ですが，最初に system 関数を使ってプログラムを実行し，その後出力された Excel ファイルの中身をチェックしています．

このように，GUI や Web アプリの Acceptance Test の自動化は非常に難しいですが（というか僕は挫折しましたが^^;），規模が小さいバッチ処理のプログラムならいきなり RubyUnit で Acceptance Test を作ってしまうことも可能です．UnitTest だけでなく Acceptance Test も RubyUnit で実装できないか考えてみるのもいいと思います．

[ホームへもどる] [Ruby による Win32 プログラミング へもどる] [先頭へもどる]