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

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[]>
                 {
                     table.DataRange.Rows()
                         .Select(tableRow =>
                             tableRow.Field("S.Number")
                                 .GetString())
                         .ToArray(),
                     table.DataRange.Rows()
                         .Select(tableRow => tableRow.Field("Name").GetString())
                         .ToArray(),
                     table.DataRange.Rows()
                     .Select(tableRow => tableRow.Field("Date").GetString())
                     .ToArray()
                 };
                 //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"] == ""))
                         dr.Delete();
                 }
                 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();
  
             table.Columns.Add("S.Number");
             table.Columns.Add("Name");
             table.Columns.Add("Date");
  
             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];
                 table.Rows.Add(row);
             }
             return table;
         }

2021-10-03