Creating configuration files for exporting Excel test artifacts

When you export an Microsoft Excel test artifact, you must have a configuration file to define how to map the XML file to a Engineering Test Management artifact.

About this task

Configuration files contain statements that associate Engineering Test Management artifact information with data locations. Each statement in a configuration file has two sides that are linked with an equal sign. One side of the statement consists of keywords. The other side equals sign of a configuration can consist of three possibilities.

A column or cell definition i.e. D or D4
A literal string i.e. "execution"
A reference to another artifact (self-reference) i.e. testscript.description

To define a row or cell, simply put the column identifier or cell identifier:

testscript.dc:title=G

To define a literal string, simply wrap it with quotes:

testscript.steps.type="execution"

To define a self reference, simply put in the information from one side of the equals from a previously defined line:

testscript.dc:title=G
testcase.dc:title=testscript.dc:title

Note: Self-references are used to create artifacts that will use data already being used by another artifact and will create a link to that artifact. Self-references cannot be used with the XLSLink keyword.

Keywords and special cases: There are several keywords that go on the one side of the equals sign. They all start with "XLS" and take the place of a field after the artifact. They are defined as:

XLSStartRow defines which row the artifact data starts after.
XLSDelimeter defines the element that denotes the next artifact (currently only \n is supported).
XLSLink specifies that the artifact be linked to all artifacts of the specified type within the worksheet.
XLSLinkReverse directs the export utility to put the link XML into the artifact, rather than the one making the link. Use this keyword only with self referential links. Use this keyword when you create an artifact based on another (for example, requirement.dc:title=testcase.dc:title), but you require that the link be created in the artifact that is the original (the one to the one of the sides of equal sign). Specifying requirement.XLSLinkReverse=true puts the link in the main artifact, rather than the one specifying the self-referential link.
XLSArtifactID specifies the external ID of the artifact. If the external ID of the artifact is not specified, a default external ID is assigned using this format:
```
Excel file name_Excel worksheet name[_artifact_count].xml
```
where artifact_count is the number of artifacts in a single spreadsheet if the spreadsheet contains two or more artifacts. If the spreadsheet contains one artifact, this argument is not required.
Important: If the spreadsheet contains an artifact with the same external ID as another artifact, the artifact is updated from the spreadsheet contents. For example, if you export the same Excel spreadsheet with the same configuration several times, the external ID is updated each time.

CAUTION:
If you are using artifact.XLSArtifactID="urn:com.ibm.rqm:testplan:2" when the value is specified as a string (not a cell), make sure there is no blank sheet, and that artifact.XLSWorksheetIDis not used to specify the worksheet to read the XLSArtifactID.
To create new artifacts when you export the same Excel files to the same repository, complete one of these steps:
- Use the XLSArtifactID keyword to specify a unique external ID for each export operation.
- Change the Excel file name or the Excel worksheet name each time you export.
- Use the Export to file option when you start the export process, and change the name of XML files, and then import the XML files into Engineering Test Management : Log into Engineering Test Management, and click one of these commands:
  - Planning > Import Test Plans
  - Construction > Import Test Cases
  - Construction > Import Test Scripts
  Tip: You can use the preceding method to import test plans, test cases, and test scripts only.
XLSWorksheetID specifies the name of the worksheet to process. You must use literal string for this keyword.
Note: From version 4.0, the syntax for testsuite linked with testcases has changed from
```
testsuite.testcases=testcase
```
to
```
testsuite.XLSLink=testcase
```
Here are some examples:
- testscript.XLSStartRow=7
- testscript.title=B
- testscript.steps.type="execution"
- testscript.steps.description=B
- testscript.steps.title=B
- testscript.steps.name=B
- testscript.steps.expectedResult=F
- testscript.XLSDelimeter=\n
- testcase.dc:title=C1
- testcase.dc:description=C2
- testcase.XLSLink=testscript
- testcase.XLSArtifactID=D1
- testcase.XLSWorksheetID="test_case_1"
The script steps, categories, sections, image exports, dynamic searches for a word or phrase, and spreadsheet properties access are special cases. A script step requires several fields for each step. Because a script can have many steps, all lines are considered a single step until a blank line is encountered, which starts the next step.
Sections are special cases. To simplify their use and to keep their definitions from interfering with the period notation that is otherwise used. To update a section, use this format:
```
testcase.Section("myns:com.ibm.rqm.planning.editor.section.testCaseAcceptanceCriteria", "RQM-KEY-TC-ACCEPT-CRITERIA-TITLE")=C
```
This example configuration entry populates the acceptance criteria section that is built into the Engineering Test Management application.

Procedure

To determine the name or display name of predefined sections:
1. Update the predefined section of a resource in Engineering Test Management.
2. Read the resource as XML with abbreviate=false.
3. Locate the element in the XML document with the section name, for example, com.ibm.rqm.planning.editor.section.testCaseAcceptanceCriteria.
4. Locate the extensionDisplayName attribute in the element that contains the section display name, for example RQM-KEY-TC-ACCEPT-CRITERIA-TITLE.
To define a custom section, replace the first parameter with a valid name, and the second parameter with a display name:
```
testcase.Section("myns:mysection","my custom section")=C
```
To define a category, use the formatcategory term=category_name.value=category_value:
```
testcase.category term="Function".value=H
```
To add a custom attribute to one artifact:
if we have two customAttributes, testcaseCustomAttribute1 and testcaseCustomAttribute1 in one testcase, the customAttributes testcaseCustomAttribute1 and testcaseCustomAttribute2 should be created in Engineering Test Management before running the Engineering Test Management Excel Importer.
We can configure the .cfg file as follows:

testcase.customAttributes.type="SMALL_STRING" //set the custom attribute type (the data type).

testcase.customAttributes.identifier=A //put an identifier into the A column in Excel. The identifier is the name of the attribute when you create it. For example, when you create the customerAttribute with the name "name1" and change it to "name2" later, the identifier remains "name1" on the Engineering Test Management server. If you set the custom attribute identifier as Custom_attribute_number_, and the first attribute is Custom_attribute_0_ and the second is Custom_attribute_1_ , this identifier will be correct when you put it into the Excel cell.

testcase.customAttributes.name=B //put attribute name in B column
testcase.customAttributes.value=C //put attribute value in C columnthe result will be:
```
<customAttributes>
     <customAttribute type="SMALL_STRING">
          <identifier>Custom_attribute_0_</identifier>
          <name>testcaseCustomAttribute1</name>
          <value>111111111111111111111111111</value>
     </customAttribute>
     <customAttribute type="SMALL_STRING">
          <identifier>Custom_attribute_1_</identifier>
          <name>testcaseCustomAttribute2</name>
          <value>3333333333333333333333333</value>
     </customAttribute>
</customAttributes>
```
A new sample TestCases 001 with custom attributes.xls and TestCases 001 with custom attributes.cfg have been added.
To access the properties of the spreadsheet that you are working on, using the following lines:
- requirement.dc:title=property(“Title”)
- requirement.dc:creator=property(“Author”)
- requirement.jzalm:owner=property(“Company”)
You can find the available properties that are built into Microsoft Office at http://msdn.microsoft.com/en-us/library/microsoft.office.core.documentproperty.aspx.
Three other properties are also available: “Name,” “Full Name,” and “Location.” If the property has not been set or if Excel does not support the property, the property (“property name”) returns the empty string: “”.

The related sample .xls and .cfg files are called Requirements001 using document properties.
To export images from Excel, understand these aspects of images in spreadsheets:
The location of images in Excel is represented by the cell that contains the one end of the image. Even if an image takes up multiple cells, you must specify the cell that contains the one part of the image to export the image. Cells can contain an unlimited number of images. If you specify the cell that contains the one part of all the images, all the images will be exported.
In Excel, you can export images in two ways. The first method is to use the image function. The image is set up as a function to put after the equal sign in a configuration file entry. Images can be specified by the cell or by the row. The image function inserts an image into the rich-text field that it is assigned to. If the attribute does not use a rich-text field, the image function will be ignored. See these examples of the image function:
- testscript.steps.expectedResult=image(G)
- testscript.steps.description=image(C4)
The second method is to use the attachment attribute:
- testcase.attachment=C4
- testcase.attachment=C
The attachment attribute adds images as attachments to the artifact rather than inserting the image into rich-text fields.
The related sample .xls and .cfg files are respectively called Test Cases made from scripts with imagesand Test Cases made from scripts with attachments.