QTP Script to convert content of excel into a html document

Suppose I have some data in excel file and I want to create a html table from the excel file content. Below code can create a html table based on the content of an excel file.We can understand how to create a html file using scripting.filesystemObject in QTP. 

Example of how to call the function:

func_createHTMLTableFromExcel "E:/test.xlsx","Sheet1","e:/ht.htm"

Code for the function:

Public Function func_createHTMLFromExcel(strDataFile,strWorksheetName,strHTMLFile)

Set objXlHandle = Createobject("Excel.Application")

 objXlHandle.visible = false

Set objExcelWB = objXlHandle.Workbooks.Open(strDataFile)

Set objExcelWS = objExcelWB.Worksheets(strWorksheetName)

'Getting column and row count 

 strColumnCount = objExcelWS.UsedRange.Columns.Count

 strTotRows = objExcelWS.UsedRange.Rows.Count

strTable = "<table border=""""2"""">"

'Creating a html table based on content of the excel file

For j=1 to strTotRows

strTable = strTable & "<tr>"

For i=1 to strColumnCount

 strData = Trim(objExcelWS.Cells(j,i))

 strTable= strTable & "<td>"& strData &"</td>"

Next

strTable =strTable & "</tr>"

Next

strTable = strTable & "</table>"

set objFSO=Createobject("scripting.FileSystemObject")

set objtxt = objFSO.createTextFile(strHTMLFile)

    objtxt.write(strTable)

'Closing the workbook

 objExcelWB.Close

 set objFSO =nothing

End Function

How to add Attachments in QC during test script execution in QTP

Using below code, we can add attachment to QC during test script execution:

''This function will add the required attachment in QC, for Location defined by argument strLocationinQC

Function func_AddAttachmentToQC(FilePath,strLocationinQC)

On Error Resume next

If (ucase(strLocationinQC) = "TEST") Then

Set objCurrentTest = QCUtil.CurrentTest.Attachments

Set objAttach = objCurrentTest.AddItem(Null)

ElseIf(ucase(strLocationinQC) = "TESTSET") Then

Set objCurrentTest = QCUtil.CurrentTestSet.Attachments

Set objAttach = objCurrentTest.AddItem(Null)

ElseIf(ucase(strLocationinQC) = "TESTRUN") Then

Set objCurrentTest = QCUtil.CurrentRun.Attachments

Set objAttach = objCurrentTest.AddItem(Null)

End If

objAttach.FileName = strFilePath

objAttach.Type = 1

objAttach.Post

objAttach.Refresh

End Function

How to create a Hybrid Framework in QTP

A hybrid Framework is a framework that uses features of multiple frameworks and is more robust. For e.g, we can create data in external files as in data-driven approach, or creation of keywords as in keyword driven framework. 

Before understanding what needs to be a part of hybrid framework, it is necessary to know what are essential requirements of a framework, Below are the points that needs to be considered before creating a framework.

1. Identify where Test Artifacts needs to be maintained 

Test Artifacts can be maintained in test management tools like Quality Center or can be stored in a shared repository. Using a test management tool, For e.g HP ALM provides following features 

•  Version controlling of test scripts, function libraries, Object repository, recovery scenario and test data

•  Multiple Users can at the same time can access the resources in tool.

• Option to run  set of tests and defining the execution flow can be done using test management tool.

In case of test management tool not available, Test Artifacts should be maintained in a shared folder, so that multiple user can have access to the  resources. In this case, we will require a driver script that will execute multiple test script in a batch and also can be scheduled.

2. Object Identification

Objects Identification is very important and can be identified in number of ways. Some of them are as follows

• Using Object Repository

• Using Descriptive Programming

• Using HTML dom.

• Using XPath/CSS

A hybrid approach can use a mix of various object identification techniques and can maintain object of different types in different vbs files which can be executed during test initialization.

3. Error Handling and Recovery Scenario

For expected errors, error handling should be implemented in the code and recovery scenario can be used to avoid interruption in test script execution.Code should have enough error handling defined.

4. Reporting

For each failed/passed step in test script execution, reporting in QTP results or user defined html results should be done. Reporting should not be direct using reporter.reportevent but should be wrapped in a function with arguments defined for taking screenshot or exiting the test based on flag status of the argument.

5. Test Data Management

Test data is an important factor in hybrid framework design. Preparation of test data should take into considerations factors like:

• What are the validations required for test data preparation in fields

• Can any random data be entered in the fields.

• How much data is required. e.g bulk data required to execute scripts for multiple iterations.

• What will be the source of data. Data can be stored on an excel file or can be imported from database at run time if specific existing data is required.

• In case of large data requirement, test data generation tools like GenerateData can be used.

6. Libraries Management

For each test script execution, An initialization library should be called at the start of script execution which should load the required environment variables, object repositories and other function libraries with application specific code.

Similarly before exit from test script execution, function to close all process that were called during script execution should be executed. Also Code should have enough error handling and reporting, minimum hard code data, and should follow standing coding practices