rubyによるExcel操縦あれこれ　〜　クエリテーブルの利用

rubyによるExcel操縦あれこれ　〜　クエリテーブルの利用

最終更新日：　2011/02/19

　Excel関連で時々用いるノウハウをメモにしておこうと思い立って、記しました。

　以下で掲げるサンプルは、Excelの外部データ取込み機能(QueryTable)に関するものです。

qt_test.zip には、以下で掲げるサンプルスクリプトのほか、exlap.rb, yado.rb も含まれています。別途ダウンロードする必要はありません。

なお、rubyがインストールされていない環境でサンプルスクリプトを実行したい方は、exl.exeをお試し下さい。詳しくは exl.exeの使い方 を参照して下さい。

• ＜はじめに＞
• 1.　Accessデータベースの扱い・その１(odbc編)
• 2.　設定済みのクエリテーブルを利用する
• ［追記］　ワークブックに記録されているクエリテーブル総ての名前を出力
• 3.　Accessデータベースの扱い・その２(ado編)
• 4.　webの取込み
• 5.　テキストファイルの取込み

　Excelの外部データの取込み機能は、QueryTablesというメソッドで行います。

　例えば、Accessデータベースに対してsql命令を実行し、その結果をExcelのワークシートに取り込むことができます。また、webやtextを取り込むことも可能です。

　以下に掲げるサンプルスクリプトを私が実行した環境は次のとおり。

• MS-Windows xp | vista
• Excel2002(Office xp版) | Excel2007
• ruby ver 1.8.7
• Accessデータベースはインストールされてなくてもかまわない。
• 使用ライブラリ: 拙作exlap.rb v1.1

　(Accessデータベース操作のためyado.rb(v1.5)を用いているものがある。)

--------

　今、ID(整数・オートナンバー), 氏名, 身長(浮動小数点数), 誕生日(日付型)の４項目からなるAccessデータベース test.mdb があります。

　テーブル名は「身長と誕生日」で、５人分のデータが記録されています。

　このデータベースにアクセスして、身長の高い順に列べた結果をExcelのワークシートに取り込むことを考えます。

　実行するsql文は次のとおり。

   select 身長,氏名,ID,誕生日 from 身長と誕生日 order by 身長 desc;

　この目的を実現する最も簡単なスクリプトは、おそらくodbc接続を利用した次のものだろうと思います。

　ado接続で行う方法もありますが(後で掲げます)、ちょっとだけ面倒になります。

   −−−−　ここから (qt01.rb)
   #! ruby -Ks
     # mdbデータベースのsql実行結果をワークシートに読み込んで保存(odbc版)
   require "exlap"
   
   dbname = "test.mdb"
   tblname = "身長と誕生日"
   sql = "select 身長,氏名,ID,誕生日 from #{tblname} order by 身長 desc;"
   cnn = "ODBC;DSN=MS Access Database;DBQ=#{Exl::getAbsolutePath(dbname)}"
   
   filename = "test01.xls"
   Exlap.new(filename) {|wb|
     ss = wb.fes  # first_empty_sheet
     ss.Name = "身長順のシート"
     qt = ss.QueryTables.Add({
       'Connection'=>cnn,
       'Destination'=>ss.Range("A1"),
       'Sql'=>sql})
     qt.Name = "身長順"  # クエリテーブルの名前
     qt.SaveData = false  # 「クエリテーブルをワークブックと一緒に保存」をしない
     qt.RefreshPeriod = 0  # 「定期的更新」の時間(0で無効)
     qt.BackgroundQuery = false  # バックグラウンド処理をしない
     qt.Refresh
     qt.Delete  # 念のためクエリテーブルを削除
     wb.save
   }
   −−−−　ここまで

　上は test.mdb を扱う例ですが、Access2007用の test.accdb を扱いたい場合は、"test.mdb" を "test.accdb" に変更するだけで大丈夫です。ただし、Office2007が導入されている環境が必要です。

　QueryTables.Addに渡す引数はHashで、サンプルでは Connection(接続用文字列), Destination(ワークシートにおける取込みの始点), Sql(sql命令文)の３つを指定しています。DestinationとしてはA1、つまり左上端を指定しています。

　その後にはQueryTablesのプロパティを設定しています。これにはいろいろあるようで、私もあまり把握していませんが、関係ありそうなものをサンプルに含めてみました。

• qt.Name クエリテーブルの名前

　もし後で再利用するなら指定しておく。再利用しないなら指定しなくてもかまわない。無指定の時は適当な名前が割り当てられる。

