When I create a TFS report of a query with the Excel integration features (we are using Excel 2003), Excel resets formatting of all cells after clicking the "Refresh" button in the TFS Toolbar.
Our team likes to print this report and drag it into our weekly meeting as it accurately lists all our open tasks. Bad formatting is a pain, though: Vertical alignment set to "bottom" and no borders on cells makes it nearly impossible to know when one Task/Bug starts and the other ends...
Hi Darren,
My guess is that since Microsoft is playing up this feature in the VS 2010 CTP, it's not currently supported.
Your best bet may be to create a sheet with all of the appropriate formatting and then cut and paste from the live excel sheet into the formatted sheet.
Ta.
Steve Porter
I ended up doing this:
tfpt.exe (Team Foundation Power Tools) tfpt query /format:xml (add your query name here etc.)This is the XSL script I used (will need tweeking if you use other fields):
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:spss="http://xml.spss.com/spss/oms"
exclude-result-prefixes="spss">
<xsl:template match="/">
<html>
<head>
<title>All active Work Items</title>
</head>
<body>
<table border="1" frame="border" rules="all">
<tr><th>ID</th><th>Area Path</th><th>Assigned To</th><th>Title</th><th>Description</th></tr>
<xsl:for-each select=".//WorkItem">
<tr>
<td><xsl:value-of select="./Field[@RefName='System.Id']/@Value"/></td>
<td><xsl:value-of select="./Field[@RefName='System.AreaPath']/@Value"/></td>
<td><xsl:value-of select="./Field[@RefName='System.AssignedTo']/@Value"/></td>
<td><xsl:value-of select="./Field[@RefName='System.Title']/@Value"/></td>
<td><xsl:value-of select="./Field[@RefName='System.Description']/@Value"/></td>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>
I ran into the same issue...
TFS Refresh is not so bad, but when the query stops working and you have to re-add it, it inserts rows to do the data creation, leaving any links to the info corrupted (or looking in the wrong place!)
I ended up with the TFS query going into a "TFS Query" sheet, which I referenced from the sheet that does calculations on the query fields (it uses Indirect, Offset, and Match functions so that there is no calculation dependence from the sheet with the TFS Query sheet. This works like a charm, and I can even delete the query, and re-add it later.