Excel用ライブラリexlap.rbの使い方

OLEオートメーションによるExcel操縦
〜 rubyによる制御 (ライブラリexlap.rb) 〜

最終更新日：　2012/07/12

　　　　

以下に掲げるドキュメントは、 exlap114.zip に同梱されている exlap.txt と同じ内容です。

--------

• 1.　はじめに
• (1) 開発の目的
• (2) 開発環境
• (3) 動作環境
• (4) exlap.rbの置き場所
• (5) exlap.rbで定義されているクラスとモジュール
• 2.　Excelによる単純なデータの書き込み
• 3.　データを読む
• 4.　複数のワークブックを扱う
• (1) 複数のワークブックを１つづつ処理
• (2) 複数のワークブックを並行して処理
• 5.　ワークシート内のセルとその集合を扱うための独自メソッド
• (1) セルオブジェクトを返す cell(y,x)
• (2) シートの各セルの値を２次元配列で返す to_a
• (3) シート中の所与の領域のセルの値を２次元配列で返す range_to_a
• (4) 所与の領域の番地を数値で返す range_address
• (5) 所与の領域の終点の番地を数値で返す range_last
• (6) 所与の領域の始点の番地を数値で返す range_start
• (7) 所与の領域の始点と終点のセルオブジェクトを返す range_pole
• (8) 利用されている領域全体を返す entire_range
• (9) 数値の番地で領域を指定するための rip_range
• (10) 指定領域のセルの幅と高さを自動調節する range_autofit
• (11) 指定領域に細線の罫線を引く range_allbox
• (12) 指定領域に罫線を引く(外枠は太線・内側の線は細線) range_doublebox
• (13) 指定領域の罫線を消去する range_clearbox
• (14) 所与のkeyと値が照合する最初のセルオブジェクトを返す findf(key)
• (15) 照合する複数のセルを配列で返す finda
• (16) ハイパーリンクを設定する hyperlink
• (17) 所与の領域のセルを一つづつ返す range_each
• (18) ワークシート内の全セルが空であるかを確認する empty?
• (19) ワークシートが空であるかを確認する empty_sheet?
• (20) 配列の内容をワークシートに貼り付ける from_a
• (21) csvファイルなどの内容をワークシートに貼り付ける from_f
• 6.　ワークシート内のセルとその集合を扱うためのサンプルあれこれ
• (1) 文字の色・大きさ・書体・下線・太さ・斜体の設定
• (2) ハイパーリンク、セルの幅と高さの自動調節、罫線
• 7.　ワークブック内においてワークシートを処理するための独自メソッド
• (1) 特定のワークシートを選択する select_sheet
• (2) 他のワークシートへの移動 offset_sheet
• (3) 最後のワークシートを返す last_sheet
• (4) 一番最後にワークシートを追加する add_sheet
• (5) 指定シートのところにワークシートを挿入する insert_sheet
• (6) ワークシートを削除する delete_sheet
• (7) ワークシートの名前一覧を返す sheet_names
• (8) ワークシートの番号を返す sheet_number
• (9) 空のワークシートの名前一覧を返す empty_sheet_names
• (10) 最初の空のワークシートを返す first_empty_sheet
• (11) 各ワークシートを選択する each
• 8. その他の独自メソッド
• (1) ワークブックを閉じる close
• (2) ワークブックの保存 save
• (3) ワークブックを開く book_open
• (4) ワークブックを閉じる book_close
• (5) ワークブックを選択する select_book
• (6) ワークブックの名前一覧を配列で返す book_names
• (7) 警告メッセージの画面表示を切り替える display_alert
• (8) 各々のワークブックを１つづつ選択する each
• (9) Exlap.new(……) {|wb| …………} のパターンおよびopensメソッド
• (10) 一度に複数のワークブックを開く opens_once メソッド
• (11) ライブラリのバージョンを返す version、Excelのバージョンを返す excel_version
• (12) ワークブックの名前を得るいくつかの方法
• (13) 該当のワークブックを制御するアプリケーションオブジェクト @app(Exlap型)の参照
• (14) テキストファイルを読み込み新しいワークブックとして開く book_opentext
• (15) アドイン関係のファイル情報を取得する addin_list
• 9.　クラスに属さない独自メソッド
• (1) セルまたはセル群(指定領域)の番地を数値で返す yx(別名 rc)
• (2) セルまたはセル群(指定領域)の番地を文字列で返す ab および a1
• (3) セルまたはセル群(指定領域)の相対番地をRC形式で返す relrc
• (4) セルまたはセル群(指定領域)の絶対番地をRC形式で返す absrc
• (5) 指定ファイル名のフルパス名を返す getAbsolutePath
• 10.　Exlapクラスのイテレータと @auto_quit, @auto_close
• (1) @auto_quit と opens, opens_once のイテレータ
• (2) @auto_close と Exlap.new および opens, opens_once のイテレータ
• 11.　マクロを扱うためのメソッド
• (1) コンポーネントのソースコードを文字列で得る macro_code(type, name)
• (2) コンポーネントのソースコードを登録する macro_add(str, type, name)
• (3) コンポーネントをファイルとして出力する macro_export(type, name)
• (4) コンポーネントをファイルから読み込む macro_import(filename, dup_check)
• (5) コンポーネントを削除する macro_remove(type, name)
• (6) コンポーネントの一覧を配列で返す macro_list(type, name)
• (7) マクロを実行する macro_run(mname, *arg)
• (8) マクロ(プロシージャ)の名前の一覧を得る macro_name(type, name)
• 12.　グラフを描く
• 13.　ライセンス
• 14.　参考サイト

---

(1) 開発の目的

　MS-Windowsに用意されている OLEオートメーションの仕組を利用して、ruby経由でExcelを操縦するために、exlap.rb というライブラリを作りました。

　末端事務職の私自身が比較的よく用いる制御をメソッド化して、簡単にExcel操縦できるようにしたものです。

　例えば、変数 ss が１つのワークシートオブジェクトを示している場合、

     ss.range_autofit
     ss.range_allbox

の２行を書くことで、データが書き込まれている領域のセルの幅と高さを自動調節し、それらセルを罫線で囲みます。

     ary = ss.to_a

とすれば、ワークシートに書かれているデータを配列の配列(以下「２次元配列」)の形で得られます。

　また、「合計」と書かれたセルのある行のところに１行挿入するなら次のとおり。

     ss.findf("合計").EntireRow.Insert

　Excel操縦の際の参考になるようでしたら幸いです。

　　　　

(2) 開発環境

　Windows XP, Excel ver 10.0(OfficeXP版), MSWin版 ruby ver 1.8.7

　　CPU：インテル(x86)・2800MHz, メモリー：190MB

　　　　

(3) 動作環境

　exlap.rbを利用するためには、MS-Windows上でExcelがインストールされている環境が必要です。

　当ドキュメントで紹介しているサンプルスクリプトを試したところでは、上の開発環境のほか、Windows VISTA, Excel ver 12.0(Office2007版)でも動きました。

　ruby.exeについては、Windows上で動くものであって win32ole.so が用意されていれば、その種類は問わないと思います。バージョンは、1.8.x であれば問題ないと思います。

　ruby v1.9.x で用いる時は、スクリプトの文字コードを日本語版MS-Windowsのデフォルト文字コードの Windows-31J にすれば、大丈夫だろうと思います。

　　　　

(4) exlap.rbの置き場所

　exlap.rbを利用する場合、カレントディレクトリに置きか、または、rubyの library path が通ったディレクトリに置いて下さい。

　例えば、ruby.exeが C:\ruby\bin\ の下にある場合でいうと、

   C:\ruby\lib\ruby\site_ruby\1.8\

の下に exlap.rb を置きます。こうしておけば、どのディレクトリにいる時でも exlap.rb を利用できます。

　なお、同梱の setup.rb, unset.rb を使うと、exlap.rbのセットアップと削除を簡単に行えます。

　setup.rb と exlap.rb がカレントディレクトリにある状態で、

   ruby.exe setup.rb ↓

と実行すれば、exlap.rb を rubyのlibrary pathが通ったディレクトリにコピーします。

   ruby.exe unset.rb ↓

と実行すれば、rubyのlibrary pathが通ったディレクトリから exlap.rb を削除できます。

　　　　

　rubyそのものののインストール方法については、rubyの情報サイト「ルビマ」に毎号掲載されている「Rubyの歩き方」が参考になります。

Rubyist Magazine - Ruby の歩き方

　　　　

(5) exlap.rbで定義されているクラスとモジュール

　次のものを定義しています。

• class Exlap: Excel.Applicationを扱うためのクラス。WIN32OLEが提供する本来のアプリケーションオブジェクトは、このクラス内の @obj にセットされる。@objは外部から参照可能。
• class Exlwb: ワークブックを扱うためのクラス。WIN32OLEが提供する本来のワークブックオブジェクトは、このクラス内の @obj にセットされる。@objは外部から参照可能。
• module Exlsheet: WIN32OLEが提供するワークシートオブジェクトに独自のメソッドを追加するためのモジュール
• class ExlSolver: アドインのソルバーを扱うためのクラス(ソルバーについては当ドキュメントで触れていません。solver.txtを参照)

　　　　

　なお、Excelを操縦する際に用いる定数(Xlで始まる定数等)は、exlap.rbをrequireすれば使えます。

　それから、exlap.rb およびサンプルプログラムにおいて、xxx.Range とか xxx.Font などのように大文字で始まるメソッドまたはプロパティは、WIN32OLEが提供するものです。VBAでそのまま使えるものです。

　それに対し、xxx.range_allbox とか xxx.findf などのように小文字で始まるメソッドは、rubyの世界で使えるものです。VBAでは使えませんのでご注意ください。

　　　　

　以下、exlap.rb の解説を記します。

---

　まずは、Excelを起動してワークブックを開き、空のワークシートを選んで、A1とB1のセルにデータを書き込む例を示します。

　なお、rubyがインストールされていて、exlap.rbがカレントディレクトリにあれば、Windowsのコマンドプロンプトを開いてから、次のように実行することによって、これ以降に出てくる sample01.rbを試すことができます。

     C:\ruby\bin\ruby.exe sample01.rb [enter]

　ruby.exeの置き場所が C:\ruby\bin でない時は、別のディレクトリを書きます。ruby.exeがpathの通ったディレクトリにあるなら、いきなり ruby.exe から書き始めて大丈夫です。

