Easiest Way to Read and Import Excel File Data into DataTable C#

In this article we will discuss how to Read or Import Excel file data into C# DataTable. For this example we will be using the ClosedXML .NET library.


What is ClosedXML?

ClosedXML is .NET library which is used for manipulating and writing the 2007+ Excel files. The ClosedXML library is available for free on GitHub.


Read and Import Excel Data to DataTable using ClosedXML

I will be using a windows forms application for this example. On button click the excel file data will be read, imported to DataTable and will bind the same DataTable to GridView. I have a sample excel file with dummy data at location “D:\ClosedXML”

File Location


Sample Data


Add ClosedXML NuGet Package

Got to Tools > NuGet Package Manager > Package Manager Console and run the below command

Install-Package ClosedXML -Version 0.96.0

NuGet Package


C# Code

Import below namespace

using ClosedXML.Excel;

        private void btnImport_Click(object sender, EventArgs e)
            using (XLWorkbook workBook = new XLWorkbook(@"D:\ClosedXML\SampleData.xlsx"))
                //Read the first Sheet from Excel file.
                //Here SalesOrders is the Work Sheet Name.
                IXLWorksheet workSheet = workBook.Worksheet("SalesOrders");

                //Creating a new datatable
                DataTable dt = new DataTable();

                //Looping through the Worksheet rows.
                bool firstRow = true;

                foreach (IXLRow row in workSheet.Rows())
                    //Using the first row to add columns names to DataTable.
                    if (firstRow)
                        foreach (IXLCell cell in row.Cells())
                        firstRow = false;
                        //Adding rows to DataTable.
                        int i = 0;
                        foreach (IXLCell cell in row.Cells())
                            dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();

                //Bind DataTable to DataGridView

                dgvData.DataSource = dt;




Wrapping Up

I hope, this blog post has helped you in learning and understanding how to read or import excel file data in C# datatable.

Don’t forget to check out: Generate QR Code in Asp.net Core with 4 Simple Steps

Thank You.

Share on facebook
Share on twitter
Share on linkedin

Related articles