Diiferent ways accessing data from an excel file

There are mainly 3 ways that we can access data from an excel file. Before using the below methods please create file calles Sample.xls in C:\ Drive

1. Data table
2. Using Excel Application COM methods
3. Using Database Connection methods.

1. Data Table :
Data Table is the quickest way to access data from any excel file and there are couple of methods provided by QTP. I am listing few of the methods.
1. datatable.import
2. datatable.importsheet
3. datatable.addsheet
4. datatable.getSheet
QTP has comprehensive help on the above topic

2. Excel Application COM methods
The second of accessing data from an excel file is by creating a Excel instance and with the help of that we can access the data of an excel.
Creating a excel file and sheet
Set xlObj= CreateObject("Excel.Application")
Set xlBook = xlObj.Workbooks.Add
set xlSheet = xlBook.worksheets.Add
xlObj.worksheets(4).name = "SampleSheet"
xlBook.SaveAs("C:\sample.xls")
set xlObj = nothing
set xlBook = nothing
set xlSheet = nothing

3. Excel application with Database connection
Const varStatic = 3
Const varLock = 3
Const varCmdText = &H0001

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\sample.xls; Extended Properties=""Excel 8.0;HDR=Yes;"";"

objRecordset.Open "Select * FROM [Sheet1$]", objConnection, varStatic, varLock, varCmdText

Do Until objRecordset.EOF
print objRecordset.Fields.Item("ColumnName1")

objRecordset.MoveNext
Loop

Set objConnection = nothing
Set objRecordSet = nothing

Creating custom dll file and using the same in QTP

Hi

Here I am discussing how to create a custom dll files using C#.

Pre requisites: Install Visual studio 2005 or 2008

1. Create Class library file


2. Create a class and their methods


3. Make the class as COM visible


4. Save it and build the file


5. Register the DLL with Registry using RegASM


6. Use the DLL in the QTP code.

Let's see the explanation of each step

1. Create Class library file :

Goto File->Add->New Project and select Class library file
Enter the Library File name
I am giving a class library name as "Framework"

2.Create a class and their methods
Write the code as below in the Library file

public class Utilitiess
{
public int i;
public int add(int a, int b)
{
return a+b;
}
public int subtract(int a, int b)
{
return a - b;
}
}

3. Make the class as COM visible
3.1 Right click on the Class Libray name.
3.2 In our case Right click on "Framework"
3.3 Click on Applications tab and Clcik on Assembly Information button
3.4 Select making com visible check box
3.5 Clck Save all button
4. Build the project
5. Registering the DLL with Registry
5.1 The DLL file would be stored in the Visual Studio Debug folder
5.2 Copy the DLL files to .NET framework tool kit folder
5.3 Open SDK Command prompt by selecting Start->Programs->Microsfot .net Framework
5.4 Entet the command RegAsm
6.Using the DLL in QTP
6.1 Use the following code to use the code in the code
set obj = createobject("Framework.Utilities")
i = obj.Add(2,3)
print i