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

'Like' us on our FaceBook page if you find this blog useful. Thanks!

Did you like this post?

			Save on Delicious
	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal