Coloring cell of runtime datatable

[indranilgoswamimcb]

I am updating the test case status in the runtime datatable as "Pass" or "fail".The entire test case excel sheet had been imported to the run time datatable.

After the execution is over the runtime datatable would simply be exported and saved as the test execution excel sheet. My query is ,I need to color the particular cell of the "pass" as light green (background color) and the "fail" status as "red"(background color again). How do I do that inside the run time data table so that the same can be exported and saved as the test execution result sheet.

[sshukla12]

Hi,
First export the datatable to excel sheet and then using Excel object u can change the color of any cell.

Let me know in case of any help required.

Regards,
Sankalp

[indranilgoswamimcb]

Hi,

The project requirement is that while exporting to excel sheet the date and time should also be appended.Hence,it is very difficult to identify the excel sheet generated as it is unique everytime.

Is there any way to color the cell in runtime datatable itself?

Regards,
Indranil.

[SteveS]

Hi Indranil,

It is not possible within QTP itself although I do something very similar including appending date and time to the excel sheet. When you perform the export write the full path and filename to an environment variable and at the end of the test call a function similar to below to update the formatting passing the environment variable as the file:

Code:

Function FormatExcel(cFile)
Dim xl, wb, ws, iRow
'
set xl=CreateObject("excel.application")
set wb=xl.workbooks.open (cFile)
set ws=wb.worksheets(1) ' Assume global sheet is first
xl.DisplayAlerts = False ' Supress any messages from excel

iRow = Datatable.GetRowCount                         ' Use the count of the current datasheet
For x = 1 to iRow + 1                                 ' +1 to skip the header row
    If ws.Cells(x, 2).Value = "Pass" Then         ' 2 is the column number with pass, fail
        ws.Cells(x, 2).Style = "Good"                ' Use built in excel styles for the colour
    ElseIf ws.Cells(x, 2).Value = "Fail" Then
        ws.Cells(x, 2).Style = "Bad"
    ElseIf ws.Cells(x, 2).Value = "Warning" Then
        ws.Cells(x, 2).Style = "Input"
    End If
Next

' Save and close the file
wb.SaveAs cFile, True
wb.close

set ws=nothing
set wb=nothing
set xl=nothing
'
End Function