自动化EOM方法

yzqwqp 2013-06-18

展开全文

excel自动化在QTP测试过程当中是十分常见的，作为一名自动化测试工作者掌握其EOM自动化模型的用法是必不可少的，下面列举出一些常见的excel操作脚本。

怎样实例化一个excel对象？
如何关闭已经打开的excel对象？
如何保存工作簿？
怎样在单元格中设置给点值？
怎样根据行数以及sheet名称获取单元格的值？
怎样根据sheet标识来获取sheet对象？
如何在当前工作簿下创建一个新的sheet？
如何重命名一个sheet？
如何删除工作簿中的sheet？
如何使用excel对象创建一个新的excel工作簿？
如何打开之前保存的excel工作簿？
怎样在多个工作簿中选取一个作为当前工作簿？
如何关闭已经打开的工作簿？
如何比较两个sheet的内容？

脚本实例：

Dim ObjExcelApp 'As Excel.Application
Dim objExcelSheet 'As Excel.worksheet
Dim objExcelBook 'As Excel.workbook
Dim objFso 'As Scripting.FileSystemObject
 
' This function will return a new Excel Object with a default new Workbook
 
Function CreateExcel() 'As Excel.Application
   Dim objExcelSheet 'As Excel.worksheet
   Set ObjExcelApp = CreateObject("Excel.Application") 'Create a new excel Object
   ObjExcelApp.Workbooks.Add
   ObjExcelApp.Visible = True
   Set CreateExcel = ObjExcelApp
End Function
 
'This function will close the given Excel Object
 
'objExcelApp - an Excel application object to be closed
 
Sub CloseExcel(ObjExcelApp)
   Set objExcelSheet = ObjExcelApp.ActiveSheet
   Set objExcelBook = ObjExcelApp.ActiveWorkbook
   Set objFso = CreateObject("Scripting.FileSystemObject")
   On Error Resume Next
   objFso.CreateFolder "C:\Viplav"
   objFso.DeleteFile "C:\Viplav\ExcelExamples.xls"
   objExcelBook.SaveAs "C:\Viplav\ExcelExamples.xls"
   ObjExcelApp.Quit
   Set ObjExcelApp = Nothing
   Set objFso = Nothing
   Err = 0
   On Error GoTo 0
End Sub
 
'The SaveWorkbook method will save a workbook according to the workbookIdentifier
 
'The method will overwrite the previously saved file under the given path
'objExcelApp - a reference to the Excel Application
'workbookIdentifier - The name or number of the requested workbook
'path - the location to which the workbook should be saved
'Return "OK" on success and "Bad Workbook Identifier" on failure
 
Function SaveWorkbook(ObjExcelApp, workbookIdentifier, path) 'As String
   Dim workbook 'As Excel.workbook
   On Error Resume Next
   Set workbook = ObjExcelApp.Workbooks(workbookIdentifier)
   On Error GoTo 0
   If Not workbook Is Nothing Then
   If path = "" Or path = workbook.FullName Or path = workbook.Name Then
   workbook.Save
   Else
   Set objFso = CreateObject("Scripting.FileSystemObject")
   'if the path has no file extension then add the 'xls' extension
   If InStr(path, ".") = 0 Then
   path = path & ".xls"
   End If
   On Error Resume Next
   objFso.DeleteFile path
   Set objFso = Nothing
   Err = 0
   On Error GoTo 0
   workbook.SaveAs path
   End If
   SaveWorkbook = "OK"
   Else
   SaveWorkbook = "Bad Workbook Identifier"
   End If
End Function
 
'The SetCellValue method sets the given 'value' in the cell which is identified by
 
'its row column and parent Excel sheet
'objExcelSheet - the excel sheet that is the parent of the requested cell
'row - the cell's row in the objExcelSheet
'column - the cell's column in the objExcelSheet
'value - the value to be set in the cell
 
Sub SetCellValue(objExcelSheet, row, column, value)
   On Error Resume Next
   objExcelSheet.Cells(row, column) = value
   On Error GoTo 0
End Sub
 
