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

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

注：测试人员只需要把数据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 strDBStringSet oexcel = CreateObject("excel.application")oexcel.Visible=False'oexcel.DisplayAlerts =falseSet oEx = oexcel.Workbooks.Open(strWorkbook)oEx.Worksheets(strWorkSheet).Activate'oEx.Savedim objConfiguration,DataRowCountset objConfiguration=new ConfigurationOfSqlobjConfiguration.DataColRange=strDataColRangeobjConfiguration.DataIDCol=intDataIDCol               '设置对象属性值objConfiguration.DataTableHeadRowNum=intDataTableHeadRowNumobjConfiguration.DataTableColumnRowNum=intDataTableColumnRowNumobjConfiguration.FirstDataRow=intFirstRowobjConfiguration.StartSearchColNum=cint(intStartSearchColNum)set objConfiguration.objExcel=oexcelstrDataArea=cstr(ucase(trim(strDataArea)))if strDataArea="ALL" thenstrDataID=""intDataRowCount=""ElsearrPara=split(strDataArea,",")strDataID=trim(arrPara(0))intDataRowCount=cint(trim(arrPara(1)))end if Do    DataRowCount=0objConfiguration.AreaFirstCol = objConfiguration.FindColAreaFirstColif objConfiguration.AreaFirstCol<>0 And Not cbool(objConfiguration.TableEnd) thenobjConfiguration.AreaLastCol = objConfiguration.FindColAreaLastColinsertColumnNameSql = objConfiguration.BuildSqlColif 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 SqlDataRowCount=DataRowCount+1   end if        if DataRowCount=cint(intDataRowCount) then     Exit For      End IF    End If   nextElse  for k=cint(objConfiguration.FirstDataRow) to cint(objConfiguration.GetLastRowNum)  if not objConfiguration.TableisEmpty(k) then'  循环向数据库插入数据Sql=objConfiguration.BuildSqlByData(insertColumnNameSql,k)conn.Execute Sqlend if   next    End IF Else Exit Doend if LoopSet objWshShell = nothingset objConfiguration=nothing  conn.CloseSet conn = nothingoEx.close Falseset oEx= nothingoexcel.Quitset 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 IfEnd Function'--------------------------------查找列构建SQL类-----------------------------------------------------class ConfigurationOfSqlPrivate strDataColRange,strAreaName,intDataTableHeadRowNum,intDataTableColumnRowNum,oexcelPrivate intDataIDCol,intAreaFirstCol,intAreaLastCol,intFirstDataRow,intStartSearchColNum,bolTheTableEndPrivate strInsertDataSuccessfullMsg,strDelDataSuccessfullyMsg,strDelDataErrorMsg,strErrormsgprivate intMsgBoxDisplayTime,strMsgBoxTitle,intMsgBoxModelPrivate Sub Class_Initialize' 设置初始化数据   '---------------------对象属性--------------------------intDataTableHeadRowNum=""intDataTableColumnRowNum=""strAreaName=""intAreaLastCol=""intAreaFirstCol=""bolTheTableEnd=Falseset oexcel=nothing'----------------提示窗口配置信息----------------------intMsgBoxDisplayTime=5strMsgBoxTitle="执行过程提示"strMsgBoxModel=1'----------------提示窗口信息内容----------------------strInsertDataSuccessfullMsg="向DB插入数据成功！"strDelDataSuccessfullyMsg="删除脏数据成功"    strDelDataErrorMsg="删除脏数据失败!数据未能完全删除"strErrormsg="无此类消息"    End Sub'--------------------------------设置构建SQL类属性值-----------------------------------------------------public Property let DataColRange(intValue)strDataColRange=cstr(intValue)End Propertypublic Property let DataIDCol(intValue)intDataIDCol=cint(intValue)End Propertypublic Property let DataTableHeadRowNum(intValue)intDataTableHeadRowNum=cint(intValue)End Propertypublic Property let DataTableColumnRowNum(intValue)intDataTableColumnRowNum=cint(intValue)End Propertypublic Property let FirstDataRow(intValue)intFirstDataRow=cint(intValue)End Propertypublic Property let StartSearchColNum(intValue)intStartSearchColNum=cint(intValue)End Propertypublic Property set objExcel(objHandle)set oexcel=objHandleEnd Propertypublic Property let AreaFirstCol(strValue)intAreaFirstCol=strValueEnd Propertypublic Property let AreaLastCol(strValue)intAreaLastCol=strValueEnd Propertypublic Property get AreaFirstCol()AreaFirstCol=intAreaFirstColEnd Propertypublic Property get FirstDataRow()FirstDataRow=intFirstDataRowEnd Propertypublic Property get TableEnd()TableEnd=bolTheTableEndEnd Propertypublic Property get DataIDCol() DataIDCol=intDataIDColEnd Property'----------------------------------信息提示窗口---------------------------------------------------------------public Function DisplayMsgbox(objWshShell,strMsgcategory)select case strMsgcategorycase "InsertDataSuccessfullMsg" objWshShell.popup strInsertDataSuccessfullMsg,intMsgBoxDisplayTime,strMsgBoxTitle,strMsgBoxModelcase "DelDataSuccessfullyMsg" objWshShell.popup strDelDataSuccessfullyMsg,intMsgBoxDisplayTime,strMsgBoxTitle,strMsgBoxModelcase "DelDataErrorMsg" objWshShell.popup strDelDataErrorMsg,intMsgBoxDisplayTime,strMsgBoxTitle,strMsgBoxModel case "TableErrorMsg"    TableErrorMsg="不存在"&strAreaName&"表"objWshShell.popup TableErrorMsg,intMsgBoxDisplayTime,strMsgBoxTitle,strMsgBoxModelCase Else    objWshShell.popup strErrormsg,intMsgBoxDisplayTime,strMsgBoxTitle,strMsgBoxModelEnd Selectend Function'----------------------------------查找指定列的列号-----------------------------------------------public Function FindColumnNum(strColName)FindColumnNum=0For i=intAreaFirstCol to intAreaLastColIf  oexcel.Cells(intDataTableColumnRowNum,i)=strColName ThenFindColumnNum=iExit FunctionEnd IfnextEnd Function'----------------------------------查找指定区域首列-----------------------------------------------  public Function  FindColAreaFirstCol()FindColAreaFirstCol=0intEndCol=cint(oexcel.activesheet.cells(intDataTableColumnRowNum,1).end(-4161).column)if intStartSearchColNum<=intEndCol thenFor i=intStartSearchColNum to intEndColIf  oexcel.Cells(intDataTableHeadRowNum,i)<>"" Then strAreaName=oexcel.Cells(intDataTableHeadRowNum,i) FindColAreaFirstCol=i Exit ForEnd IfnextElse   bolTheTableEnd=TrueEnd If End Function'----------------------------------查找指定区域末列----------------------------------------------------public Function  FindColAreaLastCol()stColAddress=oexcel.ActiveSheet.cells(intDataTableHeadRowNum,Cint(intAreaFirstCol)).addressstColName_temp=mid(stColAddress,2)stColName=mid(stColName_temp,1,(cint(instr(1,stColName_temp,"$",1))-1))intColCount=oexcel.activesheet.Range(stColName&"1").MergeArea.Columns.CountintColAreaLastCol=(cint(intColCount-1)+Cint(intAreaFirstCol))intStartSearchColNum=intColAreaLastCol+1FindColAreaLastCol=intColAreaLastColEnd Function'----------------------------------查找当前数据表最后一行的行号------------------------------------    public Function  GetLastRowNum()GetLastRowNum=oexcel.activesheet.Range(strDataColRange).end(-4162).RowEnd 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 IFnext BuildSqlCol="insert into "&strAreaName&" ("&Cols&") values ('"End Function'----------------------------------判断数据表是否为空--------------------------------------------Public function TableisEmpty(intRownum) TableisEmpty=Truefor i=intAreaFirstCol  to intAreaLastCol     if not oexcel.Cells(intRownum,i)="" then    TableisEmpty=False  Exit Function  end ifnextend 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 ifnext     BuildSqlByData=StructSqlCol&PatientSql_valueend 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 thenExit 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 sql0objADODB.Execute sql1objADODB.Execute sql2objADODB.Execute sql3objADODB.Execute sql4objADODB.Execute sql5Else   DelDirtyData=False end if   End FunctionPrivate Sub Class_Terminateset oexcel=nothingend subEnd Class'------------------------------------------------------------------------------------------------------------