In this article I will explain with an example, how to read (import) Excel file (sheet) using OpenXml in ASP.Net Core MVC.
Note: For beginners in ASP.Net Core (.Net Core 7), 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.
 
 

Database

I have made use of the following table Customers with the schema as follows.
ASP.Net Core: Read (Import) Excel file using OpenXml
 
Note: You can download the database table SQL by clicking the download link below.
          Download SQL file
 
 

Connection Strings in AppSettings.json file

The connection string to the Database is saved in the AppSettings.json file.
Note: For more details about saving and reading connection strings in AppSettings.json file, please refer .Net Core 7: Read Connection String from AppSettings.json file.
 
{
 "ConnectionStrings": {
    "constr": "Data Source=.\\SQL2022;Initial Catalog=AjaxSamples;Integrated Security=true"
 }
}
 
 

Namespaces

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

Controller

The Controller consists of following Action methods.

Action method for handling GET operation

Inside this Action method, simply the View is returned.
 

Action method for handling POST operation

This Action method gets called when Import Button is clicked, and it gets the uploaded file in the IFormFile parameter.
Note: In case the IFormFile parameter is appearing NULL, then please refer the article, ASP.Net Core: IFormFile always returns NULL.
 
The path of the Uploads Folder (Directory) where uploaded Excel file will be saved is fetched using IWebHostEnvironemnt interface and if it does not exists then it is created using CreateDirectory method.
Note: The IWebHostEnvironment interface is injected using Dependency Injection inside the Controller, for more details 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.
 
The uploaded Excel file is saved to a Folder (Directory) named Uploads and an object of DataTable is created.
Then, then it is opened and read using OpenXml SpreadSheetDocument class object.
After that, the instance of the first Sheet is determined and all the rows present in the Sheet are fetched.
Then, using nested FOR EACH loop is executed over the fetched rows and GetValue method (explained later) is called and columns are added to the DataTable.
 

GetValue

The GetValue function 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.
 
Now a connection is established with the database and the SqlBulkCopy object is initialized and the name of the Table is specified using the DestinationTableName property.
Finally, the columns are mapped and all the rows from the DataTable are inserted into the SQL Server table.
Note: The mapping of columns of the DataTable and the SQL Server table is optional and you need to do only in case where your DataTable and/or the SQL Server Table do not have same number of columns or the names of columns are different.
 
public class HomeController : Controller
{
    private IWebHostEnvironment Environment;
    private IConfiguration Configuration;
    public HomeController(IWebHostEnvironment _environment, IConfiguration _configuration)
    {
        this.Environment = _environment;
        this.Configuration = _configuration;
    }
 
    public IActionResult Index()
    {
        return View();
    }
 
    [HttpPost]
    public IActionResult Index(IFormFile postedFile)
    {
        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);
            }
 
            //Create a new DataTable.
            DataTable dt = new DataTable();
 
            //Open the Excel file in Read Mode using OpenXml.
            using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filePath, false))
            {
                //Read the first Sheet 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)
                {
                    //Use the first row to add columns to DataTable.
                    if (row.RowIndex.Value == 1)
                    {
                        foreach (Cell cell in row.Descendants<Cell>())
                        {
                            dt.Columns.Add(GetValue(doc, cell));
                        }
                    }
                    else
                    {
                        //Add rows to DataTable.
                        dt.Rows.Add();
                        int i = 0;
                        foreach (Cell cell in row.Descendants<Cell>())
                        {
                            dt.Rows[dt.Rows.Count - 1][i] = GetValue(doc, cell);
                            i++;
                        }
                    }
                }
            }
 
            //Insert the Data read from the Excel file to Database Table.
            string constr = this.Configuration.GetConnectionString("constr");
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                {
                    //Set the database table name.
                    sqlBulkCopy.DestinationTableName = "dbo.Customers";
 
                    //[OPTIONAL]: Map the Excel columns with that of the database table.
                    sqlBulkCopy.ColumnMappings.Add("Customer Id", "CustomerId");
                    sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                    sqlBulkCopy.ColumnMappings.Add("Country", "Country");
 
                    con.Open();
                    sqlBulkCopy.WriteToServer(dt);
                    con.Close();
                }
            }
        }
 
        return View();
    }
 
    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

The View consists of an HTML Form with following ASP.Net Tag Helpers 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.
The Form consists of an HTML FileUpload element and a Submit Button.
Note: The Form element must be specified with enctype attribute and its value must be multipart/form-data, otherwise the IFormFile parameter will be NULL.
 
@addTagHelper*, Microsoft.AspNetCore.Mvc.TagHelpers
@{
    Layout = null;
}
 
<!DOCTYPE html>
 
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    <form asp-controller="Home" asp-action="Index" method="post" enctype="multipart/form-data">
        <input type="file" name="postedFile" />
        <input type="submit"value="Import" />
    </form>
</body>
</html>
 
 

Screenshots

ASP.Net Core: Read (Import) Excel file using OpenXml
 

The Excel File

ASP.Net Core: Read (Import) Excel file using OpenXml
 

Table containing the data from the Excel file

ASP.Net Core: Read (Import) Excel file using OpenXml
 
 

Downloads

Download Code


Other available versions