# scenario03.rb.txt: ruby script (encoding: Windows-31J)
  # シナリオ作成とその表示  z_scenario03.xls を出力
  # 本業と副業の２種類のシナリオを登録
  # 本業:飲食店, 通信販売, 塾経営。副業: 副業Ａ, 副業Ｂ, 副業Ｃ
  # 各々の事業は「事業,収入,支出,利益(自動的に算出)」の４項目から構成される
  # 本業と副業から、その合算を自動的に算出する
  # 本業と副業の総ての組合せを表示。各組合せをワークシートを替えて表示
require "exlap"

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

# シナリオ登録のための各種情報を設定
    # セルの名前定義(「名前付き範囲」を１つづつのセルに適用する)
cell_names_str = <<EON
タイトル
ラベル
事業,収入,支出,利益
副業,副業収入,副業支出,副業利益
合算,合算収入,合算支出,合算利益
EON
cell_names = Exl::str2ary(cell_names_str)
    # セルの値(計算式を含む)
cell_values_str = <<EOV
2011年度　事業別収支計画書
事業,収入,支出,利益
,,,=収入-支出
,,,=副業収入-副業支出
合算,=収入+副業収入,=支出+副業支出,=利益+副業利益
EOV
cell_values = Exl::str2ary(cell_values_str)
    # シナリオに登録する情報
scenario_group = []
scenario_data1 = <<EOS1
飲食店のシナリオ,飲食店,20000000,11300000
通信販売のシナリオ,通信販売,12000000,6680000
塾経営のシナリオ,塾経営,30000000,20520000
EOS1
scenario_group << Exl::str2ary(scenario_data1)
scenario_data2 = <<EOS2
副業Ａのシナリオ,副業Ａ,1500000,100000
副業Ｂのシナリオ,副業Ｂ,1000000,50000
副業Ｃのシナリオ,副業Ｃ,750000,50000
EOS2
scenario_group << Exl::str2ary(scenario_data2)

xl = Exlap.new  # Excelの起動
xl.opens(filename) do |wb|  # ワークブックを開く
  ss = wb.fes  # 空のワークシートを選択
  ss.Name = "事業別収支計画書"
      # セルに名前を付ける
  cell_names.each_with_index do |list, r|
    list.each_with_index do |name, c|
      ss.cell(r+1, c+1).Name = name  if name.to_s != ''
    end
  end
      # セルの値をセットする
  cell_values.each_with_index do |list, r|
    list.each_with_index do |val, c|
      ss.cell(r+1, c+1).Value = val  if val.to_s != ''
    end
  end
  ss.rr(1,1, 1,cell_values[1].size).MergeCells = true  # タイトル部のセル結合
      # セルに関するその他の設定
  (%w(収入 支出 利益) +
   %w(副業収入 副業支出 副業利益) +
   %w(合算収入 合算支出 合算利益)).each do |name|
      ss.Range(name).NumberFormatLocal = "#,##0"  # 数値の表示形式
  end
      # シナリオの登録
  changing_cells = [ss.Range("事業,収入,支出"),
      ss.Range("副業,副業収入,副業支出")]
  scenario_group.each_with_index do |info, idx|
    info.each do |ary|
      name = ary[0]
      values = ary[1...ary.size]
      ss.Scenarios.Add('Name'=>name,
          'ChangingCells'=>changing_cells[idx],
          'Values'=>values)
    end
  end

      # 本業と副業の総ての組合せを表示
  scenario_group[0].each do |info1|
    scenario_name1 = info1[0]
    scenario_group[1].each do |info2|
      scenario_name2 = info2[0]
      ss.Scenarios(scenario_name1).Show
      ss.Scenarios(scenario_name2).Show
      ss.range_autofit  # セルの幅を自動調整
      ss.Copy({'Before'=>ss})  # 現シートの前に複製シートを新設
      ss2 = wb.offset_sheet(ss, -1)  # 新設の複製シートに移る
      ss2.Name = scenario_name1 + "＆" + scenario_name2
      if ss2.Scenarios.Count == 0  # シナリオがコピーされていない場合
        ss2.Scenarios.Merge(ss)  # ssのシナリオをss2にコピー
      end
    end
  end
  wb.delete_sheet(ss.Name)

  wb.ss(1).Activate
  wb.save  # ワークブックの保存
end
xl.quit  # Excelの終了