In this article we will implement Excel import and Export in ASP.Net Core 5
You can also watch the full Video on Youtube
And you can find the full source code on GitHub:
https://github.com/mohamadlawand087/v27-ExcelImportExport
Ingredients
Visual Studio Code (https://code.visualstudio.com/)
.Net 5 SDK (https://dotnet.microsoft.com/download)
The first thing we are going to do is check the dotnet version installed
dotnet --version
Now we need to create our application
dotnet new mvc -n "SampleExcel"
Now let us open our source code
The first thing we are going to do is build the application and run it so we can see that everything is running as it should be.
dotnet run
The next step is for us to install the excel package that we want to utilise and for this we are going to be using EPP
dotnet add package EPPlus.Core --version 1.5.4
Once the package is installed we are going to create our Model which will handle the information output.
In the root directory inside that folder let us create a new class called User
public class User
{
public string Name { get; set; }
public string Email { get; set; }
public string Phone { get; set; }
}
Now let us create a new controller which will be responsible to handle the excel import and export, inside the controllers folder lets create a new Controller called UsersController and will add the export to excel functionality
public class UsersController : Controller
{
private readonly ILogger<UsersController> _logger;
public UsersController(ILogger<UsersController> logger)
{
_logger = logger;
}
public IActionResult Index()
{
var users = GetlistOfUsers();
return View(users);
}
public IActionResult ExportToExcel()
{
// Get the user list
var users = GetlistOfUsers();
var stream = new MemoryStream();
using (var xlPackage = new ExcelPackage(stream))
{
var worksheet = xlPackage.Workbook.Worksheets.Add("Users");
var namedStyle = xlPackage.Workbook.Styles.CreateNamedStyle("HyperLink");
namedStyle.Style.Font.UnderLine = true;
namedStyle.Style.Font.Color.SetColor(Color.Blue);
const int startRow = 5;
var row = startRow;
//Create Headers and format them
worksheet.Cells["A1"].Value = "Sample";
using (var r = worksheet.Cells["A1:C1"])
{
r.Merge = true;
r.Style.Font.Color.SetColor(Color.White);
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93));
}
worksheet.Cells["A4"].Value = "Name";
worksheet.Cells["B4"].Value = "Email";
worksheet.Cells["C4"].Value = "Phone";
worksheet.Cells["A4:C4"].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells["A4:C4"].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
worksheet.Cells["A4:C4"].Style.Font.Bold = true;
row = 5;
foreach (var user in users)
{
worksheet.Cells[row, 1].Value = user.Name;
worksheet.Cells[row, 2].Value = user.Email;
worksheet.Cells[row, 3].Value = user.Phone;
row++;
}
// set some core property values
xlPackage.Workbook.Properties.Title = "User List";
xlPackage.Workbook.Properties.Author = "Mohamad Lawand";
xlPackage.Workbook.Properties.Subject = "User List";
// save the new spreadsheet
xlPackage.Save();
// Response.Clear();
}
stream.Position = 0;
return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "users.xlsx");
}
// Mimic a database operation
private List<User> GetlistOfUsers()
{
var users = new List<User>()
{
new User {
Email = "mohamad@email.com",
Name = "Mohamad",
Phone = "123456"
},
new User {
Email = "donald@email.com",
Name = "donald",
Phone = "222222"
},
new User {
Email = "mickey@email.com",
Name = "mickey",
Phone = "33333"
}
};
return users;
}
}
Now let us update the views inside the Views folder let us create a new folder called Users and inside the Users folder we will create a new file Index.cshtml
@model List<SampleExcel.Models.User>
<div class="row">
<div class="col-md-16">
<a asp-action="ExportToExcel" asp-controller="Users" class="btn btn-primary">Export to Excel</a>
</div>
<div class="col-md-16">
<a asp-action="BatchUserUpload" asp-controller="Users" class="btn btn-primary">Import from Excel</a>
</div>
</div>
<div class="row">
<div class="col-md-12">
<table class="table">
<thead>
<tr>
<th>Name</th>
<th>Email</th>
<th>Phone</th>
</tr>
</thead>
<tbody>
@foreach (var item in Model)
{
<tr>
<td>@item.Name</td>
<td>@item.Email</td>
<td>@item.Phone</td>
</tr>
}
</tbody>
</table>
</div>
</div>
Now let us update the controller with an import functionality
[HttpGet]
public IActionResult BatchUserUpload()
{
return View();
}
[HttpPost]
[ValidateAntiForgeryToken]
public IActionResult BatchUserUpload(IFormFile batchUsers)
{
if (ModelState.IsValid)
{
if (batchUsers?.Length > 0)
{
var stream = batchUsers.OpenReadStream();
List<User> users = new List<User>();
try
{
using (var package = new ExcelPackage(stream))
{
var worksheet = package.Workbook.Worksheets.First();//package.Workbook.Worksheets[0];
var rowCount = worksheet.Dimension.Rows;
for (var row = 2; row <= rowCount; row++)
{
try
{
var name = worksheet.Cells[row, 1].Value?.ToString();
var email = worksheet.Cells[row, 2].Value?.ToString();
var phone = worksheet.Cells[row, 3].Value?.ToString();
var user = new User()
{
Email = email,
Name = name,
Phone = phone
};
users.Add(user);
}
catch(Exception ex)
{
Console.WriteLine("Something went wrong");
}
}
}
return View("Index", users);
}
catch(Exception e)
{
return View();
}
}
}
return View();
}
As well we need to update the view inside the Views ⇒ Users folders we need to create a new file called BatchUserUpload.cshtml and add the following
<div class="row">
<div class="col-md-12">
<form asp-action="BatchUserUpload" asp-controller="Users" method="post" id="create-form" enctype="multipart/form-data">
<div class="form-group">
<label class="control-label">
Upload File:
</label>
<input id="batchUsers" name="batchUsers" class="form-control" type="file" tabindex="14">
<span class="form-text text-muted">Allowed file types: xlsx, xls.</span>
</div>
<div class="card-footer">
<center>
<button type="submit" class="btn btn-primary mr-2">Upload Users</button>
<a asp-action="Index" asp-controller="Users" class="btn btn-secondary">Cancel</a>
</center>
</div>
</form>
</div>
</div>
Thank you for reading, please ask your questions in the comments.
Top comments (1)
Nice article, but in this article, we are using EPPlus for importing data into database form excel, which is no longer free, so I will recommend using OleDB
You can take a look here
asp.net core excel import using oledb
Thanks