用jruby操作excel

一、下载jruby二进制安装包，解压到相应的目录下，并将其bin目录添加到系统 的PATH变量中，则完成了安装；

二、安装java，完成安装后创建系统变量JAVA_HOME，把java的安装目录设为其值；

三、在dos窗口中运行jirb_swing，出现相应的界面，那么你的jruby就成功安装了；

四、下载jxl.jar，放到某一个目录下，并在系统的CLASSPATH变量中把它添加到其中；

五、现在就可以操作excel了，下面是一个简单的实例：

 

require "java"
include_class "jxl.Workbook"
include_class "jxl.write.Label"
include_class "jxl.write.WritableSheet"
include_class "jxl.write.WritableWorkbook"

file=java.io.File.new("D:\\test.xls")
book=Workbook.createWorkbook(file)
sheet=book.createSheet("表格1",0)
label=Label.new(0,0,"世界，你好！")
sheet.addCell(label)
book.write
book.close

 

这样，一个简单的操作excel的程序就写出来了。

 

当然你也可以用ruby的win32ole包，但是要复杂的多，如下一例：

 

 

require 'win32ole'
excel = WIN32OLE::new('excel.Application')
workbook = excel.Workbooks.Open('E:\RubyApp\bmk.xls')
worksheet = workbook.Worksheets(1) #get hold of the first worksheet
worksheet.Select  #bring it to the front -need sometimes to run macros, not for working with a worksheet from ruby
#excel['Visible'] = true #make visible, set to false to make invisible again. Don't need it to be visible for script to work
#value1=worksheet.Range('a12')['Value']  #get value of single cell
#puts value1
#data = worksheet.Range('a1:c12')['Value'] #read into 2D array
#data.each do |data1|
    #data1.each do |data2|
        #puts data2
      #end
#end
line = '1'
while worksheet.Range("c#{line}")['Value']
   line.succ!
end #line now holds row number of first empty row
puts line

#i=3
#puts worksheet.cells(i-1,1).text[8,16].to_i+1
#处理准考证号，让它自动增加
#while i < line.to_i
for i in 3...line.to_i



	   worksheet.cells(i,1).value="00320501"+(worksheet.cells(i-1,1).text[8,15].to_i + 1).to_s
	   worksheet.cells(i,2).value="320501"+(worksheet.cells(i-1,2).text[6,13].to_i + 1).to_s
		   if worksheet.cells(i,7).text.length==18
			  worksheet.cells(i,5).value=worksheet.cells(i,7).text[6,8]
		   else 
			worksheet.cells(i,5).value="19"+worksheet.cells(i,7).text[6,6]
		   end
           
		   worksheet.range("h#{i}").value=worksheet.range("h#{i-1}").value.to_s
		   worksheet.range("i#{i}").value=worksheet.range("i#{i-1}").value.to_s
		   worksheet.range("k#{i}").value=worksheet.range("k#{i-1}").value.to_s
		   worksheet.range("l#{i}").value=worksheet.range("l#{i-1}").value.to_s
		   worksheet.range("o#{i}").value=worksheet.range("o#{i-1}").value.to_s
		   worksheet.range("p#{i}").value=worksheet.range("p#{i-1}").value.to_s
		   worksheet.range("q#{i}").value=worksheet.range("q#{i-1}").value.to_s
		   worksheet.range("r#{i}").value=worksheet.range("r#{i-1}").value.to_s
		   worksheet.range("s#{i}").value=worksheet.range("s#{i-1}").value.to_s
# i=i+1
 end
#worksheet.Range('e2')['Value'] = Time.now.strftime '%d/%m/%Y' #single value
#worksheet.Range('a5:c5')['Value'] = ['Test', '25', 'result']
#value2 = worksheet.range("b6").value
#puts value2
workbook.Close(1)
excel.Quit
excel = nil
GC.start