win32oleを使ってexcelからデータを取得する(2)
すこし改良↓
require 'win32ole'
require 'kconv'#==============================================
# Excel
# excelデータの取得を行う
#==============================================
class Exceldef initialize(file_path)
@file_name = absolute_path(file_path)
end
# cellのデータを取得する
def cell_value(sheet_name,range)
open_sheet(sheet_name) do |sheet|
nil_check(sheet.Range(range.tosjis).value)
end
end# excelで編集を行った範囲のデータを取得する、戻り値は行と列の2次元配列
def used_range_values(sheet_name)
ret_arr = []
open_sheet(sheet_name) do |sheet|
sheet.usedrange.rows.each do |row|
a = []
row.columns.each do |cell|
a << nil_check(cell.value)
end
ret_arr << a
end
end
return ret_arr
end
def open_sheet(sheet_name)
begin
xl = WIN32OLE.new('Excel.Application')
book = xl.Workbooks.open(@file_name)
sheet = book.worksheets.item(sheet_name.tosjis)
yield(sheet)
rescue WIN32OLERuntimeError => e
e.to_str
ensure
book.Close unless book.nil?
xl.Quit unless xl.nil?
end
end
def open_book
begin
xl = WIN32OLE.new('Excel.Application')
book = xl.Workbooks.open(@filename)
yield(book)
rescue WIN32OLERuntimeError => e
e.to_str
ensure
book.Close unless book.nil?
xl.Quit unless xl.nil?
end
end
# -- private ----------------------------------------- #
private
def absolute_path(filename)
fso = WIN32OLE.new('Scripting.FileSystemObject')
return fso.GetAbsolutePathName(filename.tosjis)
end
def nil_check(value)
if value.nil?
"" # blank
elsif value.class == String
value.toutf8 # 文字列の場合はutf8にする
else
value.truncate # 数値は小数点以下切捨て
end
end
end#e = Excel.new("excel/test.xls")
#puts e.cell_value("てすと","B2")
#p e.used_range_values("てすと")
