根据模板导出EXCEL

/// <summary>
/// `导出赛事名单EXCEL`--车前贴
/// </summary>
/// <param name="batchId"></param>
/// <param name="cartType"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
[HttpGet]
public async Task<ActionResult> ExportBookingBatch(int batchId, CartType cartType,CancellationToken cancellationToken = default)
{
    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
    FileInfo tempFilePath = new FileInfo(System.IO.Path.Combine(AppContext.BaseDirectory, "ImportTemplate", "车前贴四人车模板.xlsx"));
    //读取excel表格
    using ExcelPackage package = new ExcelPackage(tempFilePath);
    using ExcelWorksheet sheet = package.Workbook.Worksheets.FirstOrDefault();

    var course = await Db.BaseSetting.FirstOrDefaultAsync();
    var bbc = Db.BookingBatch.Where(x => x.Id == batchId).Select(t => new { t.Name, t.EventId }).FirstOrDefault();
    var bbts = Db.BookingBatchTeeTime.Where(x => x.BookingBatchId == batchId && x.Deleted != true).OrderBy(t => t.SeqMin).ToList();
    var bbgs = Db.BookingBatchGuest.Where(x => x.BookingBatchId == batchId && x.Bkd.Deleted != true).Select(t => new { t.BkdId, t.Seq }).ToList();
    var bookingBatchGuests = new Dictionary<int, List<BookingBatchGuest>>();
    int maxgroup = (int)Math.Ceiling(bbgs.Max(t => t.Seq) / 4f);
    int a = 1; int b = 4;
    for (int n = 1; n <= maxgroup; n++)
    {
        bookingBatchGuests.Add(n, Db.BookingBatchGuest.Where(x => x.BookingBatchId == batchId && x.Bkd.Deleted != true && x.Seq >= a && x.Seq <= b).OrderBy(t => t.Seq).ToList());
        a += 4;
        b += 4;
    }
    var courseHoles = Db.CourseHole.ToArray();
    var bkds = Db.BookingDetail.Where(t => t.Deleted != true && bbgs.Select(x => x.BkdId).Contains(t.Id)).ToList();

    //调试图片
    //string imgSrc = "https://file.baidu.cn/Temp/Image/202403211117464679.jpg";
    //MemoryStream imageData = new System.IO.MemoryStream(await DownloadImageAsByteArray(imgSrc));
    //二维码
    var eventInfo = await _golfEventService.GetEventSharePosterInfo(bbc.EventId.Value);
    GetEventeQrCodeRequest getEventeQrCodeRequest = new GetEventeQrCodeRequest
    {
        CourseId = eventInfo.CourseId.ToString().ToLower(),
        BookingBatchId = batchId,
        CreateUserId = User.GetId(),
    };
    MemoryStream imageData = new System.IO.MemoryStream(_eventeQrCodeApiClient.GetEventeQrCode(getEventeQrCodeRequest).QrCode.ToArray());  
    if (cartType == CartType.Four)
    {
        int i = 0; // 组计数器
        int c = 0, rows = 12; // 当前处理的批次计数器 //模板表格行数
        foreach (var item in bookingBatchGuests)
        {
            int? hole = null;
            var bbt = bbts.Where(t => t.SeqMin <= item.Value.FirstOrDefault()?.Seq && t.SeqMax >= item.Value.FirstOrDefault()?.Seq)?.FirstOrDefault();
            if (bbt != null)
            {
                hole = courseHoles.Where(x => x.Id == bbt.CourseHoleId).Select(t => t.Num)?.FirstOrDefault();
            }
            var bkdids = item.Value.Select(t => t.BkdId).ToList();
            var bkdList = bkds.Where(t => t.Deleted != true && bkdids.Contains(t.Id)).ToList();
            //基本信息
            sheet.Cells[2 + (c * rows), 1].Value = $"{bbc.Name}";
            sheet.Cells[4 + (c * rows), 1].Value = $"{course.CourseName}";
            sheet.Cells[4 + (c * rows), 14].Value = $"{++i}组";
            sheet.Cells[6 + (c * rows), 2].Value = $"{hole}洞 第{bbt?.GroupNum.ToString() ?? new string('\u00A0', 8)}组 {bbt?.Teetime}";
            sheet.Cells[12 + (c * rows), 3].Value = $"扫码查看成绩";
            //插入图片
            var image = sheet.Drawings.AddPicture($"img{i}", imageData);
            image.SetSize(200, 200);
            image.SetPosition(7 + (c * rows), 0, 1, 200);

            int d = 0;// 当前击球人计数器
            foreach (var bkd in bkdList)
            {
                //击球人
                sheet.Cells[6 + (c * rows) + (d * 2), 9].Value = $"{bkd.GuestName}";
                d++;
            }
            c++;
        }
    }
    FileContentResult result = new FileContentResult(package.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    sheet.Cells.AutoFitColumns();
    var bb = await Db.BookingBatch.FindAsync(batchId);
    result.FileDownloadName = $"{bb.Name}-{cartType.Humanize()}-车前贴.xlsx";
    return result;
}