　正しく実行されると、カレントディレクトリに test.xls が作られます。

　　　　

   −−−− sample01.rb ここから
   #! ruby -Ks
   require "exlap"
   filename = "test.xls"
   xl = Exlap.new
   wb = xl.book_open filename  # ワークブックを開く
   ss = wb.first_empty_sheet  # 最初の空シートを選択
   ss.Name = "単純な書き込みのサンプル"  # ワークシート名を設定
   ss[1,1] = "経済成長という病"  # A1欄への書き込み
   ss[1,2] = "平川　克美"  # B1欄への書き込み
   wb.save  # ワークブック(ファイル)の上書き保存
   xl.quit  # 総てのワークブックを閉じてExcelを終了
   −−−− sample01.rb ここまで

　　　　

◇「wb = xl.book_open filename」は、filenameが存在しないファイルなら新規作成でオープンし、存在していれば素直にそれを開きます。

◇「ss = wb.first_empty_sheet」は、ワークブック内の最初の空のワークシートを選びます。もし空のワークシートがなければ、新しいワークシートを追加してそれを選びます。変数 ss にワークシートオブジェクトが代入されます。なお、first_empty_sheetにはfesという短縮名が別名定義されています。

◇「ss.Name = "単純な書き込みのサンプル"」は、ワークシート名を設定するものです。デフォルトの Sheet1 などを変更したい時にこのようにします。

◇セルへのデータの書込みですが、
　「ss[1,1] = "経済成長という病"」の記述は、よりVBAに近い形で

   ss.Range("A1").Value = "経済成長という病"

あるいは

   ss.Cells.Item(1,1).Value = "経済成長という病"

と書いても同じです。

　ss[1,1] という書き方は、exlap.rbの中で、ワークシートオブジェクトに対して [] メソッドを定義しているために可能となっている記述です。rubyの配列の [] とは関係ありません。

　　　　

　なお、上のサンプルプログラムをexlap.rbを用いずに書くと下のようになります。空のワークシートを選ぶところは、単純に第１のワークシートを選ぶ形にしてあります。

   −−−− sample02.rb ここから
   #! ruby -Ks
   require "win32ole"
   fso = WIN32OLE.new("Scripting.FileSystemObject")
   filename = fso.GetAbsolutePathName("test.xls")  # フルパス名で記録
   xl = WIN32OLE.new("Excel.Application")
   xl.Visible = true  # Excelが動くのを見えるようにする
   if test(?e, filename)  # filenameが存在する場合
     wb = xl.Workbooks.Open(filename)
   else  # filenameが存在しないので新規オープン
     wb = xl.Workbooks.Add()
   end
   ss = wb.Worksheets.Item(1)  # 第１ワークシートを処理対象に
   ss.Name = "単純な書き込みのサンプル(sample02)"  # ワークシート名を設定
   ss.Range("A1").Value = "死体の経済学(sample02)"
   ss.Cells.Item(1,2).Value = "窪田　順生(sample02)"
   xl.DisplayAlerts = false  # 確認ダイアログを表示しないための表示停止
   wb.SaveAs filename, xl.Version.to_f >= 12.0 ? 56 : 43
       # ↑ xls形式の保存のため、Excel2003以降なら56, もっと前なら43
   xl.Workbooks.Close  # ワークブックを閉じる
   xl.Quit  # Excelの終了
   −−−− sample02.rb ここまで

　　　　

　上記について、コメントを見ていただければ解説の必要はないと思いますが、ちょっと分かりにくいのが

   wb.SaveAs filename, xl.Version.to_f >= 12.0 ? 56 : 43

の箇所でしょうか。これは、Excel2003以前とExcel2007以降の間で、ワークブックの保存に関する仕様が違っているので、それに対応するため面倒な形になっているものです。

　上は、*.xlsのファイルを「名前を付けて保存」するものです。Excel2003以前に限るなら、

   wb.SaveAs filename, 43  # 43=XlExcel9795

とするだけでＯＫです。一方、Excel2007以降であれば、

   wb.SaveAs filename, 56  # 56=XlExcel8

で大丈夫です。

　xl.Version は、Excelのバージョン番号を文字列で返します。それを数値化するために '.to_f' を付けています。このバージョン番号が 12.0 以上なら Excel2007以降、未満なら Excel2003以前です。

　ちなみに、Excel2007以降で *.xlsx のファイルを保存する場合は

   wb.SaveAs filename, 51  # 51=XlOpenXMLWorkbook

とします。

　　　　

　なお、exlapライブラリを利用している場合は、*.xls, *.xlsx, *.xlsm および *.xml に限りますが、ファイル名の拡張子に応じて適当に処理するので、上のようなことを意識せず

   wb.save filename

とするだけで大丈夫です。

　*.xml は、いわゆる「xmlスプレッドシート(xmlss)」です。Excel2003以降であれば、このファイルをクリックするとExcelが起動します。

　　　　

---

　ワークシートのセルに書き込まれているデータを読む方法は、書込みの場合から簡単に類推できるものです。例えば

   a = ss[1,1]

とすれば、A1欄のデータを変数 a に代入することになります。

   a = ss.Range("A1").Value

あるいは

   a = ss.Cells.Item(1,1).Value

としても同じです。

　セルに何もデータがない時は、値として nil が得られます。

　　　　

　そのほか、ワークシートに書き込まれているデータを配列で返す to_a メソッドも設けてあります。

   a = ss.to_a
   a.each {|row|
     puts row.join("\t")
   }

のようにすれば、ワークシートの中身をタブ区切りテキストで出力できます。

　以下、ワークブック中の全ワークシートのデータをタブ区切りテキストで出力するプログラムを掲げておきます。セル内の改行は「\n」の２文字に変換します。

　　　　

   −−−− sample03.rb ここから
   #! ruby -Ks
   require "exlap"
   filename = "book1.xls"
   xl = Exlap.new
   wb = xl.book_open filename  # ワークブックを開く
   out_str = ""
   wb.each {|ss|  # ssには各ワークシートオブジェクトがセットされる
     out_str += "** #{ss.Name}\n"
     ary = ss.to_a
     ary.each {|row|
       out_str += row.join("\t").gsub(/\n/, "\\n") + "\n"
     }
     out_str += "\n"
   }
   xl.quit
   File.open("book1.txt", "w") {|file|  file.write out_str}
   −−−− sample03.rb ここまで

　　　　

［補足１］　セル結合とデータの読込み

　厳密にいうと、ss[1,1] と ss.Range("A1").Value には違いがあります。

　例えば、A1とB1がセル結合している場合、実際にデータが格納されているのはA1欄で、B1は空欄です。しかし、

　ss[1,2] は nil でなく ss[1,1] と同じ値を返します。

　一方、ss.Range("B1").Value で値を取り出すと nil になります。

　つまり ss[y,x] の場合は、セル結合されている総てのセルが、あたかも同じ値を保持しているかのように値を返します。

　それに対し、ss.Range(……).Value の方は、セル結合されているか否かに影響されずに値を返します。

　ss.Cells.Item(y,x).Value は、Rangeの場合と同じ振る舞いをします。

　なお、ss.to_a で得られる配列は、セル結合の有無に関係なく、空欄は nil になります。Rangeの場合と同じです。

　　　　

　ちなみに、A1とB1のセル結合を設定するためには次のようにします。

   ss.Range("A1:B1").MergeCells = true

結合を解除する時は

   ss.Range("A1:B1").MergeCells = false

と書きます。

　　　　

［補足２］　to_a と range_to_a の違い

　ワークシートに書き込まれているデータを配列で返す to_a については前述しましたが、同じような働きをする range_to_a というメソッドを設けてあります。

　to_a は、実際に利用されている領域を広く捉えて配列に変換します。それに対し、range_to_a は、狭い範囲で捉えます。何もデータがない行と列を存在しないかのように扱います。

　例えば、ワークシート中のA100欄(100行・1列)だけにデータがあるとします。この場合、to_aは100行分の配列を返しますが、range_to_aは、1〜99行を無視して、１行だけの配列を返します。

　　　　

---

(1) 複数のワークブックを１つづつ処理

　Excelは、複数のワークブックを扱うことができますが、exlap.rbで生成したオブジェクトでも複数のワークブックを扱えます。

　ワークブックを順番に１つづつ扱えば事足りる場合は、rubyらしい次のようなスタイルで記述できます。この記述ではquitの終了処理を書かなくても大丈夫です。

　下の sample04.rb は、２つのワークブックの内容をタブ区切りテキストの形で標準出力に出力します。

　　　　

   −−−− sample04.rb ここから
   #! ruby -Ks
   require "exlap"
   Exlap.new("book1.xls", "book2.xls") {|wb|
     print "!!#{wb.name}\n"  # ワークブックの名前
     wb.each {|ss|  # ssには各ワークシートオブジェクトがセットされる
       print "** #{ss.Name}\n"  # ワークシートの名前
       ary = ss.to_a
       ary.each {|row|
         puts row.join("\t").gsub(/\n/, "\\n")
       }
       print "\n"
     }
   }
   −−−− sample04.rb ここまで

　　　　

　Exlap.new(file1, file2, ……) {|wb| ………… }

というブロック付きのスタイルで書くと、file1, file2, ... のファイル(ワークブック)が１つづつ開かれて、ワークブックオブジェクト(Exlwb)が変数 wb にセットされます。そして、指定された複数のワークブックについて総ての処理が終わると、Excel.ApplicationがQuitされます。つまりExcelが終了します。

　ARGVにワークブックの名前がセットされている場合は

   Exlap.new(*ARGV) {|wb|  ………… }

のように記述します。

　　　　

　なお、一つのワークブックの処理が終了する度にそれをクローズしたい時は、最後の '}' の直前に「wb.close」という１行を置きます。

　最後の '}' のところで、開いているワークブックを総てクローズしてからExcelを終了するので、逐一「wb.close」を置かなくても支障はないと思いますが、多数のワークブックを扱う時は、一つづつクローズした方が無難かもしれません。

　　　　

(2) 複数のワークブックを並行して処理

　複数のワークブックを順番に１つづつではなく、並行して同時に処理したい場合があります。例えば、book1.xls の Sheet1 を book2.xls の Sheet1 にコピーしたいような場合です。

