Customize export templates for Microsoft Excel

Excel export templates enable you to set up different export scenarios for users of the Excel round-trip features. Templates specify which Work Item fields are exported for round-trip, and the default type for new Work Items created via Excel round trip.

Several export templates used for exporting Work Items to Microsoft Excel format are provided in the xlsx: Microsoft Excel section of the Export Templates topic of Administration.

(AdministrationWork ItemsExport Templates xlsx: Microsoft Excel.)

The repository folder is /.polarion/tracker/export_templates/excel.

Best practice for creating your own Excel export templates is to derive new ones from Polarion's default export templates.

The following sections discuss the characteristics of an Excel export template, and describe several common customizations.

The default export templates

Polarion provides three default Excel export templates:

Basic.xlsx

Template with some basic column-to-field mapping. A good starting point for many customizations.

Empty.xslx

Contains the required tables (describe later), but nothing else. No columns are defined in the template sheet, and there is no default column-to-field mapping. If used for round-trip export, the export dialog fields selection is prefilled with all columns that are shown in the Table view of Work Items.

Tip:

This template is best used as the basis for new templates not derived from or based on any other.

TimeReport.xlsx

This template defines a work time report. It serves as a good example of how to define calculated columns in your custom Excel export templates.

Start by downloading the Excel export templates and checking to see how much is already implemented that you can use. The Empty.xlsx template is the most basic template with minimal data. The Basic.xlsx template has some typically used fields and columns predefined. The Time Report.xlsx template has some examples of calculated columns. Create a copy of one of these export templates and customize it to suite your needs.

Export template structure

Work Items are exported to a named region of the worksheet, called an Excel table, named WorkItems. Your template must contain this table, which should contain only a header and one normal row — the template row (see figure below). In Polarion's default export templates, this table is found in Sheet 1. Any column in this template sheet that has the a column label corresponding to a Polarion Work Item field ID is automatically mapped to the corresponding field. This includes custom fields. You can add columns as needed. If you need to have column names that do not correspond to field IDs, use the mapping table on the Polarion sheet.

The default Excel export templates also contain a hidden sheet named Polarion. This sheet specifies some settings for the exporter and enables column to field mapping when template column names do not correspond to Polarion Work Item field names. Your custom template should contain this sheet with the two tables from Polarion's default templates. You will need to manually show this hidden sheet in the workbook if you need to make changes to it, and then change it back to the hidden state before distributing your custom template.

The first table in the Polarion sheet is a table of property-value pairs that enable you to set default values for two properties:

New Work Item Type

Specify the type for any new Work Items that may be specified by end users in exported round-trip workbooks based on the template, and created in Polarion during re-import.

New Comments Column

A regular expression value that matches headers of columns that should be imported as new Work Item comments.

The second table on the Polarion sheet is a column-to-field mapping table. Use this table to map any columns in the template table of Sheet 1 whose column names do not correspond to Polarion field names. Each row corresponds to a column in Sheet 1 that doesn't have a column name matching a field ID. If there are rows in this table that duplicate the mapping on Sheet 1, then you don't need to remove them. The mapping will not be duplicated.

When adding a new column and field row to the mapping table, be sure to use the Insert context menu on a row in the mapping table. Do not add rows outside the table. Rows inside the table will be shaded.

If you should happen to specify a field that doesn't exist, it will not appear to users in the Export Work Items dialog box when they select the Excel option when exporting Work Items.

The end result of your customized template, when uploaded to the portal, should be that all fields you have mapped in the template appear to users as the default fields in the Selected Columns list of the Export Work Items dialog. It's recommended that you save your templates with different file names from the default templates. You can upload your finished templates to the Export Templates page (described earlier) of Administration, and then test them by invoking Export in the Table view of Work Items, and selecting xlsx: Microsoft Excel in the Format field. If you need to modify your template for any reason, you can do so and upload it again using the Replace existing export template with this upload option.

Template row	Non-existent fields in map do not appear in export dialog.
Column-to-field mapping table	Expert Work Items dialog

Add a column

Open the sheet of the export template containing the WorkItems region (normally on Sheet1).
On first row after the header row (the template row referred to earlier), drag the lower right corner of the right-most cell until you see the outline for a new column, or use the content menu of the right-most cell to insert a column to the right.
Set the value of the new column's header to the name of a Work Item field to which you want the column mapped.
Alternatively, set the header value to any unique value, and then on the Polarion sheet add a row to the mapping table.

Add a calculated column

Create a new column as described in the previous section, but do not map it to any field. Rather, specify a unique value in the column header.
Enter the desired formula in the template row cell of the new calculated column.
Tip:
You cannot use relative cell references. Rather you should use table column references. For example: =[Time Spent]+[Remaining Est.]-[Initial Est.]
The calculated columns in the default Time Report.xlsx export template can provide a useful example of such references.

Make a column read-only

Open the sheet of the export template containing the WorkItems region (normally found on Sheet1).
In the first row after the header row (the template row referred to earlier), select the cell you want to be read only.
Select the Protection page of the dialog box and select Locked.

Specify column for Test Step iteration numbers

Excel export for offline testing of Test Cases 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.