EF Core Export to excel

Question

Hello there,

I am working on an ASP.NET Core Blazor Server app. I have Orders and Order Details data in SQL Server. I would like to export this master-detail relationship to excel. Any ideas on how can I manage this? (By the way, I am using Epplus for uploading excel in my application.)

Here are the entities;

public class Order  
    {  
        public int Id { get; set; }  
          
        [Required]  
        public DateTime OrderDateTime { get; set; }  
        [Required]  
        [MaxLength(250)]  
        public string CustomerName { get; set; }  
        [Required]  
        [MaxLength(250)]  
        public string VendorName { get; set; }  
        public string Status { get; set; }  
        [MaxLength(50)]  
        public string DoneBy { get; set; }  
        public List<OrderDetail> OrderDetails { get; set; }  
  
  
    }  
  
public class OrderDetail  
    {  
        public int Id { get; set; }  
          
        [Required]  
        [MaxLength(100)]  
        public string ProductCode { get; set; }  
        [Required]  
        [MaxLength(250)]  
        public string ProductName { get; set; }  
        [Required]  
        public int BuyQuantity { get; set; }  
        [Required]  
        public int SellQuantity { get; set; }  
        public double CostRatio { get; set; }  
        public double UnitCost { get; set; }  
        public double TotalBuyPrice { get; set; }  
        public double TotalSellPrice { get; set; }  
        [MaxLength(150)]  
        public string ShippingNumber { get; set; }  
        public string Status { get; set; }  
        [MaxLength(150)]  
        public string TrackingNumber { get; set; }  
        [MaxLength(400)]  
        public string Description { get; set; }  
        public int OrderId { get; set; }  
        public virtual Order Order { get; set; }  
    }  

Here is how I populate all orders;

public async Task<IEnumerable<Order>> GetAllOrders()  
{  
         return await this._db.Orders.Include("OrderDetails").ToListAsync();  
}  

Answer 1

Hi @Cenk ,

I am working on an ASP.NET Core Blazor Server app. I have Orders and Order Details data in SQL Server. I would like to export this master-detail relationship to excel. Any ideas on how can I manage this? (By the way, I am using Epplus for uploading excel in my application.)

Since you re using Epplus to upload the excel, you can also use the Epplus to export the data to an excel file.

You can create a Razor page in the Pages folder, and add the following code: 222932-sourcecode.txt

Then, in the Blazor component, add the download hyperlink:

<a class="btn btn-primary" href="/download">Export to Excel</a>  

After clicking the hyperlink, the excel file as below:

Besides, you can also refer to my reply in this thread, and use client side package or OpenXML to export to excel.

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,
Dillion

Answer 2

@Anonymous thank you for your reply, I am looking for something like this.

Answer 3

Hi @Anonymous ,

I changed your code a little bit but the excel file does NOt download. What could be the cause of the problem?

@page "/export"  
@using IMS.CoreBusiness  
@using IMS.UseCases.Interfaces.Order  
@using IMS.UseCases.Orders  
@using Microsoft.AspNetCore.Mvc  
@using OfficeOpenXml  
@inject ILogger<ExportOrders> Logger  
@inject IWebHostEnvironment Environment  
@inject IViewAllOrdersUseCase ViewAllOrdersUseCase  
  
<h1>Export Orders</h1>  
<br/>  
<style>  
    .btns {  
        padding: 5px;  
        width: calc(16% - 10px);  
    }  
</style>  
  
<p>  
    <button type="button" class="btn btn-primary btns" @onclick="ExportExcel">  
        Orders Excel Export  
    </button>  
</p>  
  
@code {  
    IEnumerable<Order> _orders = new List<Order>();  
  
    protected override async Task OnInitializedAsync()  
    {  
        _orders = await ViewAllOrdersUseCase.ExecuteAsync();  
    }  
  
    private async Task<FileStreamResult> ExportExcel()  
    {  
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;  
        var stream = new MemoryStream();  
  
        using (var package = new ExcelPackage(stream))  
        {  
            var workSheet = package.Workbook.Worksheets.Add("Sheet1");  
            workSheet.Cells.LoadFromCollection(_orders, true);  
            await package.SaveAsync();  
        }  
        stream.Position = 0;  
        string excelName = $"OrderList-{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx";  
  
        return new FileStreamResult(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")  
        {  
            FileDownloadName = excelName  
        };  
    }  
  
}