Exporting WorkBench Tables to Excel

WorkBench provides powerful facilities for constructing tabular or matrix views of information. These views can contain items that are directly or indirectly linked to other items. Any of these views can be exported to RTF files (for Open Office, StarOffice or Microsoft Word ®), to HTML files (for web browsers or Microsoft Excel ®) or to comma-separated value (CSV) files for virtually any application, including Excel.

Showing linked items in a view is very common, for compliancy matrices, Requirements Traceability Matrices (RTMs), Performance Verification Matrices (PVMs) and a wide variety of standard systems engineering products.

Users often wish to generate these views from Cradle and then load them into Excel so that further sorting or other processing can be performed.

Opening in Excel

There are two options to export a WorkBench view and open it in Excel:

1. Export the table as a HTML file, start Excel and open the HTML file. Excel will parse the HTML and opens it as a spreadsheet with the HTML table rows and columns held inside separate spreadsheet rows and columns, and with some of the HTML table’s formatting reproduced in the spreadsheet’s cells.
2. Export the table as a CSV file, and either start Excel and open the CSV file or double-click the CSV file to open it in Excel. Each record in the CSV file becomes a row in the spreadsheet and each fields in these records becomes a separate column. No formatting of the cells occurs, as the CSV file only contains data.

Nested Tables

When a view shows items that are linked to other items (such as system requirements linked to user requirements), the linked items are shown in nested rows, for example:

If you export this table, the nested rows are preserved. For example, exporting to HTML and opening it in a web browser produces:

and opening the HTML file in Excel also preserves the nested rows:

There are no issues with any of these representations, unless you then want to sort the data by any part of the linked items. In the example, if you want to sort the data by the linked items’ Identity (the fourth column), Excel reports an error:

because there are merged (sometimes called spanned) cells in the table. That is, every cell in the fourth column does not have a corresponding cell in the first, second or third columns because these columns span all of the linked items.

To avoid this problem, the contents of the spanned columns must be repeated for every instance of the linked items. To do this, select the Disable cell spanning checkbox in the WorkBench Export Table dialogue before exporting the table:

Now when the data is opened, the first columns are repeated for each of the linked items:

and when the table is opened in Excel, you will be able to sort by any of the columns:

This principle works equally irrespective of how many levels of linked items are shown in the table. That is, our example here shows one group of linked items in which the linking is over just two levels. However, cell spanning can remain enabled, or be disabled, irrespective of the number of levels of cross referenced items that are shown, and irrespective of how many groups of linked items are being shown in the table at the same time (for example, a table that shows the system requirements linked to each user requirement and which also shows the verifications that are linked to the same user requirements in a separate group of columns).

Excel Issue with HTML Paragraphs

In general, Excel faithfully reproduces the contents of HTML tables, but there is one situation that can cause problems if you want to sort the resulting spreadsheet by part of the linked items. The problem arises if the attributes contain several paragraphs, particularly if blank lines separate these paragraphs.

To illustrate this situation, system requirement 1.4.3 has been reworded into two paragraphs:

When this is exported to HTML (with cell spanning disabled) and opened in the web browser, the nested items are shown correctly:

But when this same HTML file is opened in Excel, Excel creates one row for each paragraph in the linked items, see rows 3, 4 and 5:

Because Excel has created these separate rows for each paragraph in the attribute and these rows are spanned for all other columns, the data cannot be sorted by the linked items.

This is not a Cradle problem, as the HTML table is correct (shown by the web browser), but is instead an issue with Excel.

The way to avoid this problem is to export the table from WorkBench as a CSV file. When this is opened in Excel, the data is correctly represented and text attributes are held in single cells, irrespective of how many paragraphs they contain. In this form, the data can be sorted by any attribute of the items or linked items:

In this form, the data is less immediately visible than when opened from an HTML file, but it is a single operation to reformat all of the cells to enable text wrapping and make the entire spreadsheet readable, just select the top left header button (above the row 1 button and to the left of the column A button), right click, choose Format Cells…, select the Alignment tab, select the Wrap Text checkbox and click OK:

Summary

Our recommendations for opening WorkBench tables in Excel are:

• If you want to preserve formatting and will not be re-sorting the data, save in HTML and be aware that extra rows may be produced for separate paragraphs in the output data
• If you want to re-sort the data then disable cell spanning and save in CSV to guarantee that separate paragraphs in item attributes will not appear as extra rows
• If you want to re-sort the data then disable cell spanning and save in HTML if you are sure that the data does not include attributes containing multiple paragraphs, or you don’t care if the data does have attributes that contain multiple paragraphs