How To Get Data From Excel To DataTable Using ClosedXML C#

Minifycode 2021-10-03 Viewed 3.6K times C#

In this article, you will learn how To Get Data From Excel To DataTable Using ClosedXML In C#

In this article, how to get data from excel to datatable using closed xml in c#

Refer this link github. In this article we will see how to easily fetch data from Excel by column names.


private void FetchDataFromExcel()
             var xmlFile = Path.Combine(Environment.CurrentDirectory, "Data\\Usingcsharp_Data.xlsx");
             using (var workBook = new XLWorkbook(xmlFile))
                 var workSheet = workBook.Worksheet(1);
                 var firstRowUsed = workSheet.FirstRowUsed();
                 var firstPossibleAddress = workSheet.Row(firstRowUsed.RowNumber()).FirstCell().Address;
                 var lastPossibleAddress = workSheet.LastCellUsed().Address;               
                 var range = workSheet.Range(firstPossibleAddress, lastPossibleAddress).AsRange(); //.RangeUsed();
                 var table = range.AsTable();               
                 var dataList = new List<string[]>
                         .Select(tableRow =>
                         .Select(tableRow => tableRow.Field("Name").GetString())
                     .Select(tableRow => tableRow.Field("Date").GetString())
                 //Convert List to DataTable
                 var dataTable = ConvertListToDataTable(dataList);
                 //Unique column values, to avoid duplication
                 var uniqueCols = dataTable.DefaultView.ToTable(true, "S.Number");
                 for (var i = uniqueCols.Rows.Count - 1; i >= 0; i--)
                     var dr = uniqueCols.Rows[i];
                     if (dr != null && ((string)dr["S.Number"] == "None" || (string)dr["Title"] == ""))
                 Console.WriteLine("Total number of unique s.number in Excel : " + uniqueCols.Rows.Count);
         private static DataTable ConvertListToDataTable(IReadOnlyList<string[]> list)
             var table = new DataTable("CustomTable");
             var rows = list.Select(array => array.Length).Concat(new[] { 0 }).Max();
             for (var j = 0; j < rows; j++)
                 var row = table.NewRow();
                 row["S.Number"] = list[0][j];
                 row["Name"] = list[1][j];
                 row["Date"] = list[2][j];
             return table;

How To Get Data From Excel To DataTable Using ClosedXML In C#
C# is a programming language developed by Microsoft that runs on the .NET Framework. C# is used to develop web, desktop, mobile, games and much more application. C# is a object-oriented programming language developed by Microsoft within its .NET Framework. Led by Anders Hejlsberg, your basic C# programming and will also take you through various advanced concepts related to C# programming language. C# such as control statements, objects and classes, inheritance, constructor, destructor, this, static, sealed, polymorphism, abstraction, abstract class, interface, File IO, Collections, namespace, encapsulation, properties, indexer, arrays, strings, regex, exception handling, multithreading etc. For example... using System; namespace MinifyCode { class Program { static void Main(string[] args) { Console.WriteLine("Hello Minify Code"); } } } Output: Hello Minify Code In this article you will learn, what is server side controls. We will discuss each of these objects in due time. In this tutorial we will explore the Server object, the Request object, and the Response object. Session Application Cache Request Response Server User Trace Server Object The Server object in Asp.NET is an instance of the System.Web.HttpServerUtility class. The HttpServerUtility class provides numerous properties and methods to perform many type of jobs. Methods and Properties of the Server object The methods and properties of the HttpServerUtility class are exposed through the intrinsic Server object provided by ASP.NET. using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.Optimization; using System.Web.Routing; using System.Web.Security; using System.Web.SessionState; using System.Data.Entity; namespace minifycode { public class Global : HttpApplication { void Application_Start(object sender, EventArgs e) { // Code that runs on application startup RouteConfig.RegisterRoutes(RouteTable.Routes); BundleConfig.RegisterBundles(BundleTable.Bundles); // Initialize the product database. Database.SetInitializer(new ProductDatabaseInitializer()); // Create custom role and user. RoleActions roleActions = new RoleActions(); roleActions.AddUserAndRole(); // Add Routes. RegisterCustomRoutes(RouteTable.Routes); } void RegisterCustomRoutes(RouteCollection routes) { routes.MapPageRoute( "ProductsCategoryRoute", "Category/{categoryName}", "~/ProductList.aspx" ); routes.MapPageRoute( "ProductNameRoute", "Product/{productName}", "~/ProductDetails.aspx" ); } } }