find and solve || findandsolve.com
Please wait.....

Import (Insert) and Export Excel file data into Database ASP.Net Core MVC

Import (Insert) and Export Excel file data into Database ASP.Net Core MVC


In this article, we will learn how to read data from excel file using asp.net 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.

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



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



Or you should install EPPlus.Core as given below in asp.net 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);
        }
        [HttpPost]
        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())
            {
                model.XlsFile.CopyToAsync(stream);
                using (var package = new ExcelPackage(stream))
                {
                    package.SaveAs(file);
                  //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
                }
                else
                {
                   //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";
}
<style>
    h1 {
        padding: 50px;
        font-weight: 500;
    }
</style>
<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">
        </div>
        <br />
        <br />
        <br />
        <div class="form-group col-sm-6">
            <button type="submit" class="btn btn-primary">Read Excel File</button>
        </div>
    </form>
</div>
<div class="row">
    <h1>
        Display Excel File Records
    </h1>
    <table class="table table-active table-bordered" width="100">
        <thead>
            <tr>
                <th>First Name</th>
                <th>Last Name</th>
                <th>Email</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var item in Model.StaffInfoViewModel.StaffList)
            {
                <tr>
                    <td>@item.FirstName</td>
                    <td>@item.LastName</td>
                    <td>@item.Email</td>
                </tr>
            }
        </tbody>
    </table>
</div>


Sundar  Neupane

Sundar Neupane

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

Comments



Report Response