Exemple #1
0
        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);
        }
Exemple #2
0
        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);
            }
        }
Exemple #3
0
        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();
            }
        }
Exemple #4
0
        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;
        }