private static WorkItemManger PrepareWorkItemManager(ConnectionManager connectionManager, string teamProjectName)
{
WorkItemManger workItemManger = new WorkItemManger(connectionManager);
workItemManger.SetTeamProject(teamProjectName);
return(workItemManger);
}
public override void Dump(
StringBuilder stringBuilder,
Dictionary <string, object> parameters,
ConnectionManager connectionManager,
WordTemplateFolderManager wordTemplateFolderManager,
string teamProjectName)
{
WorkItemManger workItemManger = PrepareWorkItemManager(connectionManager, teamProjectName);
parameters = parameters.ToDictionary(k => k.Key, v => v.Value); //clone
//If we do not have query parameters we have a single query or we can have parametrized query with iterationPath
var queries = PrepareQueries(parameters);
foreach (var query in queries)
{
var workItems = workItemManger.ExecuteQuery(query).Take(Limit);
foreach (var workItem in workItems.Where(ShouldExport))
{
var values = workItem.CreateDictionaryFromWorkItem();
foreach (var value in values)
{
stringBuilder.AppendLine($"{value.Key.PadRight(50, ' ')}={value.Value}");
}
}
}
}
public override void Assemble(
WordManipulator manipulator,
Dictionary <string, object> parameters,
ConnectionManager connectionManager,
WordTemplateFolderManager wordTemplateFolderManager,
string teamProjectName)
{
parameters = parameters.ToDictionary(k => k.Key, v => v.Value); //clone
//If we do not have query parameters we have a single query or we can have parametrized query with iterationPath
var queries = PrepareQueries(parameters);
WorkItemManger workItemManger = new WorkItemManger(connectionManager);
workItemManger.SetTeamProject(teamProjectName);
foreach (var query in queries)
{
var workItems = workItemManger.ExecuteQuery(query).Take(Limit);
if (String.IsNullOrEmpty(TableTemplate))
{
foreach (var workItem in workItems)
{
if (!SpecificTemplates.TryGetValue(workItem.Type.Name, out var templateName))
{
templateName = wordTemplateFolderManager.GetTemplateFor(workItem.Type.Name);
}
else
{
templateName = wordTemplateFolderManager.GenerateFullFileName(templateName);
}
manipulator.InsertWorkItem(workItem, templateName, true, parameters);
}
}
else
{
//We have a table template, we want to export work item as a list
var tableFile = wordTemplateFolderManager.GenerateFullFileName(TableTemplate);
var tempFile = wordTemplateFolderManager.CopyFileInTempDirectory(tableFile);
using (var tableManipulator = new WordManipulator(tempFile, false))
{
tableManipulator.SubstituteTokens(parameters);
tableManipulator.FillTableWithCompositeWorkItems(true, workItems);
}
manipulator.AppendOtherWordFile(tempFile);
}
}
base.Assemble(manipulator, parameters, connectionManager, wordTemplateFolderManager, teamProjectName);
}
/// <summary>
/// This is similar to <see cref="FillTableWithSingleFieldWorkItems(bool, IEnumerable{WorkItem})"/>
/// but with this version the routine will perform a complete substitution in each cell
/// so you can have multiple value in cells. This is more time consuming that
/// <see cref="FillTableWithSingleFieldWorkItems(bool, IEnumerable{WorkItem})"/>
/// </summary>
/// <param name="skipHeader"></param>
/// <param name="workItems"></param>
/// <returns></returns>
public WordManipulator FillTableWithCompositeWorkItems(
Boolean skipHeader,
IEnumerable <WorkItem> workItems,
WorkItemManger workItemManger)
{
List <Dictionary <String, Object> > workItemCellsData = new List <Dictionary <String, Object> >();
List <Int32> parentList = workItems
.Select(wi => wi.GetParentLink())
.Where(l => l != null)
.Select(l => l.RelatedWorkItemId)
.ToList();
Dictionary <int, WorkItem> parentWorkItems = workItemManger.LoadListOfWorkItems(parentList)
.ToDictionary(w => w.Id);
List <Int32> granParentList = parentWorkItems.Values
.Select(wi => wi.GetParentLink())
.Where(l => l != null)
.Select(l => l.RelatedWorkItemId)
.ToList();
Dictionary <int, WorkItem> granParentWorkItems = workItemManger.LoadListOfWorkItems(granParentList)
.ToDictionary(w => w.Id);
foreach (var workItem in workItems)
{
var parameters = workItem.CreateDictionaryFromWorkItem();
RelatedLink parentLink = workItem.GetParentLink();
parameters["parent.title"] = String.Empty;
parameters["parent.id"] = String.Empty;
parameters["parent.parent.title"] = String.Empty;
parameters["parent.parent.id"] = String.Empty;
if (parentWorkItems.TryGetValue(parentLink?.RelatedWorkItemId ?? 0, out var parent))
{
parameters["parent.title"] = parent.Title;
parameters["parent.id"] = parent.Id;
var granParentLink = parent.GetParentLink();
if (granParentWorkItems.TryGetValue(granParentLink?.RelatedWorkItemId ?? 0, out var granParent))
{
parameters["parent.parent.title"] = granParent.Title;
parameters["parent.parent.id"] = granParent.Id;
}
}
workItemCellsData.Add(parameters);
}
return(FillCompositeTable(skipHeader, workItemCellsData));
}
private static void TestExcelExtraction(ConnectionManager connection)
{
WorkItemManger workItemManger = new WorkItemManger(connection);
workItemManger.SetTeamProject("CMMI Playground");
var hr = workItemManger.ExecuteHierarchicQuery(@"SELECT
*
FROM workitemLinks
WHERE
(
[Source].[System.TeamProject] = '{teamProjectName}'
AND[Source].[System.WorkItemType] = 'Feature'
AND[Source].[Microsoft.VSTS.Scheduling.TargetDate] < '2062-01-01T00:00:00.0000000'
AND[Source].[Microsoft.VSTS.Scheduling.TargetDate] > '1990-02-02T00:00:00.0000000'
)
AND(
[System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward'
)
AND(
[Target].[System.TeamProject] = '{teamProjectName}'
AND[Target].[System.WorkItemType] <> ''
)
MODE(Recursive)
",
new[] { "task", "requirement", "feature", "epic" });
var tempFile = @"c:\temp\test2.xlsx";
if (File.Exists(tempFile))
{
File.Delete(tempFile);
}
File.Copy(@"c:\temp\test.xlsx", tempFile);
using (ExcelManipulator m = new ExcelManipulator(tempFile))
{
m.FillWorkItems(hr);
}
System.Diagnostics.Process.Start(tempFile);
}
private static void PerformStandardIterationExport(ConnectionManager connection)
{
WorkItemManger workItemManger = new WorkItemManger(connection);
workItemManger.SetTeamProject(options.TeamProject);
var workItems = workItemManger.LoadAllWorkItemForAreaAndIteration(
options.AreaPath,
options.IterationPath);
var fileName = Path.GetTempFileName() + ".docx";
var templateManager = new TemplateManager("Templates");
var template = templateManager.GetWordDefinitionTemplate(options.TemplateName);
using (WordManipulator manipulator = new WordManipulator(fileName, true))
{
AddTableContent(manipulator, workItems, template);
foreach (var workItem in workItems)
{
manipulator.InsertWorkItem(workItem, template.GetTemplateFor(workItem.Type.Name), true);
}
}
System.Diagnostics.Process.Start(fileName);
}
public override void AssembleExcel(
ExcelManipulator manipulator,
Dictionary <string, object> parameters,
ConnectionManager connectionManager,
WordTemplateFolderManager wordTemplateFolderManager,
string teamProjectName)
{
WorkItemManger workItemManger = PrepareWorkItemManager(connectionManager, teamProjectName);
//If we do not have query parameters we have a single query or we can have parametrized query with iterationPath
var queries = PrepareQueries(parameters);
foreach (var query in queries)
{
if (HierarchyMode?.Length > 0)
{
var hr = workItemManger.ExecuteHierarchicQuery(query, HierarchyMode);
manipulator.FillWorkItems(hr);
}
else
{
throw new NotSupportedException("This version of the program only support hierarchy mode for excel");
}
}
}
