In this recipe, we will consider creation of a function, which allows reading values out from a single cell in an Excel file with the help of the DDT capabilities.
In order to retrieve a value from a specific cell:
- It is necessary to first of all, get your bearings straight with function declaration. We will need to specify the name of the file, name of the spreadsheet, the number of the line and that of the column:
function readCell(file, sheet, row, col)
- The body of the function will be to the following effect:
var data = DDT.ExcelDriver(file, sheet); var currentRow = 1; var value; while(!data.EOF()) { if(currentRow == row) { value = data.Value(col - 1); DDT.CloseDriver(data.Name); return value; } currentRow++; data.Next(); } Log.Error("Row #" + row + " not found"); DDT.CloseDriver(data.Name); - Retrieving the value from the cell in the script will look as follows:
Log.Message(readCell("C:\readcell.xls", "Sheet1", 4, 4));
This example will get the word Tester outputted to the log (the contents of the D5 cell).
The only method to retrieve the value from the targeted line via the DDT is looping through all the previous lines. To this end, we make use of the while loop and the currentRow variable (the number of the current line has to be calculated independently, as TestComplete does not extend such a possibility).
The Value property allows retrieval of the value from an assigned column (by referring to its heading or a column number).
As soon as we reach the targeted line if(currentRow == row), we store the value from the current line and the specified column and then quit the loop.
Pay attention to the use of the Next and CloseDriver methods:
- The
Nextmethod places the file pointer to the next line. If, at the end of the loop, we forget to evoke this method, the loop will carry on endlessly in the first line of the data file. - The
CloseDrivermethod closes the driver, averting overflow above the maximally allowed number of the opened drivers (the maximal number thereof can stand at 64) and releases the file to be possible to access by other applications.
Also, pay attention to the fact we have started lines numbering from one (var currentRow = 1), thus, we pre-suppose that numbers of the columns also 1-based (data.Value(
col - 1
)): because in TestComplete columns are 0-based. We have permuted this behavior, as indexing to start with one seems to be more logical.