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; }