基于QTP的自动化测试留下一份向数据库自动导入测试数据的VBS脚本

最新推荐文章于 2022-03-31 14:51:09 发布

wangxin0314

最新推荐文章于 2022-03-31 14:51:09 发布

阅读量593

点赞数

分类专栏： web UI自动化测试 QTP 文章标签：自动化测试 QTP 接口测试 vbs 脚本

本文链接：https://blog.csdn.net/wangxin1982314/article/details/50234581

版权

web UI自动化测试同时被 2 个专栏收录

34 篇文章 0 订阅

订阅专栏

QTP

2 篇文章 0 订阅

订阅专栏

当时考虑的不多有很多需要改进的地方，不过必定是自己费神写的，虽然早已转战开源工具，还是留下做个纪念吧

注：测试人员只需要把数据EXCEL编辑好即可，测试框架会自动将数据导入数据库。

 '----------------------------------说明信息-------------------------------------------
 
                         '信息编辑区域变量说明
  '2012年7月21日最终版xin.wang		 
  'strWorkbook:需要导入到数据库的Excel数据文件名
  'strWorkSheet：需要导入到数据库的Excel数据数据表名(Sheet名)
  'DataTableHeadRowNum:数据表表名所在行
  'DataTableHeadRowNum:数据表列名所在行
  'intFirstRow：需要导入的数据在sheet中的起始行
  'intLastRow：要导入的数据在sheet中的结束行
  'tableNameArr：需要导入的数据库表名数组(在sheet中定义好且与数据库表明一致)
  
  '注：1.使用人根据需要只需要修改参数值编辑区即可，
  '    2.需保证数据表数据正确

'---------------------------------------函数入口-------------------------------------------
 
 '--------------------------------参数编辑区----------------------------------------------
 	
		'strDataID="1100-01"
	
		'intDataIDCol=1
		'strWorkbook="BasicDataSheet.xls"    '配置文件中的数据           
		'intDataTableHeadRowNum=1
		'intDataTableColumnRowNum=2
		'intStartSearchColNum=1
		'intFirstRow=3
		'strDataColRange="A65536"
		
 '------------------------------------------------------------------------------------------
 
		'strWorkSheet="WebReport"  'ACTION中需要使用的参数
		'strDataArea="all"			
		
'----------------------------------调用主调函数实现向数据库批量插入数据-------------------

'autoInsert strWorkbook,strWorkSheet,intDataTableHeadRowNum,intDataTableColumnRowNum,strDataColRange,intStartSearchColNum,intFirstRow,intDataIDCol,strDataArea

'---------------------------------------主调函数-------------------------------------------

Function autoInsert(strDBString,strWorkbook,strWorkSheet,intDataTableHeadRowNum,intDataTableColumnRowNum,strDataColRange,intStartSearchColNum,intFirstRow,intDataIDCol,strDataArea)
	
	strDataID=""
	intDataRowCount=""
	
	
	Set conn = createobject("ADODB.Connection")	
	conn.Open strDBString
		
	Set oexcel = CreateObject("excel.application")	
	oexcel.Visible=False
'	oexcel.DisplayAlerts =false
	Set oEx = oexcel.Workbooks.Open(strWorkbook)
	oEx.Worksheets(strWorkSheet).Activate
