find and solve ||
Please wait.....

Import Export Excel data in Sql server database in ASP.NET Core

Download source code from here
Import Export Excel data in Sql server database in ASP.NET Core

In this article, we will learn how to read data from excel file using core C# language.
To achive this, firstly, we need to add or install  'EPPlus.Core' from cmd line or manually.
This  application reads the uploaded excel file data.Save uploaded  excel file an application root
 path and get this file for read data from saved root path an excel file.

To import and export Excel file data in an ASP.NET Core MVC application, you can use a library such as EPPlus or ClosedXML. These libraries provide classes and methods for reading and writing Excel files in .NET.

To import Excel data into a database, you can use the following steps:
  • Use EPPlus or ClosedXML to read the data from the Excel file into a data structure such as a DataTable or a list of objects.
  • Iterate through the data and insert the rows into the database using ADO.NET or an ORM such as Entity Framework Core.

1. Open your project and install 'EPPlus.Core' dll file as given below

When Click on Manage NuGet will show box as given below and enter EPPlus.Core and search

Or you should install EPPlus.Core as given below in core

I have created excel file with three column as given below

2. Add new different classess as given below
  • Create new class 'StaffInfoViewModel' for displaying read  excel file data In view page 'StaffInfoViewModel'
 public class StaffInfoViewModel
        public string FirstName { get; set;}
        public string LastName { get; set;}
        public string Email { get; set;}
        public List<StaffInfoViewModel> StaffList { get; set;}
        public StaffInfoViewModel()
            StaffList = new List<StaffInfoViewModel>();
  • Create New class 'FileUploadViewModel' for get uploaded excel file proparties.
   public class FileUploadViewModel
        public IFormFile XlsFile { get; set; }
       /*create StaffInfoViewModel  object because we need to add read
        excel data and mapping in StaffInfoViewModel*/
        public StaffInfoViewModel StaffInfoViewModel { get; set;}
        public FileUploadViewModel()//Create contractor
           //call StaffInfoViewModel  this object in contractor
            StaffInfoViewModel = new StaffInfoViewModel();

3. Open you Home Controller or any controller and and past this code as give below
using System;
using Microsoft.AspNetCore.Mvc;
using ExcelFileRead.Models;
using Microsoft.AspNetCore.Hosting;
using System.IO;
using OfficeOpenXml;
using System.Linq;
namespace ExcelFileRead.Controllers
    public class HomeController : Controller
        private readonly IHostingEnvironment _hostingEnvironment;
        public HomeController(IHostingEnvironment hostingEnvironment)
            _hostingEnvironment = hostingEnvironment;
        public ActionResult File()
            FileUploadViewModel model = new FileUploadViewModel();
            return View(model);
        public ActionResult File(FileUploadViewModel model)
            string rootFolder = _hostingEnvironment.WebRootPath;
            string fileName = Guid.NewGuid().ToString() + model.XlsFile.FileName;
            FileInfo file = new FileInfo(Path.Combine(rootFolder, fileName));
            using (var stream = new MemoryStream())
                using (var package = new ExcelPackage(stream))
                  //save excel file in your wwwroot folder and get this excel file from wwwroot
            //After save excel file in wwwroot and then
            using (ExcelPackage package = new ExcelPackage(file))
                ExcelWorksheet worksheet = package.Workbook.Worksheets.FirstOrDefault();
                if (worksheet == null)
                    //return or alert message here
                   //read excel file data and add data in  model.StaffInfoViewModel.StaffList
                    var rowCount = worksheet.Dimension.Rows;
                    for (int row = 2; row <= rowCount; row++)
                        model.StaffInfoViewModel.StaffList.Add(new StaffInfoViewModel
                            FirstName = (worksheet.Cells[row, 1].Value ?? string.Empty).ToString().Trim(),
                            LastName = (worksheet.Cells[row, 2].Value ?? string.Empty).ToString().Trim(),
                            Email = (worksheet.Cells[row, 3].Value ?? string.Empty).ToString().Trim(),
            //return same view and  pass view model 
            return View(model);
_hostingEnvironment.WebRootPath : Get Current project hosting path.
FileInfo(Path.Combine(rootFolder, fileName)) : Combination of excel file and root path.
Guid.NewGuid().ToString() : Create Random uinque string Id.

4.Open your 'File' view page which is alredy created in home conroller and past this code as given below.
@model ExcelFileRead.Models.FileUploadViewModel
    ViewData["Title"] = "File";
    Layout = "~/Views/Shared/_Layout.cshtml";
    h1 {
        padding: 50px;
        font-weight: 500;
<h1>File Upload Example</h1>
<div class="form-group row">
    <form asp-action="File" class="text-left form-validate" enctype="multipart/form-data">
        <div class="form-group col-sm-12">
            <div class="file-select-button" id="fileName">Upload Excel File</div>
            <input asp-for="XlsFile" type="file" id="chooseFile">
        <br />
        <br />
        <br />
        <div class="form-group col-sm-6">
            <button type="submit" class="btn btn-primary">Read Excel File</button>
<div class="row">
        Display Excel File Records
    <table class="table table-active table-bordered" width="100">
                <th>First Name</th>
                <th>Last Name</th>
            @foreach (var item in Model.StaffInfoViewModel.StaffList)

Related information

Sundar  Neupane

Sundar Neupane

I like working on projects with a team that cares about creating beautiful and usable interfaces.

If felt valuable to you, feel free to share it.


Report Response