The article shows how to read Microsoft Excel file that has test parameters such as min, max values and to modify the program tests requirements dynamically.
The example reads a Microsoft Excel file and store the new parameters in the program test objects. Each row in the Excel file contains the test id (test to read the parameters to) , test min, test max and additional extra parameter that is loaded to the ATest.Tag property (access from the test using test.tag).
Using the Example
The code to read the Excel file and apply the parameters resides in the ExcelTestParameters.drv driver and it uses the ATEasy® EXCEL driver. To use the driver:
1. Insert the ExcelTestParameters.drv to your system
2. Insert the Excel.drv to your system
3. To load the parameters to your program use the following code:
if not EXCELTESTS ReadTestParametersFile("C:\\ExcelTestParameters.xlsx", "Sheet1")
abort
endif
this code typically can reside in Program.OnInit event.
Example Code
The ExcelTestParameters.drv export one command / procedure that is used to load the parameters:
Procedure ReadExcelTestParametersFile(sFile, sSheet): Bool
--------------------------------------------------------------------------------
sFile: Val String
sSheet: [Val] String = "Sheet1"
xlwb: Excel.Workbook
xls: Excel.Worksheet
vrA: Variant
vrB: Variant
vrC: Variant
vrD: Variant
iRow: Long
sRow: String
{
EXCEL Initialize()
! open excel file
try
EXCEL File Open Existing(, sFile, xlwb)
catch else
endtry
if not xlwb
MsgBox("Unable to open Excel Workbook File: '"+sFile+"'")
EXCEL Quit()
return False
endif
! verify the worksheet exist and get the object
try
xls=xlwb.Sheets.Item(sSheet)
catch else
MsgBox("Bad Excel Worksheet: '"+sSheet+"' found in File: '"+sFile+"'")
EXCEL File Close(xlwb, False)
EXCEL Quit()
return False
endtry
! read row B to empty row
! each row has columns: 1=testid, 2=min, 3=max, 4=parameters
for iRow=2 to 9999
sRow=str(iRow)
EXCEL Get Value(xlwb, sSheet, "A"+sRow, vrA)
if vrA=""
exitloop ! last test
endif
EXCEL Get Value(xlwb, sSheet, "B"+sRow, vrB)
EXCEL Get Value(xlwb, sSheet, "C"+sRow, vrC)
EXCEL Get Value(xlwb, sSheet, "D"+sRow, vrD)
try
Program.Tests(vrA).Min=vrB
Program.Tests(vrA).Max=vrC
Program.Tests(vrA).Tag=vrD ! extra parameters
catch else
MsgBox("Bad Test ID: '"+vrA+"' found in Row: '"+sRow+"', in File: '"+sFile+"', Sheet: '"+sSheet+"'")
EXCEL File Close(xlwb, False)
EXCEL Quit()
return False
endtry
next
EXCEL File Close(xlwb, False)
EXCEL Quit()
return True
}
Running the Example
Running the example will open the ATEasy Test Executive with the new test parameters as specified in the spreadsheet:
How to Improve the Example
1. Use the program name as the sheet name; every sheet in the workbook can be used for one program.
2. Add support for other test type. Once the test is located the routine can check the test type and load parameters accordingly.
3. You can use the testid also for sequencing (similar to the profile driver)
Example Files
Download ATEasy ExeclTestParametrs project files