In this article I will explain with an example, how to upload, read (import) and display Excel file data using OpenXml in ASP.Net Core (.Net Core) Razor Pages.
Note: For beginners in ASP.Net Core (.Net Core 7) Razor Pages, please refer my article ASP.Net Core 7 Razor Pages: 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; }
}
 
 

Index PageModel (Code-Behind)

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

Handler method for handling GET operation

This Handler method left empty as it is not required.
 

Handler method for handling POST operation

This Handler 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 using the property of CustomerModel class (created earlier) 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 (created earlier).
 

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 IndexModel : PageModel
{
    public List<CustomerModel> Customers{ get; set; }
    private IWebHostEnvironment Environment;
 
    public IndexModel(IWebHostEnvironment _environment)
    {
        this.Environment = _environment;
    }
 
    public void OnGet()
    {
    }
 
    public void OnPostImport(IFormFile postedFile)
    {
        this.Customers = new List<CustomerModel>();
        if (postedFile != null)
        {
 
            //Create a Folder.
            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()
                        });
                    }
                }
            }
        }
    }
 
    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;
    }
}
 
 

Razor Page (HTML)

HTML Markup

Inside the Razor Page, first the TagHelpers is inherited.
The Razor Page consists of an HTML Form which has been created using the following TagHelpers attribute.
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 Form also consists of an HTML FileUpload element and a Submit Button.
The Submit Button has been set with the POST Handler method using the asp-page-handler attribute.
Note: In the Razor PageModel, the Handler method name is OnPostImport but here it will be specified as Import when calling from the Razor HTML Page.
 

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.
@page
@using Excel_Import_OpenXML_Core_Razor.Models
@addTagHelper*,Microsoft.AspNetCore.Mvc.TagHelpers
@model Excel_Import_OpenXML_Core_Razor.Pages.IndexModel
@{
    Layout = null;
}
 
<!DOCTYPE html>
 
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    <form method="post" enctype="multipart/form-data">
        <input type="file" name="postedFile" /> 
        <input type="submit" value="Import" asp-page-handler="Import" />
    </form>
    @if (Model.Customers != null)
    {
        <hr />
        <table border="0" cellpadding="0" cellspacing="0">
            <tr>
                <th>Customer Id</th>
                <th>Name</th>
                <th>Country</th>
            </tr>
            @foreach (CustomerModel customer in Model.Customers)
            {
                <tr>
                    <td>@customer.CustomerId</td>
                    <td>@customer.Name</td>
                    <td>@customer.Country</td>
                </tr>
            }
        </table>
    }
</body>
</html>
 
 

Screenshots

The Excel File

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

Grid displaying Excel data

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

Downloads

Download Code