• qt.SaveData 「クエリテーブルをワークブックと一緒に保存」の有無

　これを true にすると、クエリテーブルがワークブックと一緒に保存される。
　保存しておくと、Excelをいったん終了し、再度ワークブックを開いた時に、再びクエリテーブルを利用できる。
　サンプルでは false にしてあるので保存されない。

• qt.RefreshPeriod 定期的更新の時間間隔(分単位)

　これを 0 にすると、定期的更新は行われない。
　これを例えば 5 とすれば、5分ごとに更新が行われる。

• qt.BackgroundQuery バックグラウンド処理の有無

　これを true にすると、バックグラウンド処理で取込みを行うようになる。つまり、一定時間ごとに(定期的に)背後タスクで取込みを行う。時間の指定は先述の RefreshPeriod プロパティで指定。
　BackgroundQuery が false だと、データの更新が終了するまで待機する。

• qt.Refresh 更新

　データを実際に取り込む。あるいは、バックグラウンド処理が指定されていれば、取込みの状態を継続する(定期的に取り込む)。

• qt.Delete クエリテーブルの削除

　QueryTables.Add でクエリテーブルを追加したわけだが、それを削除する。
　先の Refresh の BackgroundQuery が true だと、クエリテーブルが働いている状態が続くので、普通は Delete しようとするとエラーになる。

＊補足: Exl::getAbsolutePath() というメソッドは、指定されたファイル名のフルパスを返すもの。exlap.rbの中で定義されている。

--------

　先に QueryTables.Add でクエリテーブルを新たに設ける例を掲げましたが、今度は、設定済みのクエリテーブルを利用する例を上げます。

　前半は先述のサンプルとほぼ同じで、クエリテーブルを新たに設定します。ただ、今度はクエリテーブルをワークブックと一緒に保存して、いったんExcelを終了します。

　そして、データベースにデータを追加したのち(拙作yadoで行います。)、Excelを再び起動し、クエリテーブルを更新して、ちゃんと新しいデータが入っているか確認します。

   −−−−　ここから (qt02.rb)
   #! ruby -Ks
     # 設定済みクエリテーブルを更新する
   require "exlap"
   require "yado"
   
   dbname = "test.mdb"
   tblname = "身長と誕生日"
   sql = "select 身長,氏名,ID,誕生日 from #{tblname} order by 身長 desc;"
   cnn = "ODBC;DSN=MS Access Database;DBQ=#{Exl::getAbsolutePath(dbname)}"
   
   filename = "test02.xls"
   Exlap.new(filename) {|wb|
     ss = wb.fes  # first_empty_sheet
     ss.Name = "身長順のシート"
     qt = ss.QueryTables.Add({
       'Connection'=>cnn,
       'Destination'=>ss.Range("A1"),
       'Sql'=>sql})
     qt.Name = "身長順"  # クエリテーブルの名前
     qt.SaveData = true  # 「クエリテーブルをワークブックと一緒に保存」する
     qt.BackgroundQuery = false  # バックグラウンド処理をしない
     qt.Refresh
     wb.save
     ary = ss.to_a
     puts "クエリテーブル更新の第１回目"
     ary.each {|row|
       puts row.join("\t")
     }
   }
   
       # データベース更新
   db = Yado.new dbname
   ary = [%w(氏名 身長  誕生日),
          %w(安部 190.5 1989/5/18),
          %w(伊藤 158.9 1968/3/28)]
   db.addnew(tblname, ary)
   db.close
   
       # Excelを再び起動して、設定済みのクエリテーブルを更新
   Exlap.new(filename) {|wb|
     ss = wb.ss("身長順のシート")
     qt = ss.QueryTables("身長順")
     qt.Refresh
     wb.save
     ary = ss.to_a
     printf("\n")
     puts "クエリテーブル更新の第２回目"
     ary.each {|row|
       puts row.join("\t")
     }
   }
   −−−−　ここまで

　設定済みのクエリテーブルを呼び出す時は、

   qt = ss.QueryTables("身長順")

のようにクエリテーブルの名前で呼び出します。

　なお、クエリテーブルを個別に呼び出して更新するのでなく、ワークブックと一緒に保存されているクエリテーブルを総て一括して更新することも可能です。

　ワークブックオブジェクトの RefreshAll メソッドで行います。exlap.rbを利用している場合でいうと、

   wb.obj.RefreshAll

という記述で一括更新を実行できます。