'	oEx.Save
	
	dim objConfiguration,DataRowCount
	
	set objConfiguration=new ConfigurationOfSql	

	objConfiguration.DataColRange=strDataColRange
	objConfiguration.DataIDCol=intDataIDCol               	'设置对象属性值
	objConfiguration.DataTableHeadRowNum=intDataTableHeadRowNum
	objConfiguration.DataTableColumnRowNum=intDataTableColumnRowNum
	objConfiguration.FirstDataRow=intFirstRow
	objConfiguration.StartSearchColNum=cint(intStartSearchColNum)
	set objConfiguration.objExcel=oexcel
	
	strDataArea=cstr(ucase(trim(strDataArea)))
	
	if strDataArea="ALL" then
			
		strDataID=""
		intDataRowCount=""
		
	Else
		
		arrPara=split(strDataArea,",")
		strDataID=trim(arrPara(0))
		intDataRowCount=cint(trim(arrPara(1)))
		
	end if
	 
		
	Do 			   
			DataRowCount=0	
			objConfiguration.AreaFirstCol = objConfiguration.FindColAreaFirstCol

			if objConfiguration.AreaFirstCol<>0 And Not cbool(objConfiguration.TableEnd) then
						
				objConfiguration.AreaLastCol = objConfiguration.FindColAreaLastCol
				insertColumnNameSql = objConfiguration.BuildSqlCol
									
						if not strDataID="" then
						
							  for k=cint(objConfiguration.FirstDataRow) to cint(objConfiguration.GetLastRowNum)	
							  
									if objConfiguration.GetDataID(k,objConfiguration.DataIDCol)=strDataID then
									
									  if not objConfiguration.TableisEmpty(k) then
																										'  循环向数据库插入数据
											Sql=objConfiguration.BuildSqlByData(insertColumnNameSql,k)
											conn.Execute Sql
											DataRowCount=DataRowCount+1
											
									   end if
									  
									   
									   if DataRowCount=cint(intDataRowCount) then
									   
										  Exit For
									   
									   End IF 
									   
									End If
								
							   next
							
						Else
									
							  for k=cint(objConfiguration.FirstDataRow) to cint(objConfiguration.GetLastRowNum)	
							  
									if not objConfiguration.TableisEmpty(k) then
																										'  循环向数据库插入数据
											Sql=objConfiguration.BuildSqlByData(insertColumnNameSql,k)
											conn.Execute Sql
											
									end if
									
							   next
									
					    End IF 
					
			Else
			
				 Exit Do
				
			end if 
	Loop
	
	
	Set objWshShell = nothing
	set objConfiguration=nothing
  	conn.Close
	Set conn = nothing			
	oEx.close False		
	set oEx= nothing
	oexcel.Quit
	set oexcel = nothing
	
'---------------------------------------------The End--------------------------------------
		
End Function
   
'-----------------------格式化日期函数----------------------------------------
Function  FormatDate(strDate)

	If month(strDate)<=9 and day(strDate)<=9 Then
			   
		   FormatDate=year(strDate)&"-"&"0"&month(strDate)&"-"&"0"&day(strDate)

	ElseIF month(strDate)<=9 and day(strDate)>9 Then

		  FormatDate=year(strDate)&"-"&"0"&month(strDate)&"-"&day(strDate)

	ElseIF  month(strDate)>9 and day(strDate)<=9 Then

		   FormatDate=year(strDate)&"-"&month(strDate)&"-"&"0"&day(strDate)

	End If

End Function


'--------------------------------查找列构建SQL类-----------------------------------------------------

class ConfigurationOfSql

	Private strDataColRange,strAreaName,intDataTableHeadRowNum,intDataTableColumnRowNum,oexcel
	
	Private intDataIDCol,intAreaFirstCol,intAreaLastCol,intFirstDataRow,intStartSearchColNum,bolTheTableEnd
	
	Private strInsertDataSuccessfullMsg,strDelDataSuccessfullyMsg,strDelDataErrorMsg,strErrormsg
	
	private intMsgBoxDisplayTime,strMsgBoxTitle,intMsgBoxModel
	
	
	
	Private Sub Class_Initialize	' 设置初始化数据
	
	   '---------------------对象属性--------------------------
		intDataTableHeadRowNum=""
		intDataTableColumnRowNum=""
		strAreaName=""
		intAreaLastCol=""
		intAreaFirstCol=""
		bolTheTableEnd=False
		set oexcel=nothing
		
		'----------------提示窗口配置信息----------------------
		
		intMsgBoxDisplayTime=5
		strMsgBoxTitle="执行过程提示"
		strMsgBoxModel=1
		
		'----------------提示窗口信息内容----------------------
		
		strInsertDataSuccessfullMsg="向DB插入数据成功！"
		strDelDataSuccessfullyMsg="删除脏数据成功"
	    strDelDataErrorMsg="删除脏数据失败!数据未能完全删除"
		strErrormsg="无此类消息"
	
    End Sub
	
