以下に掲げるrubyスクリプトのサンプルは、Excel操縦用ライブラリ exlap.rb 最新版: exlap_latest.zipに同梱のものを利用するとの前提で書かれています。
目次
#! ruby -Ks
# 性別の平均身長と平均体重のピボットテーブル集計表を作成
require "exlap"
data_list = [ # 10人分の氏名、性別、身長、体重
%w(氏名 性別 身長 体重),
%w(安部 男 158.9 50.6),
%w(伊藤 女 151.0 50.7),
%w(上村 男 178.4 73.9),
%w(榎本 女 164.0 70.6),
%w(小田 男 161.8 64.7),
%w(佐藤 女 167.9 62.1),
%w(篠原 男 174.6 71.5),
%w(杉山 女 173.9 51.5),
%w(千田 男 179.6 65.2),
%w(相馬 女 151.6 51.3)]
filename = "test01.xls"
Exlap.new(filename) {|wb|
# 元になるデータをワークシートに記録
ss = wb.fes # 最初の空のシートを選択
ss.Name = "元データのシート"
data_list.each_with_index {|row, i|
rn = i + 1
ss.rr(rn,1, rn,row.size).Value = row
}
y1,x1, y2,x2 = ss.range_address # データ記録領域の番地を取得
# 上記データの領域を「名前付き範囲」に指定
range_name = "性別と身長と体重" # 範囲に付ける名前
range_area = sprintf("R%dC%d:R%dC%d", y1, x1, y2, x2)
wb.obj.Names.Add({
'Name'=>range_name,
'RefersTo'=>"=#{ss.Name}!#{range_area}"})
# 次シートにピボットテーブルを設定
ss2 = wb.fes
ss2.Name = "ピボットテーブルのシート"
ss2.Activate # ピボットテーブルを設けるシートに焦点を当てる
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlDatabase,
'SourceData'=>range_name})
ptname = "ピボット01" # ピボットテーブルの名前
pivot_cache.CreatePivotTable({
'TableDestination'=>ss2.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt = ss2.PivotTables(ptname) # ピボットテーブルオブジェクトをptに
pt.SmallGrid = false
# 項目分類の注目欄として性別を指定。横方向(列)に広げる形に設定
ptf = pt.PivotFields("性別")
ptf.Orientation = XlColumnField # 「列」方向に並べるフィールドとする
# 集計用の注目欄として「身長」を指定。平均を採ることも指定。
ptf = pt.PivotFields("身長")
ptf.Orientation = XlDataField # 集計して新たに設ける欄であるとの指定
ptf.Function = XlAverage # 集計方法を「平均を求める」に設定
# 「体重」に関する設定(「身長」と同じ)
ptf = pt.PivotFields("体重")
ptf.Orientation = XlDataField
ptf.Function = XlAverage
wb.save
}
download pt01.rb
#! ruby -Ks
# AddFields, AddDataField を用いてピボットフィールドを設定する
require "exlap"
data_list = [ # 10人分の氏名、性別、身長、体重
%w(氏名 性別 身長 体重),
%w(安部 男 158.9 50.6),
%w(伊藤 女 151.0 50.7),
%w(上村 男 178.4 73.9),
%w(榎本 女 164.0 70.6),
%w(小田 男 161.8 64.7),
%w(佐藤 女 167.9 62.1),
%w(篠原 男 174.6 71.5),
%w(杉山 女 173.9 51.5),
%w(千田 男 179.6 65.2),
%w(相馬 女 151.6 51.3)]
filename = "test01_2.xls"
Exlap.new(filename) {|wb|
# 元になるデータをワークシートに記録
ss = wb.fes # 最初の空のシートを選択
ss.Name = "元データのシート"
data_list.each_with_index {|row, i|
rn = i + 1
ss.rr(rn,1, rn,row.size).Value = row
}
y1,x1, y2,x2 = ss.range_address # データ記録領域の番地を取得
# 上記データの領域を「名前付き範囲」に指定
range_name = "性別と身長と体重" # 範囲に付ける名前
range_area = sprintf("R%dC%d:R%dC%d", y1, x1, y2, x2)
wb.obj.Names.Add({
'Name'=>range_name,
'RefersTo'=>"=#{ss.Name}!#{range_area}"})
# 次シートにピボットテーブルを設定
ss2 = wb.fes
ss2.Name = "ピボットテーブルのシート"
ss2.Activate # ピボットテーブルを設けるシートに焦点を当てる
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlDatabase,
'SourceData'=>range_name})
ptname = "ピボット01" # ピボットテーブルの名前
pt = pivot_cache.CreatePivotTable({
'TableDestination'=>ss2.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt.SmallGrid = false
pt.AddFields({'ColumnFields'=>"性別"})
pt.AddDataField({'Field'=>pt.PivotFields("身長"), 'Function'=>XlAverage})
pt.AddDataField({'Field'=>pt.PivotFields("体重"), 'Function'=>XlAverage})
wb.save
}
download pt01_2.rb
#! ruby -Ks
# ピボットフィールドの見出し、数値表示形式、項目順序に関する操作
require "exlap"
data_list = [ # 10人分の氏名、性別、身長、体重
%w(氏名 性別 身長 体重),
%w(安部 男 158.9 50.6),
%w(伊藤 女 151.0 50.7),
%w(上村 男 178.4 73.9),
%w(榎本 女 164.0 70.6),
%w(小田 男 161.8 64.7),
%w(佐藤 女 167.9 62.1),
%w(篠原 男 174.6 71.5),
%w(杉山 女 173.9 51.5),
%w(千田 男 179.6 65.2),
%w(相馬 女 151.6 51.3)]
filename = "test01_3.xls"
Exlap.new(filename) {|wb|
# 元になるデータをワークシートに記録
ss = wb.fes # 最初の空のシートを選択
ss.Name = "元データのシート"
data_list.each_with_index {|row, i|
rn = i + 1
ss.rr(rn,1, rn,row.size).Value = row
}
y1,x1, y2,x2 = ss.range_address # データ記録領域の番地を取得
# 上記データの領域を「名前付き範囲」に指定
range_name = "性別と身長と体重" # 範囲に付ける名前
range_area = sprintf("R%dC%d:R%dC%d", y1, x1, y2, x2)
wb.obj.Names.Add({
'Name'=>range_name,
'RefersTo'=>"=#{ss.Name}!#{range_area}"})
# 次シートにピボットテーブルを設定
ss2 = wb.fes
ss2.Name = "ピボットテーブルのシート"
ss2.Activate # ピボットテーブルを設けるシートに焦点を当てる
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlDatabase,
'SourceData'=>range_name})
ptname = "ピボット01" # ピボットテーブルの名前
pivot_cache.CreatePivotTable({
'TableDestination'=>ss2.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt = ss2.PivotTables(ptname) # ピボットテーブルオブジェクトをptに
pt.SmallGrid = false
# 項目分類の注目欄として性別を指定。横方向(列)に広げる形に設定
ptf = pt.PivotFields("性別")
ptf.Orientation = XlColumnField # 「列」方向に並べるフィールドとする
ptf.PivotItems("男").Position = 1
ptf.PivotItems("女").Position = 2
# 集計用の注目欄として「身長」を指定。平均を採ることも指定。
ptf = pt.PivotFields("身長")
ptf.Orientation = XlDataField # 集計して新たに設ける欄であるとの指定
ptf.Function = XlAverage # 集計方法を「平均を求める」に設定
ptf.Caption = "身長の平均"
ptf.NumberFormat = "0.0"
# 「体重」に関する設定(「身長」と同じ)
ptf = pt.PivotFields("体重")
ptf.Orientation = XlDataField
ptf.Function = XlAverage
ptf.Caption = "体重の平均"
ptf.NumberFormat = "0.0"
wb.save
}
download pt01_3.rb
#! ruby -Ks
# ピボットテーブルの更新
require "exlap"
data_list = [ # 10人分の氏名、性別、身長、体重
%w(氏名 性別 身長 体重),
%w(安部 男 158.9 50.6),
%w(伊藤 女 151.0 50.7),
%w(上村 男 178.4 73.9),
%w(榎本 女 164.0 70.6),
%w(小田 男 161.8 64.7),
%w(佐藤 女 167.9 62.1),
%w(篠原 男 174.6 71.5),
%w(杉山 女 173.9 51.5),
%w(千田 男 179.6 65.2),
%w(相馬 女 151.6 51.3)]
filename = "test01_4.xls"
Exlap.new(filename) {|wb|
# 元になるデータをワークシートに記録
ss = wb.fes # 最初の空のシートを選択
ss.Name = "元データのシート"
data_list.each_with_index {|row, i|
rn = i + 1
ss.rr(rn,1, rn,row.size).Value = row
}
y1,x1, y2,x2 = ss.range_address # データ記録領域の番地を取得
# 上記データの領域を「名前付き範囲」に指定
range_name = "性別と身長と体重" # 範囲に付ける名前
range_area = sprintf("R%dC%d:R%dC%d", y1, x1, y2, x2)
wb.obj.Names.Add({
'Name'=>range_name,
'RefersTo'=>"=#{ss.Name}!#{range_area}"})
# 次シートにピボットテーブルを設定
ss2 = wb.fes
ss2.Name = "ピボットテーブルのシート"
ss2.Activate # ピボットテーブルを設けるシートに焦点を当てる
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlDatabase,
'SourceData'=>range_name})
ptname = "ピボット01" # ピボットテーブルの名前
pivot_cache.CreatePivotTable({
'TableDestination'=>ss2.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt = ss2.PivotTables(ptname) # ピボットテーブルオブジェクトをptに
pt.SmallGrid = false
# 項目分類の注目欄として性別を指定。横方向(列)に広げる形に設定
ptf = pt.PivotFields("性別")
ptf.Orientation = XlColumnField # 「列」方向に並べるフィールドとする
# 集計用の注目欄として「身長」を指定。平均を採ることも指定。
ptf = pt.PivotFields("身長")
ptf.Orientation = XlDataField # 集計して新たに設ける欄であるとの指定
ptf.Function = XlAverage # 集計方法を「平均を求める」に設定
# 「体重」に関する設定(「身長」と同じ)
ptf = pt.PivotFields("体重")
ptf.Orientation = XlDataField
ptf.Function = XlAverage
# ２人分を元データに追加
ary = [
%w(田中 男 167.3 66.4),
%w(須藤 女 160.5 48.9)]
ss = wb.ss("元データのシート")
y,x = ss.range_last
ary.each {|row|
y += 1
ss.rr(y,1, y,row.size).Value = row
}
ss.Range(range_name).CurrentRegion.Name = range_name # 名前付き範囲の再設定
# ピボットテーブルの更新
ss2 = wb.ss("ピボットテーブルのシート")
ss2.Activate
pt = ss2.PivotTables(ptname)
pt.RefreshTable
wb.save
}
download pt01_4.rb
#! ruby -Ks
# 「性別」の１つの欄だけに注目して表を生成：人数表示
require "exlap"
data_list = [ # 10人分の氏名、性別、身長、体重
%w(氏名 性別 身長 体重),
%w(安部 男 158.9 50.6),
%w(伊藤 女 151.0 50.7),
%w(上村 男 178.4 73.9),
%w(榎本 女 164.0 70.6),
%w(小田 男 161.8 64.7),
%w(佐藤 女 167.9 62.1),
%w(篠原 男 174.6 71.5),
%w(杉山 女 173.9 51.5),
%w(千田 男 179.6 65.2),
%w(相馬 女 151.6 51.3)]
filename = "test02.xls"
Exlap.new(filename) {|wb|
# 元になるデータをワークシートに記録
ss = wb.fes # 最初の空のシートを選択
ss.Name = "元データのシート"
data_list.each_with_index {|row, i|
rn = i + 1
ss.rr(rn,1, rn,row.size).Value = row
}
y1,x1, y2,x2 = ss.range_address # データ記録領域の番地を取得
# 上記データの領域を「名前付き範囲」に指定
range_name = "性別と身長と体重" # 範囲に付ける名前
range_area = sprintf("R%dC%d:R%dC%d", y1, x1, y2, x2)
wb.obj.Names.Add({
'Name'=>range_name,
'RefersTo'=>"=#{ss.Name}!#{range_area}"})
# 次シートにピボットテーブルを設定
ss2 = wb.fes
ss2.Name = "ピボットテーブルのシート"
ss2.Activate # ピボットテーブルを設けるシートに焦点を当てる
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlDatabase,
'SourceData'=>range_name})
ptname = "ピボット01" # ピボットテーブルの名前
pivot_cache.CreatePivotTable({
'TableDestination'=>ss2.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt = ss2.PivotTables(ptname) # ピボットテーブルオブジェクトをptに
pt.SmallGrid = false
# 項目分類の注目欄として性別を指定。横方向(列)に広げる形に設定
ptf = pt.PivotFields("性別")
ptf.Orientation = XlColumnField # 「列」方向に並べるフィールドとする
# 集計用の注目欄としても「性別」を指定。個数(人数)を取る
ptf = pt.PivotFields("性別")
ptf.Orientation = XlDataField
ptf.Function = XlCount
ptf.Caption = "性別の人数" # 「個数/性別」の見出しを変更
wb.save
}
download pt02.rb
#! ruby -Ks
# 「性別」の１つの欄だけに注目して表を生成：構成比表示
require "exlap"
data_list = [ # 10人分の氏名、性別、身長、体重
%w(氏名 性別 身長 体重),
%w(安部 男 158.9 50.6),
%w(伊藤 女 151.0 50.7),
%w(上村 男 178.4 73.9),
%w(榎本 女 164.0 70.6),
%w(小田 男 161.8 64.7),
%w(佐藤 女 167.9 62.1),
%w(篠原 男 174.6 71.5),
%w(杉山 女 173.9 51.5),
%w(千田 男 179.6 65.2),
%w(相馬 女 151.6 51.3)]
filename = "test02_2.xls"
Exlap.new(filename) {|wb|
# 元になるデータをワークシートに記録
ss = wb.fes # 最初の空のシートを選択
ss.Name = "元データのシート"
data_list.each_with_index {|row, i|
rn = i + 1
ss.rr(rn,1, rn,row.size).Value = row
}
y1,x1, y2,x2 = ss.range_address # データ記録領域の番地を取得
# 上記データの領域を「名前付き範囲」に指定
range_name = "性別と身長と体重" # 範囲に付ける名前
range_area = sprintf("R%dC%d:R%dC%d", y1, x1, y2, x2)
wb.obj.Names.Add({
'Name'=>range_name,
'RefersTo'=>"=#{ss.Name}!#{range_area}"})
# 次シートにピボットテーブルを設定
ss2 = wb.fes
ss2.Name = "ピボットテーブルのシート"
ss2.Activate # ピボットテーブルを設けるシートに焦点を当てる
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlDatabase,
'SourceData'=>range_name})
ptname = "ピボット01" # ピボットテーブルの名前
pivot_cache.CreatePivotTable({
'TableDestination'=>ss2.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt = ss2.PivotTables(ptname) # ピボットテーブルオブジェクトをptに
pt.SmallGrid = false
# 項目分類の注目欄として性別を指定。横方向(列)に広げる形に設定
ptf = pt.PivotFields("性別")
ptf.Orientation = XlColumnField # 「列」方向に並べるフィールドとする
# 集計用の注目欄としても「性別」を指定。構成比(％)を取る
ptf = pt.PivotFields("性別")
ptf.Orientation = XlDataField
ptf.Function = XlCount
ptf.Caption = "性別の構成比（％）"
ptf.Calculation = XlPercentOfRow # 「行」における構成比
wb.save
}
download pt02_2.rb
#! ruby -Ks
# 同一ワークシートに２つのピボットテーブルを作成
require "exlap"
data_list = [ # 10人分の氏名、性別、身長、体重
%w(氏名 性別 身長 体重),
%w(安部 男 158.9 50.6),
%w(伊藤 女 151.0 50.7),
%w(上村 男 178.4 73.9),
%w(榎本 女 164.0 70.6),
%w(小田 男 161.8 64.7),
%w(佐藤 女 167.9 62.1),
%w(篠原 男 174.6 71.5),
%w(杉山 女 173.9 51.5),
%w(千田 男 179.6 65.2),
%w(相馬 女 151.6 51.3)]
filename = "test03.xls"
Exlap.new(filename) {|wb|
# 元になるデータをワークシートに記録
ss = wb.fes # 最初の空のシートを選択
ss.Name = "元データのシート"
data_list.each_with_index {|row, i|
rn = i + 1
ss.rr(rn,1, rn,row.size).Value = row
}
y1,x1, y2,x2 = ss.range_address # データ記録領域の番地を取得
# 上記データの領域を「名前付き範囲」に指定
range_name = "性別と身長と体重" # 範囲に付ける名前
range_area = sprintf("R%dC%d:R%dC%d", y1, x1, y2, x2)
wb.obj.Names.Add({
'Name'=>range_name,
'RefersTo'=>"=#{ss.Name}!#{range_area}"})
# 次シートにピボットテーブルを設定
ss2 = wb.fes
ss2.Name = "ピボットテーブルのシート"
ss2.Activate # ピボットテーブルを設けるシートに焦点を当てる
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlDatabase,
'SourceData'=>range_name})
# 第１のピボットテーブルを作成
ptname = "ピボット01" # ピボットテーブルの名前
pivot_cache.CreatePivotTable({
'TableDestination'=>ss2.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt = ss2.PivotTables(ptname) # ピボットテーブルオブジェクトをptに
pt.SmallGrid = false
ptf = pt.PivotFields("性別")
ptf.Orientation = XlColumnField # 「列」方向に並べるフィールドとする
ptf = pt.PivotFields("身長")
ptf.Orientation = XlDataField # 集計して新たに設ける欄であるとの指定
ptf.Function = XlAverage # 集計方法を「平均を求める」に設定
ptf = pt.PivotFields("体重")
ptf.Orientation = XlDataField
ptf.Function = XlAverage
# 第２ピボットテーブル作成の準備
rng = pt.TableRange2 # 第１ピボットテーブルのRangeをrngに代入
y,x = ss2.range_last(rng) # 第１ピボットテーブルの最終番地を得る
start_cell = ss2.cell(y+3,1) # 第２ピボットテーブルの始点を２行空け後に
# 第２ピボットテーブルの作成
ptname = "ピボット02" # ピボットテーブルの名前
pivot_cache.CreatePivotTable({
'TableDestination'=>start_cell,
'TableName'=>ptname})
pt = ss2.PivotTables(ptname)
pt.SmallGrid = false
ptf = pt.PivotFields("性別")
ptf.Orientation = XlColumnField # 「列」方向に並べるフィールドとする
ptf = pt.PivotFields("性別")
ptf.Orientation = XlDataField
ptf.Function = XlCount
ptf.Caption = "性別の人数" # 「個数/性別」の見出しを変更
wb.save
}
download pt03.rb
#! ruby -Ks
# ワークブック内の総てのピボットテーブルの名前を出力
require "exlap"
filename = "test03.xls"
Exlap.new(filename) {|wb|
wb.each {|ss|
next if ss.PivotTables.Count < 1 # ピボットテーブルがないのでskip
puts "*ワークシート名:" + ss.Name
ss.PivotTables.each {|pt|
puts "\t" + pt.Name
}
}
}
download pt03_2.rb
#! ruby -Ks
# ワークブック内の総てのピボットテーブルを削除
require "exlap"
filename = "test03.xls"
Exlap.new(filename) {|wb|
wb.each {|ss|
next if ss.PivotTables.Count < 1 # ピボットテーブルがないのでskip
ss.Activate
ss.PivotTables.each {|pt|
pt.TableRange2.Clear
}
}
wb.save
}
download pt03_3.rb
#! ruby -Ks
# odbc接続で別のExcelワークブックのデータを材料にする
require "exlap"
source_name = "source.xls"
cnn = "ODBC;DSN=Excel Files;DBQ=#{Exl::getAbsolutePath(source_name)}"
tblname = "性別の身長と体重"
sql = "SELECT * FROM [#{tblname}$];"
filename = "test04.xls"
Exlap.new(filename) {|wb|
# シートにピボットテーブルを設定
ss = wb.fes
ss.Name = "ピボットテーブルのシート"
ss.Activate # ピボットテーブルを設けるシートに焦点を当てる
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlExternal})
pivot_cache.Connection = cnn
pivot_cache.CommandText = sql
ptname = "ピボット01" # ピボットテーブルの名前
pivot_cache.CreatePivotTable({
'TableDestination'=>ss.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt = ss.PivotTables(ptname) # ピボットテーブルオブジェクトをptに
pt.SmallGrid = false
# 項目分類の注目欄として性別を指定。横方向(列)に広げる形に設定
ptf = pt.PivotFields("性別")
ptf.Orientation = XlColumnField # 「列」方向に並べるフィールドとする
# 集計用の注目欄としても「性別」を指定。個数(人数)を取る
ptf = pt.PivotFields("性別")
ptf.Orientation = XlDataField
ptf.Function = XlCount
ptf.Caption = "性別の人数" # 「個数/性別」の見出しを変更
wb.save
}
download pt04.rb
#! ruby -Ks
# odbc接続でAccessデータベースのデータを材料にする
require "exlap"
source_name = "source.mdb"
cnn = "ODBC;DSN=MS Access Database;DBQ=#{Exl::getAbsolutePath(source_name)}"
tblname = "性別の身長と体重"
sql = "SELECT * FROM #{tblname};"
filename = "test04_2.xls"
Exlap.new(filename) {|wb|
# シートにピボットテーブルを設定
ss = wb.fes
ss.Name = "ピボットテーブルのシート"
ss.Activate # ピボットテーブルを設けるシートに焦点を当てる
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlExternal})
pivot_cache.Connection = cnn
pivot_cache.CommandText = sql
ptname = "ピボット01" # ピボットテーブルの名前
pivot_cache.CreatePivotTable({
'TableDestination'=>ss.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt = ss.PivotTables(ptname) # ピボットテーブルオブジェクトをptに
pt.SmallGrid = false
# 項目分類の注目欄として性別を指定。横方向(列)に広げる形に設定
ptf = pt.PivotFields("性別")
ptf.Orientation = XlColumnField # 「列」方向に並べるフィールドとする
# 集計用の注目欄としても「性別」を指定。個数(人数)を取る
ptf = pt.PivotFields("性別")
ptf.Orientation = XlDataField
ptf.Function = XlCount
ptf.Caption = "性別の人数" # 「個数/性別」の見出しを変更
wb.save
}
download pt04_2.rb
#! ruby -Ks
# ado接続で別のExcelワークブックのデータを材料にする
require "exlap"
source_name = "source.xls"
tblname = "性別の身長と体重"
sql = "SELECT * FROM [#{tblname}$];"
# ado接続の準備
cnn_xls = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=#{Exl::getAbsolutePath(source_name)};" +
"Extended Properties=\"Excel 8.0;HDR=Yes;\""
cnn_xlsx = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=#{Exl::getAbsolutePath(source_name)};" +
"Extended Properties=\"Excel 12.0;HDR=Yes;\""
cnn = nil
case File.extname(source_name).downcase
when '.xls'
cnn = cnn_xls
when '.xlsx'
cnn = cnn_xlsx
end
unless cnn
exit
end
cn = WIN32OLE.new("ADODB.Connection")
rs = WIN32OLE.new("ADODB.Recordset")
rs.CursorLocation = 3 # adUseClient クライアント側カーソルを使用
filename = "test04_3.xls"
Exlap.new(filename) {|wb|
# シートにピボットテーブルを設定
ss = wb.fes
ss.Name = "ピボットテーブルのシート"
ss.Activate # ピボットテーブルを設けるシートに焦点を当てる
cn.Open cnn; rs.Open sql,cn # ado接続
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlExternal})
pivot_cache.Recordset = rs
rs.Close; cn.Close # adoを閉じる
ptname = "ピボット01" # ピボットテーブルの名前
pivot_cache.CreatePivotTable({
'TableDestination'=>ss.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt = ss.PivotTables(ptname) # ピボットテーブルオブジェクトをptに
pt.SmallGrid = false
# 項目分類の注目欄として性別を指定。横方向(列)に広げる形に設定
ptf = pt.PivotFields("性別")
ptf.Orientation = XlColumnField # 「列」方向に並べるフィールドとする
# 集計用の注目欄としても「性別」を指定。個数(人数)を取る
ptf = pt.PivotFields("性別")
ptf.Orientation = XlDataField
ptf.Function = XlCount
ptf.Caption = "性別の人数" # 「個数/性別」の見出しを変更
wb.save
}
download pt04_3.rb
#! ruby -Ks
# ado接続でAccessデータベースのデータを材料にピボットテーブルを作成
require "exlap"
source_name = "source.mdb"
tblname = "性別の身長と体重"
sql = "SELECT * FROM #{tblname};"
# ado接続の準備
cnn = nil
case File.extname(source_name).downcase
when '.mdb'
cnn = "Provider=Microsoft.Jet.OLEDB.4.0;"
when '.accdb'
cnn = "Provider=Microsoft.ACE.OLEDB.12.0;"
end
unless cnn
exit
end
cnn = cnn + "Data Source=#{Exl::getAbsolutePath(source_name)};"
cn = WIN32OLE.new("ADODB.Connection")
rs = WIN32OLE.new("ADODB.Recordset")
rs.CursorLocation = 3 # adUseClient クライアント側カーソルを使用
filename = "test04_4.xls"
Exlap.new(filename) {|wb|
# シートにピボットテーブルを設定
ss = wb.fes
ss.Name = "ピボットテーブルのシート"
ss.Activate # ピボットテーブルを設けるシートに焦点を当てる
cn.Open cnn; rs.Open sql,cn # ado接続
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlExternal})
pivot_cache.Recordset = rs
rs.Close; cn.Close # adoを閉じる
ptname = "ピボット01" # ピボットテーブルの名前
pivot_cache.CreatePivotTable({
'TableDestination'=>ss.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt = ss.PivotTables(ptname) # ピボットテーブルオブジェクトをptに
pt.SmallGrid = false
# 項目分類の注目欄として性別を指定。横方向(列)に広げる形に設定
ptf = pt.PivotFields("性別")
ptf.Orientation = XlColumnField # 「列」方向に並べるフィールドとする
# 集計用の注目欄としても「性別」を指定。個数(人数)を取る
ptf = pt.PivotFields("性別")
ptf.Orientation = XlDataField
ptf.Function = XlCount
ptf.Caption = "性別の人数" # 「個数/性別」の見出しを変更
wb.save
}
download pt04_4.rb
#! ruby -Ks
# ado接続の場合のピボットキャッシュの更新(source.xls, test04_3.xls)
require "exlap"
source_name = "source.xls"
tblname = "性別の身長と体重"
sql = "SELECT * FROM [#{tblname}$];"
cnn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=#{Exl::getAbsolutePath(source_name)};" +
"Extended Properties=\"Excel 8.0;HDR=Yes;\""
cn = WIN32OLE.new("ADODB.Connection")
rs = WIN32OLE.new("ADODB.Recordset")
rs.CursorLocation = 3 # adUseClient クライアント側カーソルを使用
filename = "test04_3.xls"
sheet_name = "ピボットテーブルのシート" # ワークシートの名前
pivot_name = "ピボット01" # ピボットテーブルの名前
Exlap.new(filename) {|wb|
ss = wb.ss(sheet_name)
ss.Activate # ピボットテーブルのあるシートに焦点を当てる
pt = ss.PivotTables(pivot_name) # ピボットテーブルオブジェクトをptに
cn.Open cnn; rs.Open sql,cn # ado接続
pivot_cache = pt.PivotCache
pivot_cache.Recordset = rs
pivot_cache.Refresh
rs.Close; cn.Close # adoを閉じる
wb.save
}
download pt_ado01.rb
#! ruby -Ks
# ado接続の場合のピボットキャッシュの更新(source.mdb, test04_4.xls)
require "exlap"
source_name = "source.mdb"
tblname = "性別の身長と体重"
sql = "SELECT * FROM #{tblname};"
cnn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=#{Exl::getAbsolutePath(source_name)};"
cn = WIN32OLE.new("ADODB.Connection")
rs = WIN32OLE.new("ADODB.Recordset")
rs.CursorLocation = 3 # adUseClient クライアント側カーソルを使用
filename = "test04_4.xls"
sheet_name = "ピボットテーブルのシート" # ワークシートの名前
pivot_name = "ピボット01" # ピボットテーブルの名前
Exlap.new(filename) {|wb|
ss = wb.ss(sheet_name)
ss.Activate # ピボットテーブルのあるシートに焦点を当てる
pt = ss.PivotTables(pivot_name) # ピボットテーブルオブジェクトをptに
cn.Open cnn; rs.Open sql,cn # ado接続
pivot_cache = pt.PivotCache
pivot_cache.Recordset = rs
pivot_cache.Refresh
rs.Close; cn.Close # adoを閉じる
wb.save
}
download pt_ado02.rb
#! ruby -Ks
# 身長によるグループ化(150-160, 160-170, 170-180)
require "exlap"
data_list = [ # 10人分の氏名、性別、身長、体重
%w(氏名 性別 身長 体重),
%w(安部 男 158.9 50.6),
%w(伊藤 女 151.0 50.7),
%w(上村 男 178.4 73.9),
%w(榎本 女 164.0 70.6),
%w(小田 男 161.8 64.7),
%w(佐藤 女 167.9 62.1),
%w(篠原 男 174.6 71.5),
%w(杉山 女 173.9 51.5),
%w(千田 男 179.6 65.2),
%w(相馬 女 151.6 51.3)]
filename = "test05.xls"
Exlap.new(filename) {|wb|
# 元になるデータをワークシートに記録
ss = wb.fes # 最初の空のシートを選択
ss.Name = "元データのシート"
data_list.each_with_index {|row, i|
rn = i + 1
ss.rr(rn,1, rn,row.size).Value = row
}
y1,x1, y2,x2 = ss.range_address # データ記録領域の番地を取得
# 上記データの領域を「名前付き範囲」に指定
range_name = "性別と身長と体重" # 範囲に付ける名前
range_area = sprintf("R%dC%d:R%dC%d", y1, x1, y2, x2)
wb.obj.Names.Add({
'Name'=>range_name,
'RefersTo'=>"=#{ss.Name}!#{range_area}"})
# 次シートにピボットテーブルを設定
ss2 = wb.fes
ss2.Name = "ピボットテーブルのシート"
ss2.Activate # ピボットテーブルを設けるシートに焦点を当てる
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlDatabase,
'SourceData'=>range_name})
ptname = "ピボット01" # ピボットテーブルの名前
pivot_cache.CreatePivotTable({
'TableDestination'=>ss2.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt = ss2.PivotTables(ptname) # ピボットテーブルオブジェクトをptに
pt.SmallGrid = false
ptf1 = pt.PivotFields("身長")
ptf1.Orientation = XlColumnField
ptf2 = pt.PivotFields("身長")
ptf2.Orientation = XlDataField
ptf2.Function = XlCount
ptf1.LabelRange.Group({'Start'=>150.0, 'By'=>10.0})
wb.save
}
download pt05.rb
#! ruby -Ks
# 「氏名」によるグループ化：個々の氏名を残さずグループだけ表示
require "exlap"
data_list = [ # 10人分の氏名、性別、身長、体重
%w(氏名 性別 身長 体重),
%w(安部 男 158.9 50.6),
%w(伊藤 女 151.0 50.7),
%w(上村 男 178.4 73.9),
%w(榎本 女 164.0 70.6),
%w(小田 男 161.8 64.7),
%w(佐藤 女 167.9 62.1),
%w(篠原 男 174.6 71.5),
%w(杉山 女 173.9 51.5),
%w(千田 男 179.6 65.2),
%w(相馬 女 151.6 51.3)]
filename = "test05_2.xls"
Exlap.new(filename) {|wb|
# 元になるデータをワークシートに記録
ss = wb.fes # 最初の空のシートを選択
ss.Name = "元データのシート"
data_list.each_with_index {|row, i|
rn = i + 1
ss.rr(rn,1, rn,row.size).Value = row
}
y1,x1, y2,x2 = ss.range_address # データ記録領域の番地を取得
# 上記データの領域を「名前付き範囲」に指定
range_name = "性別と身長と体重" # 範囲に付ける名前
range_area = sprintf("R%dC%d:R%dC%d", y1, x1, y2, x2)
wb.obj.Names.Add({
'Name'=>range_name,
'RefersTo'=>"=#{ss.Name}!#{range_area}"})
# 次シートにピボットテーブルを設定
ss2 = wb.fes
ss2.Name = "ピボットテーブルのシート"
ss2.Activate # ピボットテーブルを設けるシートに焦点を当てる
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlDatabase,
'SourceData'=>range_name})
ptname = "ピボット01" # ピボットテーブルの名前
pivot_cache.CreatePivotTable({
'TableDestination'=>ss2.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt = ss2.PivotTables(ptname) # ピボットテーブルオブジェクトをptに
pt.SmallGrid = false
# まず未加工の集計表を設計
ptf1 = pt.PivotFields("氏名")
ptf1.Orientation = XlColumnField
ptf2 = pt.PivotFields("身長")
ptf2.Orientation = XlDataField
ptf2.Function = XlAverage
# 以下がグループ化の処理
xl = pt.Application
xl.Union(ptf1.PivotItems("安部").LabelRange,
ptf1.PivotItems("伊藤").LabelRange,
ptf1.PivotItems("上村").LabelRange,
ptf1.PivotItems("榎本").LabelRange,
ptf1.PivotItems("小田").LabelRange).Group
xl.Union(ptf1.PivotItems("佐藤").LabelRange,
ptf1.PivotItems("篠原").LabelRange,
ptf1.PivotItems("杉山").LabelRange,
ptf1.PivotItems("千田").LabelRange,
ptf1.PivotItems("相馬").LabelRange).Group
ptf1p = ptf1.ParentField # 親フィールド(大きなカテゴリー)のオブジェクト
ptf1p.PivotItems(1).Name = "ア行の人々"
ptf1p.PivotItems(2).Name = "サ行の人々"
ptf1.Orientation = XlHidden # 個々の氏名を非表示に
wb.save
}
download pt05_2.rb
#! ruby -Ks
# 「氏名」によるグループ化：個々の氏名を残す
require "exlap"
data_list = [ # 10人分の氏名、性別、身長、体重
%w(氏名 性別 身長 体重),
%w(安部 男 158.9 50.6),
%w(伊藤 女 151.0 50.7),
%w(上村 男 178.4 73.9),
%w(榎本 女 164.0 70.6),
%w(小田 男 161.8 64.7),
%w(佐藤 女 167.9 62.1),
%w(篠原 男 174.6 71.5),
%w(杉山 女 173.9 51.5),
%w(千田 男 179.6 65.2),
%w(相馬 女 151.6 51.3)]
filename = "test05_3.xls"
Exlap.new(filename) {|wb|
# 元になるデータをワークシートに記録
ss = wb.fes # 最初の空のシートを選択
ss.Name = "元データのシート"
data_list.each_with_index {|row, i|
rn = i + 1
ss.rr(rn,1, rn,row.size).Value = row
}
y1,x1, y2,x2 = ss.range_address # データ記録領域の番地を取得
# 上記データの領域を「名前付き範囲」に指定
range_name = "性別と身長と体重" # 範囲に付ける名前
range_area = sprintf("R%dC%d:R%dC%d", y1, x1, y2, x2)
wb.obj.Names.Add({
'Name'=>range_name,
'RefersTo'=>"=#{ss.Name}!#{range_area}"})
# 次シートにピボットテーブルを設定
ss2 = wb.fes
ss2.Name = "ピボットテーブルのシート"
ss2.Activate # ピボットテーブルを設けるシートに焦点を当てる
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlDatabase,
'SourceData'=>range_name})
ptname = "ピボット01" # ピボットテーブルの名前
pivot_cache.CreatePivotTable({
'TableDestination'=>ss2.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt = ss2.PivotTables(ptname) # ピボットテーブルオブジェクトをptに
pt.SmallGrid = false
ptf1 = pt.PivotFields("氏名")
ptf1.Orientation = XlColumnField
ptf2 = pt.PivotFields("身長")
ptf2.Orientation = XlDataField
ptf2.Function = XlAverage
# 以下がグループ化の処理
person_names = [%w(安部 伊藤 上村 榎本 小田),
%w(佐藤 篠原 杉山 千田 相馬)]
person_names.each {|list|
param = []
list.each {|name|
param << ptf1.PivotItems(name).LabelRange
}
pt.Application.Union(*param).Group
}
# 親フィールドに関する設定
ptf1p = ptf1.ParentField # 親フィールド(大きなカテゴリー)のオブジェクト
%w(ア行の人々 サ行の人々).each_with_index {|name, i|
ptf1p.PivotItems(i+1).Name = name
}
ptf1p.Subtotals = [true] + [false]*11
ptf1p.SubtotalName = "グループ平均値"
# 「氏名」の並び順を「あいうえお」順に変更
i = 0
person_names.each {|list|
list.each {|name|
i += 1
ptf1.PivotItems(name).Position = i
}
}
wb.save
}
download pt05_3.rb
#! ruby -Ks
# 日付によるグループ化：四半期単位の売上げ合計を集計
require "exlap"
Mday = [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]
data_list = [["日付", "売上げ"]]
year = 2010
for month in 1..12
for day in 1..Mday[month-1]
dt = sprintf("%d/%d/%d", year, month, day)
val = rand(90) + 10
data_list << [dt, val]
end
end
filename = "test05_4.xls"
Exlap.new(filename) {|wb|
# 元になるデータをワークシートに記録
ss = wb.fes # 最初の空のシートを選択
ss.Name = "元データのシート"
data_list.each_with_index {|row, i|
rn = i + 1
ss.rr(rn,1, rn,row.size).Value = row
}
ss.range_autofit # 列幅等の自動調整
y1,x1, y2,x2 = ss.range_address # データ記録領域の番地を取得
# 上記データの領域を「名前付き範囲」に指定
range_name = "日付と売上げ" # 範囲に付ける名前
range_area = sprintf("R%dC%d:R%dC%d", y1, x1, y2, x2)
wb.obj.Names.Add({
'Name'=>range_name,
'RefersTo'=>"=#{ss.Name}!#{range_area}"})
# 次シートにピボットテーブルを設定
ss2 = wb.fes
ss2.Name = "ピボットテーブルのシート"
ss2.Activate # ピボットテーブルを設けるシートに焦点を当てる
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlDatabase,
'SourceData'=>range_name})
ptname = "ピボット01" # ピボットテーブルの名前
pivot_cache.CreatePivotTable({
'TableDestination'=>ss2.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt = ss2.PivotTables(ptname) # ピボットテーブルオブジェクトをptに
pt.SmallGrid = false
ptf1 = pt.PivotFields("日付")
ptf1.Orientation = XlRowField # 行方向(縦方向)に並べるとの指定
ptf2 = pt.PivotFields("売上げ")
ptf2.Orientation = XlDataField
ptf2.Function = XlSum
ary = [false]*5 + [true, false] # 四半期単位の指定
ptf1.LabelRange.Group({'Periods'=>ary})
# ptf1.Orientation = XlColumnField # 列方向に並べ直したい時に指定
wb.save
}
download pt05_4.rb
#! ruby -Ks
# 日付によるグループ化：月単位・四半期単位の売上げ合計を集計
require "exlap"
Mday = [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]
data_list = [["日付", "売上げ"]]
year = 2010
for month in 1..12
for day in 1..Mday[month-1]
dt = sprintf("%d/%d/%d", year, month, day)
val = rand(90) + 10
data_list << [dt, val]
end
end
filename = "test05_5.xls"
Exlap.new(filename) {|wb|
# 元になるデータをワークシートに記録
ss = wb.fes # 最初の空のシートを選択
ss.Name = "元データのシート"
data_list.each_with_index {|row, i|
rn = i + 1
ss.rr(rn,1, rn,row.size).Value = row
}
ss.range_autofit # 列幅等の自動調整
y1,x1, y2,x2 = ss.range_address # データ記録領域の番地を取得
# 上記データの領域を「名前付き範囲」に指定
range_name = "日付と売上げ" # 範囲に付ける名前
range_area = sprintf("R%dC%d:R%dC%d", y1, x1, y2, x2)
wb.obj.Names.Add({
'Name'=>range_name,
'RefersTo'=>"=#{ss.Name}!#{range_area}"})
# 次シートにピボットテーブルを設定
ss2 = wb.fes
ss2.Name = "ピボットテーブルのシート"
ss2.Activate # ピボットテーブルを設けるシートに焦点を当てる
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlDatabase,
'SourceData'=>range_name})
ptname = "ピボット01" # ピボットテーブルの名前
pivot_cache.CreatePivotTable({
'TableDestination'=>ss2.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt = ss2.PivotTables(ptname) # ピボットテーブルオブジェクトをptに
pt.SmallGrid = false
ptf1 = pt.PivotFields("日付")
ptf1.Orientation = XlRowField
ptf2 = pt.PivotFields("売上げ")
ptf2.Orientation = XlDataField
ptf2.Function = XlSum
ary = [false]*4 + [true, true, false]
ptf1.LabelRange.Group({'Periods'=>ary})
ptf1q = pt.PivotFields("四半期") # 「四半期」のピボットフィールド
ptf1q.Subtotals = [true] + [false]*11 # 四半期合計を表示
# ptf1.Orientation = ptf1q.Orientation = XlColumnField # 列方向並びに
wb.save
}
download pt05_5.rb
#! ruby -Ks
# 新たに「身長区分」の欄を設けて集計(グループ化の代替手法)
require "exlap"
data_list = [ # 10人分の氏名、性別、身長、体重
%w(氏名 性別 身長 体重),
%w(安部 男 158.9 50.6),
%w(伊藤 女 151.0 50.7),
%w(上村 男 178.4 73.9),
%w(榎本 女 164.0 70.6),
%w(小田 男 161.8 64.7),
%w(佐藤 女 167.9 62.1),
%w(篠原 男 174.6 71.5),
%w(杉山 女 173.9 51.5),
%w(千田 男 179.6 65.2),
%w(相馬 女 151.6 51.3)]
filename = "test05_6.xls"
Exlap.new(filename) {|wb|
# 元になるデータをワークシートに記録
ss = wb.fes # 空のシートを選択
ss.Name = "元データのシート" # ワークシート名
data_list.each_with_index {|row, i|
rn = i + 1
ss.rr(rn,1, rn,row.size).Value = row
}
y1,x1, y2,x2 = ss.range_address # データが書かれている領域の番地
# 「元データのシート」のコピーを作成
ss = wb.fes # 空のシートを選択
ss.Name = "元データの変更版シート"
wb.ss("元データのシート").rr(y1,x1, y2,x2).Copy ss.rr(y1,x1, y2,x2)
# 項目分類のための新設欄を設定
last_y, last_x = ss.range_last # データ領域の最終番地
height_y, height_x = Exl::yx(ss.findf("身長")) # 見出し「身長」の番地
ss[1, last_x+1] = "身長区分" # 新たな欄の見出しを書き込む
for y in 2..last_y
height = ss[y, height_x] # 各人の身長欄の値
if height >= 155.0 and height < 165.0
group = "155-165"
elsif height >= 165.0 and height < 175.0
group = "165-175"
else
group = "その他"
end
ss[y, last_x+1] = group # 各人の身長区分欄の書き込み
end
y1,x1, y2,x2 = ss.range_address
# 上記データの領域を「名前付き範囲」に指定
range_name = "性別・身長・体重" # 範囲に付ける名前
range_area = sprintf("R%dC%d:R%dC%d", y1, x1, y2, x2)
wb.obj.Names.Add({
'Name'=>range_name,
'RefersTo'=>"=#{ss.Name}!#{range_area}"})
# 次シートにピボットテーブルを設定
ss2 = wb.fes
ss2.Name = "ピボットテーブルのシート"
ss2.Activate # ピボットテーブルを設けるシートに焦点を当てる
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlDatabase,
'SourceData'=>range_name})
ptname = "ピボット01" # ピボットテーブルの名前
pivot_cache.CreatePivotTable({
'TableDestination'=>ss2.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt = ss2.PivotTables(ptname) # ピボットテーブルオブジェクトをptに
pt.SmallGrid = false
ptf1 = pt.PivotFields("身長区分")
ptf1.Orientation = XlColumnField
ptf2 = pt.PivotFields("身長区分")
ptf2.Orientation = XlDataField
ptf2.Function = XlCount
wb.save
}
download pt05_6.rb
#! ruby -Ks
# 支店×四半期について売上げの合計を算出：クロス集計表
require "exlap"
# 元データのファイルを開く
xl = Exlap.new
source_name = "source06.xls"
sheet_name = "支店・日付・売上げのシート"
wbs = xl.book_open source_name
ss = wbs.ss(sheet_name)
source_area = "[#{wbs.obj.Name}]#{ss.Name}!" +
ss.UsedRange.Address(true, true, XlR1C1)
# ピボットテーブル保存用のワークブックを開く
filename = "test06.xls"
wb = xl.book_open filename
ss = wb.fes
ss.Name = "基盤となるシート"
ss.Activate # ピボットテーブルを設けるシートに焦点を当てる
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlDatabase,
'SourceData'=>source_area})
ptname = "基盤となるピボット" # ピボットテーブルの名前
pivot_cache.CreatePivotTable({
'TableDestination'=>ss.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt = ss.PivotTables(ptname) # ピボットテーブルオブジェクトをptに
pt.SmallGrid = false
# 集計表の設定
ptf1 = pt.PivotFields("支店")
ptf1.Orientation = XlColumnField
ptf2 = pt.PivotFields("日付")
ptf2.Orientation = XlRowField
ptf3 = pt.PivotFields("売上げ")
ptf3.Orientation = XlDataField
ptf3.Function = XlSum
ary = [false]*5 + [true, false] # 四半期のみでのグループ化
ptf2.LabelRange.Group({'Periods'=>ary})
%w(東京 名古屋 大阪).each_with_index {|name, i|
ptf1.PivotItems(name).Position = i+1
}
wb.save
wb.close
wbs.close
xl.quit
download pt06.rb
#! ruby -Ks
# 支店をページフィールドに指定して売上げ合計を集計
require "exlap"
# 元データのファイルを開く
xl = Exlap.new
source_name = "source06.xls"
sheet_name = "支店・日付・売上げのシート"
wbs = xl.book_open source_name
ss = wbs.ss(sheet_name)
source_area = "[#{wbs.obj.Name}]#{ss.Name}!" +
ss.UsedRange.Address(true, true, XlR1C1)
# ピボットテーブル保存用のワークブックを開く
filename = "test06_2.xls"
wb = xl.book_open filename
ss = wb.fes
ss.Name = "基盤となるシート"
ss.Activate # ピボットテーブルを設けるシートに焦点を当てる
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlDatabase,
'SourceData'=>source_area})
ptname = "基盤となるピボット" # ピボットテーブルの名前
pivot_cache.CreatePivotTable({
'TableDestination'=>ss.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt = ss.PivotTables(ptname) # ピボットテーブルオブジェクトをptに
pt.SmallGrid = false
# 集計表の設定
ptf1 = pt.PivotFields("支店")
ptf1.Orientation = XlPageField
ptf2 = pt.PivotFields("日付")
ptf2.Orientation = XlRowField
ptf3 = pt.PivotFields("売上げ")
ptf3.Orientation = XlDataField
ptf3.Function = XlSum
ary = [false]*5 + [true, false] # 四半期のみでのグループ化
ptf2.LabelRange.Group({'Periods'=>ary})
%w(東京 名古屋 大阪).each_with_index {|name, i|
ptf1.PivotItems(name).Position = i+1
}
pt.ShowPages
wb.save
wb.close
wbs.close
xl.quit
download pt06_2.rb
#! ruby -Ks
# 日付(四半期)をページフィールドに指定して売上げ合計を集計
require "exlap"
# 元データのファイルを開く
xl = Exlap.new
source_name = "source06.xls"
sheet_name = "支店・日付・売上げのシート"
wbs = xl.book_open source_name
ss = wbs.ss(sheet_name)
source_area = "[#{wbs.obj.Name}]#{ss.Name}!" +
ss.UsedRange.Address(true, true, XlR1C1)
# ピボットテーブル保存用のワークブックを開く
filename = "test06_3.xls"
wb = xl.book_open filename
ss = wb.fes
ss.Name = "基盤となるシート"
ss.Activate # ピボットテーブルを設けるシートに焦点を当てる
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlDatabase,
'SourceData'=>source_area})
ptname = "基盤となるピボット" # ピボットテーブルの名前
pivot_cache.CreatePivotTable({
'TableDestination'=>ss.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt = ss.PivotTables(ptname) # ピボットテーブルオブジェクトをptに
pt.SmallGrid = false
# 集計表の設定
ptf1 = pt.PivotFields("支店")
ptf1.Orientation = XlColumnField
ptf2 = pt.PivotFields("日付")
ptf2.Orientation = XlRowField
ptf3 = pt.PivotFields("売上げ")
ptf3.Orientation = XlDataField
ptf3.Function = XlSum
ary = [false]*5 + [true, false] # 四半期のみでのグループ化
ptf2.LabelRange.Group({'Periods'=>ary})
%w(東京 名古屋 大阪).each_with_index {|name, i|
ptf1.PivotItems(name).Position = i+1
}
ptf2.Orientation = XlPageField
ptf2.PivotItems.each {|ptfi|
if ptfi.RecordCount < 1
ptfi.Visible = false
end
}
pt.ShowPages
wb.save
wb.close
wbs.close
xl.quit
download pt06_3.rb
#! ruby -Ks
# 複数の元データを統合して取り込んだ時の初期状態を確認
require "exlap"
# 元データのファイルを開く
xl = Exlap.new
wbs = xl.book_open "source07.xls"
source_data = []
wbs.each {|ss|
if ss.Name =~ /^(.+)のシート$/
branch = $1
area = "[#{wbs.obj.Name}]#{ss.Name}!" +
ss.UsedRange.Address(true, true, XlR1C1)
source_data << [area, branch]
end
}
# ピボットテーブル保存用のワークブックを開く
wb = xl.book_open "test07.xls"
ss = wb.fes
ss.Name = "ピボットテーブルのシート"
ss.Activate # ピボットテーブルを設けるシートに焦点を当てる
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlConsolidation,
'SourceData'=>source_data})
ptname = "ピボット01" # ピボットテーブルの名前
pt = pivot_cache.CreatePivotTable({
'TableDestination'=>ss.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt.SmallGrid = false
pt.ShowPages
wb.save
wb.close
wbs.close
xl.quit
download pt07.rb
#! ruby -Ks
# 複数の元データを統合して取り込んだ時の各フィールドを確認
require "exlap"
# 元データのファイルを開く
xl = Exlap.new
wbs = xl.book_open "source07.xls"
source_data = []
wbs.each {|ss|
if ss.Name =~ /^(.+)のシート$/
branch = $1
area = "[#{wbs.obj.Name}]#{ss.Name}!" +
ss.UsedRange.Address(true, true, XlR1C1)
source_data << [area, branch]
end
}
# ピボットテーブル保存用のワークブックを開く
wb = xl.book_open "test07_2.xls"
ss = wb.fes
ss.Name = "ピボットテーブルのシート"
ss.Activate # ピボットテーブルを設けるシートに焦点を当てる
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlConsolidation,
'SourceData'=>source_data})
ptname = "ピボット01" # ピボットテーブルの名前
pt = pivot_cache.CreatePivotTable({
'TableDestination'=>ss.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt.SmallGrid = false
output = ["* PageField"]
i = 0
pt.PageFields.each {|ptf|
output << sprintf("\t"+"field#%d %s", i+=1, ptf.Name)
j = 0
ptf.PivotItems.each {|ptfi|
output << sprintf("\t\t"+"item#%d %s", j+=1, ptfi.Name)
}
}
puts output.join("\n")
##
output = ["* RowField"]
i = 0
pt.RowFields.each {|ptf|
output << sprintf("\t"+"field#%d %s", i+=1, ptf.Name)
j = 0
ptf.PivotItems.each {|ptfi|
output << sprintf("\t\t"+"item#%d %s", j+=1, ptfi.Name)
}
}
puts output.join("\n")
##
output = ["* ColumnField"]
i = 0
pt.ColumnFields.each {|ptf|
output << sprintf("\t"+"field#%d %s", i+=1, ptf.Name)
j = 0
ptf.PivotItems.each {|ptfi|
output << sprintf("\t\t"+"item#%d %s", j+=1, ptfi.Name)
}
}
puts output.join("\n")
##
output = ["* DataField"]
i = 0
pt.DataFields.each {|ptf|
output << sprintf("\t"+"field#%d %s", i+=1, ptf.Name)
j = 0
ptf.PivotItems.each {|ptfi|
output << sprintf("\t\t"+"item#%d %s", j+=1, ptfi.Name)
}
}
puts output.join("\n")
wb.close
wbs.close
xl.quit
download pt07_2.rb
#! ruby -Ks
# 複数の元データを統合して取り込んだ時の各フィールドを確認・その２
require "exlap"
# 各フィールド情報出力用スクリプト(__name__を適宜置き換え)
Proc_str = <<EOS
output = ["* __name__Field"]
i = 0
pt.__name__Fields.each {|ptf|
output << sprintf("\\t"+"field#%d %s", i+=1, ptf.Name)
j = 0
ptf.PivotItems.each {|ptfi|
output << sprintf("\\t\\t"+"item#%d %s", j+=1, ptfi.Name)
}
}
puts output.join("\\n")
EOS
# 元データのファイルを開く
xl = Exlap.new
wbs = xl.book_open "source07.xls"
source_data = []
wbs.each {|ss|
if ss.Name =~ /^(.+)のシート$/
branch = $1
area = "[#{wbs.obj.Name}]#{ss.Name}!" +
ss.UsedRange.Address(true, true, XlR1C1)
source_data << [area, branch]
end
}
# ピボットテーブル保存用のワークブックを開く
wb = xl.book_open "test07_3.xls"
ss = wb.fes
ss.Name = "ピボットテーブルのシート"
ss.Activate # ピボットテーブルを設けるシートに焦点を当てる
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlConsolidation,
'SourceData'=>source_data})
ptname = "ピボット01" # ピボットテーブルの名前
pt = pivot_cache.CreatePivotTable({
'TableDestination'=>ss.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt.SmallGrid = false
%w(Page Row Column Data).each {|name|
eval Proc_str.gsub(/__name__/, name)
}
wb.close
wbs.close
xl.quit
download pt07_3.rb
#! ruby -Ks
# 複数の元データを統合して取り込み、一つのクロス集計表を作成
require "exlap"
# 元データのファイルを開く
xl = Exlap.new
wbs = xl.book_open "source07.xls"
source_data = []
wbs.each {|ss|
if ss.Name =~ /^(.+)のシート$/
branch = $1
area = "[#{wbs.obj.Name}]#{ss.Name}!" +
ss.UsedRange.Address(true, true, XlR1C1)
source_data << [area, branch]
end
}
# ピボットテーブル保存用のワークブックを開く
wb = xl.book_open "test07_4.xls"
ss = wb.fes
ss.Name = "ピボットテーブルのシート"
ss.Activate # ピボットテーブルを設けるシートに焦点を当てる
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlConsolidation,
'SourceData'=>source_data})
ptname = "ピボット01" # ピボットテーブルの名前
pt = pivot_cache.CreatePivotTable({
'TableDestination'=>ss.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt.SmallGrid = false
# とりあえず各フィールドを変数に代入
ptfp = pt.PageFields(1)
ptfr = pt.RowFields(1)
ptfc = pt.ColumnFields(1)
ptfd = pt.DataFields(1)
ptfp.Orientation = XlColumnField # ページフィールドを列フィールドに変更
ary = [false]*5 + [true, false] # 四半期グループ化用の配列
ptfr.LabelRange.Group({'Periods'=>ary}) # 行フィールドを四半期グループ化
ptfc.Orientation = XlHidden # 自動設定された列フィールドを非表示に
ptfp.Caption = "支店"
ptfr.Caption = "期間"
ptfd.Caption = "合計/売上げ"
%w(東京 名古屋 大阪).each_with_index {|name, i|
ptfp.PivotItems(name).Position = i+1
}
wb.save
wb.close
wbs.close
xl.quit
download pt07_4.rb
#! ruby -Ks
# 複数の元データを統合して取り込んだ時の素材ピボットフィールドの確認
require "exlap"
# 元データのファイルを開く
xl = Exlap.new
wbs = xl.book_open "source07.xls"
source_data = []
wbs.each {|ss|
if ss.Name =~ /^(.+)のシート$/
branch = $1
area = "[#{wbs.obj.Name}]#{ss.Name}!" +
ss.UsedRange.Address(true, true, XlR1C1)
source_data << [area, branch]
end
}
# ピボットテーブル保存用のワークブックを開く
wb = xl.book_open "test07_5.xls"
ss = wb.fes
ss.Name = "ピボットテーブルのシート"
ss.Activate # ピボットテーブルを設けるシートに焦点を当てる
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlConsolidation,
'SourceData'=>source_data})
ptname = "ピボット01" # ピボットテーブルの名前
pt = pivot_cache.CreatePivotTable({
'TableDestination'=>ss.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt.SmallGrid = false
output = ["素材となるピボットフィールドの一覧"]
i = 0
pt.PivotFields.each {|ptf|
output << sprintf("field#%d %s", i+=1, ptf.Name)
j = 0
ptf.PivotItems.each {|ptfi|
output << sprintf("\t"+"item#%d %s", j+=1, ptfi.Name)
}
}
puts output.join("\n")
wb.close
wbs.close
xl.quit
download pt07_5.rb
#! ruby -Ks
# 複数の元データを統合して集計(sql命令文の活用)
require "exlap"
source_name = "source07_2.xls"
cnn = "ODBC;DSN=Excel Files;DBQ=#{Exl::getAbsolutePath(source_name)}"
tbl1 = "[性別のシート$]"
tbl2 = "[喫煙のシート$]"
sql = <<EOS
select #{tbl1}.氏名, #{tbl1}.性別, #{tbl2}.喫煙
from #{tbl1}, #{tbl2}
where #{tbl1}.氏名 = #{tbl2}.氏名;
EOS
filename = "test07_6.xls"
Exlap.new(filename) {|wb|
ss = wb.fes
ss.Name = "性別と喫煙の関係のシート"
ss.Activate # ピボットテーブルを設けるシートに焦点を当てる
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlExternal})
pivot_cache.Connection = cnn
pivot_cache.CommandText = sql
ptname = "性別と喫煙" # ピボットテーブルの名前
pt = pivot_cache.CreatePivotTable({
'TableDestination'=>ss.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt.SmallGrid = false
ptf1 = pt.PivotFields("性別")
ptf1.Orientation = XlColumnField
ptf2 = pt.PivotFields("喫煙")
ptf2.Orientation = XlRowField
ptf3 = pt.PivotFields("性別")
ptf3.Orientation = XlDataField
ptf3.Function = XlCount
ptf1.PivotItems("男").Position = 1
ptf1.PivotItems("女").Position = 2
ptf3.Caption = "性別/喫煙"
wb.save
}
download pt07_6.rb
#! ruby -Ks
# 複数の元データを統合して集計(sql命令文の活用, ado版)
require "exlap"
source_name = "source07_2.xls"
tbl1 = "[性別のシート$]"
tbl2 = "[喫煙のシート$]"
sql = <<EOS
select #{tbl1}.氏名, #{tbl1}.性別, #{tbl2}.喫煙
from #{tbl1}, #{tbl2}
where #{tbl1}.氏名 = #{tbl2}.氏名;
EOS
# ado接続の準備
cnn_xls = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=#{Exl::getAbsolutePath(source_name)};" +
"Extended Properties=\"Excel 8.0;HDR=Yes;\""
cnn_xlsx = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=#{Exl::getAbsolutePath(source_name)};" +
"Extended Properties=\"Excel 12.0;HDR=Yes;\""
cnn = nil
case File.extname(source_name).downcase
when '.xls'
cnn = cnn_xls
when '.xlsx'
cnn = cnn_xlsx
end
unless cnn
exit
end
cn = WIN32OLE.new("ADODB.Connection")
rs = WIN32OLE.new("ADODB.Recordset")
rs.CursorLocation = 3 # adUseClient クライアント側カーソルを使用
filename = "test07_7.xls"
Exlap.new(filename) {|wb|
# シートにピボットテーブルを設定
ss = wb.fes
ss.Name = "性別と喫煙の関係のシート"
ss.Activate # ピボットテーブルを設けるシートに焦点を当てる
cn.Open cnn; rs.Open sql,cn # ado接続
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlExternal})
pivot_cache.Recordset = rs
rs.Close; cn.Close # adoを閉じる
ptname = "性別と喫煙" # ピボットテーブルの名前
pt = pivot_cache.CreatePivotTable({
'TableDestination'=>ss.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt.SmallGrid = false
ptf1 = pt.PivotFields("性別")
ptf1.Orientation = XlColumnField
ptf2 = pt.PivotFields("喫煙")
ptf2.Orientation = XlRowField
ptf3 = pt.PivotFields("性別")
ptf3.Orientation = XlDataField
ptf3.Function = XlCount
ptf1.PivotItems("男").Position = 1
ptf1.PivotItems("女").Position = 2
ptf3.Caption = "性別/喫煙"
wb.save
}
download pt07_7.rb