Greetings, fellow developers! In the world of web application development, the ability to import data is a skill that comes in handy, time and time again. And when it comes to data import, Excel files often steal the spotlight. If you’re working with ASP.NET Core, knowing how to efficiently import data using an Excel file is an invaluable skill to have.
In this blog post, we’re going to be your guide through the entire process of data import using an Excel file in ASP.NET Core. Whether you’re a seasoned pro or just taking your first steps with ASP.NET Core, this step-by-step guide will equip you with the knowledge and skills you need to excel in data import using an Excel file. So, without further ado, let’s dive in and master the art of data import in ASP.NET Core!
Before proceeding with the steps below, please ensure you have the latest version of the following installer.
Output preview
The image shown below would be the final output for this project. If you are interested in this project you can copy the source from the steps below.
I. Setup ASP.NET Web Application Project
- Let’s start by creating a new ASP.NET Web Application. Open your Visual Studio IDE and proceed with the steps below.
- Select File » New » Project.
- Choose ASP.NET Web Application(.NET Framework). Name the project ImportExcel to have the same namespace as my project. Click OK.
- Select MVC template and then check Configure for HTTPS.
- Lastly, Click on Create.
If this is your first time creating MVC project please refer to this article How to start with ASP.Net MVC.
2. Now we need to install required packages for this project. I have listed it all below.
- ExcelDataReader.Dataset
Use NuGet Package Manager to install this package. If this is your first time using this you can visit this article. Or follow the steps below.
- To add right-click on your project name, from your solution explorer. As shown below.
- Then select Manage NuGet Packages and search for ExcelDataReader.
- Install ExcelDataReader and ExcelDataReader.Dataset. When a green check is shown as the image below it means that the package is already installed.
II. Crete Index Action Result View
For this tutorial, we will be using the default HomeController and the index action result. We will be creating the view for the index method that will allow us to upload and display the record from an excel file. I have attached a screenshot below of the final view that the source code below will generate.
To create an index view, navigate to your default view. If you are using default template of MVC your default controller name is HomeController that makes Home » index.cshtml as your default view. Navigate to this file and replace all code inside this file with the code below.
View
@{
ViewBag.Title = "Home Page";
}
@model System.Data.DataTable
@using System.Data;
<script src="~/Scripts/jquery-1.10.2.js"></script>
<script>
function bs_input_file() {
$(".input-file").before(
function () {
if (!$(this).prev().hasClass('input-ghost')) {
var element = $("<input type='file' id='dataFile' name='upload' class='input-ghost' style='visibility:hidden; height:0'>");
element.attr("name", $(this).attr("name"));
element.change(function () {
element.next(element).find('input').val((element.val()).split('\\').pop());
});
$(this).find("button.btn-choose").click(function () {
element.click();
});
$(this).find("button.btn-reset").click(function () {
element.val(null);
$(this).parents(".input-file").find('input').val('');
});
$(this).find('input').css("cursor", "pointer");
$(this).find('input').mousedown(function () {
$(this).parents('.input-file').prev().click();
return false;
});
return element;
}
}
);
}
function clear() {
var input = $("#dataFile").val('');
};
$(function () {
clear();
bs_input_file();
});
</script>
@using (Html.BeginForm("Index", "Home", null, FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<div class="info2">
<h2>Browse File for uploading</h2>
@Html.AntiForgeryToken()
<div class="form-group" name="Fichier1">
<!-- COMPONENT START -->
<div class="form-group">
<div class="input-group input-file">
<span class="input-group-btn">
<button class="btn btn-default btn-choose" type="button">Choose</button>
</span>
<input type="text" class="form-control" placeholder='Choose a file...' />
</div>
</div>
@*<input type="file" id="dataFile" name="upload" />*@
</div>
<div class="form-group">
<input type="submit" value="Upload" class="btn btn-default" />
</div>
<div class="alert alert-danger">
@Html.ValidationSummary()
@*<strong></strong> asdasd*@
</div>
</div>
if (Model != null)
{
<table id="table1" class="table table-bordered table-hover">
<thead>
<tr>
@foreach (DataColumn col in Model.Columns)
{
<th>@col.ColumnName</th>
}
</tr>
</thead>
<tbody>
@foreach (DataRow row in Model.Rows)
{
<tr>
@foreach (DataColumn col in Model.Columns)
{
<td>@row[col.ColumnName]</td>
}
</tr>
}
</tbody>
</table>
}
}
<script>
$(document).ready(function () {
$("table1").DataTable();
});
</script>
III. Create Index POST Action method
By default index method found in HomeController is access using a GET method. Now, we need a POST index method to process the data from the uploaded excel file.
Note : We have created two index action result in our HomeController . First index handle HttpGet request and the other one handle HttpPost request.
HttpGet index:
- This loads default view.
public ActionResult Index()
{
DataTable dt = new DataTable();
try
{
dt = (DataTable)Session["tmpdata"];
}
catch (Exception ex)
{
}
return View(dt);
}
HttpPost index:
- This received post request from the view.Which is the excel data.
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Index(HttpPostedFileBase upload)
{
if (ModelState.IsValid)
{
if (upload != null && upload.ContentLength > 0)
{
// ExcelDataReader works with the binary Excel file, so it needs a FileStream
// to get started. This is how we avoid dependencies on ACE or Interop:
Stream stream = upload.InputStream;
IExcelDataReader reader = null;
if (upload.FileName.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (upload.FileName.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
ModelState.AddModelError("File", "This file format is not supported");
return View();
}
int fieldcount = reader.FieldCount;
int rowcount = reader.RowCount;
DataTable dt = new DataTable();
DataRow row;
DataTable dt_ = new DataTable();
try
{
dt_ = reader.AsDataSet().Tables[0];
for (int i = 0; i < dt_.Columns.Count; i++)
{
dt.Columns.Add(dt_.Rows[0][i].ToString());
}
int rowcounter = 0;
for (int row_ = 1; row_ < dt_.Rows.Count; row_++)
{
row = dt.NewRow();
for (int col = 0; col < dt_.Columns.Count; col++)
{
row[col] = dt_.Rows[row_][col].ToString();
rowcounter++;
}
dt.Rows.Add(row);
}
}
catch (Exception ex)
{
ModelState.AddModelError("File", "Unable to Upload file!");
return View();
}
DataSet result = new DataSet();
result.Tables.Add(dt);
reader.Close();
reader.Dispose();
DataTable tmp = result.Tables[0];
Session["tmpdata"] = tmp; //store datatable into session
return RedirectToAction("Index");
}
else
{
ModelState.AddModelError("File", "Please Upload Your file");
}
}
return View();
}
}
}
IV. Test the Application
Now, run your project by pressing F5 from your keyboard and try importing a sample excel file. Excel file should be in table format.
Sample Excel file:
- Click on choose button in importing excel and select your excel file.
2. Then click upload. Your final view should look like this.
V. Full Source Code
Home Controller
using ExcelDataReader;
using Microsoft.AspNet.Identity;
using Microsoft.Owin.Security;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;
namespace ImportExcel.Controllers
{
public class HomeController : Controller
{
public ActionResult Index()
{
DataTable dt = new DataTable();
//if ((String)Session["tmpdata"] != null)
//{
try
{
dt = (DataTable)Session["tmpdata"];
}
catch (Exception ex)
{
}
//}
return View(dt);
}
private IAuthenticationManager AuthenticationManager //Sign out method
{
get { return HttpContext.GetOwinContext().Authentication; }
}
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Index(HttpPostedFileBase upload)
{
if (ModelState.IsValid)
{
if (upload != null && upload.ContentLength > 0)
{
// ExcelDataReader works with the binary Excel file, so it needs a FileStream
// to get started. This is how we avoid dependencies on ACE or Interop:
Stream stream = upload.InputStream;
// We return the interface, so that
IExcelDataReader reader = null;
if (upload.FileName.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (upload.FileName.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
ModelState.AddModelError("File", "This file format is not supported");
return View();
}
int fieldcount = reader.FieldCount;
int rowcount = reader.RowCount;
DataTable dt = new DataTable();
//dt.Columns.Add("UserName");
//dt.Columns.Add("Adddress");
DataRow row;
DataTable dt_ = new DataTable();
try
{
dt_ = reader.AsDataSet().Tables[0];
string ret = "";
for (int i = 0; i < dt_.Columns.Count; i++)
{
dt.Columns.Add(dt_.Rows[0][i].ToString());
}
int rowcounter = 0;
for (int row_ = 1; row_ < dt_.Rows.Count; row_++)
{
row = dt.NewRow();
for (int col = 0; col < dt_.Columns.Count; col++)
{
row[col] = dt_.Rows[row_][col].ToString();
rowcounter++;
}
dt.Rows.Add(row);
}
}
catch (Exception ex)
{
ModelState.AddModelError("File", "Unable to Upload file!");
return View();
}
DataSet result = new DataSet();//reader.AsDataSet();
result.Tables.Add(dt);
string minutes_ID = "";
reader.Close();
reader.Dispose();
// return View();
// return View(result.Tables[0]);
DataTable ddd = result.Tables[0];
Session["tmpdata"] = ddd;
return RedirectToAction("Index");
}
else
{
ModelState.AddModelError("File", "Please Upload Your file");
}
}
return View();
}
}
}
View > Index.cshtml
@{
ViewBag.Title = "Home Page";
}
@model System.Data.DataTable
@using System.Data;
<script src="~/Scripts/jquery-1.10.2.js"></script>
<script>
function bs_input_file() {
$(".input-file").before(
function () {
if (!$(this).prev().hasClass('input-ghost')) {
var element = $("<input type='file' id='dataFile' name='upload' class='input-ghost' style='visibility:hidden; height:0'>");
element.attr("name", $(this).attr("name"));
element.change(function () {
element.next(element).find('input').val((element.val()).split('\\').pop());
});
$(this).find("button.btn-choose").click(function () {
element.click();
});
$(this).find("button.btn-reset").click(function () {
element.val(null);
$(this).parents(".input-file").find('input').val('');
});
$(this).find('input').css("cursor", "pointer");
$(this).find('input').mousedown(function () {
$(this).parents('.input-file').prev().click();
return false;
});
return element;
}
}
);
}
function clear() {
var input = $("#dataFile").val('');
};
$(function () {
clear();
bs_input_file();
});
</script>
@using (Html.BeginForm("Index", "Home", null, FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<div class="info2">
<h2>Browse File for uploading</h2>
@Html.AntiForgeryToken()
<div class="form-group" name="Fichier1">
<!-- COMPONENT START -->
<div class="form-group">
<div class="input-group input-file">
<span class="input-group-btn">
<button class="btn btn-default btn-choose" type="button">Choose</button>
</span>
<input type="text" class="form-control" placeholder='Choose a file...' />
</div>
</div>
@*<input type="file" id="dataFile" name="upload" />*@
</div>
<div class="form-group">
<input type="submit" value="Upload" class="btn btn-default" />
</div>
<div class="alert alert-danger">
@Html.ValidationSummary()
@*<strong></strong> asdasd*@
</div>
</div>
if (Model != null)
{
<table id="table1" class="table table-bordered table-hover">
<thead>
<tr>
@foreach (DataColumn col in Model.Columns)
{
<th>@col.ColumnName</th>
}
</tr>
</thead>
<tbody>
@foreach (DataRow row in Model.Rows)
{
<tr>
@foreach (DataColumn col in Model.Columns)
{
<td>@row[col.ColumnName]</td>
}
</tr>
}
</tbody>
</table>
}
}
<script>
$(document).ready(function () {
$("table1").DataTable();
});
</script>
Summary
You’ve just learned how to import data from Excel files in ASP.NET Core. This skill will make your web development tasks easier and more efficient. Practice makes perfect, so don’t hesitate to give it a try in your projects.
If you have questions or need help, reach out to us. Good luck with your data import adventures in ASP.NET Core!
Keep Coding!!