Ruby: Win32OLEでの Excelの操作

2016/7/26 ここで説明しているモジュールをtrail_excelという名前でgem化しました。
gem install trail_excelで利用できます。 ruby 1.9以降用です。

ExcelのインストールされているWindows環境ではExcelを Win32OLE という仕組みで 利用することが可能です。この仕組みを使うとExcelのVBAで出来ることはRubyからも 同様に出来るようになります。

このページの元と成るコードは Ruby 1.8.7 で利用した物です。 今回はそれをRuby 1.9.2で確認しながら解説を書いています。

Ruby 1.9系では例題以上の詳細な確認はしていません。

OLEはプログラム間で通信しなが動かす仕組みですので、 ひとつひとつの機能呼び出しが比較的重くなってしまいます。 最近の速いコンピュータなら特に問題となる事はありませんが 大量の処理をしようとすると時間がかかることになります。

Excelを使い易くするためのモジュールのスクリプトの例を以下にあげます。 ruby標準で入っているwin32oleモジュールだけでも利用可能なのですが 下記のようなモジュールを定義することで更に楽に使う事が出来ます。

必要な機能をVBAのマニュアル等から探し出し、使い易いように 自分でモジュールを拡張して下さい。

下記のスクリプトを excel.rb として作成して下さい。 Ruby 1.9系で使用する場合には jcode kconv $KCODE の行は削除して下さい。

改定履歴:2013/03/02 r_strをVBA関数を使うように変更、カラム数が多い時に非対応だった事を改善。

#! ruby -Ks            # ← Ruby 1.9系では -ESJIS
require 'jcode'        # ← Ruby 1.9系では不要
require 'kconv'        # ← Ruby 1.9系では不要
$KCODE='s'             # ← Ruby 1.9系では不要

require 'win32ole'

##----- Excel module -------------------------------
module Worksheet
  
  def [] y,x
    cell = self.Cells.Item(y,x)
    if cell.MergeCells
      cell.MergeArea.Item(1,1).Value
    else
      cell.Value
    end
  end
  
  def []= y,x,value
    cell = self.Cells.Item(y,x)
    if cell.MergeCells
      cell.MergeArea.Item(1,1).Value = value
    else
      cell.Value = value
    end
  end
  
  def color(y,x)
      self.Cells.Item(y,x).interior.colorindex
  end
  
  def set_color(y,x,color)
      self.Cells.Item(y,x).interior.colorindex = color
  end
  
  def set_range_color(y1,x1,y2,x2,color)
    r = r_str(y1,x1)+':'+r_str(y2,x2)
    self.Range(r).interior.colorindex = color
  end
  
  def font_color(y,x)
      self.Cells.Item(y,x).Font.colorindex
  end
  
  def set_font_color(y,x,color)
      self.Cells.Item(y,x).Font.colorindex = color
  end
  
  def set_range_font_color(y1,x1,y2,x2,color)
    r = r_str(y1,x1)+':'+r_str(y2,x2)
    self.Range(r).Font.colorindex = color
  end
  
  def set_width(y,x,width)
      self.Cells.Item(y,x).ColumnWidth = width
  end
  
  def r_str(y,x)
    self.Cells.Item(y,x).address('RowAbsolute'=>false,'ColumnAbsolute'=>false)
  end
  
  def formula( y,x,f)
    r = r_str(y,x)
    self.Range(r).Formula = f
  end
  
  def group_row(y1,y2)
    r = r_str(y1,1)+':'+r_str(y2,1)
    self.Range(r).Rows.Group
  end
  
  def group_column(x1,x2)
    r = r_str(1,x1)+':'+r_str(1,x2)
    self.Range(r).Columns.Group
  end
  
  def merge(y1,x1,y2,x2)
    r = r_str(y1,x1)+':'+r_str(y2,x2)
    self.Range(r).MergeCells = true
  end
  
  def box(y1,x1,y2,x2)
    r = r_str(y1,x1)+':'+r_str(y2,x2)
    self.Range(r).Borders.LineStyle = 1
  end
  
  def wrap(y1,x1,y2,x2)
    r = r_str(y1,x1)+':'+r_str(y2,x2)
    self.Range(r).HorizontalAlignment = 1
    self.Range(r).WrapText = true
  end
  
  def v_top(y1,x1,y2,x2)
    r = r_str(y1,x1)+':'+r_str(y2,x2)
    self.Range(r).VerticalAlignment = -4160
  end
  
  def center(y1,x1,y2,x2)
    r = r_str(y1,x1)+':'+r_str(y2,x2)
    self.Range(r).HorizontalAlignment = -4108
  end
  
  def format_copy(y1,x1,y2,x2,y3,x3)
    r2 = r_str(y3,x3)
    r = r_str(y1,x1)+':'+r_str(y2,x2)
    self.Range(r2).Copy
    self.Range(r).PasteSpecial('Paste' => -4122)
  end
  
  def format_copy1(y1,x1,y2,x2)
    r2 = r_str(y2,x2)
    r = r_str(y1,x1)
    self.Range(r2).Copy
    self.Range(r).PasteSpecial('Paste' => -4122)
  end
  
  def copy(y1,x1,y2,x2,y3,x3)
    r2 = r_str(y3,x3)
    r = r_str(y1,x1)+':'+r_str(y2,x2)
    self.Range(r2).Copy
    self.Range(r).PasteSpecial('Paste' => -4104)
  end
  
  def insert_row(n)
    self.Rows("#{n}:#{n}").Insert('Shift' => -4121)
  end
  
