Cross-Project Queries with Microsoft Excel

Description:

The client needed to be able to run a single query for work items across multiple projects, and load the results into Microsoft Excel.

While Team Explorer includes the capability to export query results to Excel, unfortunately this functionality does not work when the query includes results from more than one project.

An add-on package from Microsoft, the Team Foundation Power Tools, includes a command line program named tfpt.exe. This program allows you to run a query across multiple projects and export the results to a tab-delimitted text file, which can be subsequently loaded into Excel. The tfpt.exe program, however, did not deal with special characters such as quotes and line breaks, which caused formatting problems when the resulting file was loaded into Excel.

Solution:

For this project, we wrote a command line program similar to tfpt.exe. We took care to encode special characters properly, and exported the results to a Comma Separated Values (CSV) file, rather than a tab-delimitted file, which Excel loads easier than a tab-delimitted file.

Since completing this project for the client, we have completely re-written this program internally, and have added support for native Excel formats and PDF as well. We will be releasing this to the community as a free utility in the near future.