I have this code in ASP.NET Core Web API. The user can use search query and then export to excel or just export to excel straight. It has maximum of ten (10) records per page.
However, I observed that only the current page (not more than 10 records) is exported to excel. Even if the Search query gives 3 pages with 30 records or as the case may be.
Kindly help resolve this.
MAIN CODE:
Models:
PagingDto:
public class PagingDto
{
public int PageNumber { get; set; }
public int PageSize { get; set; }
}
PagingFilter:
public class PagingFilter : PagingDto
{
public string SearchQuery { get; set; }
public string SortBy { get; set; }
public bool IsSortAscending { get; set; }
public DateTime? StartDate { get; set; }
public DateTime? EndDate { get; set; }
}
PageResult:
public class PageResult<T>
{
public T PageItems { get; set; }
public int CurrentPage { get; set; }
public int PageSize { get; set; }
public int NumberOfPages { get; set; }
public int TotalRecord { get; set; }
}
Response:
public class Response<T>
{
public T Data { get; set; }
public bool Successful { get; set; }
public string Message { get; set; }
public int StatusCode { get; set; }
public string ResponseCode { get; set; }
public string ResponseDescription { get; set; }
public Response(int statusCode, bool success, string msg, T data, EnumResponseCodes responseCode, string responseDescription)
{
Data = data;
Successful = success;
StatusCode = statusCode;
Message = msg;
ResponseCode = responseCode.GetStringValue();
ResponseDescription = responseDescription;
}
public Response()
{
}
public static Response<T> Success(string successMessage, T data, EnumResponseCodes responseCode, int statusCode = 200)
{
return new Response<T> {
Successful = true,
Message = successMessage,
Data = data,
StatusCode = statusCode,
ResponseCode = responseCode.GetStringValue(),
ResponseDescription = responseCode.ToString()
};
}
public override string ToString() => JsonConvert.SerializeObject(this);
}
Repository:
GetMandateQueryAsync:
public async Task<IQueryable<Mandate>> GetMandateQueryAsync(PagingFilter filter)
{
try
{
var userName = _currentUserService.UserName;
var getUser = await _userManager.FindByNameAsync(userName);
var loggedUserRole = await _userManager.GetRolesAsync(getUser);
var loggedUserRoleName = loggedUserRole[0].ToString();
if (loggedUserRoleName == "Merchant")
{
merchantId = _dbContext.Merchants.Where(u => u.User.UserName == userName).Select(m => m.Id).FirstOrDefault();
}
else
{
merchantId = _dbContext.MerchantUsers.Where(u => u.User.UserName == userName).Select(m => m.MerchantId).FirstOrDefault();
}
IQueryable<Mandate> query = _dbContext.Mandates
.Where(x => x.MerchantId == merchantId)
.Include(x => x.MandateDetails)
.Include(x => x.MandateApproval)
.Include(x => x.MandateCancellationRequest)
.Include(x => x.Merchant)
.ThenInclude(m => m.User);
// Apply search filtering
if (!string.IsNullOrEmpty(filter.SearchQuery))
{
query = query
.Where(m =>
m.Merchant.MerchantName.ToLower().Contains(filter.SearchQuery) ||
m.ReferenceNumber.ToLower().Contains(filter.SearchQuery) ||
m.DrAccountNumber.Contains(filter.SearchQuery) ||
(m.PaymentFrequency == PaymentFrequency.Monthly ? "monthly"
: m.PaymentFrequency == PaymentFrequency.Quarterly ? "quarterly"
: m.PaymentFrequency == PaymentFrequency.BiAnnual ? "biannual"
: m.PaymentFrequency == PaymentFrequency.Yearly ? "yearly"
: "unknown"
).Contains(filter.SearchQuery.ToLower()) ||
m.BankBranch.BranchName.ToLower().Contains(filter.SearchQuery) ||
m.Amount.ToString().Contains(filter.SearchQuery) ||
(m.MandateApprovalStatus == MandateApprovalStatus.PendingApproval ? "pending approval"
: m.MandateApprovalStatus == MandateApprovalStatus.Declined ? "declined"
: m.MandateApprovalStatus == MandateApprovalStatus.Approved ? "approved"
: "unknown"
).Contains(filter.SearchQuery.ToLower())
);
}
// Apply date range filtering
if (filter.StartDate.HasValue && filter.EndDate.HasValue)
{
query = query.Where(m => m.CreatedAt >= filter.StartDate.Value && m.CreatedAt <= filter.EndDate.Value);
}
// Apply sorting
if (!string.IsNullOrEmpty(filter.SortBy))
{
switch (filter.SortBy)
{
case "ReferenceNumber":
query = filter.IsSortAscending ? query.OrderBy(m => m.ReferenceNumber) : query.OrderByDescending(m => m.ReferenceNumber);
break;
case "DrAccountNumber":
query = filter.IsSortAscending ? query.OrderBy(m => m.DrAccountNumber) : query.OrderByDescending(m => m.DrAccountNumber);
break;
case "BranchName":
query = filter.IsSortAscending ? query.OrderBy(m => m.BankBranch.BranchName) : query.OrderByDescending(m => m.BankBranch.BranchName);
break;
case "Amount":
query = filter.IsSortAscending ? query.OrderBy(m => m.Amount) : query.OrderByDescending(m => m.Amount);
break;
case "MandateApprovalStatus":
query = filter.IsSortAscending ? query.OrderBy(m => m.MandateApprovalStatus) : query.OrderByDescending(m => m.MandateApprovalStatus);
break;
case "CreatedAt":
query = filter.IsSortAscending ? query.OrderBy(m => m.CreatedAt) : query.OrderByDescending(m => m.CreatedAt);
break;
}
}
else
{
query = query.OrderByDescending(m => m.CreatedAt);
}
return query;
}
catch (Exception ex)
{
throw new Exception("An error occurred while retrieving Mandates.", ex);
}
}
MerchantMandateService:
public async Task<Response<byte[]>> ExportMandateToExcelAsync(PagingFilter filter)
{
try
{
var auditReportsResponse = await GetMandateAsync(filter);
var auditReports = auditReportsResponse.Data.PageItems;
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
var excelPackage = new ExcelPackage();
var worksheet = excelPackage.Workbook.Worksheets.Add("Mandate Reports");
// Title
var title = "Reports";
var titleCell = worksheet.Cells["A1:K1"]; // Adjust the range based on the number of columns
titleCell.Merge = true;
titleCell.Value = title;
titleCell.Style.Font.Size = 16;
titleCell.Style.Font.Bold = true;
titleCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
titleCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
titleCell.Style.Fill.PatternType = ExcelFillStyle.Solid;
titleCell.Style.Fill.BackgroundColor.SetColor(Color.LightGray);
// Headers
var headers = new List<string>
{
"S.No.",
"Merchant Name",
"Reference Number",
"Customer Account No.",
"Branch",
"Payment Frequency",
"Start Date",
"End Date",
"Amount",
"Approval Status",
"Action Date"
};
// Set header values
for (int i = 0; i < headers.Count; i++)
{
worksheet.Cells[2, i + 1].Value = headers[i];
worksheet.Cells[2, i + 1].Style.Font.Bold = true;
worksheet.Cells[2, i + 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[2, i + 1].Style.Fill.BackgroundColor.SetColor(Color.LightGray);
}
// Populate data rows
var row = 3;
var serialNumber = 1;
foreach (var report in auditReports)
{
worksheet.Cells[row, 1].Value = serialNumber;
worksheet.Cells[row, 2].Value = report.Merchant.MerchantName;
worksheet.Cells[row, 3].Value = report.ReferenceNumber;
worksheet.Cells[row, 4].Value = report.DrAccountNumber;
worksheet.Cells[row, 5].Value = report.BankBranch.BranchName;
worksheet.Cells[row, 6].Value = report.PaymentFrequency;
worksheet.Cells[row, 7].Value = report.StartDate.Date.ToString("dd-MMM-yyyy");
worksheet.Cells[row, 8].Value = report.EndDate.Date.ToString("dd-MMM-yyyy");
worksheet.Cells[row, 9].Value = report.Amount.ToString("N2");
worksheet.Cells[row, 10].Value = report.MandateApprovalStatus;
worksheet.Cells[row, 11].Value = report.CreatedAt?.ToString("dd-MMM-yyyy") ?? string.Empty;
row++;
serialNumber++;
}
// Auto-fit columns
worksheet.Cells.AutoFitColumns();
var excelBytes = excelPackage.GetAsByteArray();
return new Response<byte[]>()
{
StatusCode = (int)HttpStatusCode.OK,
Successful = true,
Data = excelBytes,
Message = "Successful.",
ResponseCode = EnumResponseCodes.SUCCESS.GetStringValue(),
ResponseDescription = EnumResponseCodes.SUCCESS.ToString()
};
}
catch (Exception ex)
{
_logger.Error(ex, "An error occurred.");
return new Response<byte[]>()
{
StatusCode = (int)HttpStatusCode.InternalServerError,
Successful = false,
Message = "An error occurred.",
ResponseCode = EnumResponseCodes.FAILED.GetStringValue(),
ResponseDescription = EnumResponseCodes.FAILED.ToString()
};
}
}
public async Task<Response<PageResult<IEnumerable<MandateListDto>>>> GetMandateAsync(PagingFilter filter)
{
try
{
var query = await _unitOfWork.MerchantMandates.GetMandateQueryAsync(filter);
var totalRecords = await query.CountAsync();
var item = await query.PaginationAsync<Mandate, MandateListDto>(filter.PageNumber, filter.PageSize, _mapper);
var pageResult = new PageResult<IEnumerable<MandateListDto>>
{
PageItems = item.PageItems, // Update this line
CurrentPage = filter.PageNumber,
PageSize = filter.PageSize,
NumberOfPages = (int)Math.Ceiling((double)totalRecords / filter.PageSize),
TotalRecord = totalRecords
};
return new Response<PageResult<IEnumerable<MandateListDto>>>()
{
StatusCode = (int)HttpStatusCode.OK,
Successful = true,
Data = pageResult,
Message = "All Mandates Retrieved Successfully",
ResponseCode = EnumResponseCodes.SUCCESS.GetStringValue(),
ResponseDescription = EnumResponseCodes.SUCCESS.ToString()
};
}
catch (Exception ex)
{
_logger.Error(ex, "An error occurred while retrieving Mandate reports.");
return new Response<PageResult<IEnumerable<MandateListDto>>>()
{
StatusCode = (int)HttpStatusCode.InternalServerError,
Successful = false,
Message = "An error occurred while retrieving Mandate reports.",
ResponseCode = EnumResponseCodes.FAILED.GetStringValue(),
ResponseDescription = EnumResponseCodes.FAILED.ToString()
};
}
}
Controller:
[HttpGet]
[Route(ApiRoutes.Merchant.GetMandates)]
public async Task<ActionResult> GetAllMandateAsync([FromQuery] PagingFilter filter, [FromQuery] bool exportToExcel = false)
{
if (exportToExcel)
{
var response = await _merchantMandateService.ExportMandateToExcelAsync(filter);
if (response.Successful)
{
var fileBytes = response.Data;
var fileName = $"MandateReports_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
return File(fileBytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
}
else
{
return StatusCode(response.StatusCode, response.Message);
}
}
var result = await _merchantMandateService.GetMandateAsync(filter);
return StatusCode(result.StatusCode, result);
}