end

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

def openExcelWorkbook filename
  filename = getAbsolutePath(filename)
  xl = WIN32OLE.new('Excel.Application')
  xl.Visible = false
  xl.DisplayAlerts = false
  book = xl.Workbooks.Open(filename)
  begin
    yield book
  ensure
    xl.Workbooks.Close
    xl.Quit
  end
end

def createExcelWorkbook
  xl = WIN32OLE.new('Excel.Application')
  xl.Visible = false
  xl.DisplayAlerts = false
  book = xl.Workbooks.Add()
  begin
    yield book
  ensure
    xl.Workbooks.Close
    xl.Quit
  end
end

##----- End of Excel module -------------------------------

まず、このスクリプトはWindowsで利用するものですからSHIFT_JISを基本とします。 日本語拡張の jcodeとエンコード変換の為のkconvを読込みます。 また、主題である win32ole も読込みます。

Worksheet モジュールを宣言し、その中に手続きを定義します。 そうする事で利用時に手続きを組込むことが出来るようになります。

「 def [] y,x 」は角括弧を使って参照用の手続きを定義しています。 このように定義することで sheet[1,2] のようなセル参照が可能となります。 中はVBAのマニュアルを参照してください。 このサイト( よねさんのWordとExcelの小部屋 )が 調べるのに良いでしょう。

MargeCellか調べているのは、セルが結合されている時に結合の先頭の値を取る為です。 場合によっては邪魔になる機能かも知れません。

「 def []= y,x,value 」は角括弧を使って代入用の手続きを定義しています。 このように定義することで sheet[1,2] = 3 の様に値を入れることが出来るようになります。

以下、手続きの機能を列記します。

color(y,x)

sheet[y,x]のセルの色を参照します

set_color(y,x,color)

sheet[y,x]のセルの色をcolorに設定します

set_range_color(y1,x1,y2,x2,color)

[y1,x1]から[y2,x2]の範囲の色を colorに設定します。

font_color(y,x)

sheet[y,x]のセルの文字色を参照します

set_font_color(y,x,color)

sheet[y,x]のセルの文字色をcolorに設定します

set_range_font_color(y1,x1,y2,x2,color)

[y1,x1]から[y2,x2]の範囲の文字色を colorに設定します。

set_width(y,x,width)

セルの幅を設定します。

r_str

'A1'の形式のセル位置指定文字列を生成します。モジュール内で内部的に使用される手続きです。

formula( y,x,f)

指定された計算式をセルに設定します。

group_row(y1,y2)

行をグループ化します。

group_column(x1,x2)

