C# Convert Excel To DataTable

In this article. I will demonstrate how to convert an Excel file[Sheet data] to DataTable in C# using the EPPlus library. 

Introduction

In real-world scenarios, Excel sheets are very useful for Data Entry, Accounting, Budgeting, Reporting, and many forms of data.

Many times, we wish to use this data in our application. There are numerous tools and packages available in C# to convert Excel data into application format. In this article, we will be using EPPlus package.

EPPlus is a very helpful open-source 3rd party library and a highly popular spreadsheet/xlsx library for .NET Framework/.NET Core. It is used to create, read, update, and calculate workbook data of excel. For More details about EPPlus you can click here.

Let's see how we can convert an Excel file data to a DataTable using EPPlus library

Getting started

Step 1

Create a new C# console application in Visual Studio.

Step 2

Then, install the EPPlus NuGet package as a reference to the application from NuGet Package Manager. You can install the latest stable version. 

Step 3

Now, include the following namespaces in the class file.

using OfficeOpenXml;
using System;
using System.Data;
using System.IO;

Step 4

Now, add the following Class to your project.

In the given method, return DataTable as output, and have 3 parameters

• filePath - Provide the full directory path of your file.
• sheetName - Provide the sheet name for which you want to import your data in Datatable. Ex: Sheet1
• hasHeader - If your sheet contains a header, then pass true else false. 

public class ExcelUtility
{
	public static DataTable ExcelDataToDataTable(string filePath, string sheetName, bool hasHeader = true)
	{
		var dt = new DataTable();
		var fi = new FileInfo(filePath);
		// Check if the file exists
		if (!fi.Exists)
			throw new Exception("File " + filePath + " Does Not Exists");

		ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
		var xlPackage = new ExcelPackage(fi);
		// get the first worksheet in the workbook
		var worksheet = xlPackage.Workbook.Worksheets[sheetName];

		dt = worksheet.Cells[1, 1, worksheet.Dimension.End.Row, worksheet.Dimension.End.Column].ToDataTable(c =>
		{
			c.FirstRowIsColumnNames = true;
		});

		return dt;
	}
}

Step 5

Now, use ExcelUtility class and ExcelDataToDataTable method to convert your Excel data to Datatable in your main class.

class Program
{
	static void Main(string[] args)
	{
		var path = @"C:\Users\XXX\ExcelFile.xlsx";
		var data = ExcelUtility.ExcelDataToDataTable(path, "Sheet1");
	}
}

If your Excel file data is like below:

The method will convert the data into Datatable as follows:

The method will convert your first Row Into DataTable's ColumnName and from secondd row data will be added to it. 

I have also attached a sample code.

Note: New license EPPlus is still free to use in some cases, but will require a commercial license to be used in a commercial business.More detail

Summary

In this article, we learned about how we can use convert our Excel data into DataTable using EPPlus library. 

I hope you will find this article helpful. If you have any suggestions, then please feel free to ask in the comment section.

Thank you.