　こうしたケースの処理は、例えば、下のように記述します。

　　　　

   −−−− sample05.rb ここから
   #! ruby -Ks
   require "exlap"
   xl = Exlap.new
   wb1 = xl.book_open "book1.xls"
   wb2 = xl.book_open "book2.xls"
   ss1 = wb1.fes
   ss2 = wb2.fes
   ss1[1,1] = "経済成長という病"  # A1への書き込み
   ss1[1,2] = "平川　克美"  # B1への書き込み
   addr = ss1.UsedRange.Address  # 実際にデータがある領域の番地を得る
   ss1.Range(addr).Copy(ss2.Range(addr))  # ワークシートss1をss2にコピー
   wb1.save
   wb2.save
   xl.quit
   −−−− sample05.rb ここまで

　　　　

　「addr = ss1.UsedRange.Address」は、ss1において実際に利用されている領域の番地を得るもので、サンプルでは "$A$1:$B$1" が得られます。

　「ss1.Range(addr).Copy(ss2.Range(addr))」は、ss1の内容をss2にコピーするものです。書かれているデータだけでなく、セルの大きさ、文字の色や大きさなどもコピーします。

　　　　

　それから、後述の opens_once メソッドを用いると、上のsample05.rbを下のように書くこともできます。

　　　　

   −−−− sample05b.rb ここから
   #! ruby -Ks
   require "exlap"
   xl = Exlap.new
   xl.opens_once("book1.xls", "book2.xls") {|wb1, wb2|
     ss1 = wb1.fes
     ss2 = wb2.fes
     ss1[1,1] = "経済成長という病"  # A1への書き込み
     ss1[1,2] = "平川　克美"  # B1への書き込み
     addr = ss1.UsedRange.Address  # 実際にデータがある領域の番地を得る
     ss1.Range(addr).Copy(ss2.Range(addr))  # ワークシートss1をss2にコピー
     wb1.save
     wb2.save
   }
   xl.quit
   −−−− sample05b.rb ここまで

　　　　

---

　exlap.rbの中で独自に設けたセルとその集合処理用メソットを紹介します。

　以下において、変数 ss は、ワークシートオブジェクトを示します。

　なお、メソッド名として range_to_a のように先頭に「range_」が付くものは、引数として一定の領域を示すRangeオブジェクト(ss.Range("A1:C3") など)を渡すタイプのメソッドです。引数が省略された場合は、ss.UsedRange が渡されたものとみなされます。つまり、実際に利用されている領域全体が渡されることになります。

　　　　

(1) セルオブジェクトを返す cell(y,x)

　ss.cell(1,1) は、ss.Cells.Item(1,1) または ss.Range("A1") と同値です。

　　　　

(2) シートの各セルの値を２次元配列で返す to_a

　これは既に言及しましたが、ワークシートに書かれているデータを２次元配列で返すものです。始点は、絶対番地A1とします。

　　　　

(3) シート中の所与の領域のセルの値を２次元配列で返す range_to_a

　利用されている領域(なるべく狭い範囲で捉える)のデータを配列で返します。

　引数として領域が与えられると、その領域のデータを配列で返します。例えば

   rng = ss.Range("A1:C3")
   a = ss.range_to_a(rng)

とすれば、A1〜C3の３×３の配列が返ります。

　引数が省略された場合は ss.UsedRange が引数として与えられたものとみなされます。

　始点がA1であるとは限らない点が to_a と異なります。例えば、

   rng = ss.Range("B2:D4")
   a = ss.range_to_a(rng)

の場合、４×４の配列でなく、おそらく３×３の配列を返します。おそらくというのは、第１列目(左端の縦の列び)に何かデータがあると、３行×４列になる可能性があるからです。

　　　　

(4) 所与の領域の番地を数値で返す range_address

◇引数として与えられた領域のセル番地(絶対番地の値)をy,x座標の値(配列)で返します。
　領域の始点と終点が返されるので、４つの値が得られます。例えば、

   y1,x1,y2,x2 = ss.range_address(ss.Range("A1:C3"))

とした場合、y1,x1 にはそれぞれ 1 が、y2,x2 にはそれぞれ 3 が代入されます。

◇引数を省略すると ss.UsedRange が指定されたものとみなされます。

◇引数に ss.Range("C4") のようにセル１つが与えられた時は、その番地(２つの要素からなる配列)が返されます。例えば、
　ss.range_address(ss.Range("C4")) の戻り値は [4, 3] です。

◇ss.range_address("青森") のように、引数に文字列が与えられると、その文字列が書き込まれている最初のセルから ss.UsedRange の終点までの番地が返されます(４つの値として返されます)。文字列の他に正規表現、nilを引数にすることが可能です。この辺の仕様は後述の findf と同じなので、そちらを参照して下さい。ただし、findfの第２引数をrange_addressの方では指定できないので注意して下さい。

◇このrange_addressの第２引数を指定すると、絶対番地でなく相対番地を返します。より正確には、:ABSOLUTE 以外のもの(nil とか :RELATIVE など何でもよい)を第２引数として与えれば、相対番地が返されます。例えば、

   y1,x1,y2,x2 = ss.range_address(ss.Range("B2:C3"), :RELATIVE)

のように指定します。この場合は [1,1, 2,2] が返されます。

◇番地が取得できない時は、空配列 [] を返します。

　　　　

(5) 所与の領域の終点の番地を数値で返す range_last

◇range_address が返す値の後半の２つ(終点のy,x)を返します。

   y,x = ss.range_last(ss.Range("A1:C3"))

とすれば、y,x にそれぞれ 3 が代入されます。

◇引数を省略すると ss.UsedRange が指定されたものとみなされます。

   y,x = ss.range_last

とすれば、ワークシート ss の中で、利用されている領域の終点番地を得られることになります。

◇第２引数として :ABSOLUTE 以外を指定すれば、相対番地が返されます。

◇番地が取得できない時は、空配列 [] を返します。

　　　　

(6) 所与の領域の始点の番地を数値で返す range_start

◇range_address が返す前半の２つ(始点のy,x)を返します。

   y,x = ss.range_start(ss.Range("A1:C3"))

とすれば、y,x にそれぞれ 1 が代入されます。

◇引数を省略すると ss.UsedRange が指定されたものとみなされます。

◇第２引数として :ABSOLUTE 以外を指定すれば、相対番地が返されます。

◇番地が取得できない時は、空配列 [] を返します。

　　　　

(7) 所与の領域の始点と終点のセルオブジェクトを返す range_pole

◇ss.range_pole(rng) は、領域 rng の始点(左上端)のセルおよび終点(右下端)のセルの２つを配列として返します。番地でなくセルオブジェクトを返します。

   cell1, cell2 = ss.range_pole(ss.Range("A1:C3"))

とした場合、cell1 は ss.Range("A1")、cell2 は ss.Range("C3") と同等です。

◇引数が省略されると ss.UsedRange が指定されたものとみなされます。

◇セルオブジェクトが取得できない時は、空配列 [] を返します。

◇range_pole には range_pair および rp の別名を割り当ててあります。

　　　　

(8) 利用されている領域全体を返す entire_range

　ss.entire_range は、利用されている領域全体を返しますが、始点をA1とする領域を返します。ss.UsedRange は必ずしもA1を始点としない点で、これと異なります。

　例えば、B2とC3の２つのセルだけにデータがある場合、ss.entire_range は、A1〜C3の３×３の領域を返しますが、ss.UsedRange は、B2〜C3の２×２の領域を返します。

　　　　

(9) 数値の番地で領域を指定するための rip_range

◇Rangeプロパティを用いて、A1〜C3の３×３の領域を変数に代入するには

   rng = ss.Range("A1:C3")

と書きますが、数値の番地で指定するには

   rng = ss.Range(ss.cell(1,1), ss.cell(3,3))

と書きます。しかし、これだと面倒なので rip_range(別名 rr)を用いて

   rng = ss.rip_range(1,1, 3,3)

あるいは

   rng = ss.rr(1,1, 3,3)

と書けるようにしてあります。

◇ss.rr(1,1) のように始点の番地だけ指定し、終点を省略すると、終点として ss.UsedRange の終点を用います。つまり、実際に利用されている領域の右下端が終点となります。

◇「ss.rr(1,1, "備考")」のように書くと、A1欄から「備考」と書かれたセルまでの領域を返します。

　一方、「ss.rr("氏名", 5,5)」とすれば、「氏名」と書かれたセルからE5までの領域を指します。

　「ss.rr("氏名", "備考")」といった書き方もできます。「氏名」の欄から「備考」の欄までの領域を返します。

　ss.rr("氏名") とすれば、「氏名」と書かれたセルから ss.UsedRange の終点までの領域を返します。

◇引数を総て省略して ss.rr とした場合は ss.UsedRange と同じ領域を返します。

◇領域の取得に失敗した時は nil を返します。

　　　　

(10) 指定領域のセルの幅と高さを自動調節する range_autofit

◇range_autofit は、指定された領域にあるセルの幅と高さを自動調節します。例えば、

   rng = ss.Range("A1:C3")
   ss.range_autofit(rng)

とすると、A1〜C3の３×３の領域にあるセルについて、そこに書き込まれている文字に合わせて、セルの幅と高さを調節します。

◇引数が省略された場合は ss.UsedRange が引数として与えられたものとみなされます。

◇このメソッドが有効に働くのは、セルに書き込まれているデータが一定の長さに収まっている場合です。行の折り返しが必要になるような長いデータの場合は、自動調節でなく桁数指定した方がいいと思います。

　　　　

(11) 指定領域に細線の罫線を引く range_allbox

◇range_allbox は、指定の領域のセルを総て細い罫線で囲みます。例えば、

   rng = ss.Range("A1:C3")
   ss.range_allbox(rng)

とすれば、A1〜C3の３×３の領域が総て罫線で囲まれます。

◇引数が省略された場合は ss.UsedRange が引数として与えられたものとみなされます。

◇第２引数を与えると、罫線の種類、太さ、色の指定を行うことができます。第２引数は、ハッシュで与えます。

   ss.range_allbox(rng, {'LineStyle'=>XlDot, 'Weight'=>XlMedium})

とすると、点線・太線の罫線を引くことになります。色の指定は 'ColorIndex' で行いますが、ここでは省略しています。詳細は後述の解説を参照して下さい。

　　　　

(12) 指定領域に罫線を引く(外枠は太線・内側の線は細線) range_doublebox

　range_doublebox は、前述の range_allbox とほぼ同じですが、外枠が太線になる点だけが異なります。

　第２引数をハッシュの形で与えることができる点も range_allbox と同じです。

　　　　

(13) 指定領域の罫線を消去する range_clearbox

　ss.range_clearbox(rng) とすると、与えられた領域 rng の罫線を消去します。

