Imprint Privacy policy Terms & Conditions Sitemap

Microsoft Excel

Data access

The ANALYZE adapter for Microsoft Excel provides access to the contents of Excel cells as traceable artifacts.

Configuration

Open the ANALYZE configuration with the ANALYZE configuration editor, and add a new data access as described in section "Data accesses". Select Microsoft Excel as data access type.

Within the configuration panel, you may specify file patterns consisting of Eclipse projects, folders or file name patterns describing Excel files relevant for analysis.

Supported keywords:

  • resource – A pattern for a project, folder or file in the workspace.

The configuration may contain several resource definitions.

Artifact type

The Excel adapter makes certain Excel cells available as artifacts. The recognition of such cells/artifacts depends on both cell location (file/sheet/address) and cell content, which must not be empty. Cells may be referenced by address or by value.

Configuration

Supported options:

  • addressOf – Starts an addressOf condition.
  • locate cell where – Optional regular expression for content or address of a cell
  • include cell if – Optional filter for matched cells
  • identified by – The unique key for an artifact in this document
  • address matches – Regular expression for cell address in a format like $A$1
  • content matches – Regular expression for cell content
  • name – Specifies the name for the recognized artifact.
  • sheet – Regular expression for the workbook sheet name.
  • valueOf – Starts a valueOf condition.
  • column with header – Refers to a given column of an Excel sheet. May be used within a valueOf expression and within an locate cell where expression.
    • For attribute mappings, the actual header is looked for „from bottom to top”, i.e., the search direction is upwards, starting at the row containing the actual artifact.
    • For cell location restrictions, the column headers are expected in row 1 unless an optional in row is configured, see example below.
  • map – Attribute mappings for custom attributes
  • sheetName – Bound to the sheet name of the artifact
  • Document.userProperty – Map value to extended properties of the artifact’s document.
  • Document.property – Map value to default properties of the artifact’s document.
  • ( … ) – In expressions like the name or a mapped attribute, parts can be grouped by braces and functions can be called on the result.

For reading the current selection of Microsoft Excel and opening an artifact, the Component Object Model (COM) API of Microsoft Windows is used. Further information on COM is available on the Microsoft website.

This adapter supports the XLS, XLSM, XLSX, XLT, XLTM and XLTX formats. It provides selection propagation from ANALYZE to Excel for all formats. However, selection propagation from Excel to ANALYZE is only possible for non-template files, i.e., XLS, XLSM, and XLSX.

Example:

Sample configuration:

sheet "Table_1" {
    locate cell where {
      address matches "\\$(N)\\$(\\d*)"  // Only cells in column N with any rownum
      column with header "TestCase" in row 3 // Only cells in column with Text "TestCase" in row 3
      content matches "(ID-.*)" // which contain only numbers prefixed with 'ID-'
    }
    include cell if (
      valueOf{+1;0} == "open"
      OR valueOf{+1;0} == "accepted"
    )
    name sheetName + ":" + valueOf {0;0}
    identified by sheetName+":" + addressOf {0;0}
    map {
      department to Document.userProperty("Department") //user property in the Excel file for department
      status to valueOf {+1;0} // custom attribute status gets the value of the cell on the right
      variant to valueOf {column with header("Variant");0} //custom attribute variant gets the value
                               //of the cell in the same row in the column with the header "Variant"
      author to Document.property(creator) //Predefined property for the creator of the Excel document
    }
  }

In the above example, the workbook sheet containing the artifacts must be named „Table_1” and the cell to link with is in column N. This column requires a header „TestCase” which is expected in row 3.

ANALYZE considers only those cells as artifacts that contain a text like ID-1000.

Please note: Although restrictions address matches and column with header are overlapping, it is possible to use them together in the same locate cell block.

Sheet name, cell address and content patterns are interpreted as regular expression, see section "Regular expressions". You can find a regular expressions example in the Microsoft Word adapter documentation.

All cells which are found by previous patterns are only included in the artifacts, if the status (which is at valueOf{+1;0}) is open or accepted.

The keywords name and identified by are used for presenting and identifying artifacts. In this example the artifact name might be Table_1:ID-1000, and the identifier to find the artifact could be Table_1:$N$12. You can copy and paste the example and adjust it matching your requirements.