　このメソッドを利用する例を下に掲げておきます。二度目のExcel起動のところのみです。

   −−−−　ここから (qt03.rb 抜粋)
       # Excelを再び起動して、設定済みのクエリテーブルを総て一括更新
   Exlap.new(filename) {|wb|
     wb.obj.RefreshAll
     wb.save
   
     empty_list = wb.empty_sheet_names
     puts "クエリテーブル更新の第２回目"
     wb.each {|ss|
       next  if empty_list.include?(ss.Name)
       printf("＊ワークシート名: %s\n", ss.Name)
       ary = ss.to_a
       ary.each {|row|
         puts row.join("\t")
       }
       printf("\n")
     }
   }
   −−−−　ここまで

　この RefreshAll を用いた場合、クエリテーブルの中にバックグラウンド処理するものがあると、つまり BackgroundQuery プロパティが true になっているクエリテーブルがあると、Excelが終了しないので注意が必要です。rubyスクリプト内でExcelの終了を指示していても、通常は終了しません。

--------

　サンプルの qt03.rb を実行すると、test03.xls が生成されますが、それには２つのクエリテーブルが含まれています。「身長順」と「誕生日順」の２つです。

　せっかくなので、ここで、ワークブックに記録されているクエリテーブル総ての名前を出力するサンプル qt03_2.rb を掲げておきます。特に解説の必要がない程度の簡単なものですが、参考まで。

   −−−−　ここから (qt03_2.rb)
   #! ruby -Ks
     # ワークブックに記録されているクエリテーブル総ての名前を出力
   require "exlap"
   
   filename = "test03.xls"
   Exlap.new(filename) {|wb|
     wb.each {|ss|
       next  if ss.QueryTables.Count < 1  # クエリテーブルがないのでskip
       printf("＊ワークシート名: %s\n", ss.Name)
       ss.QueryTables.each {|qt|
         printf("\t%s\n", qt.Name)
       }
     }
   }
   −−−−　ここまで

　上のスクリプトを少し変更すれば、「ワークブック内のクエリテーブルを総て削除する」も簡単に行えます。

--------

　adoという仕組みによってAccessデータベースを扱うことができます。adoは、かなり古いものでないかぎり、MS-Windowsに標準でインストールされていると思います。Excel2002がプレインストールされたパソコンには ado v2.7 が予めインストールされていました。

　以下に掲げるサンプルは、先のodbc版と、やっていることは同じです。ただ、クエリテーブルを設定する時の方法(データベース接続の方法)が違うだけです。

   −−−−　ここから (qt04.rb)
   #! ruby -Ks
     # mdbデータベースのsql実行結果をワークシートに読み込んで保存(ado版)
   require "exlap"
   
   dbname = "test.mdb"
   tblname = "身長と誕生日"
   sql = "select 身長,氏名,ID,誕生日 from #{tblname} order by 身長 desc;"
   
       # ado接続
   cnn_str = nil
   case File.extname(dbname).downcase
   when '.mdb'
     cnn_str = "Provider=Microsoft.Jet.OLEDB.4.0;"
   when '.accdb'
     cnn_str = "Provider=Microsoft.ACE.OLEDB.12.0;"
   end
   unless cnn_str
     exit
   end
   cn = WIN32OLE.new("ADODB.Connection")
   cn.Open cnn_str + "Data Source=#{Exl::getAbsolutePath(dbname)};"
   rs = WIN32OLE.new("ADODB.Recordset")
   rs.CursorLocation = 3  # adUseClient  クライアント側カーソルを使用
   rs.Open sql,cn
   
       # Excelを起動してsql実行結果を取り込む
   filename = "test04.xls"
   Exlap.new(filename) {|wb|
     ss = wb.fes  # first_empty_sheet
     ss.Name = "身長順のシート"
     qt = ss.QueryTables.Add(rs, ss.Range("A1"))
     qt.Name = "身長順"  # クエリテーブルの名前
     qt.SaveData = false  # 「クエリテーブルをワークブックと一緒に保存」をしない
     qt.RefreshPeriod = 0  # 「定期的更新」の時間(0で無効)
     qt.BackgroundQuery = false  # バックグラウンド処理をしない
     qt.Refresh
     qt.Delete  # 念のためクエリテーブルを削除
     wb.save
   }
   
       # adoの後処理
   rs.Close
   cn.Close
   rs = cn = nil
   −−−−　ここまで

　データベースとして、test.mdb と test.accdb(Access2007以降用)の両方に対応させるため、少々ややこしくなっています。

