acrehr的理财空间

读取当前目录的 “test.xls” 的 “Sheet1” 工作簿的内容，并保存成一个对象，方便以后直接读出

require 'win32ole'
$col_map =["","A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"]

class <SPAN class=t_tag onclick=tagshow(event) href="tag.php?name=Excel">Excel</SPAN>_Info
attr_accessor :file_name
attr_accessor :<SPAN class=t_tag onclick=tagshow(event) href="tag.php?name=range">range</SPAN>

def initialize(file_name)
@file_name = file_name
@<SPAN class=t_tag onclick=tagshow(event) href="tag.php?name=range">range</SPAN> = {}
end
end

def load_data(filename)
obj = nil
File.open(filename, "rb") {|f| obj = Marshal.load(f)}
return obj
end

def save_data(obj, filename)
File.open(filename, "wb") {|f| Marshal.dump(obj, f)}
end

def get_<SPAN class=t_tag onclick=tagshow(event) href="tag.php?name=range">range</SPAN>_name(row, col)
return "ERR" if (col <1) || (row <1) || (col >26 * 27) || (row >65536)
c2 = col % 26
c2 = 26 if c2 == 0
c1 = (col - c2) / 26
return $col_map[c2] + row.<SPAN class=t_tag onclick=tagshow(event) href="tag.php?name=to_s">to_s</SPAN> if c1 == 0
return $col_map[c1] + $col_map[c2] + row.<SPAN class=t_tag onclick=tagshow(event) href="tag.php?name=to_s">to_s</SPAN>
end

def read_excel(e_name, s_name)
$excel.WorkBooks.Open("#{$MY_LOCATION}/" + e_name)

$excel.WorkSheets(s_name).Activate
$excel_info = <SPAN class=t_tag onclick=tagshow(event) href="tag.php?name=Excel">Excel</SPAN>_Info.new(e_name)

rows = $max_rows
cols = $max_cols
rows = $excel.WorkSheets(s_name).UsedRange.Rows.Count if rows == 0

for row in 1..rows
for col in 1..cols
$excel_info.<SPAN class=t_tag onclick=tagshow(event) href="tag.php?name=range">range</SPAN>[get_<SPAN class=t_tag onclick=tagshow(event) href="tag.php?name=range">range</SPAN>_name(row, col)] = $excel.Cells(row, col).value.<SPAN class=t_tag onclick=tagshow(event) href="tag.php?name=to_s">to_s</SPAN>
end
end

$excel.WorkBooks.Close()

end

$MY_LOCATION = Dir.getwd

$excel = WIN32OLE.new("excel.application")
$excel.Visible = false

$max_cols = 2
$max_rows = 0

read_excel("test.xls","Sheet1")
save_data($excel_info, "excel_info.obj")

info = load_data("excel_info.obj")
puts info.file_name
puts info.<SPAN class=t_tag onclick=tagshow(event) href="tag.php?name=range">range</SPAN>["A1"]
puts info.<SPAN class=t_tag onclick=tagshow(event) href="tag.php?name=range">range</SPAN>["A2"]
puts info.<SPAN class=t_tag onclick=tagshow(event) href="tag.php?name=range">range</SPAN>["B1"]
puts info.<SPAN class=t_tag onclick=tagshow(event) href="tag.php?name=range">range</SPAN>["B2"]

$excel.Quit()