'The GetCellValue returns the cell's value according to its row column and sheet
 
'objExcelSheet - the Excel Sheet in which the cell exists
'row - the cell's row
'column - the cell's column
'return 0 if the cell could not be found
 
Function GetCellValue(objExcelSheet, row, column)
   value = 0
   Err = 0
   On Error Resume Next
   tempValue = objExcelSheet.Cells(row, column)
    If Err = 0 Then
    value = tempValue
    Err = 0
   End If
    On Error GoTo 0
    GetCellValue = value
End Function
 
'The GetSheet method returns an Excel Sheet according to the sheetIdentifier
 
'ObjExcelApp - the Excel application which is the parent of the requested sheet
'sheetIdentifier - the name or the number of the requested Excel sheet
'return Nothing on failure
 
Function GetSheet(ObjExcelApp, sheetIdentifier) 'As Excel.worksheet
    On Error Resume Next
    Set GetSheet = ObjExcelApp.Worksheets.Item(sheetIdentifier)
    On Error GoTo 0
End Function
 
'The InsertNewWorksheet method inserts an new worksheet into the active workbook or
 
'the workbook identified by the workbookIdentifier, the new worksheet will get a default
'name if the sheetName parameter is empty, otherwise the sheet will have the sheetName
'as a name.
'Return - the new sheet as an Object
'ObjExcelApp - the excel application object into which the new worksheet should be added
'workbookIdentifier - an optional identifier of the worksheet into which the new worksheet should be added
'sheetName - the optional name of the new worksheet.
 
Function InsertNewWorksheet(ObjExcelApp, workbookIdentifier, sheetName) 'As Excel.worksheet
   Dim workbook 'As Excel.workbook
    Dim worksheet 'As Excel.worksheet
    'In case that the workbookIdentifier is empty we will work on the active workbook
    If workbookIdentifier = "" Then
    Set workbook = ObjExcelApp.ActiveWorkbook
    Else
    On Error Resume Next
    Err = 0
    Set workbook = ObjExcelApp.Workbooks(workbookIdentifier)
    If Err <> 0 Then
    Set InsertNewWorksheet = Nothing
    Err = 0
    Exit Function
    End If
    On Error GoTo 0
    End If
    sheetCount = workbook.Sheets.Count
    workbook.Sheets.Add , sheetCount
    Set worksheet = workbook.Sheets(sheetCount + 1)
    'In case that the sheetName is not empty set the new sheet's name to sheetName
    If sheetName <> "" Then
    worksheet.Name = sheetName
    End If
    Set InsertNewWorksheet = worksheet
End Function
 
'The RenameWorksheet method renames a worksheet's name
 
'ObjExcelApp - the excel application which is the worksheet's parent
'workbookIdentifier - the worksheet's parent workbook identifier
'worksheetIdentifier - the worksheet's identifier
'sheetName - the new name for the worksheet
 
Function RenameWorksheet(ObjExcelApp, workbookIdentifier, worksheetIdentifier, sheetName) 'As String
    Dim workbook 'As Excel.workbook
    Dim worksheet 'As Excel.worksheet
    On Error Resume Next
    Err = 0
    Set workbook = ObjExcelApp.Workbooks(workbookIdentifier)
    If Err <> 0 Then
    RenameWorksheet = "Bad Workbook Identifier"
    Err = 0
    Exit Function
    End If
    Set worksheet = workbook.Sheets(worksheetIdentifier)
    If Err <> 0 Then
    RenameWorksheet = "Bad Worksheet Identifier"
    Err = 0
    Exit Function
    End If
    worksheet.Name = sheetName
    RenameWorksheet = "OK"
End Function
 
'The RemoveWorksheet method removes a worksheet from a workbook
 
