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

alibasha
 
on Jan 08, 2018 12:29 AM
Sample_154978.zip
16094 Views

Hi,

I have one excel with one sheet, which contains 3 columns as Customers,Products and Orders. I just need to extract values based on Header/Column Names and put in each collection(in this case 3 collections). Thanks in advance.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jan 08, 2018 03:29 AM
on Jan 09, 2018 02:32 AM

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