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




About The Author

Suprotim Agarwal
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.

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?