Using Excel Round-trip

When you export any set of Work Items using the xlsx: Microsoft Excel option, the output is written to a special "round-trip" format for Microsoft Excel. An external user may optionally edit Work Item content in Excel and/or create new Work Items for import into Polarion. A Polarion user with the necessary permissions can then import the edited Excel workbook back to Polarion, updating the Work Item content from the externally edited Excel worksheet, and/or creating new Work Items defined externally in the exported Excel workbook.

If the Work Items were exported from a Document with the necessary export options, it is also possible for an external user to change the Document's structure externally in Excel. The structural changes are reflected in the online Document when the Excel worksheet changes are re-imported to Polarion.

Excel export may be run from any scope: repository, project group, or project. Re-importing of an Excel workbook which contains modified Work Items can also be done in any scope. However, re-importing of Excel workbooks which contain new Work Items is only allowed in the project scope.

Exported Work Item fields which are writable in the portal may be optionally locked in the Export Work Items dialog during the export procedure so that users of the exported Excel file cannot easily change the field value. If an advanced Excel user does manage to change the value of a locked field in Excel, the change is ignored when the Excel file is imported back into Polarion. (Note that fields which are read-only in the portal display the "locked" lock icon in the dialog, and are not "unlocked" when clicked.)

Note that images are not currently supported in the Description field of Work Items modified or created in an exported Excel round-trip worksheet. If images are included, they will be lost when the workbook is re-imported into Polarion. A warning is displayed and the user performing re-import may opt to cancel the operation.

Also, any advanced text formatting and lists (bullet, etc.) implemented in the exported Excel document are lost upon re-import to Polarion.

For procedures, see:

Excel Round-trip Templates

Formatting of the Excel Round-trip output is controlled by an export template. Polarion provides, and by default uses, an Excel template named Basic.xlsx. Additional templates are provided in the original Polarion installation and are available in the Template field of the Export Work Items dialog when exporting for Excel Round-trip. An administrator can download any export template and modify it locally to create a customized version of the original template, and/or additional templates. When uploaded to the portal, the template names appear in the Template field where users exporting Work Items can select which one they wish to use for the round-trip export to Excel.

An export template may also specify which columns should be locked in the exported Excel file when an export operation uses the export template. This both saves time for end users, and provides control to prevent external or otherwise unauthorized users from inappropriately changing exported data, while permitting them to view it. Even if a user of an exported template-based Excel file manages to change data in a locked column, the change is ignored on re-import to Polarion.

Excel export for offline Test Case execution supports export of Test Cases with multiple Iterations of Test Steps. By default, the export template is configured to append a label for each Iteration to the Title column in the exported workbook. It is possible to configure which field displays the Iteration number in the property Append Iteration Label to Field in the hidden Polarion sheet. The default is the "title" field.

For more information, see Administrator's Guide: Configuring Work Items: Customizing Export Templates.

The default templates can contain the same expandable variables as used in PDF or Word export, as well as one for a query (in cells and header/footer). For information on these variables, see Administrators Guide: Configuring Documents and Pages: Configuring PDF Export.

Comments in Excel Round-trip

Native Excel comments present in cells are imported to Polarion as new Work Item comments. The Polarion tab of the Excel Round-trip export template contains the property New Comments Column which contains a format for the Comments column header. The default title for imported comments is the value of the header of the comments column.

The worksheet user can create new comments in the exported worksheet by adding a column having a header Comments by [USER'S NAME] (where [USER'S NAME] is the commenter's name), and then filling in comment text in the appropriate row. Example: Comments by John Doe

When the worksheet is re-imported to Polarion, the "Comments by" cells will produce new comments in the Work Items. The title of imported comments contains the text of comment column header (in the imported worksheet) plus the name of the comment author. For example: Comments by [USER NAME].

If Work Items containing Document comments are exported for Excel Round-trip, and the Work Item Description field is allowed by the exporting user to be editable in the exported Excel workbook, a placeholder {comment:id} is inserted into description text in the exported Excel workbook. This preserves the comment marker for the round-trip process and should not be removed from the exported Excel document.

Defining New Work Items

The user of an exported Excel workbook can define a new Work Item by adding a new row in the table of exported Work Items on Worksheet 1. The user should leave the ID column blank. The Excel Round-trip importer creates IDs for newly defined Work Items during the round-trip import process.

To insert a row into the Work Items table in the Excel round-trip document, select a cell in the table, right click on the row number and select Insert. A new row is inserted before the current row. Note that in a newly exported Excel document, one empty row is present. This row is locked, and is present only so the user can insert new rows above it for new Work Items. It remains as the last row in the table, and its presence does not affect anything in the re-import to Polarion process. If you encounter a table with no empty first row present (perhaps exported from a Polarion version where the exporter did not create an empty row), you can add a first row, but it will not be editable, as just described. Simply add another row above it and proceed to define a new Work Item.

Creation of new Work Items via Excel Round-trip import is only allowed in the project scope (i.e. not in project group or repository scopes). Be sure you have the correct project open when you import Excel workbook in which new Work Items are defined.

