# pivot04.rb.txt: ruby script (encoding: Windows-31J)
  # ExcelのPivotTable操作  data01.mdb を素材にして z_pivot04.xls を出力
  # Accessデータベースを素材にして集計。
  # ワークブック内のピボットテーブル存在確認とその削除を行う
  # まず pivot01.rb.txt と同じ集計表を z_pivot04.xls に作る
  # そして z_pivot04.xls を改めて開き
  # それに含まれているピボットテーブルの一覧を表示
  # 構成比（％）の表を削除し、z_pivot04.xls を保存
require "exlap"

data_file = "data01.mdb"  # 素材データのAccessファイル
filename = "z_pivot04.xls"  # 新たに作るワークブック
if test(?e, filename)  # z_pivot04.xlsが既に存在するなら削除
  File.unlink(filename)
end
fullname = Exl::fullpath(data_file)  # data_fileのフルパス名
unless test(?e, fullname)  # data_fileがみつからない
  STDERR.puts "'#{fullname}' がみつかりません．"
  exit
end
cnn = "ODBC;DSN=MS Access Database;DBQ=#{fullname}"
sql = "SELECT * FROM 性別・職業・身長・体重;"

xl = Exlap.new
xl.opens(filename) do |wb|
  ## クロス集計：男女別・職業別の人数
  ss = wb.fes  # 空のワークシートを選択
  ss.Name = "男女別・職業別の集計"
  ss.Activate  # ピボットテーブルを設けるシートに焦点を当てる
  pvc = wb.PivotCaches.Add('SourceType'=>XlExternal)
  pvc.Connection = cnn
  pvc.CommandText = sql
  ptname = "男女別・職業別の人数"  # ピボットテーブルの名前
  pvc.CreatePivotTable(
    'TableDestination'=>ss.Range("A1"),  # ピボットテーブルの始点
    'TableName'=>ptname)
  pt = ss.PivotTables(ptname)  # ピボットテーブルオブジェクトをptに
  pt.SmallGrid = false  # レイアウト枠の調整
      # ↓ 項目分類の注目欄として性別を指定。横方向(列)に広げる形に設定
  ptf1 = pt.PivotFields("性別")
  ptf1.Orientation = XlColumnField  # 「列」方向に並べるフィールドとする
  ptf1.PivotItems("男性").Position = 1  # 男性を１番目に
  ptf1.PivotItems("女性").Position = 2  # 女性を２番目に
      # ↓ 項目分類の注目欄として職業を指定。縦方向(行)に広げる形に設定
  ptf2 = pt.PivotFields("職業")
  ptf2.Orientation = XlRowField  # 「行」方向に並べるフィールドとする
      # ↓ どの項目に注目して人数をカウントするかを設定
  ptf3 = pt.PivotFields("職業")
  ptf3.Orientation = XlDataField  # 計算・集計の材料にするとの指定
  ptf3.Function = XlCount  # 個数(人数)のカウント
  ptf3.Caption = "男女別・職業別の人数"

  ## 同じシートに構成比（％）の表を書き出す
      # ↓ 第２ピボットテーブル作成の準備
  rng = pt.TableRange2  # 第１ピボットテーブルの領域をrngに代入
  y,x = ss.range_last(rng)  # 第１ピボットテーブルの最終番地を得る
  start_cell = ss.cell(y+3,1)  # 第２ピボットテーブルの始点を２行空け後に
      # ↓ 第２ピボットテーブルの作成
  ptname = "男女別・職業別の構成比"  # ピボットテーブルの名前
  pvc.CreatePivotTable(
    'TableDestination'=>start_cell,  # ピボットテーブルの始点
    'TableName'=>ptname)
  pt = ss.PivotTables(ptname)  # ピボットテーブルオブジェクトをptに
  pt.SmallGrid = false  # レイアウト枠の調整
      # ↓ 項目分類の注目欄として性別を指定。横方向(列)に広げる形に設定
  ptf1 = pt.PivotFields("性別")
  ptf1.Orientation = XlColumnField  # 「列」方向に並べるフィールドとする
  ptf1.PivotItems("男性").Position = 1  # 男性を１番目に
  ptf1.PivotItems("女性").Position = 2  # 女性を２番目に
      # ↓ 項目分類の注目欄として職業を指定。縦方向(行)に広げる形に設定
  ptf2 = pt.PivotFields("職業")
  ptf2.Orientation = XlRowField  # 「行」方向に並べるフィールドとする
      # ↓ どの項目に注目して人数をカウントするかを設定
  ptf3 = pt.PivotFields("職業")
  ptf3.Orientation = XlDataField  # 計算・集計の材料にするとの指定
  ptf3.Function = XlCount  # 個数(人数)のカウント
  ptf3.Calculation = XlPercentOfRow  # 「行」における構成比
  ptf3.NumberFormat = "##0.0%"  # 小数点以下１位まで表示
  ptf3.Caption = "男女別・職業別の構成比(％)"

  ## 数値の集計：男女別に身長と体重の平均値を算出
  ss = wb.fes  # 空のワークシートを選択
  ss.Name = "身長と体重の値"
  ss.Activate
  pvc = wb.PivotCaches.Add('SourceType'=>XlExternal)
  pvc.Connection = cnn
  pvc.CommandText = sql
  ptname = "男女別の身長と体重の平均値"  # ピボットテーブルの名前
  pt = pvc.CreatePivotTable(
    'TableDestination'=>ss.Range("A1"),  # ピボットテーブルの始点
    'TableName'=>ptname)
  pt.SmallGrid = false
      # ↓ 項目分類の注目欄として性別を指定。横方向(列)に広げる形に設定
  ptf1 = pt.PivotFields("性別")
  ptf1.Orientation = XlColumnField  # 「列」方向に並べるフィールドとする
  ptf1.PivotItems("男性").Position = 1  # 男性を１番目に
  ptf1.PivotItems("女性").Position = 2  # 女性を２番目に
      # ↓ 計算用の注目欄として「身長」を指定。平均を採ることも指定。
  ptf2 = pt.PivotFields("身長")
  ptf2.Orientation = XlDataField  # 計算の材料にするとの指定
  ptf2.Function = XlAverage  # 計算方法を「平均」に設定
  ptf2.NumberFormat = "0.0"  # 小数点以下１位まで表示
      # ↓ 「体重」に関する設定(「身長」と同じ)
  ptf3 = pt.PivotFields("体重")
  ptf3.Orientation = XlDataField
  ptf3.Function = XlAverage
  ptf3.NumberFormat = "0.0"

  ## 男女別・身長区分別の人数(数値のグループ化)
  ss = wb.fes  # 空のシートを選択
  ss.Name = "男女別・身長区分別の集計"
  ss.Activate
  pvc = wb.PivotCaches.Add('SourceType'=>XlExternal)
  pvc.Connection = cnn
  pvc.CommandText = sql
  ptname = "男女別・身長区分別の人数"  # ピボットテーブルの名前
  pt = pvc.CreatePivotTable(
    'TableDestination'=>ss.Range("A1"),  # ピボットテーブルの始点
    'TableName'=>ptname)
  pt.SmallGrid = false  # レイアウト枠の調整
      # ↓ 項目分類の注目欄として性別を指定。横方向(列)に広げる形に設定
  ptf1 = pt.PivotFields("性別")
  ptf1.Orientation = XlColumnField  # 「列」方向に並べるフィールドとする
  ptf1.PivotItems("男性").Position = 1  # 男性を１番目に
  ptf1.PivotItems("女性").Position = 2  # 女性を２番目に
      # ↓ 分類の注目欄として「身長」を指定
  ptf2 = pt.PivotFields("身長")
  ptf2.Orientation = XlRowField  # 縦方向(行)に列べるとの指定
      # ↓ どの項目に注目して人数をカウントするかを設定
  ptf3 = pt.PivotFields("性別")
  ptf3.Orientation = XlDataField  # 計算の材料にするとの指定
  ptf3.Function = XlCount  # 個数(人数)のカウント
  ptf3.Caption = "男女別・身長区分別の人数"
      # ↓ 「身長」を手がかりにしてグループ化
  ptf2.DataRange.Cells(1).Group('Start'=>155.0, 'End'=>185.0, 'By'=>10.0)

  ss = wb.ss(1)  # 第１ワークシートを選択
  ss.Activate
  wb.save  # ワークブックの保存
