C#, Featured

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

August 15, 2022

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”

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

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())
                        {
                            dt.Columns.Add(cell.Value.ToString());
                        }
                        firstRow = false;
                    }
                    else
                    {
                        //Adding rows to DataTable.
                        dt.Rows.Add();
                        int i = 0;
                        foreach (IXLCell cell in row.Cells())
                        {
                            dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                            i++;
                        }
                    }
                }

                //Bind DataTable to DataGridView

                dgvData.DataSource = dt;
            }
        }