# pivot02.rb.txt: ruby script (encoding: Windows-31J)
  # ExcelのPivotTable操作  data02.xls を素材にして z_pivot02.xls を出力
  # data02.xlsには各支店(東京, 名古屋, 大阪)の「日付，売上げ」が365日分記録
  # 支店ごとにワークシートが異なる。つまり３つのワークシートがある
  # 第１シートに、四半期単位の支店別売上げ合計の集計表を作成
  # 第２シートに、月単位の支店別売上げ合計の集計表を作成
require "exlap"

data_file = "data02.xls"  # 集計の素材
filename = "z_pivot02.xls"  # 集計結果をこれに保存
if test(?e, filename)  # z_pivot02.xls が存在するなら、念のため消去
  File.unlink(filename)
end

xl = Exlap.new  # Excelの起動
xl.opens_once(data_file, filename) do |wb1, wb2|  # 同時に２つを開く
      # ↓ 素材データの情報取り込み
  branch_names = []  # 支店名をこれに記録
  source_data = []
  wb1.each do |ss|  # 各ワークシートを１枚づつ確認
    next unless ss.Name =~ /支店の売上げ/  # 「売上げ」のシート以外はスキップ
    branch = ss.Name
    branch = branch.sub(/の売上げ$/, "")  # 支店名に関係ない文字を消去
    branch_names << branch
    src_rng = "[#{wb1.obj.Name}]#{ss.Name}!" +
        ss.UsedRange.Address(true, true, XlR1C1)
    source_data << [src_rng, branch]
  end

  ## 四半期単位の売上げを集計
  ss = wb2.fes  # 空のシートを選択
  ss.Name = "支店別四半期単位の集計"
  ss.Activate  # ピボットテーブルのシートに焦点を当てる
  pvc = wb2.PivotCaches.Add(
      'SourceType'=>XlConsolidation,
      'SourceData'=>source_data)
  ptname = "四半期ごとの支店の売上げ"  # ピボットテーブルの名前
  pt = pvc.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]*7; ary[5]=true  # 四半期グループ化用の配列
  ptfr.LabelRange.Group('Periods'=>ary)  # 行フィールドを四半期単位でグループ化
  ptfc.Orientation = XlHidden  # 自動設定された列フィールドを非表示に
  ptfd.Function = XlSum  # 計算・集計の方法を「合計」に
  ptfd.Caption = "売上げ合計"
  ptfp.Caption = "支店"
  ptfr.Caption = "期間"
  i = 0
  branch_names.each do |name|  # 東京,名古屋,大阪の順にする
    i += 1
    ptfp.PivotItems(name).Position = i
  end
      # ↓ 行と列を入れ替える(支店の数が増えても大丈夫なように)
  ptfp.Orientation = XlRowField
  ptfr.Orientation = XlColumnField

  ## 月単位の売上げを集計
  ss = wb2.fes  # 空のシートを選択
  ss.Name = "支店別月単位の集計"
  ss.Activate  # ピボットテーブルのシートに焦点を当てる
  pvc = wb2.PivotCaches.Add(
      'SourceType'=>XlConsolidation,
      'SourceData'=>source_data)
  ptname = "月ごとの支店の売上げ"  # ピボットテーブルの名前
  pt = pvc.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]*7; ary[4]=true  # 月単位でのグループ化用の配列
  ptfr.LabelRange.Group('Periods'=>ary)  # 行フィールドを月単位でグループ化
  ptfc.Orientation = XlHidden  # 自動設定された列フィールドを非表示に
  ptfd.Function = XlSum  # 計算・集計の方法を「合計」に
  ptfd.Caption = "売上げ合計"
  ptfp.Caption = "支店"
  ptfr.Caption = "期間"
  i = 0
  branch_names.each do |name|  # 東京,名古屋,大阪の順にする
    i += 1
    ptfp.PivotItems(name).Position = i
  end
      # ↓ 行と列を入れ替える(支店の数が増えても大丈夫なように)
  ptfp.Orientation = XlRowField
  ptfr.Orientation = XlColumnField

  ss = wb2.ss(1)  # 第１ワークシートを選択
  ss.Activate
  wb2.save  # ワークブックの保存
end
xl.quit  # Excelの終了

__END__
○ 時間・日・月・四半期・年によるグループ化の指定
　ptfにビボットフィールドが代入されていれば、下のようにしてグループ化が可能。
    ptf.LabelRange.Group('Periods'=>ary)
　aryは、７つの要素からなる配列。各要素は、基本的に false を入れる。
　５番目をtrueにすると「月単位」、６番目をtrueにすれば「四半期単位」。
　　　1　秒
　　　2　分
　　　3　時
　　　4　日
　　　5　月
　　　6　四半期
　　　7　年