If the Work Items in an exported Excel workbook are contained in a Document, and if the Work Item Type field is included in the export, then only the Work Item types defined in the source Document's Configure Presentation Dialog can be created in the Excel sheet. The drop-down list for the Work Item Type column lists only these types, and the Excel user cannot manually enter any other type. If the type is not set in the export template, then the default Work Item type for the current project is created on import, and values for this type are shown in the drop-down lists. For information on the Work Item presentation configuration, see User Guide: Editing a Document: Work Items in Documents: Multiple Work Item Types.

Workflow configuration for projects often includes some initial workflow action which is executed for newly created Work Items. For example, a value may be set for the Status field. If no initial action is specified for the Work Item type in the project workflow, then the user-specified values for all editable fields will be set in Polarion when the Excel workbook is re-imported into Polarion. However, if an initial workflow action is configured, then any field values set by the initial workflow action will override user-specified values set in the Excel sheet. For example, suppose the Excel user creates a new Work Item in Excel, and sets the Status field to "Accepted". If the project's workflow configuration has an initial action which sets the value of the Status to "Draft" for new Work Items of the new item's type, then the workflow action's setting overrides the Excel user's setting, and the Status field will be set to "Draft" after the re-import operation.

Exporting for Excel Round-trip

For a general overview of exporting Work Items, please review User Guide: Work Items Topic: Exporting Work Items.

To export Work Items for Excel Round-trip:

  1. Follow the procedure described in Exporting Work Items Basic Export Procedure

  2. In the Format field, select xlsx: Microsoft Excel

  3. In the Template field, choose the template Polarion should use to format the data in the exported Excel workbook.

  4. Optionally change the items in the Selected Columns list.

  5. Optionally lock non-read only columns in the exported Excel worksheet by clicking the lock icon next to the column name in the Selected Columns list.

Unlockable Fields

Table 6.1. The following fields are unlockable:

Read-OnlyNot SupportedUnsupported Custom Field 'Types'
AuthorApprovalsEnum
CreatedAssignee(s)Feature Selection
DocumentAttachmentsTest Steps
Has Suspect LinksCategoriespvSCL
IDComments 
Outline NumberExternally Linked Work Items 
Planned EndHyperlinks 
Planned InLinked Data Resources 
Planned StartLinked Revisions 
ProjectLinked Work Items 
Repository, ProjectPlanning Constraints 
UpdatedStatus 
 Type 
 Work Records 

Notes and Tips

  • If the Work Items being exported to Excel are contained in a Document, the Export Work Items dialog provides the advanced option Allow Document structure changes in Excel. If checked, then an external user will be able to modify the structure of the online Document in Excel.

    After export, all columns will be editable in the exported Excel workbook regardless of their locked/unlocked status in the export dialog. All externally made structural changes will be reflected in the source Document when the exported and modified Excel workbook is re-imported to Polarion. The only exception is Work Item fields that are always marked as locked (i.e. read-only) by Polarion in the export dialog... Linked Work Items, for example. Such fields, when exported with the option to allow structural changes, will not be locked in the exported workbook, and can be changed by the user. However, any change to these fields will be ignored by the importer when the workbook is re-imported to Polarion.

    Structure in the exported workbook is shown by indentation. The first column is left-aligned to that indentation is visible.

  • You can optionally view the export template before finishing the export by clicking on the Show template link. You are then prompted to download and save the template file, or download and open it in Microsoft Excel.

  • Columns used in the template for some calculation cannot be removed from the Selected Columns list because they must be present in the exported file in order for the calculation(s) defined in the template to work. Such columns appear in Italic font.

  • In addition to the Add and Remove buttons, columns can be added to or removed from the list boxes by double-clicking a column name in either list.

Re-importing an Exported Excel Document

An exported Excel workbook that has been modified can subsequently be re-imported to Polarion. All the Work Items modified in the document are updated with the changes from the document, a new entry is logged in history, and a workitem_updated notification event is triggered for changed Work Items causing the notifications to be sent as defined in the notifications configuration.

If new Work Items were defined in the Excel workbook, they are created in Polarion by the importer. The following points are useful to note:

  • If Auto-assignment is configured for the project, the new items are automatically assigned and notifications are sent to the assignee(s).

  • It is possible to import the Status field when importing new items. If that field is empty upon import, then the following rules apply:

An Excel workbook can only be imported into the Polarion project from which it was originally exported. If imported to a different project, the import fails with a message, and a log file, viewable in the browser, is generated which contains details. It is possible to export Work Items from the project group scope, but such export is "one way"... Excel workbooks exported from a project group and then modified cannot be re-imported.

To re-import a modified Excel workbook:

  1. Open the project from which the Excel workbook was exported, and open the Table view of the Work Items topic in Navigation.

  2. In the Table view, click Operations > Import to launch the Import Work Items dialog.

  3. In the Format field, select xlsx: Microsoft Excel.

  4. In the File field, click Browse and select the file you want to re-import.

    If you want to overwrite any conflicting information in the portal with content from the Excel workbook, check Overwrite Conflicts.

  5. Click Start to upload the local file and launch the import process.

If the import succeeds, the Import Work Items displays the message Import finished. If the import fails, the message Import failed appears. In both cases the Show Log link is present. Click it to display a log of the import process in a new browser instance. The log reports any conflicts recognized during import and what was done with them.