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 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>
Comments