Import Excel Data Into An ASP.NET GridView using OLEDB

Tweet

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

Tweet

About The Author

Suprotim Agarwal, Developer Technologies MVP (Microsoft Most Valuable Professional) is the founder and contributor for DevCurry, DotNetCurry and SQLServerCurry. He is the Chief Editor of a Developer Magazine called DNC Magazine. He has also authored two Books - 51 Recipes using jQuery with ASP.NET Controls. and The Absolutely Awesome jQuery CookBook.

Follow him on twitter @suprotimagarwal.