以下に掲げるrubyスクリプトのサンプルは、Excel操縦用ライブラリ exlap.rb 最新版: exlap_latest.zipに同梱のものを利用するとの前提で書かれています。
目次
#! ruby -Ks
# シナリオ作成とその表示
require "exlap"
labels = %w(事業 収入 支出 利益) # 見出しの名前
cell_names = labels
filename = "test01.xls"
Exlap.new(filename) {|wb|
ss = wb.fes # 空のワークシートを選択
ss.Name = "事業別収支計画書"
ss[1,1] = "2011年度 事業別収支計画書" # A1欄にタイトルを書き込む
ss.rr(1,1, 1,labels.size).MergeCells = true # セル結合
# 第２行目に見出しを書き込む
ss.rr(2,1, 2,labels.size).Value = [labels]
# 第３行目の変化させるセルに名前を付ける(名前付き範囲の定義)
cell_names.each_with_index {|name, i|
ss.cell(3,i+1).Name = name
}
# セルに関するその他の設定
ss.Range("利益").Value = "=収入-支出" # 計算式
%w(収入 支出 利益).each {|name|
ss.Range(name).NumberFormatLocal = "#,##0" # 数値の表示形式
}
# ３つのシナリオを登録
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 # セルの幅と高さを自動調整
wb.save
}
download snr01.rb
#! ruby -Ks
# シナリオの要約レポート表示
require "exlap"
labels = %w(事業 収入 支出 利益) # 見出しの名前
cell_names = labels
filename = "test01_2.xls"
Exlap.new(filename) {|wb|
ss = wb.fes # 空のワークシートを選択
ss.Name = "事業別収支計画書"
ss[1,1] = "2011年度 事業別収支計画書" # A1欄にタイトルを書き込む
ss.rr(1,1, 1,labels.size).MergeCells = true # セル結合
# 第２行目に見出しを書き込む
ss.rr(2,1, 2,labels.size).Value = [labels]
# 第３行目の変化させるセルに名前を付ける(名前付き範囲の定義)
cell_names.each_with_index {|name, i|
ss.cell(3,i+1).Name = name
}
# セルに関するその他の設定
ss.Range("利益").Value = "=収入-支出" # 計算式
%w(収入 支出 利益).each {|name|
ss.Range(name).NumberFormatLocal = "#,##0" # 数値の表示形式
}
# ３つのシナリオを登録
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.Scenarios.CreateSummary({
'ReportType'=>XlStandardSummary,
'ResultCells'=>ss.Range("利益")})
# ピボットテーブルの要約レポート
ss.Scenarios.CreateSummary({
'ReportType'=>XlSummaryPivotTable,
'ResultCells'=>ss.Range("収入,支出,利益")})
wb.save
}
download snr01_2.rb
#! ruby -Ks
# 各シナリオの表示結果を別シートにコピー
require "exlap"
labels = %w(事業 収入 支出 利益) # 見出しの名前
cell_names = labels
filename = "test02.xls"
Exlap.new(filename) {|wb|
ss = wb.fes # 空のワークシートを選択
ss.Name = "事業別収支計画書"
ss[1,1] = "2011年度 事業別収支計画書" # A1欄にタイトルを書き込む
ss.rr(1,1, 1,labels.size).MergeCells = true # セル結合
# 第２行目に見出しを書き込む
ss.rr(2,1, 2,labels.size).Value = [labels]
# 第３行目の変化させるセルに名前を付ける(名前付き範囲の定義)
cell_names.each_with_index {|name, i|
ss.cell(3,i+1).Name = name
}
# セルに関するその他の設定
ss.Range("利益").Value = "=収入-支出" # 計算式
%w(収入 支出 利益).each {|name|
ss.Range(name).NumberFormatLocal = "#,##0" # 数値の表示形式
}
# ３つのシナリオを登録
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.each {|snr|
snr.Show # １つのシナリオを表示
ss.range_autofit # セルの幅や高さを自動調整
ss.Copy({'Before'=>ss}) # 現シートの前に複製シートを新設
ss2 = wb.offset_sheet(ss, -1) # 新設の複製シートのオブジェクトを得る
ss2.Name = snr.Name
}
wb.save
}
download snr02.rb
#! ruby -Ks
# 各シナリオの表示結果を並べて表示(ChangeScenarioの利用)
require "exlap"
labels = %w(事業 収入 支出 利益) # 見出しの名前
cell_names = labels
filename = "test02_2.xls"
Exlap.new(filename) {|wb|
ss = wb.fes # 空のワークシートを選択
ss.Name = "事業別収支計画書"
ss[1,1] = "2011年度 事業別収支計画書" # A1欄にタイトルを書き込む
ss.rr(1,1, 1,labels.size).MergeCells = true # セル結合
# 第２行目に見出しを書き込む
ss.rr(2,1, 2,labels.size).Value = [labels]
# 第３行目の変化させるセルに名前を付ける(名前付き範囲の定義)
cell_names.each_with_index {|name, i|
ss.cell(3,i+1).Name = name
}
# セルに関するその他の設定
ss.Range("利益").Value = "=収入-支出" # 計算式
%w(収入 支出 利益).each {|name|
ss.Range(name).NumberFormatLocal = "#,##0" # 数値の表示形式
}
# ３つのシナリオを登録
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.Copy({'After'=>ss}) # 現シートの次に複製シートを新設
ss2 = wb.offset_sheet(ss, 1) # 新たな複製シートのオブジェクトを得る
ss2.Name = "複製シート"
if ss2.Scenarios.Count == 0
ss2.Scenarios.Merge(ss) # シナリオを読み込む
end
# ２番、３番のシナリオのChangingCellsを変更しつつ表示
ss2.Scenarios.each {|snr|
i = snr.Index
if i > 1 # ２番目、３番目のシナリオの変更
old_changing_cells = snr.ChangingCells
values = snr.Values
changing_cells = ss2.rr(i+2,1, i+2,3)
snr.ChangeScenario(changing_cells, values)
old_changing_cells.Copy(changing_cells) # 書式も含めてコピー
ss2.cell(i+2,4).Value = "=RC[-2]-RC[-1]" # 「利益」欄の設定
ss2.cell(i+2,4).NumberFormatLocal = "#,##0"
end
snr.Show
}
ss2.range_autofit
wb.save
}
download snr02_2.rb
#! ruby -Ks
# 本業と副業の２種類のシナリオを登録
require "exlap"
require "csv"
# 名前定義のcsv
cell_names_format = <<EON
タイトル
ラベル
事業,収入,支出,利益
副業,副業収入,副業支出,副業利益
合算,合算収入,合算支出,合算利益
EON
cell_names = CSV.parse(cell_names_format) # csvを配列に変換
# セルの値のcsv
cell_values_format = <<EOV
2011年度 事業別収支計画書
事業,収入,支出,利益
,,,=収入-支出
,,,=副業収入-副業支出
合算,=収入+副業収入,=支出+副業支出,=利益+副業利益
EOV
cell_values = CSV.parse(cell_values_format) # csvを配列に変換
# シナリオ情報のcsv
scenario_group = []
scenario_data1 = <<EOS1
飲食店のシナリオ,飲食店,20000000,11300000
通信販売のシナリオ,通信販売,12000000,6680000
塾経営のシナリオ,塾経営,30000000,20520000
EOS1
scenario_group << CSV.parse(scenario_data1)
scenario_data2 = <<EOS2
副業Ａのシナリオ,副業Ａ,1500000,100000
副業Ｂのシナリオ,副業Ｂ,1000000,50000
副業Ｃのシナリオ,副業Ｃ,750000,50000
EOS2
scenario_group << CSV.parse(scenario_data2)
filename = "test03.xls"
Exlap.new(filename) {|wb|
ss = wb.fes # 空のワークシートを選択
ss.Name = "事業別収支計画書"
# セルに名前を付ける
cell_names.each_with_index {|list, r|
list.each_with_index {|name, c|
ss.Cells.Item(r+1, c+1).Name = name if name.to_s != ''
}
}
# セルの値をセットする
cell_values.each_with_index {|list, r|
list.each_with_index {|val, c|
ss.Cells.Item(r+1, c+1).Value = val if val.to_s != ''
}
}
ss.rr(1,1, 1,cell_values[1].size).MergeCells = true # タイトル部のセル結合
# セルに関するその他の設定
(%w(収入 支出 利益) +
%w(副業収入 副業支出 副業利益) +
%w(合算収入 合算支出 合算利益)).each {|name|
ss.Range(name).NumberFormatLocal = "#,##0" # 数値の表示形式
}
# シナリオの登録
changing_cells = [ss.Range("事業,収入,支出"),
ss.Range("副業,副業収入,副業支出")]
scenario_group.each_with_index {|info, idx|
info.each {|ary|
name = ary[0]
values = ary[1...ary.size]
ss.Scenarios.Add({
'Name'=>name,
'ChangingCells'=>changing_cells[idx],
'Values'=>values})
}
}
ss.Scenarios("飲食店のシナリオ").Show
ss.Scenarios("副業Ａのシナリオ").Show
ss.range_autofit # セルの幅と高さを自動調整
wb.save
}
download snr03.rb
#! ruby -Ks
# 本業と副業の総ての組合せパターンを表示・保存
require "exlap"
require "csv"
# 名前定義のcsv
cell_names_format = <<EON
タイトル
ラベル
事業,収入,支出,利益
副業,副業収入,副業支出,副業利益
合算,合算収入,合算支出,合算利益
EON
cell_names = CSV.parse(cell_names_format) # csvを配列に変換
# セルの値のcsv
cell_values_format = <<EOV
2011年度 事業別収支計画書
事業,収入,支出,利益
,,,=収入-支出
,,,=副業収入-副業支出
合算,=収入+副業収入,=支出+副業支出,=利益+副業利益
EOV
cell_values = CSV.parse(cell_values_format) # csvを配列に変換
# シナリオ情報のcsv
scenario_group = []
scenario_data1 = <<EOS1
飲食店のシナリオ,飲食店,20000000,11300000
通信販売のシナリオ,通信販売,12000000,6680000
塾経営のシナリオ,塾経営,30000000,20520000
EOS1
scenario_group << CSV.parse(scenario_data1)
scenario_data2 = <<EOS2
副業Ａのシナリオ,副業Ａ,1500000,100000
副業Ｂのシナリオ,副業Ｂ,1000000,50000
副業Ｃのシナリオ,副業Ｃ,750000,50000
EOS2
scenario_group << CSV.parse(scenario_data2)
filename = "test03_2.xls"
Exlap.new(filename) {|wb|
ss = wb.fes # 空のワークシートを選択
ss.Name = "事業別収支計画書"
# セルに名前を付ける
cell_names.each_with_index {|list, r|
list.each_with_index {|name, c|
ss.Cells.Item(r+1, c+1).Name = name if name.to_s != ''
}
}
# セルの値をセットする
cell_values.each_with_index {|list, r|
list.each_with_index {|val, c|
ss.Cells.Item(r+1, c+1).Value = val if val.to_s != ''
}
}
ss.rr(1,1, 1,cell_values[1].size).MergeCells = true # タイトル部のセル結合
# セルに関するその他の設定
(%w(収入 支出 利益) +
%w(副業収入 副業支出 副業利益) +
%w(合算収入 合算支出 合算利益)).each {|name|
ss.Range(name).NumberFormatLocal = "#,##0" # 数値の表示形式
}
# シナリオの登録
changing_cells = [ss.Range("事業,収入,支出"),
ss.Range("副業,副業収入,副業支出")]
scenario_group.each_with_index {|info, idx|
info.each {|ary|
name = ary[0]
values = ary[1...ary.size]
ss.Scenarios.Add({
'Name'=>name,
'ChangingCells'=>changing_cells[idx],
'Values'=>values})
}
}
scenario_group[0].each {|info1|
scenario_name1 = info1[0]
scenario_group[1].each {|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
}
}
wb.save
}
download snr03_2.rb
#! ruby -Ks
# 本業と副業の総ての組合せパターンを表示・保存(汎用版)
require "exlap"
require "csv"
# ２次元配列groupの要素の「組合せ」総てを返す
# groupの要素数が０の時は [] を返す
def combine(group)
res = []
if group.size == 1
group[0].each {|x|
res << [x]
}
elsif group.size == 2
first = group[0]
second = group[1]
first.each {|x1|
second.each {|x2|
res << [x1, x2]
}
}
elsif group.size > 2
first = group[0]
other = group[1...group.size]
w = combine(other)
first.each {|x|
w.each {|ary|
res << ([x] + ary)
}
}
end
return res
end
# 名前定義のcsv
cell_names_format = <<EON
タイトル
ラベル
事業,収入,支出,利益
副業,副業収入,副業支出,副業利益
合算,合算収入,合算支出,合算利益
EON
cell_names = CSV.parse(cell_names_format) # csvを配列に変換
# セルの値のcsv
cell_values_format = <<EOV
2011年度 事業別収支計画書
事業,収入,支出,利益
,,,=収入-支出
,,,=副業収入-副業支出
合算,=収入+副業収入,=支出+副業支出,=利益+副業利益
EOV
cell_values = CSV.parse(cell_values_format) # csvを配列に変換
# シナリオ情報のcsv
scenario_data_csv = <<EOS
飲食店のシナリオ,"事業,収入,支出",飲食店,20000000,11300000
通信販売のシナリオ,"事業,収入,支出",通信販売,12000000,6680000
塾経営のシナリオ,"事業,収入,支出",塾経営,30000000,20520000
副業Ａのシナリオ,"副業,副業収入,副業支出",副業Ａ,1500000,100000
副業Ｂのシナリオ,"副業,副業収入,副業支出",副業Ｂ,1000000,50000
副業Ｃのシナリオ,"副業,副業収入,副業支出",副業Ｃ,750000,50000
EOS
scenario_data = CSV.parse(scenario_data_csv) # csvを配列に変換
filename = "test03_3.xls"
Exlap.new(filename) {|wb|
ss = wb.fes # 空のワークシートを選択
ss.Name = "事業別収支計画書"
# セルに名前を付ける
cell_names.each_with_index {|list, r|
list.each_with_index {|name, c|
ss.Cells.Item(r+1, c+1).Name = name if name.to_s != ''
}
}
# セルの値をセットする
cell_values.each_with_index {|list, r|
list.each_with_index {|val, c|
ss.Cells.Item(r+1, c+1).Value = val if val.to_s != ''
}
}
ss.rr(1,1, 1,cell_values[1].size).MergeCells = true # タイトル部のセル結合
# セルに関するその他の設定
(%w(収入 支出 利益) +
%w(副業収入 副業支出 副業利益) +
%w(合算収入 合算支出 合算利益)).each {|name|
ss.Range(name).NumberFormatLocal = "#,##0" # 数値の表示形式
}
# シナリオの登録
scenario_data.each {|ary|
name = ary[0]
changing_cells = ss.Range(ary[1])
values = ary[2...ary.size]
ss.Scenarios.Add({
'Name'=>name,
'ChangingCells'=>changing_cells,
'Values'=>values})
}
# changing_cellsを手がかりにしたグループ分け
# changing_cellsが同じものを同じグループとみなす
group = []
hs = {}
scenario_data.each {|ary|
name = ary[0]
changing_cells = ary[1]
num = hs[changing_cells]
unless num
num = hs[changing_cells] = hs.size
end
unless group[num]
group[num] = []
end
group[num] << name
}
res = combine(group)
res.each {|ary|
ary.each {|name|
ss.Scenarios(name).Show
}
ss.range_autofit # セルの幅と高さを自動調整
ss.Copy({'Before'=>ss}) # 現シートの前に複製シートを新設
ss2 = wb.offset_sheet(ss, -1) # 新設の複製シートのオブジェクトを得る
ss2.Name = ary.join("＆")
}
wb.save
}
download snr03_3.rb
#! ruby -Ks
# 本業と副業の２種類のシナリオの要約レポート
require "exlap"
require "csv"
# 名前定義のcsv
cell_names_format = <<EON
タイトル
ラベル
事業,収入,支出,利益
副業,副業収入,副業支出,副業利益
合算,合算収入,合算支出,合算利益
EON
cell_names = CSV.parse(cell_names_format) # csvを配列に変換
# セルの値のcsv
cell_values_format = <<EOV
2011年度 事業別収支計画書
事業,収入,支出,利益
,,,=収入-支出
,,,=副業収入-副業支出
合算,=収入+副業収入,=支出+副業支出,=利益+副業利益
EOV
cell_values = CSV.parse(cell_values_format) # csvを配列に変換
# シナリオ情報のcsv
scenario_group = []
scenario_data1 = <<EOS1
飲食店のシナリオ,飲食店,20000000,11300000
通信販売のシナリオ,通信販売,12000000,6680000
塾経営のシナリオ,塾経営,30000000,20520000
EOS1
scenario_group << CSV.parse(scenario_data1)
scenario_data2 = <<EOS2
副業Ａのシナリオ,副業Ａ,1500000,100000
副業Ｂのシナリオ,副業Ｂ,1000000,50000
副業Ｃのシナリオ,副業Ｃ,750000,50000
EOS2
scenario_group << CSV.parse(scenario_data2)
filename = "test03_4.xls"
Exlap.new(filename) {|wb|
ss = wb.fes # 空のワークシートを選択
ss.Name = "事業別収支計画書"
# セルに名前を付ける
cell_names.each_with_index {|list, r|
list.each_with_index {|name, c|
ss.Cells.Item(r+1, c+1).Name = name if name.to_s != ''
}
}
# セルの値をセットする
cell_values.each_with_index {|list, r|
list.each_with_index {|val, c|
ss.Cells.Item(r+1, c+1).Value = val if val.to_s != ''
}
}
ss.rr(1,1, 1,cell_values[1].size).MergeCells = true # タイトル部のセル結合
# セルに関するその他の設定
(%w(収入 支出 利益) +
%w(副業収入 副業支出 副業利益) +
%w(合算収入 合算支出 合算利益)).each {|name|
ss.Range(name).NumberFormatLocal = "#,##0" # 数値の表示形式
}
# シナリオの登録
changing_cells = [ss.Range("事業,収入,支出"),
ss.Range("副業,副業収入,副業支出")]
scenario_group.each_with_index {|info, idx|
info.each {|ary|
name = ary[0]
values = ary[1...ary.size]
ss.Scenarios.Add({
'Name'=>name,
'ChangingCells'=>changing_cells[idx],
'Values'=>values})
}
}
ss.Scenarios("飲食店のシナリオ").Show
ss.Scenarios("副業Ａのシナリオ").Show
ss.range_autofit # セルの幅と高さを自動調整
# 標準タイプの要約レポート
ss.Scenarios.CreateSummary({
'ReportType'=>XlStandardSummary,
'ResultCells'=>ss.Range("利益,副業利益,合算収入,合算支出,合算利益")})
# ピボットテーブルの要約レポート
ss.Scenarios.CreateSummary({
'ReportType'=>XlSummaryPivotTable,
'ResultCells'=>ss.Range("収入,支出,利益," +
"副業収入,副業支出,副業利益," +
"合算収入,合算支出,合算利益")})
wb.save
}
download snr03_4.rb