Read (Import) Excel file data in Console Application using C# and VB.Net

Hi irshadmpct,

Check this example. Now please take its reference and correct your code.

Install ClosedXml from nuget.

Install-Package ClosedXML -Version 0.95.4

Namespaces

C#

using System.Data;
using ClosedXML.Excel;

VB.Net

Imports System.Data
Imports ClosedXML.Excel

Code

C#

class Program
{
    static void Main(string[] args)
    {
        Console.WriteLine("Enter Excel path");
        string filePath = Console.ReadLine();
        DataTable dt = GetExcelDataTable(filePath);
        foreach (DataRow dataRow in dt.Rows)
        {
            foreach (var item in dataRow.ItemArray)
            {
                Console.WriteLine(item);
            }
        }
        Console.ReadLine();
    }

    public static DataTable GetExcelDataTable(string filePath)
    {
        DataTable dt = new DataTable();
        using (XLWorkbook workBook = new XLWorkbook(filePath))
        {
            IXLWorksheet workSheet = workBook.Worksheet(1);
            bool firstRow = true;
            foreach (IXLRow row in workSheet.Rows())
            {
                if (firstRow)
                {
                    foreach (IXLCell cell in row.Cells())
                    {
                        dt.Columns.Add(cell.Value.ToString());
                    }
                    firstRow = false;
                }
                else
                {
                    dt.Rows.Add();
                    int i = 0;
                    foreach (IXLCell cell in row.Cells())
                    {
                        dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                        i++;
                    }
                }
            }
        }

        return dt;
    }
}

VB.Net

Class Program
    Private Shared Sub Main(ByVal args As String())
        Console.WriteLine("Enter Excel path")
        Dim filePath As String = Console.ReadLine()
        Dim dt As DataTable = GetExcelDataTable(filePath)

        For Each dataRow As DataRow In dt.Rows
            For Each item In dataRow.ItemArray
                Console.WriteLine(item)
            Next
        Next

        Console.ReadLine()
    End Sub

    Public Shared Function GetExcelDataTable(ByVal filePath As String) As DataTable
        Dim dt As DataTable = New DataTable()

        Using workBook As XLWorkbook = New XLWorkbook(filePath)
            Dim workSheet As IXLWorksheet = workBook.Worksheet(1)
            Dim firstRow As Boolean = True

            For Each row As IXLRow In workSheet.Rows()
                If firstRow Then
                    For Each cell As IXLCell In row.Cells()
                        dt.Columns.Add(cell.Value.ToString())
                    Next
                    firstRow = False
                Else
                    dt.Rows.Add()
                    Dim i As Integer = 0
                    For Each cell As IXLCell In row.Cells()
                        dt.Rows(dt.Rows.Count - 1)(i) = cell.Value.ToString()
                        i += 1
                    Next
                End If
            Next
        End Using

        Return dt
    End Function
End Class

Screenshots

The Excel File

Output