excel在QTP中实现自动化

'@Description Create an ExcelPath
Public Function CreateExcel(ExcelPath)
       ' TODO: add function body here
  Dim xlApp
  Dim xlWorkbook
  Dim xlWorkSheet
  Set xlApp = CreateObject("Excel.Application")
  sSourceFile = ExcelPath
  Set fso = CreateObject("Scripting.FileSystemObject")
'   判断该文件是否存在
bFileExists = fso.FileExists(sSourceFile)
If bFileExists Then
' 如果存在则打开
Set xlWorkbook = xlApp.Workbooks.open(sSourceFile)
else
' 不存在则创建文件
Set xlWorkbook = xlApp.Workbooks.add
Set xlWorkSheet = xlWorkBook.activesheet
xlWorkBook.saveas ExcelPath
End If
SystemUtil.CloseProcessByName("EXCEL.EXE")
End Function




Public Function GetCellsValue(ExcelPath, SheetName, SheetRow, SheetColumn)
       ' TODO: add function body here
  Dim ExcelBook
  Dim ExcelSheet
  Dim CellsValue
  Set ExcelBook = CreateObject("Excel.Application")
  Set ExcelSheet = CreateObject("Excel.Sheet")
  Set myExcelBook = ExcelBook.WorkBooks.Open(ExcelPath)
  Set myExcelSheet = myExcelBook.WorkSheets(SheetName)
  CellsValue = myExcelSheet.cells(SheetRow,SheetColumn).value
  GetCellsValue = CellsValue
  ExcelBook.Quit
  Set ExcelSheet = Nothing
  Set ExcelBook = Nothing
End Function




Public Function WriteCellsValue(ExcelPath, SheetName, SheetRow, SheetColumn, CellsValue)
       ' TODO: add function body here
  Set ExcelBook = CreateObject("Excel.Application")
  Set ExcelSheet = CreateObject("Excel.Sheet")
  Set myExcelbook = ExcelBook.WorkBooks.Open(ExcelPath)
  Set myExcelSheet = myExcelBook.WorkSheets(SheetName)
  myExcelSheet.cells(SheetRow,SheetColumn).value = CellsValue
  If ucase(myExcelSheet.cells(SheetRow,SheetColumn).value) = "PASS" Then
  myExcelSheet.cells(SheetRow,SheetColumn).font.colorindex = 50
  myExcelSheet.cells(SheetRow,SheetColumn).font.bold = true
elseif ucase(myExcelSheet.cells(SheetRow,SheetColumn).value) = "FAIL" then
    myExcelSheet.cells(SheetRow,SheetColumn).font.colorindex = 3
  myExcelSheet.cells(SheetRow,SheetColumn).font.bold = true  
else
myExcelSheet.cells(SheetRow,SheetColumn).font.colorindex = 0
  End If
  myExcelBook.save
  ExcelBook.Quit
  Set ExcelSheet = Nothing
  Set ExcelBook = nothing
End Function




Public Function GetExcelSheetRowCount(ExcelPath, SheetName)
       ' TODO: add function body here
  Set ExcelBook = CreateObject("Excel.Application")
  Set ExcelSheet = CreateObject("Excel.Sheet")
  Set myExcelBook = ExcelBook.WorkBooks.Open(ExcelPath)
  Set myExcelSheet = myExcelBook.WorkSheets(SheetName)
  GetExcelSheetRowCount = myExcelSheet.UsedRange.Rows.Count
  ExcelBook.Quit
  Set ExcelSheet = Nothing
  Set ExcelBook = nothing
End Function