In this article I will explain with an example, how to upload, read and display Excel file data using OpenXml in ASP.Net Core (.Net Core) MVC.
Note: For beginners in ASP.Net Core (.Net Core 7) MVC, please refer my article ASP.Net Core 7: Hello World Tutorial with Sample Program example.
 
 

Install DocumentFormat.OpenXml package

In order to install DocumentFormat.OpenXml library using Nuget, please refer my article Install DocumentFormat.OpenXml Nuget Package.
 
 

Namespaces

You will need to import the following namespaces.
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
 
 

Model

The Model class consists of following properties.
public class CustomerModel
{
    ///<summary>
    /// Gets or sets CustomerId.
    ///</summary>
    public int CustomerId { get; set; }
 
    ///<summary>
    /// Gets or sets Name.
    ///</summary>
    public string Name { get; set; }
 
    ///<summary>
    /// Gets or sets Country.
    ///</summary>
    public string Country { get; set; }
}
 
 

Controller

Inside the Controller, first the private property IWebHostEnvironment interface is created.
Then, the interface is injected into the Constructor (HomeController) using Dependency Injection method and the injected object is assigned to private property (created earlier).
The Controller consists of following Action methods.

Action method for handling GET operation

Inside this Action method, simple the View is returned.
 

Action method for handling POST operation

This Action method gets called when the Import Button is clicked and it accepts IFormFile class as a parameter.
Note: The name of the IFormFile parameter and the name of HTML FileUpload element must be exact same, otherwise the IFormFile parameter will be NULL.
 
First a Generic List collection of CustomerModel class object is created and a check is performed if the file is selected or not, if selected then the selected file is saved into a Folder (Directory) named Uploads.
Note: For more details about IWebHostEnvironment interface, please refer my article Using IWebHostEnvironment in ASP.Net Core. For more details on how to access Static Files in Core, please refer my article Static Files (Images, CSS and JS files) in ASP.Net Core.
 
An object of FileStream class is created and using CopyTo method of IFormFile class the file is saved to the Directory (Folder).
After that, the instance of the first Sheet is determined and all the rows present in the Sheet are fetched.
Then, a FOR EACH loop is executed over the fetched rows and using GetValue method (explained later) is called, and columns are defined and data of the Excel file is added to the Generic List collection of CustomerModel class object.
Finally, a Generic List collection of CustomerModel class object is returned to the View.
 

GetValue

The GetValue method accepts SpreadsheetDocument and Cell class objects as a parameter where a cell value is determined and initialized.
Then, a check is performed if cell datatype NOT equal to NULL and cell value is string then, it returns the cell value.
public class HomeController : Controller
{
    private IWebHostEnvironment Environment;
    public HomeController(IWebHostEnvironment _environment)
    {
        this.Environment = _environment;
    }
 
    public IActionResult Index()
    {
        return View();
    }
 
    [HttpPost]
    public IActionResult Index(IFormFile postedFile)
    {
        List<CustomerModel> customers = new List<CustomerModel>();
        if (postedFile != null)
        {
            string path = Path.Combine(this.Environment.WebRootPath, "Uploads");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
 
            //Save the uploaded Excel file.
            string fileName = Path.GetFileName(postedFile.FileName);
            string filePath = Path.Combine(path, fileName);
            using (FileStream stream = new FileStream(filePath, FileMode.Create))
            {
                postedFile.CopyTo(stream);
            }
 
            //Open the Excel file in Read Mode using OpenXml.
            using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filePath, false))
            {
                //Read the first Sheets from Excel file.
                Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild<Sheet>();
 
                //Get the Worksheet instance.
                Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet;
 
                //Fetch all the rows present in the Worksheet.
                IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Descendants<Row>();
 
                //Loop through the Worksheet rows.
                foreach (Row row in rows)
                {
                    //Add rows to cell.
                    if (row.RowIndex.Value > 1)
                    {
                        List<Cell> cells = row.Descendants<Cell>().ToList();
                        customers.Add(new CustomerModel
                        {
                             CustomerId = Convert.ToInt32(this.GetValue(doc, cells[0])),
                             Name = this.GetValue(doc, cells[1]).ToString(),
                             Country = this.GetValue(doc, cells[2]).ToString()
                        });
                    }
                }
            }
        }
        return View(customers);
    }
 
    private string GetValue(SpreadsheetDocument doc, Cell cell)
    {
        string value = cell.CellValue.InnerText;
        if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
        {
            return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(int.Parse(value)).InnerText;
        }
        return value;
    }
}
 
 

View

HTML Markup

Inside the View, the Generic List collection of CustomerModel class is declared as Model for the Views.
The View consists of an HTML Form which has been created using the following TagHelpers attributes.
asp-action – Name of the Action. In this case the name is Index.
asp-controller – Name of the Controller. In this case the name is Home.
method – It specifies the Form Method i.e. GET or POST. In this case it will be set to POST.
HtmlAttributes – This array allows to specify the additional Form Attributes. Here we need to specify enctype = “multipart/form-data” which is necessary for uploading Files.
The View also consists of an HTML FileUpload element and a Submit Button.
 

Submitting the Form

When the Submit Button is clicked then, the Model is checked for NULL and if it is not NULL then, a FOR EACH loop will be executed over the Model which will generate the HTML Table rows with the Excel file data.
@using Excel_Import_OpenXML_Core.Models
@model List<CustomerModel>
@addTagHelper*,Microsoft.AspNetCore.Mvc.TagHelpers
@{
     Layout = null;
}
 
<!DOCTYPE html>
 
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    <form method="post" asp-controller="Home" asp-action="Index" enctype="multipart/form-data">
        <input type="file" name="postedFile" /> 
        <input type="submit" value="Import" />
    </form>
    <hr />
    @if (Model != null)
    {
        <table cellpadding="0" cellspacing="0">
            <tr>
                <th>Customer Id</th>
                <th>Name</th>
                <th>Country</th>
            </tr>
            @foreach (CustomerModel customer in Model)
            {
                <tr>
                    <td>@customer.CustomerId</td>
                    <td>@customer.Name</td>
                    <td>@customer.Country</td>
                </tr>
            }
        </table>
    }
</body>
</html>
 
 

Screenshots

The Excel File

ASP.Net Core: Upload, Read and Display Excel file data using OpenXml
 

Grid displaying Excel data

ASP.Net Core: Upload, Read and Display Excel file data using OpenXml
 
 

Downloads

Download Code