How to Export Data from Database Table to Excel File in Asp.net Core MVC

In this article, we will learn how to Export Data from Database to Excel File in Asp.net Core MVC . In previous article we had discussed How to Upload multiple files using Asp.Net Core MVC C#.

Prerequisites

• Download and install .Net Core 6.0 SDK from here
• Download and Install Microsoft Visual Studio 2022 from here
• Sql-Server

Create an ASP.NET Core MVC Project

Open visual studio, Go to File menu and click on New and select Project. Then new project window, select ASP.NET Core Web Application (Model-View-Controller) template.

Enter  project name and click create.

Install NuGet Packages

• Microsoft Entity Framework Core Sql Server.
• Microsoft Entity Framework Core Tools
• System.Data.OleDb
• ClosedXML

Configure appsettings.json

we have added the connection string.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "ConnectionStrings": {
    "sqlconnection": "Server=(local)\\MSSQL;Database=InventoryDB;Trusted_Connection=True;MultipleActiveResultSets=true"
    
  },
  "AllowedHosts": "*"
}

Create Model

Create Model class and DbContext Class.

//Customer.cs

using System.ComponentModel.DataAnnotations;

namespace Asp.netcore_Tutorials.Models
{
    public class Customer
    {
        [Key]

        public int id { get; set; }
        public string firstName { get; set; }
        public string lastName { get; set; }
        public string job { get; set; }
        public float amount { get; set; }
        public DateTime tdate { get; set; }
    }
}

//CustDbcontext.cs
using Microsoft.EntityFrameworkCore;

namespace Asp.netcore_Tutorials.Models
{
    public class CustDbcontext :DbContext 
    {

        public CustDbcontext(DbContextOptions<CustDbcontext> options):base(options)
        {

        }

        public virtual DbSet<Customer> Customers { get; set; }
    }
}

Create Interface and Concrete Class

Customerdetail concrete class will inherit ICustomer interface.

Interface ICustomer.cs

using System.Data;

namespace Asp.netcore_Tutorials.Repository
{
      public interface IExportCustomer
    {
        DataSet ExportCustomerDataTable();
        DataTable ExportCustomer();
    }
}

Concrete Class Customerdetail.cs

using Microsoft.AspNetCore.Http;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;

namespace Asp.netcore_Tutorials.Repository
{
     public class ExportCust : IExportCustomer
    {
        private IConfiguration _configuration;

        public ExportCust(IConfiguration configuration)
        {
            _configuration = configuration;
        }
        public DataTable ExportCustomer()
        {
            DataTable Custdatatable = ExportCustomerDataTable().Tables[0];
            return Custdatatable;

        }
        public  DataSet ExportCustomerDataTable()
        {
            DataSet ds = new DataSet();
            var sqlconn = _configuration.GetConnectionString("sqlconnection");

            string getcustomer = "SELECT * FROM Customers";
            using (SqlConnection scon = new SqlConnection(sqlconn))
            {
                using (SqlCommand cmd = new SqlCommand(getcustomer))
                {
                    cmd.Connection = scon;
                    using (SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd))
                    {
                        sqlAdapter.Fill(ds);

                    }
                }
            }
            return ds;
        }
    }
}

Configure Program.cs

using Asp.netcore_Tutorials.Models;
using Asp.netcore_Tutorials.Repository;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using System.Configuration;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddControllersWithViews();

builder.Services.AddDbContext<CustDbcontext>(conn => conn.UseSqlServer(builder.Configuration.GetConnectionString("sqlconnection")));  
builder.Services.AddScoped<ICustomer,CustomerDetail>();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Home/Error");
    // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
    app.UseHsts();
}

app.UseHttpsRedirection();

app.UseStaticFiles();

app.UseRouting();

app.UseAuthorization();

app.MapControllerRoute(
    name: "default",
    pattern: "{controller=Home}/{action=Index}/{id?}");

app.Run();

Migration

Add-Migration 'Initial-Create'
Update-Database

Create Controller

 using Asp.netcore_Tutorials.Repository;
using Microsoft.AspNetCore.Mvc;
using ClosedXML.Excel;
using System.Collections;

namespace Asp.netcore_Tutorials.Controllers
{
    public class ExportController : Controller
    {

        private readonly IExportCustomer _customer;
        public ExportController(IExportCustomer customer)
        {
            _customer= customer;
        }
        public IActionResult Index()
        {           
            return View();
        }

        [HttpPost]
        [AutoValidateAntiforgeryToken]
        public IActionResult ExporttoExcel()
        {
            var arraylist = _customer.ExportCustomer();

            using (XLWorkbook xl = new XLWorkbook())           
            {
                xl.Worksheets.Add(arraylist);  
                
                using (MemoryStream mstream = new MemoryStream())
                {
                    xl.SaveAs(mstream);
                    return File(mstream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Customer.xlsx");
                }
            } 
        } 
    }
}

Customize View Index

@{
    ViewData["Title"] = "Home Page";
}

<div class="row">
    <div class="col-5">

        <form method="post" enctype="multipart/form-data" asp-controller="Export" asp-action="ExporttoExcel">
             
            
            <div class="form-group">
                <div class="col-md-10">

                    <input class="btn btn-primary" type="submit" value="Export File" />

                </div>
            </div>
        </form>

         
    </div>
</div>