private static GroupedTimeRecords GroupItemsAndLoadParents(IEnumerable <ExportItemViewModelApi> rows)
{
// Populate timeNodeByWorkItemId
Program.WriteLogLine("Grouping by parents...");
foreach (var row in rows)
{
if (row.TFSID == null)
{
Program.WriteLogLine($"*** WARN : No WorkItemId for TimetrackerRowId={row.RowID} of {row.DurationInSeconds / 60} min on {row.RecordDate} .");
continue;
}
var workItemId = row.TFSID.Value;
if (!timeNodeByWorkItemId.ContainsKey(workItemId))
{
timeNodeByWorkItemId[workItemId] = new TrackedTimeNode {
FirstTrackedTimeRow = row
};
}
timeNodeByWorkItemId[workItemId].DirectTrackedTimeRows.Add(row);
}
LoadMissingParents();
DefineHierarchyLinks();
GroupUnparentedBugsAsAnEpic();
var groupedByWI = timeNodeByWorkItemId.Values.Where(x => x.ParentId == null)
.OrderBy(x => x.Project + " " + x.WorkItemType).ThenBy(x => x.ParentId)
.ToList();
Program.WriteLogLine("Grouping by members...");
var members = rows.Select(x => x.TeamMember).Distinct().OrderBy(x => x).ToList();
var groupedByTeamMember = new List <TeamMemberRecords>();
foreach (var member in members)
{
var tmr = new TeamMemberRecords()
{
TeamMember = member,
GroupedByWorkItem = GetCloneTimeNodesFilteredByMember(member, groupedByWI).ToList()
};
groupedByTeamMember.Add(tmr);
}
var result = new GroupedTimeRecords()
{
GroupedByWorkItem = groupedByWI,
GroupedByTeamMember = groupedByTeamMember
};
return(result);
}
public string ExportAsExcel(GroupedTimeRecords groupedTimeNodes)
{
using (var workbook = new XLWorkbook(XLEventTracking.Disabled))
{
AddSheetWithGroupingByTopParents(groupedTimeNodes.GroupedByWorkItem, workbook);
AddSheetWithGroupingByTeamMember(groupedTimeNodes, workbook);
AddSheetWithGroupingByTeamMemberAndMonth(groupedTimeNodes, workbook);
var filename = $"{DateTime.Now:yyyy-MM-dd_HH-mm-ss} - Timetracker export from {Program.StartDate:yyyy-MM-dd} to {Program.EndDate:yyyy-MM-dd}.xlsx";
var filepath = Path.Combine(Directory.GetCurrentDirectory(), filename);
workbook.SaveAs(filepath);
return(filepath);
}
}
private static void Main(string[] args)
{
bool parsed = false;
CommandLineOptions cmd = null;
// Get parameters
CommandLine.Parser.Default.ParseArguments <CommandLineOptions>(args).WithParsed(x =>
{
parsed = true;
cmd = x;
})
.WithNotParsed(x => { Program.WriteLogLine("Check https://github.com/laugel/timetracker-excel-exporter to get samples of usage"); });
if (!parsed)
{
Console.ReadLine();
return;
}
tfsExtender = new TFSExtender(cmd.TfsUrl, cmd.VstsToken);
// Create OData service context
var context = cmd.IsWindowsAuth
? new TimetrackerOdataContext(cmd.ServiceUri)
: new TimetrackerOdataContext(cmd.ServiceUri, cmd.Token);
if (!string.IsNullOrEmpty(cmd.StartDate) && !string.IsNullOrEmpty(cmd.EndDate))
{
StartDate = DateTime.ParseExact(cmd.StartDate, "yyyy-MM-dd", CultureInfo.InvariantCulture);
EndDate = DateTime.ParseExact(cmd.EndDate, "yyyy-MM-dd", CultureInfo.InvariantCulture);
}
else
{ // fallback
StartDate = new DateTime(2018, 6, 1);
//StartDate = DateTime.Today.AddDays(-7);
//StartDate = DateTime.Today.AddMonths(-6);
EndDate = new DateTime(2018, 10, 31);
//EndDate = DateTime.Today;
}
// tests only
//var workItems = tfsExtender.GetMultipleTfsItemsDataWithoutCache(new int[] { 11251, 8385, 2934 });
var timeExport = context.Container.TimeExport(StartDate.ToString(DateParametersFormat), EndDate.ToString(DateParametersFormat), null, null, null);
timeExport = timeExport.AddQueryOption("api-version", "2.1");
Program.WriteLogLine("Calling Timetracker API...");
ExportItemViewModelApi[] timeExportResult = timeExport.ToArray();
Program.WriteLogLine("Loading parents...");
GroupedTimeRecords groupedItems = GroupItemsAndLoadParents(timeExportResult);
Program.WriteLogLine("Exporting...");
var filePath = new ExcelExporter().ExportAsExcel(groupedItems);
if (cmd.OpenFileAfterGeneration)
{
Program.WriteLogLine($"Opening ${filePath} ...");
Process.Start(filePath);
}
Program.WriteLogLine("Finished.");
if (cmd.ForcePauseBeforeExit)
{
Program.WriteLogLine("Press ENTER to exit.");
Console.ReadLine();
}
}
private void AddSheetWithGroupingByTeamMemberAndMonth(GroupedTimeRecords groupedTimeNodes, XLWorkbook workbook)
{
showReportsByMonth = true;
worksheet = workbook.Worksheets.Add("Grouped by user + month");
worksheet.Outline.SummaryVLocation = XLOutlineSummaryVLocation.Top;
worksheet.Outline.SummaryHLocation = XLOutlineSummaryHLocation.Left;
var frontCell = worksheet.Cell("A4");
var firstCell = frontCell;
var headerCell = frontCell;
headerCell.Value = "Member";
headerCell.WorksheetColumn().Width = 22;
headerCell = headerCell.CellRight().SetValue("WorkItem ID");
headerCell.WorksheetColumn().Width = 12;
headerCell = headerCell.CellRight().SetValue("Project");
headerCell.WorksheetColumn().Width = 18;
headerCell = headerCell.CellRight().SetValue("Type");
headerCell.WorksheetColumn().Width = 9;
headerCell = headerCell.CellRight().SetValue("ParentId");
headerCell.WorksheetColumn().Width = 8;
headerCell = headerCell.CellRight().SetValue("Parent (lev2)");
headerCell.WorksheetColumn().Width = 11;
var parentLvl2Column = headerCell.WorksheetColumn();
headerCell = headerCell.CellRight().SetValue("Parent (lev3)");
headerCell.WorksheetColumn().Width = 11;
headerCell = headerCell.CellRight().SetValue("Parent (lev4+)");
headerCell.WorksheetColumn().Width = 11;
var parentLvl4Column = headerCell.WorksheetColumn();
headerCell = headerCell.CellRight().SetValue(WorkItemTitleLabel);
var titleColumn = headerCell.WorksheetColumn();
headerCell = headerCell.CellRight().SetValue(TotalDurationLabel);
var totalDurationColumn = headerCell.WorksheetColumn();
headerCell.WorksheetColumn().Width = 14;
headerCell = headerCell.CellRight().SetValue("SubTotal (Lvl2)");
headerCell.WorksheetColumn().Width = 14;
var subTotalLvl1Column = headerCell.WorksheetColumn();
headerCell = headerCell.CellRight().SetValue("SubTotal (Lvl3)");
headerCell.WorksheetColumn().Width = 14;
headerCell = headerCell.CellRight().SetValue("SubTotal (Lvl4+)");
headerCell.WorksheetColumn().Width = 14;
var subTotalLvl4Column = headerCell.WorksheetColumn();
headerCell = headerCell.CellRight().SetValue(DirectDurationWithoutChildrenLabel);
headerCell.WorksheetColumn().Width = 14;
headerCell = headerCell.CellRight().SetValue("Date");
headerCell.WorksheetColumn().Width = 14;
headerCell = headerCell.CellRight().SetValue(TeamMemberLabel);
headerCell.WorksheetColumn().Width = 15;
if (showReportsByMonth)
{
var currentMonth = FirstMonth;
while (currentMonth <= Lastmonth)
{
headerCell = headerCell.CellRight().SetValue(currentMonth.ToString("yyyy-MM"));
headerCell.WorksheetColumn().Width = 15;
currentMonth = currentMonth.AddMonths(1);
}
}
frontCell = frontCell.CellBelow();
var lastDataCell = frontCell;
foreach (var member in groupedTimeNodes.GroupedByTeamMember)
{
var currentCell = frontCell;
var firstMemberCell = currentCell;
currentCell.SetValue(member.TeamMember);
currentCell = currentCell.CellRight(); // WorkItemID
currentCell = currentCell.CellRight(); // Project
currentCell = currentCell.CellRight(); // WorkItemType
currentCell = currentCell.CellRight(); //.SetValue(level == 2 ? row.ParentId : null);
currentCell = currentCell.CellRight(); //.SetValue(level == 3 ? row.ParentId : null);
currentCell = currentCell.CellRight(); //.SetValue(level == 4 ? row.ParentId : null);
currentCell = currentCell.CellRight(); //.SetValue(level > 4 ? row.ParentId : null);
currentCell = currentCell.CellRight(); // Title
currentCell = currentCell.CellRight().SetValue(member.GroupedByWorkItem.Sum(x => x.TotalDurationWithChildrenInMin / 60d));
currentCell = currentCell.CellRight(); // (level == 2 ? wiTotalDurationInMin : null);
currentCell = currentCell.CellRight(); // (level == 3 ? wiTotalDurationInMin : null);
currentCell = currentCell.CellRight(); // (level >= 4 ? wiTotalDurationInMin : null);
currentCell = currentCell.CellRight(); // (wiDirectDurationInMin);
currentCell = currentCell.CellRight(); // (recordDate);
currentCell = currentCell.CellRight(); // (teamMember);
if (showReportsByMonth)
{
var currentMonth = FirstMonth;
while (currentMonth <= Lastmonth)
{
currentCell = currentCell.CellRight().SetValue(member.GroupedByWorkItem.Sum(x => x.GetTotalDurationWithChildrenInMinForMonth(currentMonth) / 60d));
currentMonth = currentMonth.AddMonths(1);
}
}
frontCell = frontCell.CellBelow().CellRight();
AddWorkItems(member.GroupedByWorkItem, ref frontCell, ref lastDataCell, 1);
frontCell = frontCell.CellLeft();
// regrouper
// cf https://stackoverflow.com/questions/25756741/closedxml-outline pour identifier quelles lignes utiliser pour appeler Group().
var rowsToGroup = firstMemberCell.Worksheet.Rows(firstMemberCell.CellBelow().Address.RowNumber, frontCell.CellAbove().Address.RowNumber);
excelGroupingActions.Push(() =>
{
rowsToGroup.Group(1); // Create an outline
});
}
foreach (var action in excelGroupingActions)
{
action();
}
var excelTable = worksheet.Range(firstCell.WorksheetRow().RowNumber(), firstCell.WorksheetColumn().ColumnNumber(),
lastDataCell.WorksheetRow().RowNumber(),
worksheet.RangeUsed().LastColumnUsed().ColumnNumber()
).CreateTable();
// Add the totals row
//excelTable.ShowTotalsRow = true;
// following lines corrupt the XLSX file :
//excelTable.Field(DirectDurationWithoutChildrenLabel).TotalsRowFunction = XLTotalsRowFunction.Sum;
//excelTable.Field(titleColumn.RangeAddress.FirstAddress.ColumnNumber).TotalsRowFunction = XLTotalsRowFunction.Sum;
//excelTable.Field(WorkItemTitleLabel).TotalsRowLabel = "Sum :";
var sumDurationCell = worksheet.Cell(lastDataCell.CellBelow().Address.RowNumber, totalDurationColumn.ColumnNumber());
var firstDurationCell = totalDurationColumn.FirstCellUsed().CellBelow();
var lastDurationCell = sumDurationCell.CellAbove();
sumDurationCell.FormulaA1 = $"=SUM({firstDurationCell.Address}:{lastDurationCell.Address})";
sumDurationCell.Style.Font.Bold = true;
sumDurationCell.CellLeft().Value = "Sum : ";
sumDurationCell.CellLeft().Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
sumDurationCell.CellLeft().Style.Font.Bold = true;
titleColumn.Width = 60;
var titleCell = worksheet.Cell("B2").SetValue($"Time tracked between {Program.StartDate:yyyy-MM-dd} and {Program.EndDate:yyyy-MM-dd} (in hours)");
titleCell.Style.Font.Bold = true;
titleCell.Style.Font.FontSize = 16;
// group nested parent columns under "parentId"
worksheet.Columns(parentLvl2Column.ColumnNumber(), parentLvl4Column.ColumnNumber()).Group();
worksheet.Columns(subTotalLvl1Column.ColumnNumber(), subTotalLvl4Column.ColumnNumber()).Group();
worksheet.CollapseRows();
worksheet.CollapseColumns();
showReportsByMonth = false;
}