How can I import TFS work items with "many to many" direct links from Excel

I have a Excel spreadsheet of requirements created from ReqPro that I need to import into TFS. Each req开发者_JAVA技巧uirement has a linked-from and linked-to field, both of which can contain more than one entry. Since the link relationship is many to many it requires that the direct-link type be used (not parent-child). I thought I would be able to import this using Excel 2010, however when opening a query using direct-links in Excel the results are flat and contain no linkage information. I have over 10,000 of these requirements to import with thousands of links so I need to automate this. Is there an easy way to import many-to-many direct-links into TFS?

---

Sorry to say that this is by design, see this bug raised at microsoft connect http://connect.microsoft.com/VisualStudio/feedback/details/667973/tfs-2010-work-items-with-direct-links-query-goes-to-excel-as-a-flat-list#.

However, there seems to be several work arounds,

1. If viewing the query results is all that's needed, you can e-mail or view as a report the results from the Sharepoint portal. (Since this is apparently "by design", can anyone say inconsistent design?)

2. If editing the results is desired, perhaps you can make the query a "Tree of Work Items"-type query.

3. Use the TFS SDK to make the Excel document properly. I did some investigation with this and it's do-able, but since we only needed viewing, #1 sufficed. Making the generated document editable may take some time.

PS - I can confirm that i have tried exporting a Tree of Work Items to excel and it keeps the link structure in tact. Also, see this MSDN article on how to do this http://msdn.microsoft.com/en-us/library/dd286627.aspx

But if you wanted to you could use the TFS API, i am adding some psudo code below, 1. Use the Open XML SDK 2.0 for Microsoft Office to connect to the large excel and read through it http://msdn.microsoft.com/en-us/library/gg575571.aspx 2. Use the TFS API to create work items programmatically 3. Create links between the work items http://blogs.microsoft.co.il/blogs/shair/archive/2010/02/27/tfs-api-part-22-create-link-between-work-item-parent-child-etc.aspx

private void ReadInformationFromExcelAndCreateLinkWorkItems()
    {
        using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(@"FilePath\fileName", false))
        {
            WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
            WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
            SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
            string text;
            int WorkItemId = 0;
            foreach (Row r in sheetData.Elements<Row>())
            {
                foreach (Cell c in r.Elements<Cell>())
                {
                    // Loop through each row till you complete one logical block of work item Links.
                    var tfs = TfsTeamProjectCollectionFactory.GetTeamProjectCollection(new Uri("TfsUrl"));
                    var wiStore = tfs.GetService<WorkItemStore>();

                    WorkItem wi = new WorkItem(new WorkItemType // Create a new work item of the type as in your excel)
                    // associate all column cell values to the work item
                    wi.Save(); 
                    // This should give you an ID now
                    WorkItemId = wi.Id;

                }
            }
        }
    }

HTH Cheers, Tarun