　また、ADODB.Connection, ADODB.Recordset を使っているので、後処理としてそれぞれ Close を行っています。

　それ以外の中核部分は、先のodbc版とそれほど違いません。

　なお、私は試したことありませんが、MS-sqlサーバにアクセスする場合は、

   Provider=Microsoft.Jet.OLEDB.4.0;Data Source=……

のところを

   Provider=SQLOLEDB.1;Data Source=……;User ID=……;Password=……;Initial Catalog=……

などのようにして、ユーザIDやパスワードを盛り込む形にすれば、可能になるようです。

--------

　Excelの外部データ取込みによって、webのtable部分またはwebのページ全体をワークシートに読み込むことができます。

　やはり QueryTables で行いますが、web関連のプロパティがいろいろあるようです。

　以下に、webのtable(複数)を取り込むサンプルを掲げます。

   −−−−　ここから (qt05.rb)
   #! ruby -Ks
     # webのtableを取り込む(web page全体ではない)
   require "exlap"
   
   url = 'http://www.data.jma.go.jp/obd/stats/data/mdrr/synopday/data1s.html'
   table_index = nil
   filename = "test05.xls"
   Exlap.new(filename) {|wb|
     ss = wb.fes
     ss.Name = "気象情報のシート"
     qt = ss.QueryTables.Add({
       'Connection'=>"URL;#{url}",
       'Destination'=>ss.Range("A1")})
     qt.Name = "気象情報"
     qt.WebTables = table_index  if table_index
     qt.WebSelectionType = table_index ? XlSpecifiedTables : XlAllTables
     qt.WebFormatting = XlWebFormattingAll
     qt.SaveData = false  # 「クエリテーブルをワークブックと一緒に保存」をしない
     qt.RefreshPeriod = 0  # 「定期的更新」の時間(0で無効)
     qt.BackgroundQuery = false
     qt.Refresh
     wb.save
   }
   −−−−　ここまで

　上に出てくる table_index は、web内の何番目のtableを取り込むかを指定するものです。

　table_index が nil だと、総てのテーブルを取り込みます。各tableが別々のワークシートに記録されるのでなく、総て１つのワークシートに入ります。

　table_index を "3" とすれば、３番目のtableだけが取り込まれます。"3,4" とすれば3番目と4番目の２つが取り込まれます。

　これに関連するプロパティは、次の２つです。

   qt.WebTables = table_index  if table_index
   qt.WebSelectionType = table_index ? XlSpecifiedTables : XlAllTables

　このWebSelectionTypeプロパティの値を XlEntirePage にすれば、tableでなくwebページ全体を取り込むとの指定になります。その場合は、WebTablesプロパティを指定しません。

　「qt_test.zip」には、webページ全体を取り込む qt05_2.rb を同梱してあります。

　それから、サンプルでは WebFormatting プロパティを取り上げていますが、これは、web取込時にどれだけの書式情報を組み入れるかの指定のようです。

　サンプルでは XlWebFormattingAll を指定していますが、これを指定すると、例えば、セル結合情報がワークシート上に反映されます。

　デフォルト値の XlWebFormattingNone を指定すると、セル結合が反映されません。

　その他、値として XlWebFormattingRTF (リッチテキスト)を指定できるようですが、どう違ってくるのか確認していません。

　上に掲げたものを含め、webなどの取込み時に関連するプロパティを下に掲げてみます。それぞれどのような意味を持つのか、正確には把握していませんが。

• qt.WebSelectionType = XlSpecifiedTables

　取込みのタイプを指定。
　　XlSpecifiedTables 特定の表のみ
　　XlAllTables Webの表を総て
　　XlEntirePage Webページ全体

• qt.WebTables = "3,4"

　表の名前やインデックス番号のカンマ区切りリスト

• qt.WebFormatting = XlWebFormattingNone

　取り込む書式情報等の範囲
　　XlWebFormattingNone 指定なし(デフォルト)
　　XlWebFormattingRTF リッチテキスト
　　XlWebFormattingAll HTML

• qt.WebPreFormattedTextToColumns = true

　Webページの <pre> タグ内にあるデータを列に区切る

• qt.WebSingleBlockTextImport = false

　trueだと、<pre>タグ内にあるデータを一括処理する。falseなら連続する行からなるブロックとして取り込む。

• qt.WebConsecutiveDelimitersAsOne = true