　range_allbox または range_doublebox で引いた罫線を消去する時などに利用します。

　　　　

(14) 所与のkeyと値が照合する最初のセルオブジェクトを返す findf(key)

◇例えば、B2欄に「青森」と書き込まれている場合、ss.findf("青森") は、ss.Range("B2") と同値です。

　findf は、 find first のつもりで、指定されたkeyが最初にみつかったところのセルオブジェクトを返します。該当のkeyが複数あっても、最初の１つを返します。

　検索対象は、ss.UsedRange です。

◇keyとして文字列のほか、正規表現を指定することができます。ss.findf(/^青森$/) のように指定できます。

◇keyとして nil も指定可能です。何も書き込まれていない最初のセルは、ss.findf(nil) で得ることができます。

◇文字の色等の属性で検索することもできます。例えば、

　ss.findf(5, 'Font.ColorIndex') は、最初の青色のセルオブジェクトを返します。

　ss.findf(true, 'Font.Bold') は、最初の太字のセルを返します。

◇結局、keyで指定できるのは、文字列、正規表現、数値、nil、true、false です。

◇正規表現の場合、セルに書き込まれているデータを文字列に変換した上でパターンマッチングを行います(数値も文字列にした上で照合します)。それ以外は単純に == が成立するか否かで照合します。

◇照合するものがみつからない時は nil を返します。

　　　　

(15) 照合する複数のセルを配列で返す finda

　findfが最初にみつかった一つを返すのに対し、findaは、照合する複数のセルオブジェクトを配列で返します。

　照合するものがなければ、空配列 [] を返します。

　また、ブロック付きで呼び出すこともできます。その場合の戻り値は常に空配列です。

　例えば、ss.UsedRange領域の空欄セルを総て "::" に変換するには次のように書きます。

     ss.finda(nil) {|c|  c.Value = "::"}

　　　　

(16) ハイパーリンクを設定する hyperlink

◇hyperlink は、指定のセルにハイパーリンクを設定するためのものです。例えば、次のように用います。

   ss.hyperlink("A1", "http://jp.rubyist.net/magazine/")

　上の例を実行すると、A1欄に http…… が書き込まれますが、これは単なる文字列ではなくハイパーリンクなので、後でExcelを立ち上げてこのA1欄にカーソルを合わせ、右クリックメニューから「ハイパーリンクを開く」を選ぶと、ブラウザが起動して該当のホームページが開きます。

◇第１引数の "A1" は、1,1 のように数値で指定することもできます。つまり

   ss.hyperlink(1,1, "http://jp.rubyist.net/magazine/")

と書くことができます。

◇Excelを起動してA1欄を見た時に、http……ではなく、そのサイトが何なのかを示す文字列を表示したければ、その文字列を最後の引数として与えます。例えば、

   ss.hyperlink("A1", "http://jp.rubyist.net/magazine/", "Rubyist Magazine")

のようにします。

◇ハイパーリンクでリンクをはれるのは、ホームページに限りません。ハードディスク上のファイルにリンクをはることもできます。

   ss.hyperlink("A2", "hogehoge.pdf", "ホゲホゲPDF")

としておけば、「ハイパーリンクを開く」で hogehoge.pdf を開けます(通常はアクロバットリーダーが起動します)。

　　　　

(17) 所与の領域のセルを一つづつ返す range_each

◇range_eachは、必ずブロック付きで用います。

◇引数として与えられた領域のセルオブジェクトを一つづつ返します。例えば

   ss.range_each(rng) do |cell|
     cell.Value = "abc"
   end

とした場合、領域rngの各セルに "abc" が書き込まれます。

◇引数rngを省略したりnilを指定すると ss.UsedRange が指定されたものとみなされます。

◇第２引数として :CELL 以外のもの(例えば :YX とか :RC など)を指定すると、セルオブジェクトでなく、セルの番地を数値で返します。例えば

   rng = ss.Range("A1:C3")
   ss.range_each(rng, :YX) do |y, x|
     ss.cell(y,x).Value = "abc"  if x == 2
   end

とすれば、領域内のＢ列のセルに "abc" を書き込むことになります。y,xは、絶対番地を数値にしたものです。

(18) ワークシート内の全セルが空であるかを確認する empty?

◇ワークシート ss が空のとき、ss.empty? が true を返します。これは、値(Value)および数式(Formula)が書き込まれたセルが一つもないことを意味します。

◇値または数式が書き込まれているセルが一つでもあれば、ss.empty? は false を返します。

(19) ワークシートが空であるかを確認する empty_sheet?

◇ワークシート ss が空のとき、ss.empty_sheet? が true を返します。これは、前述の empty? が true であることに加えて、次の値がいずれも0であることを意味します。
　　　ChartObjects.Count, Shapes.Count, Hyperlinks.Count,
　　　QueryTables.Count, Scenarios.Count

◇ワークブック wb の wb.empty_sheet_names は、ss.empty_sheet? が true となるワークシート(複数)の名前を配列で返します。ss.empty? が true となるワークシートの名前ではありません。

(20) 配列の内容をワークシートに貼り付ける from_a

◇from_aは、指定番地以降に配列の内容を貼り付けます。
　　　ary = [%w(cat dog pig), %w(red black white green), %w(banana pine)]
　　　ss.from_a("B2", ary)
　この場合、B2:E4の領域にデータが書き込まれます。

◇上述のaryは、１行目が３列、２行目は４列、３行目が２列からなっていますが、これを３行×４列とみなして、一括してワークシートに貼り付けます。ワークシート上の該当領域(B2:E4)は、上書きされます。１行目の４列の欄は、空欄(nil)になります。

◇番地は２つの数値で指定することもできます。「ss.from_a(2,2, ary)」のように記述できます。

◇番地の指定を省略して「ss.from_a(ary)」とすると、A1欄が指定されたものとみなされます。

◇from_aの戻り値は、貼り付けが行われたワークシート上の該当領域(Rangeオブジェクト)です。何らかの理由で貼り付けが行われなかった時は nil を返します。
　　　rng = ss.from_a("B2", ary)
　　　p rng.Address　# => "$B$2:$E$4"

◇from_aをブロック付きで呼び出すと、ワークシート上の座標(y,x(数値))および配列の該当要素の値(val)の３つを参照できます。
　　　ss.from_a("B2", ary) do |y, x, val|
　　　　　next　if val == nil　# 空欄はスキップ
　　　　　if val.to_s =~ /^0\d+$/
　　　　　　　ss.cell(y,x).NumberFormatLocal = val.gsub(/\d/, "0")
　　　　　end
　　　end
　上は、いわゆる「001問題」の解決方法の一つです。この場合のvalは、セルの値ではなく、配列の該当要素の値です。

◇from_aに与えることのできる配列は、前述の例のような２次元配列か、もしくは、１行だけの１次元配列です。配列でなく文字列を与えると、貼り付けは行われません。

(21) csvファイルなどの内容をワークシートに貼り付ける from_f

◇from_fは、指定番地以降にcsvファイルなどの内容を貼り付けます。例えば、「ss.from_f("B2", "test.csv")」などのように用います。

◇from_fの仕様は from_a と同じです。配列の代わりにファイル名を指定する点だけが異なります。

◇ファイル名の拡張子が ".csv" 以外の場合、例えば ".txt" などであれば、タブ区切りテキストとして扱われます。

◇csvファイルの読み込みには、rubyの標準添付ライブラリcsvを用いますが、FasterCSVがrequireされていれば、FasterCSVの方を用います。ただし、rubyのバージョンが1.9系であれば FasterCSV を用いず、標準添付ライブラリcsvを用います。

　　　　

　　　　

　以上は、module Exlsheet で定義されているメソッドです。いずれも ss.cell(1,1) のように、「ss.」の後に記述して用いるものです。

　　　　

---

　前述の独自メソッドを用いる例、また、必要に応じてライブラリexlap.rbを用いない場合の記述(以下「VBA類似記述」)の例を対比しながら、いくつか基本的なサンプルを掲げます。

　　　　

(1) 文字の色・大きさ・書体・下線・太さ・斜体の設定

　セルオプジェクトに対するFont指定の例を示します。

　　　　

   −−−− sample06.rb ここから
   #! ruby -Ks
   require "exlap"
   Exlap.new("test.xls") {|wb|
     ss = wb.fes
     ss[1,1] = "死体の経済学"  # A1への書き込み
     ss.cell(1,1).Font.ColorIndex = 5  # 色を青に設定。元は1の黒
     ss[1,2] = "窪田　順生"  # B1への書き込み
     ss.cell(1,2).Font.Size = 20  # 文字サイズを20ポイントの大きさに設定。元は11
     ss.Range("C1").Value = "小学館"
     ss.Range("C1").Font.Name = "ＭＳ 明朝"  # 元は "ＭＳ Ｐゴシック"
     ss.Range("A2").Value = "円と日本経済の実力"
     ss.Range("A2").Font.Underline = XlUnderlineStyleDouble  # 二重下線
     ss.Range("B2").Value = "鈴木　淑夫"
     ss.Range("B2").Font.Bold = true  # 太字
     ss[3,1] = "This is a pen."
     ss.Range("A3").Characters(11,3).Font.Italic = true # penを斜体に
         # ↑ penは文字列中で 11文字目から3文字分に相当
     ss.range_autofit  # セルの高さと幅を自動調節
     wb.save
   }
   −−−− sample06.rb ここまで

　　　　

a. 色の指定に使う番号

　ss.Range("A1").Font.ColorIndex に設定する色の値は下のとおりです。

　　　　

黒: 1	マゼンタ: 7
白: 2	シアン: 8
赤: 3	茶: 9
明るい緑: 4	緑: 10
青: 5	紺: 11
明るい黄色: 6	うぐいす: 12

　　　　

b. 文字のサイズ

　ss.Range("A1").Font.Size は、文字の大きさを指定します。単位はポイント。１なら１ポイント、100なら100ポイント。

　デフォルトは 11

　1ポイントは1／72インチ。1インチが約25.4mmなので、11ポイントは約3.9mmとなります。

　ちなみに、セルの高さの標準値は 13.5ポイント(約4.8mm)のようです。

　　　　

c. フォントの種類

　ss.Range("A1").Font.Name は、フォントの種類を指定します。「ＭＳ 明朝」や「ＭＳ ゴシック」などが一般的。

　デフォルトは「ＭＳ Ｐゴシック」

　　　　

d. アンダーライン

