クエリーテーブル：外部データの取込み

以下に掲げるrubyスクリプトのサンプルは、Excel操縦用ライブラリ exlap.rb 最新版: exlap_latest.zipに同梱のものを利用するとの前提で書かれています。

qt01.rb: mdbデータベースのsql実行結果をワークシートに読み込んで保存(odbc版)
qt02.rb: 設定済みクエリテーブルを更新する
qt03.rb: 複数の設定済みクエリテーブルを総て一括更新
qt03_2.rb: ワークブックに記録されているクエリテーブル総ての名前を出力
qt04.rb: mdbデータベースのsql実行結果をワークシートに読み込んで保存(ado版)
qt05.rb: webのtableを取り込む(web page全体ではない)
qt05_2.rb: webページ全体を取り込む
qt06.rb: テキストファイルを取り込む

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
}

download qt01.rb

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")
  }
}

download qt02.rb

qt03.rb

#! ruby -Ks
  # 複数の設定済みクエリテーブルを総て一括更新
require "exlap"
require "yado"

dbname = "test.mdb"
tblname = "身長と誕生日"
sql1 = "select 身長,氏名,ID,誕生日 from #{tblname} order by 身長 desc;"
sql2 = "select 誕生日,氏名,ID,身長 from #{tblname} order by 誕生日;"
cnn = "ODBC;DSN=MS Access Database;DBQ=#{Exl::getAbsolutePath(dbname)}"

filename = "test03.xls"
Exlap.new(filename) {|wb|
  ss = wb.fes  # first_empty_sheet
  ss.Name = "身長順のシート"
  qt = ss.QueryTables.Add({
    'Connection'=>cnn,
    'Destination'=>ss.Range("A1"),
    'Sql'=>sql1})
  qt.Name = "身長順"  # クエリテーブルの名前
  qt.SaveData = true  # 「クエリテーブルをワークブックと一緒に保存」する
  qt.BackgroundQuery = false  # バックグラウンド処理をしない
  qt.Refresh

  ss = wb.fes
  ss.Name = "誕生日順のシート"
  qt = ss.QueryTables.Add({
    'Connection'=>cnn,
    'Destination'=>ss.Range("A1"),
    'Sql'=>sql2})
  qt.Name = "誕生日順"  # クエリテーブルの名前
  qt.SaveData = true  # 「クエリテーブルをワークブックと一緒に保存」する
  qt.BackgroundQuery = false  # バックグラウンド処理をしない
  qt.Refresh
  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")
  }
}

    # データベース更新
db = Yado.new dbname
ary = [%w(氏名 身長  誕生日),
       %w(渡辺 192.3 1985/11/19),
       %w(山下 156.7 1971/8/3)]
db.addnew(tblname, ary)
db.close

    # 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")
  }
}

download qt03.rb

qt03_2.rb

#! ruby -Ks
  # ワークブックに記録されているクエリテーブル総ての名前を出力
require "exlap"

filename = "test03.xls"
Exlap.new(filename) {|wb|
  wb.each {|ss|
    next  if ss.QueryTables.Count < 1
    printf("＊ワークシート名: %s\n", ss.Name)
    ss.QueryTables.each {|qt|
      printf("\t%s\n", qt.Name)
    }
  }
}

download qt03_2.rb

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

download qt04.rb

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
}

download qt05.rb

qt05_2.rb

#! ruby -Ks
  # webページ全体を取り込む
require "exlap"

url = 'http://www.data.jma.go.jp/obd/stats/data/mdrr/synopday/data1s.html'
filename = "test05_2.xls"
Exlap.new(filename) {|wb|
  ss = wb.fes
  ss.Name = "気象情報のシート"
  qt = ss.QueryTables.Add({
    'Connection'=>"URL;#{url}",
    'Destination'=>ss.Range("A1")})
  qt.Name = "気象情報"
  qt.WebSelectionType = XlEntirePage  # ページ全体
  qt.WebFormatting = XlWebFormattingAll
  qt.SaveData = false  # 「クエリテーブルをワークブックと一緒に保存」をしない
  qt.RefreshPeriod = 0  # 「定期的更新」の時間(0で無効)
  qt.BackgroundQuery = false
  qt.Refresh
  wb.save
}

download qt05_2.rb

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
}

download qt06.rb

exlapのページへ戻る