示例#1
0
        public byte[] Build(User currentUser, DateTime min, DateTime max, string filterUserScope, int[] filterUserIds, string filterProjectScope, int[] filterProjectIds, string filterLockStatus)
        {
            var organization = db.Organizations.Find(currentUser.OrganizationId);

            var reportTitle = $"PUNCHES BY DAY {min.ToString("M/d/yyyy")} thru {max.ToString("M/d/yyyy")} GENERATED {DateTime.Now.ToString("ddd, MMM d, yyyy h:mm:ss tt").ToUpperInvariant()}";

            using (var stream = new MemoryStream())
                using (var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
                {
                    // ------------------------------------------------------------
                    // Add document properties.
                    // ------------------------------------------------------------

                    document.PackageProperties.Creator = "BRIZBEE";
                    document.PackageProperties.Created = DateTime.UtcNow;


                    // ------------------------------------------------------------
                    // Add a WorkbookPart to the document.
                    // ------------------------------------------------------------

                    var workbookPart1 = document.AddWorkbookPart();
                    workbookPart1.Workbook = new Workbook();


                    // ------------------------------------------------------------
                    // Apply stylesheet.
                    // ------------------------------------------------------------

                    var workStylePart1 = workbookPart1.AddNewPart <WorkbookStylesPart>();
                    workStylePart1.Stylesheet = Stylesheets.Common();;


                    // ------------------------------------------------------------
                    // Add a WorksheetPart to the WorkbookPart.
                    // ------------------------------------------------------------

                    var worksheetPart1 = workbookPart1.AddNewPart <WorksheetPart>();
                    var worksheet1     = new Worksheet();
                    var rowBreaks1     = new RowBreaks()
                    {
                        Count = 0, ManualBreakCount = 0
                    };
                    var mergeCells1 = new MergeCells()
                    {
                        Count = 0
                    };
                    var sheetData1 = new SheetData();


                    // ------------------------------------------------------------
                    // Collect the users based on the filters.
                    // ------------------------------------------------------------

                    List <User> users;
                    if (filterUserScope.ToUpperInvariant() == "SPECIFIC")
                    {
                        users = db.Users
                                .Where(u => u.OrganizationId == currentUser.OrganizationId)
                                .Where(u => u.IsDeleted == false)
                                .Where(u => filterUserIds.Contains(u.Id))
                                .OrderBy(u => u.Name)
                                .ToList();
                    }
                    else
                    {
                        users = db.Users
                                .Where(u => u.OrganizationId == currentUser.OrganizationId)
                                .Where(u => u.IsDeleted == false)
                                .OrderBy(u => u.Name)
                                .ToList();
                    }


                    // ------------------------------------------------------------
                    // Collect the punches based on the filters.
                    // ------------------------------------------------------------

                    IQueryable <Punch> punchesQueryable = db.Punches
                                                          .Include(p => p.Task.Job.Customer)
                                                          .Include(p => p.User)
                                                          .Include(p => p.ServiceRate)
                                                          .Include(p => p.PayrollRate)
                                                          .Where(p => p.User.OrganizationId == currentUser.OrganizationId)
                                                          .Where(p => p.User.IsDeleted == false)
                                                          .Where(p => p.OutAt.HasValue == true)
                                                          .Where(p => DbFunctions.TruncateTime(p.InAt) >= min.Date)
                                                          .Where(p => DbFunctions.TruncateTime(p.InAt) <= max.Date);

                    // Optionally filter projects.
                    if (filterProjectScope.ToUpperInvariant() == "SPECIFIC")
                    {
                        punchesQueryable = punchesQueryable
                                           .Where(p => filterProjectIds.Contains(p.Task.JobId));
                    }

                    // Optionally filter locked or unlocked.
                    if (filterLockStatus.ToUpperInvariant() == "ONLY")
                    {
                        punchesQueryable = punchesQueryable.Where(p => p.CommitId != null);
                    }
                    else if (filterLockStatus.ToUpperInvariant() == "UNCOMMITTED")
                    {
                        punchesQueryable = punchesQueryable.Where(p => p.CommitId == null);
                    }

                    var punches = punchesQueryable.ToList();

                    var dates = punches
                                .GroupBy(p => p.InAt.Date)
                                .Select(g => g.Key)
                                .OrderBy(g => g.Date)
                                .ToList();


                    // ------------------------------------------------------------
                    // Loop each date.
                    // ------------------------------------------------------------

                    var rowIndex = (uint)1;
                    foreach (var date in dates)
                    {
                        var punchesForDate = punches
                                             .Where(p => p.InAt.Date == date.Date)
                                             .OrderBy(p => p.InAt);


                        // ------------------------------------------------------------
                        // Header for date cell.
                        // ------------------------------------------------------------

                        var rowDate = new Row()
                        {
                            RowIndex = rowIndex, Height = 22D, CustomHeight = true, Spans = new ListValue <StringValue>()
                            {
                                InnerText = "1:1"
                            }, StyleIndex = 4U, CustomFormat = true
                        };

                        var cellDate = new Cell()
                        {
                            CellReference = $"A{rowIndex}", StyleIndex = 4U, DataType = CellValues.String, CellValue = new CellValue(date.ToString("D"))
                        };
                        rowDate.Append(cellDate);

                        sheetData1.Append(rowDate);

                        // Merge the date across the row.
                        var mergeCell1 = new MergeCell()
                        {
                            Reference = $"A{rowIndex}:M{rowIndex}"
                        };
                        mergeCells1.Append(mergeCell1);
                        mergeCells1.Count++;

                        rowIndex++;


                        // ------------------------------------------------------------
                        // Headers for punch cells.
                        // ------------------------------------------------------------

                        var rowHeaders = new Row()
                        {
                            RowIndex = rowIndex, Height = 16D, CustomHeight = true, StyleIndex = 1U, CustomFormat = true
                        };

                        // InAt
                        var cellInAtHeader = new Cell()
                        {
                            CellReference = $"A{rowIndex}", DataType = CellValues.String, StyleIndex = 1U
                        };
                        var cellValueForInAtHeader = new CellValue("In");

                        cellInAtHeader.Append(cellValueForInAtHeader);
                        rowHeaders.Append(cellInAtHeader);

                        // OutAt
                        var cellOutAtHeader = new Cell()
                        {
                            CellReference = $"B{rowIndex}", DataType = CellValues.String, StyleIndex = 1U
                        };
                        var cellValueForOutAtHeader = new CellValue("Out");

                        cellOutAtHeader.Append(cellValueForOutAtHeader);
                        rowHeaders.Append(cellOutAtHeader);

                        // User Name
                        var cellUserNameHeader = new Cell()
                        {
                            CellReference = $"C{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("User")
                        };
                        rowHeaders.Append(cellUserNameHeader);

                        // Task Number
                        var cellTaskNumberHeader = new Cell()
                        {
                            CellReference = $"D{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("#")
                        };
                        rowHeaders.Append(cellTaskNumberHeader);

                        // Task Name
                        var cellTaskNameHeader = new Cell()
                        {
                            CellReference = $"E{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("Task")
                        };
                        rowHeaders.Append(cellTaskNameHeader);

                        // Project Number
                        var cellProjectNumberHeader = new Cell()
                        {
                            CellReference = $"F{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("#")
                        };
                        rowHeaders.Append(cellProjectNumberHeader);

                        // Project Name
                        var cellProjectNameHeader = new Cell()
                        {
                            CellReference = $"G{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("Project")
                        };
                        rowHeaders.Append(cellProjectNameHeader);

                        // Customer Number
                        var cellCustomerNumberHeader = new Cell()
                        {
                            CellReference = $"H{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("#")
                        };
                        rowHeaders.Append(cellCustomerNumberHeader);

                        // Customer Name
                        var cellCustomerNameHeader = new Cell()
                        {
                            CellReference = $"I{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("Customer")
                        };
                        rowHeaders.Append(cellCustomerNameHeader);

                        // Customer Rate
                        var cellCustomerRateHeader = new Cell()
                        {
                            CellReference = $"J{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("Customer Rate")
                        };
                        rowHeaders.Append(cellCustomerRateHeader);

                        // Payroll Rate
                        var cellPayrollRateHeader = new Cell()
                        {
                            CellReference = $"K{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("Payroll Rate")
                        };
                        rowHeaders.Append(cellPayrollRateHeader);

                        // Locked
                        var cellLockedHeader = new Cell()
                        {
                            CellReference = $"L{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("Locked?")
                        };
                        rowHeaders.Append(cellLockedHeader);

                        // Total
                        var cellTotalHeader = new Cell()
                        {
                            CellReference = $"M{rowIndex}", DataType = CellValues.String, StyleIndex = 2U, CellValue = new CellValue("Total")
                        };
                        rowHeaders.Append(cellTotalHeader);

                        sheetData1.Append(rowHeaders);

                        rowIndex++;


                        // ------------------------------------------------------------
                        // Punch cells.
                        // ------------------------------------------------------------

                        foreach (var punch in punchesForDate)
                        {
                            var rowPunch = new Row()
                            {
                                RowIndex = rowIndex, Height = 16D, CustomHeight = true
                            };

                            // InAt
                            var cellInAt = new Cell()
                            {
                                CellReference = $"A{rowIndex}", DataType = CellValues.String, StyleIndex = 6U, CellValue = new CellValue(punch.InAt.ToShortTimeString())
                            };
                            rowPunch.Append(cellInAt);

                            // OutAt
                            var cellOutAt = new Cell()
                            {
                                CellReference = $"B{rowIndex}", DataType = CellValues.String, StyleIndex = 6U, CellValue = new CellValue(punch.OutAt.Value.ToShortTimeString())
                            };
                            rowPunch.Append(cellOutAt);

                            // User Name
                            var cellUserName = new Cell()
                            {
                                CellReference = $"C{rowIndex}", DataType = CellValues.String, StyleIndex = 6U, CellValue = new CellValue(punch.User.Name)
                            };
                            rowPunch.Append(cellUserName);

                            // Task Number
                            var cellTaskNumber = new Cell()
                            {
                                CellReference = $"D{rowIndex}", DataType = CellValues.Number, StyleIndex = 6U, CellValue = new CellValue(punch.Task.Number)
                            };
                            rowPunch.Append(cellTaskNumber);

                            // Task Name
                            var cellTaskName = new Cell()
                            {
                                CellReference = $"E{rowIndex}", DataType = CellValues.String, StyleIndex = 6U, CellValue = new CellValue(punch.Task.Name)
                            };
                            rowPunch.Append(cellTaskName);

                            // Project Number
                            var cellProjectNumber = new Cell()
                            {
                                CellReference = $"F{rowIndex}", DataType = CellValues.Number, StyleIndex = 6U, CellValue = new CellValue(punch.Task.Job.Number)
                            };
                            rowPunch.Append(cellProjectNumber);

                            // Project Name
                            var cellProjectName = new Cell()
                            {
                                CellReference = $"G{rowIndex}", DataType = CellValues.String, StyleIndex = 6U, CellValue = new CellValue(punch.Task.Job.Name)
                            };
                            rowPunch.Append(cellProjectName);

                            // Customer Number
                            var cellCustomerNumber = new Cell()
                            {
                                CellReference = $"H{rowIndex}", DataType = CellValues.Number, StyleIndex = 6U, CellValue = new CellValue(punch.Task.Job.Customer.Number)
                            };
                            rowPunch.Append(cellCustomerNumber);

                            // Customer Name
                            var cellCustomerName = new Cell()
                            {
                                CellReference = $"I{rowIndex}", DataType = CellValues.String, StyleIndex = 6U, CellValue = new CellValue(punch.Task.Job.Customer.Name)
                            };
                            rowPunch.Append(cellCustomerName);

                            // Customer Rate
                            var cellCustomerRate = new Cell()
                            {
                                CellReference = $"J{rowIndex}", DataType = CellValues.String, StyleIndex = 6U, CellValue = new CellValue(punch.ServiceRateId.HasValue ? punch.ServiceRate.Name : "")
                            };
                            rowPunch.Append(cellCustomerRate);

                            // Payroll Rate
                            var cellPayrollRate = new Cell()
                            {
                                CellReference = $"K{rowIndex}", DataType = CellValues.String, StyleIndex = 6U, CellValue = new CellValue(punch.PayrollRateId.HasValue ? punch.PayrollRate.Name : "")
                            };
                            rowPunch.Append(cellPayrollRate);

                            // Locked
                            var cellLocked = new Cell()
                            {
                                CellReference = $"L{rowIndex}", DataType = CellValues.String, StyleIndex = 3U, CellValue = new CellValue(punch.CommitId.HasValue ? "X" : "")
                            };
                            rowPunch.Append(cellLocked);

                            // Calculate the total
                            var total = Math.Round((punch.OutAt.Value - punch.InAt).TotalMinutes / 60, 2).ToString("0.00");

                            // Total
                            var cellTotal = new Cell()
                            {
                                CellReference = $"M{rowIndex}", DataType = CellValues.Number, StyleIndex = 5U, CellValue = new CellValue(total)
                            };
                            rowPunch.Append(cellTotal);

                            sheetData1.Append(rowPunch);

                            rowIndex++;
                        }


                        // ------------------------------------------------------------
                        // Cells for date total.
                        // ------------------------------------------------------------

                        var rowDateTotal = new Row()
                        {
                            RowIndex = rowIndex, Height = 16D, CustomHeight = true, StyleIndex = 1U, CustomFormat = true
                        };

                        // Header Cell
                        var cellDateFormatted = new Cell()
                        {
                            CellReference = $"A{rowIndex}", DataType = CellValues.String, StyleIndex = 2U, CellValue = new CellValue("Daily Total")
                        };
                        rowDateTotal.Append(cellDateFormatted);

                        // Calculate the total
                        double dailyTotalMinutes = 0;
                        foreach (var punch in punchesForDate)
                        {
                            dailyTotalMinutes += (punch.OutAt.Value - punch.InAt).TotalMinutes;
                        }
                        var dailyTotal = Math.Round(dailyTotalMinutes / 60, 2).ToString("0.00");

                        // Total Cell
                        var cellDateTotal = new Cell()
                        {
                            CellReference = $"M{rowIndex}", DataType = CellValues.Number, StyleIndex = 2U, CellValue = new CellValue(dailyTotal)
                        };
                        rowDateTotal.Append(cellDateTotal);

                        sheetData1.Append(rowDateTotal);

                        // Merge the date across the row.
                        var mergeCell3 = new MergeCell()
                        {
                            Reference = $"A{rowIndex}:L{rowIndex}"
                        };
                        mergeCells1.Append(mergeCell3);
                        mergeCells1.Count++;

                        rowIndex++;


                        // ------------------------------------------------------------
                        // Add a page break.
                        // ------------------------------------------------------------

                        var rowBreak1 = new Break()
                        {
                            Id = rowIndex, Max = 16383U, ManualPageBreak = true
                        };
                        rowBreaks1.Append(rowBreak1);
                        rowBreaks1.ManualBreakCount++;
                        rowBreaks1.Count++;

                        rowIndex++;
                    }


                    // ------------------------------------------------------------
                    // Custom column width.
                    // ------------------------------------------------------------

                    var columns1 = new Columns();

                    var column1 = new Column()
                    {
                        Min = 1U, Max = 1U, Width = 9D, CustomWidth = true
                    };
                    var column2 = new Column()
                    {
                        Min = 2U, Max = 2U, Width = 9D, CustomWidth = true
                    };
                    var column3 = new Column()
                    {
                        Min = 3U, Max = 3U, Width = 28D, CustomWidth = true
                    };
                    var column4 = new Column()
                    {
                        Min = 4U, Max = 4U, Width = 8D, CustomWidth = true
                    };
                    var column5 = new Column()
                    {
                        Min = 5U, Max = 5U, Width = 28D, CustomWidth = true
                    };
                    var column6 = new Column()
                    {
                        Min = 6U, Max = 6U, Width = 8D, CustomWidth = true
                    };
                    var column7 = new Column()
                    {
                        Min = 7U, Max = 7U, Width = 28D, CustomWidth = true
                    };
                    var column8 = new Column()
                    {
                        Min = 8U, Max = 8U, Width = 8D, CustomWidth = true
                    };
                    var column9 = new Column()
                    {
                        Min = 9U, Max = 9U, Width = 28D, CustomWidth = true
                    };
                    var column10 = new Column()
                    {
                        Min = 10U, Max = 10U, Width = 15D, CustomWidth = true
                    };
                    var column11 = new Column()
                    {
                        Min = 11U, Max = 11U, Width = 15D, CustomWidth = true
                    };
                    var column12 = new Column()
                    {
                        Min = 12U, Max = 12U, Width = 8D, CustomWidth = true
                    };
                    var column13 = new Column()
                    {
                        Min = 13U, Max = 13U, Width = 8D, CustomWidth = true
                    };

                    columns1.Append(column1);
                    columns1.Append(column2);
                    columns1.Append(column3);
                    columns1.Append(column4);
                    columns1.Append(column5);
                    columns1.Append(column6);
                    columns1.Append(column7);
                    columns1.Append(column8);
                    columns1.Append(column9);
                    columns1.Append(column10);
                    columns1.Append(column11);
                    columns1.Append(column12);
                    columns1.Append(column13);


                    // ------------------------------------------------------------
                    // Sheet Views.
                    // ------------------------------------------------------------

                    var sheetViews1 = new SheetViews();

                    var sheetView1 = new SheetView()
                    {
                        ShowGridLines = true, TabSelected = true, ZoomScaleNormal = 100U, WorkbookViewId = 0U
                    };
                    var selection1 = new Selection()
                    {
                        ActiveCell = "A1", SequenceOfReferences = new ListValue <StringValue>()
                        {
                            InnerText = "A1"
                        }
                    };

                    sheetView1.Append(selection1);
                    sheetViews1.Append(sheetView1);


                    // ------------------------------------------------------------
                    // Sheet Format.
                    // ------------------------------------------------------------

                    var sheetFormatProperties1 = new SheetFormatProperties()
                    {
                        DefaultRowHeight = 16D, DyDescent = 0.35D
                    };


                    // ------------------------------------------------------------
                    // Page Setup.
                    // ------------------------------------------------------------

                    var pageMargins1 = new PageMargins()
                    {
                        Left = 0.5D, Right = 0.5D, Top = 0.5D, Bottom = 0.5D, Header = 0.3D, Footer = 0.3D
                    };
                    var pageSetup1 = new PageSetup()
                    {
                        Orientation = OrientationValues.Landscape
                    };


                    // ------------------------------------------------------------
                    // Header and Footer.
                    // ------------------------------------------------------------

                    var headerFooter1 = new HeaderFooter();

                    var oddHeader1 = new OddHeader();
                    oddHeader1.Text = reportTitle;

                    var oddFooter1 = new OddFooter();
                    oddFooter1.Text = organization.Name;

                    headerFooter1.Append(oddHeader1);
                    headerFooter1.Append(oddFooter1);


                    // ------------------------------------------------------------
                    // Build the worksheet.
                    // ------------------------------------------------------------

                    worksheet1.Append(sheetViews1);
                    worksheet1.Append(columns1);
                    worksheet1.Append(sheetData1);

                    // Cannot add zero merge cells.
                    if (mergeCells1.Count != 0)
                    {
                        worksheet1.Append(mergeCells1);
                    }

                    worksheet1.Append(pageMargins1);
                    worksheet1.Append(pageSetup1);
                    worksheet1.Append(headerFooter1);

                    worksheet1.Append(rowBreaks1);

                    worksheetPart1.Worksheet = worksheet1;


                    // ------------------------------------------------------------
                    // Add Sheets to the Workbook.
                    // ------------------------------------------------------------

                    var sheets = workbookPart1.Workbook.AppendChild(new Sheets());


                    // ------------------------------------------------------------
                    // Append a new worksheet and associate it with the workbook.
                    // ------------------------------------------------------------

                    var sheet = new Sheet()
                    {
                        Id      = workbookPart1.GetIdOfPart(worksheetPart1),
                        SheetId = 1,
                        Name    = "Report"
                    };
                    sheets.Append(sheet);


                    // Save and close the document.
                    workbookPart1.Workbook.Save();
                    document.Close();

                    return(stream.ToArray());
                }
        }
        public byte[] Build(User currentUser, DateTime min, DateTime max, string filterUserScope, int[] filterUserIds, string filterProjectScope, int[] filterProjectIds)
        {
            var organization = db.Organizations.Find(currentUser.OrganizationId);

            var reportTitle = $"TIME CARDS BY PROJECT {min.ToString("M/d/yyyy")} thru {max.ToString("M/d/yyyy")} GENERATED {DateTime.Now.ToString("ddd, MMM d, yyyy h:mm:ss tt").ToUpperInvariant()}";

            using (var stream = new MemoryStream())
                using (var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
                {
                    // ------------------------------------------------------------
                    // Add document properties.
                    // ------------------------------------------------------------

                    document.PackageProperties.Creator = "BRIZBEE";
                    document.PackageProperties.Created = DateTime.UtcNow;


                    // ------------------------------------------------------------
                    // Add a WorkbookPart to the document.
                    // ------------------------------------------------------------

                    var workbookPart1 = document.AddWorkbookPart();
                    workbookPart1.Workbook = new Workbook();


                    // ------------------------------------------------------------
                    // Apply stylesheet.
                    // ------------------------------------------------------------

                    var workStylePart1 = workbookPart1.AddNewPart <WorkbookStylesPart>();
                    workStylePart1.Stylesheet = Stylesheets.Common();;


                    // ------------------------------------------------------------
                    // Add a WorksheetPart to the WorkbookPart.
                    // ------------------------------------------------------------

                    var worksheetPart1 = workbookPart1.AddNewPart <WorksheetPart>();
                    var worksheet1     = new Worksheet();
                    var rowBreaks1     = new RowBreaks()
                    {
                        Count = 0, ManualBreakCount = 0
                    };
                    var mergeCells1 = new MergeCells()
                    {
                        Count = 0
                    };
                    var sheetData1 = new SheetData();


                    // ------------------------------------------------------------
                    // Collect the users based on the filters.
                    // ------------------------------------------------------------

                    List <User> users;
                    if (filterUserScope.ToUpperInvariant() == "SPECIFIC")
                    {
                        users = db.Users
                                .Where(u => u.OrganizationId == currentUser.OrganizationId)
                                .Where(u => u.IsDeleted == false)
                                .Where(u => filterUserIds.Contains(u.Id))
                                .OrderBy(u => u.Name)
                                .ToList();
                    }
                    else
                    {
                        users = db.Users
                                .Where(u => u.OrganizationId == currentUser.OrganizationId)
                                .Where(u => u.IsDeleted == false)
                                .OrderBy(u => u.Name)
                                .ToList();
                    }


                    // ------------------------------------------------------------
                    // Collect the time cards based on the filters.
                    // ------------------------------------------------------------

                    IQueryable <TimesheetEntry> timeCardsQueryable = db.TimesheetEntries
                                                                     .Include(t => t.Task.Job.Customer)
                                                                     .Include(t => t.User)
                                                                     .Where(t => t.User.OrganizationId == currentUser.OrganizationId)
                                                                     .Where(t => t.User.IsDeleted == false)
                                                                     .Where(t => DbFunctions.TruncateTime(t.EnteredAt) >= min.Date)
                                                                     .Where(t => DbFunctions.TruncateTime(t.EnteredAt) <= max.Date);

                    // Optionally filter projects.
                    if (filterProjectScope.ToUpperInvariant() == "SPECIFIC")
                    {
                        timeCardsQueryable = timeCardsQueryable
                                             .Where(t => filterProjectIds.Contains(t.Task.JobId));
                    }

                    var timeCards = timeCardsQueryable.ToList();

                    var projectIds = timeCards
                                     .OrderBy(t => t.Task.Job.Number)
                                     .GroupBy(t => t.Task.JobId)
                                     .Select(g => g.Key)
                                     .ToList();


                    // ------------------------------------------------------------
                    // Loop each project.
                    // ------------------------------------------------------------

                    var rowIndex = (uint)1;
                    foreach (var projectId in projectIds)
                    {
                        var project        = db.Jobs.Find(projectId);
                        var groupedTaskIds = timeCards
                                             .GroupBy(t => t.TaskId)
                                             .Select(g => g.Key)
                                             .ToList();
                        var timeCardsForProject = timeCards
                                                  .Where(t => groupedTaskIds.Contains(t.TaskId))
                                                  .ToList();


                        // ------------------------------------------------------------
                        // Header for project cell.
                        // ------------------------------------------------------------

                        var rowProject = new Row()
                        {
                            RowIndex = rowIndex, Height = 22D, CustomHeight = true, Spans = new ListValue <StringValue>()
                            {
                                InnerText = "1:1"
                            }, StyleIndex = 4U, CustomFormat = true
                        };

                        var cellProject = new Cell()
                        {
                            CellReference = $"A{rowIndex}", StyleIndex = 4U, DataType = CellValues.String, CellValue = new CellValue($"Project {project.Number} - {project.Name} for Customer {project.Customer.Number} - {project.Customer.Name}")
                        };
                        rowProject.Append(cellProject);

                        sheetData1.Append(rowProject);

                        // Merge the user name across the row.
                        var mergeCell0 = new MergeCell()
                        {
                            Reference = $"A{rowIndex}:E{rowIndex}"
                        };
                        mergeCells1.Append(mergeCell0);
                        mergeCells1.Count++;

                        rowIndex++;


                        // ------------------------------------------------------------
                        // Loop each date.
                        // ------------------------------------------------------------

                        var dates = timeCards
                                    .GroupBy(t => t.EnteredAt.Date)
                                    .Select(g => g.Key)
                                    .OrderBy(g => g)
                                    .ToList();
                        foreach (var date in dates)
                        {
                            var timeCardsForProjectAndDate = timeCards
                                                             .Where(t => t.EnteredAt.Date == date.Date)
                                                             .Where(t => groupedTaskIds.Contains(t.TaskId))
                                                             .OrderBy(t => t.EnteredAt)
                                                             .ToList();

                            if (!timeCardsForProjectAndDate.Any())
                            {
                                continue;
                            }

                            rowIndex++;


                            // ------------------------------------------------------------
                            // Header for date cell.
                            // ------------------------------------------------------------

                            var rowDate = new Row()
                            {
                                RowIndex = rowIndex, Height = 22D, CustomHeight = true, Spans = new ListValue <StringValue>()
                                {
                                    InnerText = "1:1"
                                }, StyleIndex = 4U, CustomFormat = true
                            };

                            var cellDate = new Cell()
                            {
                                CellReference = $"A{rowIndex}", StyleIndex = 4U, DataType = CellValues.String, CellValue = new CellValue(date.ToString("D"))
                            };
                            rowDate.Append(cellDate);

                            sheetData1.Append(rowDate);

                            // Merge the date across the row.
                            var mergeCell1 = new MergeCell()
                            {
                                Reference = $"A{rowIndex}:E{rowIndex}"
                            };
                            mergeCells1.Append(mergeCell1);
                            mergeCells1.Count++;

                            rowIndex++;


                            // ------------------------------------------------------------
                            // Headers for time cards cells.
                            // ------------------------------------------------------------

                            var rowHeaders = new Row()
                            {
                                RowIndex = rowIndex, Height = 16D, CustomHeight = true, StyleIndex = 1U, CustomFormat = true
                            };

                            // Task Number
                            var cellTaskNumberHeader = new Cell()
                            {
                                CellReference = $"A{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("#")
                            };
                            rowHeaders.Append(cellTaskNumberHeader);

                            // Task Name
                            var cellTaskNameHeader = new Cell()
                            {
                                CellReference = $"B{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("Task")
                            };
                            rowHeaders.Append(cellTaskNameHeader);

                            // User Name
                            var cellUserNameHeader = new Cell()
                            {
                                CellReference = $"C{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("User")
                            };
                            rowHeaders.Append(cellUserNameHeader);

                            // Notes
                            var cellNotesHeader = new Cell()
                            {
                                CellReference = $"D{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("Notes")
                            };
                            rowHeaders.Append(cellNotesHeader);

                            // Total
                            var cellTotalHeader = new Cell()
                            {
                                CellReference = $"E{rowIndex}", DataType = CellValues.String, StyleIndex = 2U, CellValue = new CellValue("Total")
                            };
                            rowHeaders.Append(cellTotalHeader);

                            sheetData1.Append(rowHeaders);

                            rowIndex++;


                            // ------------------------------------------------------------
                            // Time cards cells.
                            // ------------------------------------------------------------

                            foreach (var timeCard in timeCardsForProjectAndDate)
                            {
                                var rowTimeCard = new Row()
                                {
                                    RowIndex = rowIndex, Height = 16D, CustomHeight = true
                                };

                                // Task Number
                                var cellTaskNumber = new Cell()
                                {
                                    CellReference = $"A{rowIndex}", DataType = CellValues.Number, StyleIndex = 6U, CellValue = new CellValue(timeCard.Task.Number)
                                };
                                rowTimeCard.Append(cellTaskNumber);

                                // Task Name
                                var cellTaskName = new Cell()
                                {
                                    CellReference = $"B{rowIndex}", DataType = CellValues.String, StyleIndex = 6U, CellValue = new CellValue(timeCard.Task.Name)
                                };
                                rowTimeCard.Append(cellTaskName);

                                // User Name
                                var cellUserName = new Cell()
                                {
                                    CellReference = $"C{rowIndex}", DataType = CellValues.String, StyleIndex = 6U, CellValue = new CellValue(timeCard.User.Name)
                                };
                                rowTimeCard.Append(cellUserName);

                                // Notes
                                var cellNotes = new Cell()
                                {
                                    CellReference = $"D{rowIndex}", DataType = CellValues.String, StyleIndex = 6U, CellValue = new CellValue(timeCard.Notes)
                                };
                                rowTimeCard.Append(cellNotes);

                                // Calculate the total
                                var total = Math.Round((double)timeCard.Minutes / 60, 2).ToString("0.00");

                                // Total
                                var cellTotal = new Cell()
                                {
                                    CellReference = $"E{rowIndex}", DataType = CellValues.Number, StyleIndex = 5U, CellValue = new CellValue(total)
                                };
                                rowTimeCard.Append(cellTotal);

                                sheetData1.Append(rowTimeCard);

                                rowIndex++;
                            }

                            // ------------------------------------------------------------
                            // Cells for date total.
                            // ------------------------------------------------------------

                            var rowDateTotal = new Row()
                            {
                                RowIndex = rowIndex, Height = 16D, CustomHeight = true, StyleIndex = 1U, CustomFormat = true
                            };

                            // Header Cell
                            var cellDateFormatted = new Cell()
                            {
                                CellReference = $"A{rowIndex}", DataType = CellValues.String, StyleIndex = 2U, CellValue = new CellValue("Daily Total")
                            };
                            rowDateTotal.Append(cellDateFormatted);

                            // Calculate the total
                            double dailyTotalMinutes = 0;
                            foreach (var timeCard in timeCardsForProjectAndDate)
                            {
                                dailyTotalMinutes += timeCard.Minutes;
                            }
                            var dailyTotal = Math.Round(dailyTotalMinutes / 60, 2).ToString("0.00");

                            // Total Cell
                            var cellDateTotal = new Cell()
                            {
                                CellReference = $"E{rowIndex}", DataType = CellValues.Number, StyleIndex = 2U, CellValue = new CellValue(dailyTotal)
                            };
                            rowDateTotal.Append(cellDateTotal);

                            sheetData1.Append(rowDateTotal);

                            // Merge the date across the row.
                            var mergeCell3 = new MergeCell()
                            {
                                Reference = $"A{rowIndex}:D{rowIndex}"
                            };
                            mergeCells1.Append(mergeCell3);
                            mergeCells1.Count++;

                            rowIndex++;
                        }


                        // ------------------------------------------------------------
                        // Cells for project total.
                        // ------------------------------------------------------------

                        var rowTotal = new Row()
                        {
                            RowIndex = rowIndex, Height = 16D, CustomHeight = true, StyleIndex = 1U, CustomFormat = true
                        };

                        // Header Cell
                        var cellProjectName = new Cell()
                        {
                            CellReference = $"A{rowIndex}", DataType = CellValues.String, StyleIndex = 2U, CellValue = new CellValue($"Total for Project {project.Number} - {project.Name}")
                        };
                        rowTotal.Append(cellProjectName);

                        // Calculate the total
                        double projectTotalMinutes = 0;
                        foreach (var timeCard in timeCardsForProject)
                        {
                            projectTotalMinutes += timeCard.Minutes;
                        }
                        var projectTotal = Math.Round(projectTotalMinutes / 60, 2).ToString("0.00");

                        // Total Cell
                        var cellProjectTotal = new Cell()
                        {
                            CellReference = $"E{rowIndex}", DataType = CellValues.Number, StyleIndex = 2U, CellValue = new CellValue(projectTotal)
                        };
                        rowTotal.Append(cellProjectTotal);

                        sheetData1.Append(rowTotal);

                        // Merge the project name across the row.
                        var mergeCell4 = new MergeCell()
                        {
                            Reference = $"A{rowIndex}:D{rowIndex}"
                        };
                        mergeCells1.Append(mergeCell4);
                        mergeCells1.Count++;

                        rowIndex++;


                        // ------------------------------------------------------------
                        // Add a page break.
                        // ------------------------------------------------------------

                        var rowBreak1 = new Break()
                        {
                            Id = rowIndex, Max = 16383U, ManualPageBreak = true
                        };
                        rowBreaks1.Append(rowBreak1);
                        rowBreaks1.ManualBreakCount++;
                        rowBreaks1.Count++;

                        rowIndex++;
                    }


                    // ------------------------------------------------------------
                    // Custom column width.
                    // ------------------------------------------------------------

                    var columns1 = new Columns();

                    var column1 = new Column()
                    {
                        Min = 1U, Max = 1U, Width = 10D, CustomWidth = true
                    };
                    var column2 = new Column()
                    {
                        Min = 2U, Max = 2U, Width = 28D, CustomWidth = true
                    };
                    var column3 = new Column()
                    {
                        Min = 3U, Max = 3U, Width = 28D, CustomWidth = true
                    };
                    var column4 = new Column()
                    {
                        Min = 4U, Max = 4U, Width = 40D, CustomWidth = true
                    };
                    var column5 = new Column()
                    {
                        Min = 5U, Max = 5U, Width = 10D, CustomWidth = true
                    };

                    columns1.Append(column1);
                    columns1.Append(column2);
                    columns1.Append(column3);
                    columns1.Append(column4);
                    columns1.Append(column5);


                    // ------------------------------------------------------------
                    // Sheet Views.
                    // ------------------------------------------------------------

                    var sheetViews1 = new SheetViews();

                    var sheetView1 = new SheetView()
                    {
                        ShowGridLines = true, TabSelected = true, ZoomScaleNormal = 100U, WorkbookViewId = 0U
                    };
                    var selection1 = new Selection()
                    {
                        ActiveCell = "A1", SequenceOfReferences = new ListValue <StringValue>()
                        {
                            InnerText = "A1"
                        }
                    };

                    sheetView1.Append(selection1);
                    sheetViews1.Append(sheetView1);


                    // ------------------------------------------------------------
                    // Sheet Format.
                    // ------------------------------------------------------------

                    var sheetFormatProperties1 = new SheetFormatProperties()
                    {
                        DefaultRowHeight = 16D, DyDescent = 0.35D
                    };


                    // ------------------------------------------------------------
                    // Page Setup.
                    // ------------------------------------------------------------

                    var pageMargins1 = new PageMargins()
                    {
                        Left = 0.5D, Right = 0.5D, Top = 0.5D, Bottom = 0.5D, Header = 0.3D, Footer = 0.3D
                    };
                    var pageSetup1 = new PageSetup()
                    {
                        Orientation = OrientationValues.Landscape
                    };


                    // ------------------------------------------------------------
                    // Header and Footer.
                    // ------------------------------------------------------------

                    var headerFooter1 = new HeaderFooter();

                    var oddHeader1 = new OddHeader();
                    oddHeader1.Text = reportTitle;

                    var oddFooter1 = new OddFooter();
                    oddFooter1.Text = organization.Name;

                    headerFooter1.Append(oddHeader1);
                    headerFooter1.Append(oddFooter1);


                    // ------------------------------------------------------------
                    // Build the worksheet.
                    // ------------------------------------------------------------

                    worksheet1.Append(sheetViews1);
                    worksheet1.Append(columns1);
                    worksheet1.Append(sheetData1);

                    // Cannot add zero merge cells.
                    if (mergeCells1.Count != 0)
                    {
                        worksheet1.Append(mergeCells1);
                    }

                    worksheet1.Append(pageMargins1);
                    worksheet1.Append(pageSetup1);
                    worksheet1.Append(headerFooter1);

                    worksheet1.Append(rowBreaks1);

                    worksheetPart1.Worksheet = worksheet1;


                    // ------------------------------------------------------------
                    // Add Sheets to the Workbook.
                    // ------------------------------------------------------------

                    var sheets = workbookPart1.Workbook.AppendChild(new Sheets());


                    // ------------------------------------------------------------
                    // Append a new worksheet and associate it with the workbook.
                    // ------------------------------------------------------------

                    var sheet = new Sheet()
                    {
                        Id      = workbookPart1.GetIdOfPart(worksheetPart1),
                        SheetId = 1,
                        Name    = "Report"
                    };
                    sheets.Append(sheet);


                    // Save and close the document.
                    workbookPart1.Workbook.Save();
                    document.Close();

                    return(stream.ToArray());
                }
        }