Excel Sheet Validation in ASP.NET Core

Excel is a popular format for importing and exporting data in enterprise web applications. However, ensuring that the Excel file uploaded by the user contains valid and consistent data is critical to avoid errors and maintain data integrity.

In this post, we’ll explore how to upload, read, and validate Excel files in an ASP.NET Core web application.

Excel Sheet Validation in ASP.NET Core

Getting Started

Excel files are commonly used to import large sets of data into web applications. However, before processing that data, validating the contents of an Excel sheet is essential to prevent corrupt, missing, or invalid data from entering your system.

We’ll cover:

1. Reading the Excel file
2. Checking for required columns
3. Validating data row-by-row
4. Returning validation results to the user

Demonstration

In this demonstration, we will read an Excel sheet containing employee data, as shown in the image below. We will validate each column and row value, including fields such as Employee ID, Name, Email, and Contact Information.

If any value does not meet the required conditions. For example, if the email format is invalid—an error message will be displayed on the page.

Create a new project

1. Open Visual Studio
2. Click "Create a new project"
3. Search for "ASP.NET Core Web App", then select it and click Next
4. Name your project and choose a location
5. Click Next
6. Choose:
   • .NET version (e.g., .NET 6, 7, or 8)
   • Web App (Model-View-Controller) for MVC or Empty for minimal APIs
   • Uncheck or check HTTPS, Docker, or Authentication as needed
7. Click Create

Install ClosedXML Package

Using ClosedXML, excel sheet data we will read here, the ClosedXML package needs to be installed. Here are the steps to install the package

To install the ClosedXML package using the NuGet Package Manager in a .NET project, follow these steps:

1. Open your project in Visual Studio.
2. In the Solution Explorer, right-click on your project.
3. Select "Manage NuGet Packages...".
4. Go to the "Browse" tab.
5. In the search box, type: ClosedXML.
6. Find the ClosedXML package in the results and click Install.
7. Accept any license agreements that appear.

Add a Razor Page

Now we will add a Razor page in the project to upload and validate excel file, here are the steps to add a razor page in to the project.

1. Right-click on the "Pages" folder in Solution Explorer
2. Select Add → Razor Page...
3. Choose Empty templates
4. Name your Razor Page (e.g., Upload.cshtml)
5. Click Add

Read & Validate Excel Sheet

Update the Upload.cshtml and Upload.cshtml.cs files using the code below, which contains logic for reading, validating, and showing error messages.

Upload.cshtml file

 @page  
 @model excelsheetvalidation.Pages.UploadModel  
 @{  
   ViewData["Title"] = "Excel Upload";  
 }  
 <h2>Upload Excel File</h2>  
 <form method="post" enctype="multipart/form-data">  
   <input type="file" name="ExcelFile" />  
   <button type="submit">Upload</button>  
 </form>  
 @if (Model.Errors.Any())  
 {  
   <h3>Validation Errors:</h3>  
    <div style="max-height: 200px; overflow-y: auto; border: 1px solid #ccc; padding: 10px;">  
   <ul>  
     @foreach (var error in Model.Errors)  
     {  
       <li>@error</li>  
     }  
   </ul>  
   </div>  
 }  
 else if (Model.SuccessMessage != null)  
 {  
   <h3>@Model.SuccessMessage</h3>  
 }  

Upload.cshtml.cs file

 using ClosedXML.Excel;  
 using Microsoft.AspNetCore.Mvc;  
 using Microsoft.AspNetCore.Mvc.RazorPages;  
 using System.ComponentModel.DataAnnotations;  
 using System.Text.RegularExpressions;  
 namespace excelsheetvalidation.Pages  
 {  
   public class UploadModel : PageModel  
   {  
     [BindProperty]  
     [Required]  
     public IFormFile ExcelFile { get; set; }  
     public List<string> Errors { get; set; } = new();  
     public string SuccessMessage { get; set; }  
     public void OnGet()  
     {  
     }  
     public IActionResult OnPost()  
     {  
       if (ExcelFile == null || ExcelFile.Length == 0)  
       {  
         Errors.Add("Please upload a valid Excel (.xlsx) file.");  
         return Page();  
       }  
       using (var stream = new MemoryStream())  
       {  
         ExcelFile.CopyTo(stream);  
         using (var workbook = new XLWorkbook(stream))  
         {  
           var worksheet = workbook.Worksheets.FirstOrDefault();  
           if (worksheet == null)  
           {  
             Errors.Add("No worksheet found in the Excel file.");  
             return Page();  
           }  
           // 1. Define expected headers (case-insensitive)  
           var expectedHeaders = new List<string> { "EmployeeID", "EmployeeName", "Contact No", "EmailID" };  
           // 2. Read actual headers from first row  
           var actualHeaders = worksheet.Row(1)  
             .CellsUsed()  
             .Select(cell => cell.GetString().Trim())  
             .ToList();  
           // 3. Validate presence of required headers  
           foreach (var header in expectedHeaders)  
           {  
             if (!actualHeaders.Contains(header, StringComparer.OrdinalIgnoreCase))  
             {  
               Errors.Add($"Missing required column: {header}");  
             }  
           }  
           int rowCount = worksheet.LastRowUsed().RowNumber();  
           for (int row = 2; row <= rowCount; row++)  
           {  
             var empid = worksheet.Cell(row, 1).GetValue<string>()?.Trim();  
             var name = worksheet.Cell(row, 2).GetValue<string>()?.Trim();  
             var email = worksheet.Cell(row, 3).GetValue<string>()?.Trim();  
             var cont = worksheet.Cell(row, 4).GetValue<string>()?.Trim();  
             if (!IsValidEmpid(empid))  
               Errors.Add($"Row {row}: EmployeeID is not valid.");  
             if (!IsValidName(name))  
               Errors.Add($"Row {row}: Name is not valid.");  
             if (string.IsNullOrEmpty(email) || !IsValidEmail(email))  
               Errors.Add($"Row {row}: Email is invalid.");  
             if (!IsValidContact(cont))  
               Errors.Add($"Row {row}: ContactNo not valid.");  
           }  
         }  
       }  
       if (Errors.Any())  
         return Page();  
       SuccessMessage = "Excel file validated successfully!";  
       return Page();  
     }  
     private bool IsValidEmpid(string empid)  
     {  
       if (string.IsNullOrEmpty(empid) == true)  
         return false;  
       else if (empid.Length < 10)  
         return false;  
       return true;  
     }  
     private bool IsValidName(string name)  
     {  
       if (string.IsNullOrEmpty(name) == true)  
         return false;  
       else  
         return true;  
     }  
     private bool IsValidEmail(string email)  
     {  
       return new EmailAddressAttribute().IsValid(email);  
     }  
     private bool IsValidContact(string cont)  
     {  
       string pattern = @"^[+]{1}(?:[0-9\\-\\(\\)\\/" +  
              "\\.]\\s?){6,15}[0-9]{1}$";  
       if (string.IsNullOrEmpty(cont) == true)  
         return false;  
       else if (cont.Length < 10)  
         return false;  
       else if(Regex.IsMatch(cont, pattern))  
         return false;  
       return true;  
     }  
   }  
 }  

Testing

• Run the Application
• Start the ASP.NET Core app locally (dotnet run or via Visual Studio).
• Navigate to the upload page.
• Continue testing by uploading excel file with different schenario.

Summary

Using ASP.NET Core + ClosedXML, we can build robust Excel file upload and validation features quickly. It provides an efficient and scalable solution without relying on COM interop or external tools.

Thanks