'--------------------------------设置构建SQL类属性值-----------------------------------------------------

	public Property let DataColRange(intValue)

		strDataColRange=cstr(intValue)

	End Property

	public Property let DataIDCol(intValue)

		intDataIDCol=cint(intValue)

	End Property
	
	public Property let DataTableHeadRowNum(intValue)

		intDataTableHeadRowNum=cint(intValue)

	End Property
	
	public Property let DataTableColumnRowNum(intValue)

		intDataTableColumnRowNum=cint(intValue)

	End Property
	
	public Property let FirstDataRow(intValue)

		intFirstDataRow=cint(intValue)

	End Property

	public Property let StartSearchColNum(intValue)

		intStartSearchColNum=cint(intValue)

	End Property
	
	public Property set objExcel(objHandle)

		set oexcel=objHandle

	End Property
	
		public Property let AreaFirstCol(strValue)

		intAreaFirstCol=strValue

	End Property
	
	public Property let AreaLastCol(strValue)

		intAreaLastCol=strValue

	End Property
	
	public Property get AreaFirstCol()

		AreaFirstCol=intAreaFirstCol

	End Property
	
	public Property get FirstDataRow()

		FirstDataRow=intFirstDataRow

	End Property
	
	public Property get TableEnd()

		TableEnd=bolTheTableEnd

	End Property
	
	
	public Property get DataIDCol()

		 DataIDCol=intDataIDCol

	End Property
		
'----------------------------------信息提示窗口---------------------------------------------------------------

	public Function DisplayMsgbox(objWshShell,strMsgcategory)
		
		select case strMsgcategory
		
		case "InsertDataSuccessfullMsg"
				
			 objWshShell.popup strInsertDataSuccessfullMsg,intMsgBoxDisplayTime,strMsgBoxTitle,strMsgBoxModel
		
		case "DelDataSuccessfullyMsg"
			
			 objWshShell.popup strDelDataSuccessfullyMsg,intMsgBoxDisplayTime,strMsgBoxTitle,strMsgBoxModel
			
		case "DelDataErrorMsg"
		
			 objWshShell.popup strDelDataErrorMsg,intMsgBoxDisplayTime,strMsgBoxTitle,strMsgBoxModel
			 
		case "TableErrorMsg"
		
		    TableErrorMsg="不存在"&strAreaName&"表"
			
			objWshShell.popup TableErrorMsg,intMsgBoxDisplayTime,strMsgBoxTitle,strMsgBoxModel

		Case Else
		
		    objWshShell.popup strErrormsg,intMsgBoxDisplayTime,strMsgBoxTitle,strMsgBoxModel
			
		End Select
	
	end Function
'----------------------------------查找指定列的列号-----------------------------------------------

	public Function FindColumnNum(strColName)

		FindColumnNum=0

		For i=intAreaFirstCol to intAreaLastCol
		
				If  oexcel.Cells(intDataTableColumnRowNum,i)=strColName Then

						FindColumnNum=i
			
						Exit Function
		
				End If
		
			next

	End Function

'----------------------------------查找指定区域首列-----------------------------------------------


  public Function  FindColAreaFirstCol()

		FindColAreaFirstCol=0
		
		intEndCol=cint(oexcel.activesheet.cells(intDataTableColumnRowNum,1).end(-4161).column)
		
		if intStartSearchColNum<=intEndCol then
		
			For i=intStartSearchColNum to intEndCol

					If  oexcel.Cells(intDataTableHeadRowNum,i)<>"" Then
							
							 strAreaName=oexcel.Cells(intDataTableHeadRowNum,i)
							 FindColAreaFirstCol=i
							 Exit For
			
					End If
			
			next
		Else
		
		   bolTheTableEnd=True
			
		End If 
		
	End Function

