以下に掲げるrubyスクリプトのサンプルは、Excel操縦用ライブラリ exlap.rb 最新版: exlap_latest.zipに同梱のものを利用するとの前提で書かれています。
目次
#! ruby -Ks
# データテーブル: 身長1.7メートルの人のBMIが、体重に応じてどう変化するか
# 体重データを横方向に拡げる形
require "exlap"
filename = "dtbl01.xls"
Exlap.new(filename) do |wb|
ss = wb.fes # 空のワークシートを選択
ss.Range("B1:H1").Value = [[50, 55, 60, 65, 70, 75, 80]] # 体重データ
x_cell = "A99" # 代入セル、使う予定のない適当なセル
ss.Range("A2").Formula = "=#{x_cell}/1.7/1.7" # BMI計算式
rng = ss.Range("A1:H2") # データテーブル領域全体
rng.Table({'RowInput'=>ss.Range(x_cell)}) # データテーブルの設定
wb.save # ワークブック保存
end
download dtbl01.rb
#! ruby -Ks
# データテーブル: 身長1.7メートルの人のBMIが、体重に応じてどう変化するか
# 体重データを縦方向に拡げる形
require "exlap"
filename = "dtbl02.xls"
Exlap.new(filename) do |wb|
ss = wb.fes # 空のワークシートを選択
ss.Range("A2:A8").Value = [[50], [55], [60], [65], [70], [75], [80]]
x_cell = "A99" # 代入セル、使う予定のない適当なセル
ss.Range("B1").Formula = "=IF(#{x_cell}=\"\",\"\"," +
"#{x_cell}/1.7/1.7)" # BMI計算式
rng = ss.Range("A1:B8") # データテーブル領域全体
rng.Table({'ColumnInput'=>ss.Range(x_cell)}) # データテーブルの設定
wb.save # ワークブック保存
end
download dtbl02.rb
#! ruby -Ks
# データテーブル: 複入力テーブルのサンプル
# BMIの計算。体重50〜80、身長150〜180の変化結果をみる
require "exlap"
filename = "dtbl03.xls"
Exlap.new(filename) do |wb|
ss = wb.fes # 空のワークシートを選択
ss.Range("B1:H1").Value = [[50, 55, 60, 65, 70, 75, 80]] # 体重
ss.Range("A2:A8").Value = [[150], [155], [160], [165], # 身長
[170], [175], [180]]
x_cell = "A99" # 体重の代入セル
y_cell = "B99" # 身長の代入セル
ss.Range("A1").Formula = "=IF(OR(#{x_cell}=\"\",#{y_cell}=\"\"),\"\"," +
"#{x_cell}/((#{y_cell}/100)^2))"
rng = ss.Range("A1:H8") # データテーブル領域全体
rng.Table(ss.Range(x_cell), ss.Range(y_cell)) # データテーブルの設定
wb.save # ワークブック保存
end
download dtbl03.rb
#! ruby -Ks
# データテーブル: 複入力テーブル。いろいろなケースに応用しやすいよう改定
# BMIの計算。体重50〜80、身長150〜180の変化結果をみる
require "exlap"
row_data = [50, 55, 60, 65, 70, 75, 80] # 横方向データ(体重)
col_data = [150, 155, 160, 165, 170, 175, 180] # 縦方向データ(身長)
row_cell = "H1" # 横方向データ用の代入セル
col_cell = "I1" # 縦方向データ用の代入セル
formula = "#{row_cell}/((#{col_cell}/100)^2)" # BMI計算式
formula_cell = "B3" # 計算式のセル(データテーブル領域の始点)
filename = "dtbl04.xls"
Exlap.new(filename) do |wb|
ss = wb.fes # 空のワークシートを選択
y,x = Exl::yx(ss.Range(formula_cell)) # 計算式セルの番地を数値で得る
row_data.each_with_index do |val, i| # 横方向のデータ書込み
ss[y, x+i+1] = val
end
col_data.each_with_index do |val, i| # 縦方向のデータ書込み
ss[y+i+1, x] = val
end
ss.Range(formula_cell).Formula = "=IF(OR(#{row_cell}=\"\"," +
"#{col_cell}=\"\"),\"\",#{formula})"
rng = ss.Range(formula_cell).CurrentRegion # データテーブル領域全体
rng.Table(ss.Range(row_cell), ss.Range(col_cell)) # データテーブルを設定
ly,lx = ss.range_last(rng) # データテーブル領域の終点を数値で得る
ss.rr(y+1,x+1, ly,lx).NumberFormatLocal = "#0.0" # 小数点１桁まで表示
# 見出しなどの書込み
ss[1,1] = "BMI算出結果"
ss[y-1, x+1] = "体重" # 横方向の見出し
ss[y-1, x+2] = "(kg)" # 体重の単位(キログラム)
ss[y+1, x-1] = "身長" # 縦方向の見出し
ss[y+2, x-1] = "(cm)" # 身長の単位(センチメートル)
wb.save
end
download dtbl04.rb
#! ruby -Ks
# データテーブル: 複入力テーブル。dtbl04.rbをローン返済用に変更
# ローン返済で、返済年数 10〜35、年利 1%〜3.5%の変化結果をみる
require "exlap"
amount = 30000000 # 返済総額
row_data = [10, 15, 20, 25, 30, 35] # 返済年数
col_data = %w(1% 1.5% 2% 2.5% 3% 3.5%) # 年利
row_cell = "H1" # 横方向データ用の代入セル
col_cell = "I1" # 縦方向データ用の代入セル
formula = "PMT(#{col_cell}/12,#{row_cell}*12,-#{amount})"
formula_cell = "B3" # 計算式のセル(データテーブル領域の始点)
filename = "dtbl05.xls"
Exlap.new(filename) do |wb|
ss = wb.fes # 空のワークシートを選択
y,x = Exl::yx(ss.Range(formula_cell)) # 計算式セルの番地を数値で得る
row_data.each_with_index do |val, i| # 横方向のデータ書込み
ss[y, x+i+1] = val
end
col_data.each_with_index do |val, i| # 縦方向のデータ書込み
ss[y+i+1, x] = val
end
ss.Range(formula_cell).Formula = "=IF(OR(#{row_cell}=\"\"," +
"#{col_cell}=\"\"),\"\",#{formula})"
rng = ss.Range(formula_cell).CurrentRegion # データテーブル領域全体
rng.Table(ss.Range(row_cell), ss.Range(col_cell)) # データテーブルを設定
ly,lx = ss.range_last(rng) # データテーブル領域の終点
ss.rr(y+1,x+1, ly,lx).NumberFormatLocal = "#,##0" # カンマ付き整数
# 見出しなどの書込み
ss[1,1] = "ローン返済月額算出"
ss[1,2] = "返済額 #{amount}円"
ss[y-1, x+1] = "返済期間(年)" # 横方向の見出し
ss[y+1, x-1] = "年利" # 縦方向の見出し
wb.save
end
download dtbl05.rb
#! ruby -Ks
# データテーブルの存在確認、関連情報の出力
# カレントディレクトリの *.xls を総てチェックする
require "exlap"
file_list = Dir.glob("./*.xls")
if file_list.size < 1
exit
end
puts 'データテーブル関連情報出力'
Exlap.new(*file_list) do |wb|
printf("FileName: %s\n", wb.name)
empty_names = wb.empty_sheet_names
wb.each do |ss| # 各ワークシートを調べる
next if empty_names.include?(ss.Name) # 空のワークシートをスキップ
printf("WorkSheet: %s\n", ss.Name)
tbl_ary = ss.finda(/^=table\(/i, 'Formula') # TABLE関数のセルを抽出
dtbl_count = 0
while tbl_ary.size > 0
dtbl_count += 1 # 同一シート内におけるデータテーブルの番号
first_cell = tbl_ary[0] # 最初のTABLE関数記録セル
tbl_str = first_cell.Formula # TABLE関数の具体的記述(文字列)
row_cell = col_cell = nil # 行と列の代入セルをこれに記録
if tbl_str =~ /^=table\((.*?),(.*?)\)$/i
row_cell = $1
col_cell = $2
end
##
last_cell = nil # データテーブル領域の終点セルをこれに
while tbl_ary.size > 0 and tbl_ary[0].Formula == tbl_str
last_cell = tbl_ary.shift
end
y1,x1 = Exl::yx(first_cell)
y2,x2 = Exl::yx(last_cell)
fy = (y1 < y2) ? y1-1 : y1 # 計算式セルのy座標値
fx = (x1 < x2) ? x1-1 : x1 # 計算式セルのx座標値
if [fy,fx] == [y1,x1]
if ss.cell(fy-1,fx).Formula != ''
fy -= 1
elsif ss.cell(fy,fx-1).Formula != ''
fx -= 1
else
fy = fx = nil
end
end
formula_cell = Exl::a1(ss.cell(fy,fx)) # y,xをA1形式に変換
formula = ss.Range(formula_cell).Formula # 計算式(文字列)
if formula =~ /^=([A-Z]+\d+|\$[A-Z]+\$\d+)$/i # 別セル参照の場合
w = $1
formula = ss.Range(w).Formula
end
##
row_data = []
unless [fy,fx] == [y1-1,x1]
(x1..x2).each {|x| row_data<<ss.cell(y1-1,x).Value}
end
col_data = []
unless [fy,fx] == [y1,x1-1]
(y1..y2).each {|y| col_data<<ss.cell(y,x1-1).Value}
end
##
printf("データテーブル #%d\n", dtbl_count)
printf("行の代入セル: %s\n", row_cell) if row_cell.to_s != ''
printf("列の代入セル: %s\n", col_cell) if col_cell.to_s != ''
printf("計算式のセル番地: %s\n", formula_cell)
printf("計算式: %s\n", formula)
if row_data.size > 0
printf("行データ(横方向): ")
p row_data
end
if col_data.size > 0
printf("列データ(縦方向): ")
p col_data
end
printf("\n") if tbl_ary.size > 0
end
printf("\n")
end
wb.close
end
download dtbl06.rb
#! ruby -Ks
# 単純なゴールシーク設定例: 2の平方根を求める
require "exlap"
filename = "gs01.xls"
Exlap.new(filename) do |wb|
ss = wb.fes # 空のワークシートを選択
ss.Range("A1").Formula = '=B1*B1'
ss.Range("A1").GoalSeek(2, ss.Range("B1"))
wb.save
end
download gs01.rb
#! ruby -Ks
# ゴールシーク: 1〜10の平方根を表示
require "exlap"
filename = "gs02.xls"
Exlap.new(filename) do |wb|
ss = wb.fes # 空のワークシートを選択
y = 0
for val in 1..10
y += 1
cell1 = ss.Cells.Item(y,1) # A列
cell2 = cell1.Offset(0,1) # cell1の右隣、つまりB列
cell1.Value = val
cell2.Formula = "=RC[1]*RC[1]"
cell2.GoalSeek(cell1.Value, "RC[1]")
end
wb.save
end
download gs02.rb
#! ruby -Ks
# ゴールシーク: PMT関数で返済総額を逆算。返済期間(36ヶ月)は固定
# 年利 1%〜10%、返済月額 5万円〜10万円のとき、借用可能限度額をみる
require "exlap"
row_data = [50000, 60000, 70000, 80000, 90000, 100000] # 返済月額
col_data = %w(1% 2% 3% 4% 5% 6% 7% 8% 9% 10%) # 年利
calc_cell = "H1" # 数式＆ゴールシーク用セル
start_cell = "B3" # 表の領域の始点
pay_count = 3*12 # 返済期間：３年＝36ヶ月
yen_format = "\\#,##0;[赤]\\-#,##0" # Excelでの「円」の表示フォーマット
filename = "gs03.xls"
Exlap.new(filename) do |wb|
ss = wb.fes
ccell = ss.Range(calc_cell)
sy,sx = Exl::yx(ss.Range(start_cell))
row_data.each_with_index do |val, i|
ss[sy, sx+i+1] = val
end
col_data.each_with_index do |val, i|
ss[sy+i+1, sx] = val
end
rng = ss.Range(start_cell).CurrentRegion # 表の領域全体
ly,lx = ss.range_last(rng)
for y in (sy+1)..ly
for x in (sx+1)..lx
rate = Exl::a1(ss.cell(y,sx)) # 年利データのセル番地
unit_pay = Exl::a1(ss.cell(sy,x)) # 返済月額データのセル番地
amount = Exl::a1(ss.cell(y,x)) # 可能借用限度額格納セル番地
ccell.Formula = "=PMT(#{rate}/12,#{pay_count},-#{amount})"
ccell.GoalSeek(ss.Range(unit_pay).Value, ss.Range(amount))
end
end
ccell.Formula = ccell.Value = ""
ss.rr(sy,sx+1, sy,lx).NumberFormatLocal = yen_format
ss.rr(sy+1,sx+1, ly,lx).NumberFormatLocal = yen_format
##
ss[1,1] = "借用可能限度額算出"
ss[1,2] = "返済期間："
ss[1,3] = "#{pay_count}ヶ月"
ss[sy+1,sx-1] = "年利"
ss[sy-1,sx+1] = "返済月額"
ss.range_autofit
wb.save
end
download gs03.rb