カラムをグループ化します。

merge(y1,x1,y2,x2)

指定された領域を結合します。

box(y1,x1,y2,x2)

指定された領域を囲む境界線を書きます。

wrap(y1,x1,y2,x2)

指定された領域のセルに文字を折り返して表示する設定をします。

v_top(y1,x1,y2,x2)

セルを立て位置で上付きに設定します。

center(y1,x1,y2,x2)

セルの横位置を中央揃えに設定します。

format_copy(y1,x1,y2,x2,y3,x3)

sheet[y3,x3]の書式を[y1,x1]-[y2,x2]の領域にコピーします。

format_copy1(y1,x1,y2,x2)

sheet[y2,x2]の書式をsheet[y1,x1]にコピーします。

copy(y1,x1,y2,x2,y3,x3)

sheet[y3,x3]の内容を[y1,x1]-[y2,x2]の領域にコピーします。

insert_row(n)

n行目に行を挿入します。

getAbsolutePath filename

パスを生成します。モジュール内で内部的に使用される手続きです。

openExcelWorkbook filename

Excelのブックを開きます。visibleをtrueにすると開いたシートが表示されます。ここでは表示しない設定にしています。

createExcelWorkbook

Excelのブックを生成します。

ここではRuby 1.9.2 で確認しています。

spreadsheet gem ではセルの位置はゼロ始まりですが win32oleで Excelを使う場合にはセルの位置は１始まりです。 この点に充分注意して下さい。

まずは新しいブックを作ってセルに値を入れる例題をあげます。 前出の excel.rb は下記のスクリプトと同じフォルダーに置いて下さい。

#! ruby -EWindows-31J
# -*- mode:ruby; coding:Windows-31J -*-

require './excel'


createExcelWorkbook do |book|
  sheet = book.Worksheets.Item('Sheet1')
  sheet.extend Worksheet
  
  10.times do |n|
    n += 1                   # ← 位置は 1 始まりのため補正
    sheet[n,1] = n
    sheet[n,2] = n*n
    sheet[n,3] = "日本語表示"
  end
  
  book.SaveAs('sp_ole_test.xls')
end

作成されたExcelファイルはカレントディレクトリに作られます。 カレントディレクトリはプログラムの起動の仕方で変化します。 コマンドラインから起動するならカレントディレクトリはその時点の DOS窓のディレクトリとなります。他のRDE等のツールから起動した場合には カレントディレクトリはマイドキュメントと成っている場合と スクリプトを保存したディレクトリに成っている場合があります。

注意点はセルの位置が 1始まり と言う点だけだと思います。 文字コードはWindowsなのでSHIFT_JISを使用しています。

sheet.extend Worksheet

この行で excel.rb の中のWorksheetモジュールをsheetオブジェクトに 組み込んでいます。モジュールは個別のオブジェクトに組込む事が出来ます。 以後、モジュール内の手続きが使えるようになります。

ここではRuby 1.9.2 で確認しています。

Win32OLEでの操作はExcelそのものの動作ですので、直接編集しても 意図しないバグによる破壊が発生することはほとんどありません。 データを破壊するようなプログラムを書けば、当然、データを壊せます。

#! ruby -EWindows-31J
# -*- mode:ruby; coding:Windows-31J -*-

require './excel'


openExcelWorkbook('sp_ole_test.xls') do |book|
  sheet = book.Worksheets.Item('Sheet1')
  sheet.extend Worksheet
  
  10.times do |n|
    n += 1
    printf "%3d %3d %s\n",sheet[n,1], sheet[n,2],sheet[n,3]
    sheet[n,4] = '●'
    sheet.set_font_color(n,4,n)
  end
  
  sheet.set_range_font_color(1,1,10,2,4)
  book.Save
end

このスクリプトでは既存のファイルを開き順に表示しています。 4カラム目に丸を書き、セルの色を設定しています。

シートの選択は下記の様に番号でも選択可能です。

sheet = book.Worksheets.Item(1)

参照だけなら最後の book.Save は必要ありません。 book.Saveで更新した結果が書込まれます。