Use testcomplete to export data from database tables to excel

Refer:

Exporting database tables to Excel sheets using testcomplete

Http://www.automatedqa.com/blogs/post/11-01-25/exporting-database-tables-to-excel-sheets-using-testcomplete/

 

First of all, we connect to the desired database using the ADO functionality:

// Creates a new connection
VaRConndb = ADO. createconnection ();
Conndb. connectionstring = "driver = SQL Server; server = h_kptest; uid = aaa; Pwd = aaa ";
Conndb. open ();

We create an ADO connection object by calling testcomplete'sADO.CreateConnectionMethod, then form a string with connection attributes and open the connection. If you have problems with specifying a connection string for your database, please visit www.connectionstrings.com.

The connection object which we obtained is an ADO Data Access Object registered in the operating system. We use its execute method to retrieve data from the database:

Tablename = "listoforders ";
// Opens a recordset
VaRTBL = conndb. Execute ("select * from" + tablename );

After we get data from the database, we connect to excel via COM. For this purpose we will use testcomplete's property-Sys.OleObject. This property is parameterized with the program ID of the desired COM server. After we connect to the Excel com engine, we use Excel COM objects and methods to open the desired Worksheet:

VaRExcelfilepath = "C: \ book1.xls ";
VaRSheetname = "sheet1 ";
 
// Opens an Excel file
VaRMSExcel = SYS. oleobject ("Excel. application ");
MSExcel. workbooks. Open (excelfilepath );
VaRSheet = MSExcel. Sheets (sheetname );
MSExcel. Visible = true;

Then, we use both ADO and Excel COM objects to copy desired column names and data from the database table to the Work Sheet:

// Copies Field Names
For(VaRI = 0; I <TBL. Fields. Count; I ++)
Sheet. cells (1, I + 1). value = TBL. Fields. Item (I). Name;

// Scans all records returned by the query
TBL. movefirst ();
VaRRowindex = 2;
While(! TBL. EOF)
{
For(I = 0; I <TBL. Fields. Count; I ++)
Sheet. cells (rowindex, I + 1). value = TBL. Fields. Item (I). value;
TBL. movenext ();
Rowindex ++;
}

At the end, we save the changes to the Excel worksheet and close the connection to the database:

// Closes the Excel file
MSExcel. Save ();
MSExcel. workbooks. Close ();
 
// Closes the recordset and the connection
TBL. Close ();
Conndb. Close ();

For more information on working with databases and Excel files from your tests, see testcomplete on-line help: Working with databases and working with Microsoft Excel files