　Webページの <pre> タグ内のデータが複数の列に区切られる場合、trueだと連続する区切り文字を１つの区切り文字として扱う。falseならそのまま。

• qt.AdjustColumnWidth = false

　trueだと列幅の自動調整を行う。web取込みに直結するものではないが参考まで。

--------

　テキストファイルの取込みについては、いろいろなプロパティがあるようです。

　例えば、タブ区切り、カンマ区切り(csv)、セミコロン区切りのどれを選択するかの指定があります。また、テキストファイルの文字コードに関するものもあります。

　とりあえず、比較的オーソドックスと思われる形、タブ区切りテキストの取込みの例を掲げてみます。

   −−−−　ここから (qt06.rb)
   #! ruby -Ks
     # テキストファイルを取り込む
   require "exlap"
   
   text_filename = "test.txt"
   filename = "test06.xls"
   Exlap.new(filename) {|wb|
     ss = wb.fes
     ss.Name = "身長と誕生日のシート"
     qt = ss.QueryTables.Add({
       'Connection'=>"TEXT;#{Exl::getAbsolutePath(text_filename)}",
       'Destination'=>ss.Range("A1")})
     qt.Name = "身長と誕生日"
     qt.TextFilePromptOnRefresh = false  # 更新時のファイル名再指定の有無
     qt.TextFileParseType = XlDelimited  # 元のデータの形式
     qt.TextFileTabDelimiter = true  # タブ区切り
     qt.TextFileCommaDelimiter = false  # カンマ切り
     qt.TextFileSemicolonDelimiter = false  # セミコロン区切り
     qt.TextFileStartRow = 1  # 外部データの１行目から取り込む
     qt.TextFilePlatform = 932  # SJIS 932, EUC-JP 20932, UTF8 65001, ASCII 1252
     qt.TextFileTrailingMinusNumbers = true  # マイナス記号の扱い方
     qt.TextFileColumnDataTypes = Array.new(256,2)  # データ型の指定
     qt.SaveData = false  # 「クエリテーブルをワークブックと一緒に保存」をしない
     qt.RefreshPeriod = 0  # 「定期的更新」の時間(0で無効)
     qt.BackgroundQuery = false
     qt.Refresh
     wb.save
   }
   −−−−　ここまで

　サンプルで取り上げた各プロパティの意味を下に簡単に記します。

• qt.TextFilePromptOnRefresh

　更新時のファイル名再指定の有無
　これが true だと、クエリテーブルが更新される時に、「テキストファイルのインポート」ダイアログボックスで、パスとファイル名を指定することになるようです。ただし、初回の更新(QueryTables.Add)の時はダイアログボックスが出ないみたいです。
　デフォルトは false

• qt.TextFileParseType

　元のデータの形式。次の値を指定可能。
　　XlDelimited　カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ
　　XlFixedWidth　スペースによって位置が揃えられた固定長フィールドのデータ

• qt.TextFileTabDelimiter　タブ区切りの場合はこれを true にする。
• qt.TextFileCommaDelimiter　カンマ切りの時はこれを true にする。
• qt.TextFileSemicolonDelimiter　セミコロン区切りの時はこれを true にする。
• qt.TextFileStartRow

　元のテキストファイルの何行目から取り込むかの指定
　3 を指定すると３行目以降を取り込む。

• qt.TextFilePlatform

　元のテキストの文字コードを指定。次のものが指定可能(数値で指定)。
　Shift_JIS 932, EUC-JP 20932, UTF8 65001, ASCII 1252
　私が試したところでは、次のものは指定できなかった(エラーになった)。
　JIS 50220〜50222, UTF16 1200
　なお、Excel2002よりも前のバージョンのExcelでは、Shift_JISのみ対応で、このプロパティに指定する値は XlWindows にしなければならないのでは？と思います。

• qt.TextFileTrailingMinusNumbers

　マイナス記号の扱い方。trueにすると、数値のマイナス記号とみなされる。

• qt.TextFileColumnDataTypes

　データ型を配列で指定。配列の要素は次の数値。
　　1 標準（数値）
　　2 文字列
　　5 日付
　　9 無視（表示しない）
　サンプルの Array.new(256,2) は、256個の列を文字列として取り込むとの意味。実質的に「総て文字列として取り込む」ようにしたもの。１行の列数が不明な場合は、便法的にこのような指定が可能。
　予め列数が４個と分かっていれば、[2, 2, 1, 5] などのように指定できる。

　他にも様々なプロパティがあるようですが、調べてないので省略します。

−　以上　−