　ss.Range("A1").Font.Underline は、下線の種類を指定します。次の5種類が指定可能。

　　　　

普通の下線	XlUnderlineStyleSingle	2
二重下線	XlUnderlineStyleDouble	-4119
会計用の下線	XlUnderlineStyleSingleAccounting	4
会計用の二重下線	XlUnderlineStyleDoubleAccounting	5
下線の消去	XlUnderlineStyleNone	-4142

　　　　

e. 太字の指定

　ss.Range("A1").Font.Bold = true ← 太字

　ss.Range("A1").Font.Bold = false ← 太字の解除

　　　　

f. 斜体の指定

　ss.Range("A1").Font.Italic = true ← 斜体

　ss.Range("A1").Font.Italic = false ← 斜体の解除

　　　　

(2) ハイパーリンク、セルの幅と高さの自動調節、罫線

　ハイパーリンクの設定方法、セルの幅と高さを自動調節する方法、罫線の引き方を含むサンプルを掲げます。

　　　　

   −−−− sample07.rb ここから
   #! ruby -Ks
   require "exlap"
   Exlap.new("test.xls") {|wb|
     ss = wb.fes
         # ↓ ホームページへのハイパーリンクを書き込む
     ss.hyperlink(1,1, "http://www.mhlw.go.jp/")
     ss.hyperlink(1,2, "http://www.rubylife.jp/ini/index.html", "Ruby入門")
     ss.hyperlink("A2", "http://www13.plala.or.jp/mugi_cyan/excel/index.html",
         "むぎちゃんのExcelVBA入門")
     ss.hyperlink("B2", "http://jp.rubyist.net/magazine/", "Rubyist Magazine")
         # ↓ A1:B2のセルの幅と高さを自動調節
     rng = ss.Range("A1:B2")
     ss.range_autofit(rng)
         # ↓ これまで設けた２×２のセルに罫線を引く。線は総て同じ太さ
     ss.range_allbox(rng)
     wb.save
   }
   −−−− sample07.rb ここまで

　　　　

　上のサンプルについて、コメントを付けてあるので、特に解説を加える必要はないと思います。

　以下では、VBA類似記述で書くとすればどうなるかを参考まで記します。

　　　　

a.　ハイパーリンク設定のVBA類似記述

　ハイパーリング設定をVBA類似の形式で記述すると次のようになります。ハッシュ(連想配列)を用います。

   ss.Hyperlinks.Add({'Anchor'=>ss.Range("A4"),
       'Address'=>"http://www.hogehoge.com/",
       'TextToDisplay'=>"ホゲホゲ"})

　上の {……} の部分はハッシュ(連想配列)です。したがって、次のように書いても同じです。

   hs = {}
   hs['Anchor'] = ss.Range("A4")
   hs['Address'] = "http://www.hogehoge.com/"
   hs['TextToDisplay'] = "ホゲホゲ"
   ss.Hyperlinks.Add(hs)

　　　　

b. セルの幅と高さの自動調節のVBA類似記述

　ss.range_autofit(ss.Range("A1:C3")) をVBAに即した記述にすると次のとおりです。

   rng = ss.Range("A1:C3")
   rng.Columns.each {|col|  col.AutoFit}
   rng.Rows.each {|row|  row.AutoFit}

　　　　

c. VBA類似記述の罫線の引き方

　ss.range_allbox(ss.Range("A1:C3")) をVBAに即した記述にすると次のとおりです。

   bd = ss.Range("A1:C3").Borders
   bd.LineStyle = XlContinuous  # 線の種類を実践
   bd.Weight = XlThin  # 線の太さを細線
   bd.ColorIndex = XlAutomatic  # 線の色を自動設定

　　　　

　range_doubleboxをVBA類似記述にする場合は、枠の上・下・左・右などを逐一設定します。例えば、枠の上の罫線を設定するのは次のようにします。

   rng = ss.Range("A1:C3")
   bd1 = rng.Borders(XlEdgeTop)  # XlEdgeTopが上端の指定
   bd1.LineStyle = XlContinuous  # 線の種類を実践
   bd1.Weight = XlThick  # 線の太さを極太線
   bd1.ColorIndex = XlAutomatic  # 線の色を自動設定

　　　　

　上は上端の設定ですが、同じような記述を下・左・右などについても行います。

　以下に、罫線に関連する定数を掲げておきます。

　なお、罫線の色(ColorIndex)の指定は、自動設定が XlAutomatic(-4105)ですが、それ以外は Font.ColorIndex と同じ数値を用います。赤が3、青が5などです。

　　　　

［参考］

○　罫線の種類 例: rng.Borders.LineStyle=XlContinuous

実線	XlContinuous	1
破線	XlDash	-4115
一点鎖線	XlDashDot	4
二点鎖線	XlDashDotDot	5
点線	XlDot	-4118
二重線	XlDouble	-4119
斜め斜線	XlSlantDashDot	13
罫線消去	XlLineStyleNone	-4142

　　　　

○　罫線の太さ 例: rng.Borders.Weight=XlThin

極細線	XlHairline	1
細線	XlThin	2
太線	XlMedium	-4138
極太線	XlThick	4

　　　　

○　罫線の位置 例: bd=rng.Borders(XlEdgeTop)

範囲の上端	XlEdgeTop	8
範囲の下端	XlEdgeBottom	9
範囲の左端	XlEdgeLeft	7
範囲の右端	XlEdgeRight	10
範囲の内側の垂直線	XlInsideVertical	11
範囲の内側の水平線	XlInsideHorizontal	12
左上から右下への斜線	XlDiagonalDown	5
左下から右上への斜線	XlDiagonalUp	6

　　　　

---

　特定のワークシートを選ぶ select_sheet(別名ss)、ワークシートを追加する add_sheet などの独自メソッドがあります。それらについて紹介します。

　なお、以下では wb がワークブックオブジェクト(Exlwb)を示します。

　　　　

(1) 特定のワークシートを選択する select_sheet

　ss = wb.select_sheet(1) ← １番のワークシートを選択

　ss = wb.select_sheet("Sheet1") ← ワークシート名が Sheet1 のシートを選択

　戻り値は、ワークシートオブジェクトです。

　なお、select_sheetにはssという別名が定義されています。「ss = wb.ss(1)」のように書くことができます。

　　　　

(2) 他のワークシートへの移動 offset_sheet

　ss がワークシートオブジェクトである場合、

　ss2 = wb.offset_sheet(ss, 1) は、次の番号のワークシートを返します。

　ss3 = wb.offset_sheet(ss, -1) なら１つ前の番号のワークシートを返します。

　第２引数(上の 1 や -1)は任意の整数値を与えることができますが、該当のシートがない時は nil が返されます。

　　　　

(3) 最後のワークシートを返す last_sheet

　ss = wb.last_sheet は、最後の番号のワークシートオブジェクトを返します。

　　　　

(4) 一番最後にワークシートを追加する add_sheet

　ss = wb.add_sheet は、ワークブックの最後に新たなワークシートを追加して、そのワークシートオブジェクトを返します。

　　　　

(5) 指定シートのところにワークシートを挿入する insert_sheet

　ss = wb.insert_sheet(2) は、２番目のワークシートのところに新たなワークシートを挿入します。これまで２番だったシートは３番となり、新しいシートが２番になります。

　ss = wb.insert_sheet("Sheet3") は、シート名が Sheet3 のところに新しいワークシートを挿入します。

　戻り値は新しいワークシートのオブジェクトです。

　　　　

(6) ワークシートを削除する delete_sheet

　wb.delete_sheet(1) ← １番のワークシートを削除

　wb.delete_sheet("Sheet1") ← シート名が Sheet1 のワークシートを削除

　ワークブックの中にワークシートが１枚しか残っていない時は、そのシートを削除しようとしても削除されません。

　　　　

(7) ワークシートの名前一覧を返す sheet_names

　sheet_names は、ワークシートの名前一覧を配列(文字列からなる配列)で返します。

   ary = wb.sheet_names
   ary.each {|sn|  puts sn}

　　　　

(8) ワークシートの番号を返す sheet_number

　ss がワークシートオブジェクトである場合、

　wb.sheet_number(ss) は、そのシートの番号を返します。ワークブック内で、何番目のシートかを示す数値です。

　　　　

(9) 空のワークシートの名前一覧を返す empty_sheet_names

　empty_sheet_names は、空のワークシートの名前一覧を配列(文字列からなる配列)で返します。

　空でないワークシートの名前一覧を得たい時は、次のようにすればＯＫです。

   ary = (wb.sheet_names - wb.empty_sheet_names)

　　　　

(10) 最初の空のワークシートを返す first_empty_sheet

　「ss = wb.first_empty_sheet」は、ワークブック内で最初にみつかった空のワークシートを返します。短縮型の別名を用いて「wb.fes」と書くこともできます。

　もし空のシートがなければ、最後に新たなワークシートを設けて、それを返します。つまり wb.add_sheet が実行されます。

　戻り値はワークシートオブジェクトです。

　　　　

(11) 各ワークシートを選択する each

　ワークブックに含まれるワークシートを１枚づつ取り出すのに wb.each を使うことができます。

   wb.each {|ss|  p ss.range_to_a}

のような記述が可能です。

　　　　

---

　他にもいくつか独自メソッドがあるので紹介します。

　　　　

(1) ワークブックを閉じる close

　wbがワークブックオブジェクト(Exlwb)である場合、それを閉じる時に wb.close と記述します。

　　　　

(2) ワークブックの保存 save

　wbがワークブックオブジェクト(Exlwb)である場合、

　wb.save とすれば、上書き保存されます。より正確にいうと、ワークブックを開く時に指定したファイル名で保存されます。

　wb.save "test09.xls" のようにファイル名を指定した時は、test09.xls という名前で保存されます。いわゆる「名前を付けて保存」です。

　この「名前を付けて保存」の場合、ファイル名の拡張子として .xls が前提とされます。他の拡張子が指定されても、.xls に変更されてしまいますので注意して下さい。ただし、Excel2007の時は .xlsx および .xlsm も指定可能です。

　CSVなど別の形式で保存したい場合は、

   wb.obj.SaveAs({'FileName'=>"A:\\test.csv", 'FileFormat'=>XlCSV})

のようにVBA類似記述を用いて下さい。

　　　　

(3) ワークブックを開く book_open

   xl = Exlap.new
   wb = xl.book_open "book1.xls"

とすれば、book1.xls というファイル名でワークブックをオープンします。

