[编程示例]excel, mysql, testunit

require 'win32ole'
require 'rubygems'
require 'mysql'
class Excel2Mysql @excel
@wbook
@myDB
def open_ExlDB(exlFile, dbName)
@excel=WIN32OLE.new('excel.application')
@wbook=@excel.Workbooks.Open(exlFile)
@myDB=Mysql.connect('localhost','root','root','')
puts @myDB.get_server_info
if @myDB.list_dbs.index(dbName) != nil
puts "exists #{dbName}"
return false
end
@myDB.query("create database #{dbName}")
@myDB.query("use #{dbName}")
return true
end
def xls2db(exlFile, dbName)
finished=0 if !open_ExlDB(exlFile,dbName)
return finished
end
@myDB.query("use #{dbName}")
@wbook.worksheets.each {|ws|
finished+=saveOneSheet(ws)
}
closeAll
return finished
end
private
def saveOneSheet(ws)
if ws.Cells(1,1).value ==nil
puts "#{ws.name} is empty, neednot create database"
return 0
end
colName=ws.Cells(1,1).value
typeName=getType(ws.Cells(2,1).value)
puts ws.Cells(2,1).value
puts typeName
puts "create table #{ws.name}(#{colName} #{typeName})"
@myDB.query("CREATE table #{ws.name}(#{colName} #{typeName},primary key(#{colName}))")
for colum in 2..ws.UsedRange.Columns.Count do
colName=ws.Cells(1,colum).value
typeName=getType(ws.Cells(2,colum).value)
@myDB.query("ALTER table #{ws.name} ADD #{colName} #{typeName}")
end
for row in 2..ws.UsedRange.Rows.Count do
saveRow(ws,row)
end
return 1
end
def saveRow(ws, row)
queryStr="insert into #{ws.name}("
for colum in 1..ws.UsedRange.Columns.Count do
queryStr << ws.Cells(1,colum).value
queryStr+=","
end
queryStr.chop!
queryStr<<") VALUES("
for colum in 1..ws.UsedRange.Columns.Count do
if(ws.Cells(row,colum).value==nil)
queryStr<<"NULL,"
next
end
queryStr<<"'"
queryStr<<ws.Cells(row,colum).value.to_s
queryStr<<"',"
end
queryStr.chop!
queryStr<<")"
puts queryStr
@myDB.query(queryStr)
end
def getType(temValue)
if temValue==nil
return "char(50)"
end
if (temValue=~/\d+\.\d+/)!=nil
return "float"
elsif (temValue=~/\D+/)!=nil
return "char(50)"
end
return "int"
end
public
def closeAll()
@excel.quit()
@myDB.close()
return true
end
end

这段是对上面类的UNIT TEST，creatExcel方法自己造一个测试用的EXCEL文件
两个测试METHOD， 一个测试文件是否能打开，DATABASE是否能创建；另一个测试导入的数据是否相同
随便楼主学习了一下如何用RUBY操作YSQL数据库
require "test/unit"
require "excel2mySql.rb"
require 'win32ole'
require 'rubygems'
require 'mysql'
class Test_excel2Mysql < Test::Unit::TestCase
@aTransfer
@exName="d:\\test.xls"
@dbName="testDB"
def creatExcel
excel=WIN32OLE.new('excel.application')
excel.Visible=true
workbook = excel.Workbooks.Add()
worksheet = workbook.Worksheets(1)
worksheet.name="fruits"
worksheet.Cells(1,1).Value="NAME"
worksheet.Range("b1").Value="ID"
worksheet.Range("a2").Value="apple"
worksheet.Range("a3").Value="banana"
worksheet.Range("a4").Value="cherry"
id=1
worksheet.Range("b2:b4").each do |oneC|
oneC.Value=id
id=id+1
end
worksheet=workbook.Worksheets(2)
worksheet.Range("a1").Value="ID"
worksheet.Range("b1").Value="GOODS"
worksheet.Range("c1").Value="PRICE"
worksheet.Range("a2").Value="10001"
worksheet.Range("b2").Value="computer"
worksheet.Range("c2").Value="5050.5"
worksheet.Name="goods"
excel.ActiveWorkbook.SaveAs("d:\\test.xls")
excel.ActiveWorkbook.Close(0)
excel.Quit()
end
def setup
@aTransfer=Excel2Mysql.new
creatExcel
#super
end
def teardown
myDB=Mysql.connect('localhost','root','root','')
if myDB.list_dbs.index('test')!=nil
myDB.query("drop database test")
end
end
def testOpenClose
assert @aTransfer.open_ExlDB("d:\\test.xls","test"), "error in open"
assert @aTransfer.closeAll(), "error in close"
end
def testTrans
assert @aTransfer.xls2db("d:\\test.xls","test")
myDB=Mysql.connect('localhost','root','root','')
assert_not_nil myDB.list_dbs.index("test")
myDB.query("use test")
assert_equal myDB.list_tables.size(), 2
res=myDB.query("select * from fruits")
assert_equal res.num_rows(), 3
res.data_seek(1)
row=res.fetch_row()
assert_equal row[0],"banana"
end
end