Flawless Excel Import in .NET 9 Using EPPlus

Spread the love

Importing Excel data to a SQL Server Database is a widespread requirement in any web application. Whether you’re building an employee management system or an E-Commerce website, there is often a need to upload bulk data via Excel.

In this blog, we will explore how we can import an Excel file in .NET Core (version 9) using the EPPlus library. EPPlus is a popular open-source .NET library that makes it easy to work with Excel files. In this blog, we will cover Razor-based file uploads, reading Excel data, validating it, and inserting records into a SQL Server database.

🔧 What We’ll Build

We’ll create a .NET 9 MVC Web App that allows users to upload an Excel file containing employee data. The data will be:

Validated (correct file type, structure, and data types)
Parsed using EPPlus
Stored in a SQL Server Employees table

First, we need to configure the database in our web application. We will use the same Reverse Engineering demo for this example. If you didn’t read that blog, please read it.

Step 1: Install EPPlus Packages

You need to install the EPPlus library in your project. I have installed the EPPlus(7.7.2) version for the demo. You can install it via the NuGet Package Manager Console or Manage NuGet Packages from the solution.

Step 2: Create a Sample Excel File

You need to create a sample file as per your database table field. I have created an Employee table in the database, which has FirstName, LastName and Email columns. Below is my sample file.

Step 3: Create Upload View

@{
    ViewData["Title"] = "Upload Excel File";
}

<h2>Upload Excel File</h2>

<form asp-controller="Home" asp-action="Import" method="post" enctype="multipart/form-data">
    <div>
        <label>Select Excel File (.xlsx only)</label>
        <input type="file" name="excelFile" accept=".xlsx" required />
    </div>
    <button type="submit">Upload</button>
</form>

Step 4: Create Controller Logic

using ImportExcel.DBModels;
using ImportExcel.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using OfficeOpenXml;
using System.Diagnostics;

namespace ImportExcel.Controllers
{
    public class HomeController : Controller
    {
        private readonly EmployeeDbContext dbContext;

        public HomeController(EmployeeDbContext dbContext)
        {
            this.dbContext = dbContext;
            // If you are a commercial business and have
            // purchased commercial licenses use the static property
           // LicenseContext of the ExcelPackage class:
           //ExcelPackage.LicenseContext = LicenseContext.Commercial;

           // If you use EPPlus in a noncommercial context
           // according to the Polyform Noncommercial license:
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        }

        public IActionResult Index()
        {           
            return View();
        }


        [HttpPost]
        public async Task<IActionResult> Import(IFormFile excelFile)
        {
            if (excelFile == null || excelFile.Length == 0)
            {
                ViewBag.Message = "Please upload a valid Excel file.";
                return View();
            }

            if (!Path.GetExtension(excelFile.FileName).Equals(".xlsx", StringComparison.OrdinalIgnoreCase))
            {
                ViewBag.Message = "Only .xlsx files are allowed.";
                return View();
            }

            var empList = new List<Employee>();

            try
            {
                using var stream = new MemoryStream();
                await excelFile.CopyToAsync(stream);
                stream.Position = 0;
                using var package = new ExcelPackage(stream);
                var worksheet = package.Workbook.Worksheets.FirstOrDefault();

                if (worksheet == null)
                {
                    ViewBag.Message = "Excel file is empty or invalid.";
                    return View();
                }

                int rowCount = worksheet.Dimension.Rows;
                for (int row = 2; row <= rowCount; row++)
                {
                    var fname = worksheet.Cells[row, 1].Text;
                    var lname = worksheet.Cells[row, 2].Text;
                    var email = worksheet.Cells[row, 3].Text;

                    if (string.IsNullOrWhiteSpace(fname) || string.IsNullOrWhiteSpace(lname) || string.IsNullOrWhiteSpace(email))
                        continue;

                    empList.Add(new Employee
                    {
                        FirstName = fname,
                        LastName = lname,
                        Email = email
                    });
                }

                if (empList.Count == 0)
                {
                    ViewBag.Message = "No valid records found in the Excel file.";
                    return View();
                }

                dbContext.Employees.AddRange(empList);
                await dbContext.SaveChangesAsync();

                ViewBag.Message = $"{empList.Count} employees imported successfully!";
            }
            catch (Exception ex)
            {
                ViewBag.Message = $"Error importing file: {ex.Message}";
            }

            return View();
        }
       
    }
}

🛡️ Key Validations

Validation Type	Description
File Type	Accept only `.xlsx`
File Required	Must upload a file
Column Structure	Ensure required columns exist
Exception Handling	Graceful error messages with `try-catch` block

When you run the project and upload a valid Excel file, the data will be saved into the database.

📌 Conclusion

You’ve just mastered a flawless way to import Excel to SQL Server in .NET using EPPlus and ASP.NET Core MVC. This technique is not just efficient but also secure and scalable.

With this structure, you can now expand to upload other data types, build bulk data tools, or even schedule Excel processing jobs.

🔁 Share this guide with your dev friends, clone it for your projects, and never struggle with Excel imports again.

Also Read,

Spread the love