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

Will you give this article a +1 ? Thanks in advance


About The Author

Suprotim Agarwal
Suprotim Agarwal, ASP.NET Architecture MVP (Microsoft Most Valuable Professional) 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 is the Editor of a Developer Magazine called DNC Magazine. He has also written two EBooks 51 Recipes using jQuery with ASP.NET Controls. and The Absolutely Awesome jQuery CookBook

Follow him on twitter @suprotimagarwal

2 comments:

Anonymous said...

I am using OLEDB providers and have some problems with some Excel files - not getting the last row unless the worksheet is open in Excel. I wonder if you have seen this issue?

mkamoski said...

Is there a variation of this that does use OLEDB but does not require a physical file path but takes string input (CSV) instead?