'----------------------------------查找指定区域末列----------------------------------------------------

	public Function  FindColAreaLastCol()
	
		stColAddress=oexcel.ActiveSheet.cells(intDataTableHeadRowNum,Cint(intAreaFirstCol)).address

		stColName_temp=mid(stColAddress,2)

		stColName=mid(stColName_temp,1,(cint(instr(1,stColName_temp,"$",1))-1))

		intColCount=oexcel.activesheet.Range(stColName&"1").MergeArea.Columns.Count
		
		intColAreaLastCol=(cint(intColCount-1)+Cint(intAreaFirstCol))
		
		intStartSearchColNum=intColAreaLastCol+1
		
		FindColAreaLastCol=intColAreaLastCol

	End Function
	
	'----------------------------------查找当前数据表最后一行的行号------------------------------------
	
    public Function  GetLastRowNum()

			GetLastRowNum=oexcel.activesheet.Range(strDataColRange).end(-4162).Row
			
	End Function
	
	'----------------------------------构建SQL所需的列-------------------------------------------------
	
	Public function BuildSqlCol()
	
		Cols=""

		for i=cint(intAreaFirstCol) to cint(intAreaLastCol)

                if i=cint(intAreaLastCol) then

                  Cols=Cols&oexcel.Cells(intDataTableColumnRowNum,i)
				  
				else
				 
				  Cols=Cols&oexcel.Cells(intDataTableColumnRowNum,i)&","
				
				End IF
			
		next 
		
		BuildSqlCol="insert into "&strAreaName&" ("&Cols&") values ('"
	
	End Function
	
	
	'----------------------------------判断数据表是否为空--------------------------------------------
	
	Public function TableisEmpty(intRownum)
		
		 TableisEmpty=True
		
		for i=intAreaFirstCol  to intAreaLastCol
		
		     if not oexcel.Cells(intRownum,i)="" then
				  
				  TableisEmpty=False
				  Exit Function		 
				
			 end if	
		next
			
	end function
	
	'----------------------------------构建完整可被执行SQL-----------------------------------------------
	
	Public function BuildSqlByData(StructSqlCol,intRownum)
		
	    BuildSqlByData=""
		PatientSql_value=""

	    for i=cint(intAreaFirstCol)  to cint(intAreaLastCol)
								
				 if i=cint(intAreaLastCol) then
				  
				    PatientSql_value=PatientSql_value&oexcel.Cells(intRownum,i)&"')"
					
				 else
				
				    PatientSql_value=PatientSql_value&oexcel.Cells(intRownum,i)&"','"
				  
				 end if							
		next 
		
		    BuildSqlByData=StructSqlCol&PatientSql_value
		
	end function

	'----------------------------------GetDataIDOfCurrentRow-----------------------------------------------
	
	public Function  GetDataID(intCaseIDCol,intCaseIDRow)

	   GetDataID=oexcel.Cells(cint(intCaseIDCol),cint(intCaseIDRow))
		
	End Function
	'----------------------------------删除脏数据---------------------------------------------------------
	
	public Function DelDirtyData(objADODB,arrtableNameArr,intPosition)
	
		DelDirtyData=true
		   
		Position=cint(intPosition)
			
		if Position=0 then
			
				Exit Function
		  
		end if
		  
		if Position>0 and  Position <= ubound(arrtableNameArr) then
				  
						sql0="delete from MPPS"
						sql1="delete from OrderInfo"
						sql2="delete from ExamInfo"
						sql3="delete from PatientInfo"
						sql4="delete from ScheduleTemp"
						sql5="delete from Report"
	
						objADODB.Execute sql0
						objADODB.Execute sql1
						objADODB.Execute sql2
						objADODB.Execute sql3
						objADODB.Execute sql4
						objADODB.Execute sql5
							
		Else
						   
					DelDirtyData=False
				 
		end if
	   
	End Function

	Private Sub Class_Terminate
		
		set oexcel=nothing
	
	end sub
	
End Class

'------------------------------------------------------------------------------------------------------------

wangxin0314

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
基于QTP的自动化测试留下一份向数据库自动导入测试数据的VBS脚本

当时考虑的不多有很多需要改进的地方，不过必定是自己费神写的，虽然早已转战开源工具，还是留下做个纪念吧注：测试人员只需要把数据EXCEL编辑好即可，测试框架会自动将数据导入数据库。 '----------------------------------说明信息-------------------------------------------
复制链接

扫一扫