经验介绍－如何添加一行到到XLS文件的末尾

公司要使用Robot进行自动画测试，最近忙于编制通用的库函数。

下列是关于函数是对XLS文件的操作。这是我在自动化测试中形成REPORT的方法。共定义了10个字段

' FuncitonArea  -  Test CaseID  -  Test Title  - Report Time -Check Points    -Result - Expected Values- Test Value-  Break Point- LogFile

大家有兴趣可以查考参考。欢迎指教!

Global Const  XlsFieldNumber   As Integer = 10
Declare Function AppendOneLineIntoXlsFile(XlsFields() As String, XlsFileName As String) As Integer

Sub Main

    Dim Result, i As Integer
    Dim FieldNumber As Integer
    Dim XlsFileName As String
    Dim XlsFields(1 to XlsFieldNumber ) As String
   
   
    XlsFileName = "D:\TestReport.xls"
    For i = 1 to XlsFieldNumber
        XlsFields(i) = cstr(i)
    Next
    
    
    Result = AppendOneLineIntoXlsFile(XlsFields(),XlsFileName)
    
    
   
End Sub

Function AppendOneLineIntoXlsFile(XlsFields() As String, XlsFileName As String) As Integer

   
    'Open up Excel Appliation
    Dim objExcel As Object            ' Refer to MS EXCEL Application
    If objExcel Is Nothing Then
        Set ōbjExcel = CreateObject("Excel.Application")
        If objExcel is Nothing Then
            MsgBox "Cannot Run Excel Application"
            AppendOneLineIntoXlsFile = sqaFail
            Exit Function
        End If
    End If
   
    'Open the workbook File
    Dim objWorkBook As Object         ' Refer to MS EXCEL File
    Set ōbjWorkBook = objExcel.Workbooks.Open(XlsFileName)
    If objWorkBook Is Nothing Then
        MsgBox "Cannot find the file of " + XlsFileName
        AppendOneLineIntoXlsFile = sqaFail
        Exit Function
    End If
   
    'Set up a reference to the first sheet in the workbook
    Dim objWorkSheet As Object        ' Refer to MS EXCEL Sheet
    Dim SheetNumber As Variant        ' Refer to Sheet number of one Xls file
    SheetNumber = 1                    ' Here Only write data into the sheet-1
    Set ōbjWorkSheet = objWorkBook.WorkSheets(SheetNumber)
    If objWorkSheet Is Nothing Then
        MsgBox "Cannot Find WorkSheet"+ Cstr(SheetNumber)
        AppendOneLineIntoXlsFile = sqaFail
        Exit Function
    End If
   
    ' Get the lineLocation to append the data in XlsFileName
    Dim TmpString As String
    Dim j As Integer   ' i stands for row number, j stands for column number
    Dim LineLocation As Integer
    LineLocation = 1   ' Default value
    Do
        TmpString = ""
        For j = 1 to XlsFieldNumber
            TmpString = TmpString + Trim(objWorkSheet.Cells(LineLocation,j))
        Next
        If TmpString =  "" Then
            Exit Do
        Else
            LineLocation = LineLocation + 1
        End If
    Loop 
   
    'Append the XlsFields() Array data on i'th row 
     For j = 1 to XlsFieldNumber
        objWorkSheet.Cells(LineLocation,j) = XlsFields(j)
     Next
   
   'Save the changes
    objWorkBook.Save
  
    'Close the workbook
    objWorkBook.Close
    'Exit Excel
    objExcel.Quit
    'Clear all the references to the objects
    Set ōbjWorkBook = Nothing
    Set ōbjWorkSheet = Nothing
    Set ōbjExcel = Nothing

End Function