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