　ワークブックを読取り専用でオープンしたい時は、ハッシュを使って次のようにします。

   wb = xl.book_open({'Filename'=>"book1.xls", 'ReadOnly'=>true})

　ハッシュで指定できるオプションには、Filename, ReadOnly の他に、UpdateLinks, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Delimiter など、いろいろあるようです。VBAのサイトなどを参照して下さい。

　　　　

(4) ワークブックを閉じる book_close

　wbがワークブックオブジェクト(Exlwb)であるとき、

　xl.book_close(wb) とすれば、wbを閉じることになります。

　これは、wb.close と書くのと同じことです。

　　　　

(5) ワークブックを選択する select_book

　wb = xl.select_book(1) ← １番のワークブックを選択

　wb = xl.select_book("book2.xls") ← book2.xlsを選択

　引数としてワークブックの名前を指定する場合は、フルパス名でなく、ファイル名本体(拡張子を含む)で指定します。

　　　　

(6) ワークブックの名前一覧を配列で返す book_names

　book_names は、ワークブックの名前一覧を配列(文字列からなる配列)で返します。フルパス名でなく、ファイル本体(拡張子を含む)です。

   ary = xl.book_names
   ary.each {|wn|  puts wn}

　　　　

(7) 警告メッセージの画面表示を切り替える display_alert

   val = xl.display_alert(false)

とすれば、警告メッセージの画面表示をoffに切り替えます。val には、切り替える前の値(true または false)がセットされます。したがって、次に

   xl.display_alert(val)

とすれば、切り替える前の元の状態に戻すことができます。

　また、このメソッドをブロック付きで呼び出すこともできます。これにより、一時的に警告の画面表示をoffにしたい場合に次のように書けます。

     xl.display_alert(false) do
       ss = wb.ss("Sheet1")
       ss.Delete
     end

上が実行された後は、警告の画面表示が元の状態(trueまたはfalse)に戻ります。元の状態が何であるかを気にしないで済みます。

　　　　

(8) 各々のワークブックを１つづつ選択する each

   xl.each {|wb|  …………}

とすれば、既に開いているワークブックを１つづつ処理できます。

　　　　

(9) Exlap.new(……) {|wb| …………} のパターンおよびopensメソッド

   Exlap.new("book1.xls", "book2.xls") {|wb|  …………}

と記述することで、複数のワークブックを１つづつ処理できることは前述しましたが、"book1.xls" や "book2.xls" のところにハッシュを書くことによって、読取り専用モードでワークブックを開くといったことが可能になります。例えば、次のように書きます。

   Exlap.new({'Filename'=>"book1.xls", 'ReadOnly'=>true}, "book2.xls") {|wb|
     wb.each {|ss|  p ss.to_a}
   }

この場合、book1.xls は読取り専用、book2.xls の方は読み書き両用で開くことになります。

　ブロック付きの Exlap.new(……) は、opensメソッドを使って次のように書いても同じです。

     xl = Exlap.new
     xl.opens("book1.xls", "book2.xls") {|wb|  …………}
     xl.quit

　ブロック内で、wbが何番目のワークブックかを知りたい時は、xl.orderを参照します。最初の番号は１です。０(ゼロ)ではありません。また、処理するワークブックの総個数は xl.last で確認できます。上の例だと2が得られるはずです。

　ちなみに、opensメソッドをブロックなしで呼び出した時は、複数のワークブックオブジェクト(Exlwb型)が記録された配列を戻り値として返します。例えば、

     wb1, wb2 = xl.opens("book1.xls", "book2.xls")

とすれば、book1.xlsを変数wb1で、book2.xlsを変数wb2で扱えるようになります。

　　　　

(10) 一度に複数のワークブックを開く opens_once メソッド

　opens_onceメソッドは、一度に複数のワークブックを開いて処理する時に用います。必ずブロック付きで使います。

　このメソッドのサンプルは、既に sample05b.rb として掲げましたが、エッセンスのみ再掲します。

     xl = Exlap.new
     xl.opens_once("book1.xls", "book2.xls") do |wb1, wb2|
         ss1 = wb1.fes
         ss2 = wb2.fes
         ……
     end
     xl.quit

　上は２つのワークブックを扱う例ですが、もちろん３つ以上のワークブックを扱うこともできます。

　引数を１つだけ渡した時の挙動は、opensメソッドをブロック付きで呼び出した時と同じです。

　　　　

(11) ライブラリのバージョンを返す version、Excelのバージョンを返す excel_version

　version は、exlap のバージョンを浮動小数点数の値で返します。

   xl = Exlap.new;  puts xl.version

のように実行します。

　また、excel_version は、Excelのバージョンを浮動小数点数の値で返します。

　　　　

(12) ワークブックの名前を得るいくつかの方法

　wbがExlwb型のオブジェクトである場合、そのワークブックの名前を知る方法がいくつかあります。

　　　　

a.　ワークブックを開く時に指定した名前を得る

• wb.filename：　ワークブックの名前をフルパスで得ます。
• wb.name：　ワークブックの名前をファイル名本体(拡張子を含む)で得ます。

　上の２つは、ワークブックを開く時に指定した名前です。したがって、その名前のファイルが実際にはまだ存在していない場合、つまりワークブックを新規作成で開いている場合は、Excel上で扱われている実際のワークブックの名前(通常は Book1 とか Book2)と異なりますので注意が必要です。

　　　　

b.　処理中のワークブックの実際の名前を得る

• wb.obj.FullName：　ワークブックの名前をフルパスで得ます。
• wb.obj.Name：　ワークブックの名前をファイル名本体(拡張子を含む)で得ます。

　ワークブックを新規作成で開いている場合、上の FullName と Name は、Book1 とか Book2 のように、フォルダ名も拡張子も含まない名前になります。

　既存のファイルを開いている時は、フォルダ名が付いたり拡張子が付いたりします。それぞれ前述の wb.filename, wb.name と同じ名前が得られます。

　　　　

(13) 該当のワークブックを制御するアプリケーションオブジェクト @app(Exlap型)の参照

　ワークブックオブジェクト(Exlwb)の @app を外部から参照できます。@app は、そのワークブックを制御するアプリケーションオブジェクト(Exlap)を示すものです。例えば次のような記述が可能になります。

     Exlap.new("test.xls") {|wb|
       xl = wb.app
       xl.display_alert(false) do
         ss = wb.select("Sheet1")
         ss.Delete  # ワークシートの削除
       end
       wb.save
     }

　　　　

(14) テキストファイルを読み込み新しいワークブックとして開く book_opentext

　book_opentextは、VBA類似記述における OpenText に該当するもので、テキストファイルを分析して1枚のシートに読み込み、新しいブックとして開きます。

　例えば、タブ区切りのテキストを読み込む場合は次のとおり(sample13.rb も参照のこと)。

     （前略）
     xl = Exlap.new
     wb = xl.book_opentext({'Filename'=>"test.txt", 'DataType'=>XlDelimited,
         'Tab'=>true, 'TextQualifier'=>XlTextQualifierNone,
         'FieldInfo'=>[[1,XlTextFormat]]})  # 第１列を文字型として読み込む
     if wb
       ss = wb.ss(1)
       ss.range_autofit
       wb.save wb.filename.sub(/\.txt$/i, ".xls")
     end
     xl.quit

　上の「'Tab'=>true」を「'Comma'=>true」とすれば、カンマで区切られたcsvファイルを読み込むとの指定になります。

　ただし、対象ファイルの拡張子を .txt でなく .csv にしてしまうと、FieldInfoの指定が効かなくなり、第１列目が文字列として読み込まれなくなります。例えば、「001」が「1」になってしまいます。拡張子が .txt であれば FieldInfo が効きます。

　　　　

(15) アドイン関係のファイル情報を取得する addin_list

　アドインとして組み込まれているものに、どのようなものがあるかを確認したい場合、addin_list を用います。

　結果は、配列として得られます。例えば次のようにします。

     xl = Exlap.new
     ary = xl.addin_list
     ary.each {|row|
       p row  # => ["XXX.XLA", "C:\\Program Files\\……\\XXX.XLA"]
     }
     xl.quit

　上記を実行すると、アドインのファイルに関して、その名前とフルパス名が出力されます。

   ["HTML.XLAM", "C:\\Program Files\\……\\HTML.XLAM"]
   ["SOLVER.XLAM", "C:\\Program Files\\……\\SOLVER.XLAM"]
   ["ANALYS32.XLL", "C:\\Program Files\\……\\ANALYS32.XLL"]

のような出力を得られます。

　また、addin_list に引数として正規表現を渡すと、それにマッチするものだけが取得できます。

     xl.addin_list(/^solver\.x/i)

とすれば、ソルバーのアドインについて、そのファイル名とフルパス名を取得できます。

　addin_list の戻り値は、常に配列の配列です。該当のアドインが一つしかなかったとしても、配列の配列として得られます。

　ただし、該当するものが何もみつからなかった時は、空配列 [] が返されます。

　　　　

---

　どのクラスにも属さない独自メソッドをいくつか用意しています。

　それらは、Exlというモジュールに属しています。なので、呼び出す時は、「Exl::」を頭に付けます。例えば「Exl::yx(……)」のように記述します。

　あるいは、スクリプトの最初の方、例えば「require "exlap"」の次の行あたりに、「include Exl」と書いておけば、逐一「Exl::」を頭に付ける必要がなくなります。

　　　　

(1) セルまたはセル群(指定領域)の番地を数値で返す yx(別名 rc)

◇セルまたはセル群(指定領域)を引数として与えると、その番地を数値で返します。

   cell = ss.Range("B4")
   p yx(cell)  # => [4, 2]
   rng = ss.Range("A1:D4")
   p rc(rng)  # => [1, 1, 4, 4]

◇第２引数として :ABSOLUTE 以外を与えると、絶対番地でなく相対番地を返します。

   rng = ss.Range("B2:D4")
   p yx(rng, :RELATIVE)  # => [1, 1, 3, 3]

　　　　

(2) セルまたはセル群(指定領域)の番地を文字列で返す ab および a1

◇セルまたはセル群(指定領域)を引数として与えると、その番地を文字列で返します。

　第２引数として :ABSOLUTE 以外を与えると、相対番地の形で返します。

   cell = ss.cell(3,3)
   p ab(cell)  # => "$C$3"
   rng = ss.rr(2,2, 4,4)  # ss.Range("B2:D4") と同じ
   p ab(rng)  # => "$B$2:$D$4"
   p ab(rng, :RELATIVE)  # => "B2:D4"

