示例#1
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;
        }
示例#2
0
        ///<summary>
        ///returns next spare row number
        ///</summary>
        public static int AddLinesPeriodStatisticToSheet(IXLWorksheet toSheet, int startRow, List <LinePeriodStatisticDto> data, List <LinesDatedTotalStatisticDto> footerData)
        {
            var dates = data[0].DayDate;

            toSheet.Cell(startRow, 1).Value = DEBS.Translate("Line.ReportByDays");
            RowStyle_H2(toSheet.Row(startRow));
            startRow += 1;
            toSheet.Cell(startRow, 1).Value = DEBS.Translate("Line.LineNumber");

            for (int i = 0; i < dates.Count; i++)
            {
                toSheet.Cell(startRow, 2 + i).Value = dates[i].ToString(@"ddd dd/MM/yyyy");
            }
            startRow++;
            for (int i = 0; i < data.Count; i++)
            {
                //Pure Row
                toSheet.Cell(startRow, 1).Value = data[i].LineNumber;
                toSheet.Row(startRow).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                MakeBottomBorder(toSheet.Row(startRow));
                for (int colomn = 0; colomn < dates.Count; colomn++)
                {
                    var  c         = toSheet.Cell(startRow, 2 + colomn);
                    bool scheduled = data[i].DayScheduleData[colomn] != LinePeriodStatisticDto.DayScheduleData_INACTIVE;
                    c.Value = scheduled ? data[i].DayScheduleData[colomn] : "-";
                    if (scheduled)
                    {
                        c.Style.Fill.BackgroundColor = XLColor.SeaGreen;
                    }
                }
                startRow++;
                //Info
                var infoRowStart = startRow;
                toSheet.Cell(startRow, 2).Value = DEBS.Translate("Line.Name");
                toSheet.Cell(startRow, 3).Value = data[i].LineName;
                startRow++;
                toSheet.Cell(startRow, 2).Value = DEBS.Translate("Line.Direction");
                toSheet.Cell(startRow, 3).Value = DirectionToString(data[i].Direction);
                startRow++;
                toSheet.Cell(startRow, 2).Value = DEBS.Translate("Line.totalStudents");
                toSheet.Cell(startRow, 3).Value = data[i].totalStudents;
                startRow++;
                toSheet.Cell(startRow, 2).Value = DEBS.Translate("Bus.CompanyName");
                toSheet.Cell(startRow, 3).Value = data[i].BusCompanyName;
                startRow++;
                toSheet.Cell(startRow, 2).Value = DEBS.Translate("Bus.seats");
                toSheet.Cell(startRow, 3).Value = data[i].seats;
                startRow++;
                toSheet.Cell(startRow, 2).Value = DEBS.Translate("Bus.price");
                toSheet.Cell(startRow, 3).Value = data[i].price;
                //styling and collapsing
                FoldRows(toSheet.Rows(infoRowStart, startRow));
                startRow++;
            }
            toSheet.CollapseRows(1);
            //Footer
            toSheet.Cell(startRow, 1).Value = DEBS.Translate("Lines.totalPrice");
            toSheet.Row(startRow).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
            MakeFooterStyle(toSheet.Row(startRow));
            for (int colomn = 0; colomn < dates.Count; colomn++)
            {
                var c = toSheet.Cell(startRow, 2 + colomn);
                c.Value = footerData[colomn].totalPrice;
            }
            startRow++;
            return(startRow);
        }
示例#3
0
        private void AddSheetWithGroupingByTopParents(IEnumerable <TrackedTimeNode> groupedItems, XLWorkbook workbook)
        {
            worksheet = workbook.Worksheets.Add("Grouped by parents");
            worksheet.Outline.SummaryVLocation = XLOutlineSummaryVLocation.Top;
            worksheet.Outline.SummaryHLocation = XLOutlineSummaryHLocation.Left;

            var frontCell  = worksheet.Cell("B4");
            var firstCell  = frontCell;
            var headerCell = frontCell;

            headerCell.Value = "WorkItem ID";
            headerCell       = headerCell.CellRight().SetValue("Project");
            headerCell       = headerCell.CellRight().SetValue("Type");
            headerCell       = headerCell.CellRight().SetValue("ParentId");
            headerCell       = headerCell.CellRight().SetValue("Parent (lev2)");
            var parentLvl2Column = headerCell.WorksheetColumn();

            headerCell = headerCell.CellRight().SetValue("Parent (lev3)");
            headerCell = headerCell.CellRight().SetValue("Parent (lev4+)");
            var parentLvl4Column = headerCell.WorksheetColumn();

            headerCell = headerCell.CellRight().SetValue(WorkItemTitleLabel);
            var titleColumn = headerCell.WorksheetColumn();

            headerCell = headerCell.CellRight().SetValue(TotalDurationLabel);
            headerCell = headerCell.CellRight().SetValue("SubTotal (Lvl2)");
            var subTotalLvl1Column = headerCell.WorksheetColumn();

            headerCell = headerCell.CellRight().SetValue("SubTotal (Lvl3)");
            headerCell = headerCell.CellRight().SetValue("SubTotal (Lvl4+)");
            var subTotalLvl4Column = headerCell.WorksheetColumn();

            headerCell = headerCell.CellRight().SetValue(DirectDurationWithoutChildrenLabel);
            headerCell = headerCell.CellRight().SetValue("Date");
            headerCell = headerCell.CellRight().SetValue(TeamMemberLabel);

            frontCell = frontCell.CellBelow();
            var lastDataCell = frontCell;

            AddWorkItems(groupedItems, ref frontCell, ref lastDataCell, 1);

            var excelTable = worksheet.Range(firstCell, lastDataCell).CreateTable();

            // Add the totals row
            excelTable.ShowTotalsRow = true;
            // Put the average on the field "Income"
            // Notice how we're calling the cell by the column name
            excelTable.Field(DirectDurationWithoutChildrenLabel).TotalsRowFunction = XLTotalsRowFunction.Sum;
            excelTable.Field(TotalDurationLabel).TotalsRowFunction = XLTotalsRowFunction.Sum;
            // Put a label on the totals cell of the field "Title"
            excelTable.Field(WorkItemTitleLabel).TotalsRowLabel = "Sum:";


            foreach (var action in excelGroupingActions)
            {
                action();
            }

            worksheet.Columns().AdjustToContents();

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