Rails中使用win32ole操作Excel的示例

参考资料：http://jp.rubyist.net/magazine/?0004-Win32OLE

API地址：http://www.ruby-doc.org/stdlib/libdoc/win32ole/rdoc/index.html

 

示例代码：

以下是一个计算勤务时间系统代码片段：

class ReportController < ApplicationController require 'win32ole' require "jcode" $cov = Iconv.new('shift-jis', 'utf-8') def download filename = params[:filename] filepath = "C:/"+ filename + ".xls" excel = WIN32OLE.new("excel.application") excel.Visible = false workbook = excel.WorkBooks.Open(filepath) sheet = workbook.Worksheets(1) #sheet.Select #sheet.Activate remote_ip = request.remote_ip user = User.find(:first, :conditions => ["ip=?", remote_ip]) year = Time.now.strftime('%Y').to_i month = Time.now.strftime('%m').to_i sheet.cells.Item(4, 2).Value = $cov.iconv(year.to_s.tr('0-9', '０-９') + '年' + month.to_s.tr('0-9', '０-９') + '月度作業報告書') sheet.cells.Item(5, 9).Value = $cov.iconv(user.name) eday = Date.new(year, month, -1).strftime('%d').to_i wdays = ["日", "月", "火", "水", "木", "金", "土"] for i in 1..eday do aday = Date.new(year, month, i).wday #sheet.setproperty('Cells', 8+i, 1, i) #sheet.setproperty('Cells', 8+i, 2, $cov.iconv(wdays[aday])) cell_1 = sheet.cells.Item(8+i,1) cell_2 = sheet.cells.Item(8+i,2) cell_1.Value = i cell_2.Value = $cov.iconv(wdays[aday]) if aday == 0 || aday == 6 cell_1.Font.Color = convert_color_tohex('#0000FF') cell_2.Font.Color = convert_color_tohex('#0000FF') else cell_1.Font.Color = convert_color_tohex('#000000') cell_2.Font.Color = convert_color_tohex('#000000') end end work_date = params[:date] ||= Time.now.strftime('%Y-%m') works = Work.find(:all, :conditions => ["adate like '#{work_date}%' and ip=?", remote_ip], :order => 'adate' ) works.each do |work| aday = work.adate.strftime('%d').to_i btime = work.btime ctime = work.ctime content = work.content sheet.cells.Item(8+aday,4).Value = btime sheet.cells.Item(8+aday,5).Value = ctime sheet.cells.Item(8+aday,6).Value = overtime(ctime, btime) sheet.cells.Item(8+aday,7).Value = $cov.iconv(content) end reprotfile = "C:/worktime_#{Time.now.strftime('%Y%m%d')}.xls" File.delete reprotfile if FileTest::exist? reprotfile workbook.saveas reprotfile sheet = nil workbook.Close workbook = nil #excel.ActiveWorkbook.Close #excel.Workbooks.Close excel.Quit excel = nil GC.start send_file reprotfile unless reprotfile.blank? end private def convert_color_tohex(color) color = color.slice(1 , color.length) if color =~ /^#/ return 0 if color.length != 6 return (color[4,2] + color[2,2] + color[0,2]).hex end def substr(str,start,length) str.split(//)[start,length] end def overtime(btime, ctime) bh = substr(btime, 0, 2).to_s.to_i bm = substr(btime, 2, 4).to_s.to_f ch = substr(ctime, 0, 2).to_s.to_i cm = substr(ctime, 2, 4).to_s.to_f if bm > cm xm = (bm - cm) / 60 else xm = (bm + 60 - cm) / 60 bh = bh - 1 end bh - ch - 1 + xm end end

事实上，上面的代码也曾一度困扰我多时，原因是打开的Excel进程居然不会自己关闭，这种很容易耗尽系统资源。

解决的办法是：

class ReportController < ApplicationController require 'win32ole' require "jcode" $cov = Iconv.new('shift-jis', 'utf-8') def download filename = params[:filename] filepath = "C:/"+ filename + ".xls" excel = WIN32OLE.new("excel.application") excel.Visible = false workbook = excel.WorkBooks.Open(filepath) sheet = workbook.Worksheets(1) #sheet.Select #sheet.Activate remote_ip = request.remote_ip user = User.find(:first, :conditions => ["ip=?", remote_ip]) year = Time.now.strftime('%Y').to_i month = Time.now.strftime('%m').to_i sheet.cells.Item(4, 2).Value = $cov.iconv(year.to_s.tr('0-9', '０-９') + '年' + month.to_s.tr('0-9', '０-９') + '月度作業報告書') sheet.cells.Item(5, 9).Value = $cov.iconv(user.name) eday = Date.new(year, month, -1).strftime('%d').to_i wdays = ["日", "月", "火", "水", "木", "金", "土"] for i in 1..eday do aday = Date.new(year, month, i).wday #sheet.setproperty('Cells', 8+i, 1, i) #sheet.setproperty('Cells', 8+i, 2, $cov.iconv(wdays[aday])) cell_1 = sheet.cells.Item(8+i,1) cell_2 = sheet.cells.Item(8+i,2) cell_1.Value = i cell_2.Value = $cov.iconv(wdays[aday]) if aday == 0 || aday == 6 cell_1.Font.Color = convert_color_tohex('#0000FF') cell_2.Font.Color = convert_color_tohex('#0000FF') else cell_1.Font.Color = convert_color_tohex('#000000') cell_2.Font.Color = convert_color_tohex('#000000') end end work_date = params[:date] ||= Time.now.strftime('%Y-%m') works = Work.find(:all, :conditions => ["adate like '#{work_date}%' and ip=?", remote_ip], :order => 'adate' ) works.each do |work| aday = work.adate.strftime('%d').to_i btime = work.btime ctime = work.ctime content = work.content sheet.cells.Item(8+aday,4).Value = btime sheet.cells.Item(8+aday,5).Value = ctime sheet.cells.Item(8+aday,6).Value = overtime(ctime, btime) sheet.cells.Item(8+aday,7).Value = $cov.iconv(content) end reprotfile = "C:/worktime_#{Time.now.strftime('%Y%m%d')}.xls" File.delete reprotfile if FileTest::exist? reprotfile workbook.saveas reprotfile sheet = nil workbook.Close workbook = nil #excel.ActiveWorkbook.Close #excel.Workbooks.Close excel.Quit excel = nil GC.start redirect_to :action => "sendfile" end def sendfile reprotfile = "C:/worktime_#{Time.now.strftime('%Y%m%d')}.xls" send_file reprotfile unless reprotfile.blank? end private def convert_color_tohex(color) color = color.slice(1 , color.length) if color =~ /^#/ return 0 if color.length != 6 return (color[4,2] + color[2,2] + color[0,2]).hex end def substr(str,start,length) str.split(//)[start,length] end def overtime(btime, ctime) bh = substr(btime, 0, 2).to_s.to_i bm = substr(btime, 2, 4).to_s.to_f ch = substr(ctime, 0, 2).to_s.to_i cm = substr(ctime, 2, 4).to_s.to_f if bm > cm xm = (bm - cm) / 60 else xm = (bm + 60 - cm) / 60 bh = bh - 1 end bh - ch - 1 + xm end end

