In any serious business tool, import and export Excel data is a basic feature. This is the fastest way to input data to the database, and Excel being so popular, it's the most common for accountants and business people to proceed like this when bulk inputting any data to an application.
Additionally, I decided to search further Jquery plugins to make my Razor Pages apps more interactive on the front-end, and that's how I found Chosen.
Prerequisites for this tutorial:
1. basic javascript/jquery
2. intermediate Razor Pages (See my other tutorials for ground knowledge)
3. website is running under: https://excelupload-chosen.zoltanhalasz.net/
4. code can be downloaded from: https://drive.google.com/open?id=10YzI-OrrhH_yN6YAKlHcJ7ZGEGZSrzP_
Materials I used to prepare this tutorial:
1. https://harvesthq.github.io/chosen/
2. inspiration for the excel upload: https://www.c-sharpcorner.com/article/using-epplus-to-import-and-export-data-in-asp-net-core/
3. I use an in-memory database for the application, see my previous Datatables 2 tutorial
4. this project is on top of my Datatables 2 tutorial, as you can see the source code, free to use.
Preliminary steps:
1. for the Razor Pages project, include latest package in Nuget manager "EPPlus"
2. Copy the css and js files for chosen in wwwroot, see source https://github.com/harvesthq/chosen/releases/
unzip the file, create a "chosen" folder in wwwroot and copy the content
3. Create a special layout page, containing the references for the css files for formatting reasons
call it "_LayoutChosen " this will be the basis for the
include these in the head tag of the layout file, just below site.css
<link rel="stylesheet" href="~/chosen/docsupport/prism.css">
<link rel="stylesheet" href="~/chosen/chosen.css">
4. use the following file for excel upload:
https://drive.google.com/open?id=1u_zQ4JrwZ5sFXX8eX59vnXdIOPkR3wLm
Steps for the application:
1. Index page:
on the backend
- we have to populate the select list with all cost categories
- we write a function for filtering, that will be the handler for the form
public class IndexModel : PageModel
{
private InvoiceContext _context;
public List<InvoiceModel> InvoiceList;
public IndexModel(InvoiceContext context)
{
_context = context;
}
[BindProperty]
[Display(Name = "Category")]
public string SelectedCategory { get; set; }
public IList<SelectListItem> CategoryList { get; set; } = new List<SelectListItem>();
public void OnGet()
{
InvoiceList = _context.InvoiceTable.ToList();
var distinctCategories = InvoiceList.GroupBy(test => test.CostCategory).Select(grp => grp.First()).ToList();
CategoryList.Add(new SelectListItem() { Text = "All", Value = "All" });
foreach (var cat in distinctCategories)
{
CategoryList.Add(new SelectListItem() { Text = cat.CostCategory, Value = cat.CostCategory});
}
}
public IActionResult OnPostFilter()
{
InvoiceList = _context.InvoiceTable.ToList();
CategoryList.Add(new SelectListItem() { Text = "All", Value = "All" });
var distinctCategories = InvoiceList.GroupBy(test => test.CostCategory).Select(grp => grp.First()).ToList();
foreach (var cat in distinctCategories)
{
CategoryList.Add(new SelectListItem() { Text = cat.CostCategory, Value = cat.CostCategory });
}
if (SelectedCategory == "All") SelectedCategory = "";
InvoiceList = _context.InvoiceTable.Where(x=>x.CostCategory.ToLower().Contains(SelectedCategory.ToLower())).ToList();
return Page();
}
}
on the frontend
we need to implement the form with the chosen select, and then draw the table.
below the table, we implement the chosen jquery action, as per documentation
@page
@model IndexModel
@{
ViewData["Title"] = "Chosen";
Layout = "_LayoutChosen";
}
<div class="text-center">
<h1 class="display-4">Invoice List without DataTable</h1>
<p>
<a asp-page="DataTableArrayRender">Show DataTable</a>
</p>
<p>
<a asp-page="ExcelUpload">Upload Excel File</a>
</p>
</div>
<form class="col-8" id="FilterForm" method="post" asp-page-handler="Filter">
<div class="form-row">
<label asp-for="SelectedCategory" class="col-form-label col-sm-2"></label>
<select class="chosen-select" asp-for="SelectedCategory" data-placeholder="Choose a category..."
asp-items="@Model.CategoryList" onchange="this.form.submit()"></select>
</div>
</form>
<table class="table table-sm">
<thead>
<tr>
<th>
@Html.DisplayNameFor(model => model.InvoiceList[0].InvoiceNumber)
</th>
<th>
@Html.DisplayNameFor(model => model.InvoiceList[0].Amount)
</th>
<th>
@Html.DisplayNameFor(model => model.InvoiceList[0].CostCategory)
</th>
<th>
@Html.DisplayNameFor(model => model.InvoiceList[0].Period)
</th>
<th></th>
</tr>
</thead>
<tbody>
@foreach (var item in Model.InvoiceList)
{
<tr>
<td>
@Html.DisplayFor(modelItem => item.InvoiceNumber)
</td>
<td>
@Html.DisplayFor(modelItem => item.Amount)
</td>
<td>
@Html.DisplayFor(modelItem => item.CostCategory)
</td>
<td>
@Html.DisplayFor(modelItem => item.Period)
</td>
<td></td>
</tr>
}
</tbody>
</table>
<script src="~/chosen/docsupport/jquery-3.2.1.min.js" type="text/javascript"></script>
<script src="~/chosen/chosen.jquery.js" type="text/javascript"></script>
<script src="~/chosen/docsupport/prism.js" type="text/javascript" charset="utf-8"></script>
<script src="~/chosen/docsupport/init.js" type="text/javascript" charset="utf-8"></script>
<script>
$(".chosen-select").chosen({no_results_text: "Oops, nothing found!"});
</script>
Result as below:
2. The excel upload:
Create a new Razor Page: ExcelUpload
On the backend we will use the library from EPPlus (using OfficeOpenXml;)
We will parse the input excel file, transmitted by the form.
For the parsing, we go row by row and get the data.
The upload file has to be in the established format according to the InvoiceModel Class, else the app will throw an exception that we will treat and show an error message.
public class ExcelUploadModel : PageModel
{
private IHostingEnvironment _environment;
private InvoiceContext _context;
public ExcelUploadModel(IHostingEnvironment environment, InvoiceContext context)
{
_environment = environment;
_context = context;
}
[BindProperty]
public IFormFile UploadedExcelFile { get; set; }
[BindProperty]
public String Message { get; set; }
public async Task<IActionResult> OnPostAsync()
{
return await Import(UploadedExcelFile);
}
public async Task <IActionResult> Import(IFormFile formFile)
{
if (formFile == null || formFile.Length <= 0)
{
Message = "This is not a valid file.";
return Page();
}
if (formFile.Length > 500000)
{
Message = "File should be less then 0.5 Mb";
return Page();
}
if (!Path.GetExtension(formFile.FileName).Equals(".xlsx", StringComparison.OrdinalIgnoreCase))
{
Message = "Wrong file format. Should be xlsx.";
return Page();
}
var newList = new List<InvoiceModel>();
try
{
using (var stream = new MemoryStream())
{
await formFile.CopyToAsync(stream);
using (var package = new ExcelPackage(stream))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
var rowCount = worksheet.Dimension.Rows;
for (int row = 2; row <= rowCount; row++)
{
newList.Add(new InvoiceModel
{
//ID = row - 1,
InvoiceNumber = int.Parse(worksheet.Cells[row, 1].Value.ToString().Trim()),
Amount = float.Parse(worksheet.Cells[row, 2].Value.ToString().Trim()),
CostCategory = worksheet.Cells[row, 3].Value.ToString().Trim(),
Period = worksheet.Cells[row, 4].Value.ToString().Trim(),
});
}
}
}
}
catch (Exception ex)
{
Message = "Error while parsing the file. Check the column order and format.";
return Page();
}
List<InvoiceModel> oldInvoiceList = _context.InvoiceTable.ToList();
_context.InvoiceTable.RemoveRange(oldInvoiceList);
_context.InvoiceTable.AddRange(newList);
_context.SaveChanges();
//oldInvoiceList = _context.InvoiceTable.ToList();
return RedirectToPage("./Index");
}
}
On the front-end
We will implement a simple upload form with an Excel file as input. Below, will be the error message in case the upload and data parsing goes wrong.
Please use the sample upload xlsx file shown in the beginning.
@page
@model DataTables.ExcelUploadModel
@{
ViewData["Title"] = "ExcelUpload";
Layout = "~/Pages/Shared/_Layout.cshtml";
}
<h1>ExcelUpload</h1>
<form method="post" enctype="multipart/form-data">
<input type="file" asp-for="UploadedExcelFile" accept=".xlsx"/>
<input type="submit" />
</form>
<strong class="alert-danger">
@Model.Message
</strong>
Showing the error message:
