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