　「ab(rng, :RELATIVE)」は、yx() の場合と異なり、相対番地というよりは、絶対番地からドル記号を取り除いたものになっています。

◇a1() を用いると、絶対番地からドル記号を除いた文字列を返します。通常のExcel操作に近い感覚で使うならこれかもしれません。

   rng = ss.rr(2,2, 4,4)
   p a1(rng)  # => "B2:D4"

　　　　

(3) セルまたはセル群(指定領域)の相対番地をRC形式で返す relrc

◇relrc(target_range, base_cell)のように、必ず引数を２つ与えます。

　target_rangeは、セルまたはセル群(指定領域)です。base_cellは、現在位置(１つのセル)です。

　base_cellを起点とした時のtarget_rangeの相対番地をRC形式で返します。

　例えば、A1を起点とした時のB2は、R[1]C[1] です。逆に、B2を起点にした時のA1は、R[-1]C[-1] です。

   p relrc(ss.Range("B2"), ss.Range("A1"))  # => "R[1]C[1]"

◇target_rangeに複数セルからなる領域を与えると、R[1]C[1]:R[2]C[2] などの形で返します。

   p relrc(ss.Range("A1:C3"), ss.Range("B2"))  # => "R[-1]C[-1]:R[1]C[1]"

◇引数の target_range, base_cell は、数値からなる配列(番地を示すもの)で与えることができます。

   p relrc([1,1, 3,3], [1,1])  # => "RC:R[2]C[2]"

　　　　

(4) セルまたはセル群(指定領域)の絶対番地をRC形式で返す absrc

◇absrc(target_range)のように、引数を一つだけ与えます。target_rangeの絶対番地をRC形式で返します。

 p absrc(ss.Range("B2"))  # => "R2C2"

 p absrc(ss.Range("A1:C3"))  # => "R1C1:R3C3"

◇引数の target_range は、数値からなる配列(番地を示すもの)で与えることができます。

 p absrc([1,1, 3,3])  # => "R1C1:R3C3"

　　　　

(5) 指定ファイル名のフルパス名を返す getAbsolutePath

　getAbsolutePathは、カレントディレクトリがどこかを考慮しつつ、指定のファイル名のフルパス名を文字列で返します。

   getAbsolutePath('test.xls')
   getAbsolutePath('.\test.xls')
   getAbsolutePath('D:test.xls')

などのように呼び出します。

　　　　

---

　Exlapクラスには、@auto_quit および @auto_close というインスタンス変数があります。

　これらインスタンス変数の値が true だと、イテレータブロックの処理が一通り終了した時点で自動的にExcelが終了し、また、イテレータブロックの中で一つのワークブックに関する処理が終了した時点で自動的にそのワークブックがクローズされるようになります。

　この @auto_quit, @auto_close は、どちらもデフォルト値が false なので、自動的なExcel終了、自動的なワークブックのクローズは行われません。

　自動的なExcel終了をデフォルトにしたい場合は、exlap.rbの中の「@auto_quit = false」という記述の false を true に書き換えます。

　自動的なワークブックのクローズをデフォルトにしたい場合は、exlap.rbの中の「@auto_close = false」という記述の false を true に書き換えます。

　あるいは、rubyスクリプト内で次のように指定することもできます。

   xl = Exlap.new
   xl.auto_quit = true
   xl.auto_close = true

　　　　

　以下、@auto_quit, @auto_close が true のとき、どのような場面で影響を及ぼすかを示します。

　　　　

(1) @auto_quit と opens, opens_once のイテレータ

　「xl = Exlap.new」とした場合、xl.opens で複数のワークブックの一つづつの処理、xl.opens_once で複数のワークブックの同時オープンによる処理が可能になります。

　@auto_quitがtrueになっていると、opens, opens_once メソッドがブロック付きで呼び出された場合、そのイテレータブロックの実行が終了した時点で、Excelが自動的に終了します。「xl.quit」を記述しなくてもよくなります。

　具体的なスクリプト記述を上げると次のとおり。

　　　　

   xl = Exlap.new
   xl.auto_quit = true
   xl.opens("book1.xls", "book2.xls", "book3.xls") do |wb|
     ss = wb.ss(1)  # 第１ワークシートを選択
     …………
     wb.save
   end

　　　　

　こうすると、book1.xls 〜 book3.xls の三つのワークブックを一つづつ処理して、それらが総て終了した時点で、Excelが終了します。

　opensメソッドをブロックなしで呼び出した時は、@auto_quitの値が何であれ、Excelが終了してしまうことはありません。

　opens_once の記述例は次のとおり。

　　　　

   xl = Exlap.new
   xl.auto_quit = true
   xl.opens_once("book1.xls", "book2.xls", "book3.xls") do |wb1, wb2, wb3|
     ss1 = wb1.ss(1)
     …………
     wb1.save
     wb2.save
     wb3.save
   end

　　　　

　なお、「Exlap.new(file1, file2, file3) {|wb| ……}」のように、Exlap.newをブロック付きで呼び出した時は、@auto_quitの値に関係なく、イテレータブロックの実行が終了した時点でExcelが終了します。

　　　　

(2) @auto_close と Exlap.new および opens, opens_once のイテレータ

　Exlap.new または opensメソッドをブロック付きで呼び出した場合、そのイテレータブロックの中で引数で渡されたワークブックを一つづつ処理することになります。

　この場合、一つのワークブックの処理が終わったとしても、そのワークブックがクローズされることはありません。開いたままの状態で次のワークブックの処理に移ります。

　もしクローズしたければ、「wb.close」のように明示する必要があります。

　しかし、@auto_close が true になっていると、イテレータブロックの中で一つのワークブックの処理がおわる度に、そのワークブックが自動的にクローズされます。

　　　　

   xl = Exlap.new
   xl.auto_close = true
   xl.opens("book1.xls", "book2.xls", "book3.xls") do |wb|
     ss = wb.ss(1)  # 第１ワークシートを選択
     …………
     wb.save
   end
   xl.quit

　　　　

　上のスクリプトの場合、オープンされているワークブックは、常に一つだけです。

　それに対し、@auto_close が false だと、「xl.quit」の直前でオープンされているワークブックは、三つあることになります。

　なお、Exlap.newをブロック付きで呼び出す時は、事前に @auto_close の値を調整することができません。exlap.rbを書き換えて、@auto_closeのデフォルト値を予め好みのものにしておく必要があります。

　　　　

　それから、opens_once で複数のワークブックを同時にオープンした場合、@auto_close が true になっていると、イテレータブロックの処理が終了した時点で、その複数のワークブックが総てクローズされます。

　@auto_close が false だと、クローズされることなくイテレータの外の処理に移っていきます。

　　　　

　以上、@auto_quit, @auto_close の影響が及ぶ範囲について述べました。

　ただし、Exlapオブジェクトのeachメソッドについてはこれら @auto_quit, @auto_close の影響が及ばないので注意して下さい。

　具体的には次のような場合です。

　　　　

   xl = Exlap.new("book1.xls", "book2.xls", "book3.xls")
   xl.each do |wb|
     ss = wb.ss(1)
     …………
     wb.save
   end

　　　　

　上のようにすると、三つのワークブックを一つづつ処理することができますが、この場合は、@auto_quit が true であっても、自動的にExcelが終了することはありません。また、@auto_close が true であっても、自動的にワークブックがクローズされることもありません。

　　　　

---

　ワークブックを処理するためのクラス Exlwb に、Excelのマクロを扱うためのメソッドをいくつか設けています。

　マクロの取り出しと組み込み(文字列経由、ファイル経由)、マクロの削除、マクロの一覧取得等があります。

　Excelのマクロの仕組みとして、まずプロジェクト(VBProject)というのがあり、その中に複数のコンポーネント(VBComponent)があります。そして、コンポーネントの中身(ソースコード)を見ると、「Sub Macro1 …… End Sub」のような各プロシージャの記述が書かれています。一つのコンポーネントに複数のプロシージャが書かれていることもしばしばです。

　コンポーネントには、標準モジュールとかクラスモジュールなどの種類(Type)と、「Module1」とか「Class1」などの名前(Name)の属性があります。componentが一つのコンポーネントオブジェクトを指す場合、component.Type(数値)、component.Name(文字列)でそれら属性を知ることができます。

　以下の各種メソッドで扱うのは、コンポーネントのタイプが 1:標準モジュール、2:クラスモジュール、3:ユーザーフォームの３種類についてです。他に 11:ActiveXデザイナ、100:ドキュメントモジュールがありますが、この２種類は対象としません。

　　　　

(1) コンポーネントのソースコードを文字列で得る macro_code(type, name)

　wbがワークブックを指す場合、「str = wb.macro_code()」とすれば、ワークブックに登録されているコンポーネントのソースコードを文字列で得ることができます。タイプ1〜3の総てを取得します。

　「str = wb.macro_code(1)」とすれば、標準モジュールのみを取得します。

　「str = wb.macro_code(nil, "Module1")」とすれば、タイプを問わず、「Module1」という名前のコンポーネントを取得します。

　「str = wb.macro_code(2, "Class1")」とすれば、クラスモジュールの中の「Class1」という名前のコンポーネントを取得します。

　取り出したソースコードには、要所要所に

     ' <<type:Standard,  name:Module1>>

のようなコメント行が挿入されています。これは、コンポーネントのタイプと名前をコメントとして挿入したものです。オリジナルのソースコードにはなかった記述です。

　なお、「str = wb.macro_code()」のように、引数を省略して得たソースコードには、複数のコンポーネントのコードが含まれている可能性があります。そのstrをそのままマクロとして組み込むのは避けるべきです。タイプの異なる複数のコンポーネントを一つのコンポーネントとして組み込むのは、トラブルの原因になります。

　「str = wb.macro_code(1, "Module1")」のように、引数の type, name の両方とも指定して得たソースコードは、そのままマクロとして組み込んでも不都合はないと思います。

　　　　

(2) コンポーネントのソースコードを登録する macro_add(str, type, name)

　変数 str に「Sub Macro1 …… End Sub」などのソースコードが記録されている場合、

　「wb.macro_add(str, 1, "Module5")」とすれば、標準モジュールの「Module5」という名前のコンポーネントを設けてマクロを登録します。「Module5」などの名前は、既存のものと重複しないよう注意する必要があります。

