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.
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.
See Also
Working With Microsoft Excel Files
Using DB Table Variables to Retrieve Data From Excel Files
Working With Excel Files via COM