ruby中excel简单操作以及文件读取操作方法

# -*-coding:utf-8 -*-
#author:kanlijun
require 'win32ole'
require 'fileutils'
class ResultAnalyse
@@i=0
@@f=0
def initialize path
excel =WIN32OLE.new('excel.application')
@workbook =excel.WorkBooks.open(path)
@worksheet=@workbook.Worksheets(1)
end #获取excel第一行第一列的标题文本
def get_title
@worksheet.Range("a1").Value
end def get_receive
@worksheet.Range("i3").Value
end #获取sheet中用例行数
def get_rows
@worksheet.usedrange.rows.count
end #删除excel中上一次写入的数据
def del_excel_result
num = get_rows
for i in 3..num
@worksheet.Range("g#{i}").Value=''
@worksheet.Range("g#{i}").Interior.ColorIndex = 0 #无色 end
end #获取flag等于1的文件名称以及行数
def get_runinterface
num= get_rows
arr=[]
rows=[]
for i in 3..num
t=@worksheet.Range("d#{i}").Value
if t=='1'
rows<<i
arr<<@worksheet.Range("c#{i}").Value+'.rb'
end
end
return arr,rows
end #将执行结果写入excel
def judge_pass_fail file_path,rows
n=rows
if File.exist?("#{file_path}")
f=File.open("#{file_path}",mode='r')
case_str =f.readlines[2..-1]
case_str.each do|item|
@@i+=1
if item.include?('passed')
@worksheet.Range("g#{n}").Value='Pass'
@worksheet.Range("g#{n}").Interior.ColorIndex = 4 #绿色
elsif item.include? 'failed'
@@f +=1
@worksheet.Range("g#{n}").Value='Fail'
@worksheet.Range("g#{n}").Interior.ColorIndex = 3 #红色
else
@worksheet.Range("g#{n}").Value='wrong'
@worksheet.Range("g#{n}").Interior.ColorIndex = 40 #黄色
end
n +=1
end
else
puts 'not exist file'
end
[@@i,@@f]
end #关闭excel
def excel_close
@workbook.close(1)
end #获取case用例文件txt
def get_interface_result(file_path,rows)
if File.directory? file_path
i=0
Dir.foreach(file_path) do |file|
if file !="." and file !=".."
# p file_path+"/#{file}"
@one_line=judge_pass_fail(file_path+"/#{file}",rows[i])
i+=1
end
end
return @one_line
else
puts "file_path is not exist!"
end
end #删除上一次用例执行结果txt
def delete_spec list
if File.directory? list
FileUtils.rm_rf(list)
end
end def auto_write endend