To read data stored in Microsoft Office Excel files in scripts, you can use the special DDTDriver object that lets you easily access data stored in an Excel sheet. To obtain the driver object in script, use the ExcelDriver method of the DDT object.

Requirements

To access data in Excel files, you must have Microsoft Office 12.0 Access Database Engine OLE DB Provider installed on your computer. Make sure the “bitness” of this data provider (32- or 64-bit) is the same the TestComplete “bitness”, otherwise, you will get the “The needed data source driver is not installed...” error message. To learn more, see Microsoft Office Excel Data Connectivity.

Working with DDTDriver object

The DDTDriver object simplifies retrieving data from Excel files and iterating through data rows in your scripts. It represents Excel data in the form of a table, where the number of its columns and rows is specified by the number of columns and rows of the Excel file. Using properties and methods of the DDTDriver object, you can get the number of columns and rows in the Excel file, obtain the value of the specified cell, etc.

The code below illustrates the use of DDT.ExcelDriver. This code creates a DDT driver for an Excel sheet, runs through records of the driver’s table and posts values stored in record fields to the test log:

JavaScript, JScript

var RecNo;

// Posts data to the log (helper routine)
function ProcessData()
{
  var Fldr, i;

  Fldr = Log.CreateFolder("Record: " + aqConvert.VarToStr(RecNo));
  Log.PushLogFolder(Fldr);

  for(i = 0; i < DDT.CurrentDriver.ColumnCount; i++)
    Log.Message(DDT.CurrentDriver.ColumnName(i) + ": " + aqConvert.VarToStr(DDT.CurrentDriver.Value(i)));

  Log.PopLogFolder();
  RecNo = RecNo + 1;
}

// Creates the driver (main routine)
function TestDriver()
{
  var Driver;

  // Creates the driver
  // If you connect to an Excel 2007 sheet, use the following method call:
  // Driver = DDT.ExcelDriver("C:\\MyFile.xlsx", "Sheet1", true);
  Driver = DDT.ExcelDriver("C:\\MyFile.xls", "Sheet1");

  // Iterates through records
  RecNo = 0;
  while (! Driver.EOF() )
  {
    ProcessData(); // Processes data
    Driver.Next(); // Goes to the next record
  }

  // Closing the driver
  DDT.CloseDriver(Driver.Name);
}

Python

RecNo = 0
  
# Posts data to the log (helper routine)
def ProcessData():
  global RecNo
  Fldr = Log.CreateFolder("Record: " + aqConvert.VarToStr(RecNo))
  Log.PushLogFolder(Fldr)
  
  for i in range(DDT.CurrentDriver.ColumnCount):
    Log.Message(DDT.CurrentDriver.ColumnName[i] + ": " + aqConvert.VarToStr(DDT.CurrentDriver.Value[i]))
  
  Log.PopLogFolder()
  RecNo = RecNo + 1

# Creates the driver (main routine)
def TestDriver():

  # Creates the driver
  # If you connect to an Excel 2007 sheet, use the following method call:
  # Driver = DDT.ExcelDriver("C:\\MyFile.xlsx", "Sheet1", True)
  Driver = DDT.ExcelDriver("C:\\MyFile.xls", "Sheet1")
  
  global RecNo
  # Iterates through records
  RecNo = 0
  while not Driver.EOF():
    ProcessData() # Processes data
    Driver.Next() # Goes to the next record

  # Closing the driver
  DDT.CloseDriver(Driver.Name)

VBScript

Dim RecNo

' Posts data to the log (helper routine)
Sub ProcessData
  Dim Fldr, i

  Fldr = Log.CreateFolder("Record: " + aqConvert.VarToStr(RecNo))
  Log.PushLogFolder Fldr

  For i = 0 To DDT.CurrentDriver.ColumnCount - 1
    Log.Message DDT.CurrentDriver.ColumnName(i) + ": " + aqConvert.VarToStr(DDT.CurrentDriver.Value(i))
  Next

  Log.PopLogFolder
  RecNo = RecNo + 1
