Báo cáo, thống kê có vai trò rất quan trọng trong 1 hệ thống. Việc xuất dữ liệu từ ứng dụng của bạn và chia sẻ chúng dưới định dạng Excel không chỉ là một yêu cầu phổ biến mà còn là một phần quan trọng của trải nghiệm người dùng. Trong bài viết này, chúng ta sẽ tìm hiểu cách thực hiện chức năng xuất Excel từ API của .NET Core, một giải pháp hiệu quả để tạo ra các bảng dữ liệu chuyên nghiệp.
Bài viết này sẽ hướng đến 2 chủ đề chính :
- Xuất Excel bằng cách sử dụng Template.
- Xuất Excel bằng cách tự vẽ Template.
Trước hết chúng ta sẽ đến chủ đề đầu tiên:
+ Bước 1 : Cài đặt thư viện: Thư viện ở đây chúng ta dùng đó là EPPlus.
Hoặc có thể dùng lệnh command sau :
PM> Install-Package EPPlus
Sau khi Install xong thì phải chuẩn bị 1 template bằng Excel để xuất dữ liệu ra trong file đó.
Ví dụ:
+ Bước 2: Sẽ sử dụng API GetAll để export data. Dự án mình đang làm và dùng CQRS Pattern để handle. Dưới đây là ví dụ, các bạn cùng tham khảo:
using MediatR;
using Microsoft.AspNetCore.Identity;
using Microsoft.EntityFrameworkCore;
using System.Linq.Dynamic.Core;
using WSM.Application.Enums;
using WSM.Application.Interfaces.Repositories.Device;
using WSM.Application.Interfaces.Repositories.MasterData;
using WSM.Application.Interfaces.Services;
using WSM.Domain.Constants;
using WSM.Domain.Entities.Identity;
using WSM.Domain.Wrappers;
namespace WSM.Application.Features.Devices.Queries.GetAll
{
public class GetAllDeviceQuery : GetAllDeviceParameter, IRequest<PaginatedResult<GetAllDevicesResponse>>
{
}
internal class GetAllDeviceQueryHandler : IRequestHandler<GetAllDeviceQuery, PaginatedResult<GetAllDevicesResponse>>
{
private readonly IDeviceRepository _deviceRepository;
private readonly IDeviceAllocationItemRepository _deviceAllocationItemRepository;
private readonly IDeviceAllocationRepository _deviceAllocationRepository;
private readonly IUploadService _uploadService;
private readonly IDeviceTypeRepository _deviceTypeRepository;
private readonly ISupplierRepository _supplierRepository;
private readonly IDepartmentRepository _departmentRepository;
private readonly ICurrentUserService _currentUserService;
private readonly UserManager _userManager;
private readonly IDeviceStatusRepository _deviceStatusRepository;
private readonly ISettingSystemRepository _settingSystemRepository;
private readonly ICapitalRepository _capitalRepository;
public GetAllDeviceQueryHandler(IDeviceRepository deviceRepository,
IUploadService uploadService,
IDeviceTypeRepository deviceTypeRepository,
ISupplierRepository supplierRepository,
IDepartmentRepository departmentRepository,
ICurrentUserService currentUserService,
UserManager userManager,
IDeviceStatusRepository deviceStatusRepository,
ISettingSystemRepository settingSystemRepository,
IDeviceAllocationItemRepository deviceAllocationItemRepository,
IDeviceAllocationRepository deviceAllocationRepository,
ICapitalRepository capitalRepository)
{
_deviceRepository = deviceRepository;
_uploadService = uploadService;
_deviceTypeRepository = deviceTypeRepository;
_supplierRepository = supplierRepository;
_departmentRepository = departmentRepository;
_currentUserService = currentUserService;
_userManager = userManager;
_deviceStatusRepository = deviceStatusRepository;
_settingSystemRepository = settingSystemRepository;
_deviceAllocationItemRepository = deviceAllocationItemRepository;
_deviceAllocationRepository = deviceAllocationRepository;
_capitalRepository = capitalRepository;
}
public async Task<PaginatedResult<GetAllDevicesResponse>> Handle(GetAllDeviceQuery request, CancellationToken cancellationToken)
{
// Keyword Search
if (request.Keyword != null)
request.Keyword = request.Keyword.Trim();
if (request.Serial != null)
request.Serial = request.Serial.Trim();
var dateTime = DateTime.Now;
// Query
var query = from x in _deviceRepository.Entities.Where(x => x.DataType == request.DataType)
join dt in _deviceTypeRepository.Entities.Where(x => !x.IsDeleted)
on x.DeviceTypeId equals dt.Id into leftJoinDeviceType
from deviceType in leftJoinDeviceType.DefaultIfEmpty()
join s in _supplierRepository.Entities.Where(x => !x.IsDeleted)
on x.SupplierId equals s.Id into leftJoinSupplier
from supplier in leftJoinSupplier.DefaultIfEmpty()
join sy in _deviceRepository.Entities.Where(x => x.DataType == request.DataType)
on x.ParentId equals sy.Id into leftJoinSystem
from system in leftJoinSystem.DefaultIfEmpty()
join st in _deviceStatusRepository.Entities.Where(x => !x.IsDeleted)
on x.DeviceStatusId equals st.Id into leftJoinStatus
from status in leftJoinStatus.DefaultIfEmpty()
join caption in _capitalRepository.Entities
on x.CapitalId equals caption.Id into leftJoinCapital
from cap in leftJoinCapital.DefaultIfEmpty()
let systemName = _deviceRepository.Entities.Where(a => a.Id == x.ParentId && !a.IsDeleted).FirstOrDefault()
let checkCodition = request.DataType == 2 ? x.DataType == (short)DataType.Devices && (x.ParentId == 0 || x.ParentId == null) : x.DataType == (short)DataType.System
where !x.IsDeleted
&& checkCodition
&& (string.IsNullOrEmpty(request.Keyword) || x.Name.ToLower().Contains(request.Keyword.ToLower()))
&& (string.IsNullOrEmpty(request.Serial) || x.SerialNumber.ToLower().Contains(request.Serial.ToLower()))
&& (request.SystemId == null || request.SystemId.Contains(systemName.Id))
&& (request.StatusId == null || request.StatusId == status.Id)
&& (request.DepartmentId == null || request.DepartmentId.Contains((int)x.DepartmentId))
&& (request.CapitalId == null || request.CapitalId.Contains(cap.Id))
&& (!request.ReceivedDate.HasValue || request.ReceivedDate.Value.Date.Equals(x.ReceivedDate.Value.Date))
&& (request.WarrantyStatus == null || request.WarrantyStatus.Value.Equals(x.WarrantyStatus))
&& (request.DeviceStatusPercent == null || request.DeviceStatusPercent.Value.Equals(x.DeviceStatusPercent))
&& (string.IsNullOrEmpty(request.UsageNote) || x.UsageNote.ToLower().Contains(request.UsageNote.ToLower()))
&& (string.IsNullOrEmpty(request.Manufacturer) || x.Manufacturer.ToLower().Contains(request.Manufacturer.ToLower()))
&& (string.IsNullOrEmpty(request.ProducingCountry) || x.ProducingCountry.ToLower().Contains(request.ProducingCountry.ToLower()))
&& (request.OriginalPrice == null || x.OriginalPrice == request.OriginalPrice)
&& (!request.CreatedOn.HasValue || request.CreatedOn.Equals(x.CreatedOn.Date))
&& (!request.WarrantyFromDate.HasValue || (x.WarrantyFromDate.Value.Date <= request.WarrantyFromDate.Value.Date && x.WarrantyToDate.Value.Date >= request.WarrantyFromDate.Value.Date))
&& (!request.WarrantyToDate.HasValue || (request.WarrantyToDate.Value.Date >= x.WarrantyFromDate.Value.Date && request.WarrantyToDate.Value.Date <= x.WarrantyToDate.Value.Date))
let department = _departmentRepository.Entities.FirstOrDefault(a => a.Id == x.DepartmentId && !x.IsDeleted)
let warrantyLevel = (bool)!x.WarrantyStatus || x.WarrantyStatus == null ? DeviceWarranty.NoWarranty : x.WarrantyToDate!.Value.Date <= dateTime.Date ? DeviceWarranty.Expired :
x.WarrantyToDate!.Value.Date <= dateTime.Date.AddMonths(1) ? DeviceWarranty.AboutOneMonth :
x.WarrantyToDate!.Value.Date <= dateTime.Date.AddMonths(3) ? DeviceWarranty.AboutThreeMonth : DeviceWarranty.StillLong
select new GetAllDevicesResponse
{
Id = x.Id,
AccessoriesIncluded = x.AccessoriesIncluded,
Depreciation = x.Depreciation,
DeviceManagementCode = x.DeviceManagementCode,
DeviceStatusPercent = x.DeviceStatusPercent,
MaintenanceContent = x.MaintenanceContent,
MaintenanceCycle = x.MaintenanceCycle,
MaintenanceRequired = x.MaintenanceRequired,
Manufacturer = x.Manufacturer,
OriginalPrice = x.OriginalPrice,
Name = x.Name,
ProducingCountry = x.ProducingCountry,
DeviceStatusContent = x.DeviceStatusContent,
QRLink = x.QRLink,
QRUrl = _uploadService.GetFullUrl(x.QRLink),
ReceivedDate = x.ReceivedDate,
SerialNumber = x.SerialNumber ?? string.Empty,
Specification = x.Specification,
SupplierId = x.SupplierId,
SupplierName = supplier.Name,
UsageNote = x.UsageNote,
WarrantyFromDate = x.WarrantyFromDate,
WarrantyStatus = x.WarrantyStatus,
WarrantyToDate = x.WarrantyToDate,
CreatedOn = x.CreatedOn,
DeviceTypeId = x.DeviceTypeId,
DeviceTypeName = deviceType.Name,
DepartmentId = x.DepartmentId,
StatusManagementId = x.StatusManagementId,
DepartmentName = department != null ? department.Name : "",
WarrantyStatusManagement = (int?)warrantyLevel,
YearOfUse = DateTime.Now.Year - x.ReceivedDate.Value.Year,
CirculationCode = x.CirculationCode,
Model = x.Model ?? string.Empty,
DateOfDecision = x.DateOfDecision,
DecisionNo = x.DecisionNo,
YearOfProducing = x.YearOfProducing,
DeviceStatusId = x.DeviceStatusId,
DeviceStatusName = status.Name,
StatusManagementSystemId = system.StatusManagementId,
ParentId = x.ParentId,
SystemName = systemName.Name != null ? systemName.Name : null,
AllocationDate = (from dai in _deviceAllocationItemRepository.Entities.Where(d => !d.IsDeleted && x.Id == (request.DataType == (short)DataType.Devices ? d.DeviceId : d.SystemId)).OrderByDescending(x=>x.CreatedOn).Take(1)
join da in _deviceAllocationRepository.Entities.Where(da => !da.IsDeleted && da.Status == (short)StaticVariable.UserApproveStatus.APPROVE)
on dai.DeviceAllocationId equals da.Id into leftDeviceAllocation
from da in leftDeviceAllocation.DefaultIfEmpty()
select (da != null) ? da.ReceivedDate : (DateTime?)null).FirstOrDefault(),
CapitalId = x.CapitalId,
CapitalName = cap.Name != null ? cap.Name : null
};
// Where and Order By
var data = query.Where(x => (!request.WarrantyLevel.HasValue || x.WarrantyStatusManagement == request.WarrantyLevel)
&& (!request.AllocationDate.HasValue || (x.AllocationDate != null && request.AllocationDate.Value.Month == x.AllocationDate.Value.Month && request.AllocationDate.Value.Year == x.AllocationDate.Value.Year))).OrderBy(request.OrderBy);
// Count total record
var totalRecord = data.Count();
List result;
// Pagination
if (!request.IsExport)
result = await data.Skip((request.PageNumber - 1) * request.PageSize).Take(request.PageSize).ToListAsync(cancellationToken);
else
result = await data.ToListAsync(cancellationToken);
return PaginatedResult.Success(result, totalRecord, request.PageNumber, request.PageSize);
}
}
} + Bước 3 : Viết API cho Export Excel:
using MediatR;
using Microsoft.AspNetCore.Hosting;
using OfficeOpenXml;
using WSM.Application.Enums;
using WSM.Application.Exceptions;
using WSM.Application.Features.Devices.Queries.GetAll;
namespace WSM.Application.Features.Devices.Queries.ExportDevices
{
public class ExportDeviceQuery : GetAllDeviceParameter, IRequest
{
}
internal class ExportDeviceQueryHandler : IRequestHandler
{
private readonly IMediator _mediator;
private readonly IHostingEnvironment _environment;
public ExportDeviceQueryHandler(IMediator mediator, IHostingEnvironment environment)
{
_mediator = mediator;
_environment = environment;
}
public async Task Handle(ExportDeviceQuery request, CancellationToken cancellationToken)
{
// Data được lấy từ List, sẽ handle với các điều kiện search, để xuất Excel ý muốn.
var devices = await _mediator.Send(new GetAllDeviceQuery()
{
DataType = (short)DataType.Devices,
OrderBy = request.OrderBy,
IsExport = request.IsExport,
AllocationDate = request.AllocationDate,
CapitalId = request.CapitalId,
CreatedOn = request.CreatedOn,
DepartmentId = request.DepartmentId,
DeviceStatusPercent = request.DeviceStatusPercent,
Keyword = request.Keyword,
Manufacturer = request.Manufacturer,
OriginalPrice = request.OriginalPrice,
PageNumber = request.PageNumber,
PageSize = request.PageSize,
ProducingCountry = request.ProducingCountry,
ReceivedDate = request.ReceivedDate,
Serial = request.Serial,
StatusId = request.StatusId,
SystemId = request.SystemId,
UsageNote = request.UsageNote,
WarrantyFromDate = request.WarrantyFromDate,
WarrantyLevel = request.WarrantyLevel,
WarrantyStatus = request.WarrantyStatus,
WarrantyToDate = request.WarrantyToDate,
}, cancellationToken);
var sWebRootFolder = !string.IsNullOrEmpty(_environment.WebRootPath) ? _environment.WebRootPath : _environment.ContentRootPath;
// Setting tên file khi lưu
var sFileName = $"Danh sách Thiết bị_{DateTime.Now.ToString("ddMMyyyyHHmm")}.xlsx";
// Template
var templatePath = "Devices.xlsx";
string templateDocument = Path.Combine(sWebRootFolder, "templates", "excel", templatePath);
FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, "export-files", sFileName));
if (file.Exists)
{
file.Delete();
file = new FileInfo(Path.Combine(sWebRootFolder, "export-files", sFileName));
}
// Config file Excel template
using (FileStream templateDocumentStream = File.OpenRead(templateDocument))
{
using (ExcelPackage package = new ExcelPackage(templateDocumentStream))
{
// Add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets["Sheet1"];
// Insert data employee data
var rowIndex = 6;
// Check nếu list không tồn tại dữ liệu nào thì sẽ throw Exception luôn.
if (devices.Data == null || !devices.Data.Any()) throw new ApiException("Thiết bị không tồn tại.");
// Bắt đầu STT là 1
var rowNumber = 1;
// set dữ liệu vào file excel
foreach (var item in devices.Data)
{
worksheet.Cells[rowIndex, 1].Value = rowNumber.ToString();
worksheet.Cells[rowIndex, 2].Value = item.Name;
worksheet.Cells[rowIndex, 3].Value = item.DeviceStatusName;
worksheet.Cells[rowIndex, 4].Value = item.DepartmentName ?? "-";
worksheet.Cells[rowIndex, 5].Value = item.DeviceManagementCode ?? "-";
worksheet.Cells[rowIndex, 6].Value = item.SerialNumber ?? "-";
worksheet.Cells[rowIndex, 7].Value = (item.ReceivedDate.HasValue ? item.ReceivedDate.Value.ToString("dd-MM-yyyy") : "-");
worksheet.Cells[rowIndex, 8].Value = item.WarrantyFromDate?.ToString("dd-MM-yyyy") ?? "-";
worksheet.Cells[rowIndex, 9].Value = item.WarrantyToDate?.ToString("dd-MM-yyyy") ?? "-";
worksheet.Cells[rowIndex, 10].Value = item.DeviceStatusPercent + "%" ?? "-";
worksheet.Cells[rowIndex, 11].Value = item.Manufacturer ?? "-";
worksheet.Cells[rowIndex, 12].Value = item.ProducingCountry ?? "-";
worksheet.Cells[rowIndex, 13].Value = item.Specification ?? "-";
worksheet.Cells[rowIndex, 14].Value = item.OriginalPrice ?? 0;
worksheet.Cells[rowIndex, 15].Value = item.CapitalName ?? "-";
worksheet.Cells[rowIndex, 16].Value = item.CreatedOn.ToString("dd-MM-yyyy");
worksheet.Cells[rowIndex, 17].Value = (item.AllocationDate.HasValue ? item.AllocationDate.Value.ToString("dd-MM-yyyy") : "-");
rowNumber++;
rowIndex++;
}
//Save file to .xlsx
await package.SaveAsAsync(file, cancellationToken);
}
}
return sFileName;
}
}
} + Bước 4: Tạo API Export, gọi lại function Export Excel ở trên và viết thêm 1 function để có thể Download File về.
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.FileProviders;
using WSM.Application.Features.Devices.Queries.ExportDevices;
using WSM.Application.Features.Devices.Queries.GetAll;
using WSM.Application.Shared.Permission;
using WSM.Domain.Wrappers;
namespace WSM.WebApi.Controllers.V1.Device
{
[Route("api/v{version:apiVersion}/device")]
public class DeviceController : BaseApiController<DeviceController>
{
private readonly IWebHostEnvironment _webHostEnvironment;
public DeviceController(IWebHostEnvironment webHostEnvironment)
{
_webHostEnvironment = webHostEnvironment;
}
/// <summary>
/// Get All Device
/// </summary>
/// <param name="parameter"></param>
/// <returns></returns>
[Authorize(Policy = Permissions.Devices.View)]
[HttpGet]
public async Task<ActionResult<PaginatedResult<GetAllDevicesResponse>>> GetAllDevice([FromQuery] GetAllDeviceParameter parameter)
{
return Ok(await Mediator.Send(new GetAllDeviceQuery
{
StatusId = parameter.StatusId,
SystemId = parameter.SystemId,
Serial = parameter.Serial,
IsExport = parameter.IsExport,
Keyword = parameter.Keyword,
OrderBy = parameter.OrderBy,
PageNumber = parameter.PageNumber,
PageSize = parameter.PageSize,
Filter = parameter.Filter,
DepartmentId = parameter.DepartmentId,
DataType = parameter.DataType,
WarrantyToDate = parameter.WarrantyToDate,
WarrantyFromDate = parameter.WarrantyFromDate,
UsageNote = parameter.UsageNote,
DeviceStatusPercent = parameter.DeviceStatusPercent,
Manufacturer = parameter.Manufacturer,
OriginalPrice = parameter.OriginalPrice,
ProducingCountry = parameter.ProducingCountry,
ReceivedDate = parameter.ReceivedDate,
WarrantyLevel = parameter.WarrantyLevel,
WarrantyStatus = parameter.WarrantyStatus,
CreatedOn = parameter.CreatedOn,
AllocationDate = parameter.AllocationDate,
CapitalId = parameter.CapitalId
}));
}
/// <summary>
/// Export Excel Devices
/// </summary>
/// <returns></returns>
[HttpGet("export-device")]
public async Task<IActionResult> ExportDevice([FromQuery] GetAllDeviceParameter parameter)
{
var sWebRootFolder = !string.IsNullOrEmpty(_webHostEnvironment.WebRootPath) ? _webHostEnvironment.WebRootPath : _webHostEnvironment.ContentRootPath;
var result = await Mediator.Send(new ExportDeviceQuery()
+ Bước 5 : Vậy là Oke rồi bây giờ đến việc test thôi.
Đây là list Thiết bị mà mình muốn export về file excel.
API mà mình đã xây dựng để export excel.
- Sau khi gọi API thì hiển thị status 200 và mục download file excel về.
- Kết quả sau khi download file.
Lưu ý: Với việc xuất Excel như này thì sẽ quá cứng nhắc, cũng chưa flexible được hệ thống, trải nghiệm người dùng cũng chưa cao. (Ví dụ : Muốn Export những cột cần thiết trong dữ liệu của mình, kết quả search như thế nào thì xuất như thế ấy.). Bài viết tới mình sẽ làm phần này nhé!
Vậy là đã hoàn thành, bài viết cũng đã dài. Hẹn gặp lại các bạn trong những topic trên. Cảm ơn các bạn đã xem, chúc các bạn thành công !
share bài firebase đi bạn kkk
Trả lờiXóa