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; } }
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.