Import Excel Data Into An ASP.NET GridView using OLEDB

I have seen a lot of users asking how to import data from an excel sheet into an ASP.NET GridView.

I will show two ways to do so - Using OLEDB and Using Microsoft.Office.Interop.Excel

In this post, we will see how to import data using OLEDB

C#

using System.Data.OleDb;
using System.Data;

public partial class UploadD : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
    string cnstr = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\a.xls;"
    + "Extended Properties=Excel 8.0";
    OleDbConnection oledbConn = new OleDbConnection(cnstr);
    string strSQL = "SELECT * FROM [Sheet$]";

    OleDbCommand cmd = new OleDbCommand(strSQL, oledbConn);
    DataSet ds = new DataSet();
    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
    da.Fill(ds);
    GridView1.DataSource = ds;
    GridView1.DataBind();
}
}

VB.NET

Imports System.Data.OleDb
Imports System.Data

Partial Public Class UploadD
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
    Dim cnstr As String = "Provider=Microsoft.Jet.Oledb.4.0;" &_
    "Data Source=C:\a.xls; Extended Properties=Excel 8.0"
        Dim oledbConn As New OleDbConnection(cnstr)
        Dim strSQL As String = "SELECT * FROM [Sheet$]"

        Dim cmd As New OleDbCommand(strSQL, oledbConn)
        Dim ds As New DataSet()
        Dim da As New OleDbDataAdapter(cmd)
        da.Fill(ds)
        GridView1.DataSource = ds
        GridView1.DataBind()
    End Sub
End Class

In the next article, we will see how to import Excel Data Into an ASP.NET GridView using Microsoft.Office.Interop.Excel