Read Header Row Cells of Excel file using OLEDB and ADO.Net in C# and VB.Net

Hi alibasha,

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

For reading Excel file you can refer below article.

Read and Import Excel Sheet using ADO.Net and C#

The Excel File

HTML

<asp:Button Text="Export" runat="server" OnClick="Export" />

Namespaces

C#

using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;

VB.Net

Imports System.Data.OleDb
Imports System.Data

Code

C#

protected void Export(object sender, EventArgs e)
{
    DataSet ds = ImportExcel(Server.MapPath("~/Book1.xls"));
    //DataSet ds = ImportExcel2007(Server.MapPath("~/Book1.xlsx"));
    List<string> id = new List<string>();
    List<string> name = new List<string>();
    List<string> country = new List<string>();

    int idIndex = 0;
    int nameIndex = 0;
    int countryIndex = 0;

    for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
    {
        if (ds.Tables[0].Rows[0][i].ToString().ToUpper() == "ID")
        {
            idIndex = i;
        }
        else if (ds.Tables[0].Rows[0][i].ToString().ToUpper() == "NAME")
        {
            nameIndex = i;
        }
        else if (ds.Tables[0].Rows[0][i].ToString().ToUpper() == "COUNTRY")
        {
            countryIndex = i;
        }
    }

    for (int i = 1; i < ds.Tables[0].Rows.Count; i++)
    {
        id.Add(ds.Tables[0].Rows[i][idIndex].ToString());
        name.Add(ds.Tables[0].Rows[i][nameIndex].ToString());
        country.Add(ds.Tables[0].Rows[i][countryIndex].ToString());
    }
}
//Read To Excel 97-2003 File
private DataSet ImportExcel(String strFilePath)
{
    String strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strFilePath + "; Extended Properties='Excel 8.0;IMEX=1;HDR=No'";
    OleDbConnection connExcel = new OleDbConnection(strExcelConn);
    OleDbCommand cmdExcel = new OleDbCommand();
    try
    {
        cmdExcel.Connection = connExcel;

        //Check if the Sheet Exists
        connExcel.Open();
        DataTable dtExcelSchema;
        //Get the Schema of the WorkBook
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        connExcel.Close();

        //Read Data from Sheet1
        connExcel.Open();
        OleDbDataAdapter da = new OleDbDataAdapter();
        DataSet ds = new DataSet();
        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
        //Range Query
        //cmdExcel.CommandText = "SELECT * From [" + SheetName + "A3:B5]";

        da.SelectCommand = cmdExcel;
        da.Fill(ds);
        connExcel.Close();
        return ds;
    }
    catch
    {
        return null;
    }
    finally
    {
        cmdExcel.Dispose();
        connExcel.Dispose();
    }
}
//Read To Excel 97-2007 File
private DataSet ImportExcel2007(String strFilePath)
{
    String strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + strFilePath + "; Extended Properties='Excel 8.0;IMEX=1;HDR=No'";
    OleDbConnection connExcel = new OleDbConnection(strExcelConn);
    OleDbCommand cmdExcel = new OleDbCommand();
    try
    {
        cmdExcel.Connection = connExcel;

        //Check if the Sheet Exists
        connExcel.Open();
        DataTable dtExcelSchema;
        //Get the Schema of the WorkBook
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        connExcel.Close();

        //Read Data from Sheet1
        connExcel.Open();
        OleDbDataAdapter da = new OleDbDataAdapter();
        DataSet ds = new DataSet();
        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";

        //Range Query
        //cmdExcel.CommandText = "SELECT * From [" + SheetName + "A3:B5]";

        da.SelectCommand = cmdExcel;
        da.Fill(ds);
        connExcel.Close();
        return ds;
    }
    catch
    {
        return null;
    }
    finally
    {
        cmdExcel.Dispose();
        connExcel.Dispose();
    }
}

VB.Net

Protected Sub Export(ByVal sender As Object, ByVal e As EventArgs)
    Dim ds As DataSet = ImportExcel(Server.MapPath("~/Book1.xls"))
    Dim id As List(Of String) = New List(Of String)()
    Dim name As List(Of String) = New List(Of String)()
    Dim country As List(Of String) = New List(Of String)()
    Dim idIndex As Integer = 0
    Dim nameIndex As Integer = 0
    Dim countryIndex As Integer = 0
    For i As Integer = 0 To ds.Tables(0).Columns.Count - 1
        If ds.Tables(0).Rows(0)(i).ToString().ToUpper() = "ID" Then
            idIndex = i
        ElseIf ds.Tables(0).Rows(0)(i).ToString().ToUpper() = "NAME" Then
            nameIndex = i
        ElseIf ds.Tables(0).Rows(0)(i).ToString().ToUpper() = "COUNTRY" Then
            countryIndex = i
        End If
    Next

    For i As Integer = 1 To ds.Tables(0).Rows.Count - 1
        id.Add(ds.Tables(0).Rows(i)(idIndex).ToString())
        name.Add(ds.Tables(0).Rows(i)(nameIndex).ToString())
        country.Add(ds.Tables(0).Rows(i)(countryIndex).ToString())
    Next
End Sub

Private Function ImportExcel(ByVal strFilePath As String) As DataSet
    Dim strExcelConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strFilePath & "; Extended Properties='Excel 8.0;IMEX=1;HDR=No'"
    Dim connExcel As OleDbConnection = New OleDbConnection(strExcelConn)
    Dim cmdExcel As OleDbCommand = New OleDbCommand()
    Try
        cmdExcel.Connection = connExcel
        connExcel.Open()
        Dim dtExcelSchema As DataTable
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
        connExcel.Close()
        connExcel.Open()
        Dim da As OleDbDataAdapter = New OleDbDataAdapter()
        Dim ds As DataSet = New DataSet()
        Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
        cmdExcel.CommandText = "SELECT * From [" & SheetName & "]"
        da.SelectCommand = cmdExcel
        da.Fill(ds)
        connExcel.Close()
        Return ds
    Catch
        Return Nothing
    Finally
        cmdExcel.Dispose()
        connExcel.Dispose()
    End Try
End Function

Private Function ImportExcel2007(ByVal strFilePath As String) As DataSet
    Dim strExcelConn As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strFilePath & "; Extended Properties='Excel 8.0;IMEX=1;HDR=No'"
    Dim connExcel As OleDbConnection = New OleDbConnection(strExcelConn)
    Dim cmdExcel As OleDbCommand = New OleDbCommand()
    Try
        cmdExcel.Connection = connExcel
        connExcel.Open()
        Dim dtExcelSchema As DataTable
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
        connExcel.Close()
        connExcel.Open()
        Dim da As OleDbDataAdapter = New OleDbDataAdapter()
        Dim ds As DataSet = New DataSet()
        Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
        cmdExcel.CommandText = "SELECT * From [" & SheetName & "]"
        da.SelectCommand = cmdExcel
        da.Fill(ds)
        connExcel.Close()
        Return ds
    Catch
        Return Nothing
    Finally
        cmdExcel.Dispose()
        connExcel.Dispose()
    End Try
End Function

Output