軟件測(cè)試非常全qtp學(xué)習(xí)excel函數(shù)

DimExcelApp'AsExcel.ApplicationDimexcelSheet'AsExcel.worksheetDimexcelBook'AsExcel.workbookDimfso'As*********************************************************************************************'函數(shù)說(shuō)明：創(chuàng)建一個(gè)Excel應(yīng)用程序ExcelApp,并創(chuàng)建一個(gè)新的工作Workbook;'參數(shù)說(shuō)明：無(wú)'調(diào)用方法 'FunctionCreateExcel()DimexcelSheetSetExcelApp=CreateObject("Excel.Application")ExcelApp.Visible=SetCreateExcel=ExcelAppEndFunction*********************************************************************************************'函數(shù)說(shuō)明：關(guān)閉Excel應(yīng)用程序;' (1)ExcelApp:Excel應(yīng)用程序名稱(chēng);'調(diào)用方法: '*********************************************************************************************SubCloseExcel(ExcelApp)SetexcelSheet=ExcelApp.ActiveSheetSetexcelBook=ExcelApp.ActiveWorkbookSetfso=OnErrorResumeNextfso.CreateFolder"C:\Temp"fso.DeleteFile"C:\Temp\ExcelExamples.xls"excelBook.SaveAs"C:\Temp\ExcelExamples.xls"SetExcelApp=NothingSetfso=NothingErr=OnErrorGoTo0EndSub*********************************************************************************************'函數(shù)說(shuō)明：保存工作薄;' (2)workbookIdentifier:屬于ExcelApp的工作薄名稱(chēng); ' (2)保存失敗，返回字符串：BadWorksheetIdentifier'調(diào)用方法: ret=SaveWorkbook(ExcelApp,"Book1",'FunctionSaveWorkbook(ExcelApp,workbookIdentifier,path)'AsStringDimworkbookOnErrorResumeNext'SetworkbookExcelApp.Workbooks(workbookIdentifier)OnErrorGoTo0'禁用錯(cuò)誤處理程序IfNotworkbookIsNothingIfpath=""Orpath=workbook.FullNameOrpath=workbook.NameThenSetfso=IfInStr(path,".")=0Thenpath=path&EndOnErrorResumeNextfso.DeleteFilepathSetfso=NothingErr=0OnErrorGoToworkbook.SaveAspathEndIfSaveWorkbook="OK"SaveWorkbook="BadWorkbookIdentifier"EndIfEnd*********************************************************************************************'函數(shù)說(shuō)明：設(shè)置工作表excelSheet單元格的值' (3)column:行的序號(hào),第一行為1; ' '調(diào)用方法 SetCellValueexcelSheet1,1,2,'SubSetCellValue(excelSheet,row,column,OnErrorResumeNextexcelSheet.Cells(row,column)=valueOnErrorGoTo0End'TheGetCellValuereturnsthecell'svalueaccordingtoitsrowcolumnandsheet'excelSheet-theExcelSheetinwhichthecellexists'row-thecell'srow'column-thecell'scolumn'return0ifthecellcouldnotbe*********************************************************************************************'函數(shù)說(shuō)明：獲取工作表excelSheet單元格的值' '返回結(jié)果： (2)單元格不存在，返回0;'調(diào)用方法 setCellValue=GetCellValue(excelSheet,1,'FunctionGetCellValue(excelSheet,row,column)value=0Err=OnErrorResumetempValue=excelSheet.Cells(row,column)IfErr=0Thenvalue=tempValueErr=0EndOnErrorGoTo0GetCellValue=valueEnd*********************************************************************************************'函數(shù)說(shuō)明：獲取并返回工作表對(duì)象' (2)sheetIdentifier:ExcelApp的工作表名稱(chēng);'返回結(jié)果： (1)成功：工作表對(duì)象Excel.worksheet '調(diào)用方法 SetexcelSheet1=GetSheet(ExcelApp,"Sheet'FunctionGetSheet(ExcelApp,sheetIdentifier)OnErrorResumeNextSetGetSheet=ExcelApp.Worksheets.Item(sheetIdentifier)OnErrorGoTo0End*********************************************************************************************'函數(shù)說(shuō)明：添加一張新的工作表' (2)workbookIdentifier:屬于ExcelApp的工作薄名稱(chēng); ' (1)成功：工作表對(duì)象 '調(diào)用方法: 'FunctionInsertNewWorksheet(ExcelApp,workbookIdentifier,sheetName)Dimworkbook'AsExcel.workbookDimworksheet'AsIfworkbookIdentifier=""ThenSetworkbook=ExcelApp.ActiveWorkbookOnErrorResumeNextErr=0Setworkbook=ExcelApp.Workbooks(workbookIdentifier)IfErr<>0ThenSetInsertNewWorksheet=NothingErr=0ExitFunctionEndIfOnErrorGoTo0EndIfsheetCountworkbook.Sheets.Count'獲取工作薄中工作表的數(shù)量workbook.Sheets.Add,sheetCount'添加工作表Setworksheetworkbook.Sheets(sheetCount1)'初始化worksheetIfsheetName<>""Thenworksheet.Name=sheetNameEndIfSetInsertNewWorksheet=worksheetEndFunction*********************************************************************************************'函數(shù)說(shuō)明：修改工作表的名稱(chēng);' (2)workbookIdentifier:屬于ExcelApp的工作薄名稱(chēng) (3)worksheetIdentifier:屬于workbookIdentifier工作薄的工作表名稱(chēng); ' (2)修改失敗，返回字符串：BadWorksheetIdentifier'調(diào)用方法: setret=RenameWorksheet(ExcelApp,"Book1","Sheet1","SheetName")'*********************************************************************************************FunctionRenameWorksheet(ExcelApp,workbookIdentifier,worksheetIdentifier,sheetName)DimworkbookDimOnErrorResumeNextErr=0Setworkbook=ExcelApp.Workbooks(workbookIdentifier)IfErr<>0ThenRenameWorksheet="BadWorkbookIdentifier"Err=0ExitFunctionEndIfSetworksheet=workbook.Sheets(worksheetIdentifier)IfErr<>0ThenRenameWorksheet="BadWorksheetIdentifier"Err=0ExitEndworksheet.Name=sheetNameRenameWorksheet="OK"End*********************************************************************************************'函數(shù)說(shuō)明：刪除工作表;' (2)workbookIdentifier:屬于ExcelApp的工作薄名稱(chēng) (3)worksheetIdentifier:workbookIdentifier工作薄的工作表名稱(chēng);'返回結(jié)果： (2)刪除失敗，返回字符串：BadWorksheetIdentifier'調(diào)用方法: setret=RemoveWorksheet(ExcelApp,"Book1",'FunctionRemoveWorksheet(ExcelApp,workbookIdentifier,worksheetIdentifier)Dimworkbook'AsExcel.workbookDimworksheet'AsExcel.worksheetOnErrorResumeNextErr=Setworkbook=ExcelApp.Workbooks(workbookIdentifier)IfErr<>0ThenRemoveWorksheet="BadWorkbookIdentifier"ExitFunctionEndSetworksheet=workbook.Sheets(worksheetIdentifier)IfErr<>0ThenRemoveWorksheet="BadWorksheetIdentifier"ExitFunctionEnd''RemoveWorksheet="OK"End*********************************************************************************************'函數(shù)說(shuō)明：添加新的工作薄' (1)ExcelApp:Excel應(yīng)用程序名稱(chēng);'返回結(jié)果： (1)成功：工作表對(duì)象NewWorkbook '調(diào)用方法 setNewWorkbook='FunctionSetNewWorkbook=ExcelApp.Workbooks.Add()SetCreateNewWorkbook=NewWorkbookEnd*********************************************************************************************'函數(shù)說(shuō)明：打開(kāi)工作薄' (1)ExcelApp:Excel應(yīng)用程序名稱(chēng); ' (1)成功：工作表對(duì)象NewWorkbook '調(diào)用方法 setNewWorkbook=FunctionOpenWorkbook(ExcelApp,path)OnErrorResumeNextSetNewWorkbook=ExcelApp.Workbooks.Open(path)SetōpenWorkbook=NewWorkbookOnErrorGoTo0EndFunction*********************************************************************************************'函數(shù)說(shuō)明：將工作薄設(shè)置為當(dāng)前工作狀態(tài)' '返回結(jié)果：無(wú)返回值;'調(diào)用方法 'SubActivateWorkbook(ExcelApp,workbookIdentifier)OnErrorResumeNextOnErrorGoTo0End*********************************************************************************************'函數(shù)說(shuō)明：關(guān)閉Excel工作薄;' (1)ExcelApp:Excel應(yīng)用程序名稱(chēng); '調(diào)用方法 SubCloseWorkbook(ExcelApp,workbookIdentifier)OnErrorResumeNextOnErrorGoTo0End*********************************************************************************************'函數(shù)說(shuō)明：判斷兩個(gè)工作表對(duì)應(yīng)單元格內(nèi)容是否相等' (7)trimed:是否先除去字符串開(kāi)始的空格和尾部空格后再進(jìn)行比較,true或flase;'返回結(jié)果： '調(diào)用方法: ret=CompareSheets(excelSheet1,excelSheet2,