'ObjExcelApp - the excel application which is the worksheet's parent
'workbookIdentifier - the worksheet's parent workbook identifier
'worksheetIdentifier - the worksheet's identifier
Function RemoveWorksheet(ObjExcelApp, workbookIdentifier, worksheetIdentifier) 'As String
   Dim workbook 'As Excel.workbook
    Dim worksheet 'As Excel.worksheet
    On Error Resume Next
    Err = 0
    Set workbook = ObjExcelApp.Workbooks(workbookIdentifier)
    If Err <> 0 Then
    RemoveWorksheet = "Bad Workbook Identifier"
    Exit Function
    End If
    Set worksheet = workbook.Sheets(worksheetIdentifier)
    If Err <> 0 Then
    RemoveWorksheet = "Bad Worksheet Identifier"
    Exit Function
    End If
    worksheet.Delete
    RemoveWorksheet = "OK"
End Function
 
'The CreateNewWorkbook method creates a new workbook in the excel application
 
'ObjExcelApp - the Excel application to which an new Excel workbook will be added
Function CreateNewWorkbook(ObjExcelApp)
    Set NewWorkbook = ObjExcelApp.Workbooks.Add()
    Set CreateNewWorkbook = NewWorkbook
End Function
 
'The OpenWorkbook method opens a previously saved Excel workbook and adds it to the Application
 
'objExcelApp - the Excel Application the workbook will be added to
'path - the path of the workbook that will be opened
'return Nothing on failure
Function OpenWorkbook(ObjExcelApp, path)
    On Error Resume Next
    Set NewWorkbook = ObjExcelApp.Workbooks.Open(path)
    Set OpenWorkbook = NewWorkbook
    On Error GoTo 0
End Function
 
'The ActivateWorkbook method sets one of the workbooks in the application as Active workbook
 
'ObjExcelApp - the workbook's parent excel Application
'workbookIdentifier - the name or the number of the workbook
Sub ActivateWorkbook(ObjExcelApp, workbookIdentifier)
On Error Resume Next
ObjExcelApp.Workbooks(workbookIdentifier).Activate
On Error GoTo 0
End Sub
 
'The CloseWorkbook method closes an open workbook
 
'ObjExcelApp - the parent Excel application of the workbook
'workbookIdentifier - the name or the number of the workbook
Sub CloseWorkbook(ObjExcelApp, workbookIdentifier)
    On Error Resume Next
    ObjExcelApp.Workbooks(workbookIdentifier).Close
    On Error GoTo 0
End Sub
 
'The CompareSheets method compares between two sheets.
 
'if there is a difference between the two sheets then the value in the second sheet
'will be changed to red and contain the string:
'"Compare conflict - Value was 'Value2', Expected value is 'value2'"
'sheet1, sheet2 - the excel sheets to be compared
'startColumn - the column to start comparing in the two sheets
'numberOfColumns - the number of columns to be compared
'startRow - the row to start comparing in the two sheets
'numberOfRows - the number of rows to be compared
Function CompareSheets(sheet1, sheet2, startColumn, numberOfColumns, startRow, numberOfRows, trimed) 'As Boolean
   Dim returnVal 'As Boolean
   returnVal = True
    'In case that one of the sheets doesn't exists, don't continue the process
    If sheet1 Is Nothing Or sheet2 Is Nothing Then
    CompareSheets = False
    Exit Function
   End If
    'loop through the table and fill values into the two worksheets
   For r = startRow to (startRow + (numberOfRows - 1))
    For c = startColumn to (startColumn + (numberOfColumns - 1))
    Value1 = sheet1.Cells(r, c)
    Value2 = sheet2.Cells(r, c)
    'if 'trimed' equels True then used would like to ignore blank spaces
    If trimed Then
    Value1 = Trim(Value1)
    Value2 = Trim(Value2)
    End If
    'in case that the values of a cell are not equel in the two worksheets
    'create an indicator that the values are not equel and set return value
    'to False
    If Value1 <> Value2 Then
    Dim cell 'As Excel.Range
    sheet2.Cells(r, c) = "Compare conflict - Value was '" & Value2 & "', Expected value is '" & Value1 & "'."
    Set cell = sheet2.Cells(r, c)
    cell.Font.Color = vbRed
    returnVal = False
    End If
    Next
    Next
    CompareSheets = returnVal
End Function