　引数の type, name を省略すると、typeとして1(つまり標準モジュール)が仮定されます。nameの方は、Excelが適当に割り当てます。標準モジュールであれば「Module1」などの名前になります。

　要するに、標準モジュールにマクロを登録したい時は、「wb.macro_add(str)」とだけ書けばOkです。

　「wb.macro_add(str, 2, "TestClass1")」とすれば、クラスモジュールを登録します。クラスモジュールの名前は「TestClass1」となります。そして、これがクラス名にもなります。

　nameを省略して「wb.macro_add(str, 2)」とすれば、クラス名は、おそらく「Class1」になります。既に「Class1」が存在する時は「Class2」になります。

　何らかの理由で第３引数で指定した名前をコンポーネントに割り当てることができなかった時は、その旨を標準エラー出力に出力します。コンポーネントの名前は、Excelが割り当てたものになります。

　変数 str にキャリッジリターン(CR)も改行(LF)も含まれておらず、かつ、strがファイル名として有効な場合(実際に該当のファイルが存在する場合)、strをファイル名とみなして、その中身をコンポーネントとして登録します。

　「wb.macro_add("test.txt")」とすれば、「test.txt」の中身を標準モジュールに登録します。

　macro_add() の戻り値は、追加されたコンポーネントオブジェクトです。なので、次のようにして追加コンポーネントの名前を確認できます。

     component = wb.macro_add(str)
     p component.Name  # => "Module1"

　なお、前述の macro_code() で自動的に挿入されたコメント「' <<type:……>>」が str の中にある場合は、それを削除した上で登録します。

　第１引数を指定したくない時は、「wb.macro_add(nil, 3)」のように nil を指定して下さい。

　　　　

(3) コンポーネントをファイルとして出力する macro_export(type, name)

　「wb.macro_export()」とすれば、タイプ1〜3のコンポーネント総てをファイルとして書き出します。

　そのとき、標準モジュールの拡張子は ".bas"、クラスモジュールは ".cls"、ユーザーフォームは ".frm" になります。

　ファイル名本体(拡張子を除く)は、コンポーネントの名前がそのまま用いられます。つまり「Module1」とか「Class1」などの名前になります。

　ファイルが書き出されるフォルダは、ワークブックが存在するのと同じフォルダです。

　同名のファイルがあると、上書きになります。

　引数の type, name の指定の仕方は、macro_code() と同じです。

　macro_export() の戻り値は、出力成功コンポーネントのタイプと名前の組みが複数含まれる配列 type1, name1], [type2, name2 などになります。

　何も出力しなかった時は、空配列 [] を返します。

　コンポーネントを書き出せなかった時は、書き出しに失敗した旨とそのファイル名を標準エラー出力に出力します。

　なお、第３引数に true を与えると、書き出しファイル名にワークブック名を付加します。「test.xls!Module1.bas」のようなファイル名に書き出します。

　すべてのコンポーネントをワークブック名つきで書き出すには

     wb.macro_export(nil, nil, true)

とします。

　　　　

(4) コンポーネントをファイルから読み込む macro_import(filename, dup_check)

　「wb.macro_import("Module1.bas")」とすれば、「Module1.bas」を読み込みます。

　ファイルの中身が標準モジュールなのかクラスモジュールなのかなどは、中身の記述によって識別されます。ファイルの拡張子で識別されるわけではありません。

　第２引数の dup_check が true だと、重複チェックを行います。指定したファイル名が「Module1.bas」のとき、拡張子を除いた「Module1」という名前のコンポーネントがワークブック中に既に存在するかを確認し、存在する場合は読込みを行いません。

　dup_check が false であれば、重複チェックは行わず、ファイルを読み込みます。その場合のコンポーネントの名前は、Excelが自動的に調整するはずです。

　dup_checkのデフォルト値は false です。

　先述の macro_export で書き出したファイルを、別のワークブックにおいて macro_import を使って読み込めば、そのワークブックに同じマクロを組み込むことができます。

　macro_import() の戻り値は、読み込みによって生成されたコンポーネントのタイプと名前 [type, name] となります。

　読み込みが行われなかった時は nil を返します。

　　　　

(5) コンポーネントを削除する macro_remove(type, name)

　「wb.macro_remove()」とすれば、タイプ1〜3のコンポーネントを総て削除します。

　「wb.macro_remove(1)」だと、標準モジュールを総て削除します。

　「wb.macro_remove(1, "Module1")」であれば、標準モジュールの中の「Module1」というコンポーネントを削除します。

　引数の type, name の指定の仕方は、macro_code() と同様です。

　macro_remove() の戻り値は、削除したコンポーネントのタイプと名前の組みが複数含まれる形 type1, name1], [type2, name2 などになります。何も削除されなかった時は、空配列 [] を返します。

　　　　

(6) コンポーネントの一覧を配列で返す macro_list(type, name)

　「ary = wb.macro_list()」とすれば、ワークブックに登録されている全コンポーネントの [type, name] の組みを返します。

　戻り値の ary は、「1, "Module1"], [2, "Class1"」などのように、配列の配列という形になっています。

　コンポーネントが何もみつからない時は、空配列 [] を返します。

　「ary = wb.macro_list(1)」とすれば、標準モジュールのリストを返します。

　引数の type, name の指定の仕方は、macro_code() と同じです。

　なお、macro_list2() を用いると、該当のコンポーネントオブジェクトを配列に入れて返します。使い方は macro_list() と同じです。

　　　　

(7) マクロを実行する macro_run(mname, *arg)

　ワークブックwbに登録されている Macro1 というマクロを実行する時は、「wb.macro_run("Macro1")」とします。このとき、wbは開かれている必要があります。

　macro_run には run という別名があるので「wb.run("Macro1")」と書いてもOkです。

　Macro1 が引数を指定できるものであれば、「wb.run("Macro1", "abc", 2)」のようにして引数をわたすことができます。

　このメソッドは、VBAでいうところの Application.Run を内部で呼び出しますが、その Application.Run の戻り値が macro_run の戻り値になります。

　もし Module1 と Module2 の両方に Macro1 というマクロが登録されている場合は、

　「wb.run("Module1.Macro1")」　「wb.run("Module2.Macro1")」のように、モジュール名の後に半角ピリオド記号を置いてからマクロ名を記します。

　　　　

(8) マクロ(プロシージャ)の名前の一覧を得る macro_name(type, name)

　「hs = wb.macro_name(1, "Module1")」とすると、標準モジュール「Module1」に含まれるプロシージャの名前の一覧を取得できます。

　戻り値はハッシュで、「{[1, "Module1"]=>["Macro1", "Macro2"]}」のようになります。

　引数を省略して「hs = wb.macro_name」とすれば、ワークブックに含まれる全コンポーネントについてプロシージャ名を取得します。

　引数の type, name の指定の仕方は、macro_code() と同様です。

　例えば、次のようにすると全コンポーネントのプロシージャ名を標準出力に出力できます。

   wb.macro_name.each do |key, val|
       printf("%s: ", key.join(" "))
       puts val.join(", ")
   end

　　　　

　以上が Exlwb クラスに用意されているマクロ関連のメソッドです。

　　　　

---

　グラフを扱うための独自メソッドは設けていませんが、参考まで、ワークシートにグラフを埋め込む場合の例を掲げます。

　　　　

   −−−− sample12.rb ここから
   #! ruby -Ks
   require "exlap"
       # 東北６県の人口データを変数に代入
       population = [["都道府県", "H17人口", "人口密度", "人口性比"],
          ["青森", 1437000, 149.5, 89.0],
          ["岩手", 1385000, 90.7, 91.5],
          ["宮城", 2360000, 324.0, 94.4],
          ["秋田", 1146000, 98.6, 88.9],
          ["山形", 1216000, 130.4, 92.3],
          ["福島", 2091000, 151.7, 94.4]]
   Exlap.new("test.xls") {|wb|
     ss = wb.fes
     y = 0
     population.each {|row|
       y += 1
       for x in 1..row.size
         ss[y,x] = row[x-1]
       end
     }
     ss.range_autofit
     ss.range_allbox
     y,x = ss.range_last
     srng = ss.Range(ss.cell(2,1), ss.cell(y,2)) # グラフ化するデータ記録領域
     grng = ss.Range(ss.cell(y+4,2), ss.cell(y+14,12))  # グラフを描く領域
     chobj = ss.ChartObjects.Add({'Left'=>grng.Left, 'Top'=>grng.Top,
         'Width'=>grng.Width, 'Height'=>grng.Height})
     chobj.Activate
     ch = chobj.Chart
     # ch.chartType = XlLine  # グラフのタイプ(折れ線)
     ch.SetSourceData({'Source'=>srng})
     ch.HasTitle = true
     ch.ChartTitle.Characters.Text = "東北６県の人口"
     ch.Axes(XlCategory, XlPrimary).HasTitle = true # X項目軸
     ch.Axes(XlCategory, XlPrimary).AxisTitle.Characters.Text = "都道府県"
     ch.Axes(XlValue, XlPrimary).HasTitle = true # Y項目軸
     ch.Axes(XlValue, XlPrimary).AxisTitle.Characters.Text = "H17人口"
     wb.save
   }
   −−−− sample12.rb ここまで

　　　　

　上は棒グラフを描くものですが、ch.chartType のある行の最初の '#' を削除すれば、折れ線グラフになります。グラフの種類(タイプ)は多様なので、いろいろ選ぶことができます。

　なお、グラフを描く場合、通常のワークシートとは別に、グラフ用のシートを設けてそこに描く方法もあります。それに関するエッセンス部分だけ示すと次のとおりです。

　ssは、グラフを作る材料となるデータが書き込まれているワークシートのオブジェクトです。グラフ用のシートは、このワークシートの次に新たに設けられます。

　　　　

   −−−−
   y,x = ss.range_last
   srng = ss.Range(ss.cell(2,1), ss.cell(y,2))
   ch = wb.obj.Charts.Add({'After'=>ss})
   ch.SetSourceData srng
   −−−−

　　　　

---

　exlapに関するライセンスは、最新のrubyライセンスに従います。BSDLとのデュアルライセンスとします。

　　　　

---

　rubyでExcelを操縦する方法について、最も参考にさせていただいたのは次のサイトです。

Rubyist Magazine - Win32OLE活用法【第2回】 Excel

　　　　

　その他、数が多いので逐一は上げませんが、多数のVBA解説サイトなどを参照させていただきました。

　　　　

以上。

　　　　

Copyright (C) T. Yoshiizumi, 2010-2012 All rights reserved.