End Sub

' Creates the driver (main routine)
Sub TestDriver
  Dim Driver

  ' Creates the driver
  ' If you connect to an Excel 2007 sheet, use the following method call:
  ' Set Driver = DDT.ExcelDriver("C:\MyFile.xlsx", "Sheet1", True)
  Set Driver = DDT.ExcelDriver("C:\MyFile.xls", "Sheet1")

  ' Iterates through records
  RecNo = 0
  While Not Driver.EOF
    Call ProcessData() ' Processes data
    Call Driver.Next() ' Goes to the next record
  WEnd

  ' Closing the driver
  Call DDT.CloseDriver(Driver.Name)
End Sub

DelphiScript

var RecNo;


// Posts data to the log (helper routine)
procedure ProcessData;
var
  Fldr, i : OleVariant;
begin
  Fldr := Log.CreateFolder('Record: ' + aqConvert.VarToStr(RecNo));
  Log.PushLogFolder(Fldr);

  for i := 0 to DDT.CurrentDriver.ColumnCount - 1 do
    Log.Message(DDT.CurrentDriver.ColumnName[i] + ': ' + aqConvert.VarToStr(DDT.CurrentDriver.Value[i]));

  Log.PopLogFolder;
  RecNo := RecNo + 1;
end;

  // Creates the driver (main routine)
procedure TestDriver;
var
  Driver : OleVariant;
begin
  // Creates the driver
  // If you connect to an Excel 2007 sheet, use the following method call:
  // Driver := DDT.ExcelDriver('C:\MyFile.xlsx', 'Sheet1', true);
  Driver := DDT.ExcelDriver('C:\MyFile.xls', 'Sheet1');

  // Iterates through records
  RecNo := 0;
  while not Driver.EOF do
  begin
    ProcessData; // Processes data
    Driver.Next; // Goes to the next record
  end;

  // Closing the driver
  DDT.CloseDriver(Driver.Name);
end;

C++Script, C#Script

var RecNo;

// Posts data to the log (helper routine)
function ProcessData()
{
  var Fldr, i;

  Fldr = Log["CreateFolder"]("Record: " + aqConvert.VarToStr(RecNo));
  Log["PushLogFolder"](Fldr);

  for(i = 0; i < DDT["CurrentDriver"]["ColumnCount"]; i++)
    Log.Message(DDT["CurrentDriver"]["ColumnName"](i) + ": " + aqConvert.VarToStr(DDT["CurrentDriver"]["Value"](i)));

  Log["PopLogFolder"]();
  RecNo = RecNo + 1;
}

// Creates the driver (main routine)
function TestDriver()
{
  var Driver;

  // Creates the driver
  // If you connect to an Excel 2007 sheet, use the following method call:
  // Driver = DDT["ExcelDriver"]("C:\\MyFile.xlsx", "Sheet1", true);
  Driver = DDT["ExcelDriver"]("C:\\MyFile.xls", "Sheet1");

  // Iterates through records
  RecNo = 0;
  while (! Driver["EOF"]() )
  {
    ProcessData(); // Processes data
    Driver["Next"](); // Goes to the next record
  }

  // Closing the driver
  DDT["CloseDriver"](Driver["Name"]);
}

Note: There are some specifics of using the DDTDriver scripting object to read data from Excel worksheets. For more information about this, see Using Excel Files as Data Storages.

You can also use the DDTDriver object in keyword tests. In this case, to call the DDT.ExcelDriver method and properties and methods of the returned object, use the Run Code Snippet and Call Object Method operations. However, the best way to access Excel data from keyword tests is to use DB Table variables. For more information about this, see Using DB Table Variables to Retrieve Data From Excel Files.

Using the DDTDriver Object to Retrieve Data From Excel Files

Requirements

Working with DDTDriver object

See Also