C# Convert Excel To DataTable Using EPPlus

Excel sheets are extremely useful in real-world scenarios for Data Entry, Accounting, Budgeting, Reporting, and many other types of data.

We frequently want to use this data in our application. To convert Excel data into application format, there are numerous tools and packages available in C#. We will be using the EPPlus package in this article.

EPPlus is a very useful open-source 3rd party library for.NET Framework/.NET Core, as well as a very popular spreadsheet/xlsx library. It is used to create, read, update, and calculate Excel workbook data.

Let’s look at how we can use the EPPlus library to convert data from an Excel file to a DataTable.

Step 1: Create a new C# console application in Visual Studio.

Step 2: Install the EPPlus NuGet package as a reference to the application. 

Step 3: Include the following Namespace

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

Step 4: Add the following Class to your project, Copy and Paste the below code.

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:In your main class, use the ExcelUtility class and the ExcelDataToDataTable method to convert your Excel data to a Datatable.

public class Program
    {
        static void Main(string[] args)
        {
            var path = @"C:\Users\pc3\Desktop\Excel To DataTable\Files\Exceldata.xlsx";
            var data = ExcelUtility.ExcelDataToDataTable(path, "Sheet1");
        }
      
    }

If excel data is lIke below

The following is how the method will convert the data into a Datatable:

The method will convert your first row into the DataTable’s ColumnName and add data from the second row to it.