比较一下上面的两段代码或者就能明白原因了。

 

??进程还是关不掉？

试试这个：%x(taskkill /F /IM excel*)

全部代码如下：

class ReportController < ApplicationController require 'win32ole' require "jcode" $cov = Iconv.new('cp932', 'utf-8') def download filename = params[:filename] filepath = "C://ricksystem//"+ filename + ".xls" excel = WIN32OLE.new("excel.application") excel.Visible = false workbook = excel.WorkBooks.Open(filepath) sheet = workbook.Worksheets(1) #sheet.Select #sheet.Activate remote_ip = request.remote_ip user = User.find(:first, :conditions => ["ip=?", remote_ip]) year = Time.now.strftime('%Y').to_i month = Time.now.strftime('%m').to_i sheet.cells.Item(4, 2).Value = $cov.iconv(year.to_s.tr('0-9', '０-９') + '年' + month.to_s.tr('0-9', '０-９') + '月度作業報告書') sheet.cells.Item(5, 9).Value = $cov.iconv(user.name) eday = Date.new(year, month, -1).strftime('%d').to_i wdays = ["日", "月", "火", "水", "木", "金", "土"] for i in 1..eday do aday = Date.new(year, month, i).wday #sheet.setproperty('Cells', 8+i, 1, i) #sheet.setproperty('Cells', 8+i, 2, $cov.iconv(wdays[aday])) cell_1 = sheet.cells.Item(8+i,1) cell_2 = sheet.cells.Item(8+i,2) cell_1.Value = i cell_2.Value = $cov.iconv(wdays[aday]) if aday == 0 || aday == 6 cell_1.Font.Color = convert_color_tohex('#0000FF') cell_2.Font.Color = convert_color_tohex('#0000FF') else cell_1.Font.Color = convert_color_tohex('#000000') cell_2.Font.Color = convert_color_tohex('#000000') end end work_date = params[:date] ||= Time.now.strftime('%Y-%m') works = Work.find(:all, :conditions => ["adate like '#{work_date}%' and ip=?", remote_ip], :order => 'adate' ) works.each do |work| aday = work.adate.strftime('%d').to_i btime = work.btime ctime = work.ctime content = work.content sheet.cells.Item(8+aday,4).Value = btime sheet.cells.Item(8+aday,5).Value = ctime sheet.cells.Item(8+aday,6).Value = overtime(ctime, btime) sheet.cells.Item(8+aday,7).Value = $cov.iconv(content) end reportname = $cov.iconv(user.name) + "_" + Time.now.strftime('%Y%m') reportfile = "C://ricksystem//" + reportname + ".xls" File.delete reportfile if FileTest::exist? reportfile workbook.saveas reportfile sheet = nil workbook.Close #excel.ActiveWorkbook.Close(0) workbook = nil excel.Workbooks.Close excel.Quit excel = nil GC.start redirect_to :action => "sendfile" end def sendfile %x(taskkill /F /IM excel*) remote_ip = request.remote_ip user = User.find(:first, :conditions => ["ip=?", remote_ip]) reportname = $cov.iconv(user.name) + "_" + Time.now.strftime('%Y%m') reportfile = "C://ricksystem//" + reportname + ".xls" send_file reportfile end private def convert_color_tohex(color) color = color.slice(1 , color.length) if color =~ /^#/ return 0 if color.length != 6 return (color[4,2] + color[2,2] + color[0,2]).hex end def substr(str,start,length) str.split(//)[start,length] end def overtime(btime, ctime) bh = substr(btime, 0, 2).to_s.to_i bm = substr(btime, 2, 4).to_s.to_f ch = substr(ctime, 0, 2).to_s.to_i cm = substr(ctime, 2, 4).to_s.to_f if bm > cm xm = (bm - cm) / 60 else xm = (bm + 60 - cm) / 60 bh = bh - 1 end bh - ch - 1 + xm end end

 

其他资料：

http://d.hatena.ne.jp/spider-man/20080915/p1

下面是Perl对Excel操作的参考实现：

http://sukuna.net/blog/2009/04/10/perlperl%E3%81%8B%E3%82%89excel%E3%81%AE%E3%82%B0%E3%83%A9%E3%83%95%E3%82%92%E4%BD%9C%E3%82%8B/

http://www.tek-tips.com/faqs.cfm?fid=6715