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