# pivot03.rb.txt: ruby script (encoding: Windows-31J)
  # ExcelのPivotTable操作  data03.xls を素材にして z_pivot03.xls を出力
  # 「都道府県, 人口」のデータ(人口の多い順)を素材にして
  # 地方ブロック別の人口の合計を算出
  # 都道府県名を非表示にする表と、表示する表の２種類を別々のシートに作成
require "exlap"

    # 地方ブロックと都道府県の分類に関する情報
area_csv = <<EOS
北海道・東北地方,北海道,青森県,岩手県,宮城県,秋田県,山形県,福島県
関東地方,茨城県,栃木県,群馬県,埼玉県,千葉県,東京都,神奈川県
中部地方,新潟県,富山県,石川県,福井県,山梨県,長野県,岐阜県,静岡県,愛知県
近畿地方,三重県,滋賀県,京都府,大阪府,兵庫県,奈良県,和歌山県
中国地方,鳥取県,島根県,岡山県,広島県,山口県
四国地方,徳島県,香川県,愛媛県,高知県
九州・沖縄地方,福岡県,佐賀県,長崎県,熊本県,大分県,宮崎県,鹿児島県,沖縄県
EOS
area_ary = Exl::str2ary(area_csv)  # csv→array
area_hs = Hash.new
area_ary.each do |val|  # array→hash
  key = val.shift
  area_hs[key] = val
end

data_file = "data03.xls"  # 素材データのワークブック
filename = "z_pivot03.xls"  # 新たに作るワークブック
if test(?e, filename)  # test01.xlsが既に存在するなら削除
  File.unlink(filename)
end

xl = Exlap.new
xl.opens_once(data_file, filename) do |wb1, wb2|  # ２つを同時に開く
      # 素材データがある領域を変数 src_rng に記録
  ss = wb1.ss(1)  # 第１ワークシート(素材データがある)を選択
  src_rng = "[#{wb1.Name}]#{ss.Name}!" +
        ss.UsedRange.Address(true, true, XlR1C1)

  ## 地方ブロックごとの人口を算出(都道府県名を表示しない)
  ss = wb2.fes  # 空のワークシートを選択
  ss.Name = "地方ブロック別の集計"
  ss.Activate  # ピボットテーブルのシートに焦点を当てる
  pvc = wb2.PivotCaches.Add('SourceType'=>XlDatabase, 'SourceData'=>src_rng)
  ptname = "地方ブロック別の人口"  # ピボットテーブルの名前
  pvc.CreatePivotTable(
    'TableDestination'=>ss.Range("A1"),  # ピボットテーブルの始点
    'TableName'=>ptname)
  pt = ss.PivotTables(ptname)  # ピボットテーブルオブジェクトをptに
  pt.SmallGrid = false  # レイアウト枠の調整
      # ↓ 項目分類の注目欄として都道府県を指定。縦方向(行)に広げる形に設定
  ptf1 = pt.PivotFields("都道府県")
  ptf1.Orientation = XlRowField  # 「行」方向に並べるフィールドとする
      # ↓ 「人口」を計算・集計の材料にする
  ptf2 = pt.PivotFields("人口")
  ptf2.Orientation = XlDataField  # 計算・集計の材料にするとの指定
  ptf2.Function = XlSum  # 合計を算出
      # ↓ 地方ブロックごとにグループ化する
  app = pt.Application  # Excel.Application
  area_hs.values.each do |prefecture_names|
    param = []
    prefecture_names.each do |prefecture|
      param << ptf1.PivotItems(prefecture).LabelRange
    end
    app.Union(*param).Group
  end
  ptfp = ptf1.ParentField  # 親フィールド(グループ分類したものの全体)
  ptfp.Name = "地方ブロック"
      # ↓ 各グループの名前と順番を変更・調整
  i = 0
  area_hs.keys.each do |area|
    i += 1
    pvi = ptfp.PivotItems("ｸﾞﾙｰﾌﾟ#{i}")
    pvi.Name = area
    pvi.Position = i
  end
  ptf1.Orientation = XlHidden  # 都道府県名を非表示に

  ## 地方ブロックごとの人口を算出(都道府県名を表示)
  ss = wb2.fes  # 空のワークシートを選択
  ss.Name = "地方ブロック別・都道府県別の集計"
  ss.Activate  # ピボットテーブルのシートに焦点を当てる
  pvc = wb2.PivotCaches.Add('SourceType'=>XlDatabase, 'SourceData'=>src_rng)
  ptname = "地方ブロック別・都道府県別の人口"  # ピボットテーブルの名前
  pvc.CreatePivotTable(
    'TableDestination'=>ss.Range("A1"),  # ピボットテーブルの始点
    'TableName'=>ptname)
  pt = ss.PivotTables(ptname)  # ピボットテーブルオブジェクトをptに
  pt.SmallGrid = false  # レイアウト枠の調整
      # ↓ 項目分類の注目欄として都道府県を指定。縦方向(行)に広げる形に設定
  ptf1 = pt.PivotFields("都道府県")
  ptf1.Orientation = XlRowField  # 「行」方向に並べるフィールドとする
      # ↓ 「人口」を計算・集計の材料にする
  ptf2 = pt.PivotFields("人口")
  ptf2.Orientation = XlDataField  # 計算・集計の材料にするとの指定
  ptf2.Function = XlSum  # 合計を算出
      # ↓ 地方ブロックごとにグループ化する
  app = pt.Application  # Excel.Application
  area_hs.values.each do |prefecture_names|
    param = []
    prefecture_names.each do |prefecture|
      param << ptf1.PivotItems(prefecture).LabelRange
    end
    app.Union(*param).Group
  end
  ptfp = ptf1.ParentField  # 親フィールド(グループ分類したものの全体)
  ptfp.Name = "地方ブロック"
      # ↓ 各グループの名前と順番を変更・調整
  i = 0
  area_hs.keys.each do |area|
    i += 1
    pvi = ptfp.PivotItems("ｸﾞﾙｰﾌﾟ#{i}")
    pvi.Name = area
    pvi.Position = i
  end
      # その他の調整
  ptfp.Subtotals = [true] + [false]*11  # 地方ブロック単位の合計を表示
  ptfp.SubtotalName = "地方ブロック合計"  # 地方ブロック単位の合計欄の名前
  i = 0
  area_hs.values.each do |prefecture_names|
    prefecture_names.each do |prefecture|
      i += 1
      ptf1.PivotItems(prefecture).Position = i
    end
  end

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