Reference › Test Case Reference › Data Set Descriptions › Read DTOs from Excel File Data Set

Read DTOs from Excel File Data Set

The Read DTOs from Excel File data set lets you parameterize Java data transfer objects (DTOs) in your test steps. The data set gives you an easy way, through Excel spreadsheets, to provide data values for those parameters.

The Read DTOs from Excel file data set assigns values to the properties of a DTO and stores the object in a property. This property can then be used whenever the DTO is required as a parameter. The data in the data set can be simple data types like numbers or strings, or complex data types such as DTOs, arrays, and collections. The data that is represented in Excel is converted into the proper data types automatically when needed. The only complex part of using this data set is the initial creation of the Excel spreadsheet. Fortunately, the creation is done for you. Given the package name of the DTO, a template is created, using one or more Excel sheets that represent the object. Data types such as primitives, strings, arrays of primitives and simple individual DTOs can be represented on a single sheet. More complex data types, such as arrays of objects, require more Excel sheets to represent the full DTO.

Often, a web service endpoint expects complex DTOs. The Excel data set simplifies creating objects to use as parameters to the web service. When the web service is first referenced, it is given a name and a URL for the WSDL. Java DTO classes in the form com.lisa.wsgen.SERVICENAME.OBJECTNAME are automatically generated and made available on the classpath. You can browse to that generated class in the DTO class browser, generate an Excel file, and simply fill in the template. See the following example.

Building the data set is a two-step process. First, let DevTest build the template in Excel. Then open the Excel spreadsheet and fill in the data fields in all the sheets that are produced.

To build the template:

1. Enter the following parameters in the Data Set Editor:

Name

The name of the data set. This name becomes the property that is used to store the current DTO object.

Local

Designates whether the data set is global or local. Global is the default. Local data sets are created with one for each simulator. Global data sets are created once and shared by all simulators.

Random

Whether the record after the current record (sequential access) is read, or a random record is read. Sequential reading is the default.

Max Records to Fetch

The upper bound on the number of records to fetch for random access. This text field is disabled if the Random check box is not selected.

At End Of Data

Select the action for the end of the data set. You can start over, reading values from the start of the data set, or you can select the step to execute.

File

The fully qualified path name, or browse to the Excel file using the browse pull-down menu.

DTO Class Name

The full package name, or browse to, the DTO object. The class file must be on Test Manager. Your class can be copied to the hotDeploy directory to put it on the Test Manager.

Advanced Settings let you specify:

Use flattened child property notation during generation

Select to override child property flattening during Excel DTO data set generation. If cleared, child properties are generated as references with their own worksheets.

Use new empty cell semantics for flattened properties

Empty cell semantics for flattened properties means how empty cells are interpreted in a DTO spreadsheet. For example, given the following flattened properties:

{ "prop1.subprop1", "prop1.subprop2" }

if both subprop1 and subprop2 have empty cell values, then under the new semantics the reference to "prop1" is set to null. Under the old semantics, prop1 would be not null, but references to subprop1 and subprop2 would both be null. The new semantics should be used by default, especially by web services with WSDLs that use nonnillable types. If not used in the case of nonnillable types, the intermediate not null references for containing properties that are automatically created when reading a DTO spreadsheet could result in the generation of invalid XML according to the schema (because cell values are empty).

1. Click the Generate Template button. DevTest builds the template and the system messages report when the file is built.
2. Click the Open XLS File button.

   The spreadsheet contains everything that is necessary to construct the object. We will show how to add data in the next section.

3. Close the XLS file.
4. Click the Test and Keep button to test and load the data. You see a window that confirms that the data set can be read, and shows the first set of data.

Building the Excel spreadsheet

To facilitate this explanation, we use an actual DTO object: com.itko.example.dto.Customer. This class is included with the DevTest examples and can be found by browsing the Test Manager using the Browse button.

The Customer DTO has the following properties:

The Address DTO has the following properties:

The first six Customer DTO properties can appear on one Excel spreadsheet. However, the locations property, an array of Address objects, requires a second Excel spreadsheet.

Looking at the first spreadsheet, at the top, DevTest lists the DTO spec (Customer) and the current DTO object (Customer). The spreadsheet would also list the Java doc location, if available. The following graphic shows the data sheet, with a row specifying property names, followed by a row specifying the data types. The first field (column) is not a DTO property, but a special field (Primary key), that holds a unique value for each row.

Looking at the data sheet we can see:

• Each row contains the data for a single Customer DTO.
• The poAddr property, of type Address, has been "flattened" and its properties are listed on this sheet. These properties are prefixed with poAddr and then the property name in the address object (that is, poAddr.city).
• The since property, of type date, is prefilled with the current date. This entry is to show you the required format for the date mm/dd/yyyy. All dates must have this format in the Excel template.
• The types property, of type int[], is a single cell that can contain the array elements as a comma-separated list. This list is only possible for arrays of primitives or strings.

The location property, of type Address[], does not appear on this sheet. Because it is an array of objects, the location property is on the second sheet in the Excel file. This sheet contains the data for an Address object in each row. There are wo special fields in this sheet: "Primary Key", and the "reference the containing DTO" field that is used to link the rows in this sheet to the rows in the primary sheet.

Because each Customer object can have several locations, several rows in the locations sheet belong to an object specified in a single row of the Customer sheet. The second sheet manifests this by listing the primary key of the parent Customer object in the "reference the containing DTO" field of each location that belongs to the Customer. This is similar to primary/foreign key relationships in databases.

When you save the spreadsheet and click Test and Keep in DevTest, you see the first Customer DTO in the message.

Depending on the complexity of your DTO, you could have several more Excel sheets in the Excel workbook. However, the process is the same as in the previous example.

To see how you could use this Customer DTO as a property, see the sections on testing Java objects in Test Steps in Using CA Application Test.