Back to: ASP.NET Core Tutorials For Beginners and Professionals
How to Import Excel Data to Database in ASP.NET Core MVC
In this article, I will discuss How to Import Excel Data to a Database in an ASP.NET Core MVC Application with Examples. Please read our previous article discussing How to Export Data to an Excel File in ASP.NET Core MVC Application with Examples.
Why Do We Need to Import Data from Excel in a Web Application?
Many businesses and organizations rely on Excel for data entry, record-keeping, and offline manipulation. Web applications often need to accept data in bulk from external sources. Excel is a widely accepted format for structured data, making it an ideal medium for importing information. Allowing users to upload Excel sheets into a web application streamlines data entry and eliminates the need to re-enter large datasets manually. The following are some of the common reasons and scenarios for Import Data from Excel in a Web Application:
- Bulk Data Entry: You can quickly add many new records (e.g., products, orders, or user accounts) by uploading an Excel sheet rather than entering data individually.
- Data Synchronization: Excel files exported from one system can be easily imported into another, improving data flow between different applications.
- Historical Data Migration: When migrating from older systems or storing offline spreadsheets, you can quickly consolidate data into a new modern web application.
- Integration: Many third-party systems (like inventory management or ERP software) provide Excel files, which can be easily imported to synchronize data.
- Initial Data Seeding: Populating a new database with predefined data sets during the application’s initial setup.
Real-Time Application: Inventory Management System
We will build an application to manage the inventory of products. It supports listing products, filtering them by name, category, or supplier, paginating results, and importing/updating product data via Excel files. The following are the Key Features:
- Product Listing: Display a table of products with details such as Product ID, Name, Brand, SKU, Price, Quantity, Category, Supplier, and Active status.
- Filtering & Pagination: Users can search for products and filter the list by various criteria with navigable pages.
- Excel Import: Let users upload an Excel file (with a provided downloadable template) to import or update product information.
- Dynamic SKU Generation: Create a unique SKU based on parts of the category, brand, and product name combined with the current year.
- Database Seeding: Populate the database with initial seed data for categories, suppliers, and products to simplify testing.
Pictorial Representation of the Project:
Let us understand the flow of the application.
Products List with Filtering Page:
This page shows how users can filter and browse products. Provides a form to filter products by name, category, and supplier. Displays a paginated list of products matching those filters. Offers quick links to download the Excel template or navigate to the Import page.
Import Products Page
This Page allows users to upload an Excel file to bulk-import or update product data. Provides a button to download a pre-formatted Excel template. Presents a file-upload section and an “Upload and Import” button that processes the file.
Import Summary Page
Once the File Upload is Completed, it gives detailed feedback on which products were successfully imported or failed during the import process. Lists each row’s product name, status (Processed or Failed), and a message indicating success or any specific errors/validation issues.
Implementing the Inventory Management Application in ASP.NET Core MVC
In this application, we will use:
- ASP.NET Core MVC is used to build the web application.
- Entity Framework Core (Code First) to interact with the SQL Server database.
- EPPlus for reading and processing Excel files.
- Bootstrap and Font Awesome are great for a responsive and modern user interface.
Create a New ASP.NET Core MVC Project and Install Packages
Open Visual Studio and create a new ASP.NET Core Web App (Model-View-Controller) project. Name the project InventoryManagementApp. Then, install the necessary NuGet packages using the Package Manager Console:
- Install-Package Microsoft.EntityFrameworkCore.SqlServer
- Install-Package Microsoft.EntityFrameworkCore.Tools
- Install-Package EPPlus
Create the Model Classes
Models are the classes that represent the data and business/domain entities in your application. We will use three models in this example: Category, Supplier, and Product.
Category.cs
Create a file named Category.cs in the Models folder, and then copy and paste the following code. The Category Model represents product categories. It has properties like CategoryId, Name, and Description. It also holds a navigation property (Products) pointing to the related Product entities in the database.
namespace InventoryManagementApp.Models
{
// Represents a product category (e.g., Electronics, Apparel, Home Goods)
public class Category
{
public int CategoryId { get; set; } // Primary Key
public string Name { get; set; } // Category Name
public string Description { get; set; } // Optional description
// Navigation property to related Products
public ICollection<Product> Products { get; set; }
}
}
Supplier.cs
Create a file named Supplier.cs in the Models folder, and then copy and paste the following code. The Supplier Model represents suppliers of products. It has properties like SupplierId, Name, ContactEmail, and PhoneNumber. It also holds a navigation property (Products) pointing to the related Product entities.
namespace InventoryManagementApp.Models
{
// Represents a supplier providing products
public class Supplier
{
public int SupplierId { get; set; } // Primary Key
public string Name { get; set; } // Supplier Name
public string ContactEmail { get; set; } // Email address for contact
public string PhoneNumber { get; set; } // Contact phone number
// Navigation property to related Products
public ICollection<Product> Products { get; set; }
}
}
Product.cs
Create a file named Product.cs in the Models folder, and then copy and paste the following code. The Product Model represents a product in the inventory. It has properties like ProductId, Name, Price, Quantity, Brand, DiscountPercentage, SKU,and IsActive and also includes foreign key properties and navigation properties for Category and Supplier. It uses validation attributes such as [Required], [StringLength], and [Range] to enforce rules.
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace InventoryManagementApp.Models
{
// Represents a product in the inventory
public class Product
{
public int ProductId { get; set; } // Primary Key
[Required]
[StringLength(100)]
public string Name { get; set; } // Product Name
public string? Description { get; set; } // Product Description
[Required]
[Column(TypeName = "decimal(18,2)")]
[Range(0.01, double.MaxValue, ErrorMessage = "Price must be greater than zero.")]
public decimal Price { get; set; } // Product Price
[Required]
[Range(0, 100000, ErrorMessage = "Quantity cannot be Negative Number")]
public int Quantity { get; set; } // Quantity in Stock
[Required]
[StringLength(50)]
public string Brand { get; set; } // Product Brand
[Column(TypeName = "decimal(18,2)")]
[Range(0, 100, ErrorMessage = "Discount must be between 0 and 100.")]
public decimal DiscountPercentage { get; set; } // Discount Percentage
[Required]
[StringLength(50)]
public string SKU { get; set; } // Stock Keeping Unit
public bool IsActive { get; set; } // Active status
// Foreign Key and Navigation for Category
[Required]
public int CategoryId { get; set; }
public Category Category { get; set; }
// Foreign Key and Navigation for Supplier
[Required]
public int SupplierId { get; set; }
public Supplier Supplier { get; set; }
}
}
Together, these model classes map directly to tables in the database via Entity Framework Core. They encapsulate the core data and validation rules for your domain.
Create the EF Core DbContext with Seed Data
Acts as the bridge between your models and the SQL Server database. It configures the database context, defines DbSet properties for each entity, and seeds initial data for testing. So, create a folder named Data in the project root. Add a new class file named ApplicationDbContext.cs in the Data folder, then copy and paste the following code:
using InventoryManagementApp.Models;
using Microsoft.EntityFrameworkCore;
namespace InventoryManagementApp.Data
{
public class ApplicationDbContext : DbContext
{
public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
: base(options)
{ }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
// Seed Categories
modelBuilder.Entity<Category>().HasData(
new Category { CategoryId = 1, Name = "Electronics", Description = "Electronic gadgets and devices" },
new Category { CategoryId = 2, Name = "Apparel", Description = "Clothing and fashion accessories" },
new Category { CategoryId = 3, Name = "Home Goods", Description = "Furniture and home decor" }
);
// Seed Suppliers
modelBuilder.Entity<Supplier>().HasData(
new Supplier { SupplierId = 1, Name = "TechSource", ContactEmail = "contact@techsource.com", PhoneNumber = "555-1234" },
new Supplier { SupplierId = 2, Name = "FashionHub", ContactEmail = "sales@fashionhub.com", PhoneNumber = "555-5678" }
);
// Assume the Year of Creating is 2025 for seed data.
// The SKU is computed as:
// [First 3 letters of Category]-[First 3 letters of Brand]-[First 3 letters of Product Name]-[First 3 letters of Supplier]-Year (2025)
// If Brand is missing, "BRD" is used.
// (For these seeds, all Categories and Suppliers are available)
modelBuilder.Entity<Product>().HasData(
new Product
{
ProductId = 1,
Name = "Wireless Headphones",
Description = "Noise-cancelling over-ear headphones",
Price = 99.99m,
Quantity = 50,
Brand = "SoundMax",
DiscountPercentage = 10,
SKU = "ELE-SOU-WIR-TEC-2025", // Electronics (ELE) - SoundMax (SOU) - Wireless (WIR) - TechSource (TEC) - 2025
IsActive = true,
CategoryId = 1, // Electronics
SupplierId = 1 // TechSource
},
new Product
{
ProductId = 2,
Name = "Designer T-Shirt",
Description = "100% cotton, trendy design",
Price = 29.99m,
Quantity = 150,
Brand = "FashionCo",
DiscountPercentage = 5,
SKU = "APP-FAS-DES-FAS-2025", // Apparel (APP) - FashionCo (FAS) - Designer (DES) - FashionHub (FAS) - 2025
IsActive = true,
CategoryId = 2, // Apparel
SupplierId = 2 // FashionHub
},
new Product
{
ProductId = 3,
Name = "Smart LED TV",
Description = "50-inch 4K Ultra HD Smart LED TV",
Price = 499.99m,
Quantity = 30,
Brand = "VisionTech",
DiscountPercentage = 15,
SKU = "ELE-VIS-SMA-TEC-2025", // Electronics (ELE) - VisionTech (VIS) - Smart (SMA) - TechSource (TEC) - 2025
IsActive = true,
CategoryId = 1, // Electronics
SupplierId = 1 // TechSource
},
new Product
{
ProductId = 4,
Name = "Modern Sofa",
Description = "Comfortable 3-seater sofa",
Price = 299.99m,
Quantity = 20,
Brand = "HomeComfort",
DiscountPercentage = 8,
SKU = "HOM-HOM-MOD-FAS-2025", // Home Goods (HOM) - HomeComfort (HOM) - Modern (MOD) - FashionHub (FAS) - 2025
IsActive = true,
CategoryId = 3, // Home Goods
SupplierId = 2 // FashionHub
},
new Product
{
ProductId = 5,
Name = "Bluetooth Speaker",
Description = "Portable wireless speaker with deep bass",
Price = 49.99m,
Quantity = 80,
Brand = "SoundMax",
DiscountPercentage = 12,
SKU = "ELE-SOU-BLU-TEC-2025", // Electronics (ELE) - SoundMax (SOU) - Bluetooth (BLU) - TechSource (TEC) - 2025
IsActive = true,
CategoryId = 1, // Electronics
SupplierId = 1 // TechSource
},
new Product
{
ProductId = 6,
Name = "Casual Sneakers",
Description = "Comfortable and stylish sneakers",
Price = 59.99m,
Quantity = 100,
// Brand is missing (empty), so default value "BRD" is used in the SKU.
Brand = "",
DiscountPercentage = 0,
SKU = "APP-BRD-CAS-FAS-2025", // Apparel (APP) - Missing Brand => (BRD) - Casual (CAS) - FashionHub (FAS) - 2025
IsActive = true,
CategoryId = 2, // Apparel
SupplierId = 2 // FashionHub
}
);
}
public DbSet<Category> Categories { get; set; }
public DbSet<Supplier> Suppliers { get; set; }
public DbSet<Product> Products { get; set; }
}
}
Create the View Models
View Models are specialized classes that contain data needed specifically for rendering Views (and for handling user input from those Views). They often combine multiple Models or include extra properties that are not stored in the database but are needed by the View. First, create a ViewModels folder in the Project root directory, where we will add all our View Models.
ProductListViewModel
Create a file named ProductListViewModel.cs inside the ViewModels folder, and then copy and paste the following code. The View uses the ProductListViewModel to display a filtered and paginated list of Product entities. It holds the list of products (IEnumerable<Product> Products), plus filtering/pagination info (SearchName, SelectedCategoryId, SelectedSupplierId, CurrentPage, TotalPages), and lists for dropdown filters (Categories, Suppliers).
using InventoryManagementApp.Models;
namespace InventoryManagementApp.ViewModels
{
// View model for listing products with filtering and pagination
public class ProductListViewModel
{
public IEnumerable<Product> Products { get; set; }
// Filtering properties
public string? SearchName { get; set; }
public int? SelectedCategoryId { get; set; }
public int? SelectedSupplierId { get; set; }
// Dropdown lists for filter options
public IEnumerable<Category> Categories { get; set; }
public IEnumerable<Supplier> Suppliers { get; set; }
// Pagination properties
public int CurrentPage { get; set; }
public int TotalPages { get; set; }
}
}
ProductImportResult
Create a file named ProductImportResult.cs inside the ViewModels folder, and then copy and paste the following code. The ProductImportResult is used within ProductImportViewModel to capture the outcome of processing each Excel row. It stores the product name being processed, a success flag, and a message for errors or additional info.
namespace InventoryManagementApp.ViewModels
{
// Contains the result for each product row processed during import
public class ProductImportResult
{
public string ProductName { get; set; }
public bool Success { get; set; }
// If not processed, this message will hold the validation errors or exception details
public string Message { get; set; }
}
}
ProductImportViewModel
Create a file named ProductImportViewModel.cs inside the ViewModels folder, and then copy and paste the following code. The ProductImportViewModel is Used by the View to manage the Excel file upload. It contains FileUpload (the uploaded Excel file) and ImportResults (a list of ProductImportResult objects that indicate success/failure per row). It also contains a Message property for displaying overall status messages to the user.
namespace InventoryManagementApp.ViewModels
{
// View model for importing products from an Excel file
public class ProductImportViewModel
{
// The uploaded Excel file (.xlsx)
public IFormFile FileUpload { get; set; }
// Message for overall import status
public string Message { get; set; }
// Detailed results of processing each product row
public List<ProductImportResult> ImportResults { get; set; } = new List<ProductImportResult>();
}
}
View Models ensure our Views only receive or send the data they need, keeping them cleaner and more focused than directly passing entities.
Create the Controller
A Controller is part of the “C” in MVC. It handles HTTP requests, interacts with the Models (and/or database), and chooses a View (or other result) to return. In this example, the ProductsController will have actions to:
- Display the product list (Index action).
- Provide a file upload form (Import action).
- Handle file upload and processing logic (Import POST action).
- Allow downloading a template file (DownloadTemplate action).
So, create a new Empty MVC Controller named ProductsController.cs in the Controllers folder and add the following code:
using InventoryManagementApp.Data;
using InventoryManagementApp.Models;
using InventoryManagementApp.ViewModels;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using OfficeOpenXml;
using System.ComponentModel.DataAnnotations;
namespace InventoryManagementApp.Controllers
{
public class ProductsController : Controller
{
private readonly ApplicationDbContext _context;
private const int PageSize = 5;
public ProductsController(ApplicationDbContext context)
{
_context = context;
}
// GET: Products/Index
// Displays the list of products with filtering and pagination.
public async Task<IActionResult> Index(string? searchName, int? categoryId, int? supplierId, int page = 1)
{
var query = _context.Products.AsNoTracking()
.Include(p => p.Category)
.Include(p => p.Supplier)
.AsQueryable();
if (!string.IsNullOrEmpty(searchName))
query = query.Where(p => p.Name.Contains(searchName));
if (categoryId.HasValue)
query = query.Where(p => p.CategoryId == categoryId.Value);
if (supplierId.HasValue)
query = query.Where(p => p.SupplierId == supplierId.Value);
int totalRecords = await query.CountAsync();
int totalPages = (int)Math.Ceiling(totalRecords / (double)PageSize);
var products = await query
.OrderBy(p => p.ProductId)
.Skip((page - 1) * PageSize)
.Take(PageSize)
.ToListAsync();
var model = new ProductListViewModel
{
Products = products,
SearchName = searchName,
SelectedCategoryId = categoryId,
SelectedSupplierId = supplierId,
Categories = await _context.Categories.ToListAsync(),
Suppliers = await _context.Suppliers.ToListAsync(),
CurrentPage = page,
TotalPages = totalPages
};
return View(model);
}
// GET: Products/Import
// Displays the file upload form and a link to download the Excel template.
public IActionResult Import()
{
var model = new ProductImportViewModel();
return View(model);
}
// GET: Products/DownloadTemplate
// Allows the user to download the Excel template for product import.
public IActionResult DownloadTemplate()
{
// Define the Excel file template on the fly using EPPlus.
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("Template");
// Set headers (columns):
// 1: Product Name, 2: Description, 3: Price, 4: Quantity,
// 5: Category Name, 6: Supplier Name, 7: Supplier Email, 8: Supplier Phone,
// 9: Brand, 10: Discount Percentage, 11: IsActive (true/false)
worksheet.Cells[1, 1].Value = "Product Name";
worksheet.Cells[1, 2].Value = "Description";
worksheet.Cells[1, 3].Value = "Price";
worksheet.Cells[1, 4].Value = "Quantity";
worksheet.Cells[1, 5].Value = "Category Name";
worksheet.Cells[1, 6].Value = "Supplier Name";
worksheet.Cells[1, 7].Value = "Supplier Email";
worksheet.Cells[1, 8].Value = "Supplier Phone";
worksheet.Cells[1, 9].Value = "Brand";
worksheet.Cells[1, 10].Value = "Discount Percentage";
worksheet.Cells[1, 11].Value = "IsActive (true/false)";
worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
var fileBytes = package.GetAsByteArray();
return File(
fileBytes,
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
"ProductImportTemplate.xlsx"
);
}
}
// POST: Products/Import
// Processes the uploaded Excel file, validates each product, and displays an import summary.
[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Import(ProductImportViewModel model)
{
if (model.FileUpload == null || model.FileUpload.Length == 0)
{
ModelState.AddModelError("", "Please select an Excel file to upload.");
return View(model);
}
// Set EPPlus license context for non-commercial use.
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
int successCount = 0;
var importResults = new List<ProductImportResult>();
using (var stream = new MemoryStream())
{
await model.FileUpload.CopyToAsync(stream);
using (var package = new ExcelPackage(stream))
{
var worksheet = package.Workbook.Worksheets.FirstOrDefault();
if (worksheet == null)
{
ModelState.AddModelError("", "No worksheet found in Excel file.");
return View(model);
}
int rowCount = worksheet.Dimension.Rows;
// Loop from row 2 (assuming row 1 contains headers)
for (int row = 2; row <= rowCount; row++)
{
// Expected columns:
// 1: Product Name, 2: Description, 3: Price, 4: Quantity,
// 5: Category Name, 6: Supplier Name, 7: Supplier Email, 8: Supplier Phone,
// 9: Brand, 10: Discount Percentage, 11: IsActive
string productName = worksheet.Cells[row, 1].Text.Trim();
string description = worksheet.Cells[row, 2].Text.Trim();
string priceText = worksheet.Cells[row, 3].Text.Trim();
string quantityText = worksheet.Cells[row, 4].Text.Trim();
string categoryName = worksheet.Cells[row, 5].Text.Trim();
string supplierName = worksheet.Cells[row, 6].Text.Trim();
string supplierEmail = worksheet.Cells[row, 7].Text.Trim();
string supplierPhone = worksheet.Cells[row, 8].Text.Trim();
string brand = worksheet.Cells[row, 9].Text.Trim();
string discountText = worksheet.Cells[row, 10].Text.Trim();
string isActiveText = worksheet.Cells[row, 11].Text.Trim();
// Skip rows with an empty product name.
if (string.IsNullOrEmpty(productName))
continue;
var importResult = new ProductImportResult { ProductName = productName };
try
{
decimal price = decimal.Parse(priceText);
int quantity = int.Parse(quantityText);
decimal discount = string.IsNullOrEmpty(discountText) ? 0 : decimal.Parse(discountText);
bool isActive = bool.Parse(isActiveText);
// Retrieve or create Category.
var category = await _context.Categories.FirstOrDefaultAsync(c => c.Name == categoryName);
if (category == null)
{
category = new Category { Name = categoryName, Description = "" };
_context.Categories.Add(category);
// Do not save here; let the transaction handle it.
}
// Retrieve or create Supplier.
var supplier = await _context.Suppliers.FirstOrDefaultAsync(s => s.Name == supplierName);
if (supplier == null)
{
supplier = new Supplier
{
Name = supplierName,
ContactEmail = supplierEmail,
PhoneNumber = supplierPhone
};
_context.Suppliers.Add(supplier);
}
// Generate the SKU dynamically.
string generatedSKU = GenerateSKU(categoryName, brand, productName, supplier.Name);
// Check if a product with the generated SKU already exists.
var product = await _context.Products.FirstOrDefaultAsync(p => p.SKU == generatedSKU);
if (product == null)
{
// Create a new product.
product = new Product
{
Name = productName,
Description = description,
Price = price,
Quantity = quantity,
Brand = brand,
DiscountPercentage = discount,
IsActive = isActive,
Category = category,
Supplier = supplier,
SKU = generatedSKU
};
// Validate the new product.
var validationContext = new ValidationContext(product, null, null);
var validationResults = new List<ValidationResult>();
if (!Validator.TryValidateObject(product, validationContext, validationResults, true))
{
importResult.Success = false;
importResult.Message = string.Join("; ", validationResults.Select(v => v.ErrorMessage));
importResults.Add(importResult);
continue;
}
_context.Products.Add(product);
}
else
{
// Update existing product.
product.Description = description;
product.Price = price;
product.Quantity = quantity;
product.Brand = brand;
product.DiscountPercentage = discount;
product.IsActive = isActive;
product.Category = category;
product.Supplier = supplier;
var validationContext = new ValidationContext(product, null, null);
var validationResults = new List<ValidationResult>();
if (!Validator.TryValidateObject(product, validationContext, validationResults, true))
{
importResult.Success = false;
importResult.Message = string.Join("; ", validationResults.Select(v => v.ErrorMessage));
importResults.Add(importResult);
continue;
}
_context.Products.Update(product);
}
importResult.Success = true;
importResult.Message = "Processed successfully.";
importResults.Add(importResult);
successCount++;
}
catch (Exception ex)
{
importResult.Success = false;
importResult.Message = $"Exception: {ex.Message}";
importResults.Add(importResult);
}
} // end for loop
}
}
// Use an explicit transaction to commit all changes as a batch.
using (var transaction = await _context.Database.BeginTransactionAsync())
{
try
{
await _context.SaveChangesAsync();
await transaction.CommitAsync();
}
catch (Exception ex)
{
await transaction.RollbackAsync();
ModelState.AddModelError("", "An error occurred while saving changes: " + ex.Message);
// Optionally, log the error.
}
}
model.ImportResults = importResults;
model.Message = $"Import process completed. {successCount} products processed successfully. See summary below.";
return View(model);
}
// Generates a SKU based on the first three letters of the category, brand, product name, supplier, and the current year.
// If the Category is missing, "CAT" is used.
// If the Brand is missing, "BRD" is used.
// If the Supplier is missing, "SUP" is used.
private string GenerateSKU(string categoryName, string brand, string productName, string supplierName)
{
string catPart = !string.IsNullOrEmpty(categoryName) && categoryName.Length >= 3
? categoryName.Substring(0, 3).ToUpper()
: "CAT";
string brandPart = !string.IsNullOrEmpty(brand) && brand.Length >= 3
? brand.Substring(0, 3).ToUpper()
: "BRD";
string supplierPart = !string.IsNullOrEmpty(supplierName) && supplierName.Length >= 3
? supplierName.Substring(0, 3).ToUpper()
: "SUP";
string prodPart = !string.IsNullOrEmpty(productName) && productName.Length >= 3
? productName.Substring(0, 3).ToUpper()
: productName.ToUpper();
string yearPart = DateTime.Now.Year.ToString();
return $"{catPart}-{brandPart}-{prodPart}-{supplierPart}-{yearPart}";
}
}
}
Actions Method of Products Controller:
Actions are public methods in a Controller that respond to specific HTTP requests (like GET, POST). The following actions coordinate the flow between the Views and the database (via ApplicationDbContext).
Index (GET)
- It accepts optional filter parameters (searchName, categoryId, supplierId) and page numbers.
- Queries the database, applies filtering (by name, category, supplier), applies pagination, and passes a ProductListViewModel to the View.
- Returns the Index.cshtml View.
Import (GET)
- Displays a form where the user can upload an Excel file.
- Returns the Import.cshtml View with an empty ProductImportViewModel.
Import (POST)
- Processes the uploaded Excel file (using EPPlus).
- Reads each row and creates or updates Product, Category, and Supplier data in the database.
- Validates each product.
- Collects success/failure info in a ProductImportViewModel and returns the same Import.cshtml View but now with results to display.
DownloadTemplate (GET)
- Dynamically generates an Excel file template (with required columns) and streams it to the user for download.
- Uses EPPlus to create the Excel file in memory and returns it as a FileResult.
Create the Views
Views (the V in MVC) are Razor .cshtml pages that Render the user interface. Views are associated with specific actions and are responsible for displaying data from the Models or View Models passed by the controller. In the example, there are two main Views in Views/Products:
Import View
Create a view named Import.cshtml within the Views/Products folder, and then copy and paste the following code. This view shows the file upload form and a summary of the import results.
@model InventoryManagementApp.ViewModels.ProductImportViewModel
@{
ViewData["Title"] = "Import Products";
}
<div class="container mt-5">
<h2 class="text-center mb-4">Import Products</h2>
<div class="mb-3">
<a asp-action="DownloadTemplate" class="btn btn-warning">
<i class="fas fa-download"></i> Download Excel Template
</a>
</div>
@if (!string.IsNullOrEmpty(Model.Message))
{
<div class="alert alert-info">@Model.Message</div>
}
<form asp-action="Import" method="post" enctype="multipart/form-data">
<div class="form-group">
<label for="fileUpload">Select Excel File (.xlsx)</label>
<input type="file" class="form-control" name="FileUpload" id="fileUpload" accept=".xlsx" required />
</div>
<button type="submit" class="btn btn-primary mt-3">
<i class="fas fa-upload"></i> Upload and Import
</button>
</form>
@if (Model.ImportResults.Any())
{
<h4 class="mt-5">Import Summary</h4>
<table class="table table-bordered mt-3">
<thead class="thead-light">
<tr>
<th>Product Name</th>
<th>Status</th>
<th>Details</th>
</tr>
</thead>
<tbody>
@foreach (var result in Model.ImportResults)
{
<tr>
<td>@result.ProductName</td>
<td>
@if (result.Success)
{
<span class="text-success">Processed</span>
}
else
{
<span class="text-danger">Failed</span>
}
</td>
<td>@result.Message</td>
</tr>
}
</tbody>
</table>
}
<div class="mt-4">
<a asp-action="Index" class="btn btn-secondary">Back to Product List</a>
</div>
</div>
Key Features of Import.cshtml View
- Renders a form for uploading the Excel file.
- Shows a button for downloading the Excel template.
- If import results exist (in Model.ImportResults), display a summary table of each row’s success or failure.
Index View
Create a view named Index.cshtml within the Views/Products folder, and then copy and paste the following code. This view displays the product list, including filtering options and pagination controls.
@model InventoryManagementApp.ViewModels.ProductListViewModel
@{
ViewData["Title"] = "Product List";
}
<div class="container mt-5">
<h2 class="text-center mb-4">Product List</h2>
<!-- Filter Section -->
<div class="card mb-4">
<div class="card-header bg-primary text-white">
Filter Options
</div>
<div class="card-body">
<form method="get" asp-controller="Products" asp-action="Index">
<div class="form-row">
<div class="form-group col-md-4">
<label for="searchName">Product Name:</label>
<input type="text" class="form-control" id="searchName" name="searchName" value="@Model.SearchName" placeholder="Search by name" />
</div>
<div class="form-group col-md-4">
<label for="categoryId">Category:</label>
<select id="categoryId" name="categoryId" class="custom-select">
<option value="">All</option>
@foreach (var cat in Model.Categories)
{
<option value="@cat.CategoryId" selected="@(cat.CategoryId == Model.SelectedCategoryId ? "selected" : null)">@cat.Name</option>
}
</select>
</div>
<div class="form-group col-md-4">
<label for="supplierId">Supplier:</label>
<select id="supplierId" name="supplierId" class="custom-select">
<option value="">All</option>
@foreach (var sup in Model.Suppliers)
{
<option value="@sup.SupplierId" selected="@(sup.SupplierId == Model.SelectedSupplierId ? "selected" : null)">@sup.Name</option>
}
</select>
</div>
</div>
<button type="submit" class="btn btn-primary">
<i class="fas fa-filter"></i> Apply Filters
</button>
</form>
</div>
</div>
<!-- Product Table and Import Button -->
<div class="card">
<div class="card-header d-flex justify-content-between align-items-center bg-secondary text-white">
<span>Products</span>
<div>
<a asp-action="DownloadTemplate" class="btn btn-warning btn-sm mr-2">
<i class="fas fa-download"></i> Download Excel Template
</a>
<a asp-action="Import" class="btn btn-success btn-sm">
<i class="fas fa-file-excel"></i> Import Products
</a>
</div>
</div>
<div class="card-body p-0">
<table class="table table-striped mb-0">
<thead class="thead-dark">
<tr>
<th>Product ID</th>
<th>Name</th>
<th>Brand</th>
<th>SKU</th>
<th>Price</th>
<th>Qty</th>
<th>Category</th>
<th>Supplier</th>
<th>Active</th>
</tr>
</thead>
<tbody>
@foreach (var product in Model.Products)
{
<tr>
<td>@product.ProductId</td>
<td>@product.Name</td>
<td>@product.Brand</td>
<td>@product.SKU</td>
<td>@product.Price.ToString("C")</td>
<td>@product.Quantity</td>
<td>@product.Category?.Name</td>
<td>@product.Supplier?.Name</td>
<td>@(product.IsActive ? "Yes" : "No")</td>
</tr>
}
</tbody>
</table>
</div>
</div>
<!-- Pagination Controls -->
@if (Model.TotalPages > 1)
{
<nav class="mt-4">
<ul class="pagination justify-content-center">
<li class="page-item @(Model.CurrentPage == 1 ? "disabled" : "")">
<a class="page-link" asp-action="Index"
asp-route-page="1"
asp-route-searchName="@Model.SearchName"
asp-route-categoryId="@Model.SelectedCategoryId"
asp-route-supplierId="@Model.SelectedSupplierId">First</a>
</li>
<li class="page-item @(Model.CurrentPage == 1 ? "disabled" : "")">
<a class="page-link" asp-action="Index"
asp-route-page="@(Model.CurrentPage - 1)"
asp-route-searchName="@Model.SearchName"
asp-route-categoryId="@Model.SelectedCategoryId"
asp-route-supplierId="@Model.SelectedSupplierId">Previous</a>
</li>
<li class="page-item active">
<span class="page-link">Page @Model.CurrentPage of @Model.TotalPages</span>
</li>
<li class="page-item @(Model.CurrentPage == Model.TotalPages ? "disabled" : "")">
<a class="page-link" asp-action="Index"
asp-route-page="@(Model.CurrentPage + 1)"
asp-route-searchName="@Model.SearchName"
asp-route-categoryId="@Model.SelectedCategoryId"
asp-route-supplierId="@Model.SelectedSupplierId">Next</a>
</li>
<li class="page-item @(Model.CurrentPage == Model.TotalPages ? "disabled" : "")">
<a class="page-link" asp-action="Index"
asp-route-page="@(Model.TotalPages)"
asp-route-searchName="@Model.SearchName"
asp-route-categoryId="@Model.SelectedCategoryId"
asp-route-supplierId="@Model.SelectedSupplierId">Last</a>
</li>
</ul>
</nav>
}
</div>
Key Features of Index.cshtml View
- Renders a filtered, paginated list of products (ProductListViewModel).
- Displays a form at the top for filtering by product name, category, and supplier.
- Lists products in a table.
- Shows pagination controls (First / Previous / Next / Last pages).
Modify the Layout View
Serves as the common layout for all pages, defining the HTML structure (header, footer, CSS/JS references). So, please modify the _Layout.cshtml as follows:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>@ViewData["Title"] - InventoryManagementApp</title>
<!-- Local Bootstrap and Site CSS -->
<link rel="stylesheet" href="~/lib/bootstrap/dist/css/bootstrap.min.css" />
<link rel="stylesheet" href="~/css/site.css" asp-append-version="true" />
<!-- CDN Bootstrap and Font Awesome -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" />
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.15.1/css/all.min.css" />
<!-- Inline CSS for content spacing -->
<style>
.content-container {
margin-bottom: 100px;
}
</style>
</head>
<body>
<header>
<nav class="navbar navbar-expand-sm navbar-light bg-white border-bottom box-shadow mb-3">
<div class="container-fluid">
<a class="navbar-brand" asp-area="" asp-controller="Home" asp-action="Index">InventoryManagementApp</a>
<button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target=".navbar-collapse"
aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="navbar-collapse collapse d-sm-inline-flex justify-content-between">
<ul class="navbar-nav flex-grow-1">
<li class="nav-item">
<a class="nav-link text-dark" asp-area="" asp-controller="Home" asp-action="Index">Home</a>
</li>
<li class="nav-item">
<a class="nav-link text-dark" asp-area="" asp-controller="Home" asp-action="Privacy">Privacy</a>
</li>
</ul>
</div>
</div>
</nav>
</header>
<!-- Main content container -->
<div class="container content-container">
<main role="main" class="pb-3">
@RenderBody()
</main>
</div>
<footer class="border-top footer text-muted">
<div class="container">
© 2025 - InventoryManagementApp - <a asp-area="" asp-controller="Home" asp-action="Privacy">Privacy</a>
</div>
</footer>
<script src="~/lib/jquery/dist/jquery.min.js"></script>
<script src="~/lib/bootstrap/dist/js/bootstrap.bundle.min.js"></script>
<script src="~/js/site.js" asp-append-version="true"></script>
@RenderSection("Scripts", required: false)
</body>
</html>
Update appsettings.json
Please modify the appsettings.json file as follows. It stores application configuration settings, such as connection strings and logging configuration. The appsettings.json file contains the ConnectionStrings:DefaultConnection entry, which tells Entity Framework Core how to connect to the SQL Server database. This information is used in the Program class when configuring the ApplicationDbContext.
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"DefaultConnection": "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=InventoryDB;Trusted_Connection=True;TrustServerCertificate=True;"
}
}
Configure the Application and Database Connection
In ASP.NET Core (especially from .NET 6 onward), the Program.cs file is the entry point for our web application. It sets up services, middleware, and the app’s routing. The Program class configures services such as ApplicationDbContext and sets up the HTTP request pipeline (middleware, routing, etc.). It also establishes the default routing pattern, which directs requests to the ProductsController by default. So, please modify the Program.cs file as follows:
using InventoryManagementApp.Data;
using Microsoft.EntityFrameworkCore;
namespace InventoryManagementApp
{
public class Program
{
public static void Main(string[] args)
{
var builder = WebApplication.CreateBuilder(args);
// Add services to the container.
builder.Services.AddControllersWithViews();
// Configure EF Core to use SQL Server.
builder.Services.AddDbContext<ApplicationDbContext>(options =>
options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"))
);
var app = builder.Build();
// Configure the HTTP request pipeline.
if (!app.Environment.IsDevelopment())
{
app.UseExceptionHandler("/Home/Error");
// The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
app.UseHsts();
}
app.UseHttpsRedirection();
app.UseStaticFiles();
app.UseRouting();
app.UseAuthorization();
app.MapControllerRoute(
name: "default",
pattern: "{controller=Products}/{action=Index}/{id?}");
app.Run();
}
}
}
Migrate and Create the Database
Open the Visual Studio Package Manager Console and run the following commands to create the database and tables:
- Add-Migration InitialCreate
- Update-Database
This will create your InventoryDB database with the tables for Categories, Suppliers, and Products as shown in the below image:
Now, run the application and test the functionalities, and it should work as expected.
Real-time Use Cases:
Examples of Real-time scenarios where importing data from Excel is valuable:
- E-commerce Product Catalog Updates: Administrators can import many products, including images, pricing, and category details from supplier spreadsheets.
- School Administration: Importing student enrolment data or course registration from Excel spreadsheets shared by different departments.
- Finance and Accounting: Accountants can import bulk transaction records from Excel for reconciliation and record-keeping.
- Supply Chain Management: Logistics teams can import shipment or order details provided by partners in Excel format.
The above real-time application shows how to build an ASP.NET Core MVC application that integrates data management, user-friendly interfaces, and robust back-end processing. With its use of modern technologies such as EF Core and EPPlus, the project is an excellent starting point for developers looking to implement inventory management systems or similar applications requiring data import/export capabilities, validation, and efficient database interactions.
In the next article, I will discuss How to Generate a PDF in the ASP.NET Core MVC Application with examples. In this article, I try to explain How to Import Excel Data to a Database in an ASP.NET Core MVC Application with Examples. I hope you enjoy this article on How to Import Excel Data to a Database in ASP.NET Core MVC.
About the Author: Pranaya Rout
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.