Attribute and cell mappings

The custom attributes of artifacts can be mapped from cells relative to the artifact’s primary cell or extracted from the document properties. The example in the previous section the department is extracted from the document properties of the Excel file and the status is extracted from the cell right from the primary cell. The syntax supports two variants. The attribute value is referenced either by cell offsets or by column headers. If the resolved cell address is part of a merged cell, the value of the merged cell is used.

Example for offsets:

" valueOf( column offset ; row offset )"

Negative offsets are allowed. The attribute value is taken from row row of artifact + row offset, column column of artifact + column offset.

Example for column header:

" valueOf( column with header( " Header " ); row offset )"

ANALYZE scans from top to bottom and from left to right to look for cells with „Header” as their contents. If a cell matches, the attribute value is taken from the associated column in row row of artifact + row offset.

To use the cell address instead of the contents, the addressOf keyword supports the same offsets, but is evaluated to the address of the cell. The document properties can be accessed by Document.userProperty(" Name ") for custom properties and by Document.property( creator or title ) for the default document properties.

If your configuration tries to access a cell „preceding” the first cell or row, the value of such an non-existent cell is replaced by an information message like „Illegal cell $B$2-3”.

Value references

In the above example, the artifact named Table_1:ID-1000 is identified by the cell reference Table_1:$N$12. As a consequence, the name of the artifact will change if the text in that particular cell changes. This implies a risk of „crooked” links:

Assuming we have an Excel sheet like this:

If we delete row 2 in Excel, all trace links, e.g., from/to Table_1:ID-1001 will be „bended” to link from/to Table_1:ID-1004.

This risk can obviated by using value references instead of absolute cell references. In this case, ANALYZE stores ID-1001 as reference and searches the area specified in the address matches section of the configuration for that value. Hence, the trace link stays valid.

In order to have ANALYZE use references, change the configuration from

resource *risks.xls {
  …
    identified by sheetName + ":" + addressOf {0;0}
  …
  }
}

to

resource *risks.xls {
  …
    identified by sheetName + ":" + valueOf {0;0}
  …
  }
}

Caveat: If you change the configuration from addressOf to valueOf there is a risk to corrupt existing links. Please consult the support before making such adjustments!

Version

An artifact’s version is used for suspicious links validation. The version of an artifact of this type is evaluated as a JSON-like concatenation of all artifact custom attribute values.

With the Excel link type, it is possible to store references from Excel artifacts to other artifacts within Excel sheets. This is achieved by populating a cell „close” to the cell representing the artifact with the ID(s) of the referenced artifact(s). The meaning of „close” is expressed in the ANALYZE configuration for the particular Excel link type. If the cardinality of the link is larger than one, the IDs of the referenced artifacts are joined in the cell.

ANALYZE supports not only to store the link by means of storing the ID, ANALYZE also supports synchronization of attributes from the linked artifact into the Excel sheet.

Let’s say test cases in Excel are linked to requirements, which, by the way, might reside in a totally different data source, e.g., PTC Integrity. You can configure ANALYZE such that on link creation it does not only store the requirement ID in cell A, but can also copy the requirement description into another cell. This may support the person responsible for the test during specification of the test case.

Using the Microsoft Excel link type requires at least one link type mapping with a configured Microsoft Excel data access. At least one of the artifact types selected for the link type must be a configured Microsoft Excel artifact type.

Open the ANALYZE configuration with the ANALYZE configuration editor, and add a new link type as described in section "Configuring a link type".

  • As at least one of A and B, select a Microsoft Excel artifact type with Microsoft Excel as its adapter. If you want to derive links from and to the same artifact type, choose this type both as A and as B.
  • As data access, select an Excel data access, which should have been configured in a prior step.

