# scenario01.rb.txt: ruby script (encoding: Windows-31J)
  # シナリオ作成とその表示  z_scenario01.xls を出力
  # ３つの事業(飲食店事業, 通信販売事業, 塾経営事業)の簡易な予算書を作成
  # 予算書は基本的に収入と支出の２つだけ。その差額(利益)は関数で求める。
  # ３つの事業ごとにシナリオを登録
  # 第１シートでは、第１のシナリオ(飲食店事業のシナリオ)を表示
  # 第２シートは、３つの事業の要約レポート(標準タイプ)
  # 第３シートは、３つの事業の要約レポート(PivotTableタイプ)
  # ダミーのシートを設けて、シナリオを含めコピーする方法も含まれている
  # __END__ 以降に、関連プロパティの説明あり
require "exlap"

filename = "z_scenario01.xls"
if test(?e, filename)  # z_scenario01.xlsが存在するなら削除
  File.unlink(filename)
end

labels = %w(事業 収入 支出 利益)  # 予算書の見出し
xl = Exlap.new  # Excelを起動
xl.opens(filename) do |wb|  # ワークブックを開く
  ## ３つの事業の予算書をシナリオとして登録し、第１のシナリオを表示
  ss = wb.fes  # 空のワークシートを選択
  ss.Name = "事業別収支計画書"
  ss[1,1] = "2011年度　事業別収支計画書"  # A1欄にタイトルを書き込む
  ss.rr(1,1, 1,labels.size).MergeCells = true  # セル結合
      # ↓ 第２行目に見出しを書き込む
  ss.from_a(2,1, labels)
      # ↓ 第３行目の変化させるセルに名前を付ける(名前付き範囲の定義)
  labels.each_with_index do |name, i|
    ss.cell(3,i+1).Name = name
  end
      # ↓ セルに関するその他の設定
  ss.Range("利益").Formula = "=収入-支出"  # 計算式
  %w(収入 支出 利益).each do |name|  # 数値の表示形式を設定
    ss.Range(name).NumberFormatLocal = "#,##0"
  end
      # ↓ ３つのシナリオを登録
  changing_cells = ss.Range("事業,収入,支出")
  ss.Scenarios.Add('Name'=>"飲食店のシナリオ",
    'ChangingCells'=>changing_cells,
    'Values'=>["飲食店", 20000000, 11300000],
    'Comment'=>"飲食店事業の見込み",
    'Locked'=>true,
    'Hidden'=>false)
  ss.Scenarios.Add('Name'=>"通信販売のシナリオ",
    'ChangingCells'=>changing_cells,
    'Values'=>["通信販売", 12000000, 6680000],
    'Comment'=>"通信販売事業の見込み",
    'Locked'=>true,
    'Hidden'=>false)
  ss.Scenarios.Add('Name'=>"塾経営のシナリオ",
    'ChangingCells'=>changing_cells,
    'Values'=>["塾経営", 30000000, 20520000],
    'Comment'=>"塾経営事業の見込み",
    'Locked'=>true,
    'Hidden'=>false)
  ss.Scenarios(1).Show  # 第１のシナリオを表示
  ss.range_autofit  # セルの幅を自動調整

  ## シナリオの要約レポートを出力
      # ↓ ダミーのワークシートを作成
  ss.Copy('After'=>ss)
  ss2 = wb.offset_sheet(ss, 1)  # 次シートに移る
  ss2.Activate
  ss2.Name = "dummy_sheet"
  if ss2.Scenarios.Count == 0  # シナリオがコピーされていない場合
    ss2.Scenarios.Merge(ss)  # ssのシナリオをss2にコピー
  end
  ss2.Scenarios(1).Show  # 第１のシナリオを表示
      # ↓ 標準タイプの要約レポート(ダミーシートの前にワークシートが新設)
  ss2.Scenarios.CreateSummary('ReportType'=>XlStandardSummary,
    'ResultCells'=>ss2.Range("利益"))
      # ↓ ピボットテーブルの要約レポート(これもダミーシートの前に新設)
  ss2.Scenarios.CreateSummary('ReportType'=>XlSummaryPivotTable,
    'ResultCells'=>ss2.Range("収入,支出,利益"))
  ss3 = wb.offset_sheet(ss2, -1)  # 直前のシートに移る
  ss3.Activate
  ptfr = ss3.PivotTables(1).RowFields(1)
  ss2.Scenarios.each do |snr|  # シナリオの登録順になるよう調整
    ptfr.PivotItems(snr.Name).Position = snr.Index
  end
  wb.delete_sheet("dummy_sheet")  # ダミーシートを削除

  wb.ss(1).Activate  # 第１シートに移る
  wb.save  # ワークブックの保存
end
xl.quit

__END__
＊シナリオを登録する時の引数
　ss.Scenarios.Add(……) の引数は次のとおり。
・Name: シナリオの名前
　後でシナリオを表示したりする時に、この名前を手がかりにする。
・ChangingCells: 変化させるセル
　登録されているシナリオの値をどのセルに出力するかの指定。
・Values: 代入する値のセット
　代入する値のセットを配列で与える。
　ここで設定した３つの値は、先の ChangingCells で指定した３つのセルにそれぞれ出力される。
・Comment: シナリオに付ける注釈
　適当なものでかまわないが、後で「シナリオの要約レポート」を表示させた時にその中に盛り込まれるので、簡潔かつ参考になるものにする。最長半角255文字まで。
　長めになる時は、改行コードを含めて１行の長さを抑えた方がいいかもしれない。
　省略すると、「作成者:xxx 日付:2011/4/30」などのようになる。作成者は、通常、Excelインストール時の登録ユーザー名。
・Locked: シナリオのロックの有無
　これをtrueにすると、シナリオにロックがかかり変更できなくなる。falseならワークシートを保護しているときでも変更可能。既定値は true。
・Hidden: 表示／非表示の指定
　これを true にすると、シナリオが非表示になる。既定値は false。

＊要約レポートの指定方法
　ss.Scenarios.CreateSummary(……)の引数は次のとおり。
・ReportType: 要約レポートの種類
　種類には次の２つがある。
　標準タイプ： XlStandardSummary
　ピボットテーブル： XlSummaryPivotTable
・ResultCells: 結果出力セル
　要約レポートの種類が標準タイプの場合、特に指定しなくても「変化させるセル」の値は表示される。
　それに加えて、「結果出力セル」(サンプルでは「利益」)の値も表示したい時は、それをRange形式で指定する。
　要約レポートの種類がピボットテーブルの場合は、「変化させるセル」と「結果出力セル」の区別なく、その両方から表示したいセルを指定する。やはりRange形式で指定。
　「変化させるセル」と「結果出力セル」に名前を定義していないと、該当のセルが「$A$3」などのように番地で表示される。
　それだと見ても分かりにくいので、要約レポートの表示を行う時は、該当のセルに名前を付けておくのが望ましい。