logo

,

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())
                        {
                            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;
            }
        }

Demo

Demo

 

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.

2 Responses

  1. Wow that was odd. I just wrote an incredibly long comment but after I clicked submit my comment didn’t appear. Grrrr… well I’m not writing all that over again. Anyways, just wanted to say excellent blog!

  2. Thank you for posting this, I was looking to populate a datatable from Excel file. I worked with ClosedXML before, its an awesome library.

Leave a Reply

Your email address will not be published.

Share on facebook
Share on twitter
Share on linkedin

Related articles