end  # ここでワークブックが閉じられる

xl.opens(filename) do |wb|  # 改めてワークブックを開く
  puts "ピボットテーブル名の一覧"
  wb.each do |ss|  # 各ワークシートを１枚づつチェック
    next if ss.PivotTables.Count < 1  # ピボットテーブルがないのでskip
    names = []  # これにピボットテーブルの名前を記録
    ss.PivotTables.each do |pt|  # 各ピボットテーブルを確認
      names << pt.Name
    end
    puts "*ワークシート名: " + ss.Name
    names.each do |name|
      puts "\t" + name
      if name =~ /構成比/  # 名前に「構成比」が含まれている
        pt = ss.PivotTables(name)
        pt.TableRange2.Clear  # ピボットテーブルを削除
        puts "\t\t↑ 削除しました．"
      end
    end
  end
  ss = wb.ss(1)  # 第１ワークシートを選択
  ss.Activate
  wb.save  # ワークブックの保存
end
xl.quit

__END__
＊外部のデータにアクセスするための「接続文字列」
　スクリプト中にある cnn は「接続文字列」といわれる。
　これにファイル名を盛り込む時は、フルパス名を用いる。

○ Accessデータベース用の文字列
cnn = "ODBC;DSN=MS Access Database;DBQ=#{fullname}"

○ Excelワークブック用の文字列
cnn = "ODBC;DSN=Excel Files;DBQ=#{fullname}"