Supported keywords are :

  • link where … in … – Defines which condition must be fulfilled to derive trace links from cells in Excel. Left from in, a custom attribute of the target linked artifact type must be provided that identifies artifacts of this type within Excel. Right from in, a position within Excel must be provided via the valueOf keyword. This position must refer to an Excel artifact type.
  • map – Defines a list of mappings (see below).
  • … based on … to – Defines a mapping of an attribute of B or A (left from to) to a cell in Excel (right from to), i.e., the attribute value will be stored in the specified cell when creating or updating the link. Left from based on, a custom string attribute of the configured Excel link type must be specified. When reading a link from Excel, this custom attribute will be set to the value read from the specified cell. When creating or updating a link, the custom attribute will be set to the value of the specified custom attribute of an artifact.
  • valueOf – Allows to specify the position of a cell within an Excel sheet, see Microsoft Excel artifact type.

When reading or writing links of the configured Excel link type, ANALYZE will generally only consider the same Excel sheet in which the Excel artifact was found that is referred to by the in clause. All relative positions specified with valueOf are relative to the cell of this artifact, i.e., they assume {0;0} to be the position of this cell. Creation and update of links is not possible, if any of the cells required for the link are part of merged cells.

In general, each entry of map has the form: connectorAttr based on B. artifactAttr to A.valueOf{ xPos; yPos} . The expression connectorAttr is the name of a custom attribute defined for the link type, while the expression artifactAttr is the name of a custom attribute defined for the artifact type of B. The expression A.valueOf{ xPos; yPos} defines the position to store the attribute in, as described above.

The Excel link type supports linking from one Excel artifact to zero, one or more artifacts, the type of which is set in the link configuration:

  • If there is no link, the cell holding the link information is empty.
  • In case of one link, the cell contains the ID of the referenced artifact as specified in the link type configuration.
  • In case of more than one link, the IDs are concatenated, separated by the pipe symbol ( |). If the pipe symbol is used within an ID, it must be encoded as \|, i.e., there must be preceeded by a backslash. A backslash within an ID must be encoded as \\. ANALYZE automatically performs these conversions when storing links, but users who manually change these Excel cells need to make sure that the encoding is as described above.

If attributes are mapped, the same mechanism holds true for attributes. The number of items in the cell holding the IDs and the cells holding attributes must match, i.e., the n th attribute is associated with the n th link.

If in this case the links in Excel are edited manually (i.e., not by ANALYZE), the user must take care of making consistent changes: If he deletes the n th link in the cell holding the ID, he must also delete the n th attribute in each cell holding attributes. 

link where B.IDX in A.valueOf{column with header("Requirement ID");0}
map {
    module based on B.ModuleName to A.valueOf{column with header ("Module");0}
    variant based on B.variant to A.valueOf{+4;0}
}

The sample configuration above describes a link type mapping from artifacts of the Excel artifact type (artifact A) to requirement (artifact B). The ID of artifact type B is configured in the custom attribute B.IDX, and artifact type B has the custom attributes B.ModuleName and B.variant.

The configuration tells ANALYZE that the IDs of the linked artifacts should be written into or read from the cell at the following position: "column labeled „Requirement ID”; same row as ID of Artifact A". ANALYZE splits the string contained in the cell by using the separator | and removes any whitespace surrounding each of the resulting parts. ANALYZE derives a link whenever one of the resulting parts is equivalent to B.IDX (i.e., the ID of the requirement).

The definition of the position has the form A.valueOf{ xPos; yPos} . The syntax is the same as for the Excel artifact type.

Via the keyword map, the configuration tells ANALYZE to store certain custom attributes of artifact B in the Excel file and to map these values to certain custom attributes of the link type. In the example, it will derive the value of the custom attribute variant of the configured Excel link type from the custom attribute variant of B and the link attribute module from B's custom attribute ModuleName. ANALYZE stores all attributes for the link in the row of the Excel sheet containing A. The column to store the value of B.ModuleName in is defined to be the column having the header „Module”. The second entry defines a similar mapping for another variable, but this time the column for storing the attribute is defined relative to the column of A: It is stored four columns to the right of A.

In the above Excel sheet, two links have been created by means of ANALYZE:

  • From test case 1 to requirement A13, which comes from module A in variant 3
  • From test case 1 to requirement A16, which comes from module G in variant 7

Suspicious links validation is done by re-evaluating the link’s custom attributes and comparing these values to those stored in the link. If there are any differences, the link is suspicious.

IBM DOORS Next Microsoft Visual Studio