Exporting Database Tables to Excel Sheets Using TestComplete

One of our users asked if it is possible to create a TestComplete test that would retrieve data from a database table and store it to an Excel worksheet. The answer is yes, you can automate this task by writing script code. For users that have programming experience this does not seem to be hard. For those who have no development background, however, things are not as trivial because the solution requires not just knowing TestComplete scripting objects, but also knowing the Excel COM interface as well as methods and properties of ADO objects.

The idea is that you use TestComplete’s built-in program objects to connect to the desired database and Excel sheet and then work with the database and Excel via COM. The entire JScript code for this approach looks like this:

// TestComplete JScript code

 
function Main()

{

  var ConnectionString = "DRIVER=SQL Server;SERVER=H_kptest;UID=aaa;PWD=aaa";

  var TableName = "ListOfOrders";

  var ExcelFilePath = "C:\Book1.xls";

  var SheetName = "Sheet1";

  ExportTableToExcelFile(ConnectionString, TableName, ExcelFilePath, SheetName);

}

function ExportTableToExcelFile(ConnectionString, TableName, ExcelFilePath, SheetName)

{

  // Creates a new connection

  var ConnDB = ADO.CreateConnection();

  ConnDB.ConnectionString = ConnectionString;

  ConnDB.Open();


  // Opens a recordset

  var Tbl = ConnDB.Execute("SELECT * FROM " + TableName);

  

  // Opens an Excel file

  var MsExcel = Sys.OleObject("Excel.Application");

  MsExcel.Workbooks.Open(ExcelFilePath);

  var Sheet = MsExcel.Sheets(SheetName);

  MsExcel.Visible = true;

  

  // Copies field names

  for (var i = 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();

  var RowIndex = 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++;

}

  

  // Closes the Excel file

  MsExcel.Save();

  MsExcel.Workbooks.Close();


  // Closes the recordset and the connection

  Tbl.Close();

  ConnDB.Close();

}

Let’s go through it step-by-step.

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

// Creates a new connection
var ConnDB = 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’s ADO.CreateConnection method, 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
var Tbl = 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:

var ExcelFilePath = "C:\Book1.xls";
var SheetName = "Sheet1";

 
// Opens an Excel file
var MsExcel = Sys.OleObject("Excel.Application");

MsExcel.Workbooks.Open(ExcelFilePath);
var Sheet = 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 (var i = 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();
var RowIndex = 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 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.


Close

By submitting this form, you agree to our
Terms of Use and Privacy Policy

Thanks for Subscribing

Keep an eye on your inbox for more great content.

Continue Reading

Add a little SmartBear to your life

Stay on top of your Software game with the latest developer tips, best practices and news, delivered straight to your inbox