使用QTP从excell表中拷贝数据到数据库表中

发表于:2013-07-22来源:淘测试作者:宝驹点击数: 标签:qtp
使用QTP从excell表中拷贝数据到数据库表中
'----------------------------------------------
 
' 函数名称: InsertFromTable(datasource,fold,sheet,row)
 
' 作用:  从excell表中插入数据到指定的数据表
 
' 参数说明: datasource: 指定的数据库名称
 
'                           fold: excell文件目录
 
'                          sheet: 指定的sheet,sheet名称为数据表的名称
 
'                          '      row= all ,那么插入该sheet下的所有行数据
 
                           '      row =2 ,3等序数,则插入数据指定的行数
 
'----------------------------------------------
 
Sub InsertFromTable(datasource,fold,sheet,row)
 
     Set excell = CreateObject("Excel.Application")
 
  excell.Visible = false
 
  excell.WorkBooks.Open(fold)
 
  excell.WorkSheets(sheet).Activate
 
  '得到该sheet的总列数
 
  col = excell.worksheets(sheet).UsedRange.columns.count
 
  '得到该sheet的总行数
 
  If row = "all" Then
 
      rows = excell.WorkSheets(sheet).UsedRange.Rows.Count
 
       For indexi = 2 to rows
 
        strSql = ""
 
        For indexj = 1 to col
 
        If indexj = col Then
 
         strSql = strSql +"'" +  CStr(excell.Cells(indexi, indexj).Text) + "'"
 
        else
 
         strSql = strSql + "'" +  CStr(excell.Cells(indexi, indexj).Text) + "',"
 
        End If
 
       Next
 
        '插入数据的sql
 
        sql = "insert into " + sheet +  " values(" + strSql + ")"
 
        Call InsertOne(datasource,sql)
 
      Next
 
  else
 
      For index = 1 to col
 
       If index = col Then
 
        strSql = strSql +"'" +  CStr(excell.Cells(row, index).Text) + "'"
 
       else
 
        strSql = strSql + "'" +  CStr(excell.Cells(row, index).Text) + "',"
 
       End If
 
         Next
 
        sql = "insert into AUCTION_BUYER_ANONY values(" + strSql + ")"
 
        Call InsertOne(datasource,sql)
 
     End If
 
  excell.ActiveWorkbook.Close(0)
 
  excell.Quit()
 
End Sub
 
 
如要在dbc中插入数据,则可以使用下面的方法:
 
 
fold = "d:\test.xls"
 
sheet = "AUCTION_BUYER_ANONY"
 
call InsertFromTable(devdbc,fold,sheet,"all") 

原文转自:http://www.taobaotesting.com/blogs/qa?bid=107