C# - Export Excel Data to Datatable or Dataset or Gridview in Asp.net - ASP.NET,C#.NET,VB.NET,JQuery,JavaScript,Gridview

C# - Export Excel Data to Datatable or Dataset or Gridview in Asp.net

By: Suresh Dasari Nov 5, 2014

Introduction:

Here I will explain how to export excel data to datatable or dataset in C#, VB.NET or export data from excel to gridview in asp.net using OLEDB in C#, VB.NET.

Description:

In previous article I explained Export gridview data to excel in asp.net, How to Export gridview data to excel/word document, Import Excel data to sql database and some other articles relating to export gridview data. Now I will explain how to export excel data to datatable or dataset or gridview in asp.net using C#, VB.NET.

To implement this concept first we need to create one excel file like as shown below

Once excel creation done we need to create new website and write the following code in your aspx page

<head>

<title>Read and Display Data From an Excel File (.xsl or .xlsx) in ASP.NET</title>

</head>

<body>

<div>

<b>Please Select Excel File: </b>

<asp:FileUpload ID="fileuploadExcel" runat="server" />  

<asp:Button ID="btnImport" runat="server" Text="Import Data" OnClick="btnImport_Click" />

<br />

<asp:Label ID="lblMessage" runat="server" Visible="False" Font-Bold="True" ForeColor="#009933"></asp:Label><br />

<asp:GridView ID="grvExcelData" runat="server">

</asp:GridView>

</div>

</form>

</body>

</html>

Now open code behind file and add the following namespaces

using System;

using System.Data;

using System.Data.OleDb;

using System.IO;

After that write the following code in code behind

C#.NET Code

protected void btnImport_Click(object sender, EventArgs e)

{

string connString = "";

string strFileType = Path.GetExtension(fileuploadExcel.FileName).ToLower();

string path = fileuploadExcel.PostedFile.FileName;

//Connection String to Excel Workbook

if (strFileType.Trim() == ".xls")

{

connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";

}

else if (strFileType.Trim() == ".xlsx")

{

connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";

}

string query = "SELECT [UserName],[Education],[Location] FROM [Sheet1$]";

OleDbConnection conn = new OleDbConnection(connString);

if (conn.State == ConnectionState.Closed)

conn.Open();

OleDbCommand cmd = new OleDbCommand(query, conn);

OleDbDataAdapter da = new OleDbDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

grvExcelData.DataSource = ds.Tables[0];

grvExcelData.DataBind();

da.Dispose();

conn.Close();

conn.Dispose();

}

VB.NET Code:

Imports System.Data

Imports System.Data.OleDb

Imports System.IO

Partial Class Default2

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

End Sub

Protected Sub btnImport_Click(ByVal sender As Object, ByVal e As EventArgs)

Dim connString As String = ""

Dim strFileType As String = Path.GetExtension(fileuploadExcel.FileName).ToLower()

Dim path__1 As String = fileuploadExcel.PostedFile.FileName

'Connection String to Excel Workbook

If strFileType.Trim() = ".xls" Then

connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path__1 & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""

ElseIf strFileType.Trim() = ".xlsx" Then

connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path__1 & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"""

End If

Dim query As String = "SELECT [UserName],[Education],[Location] FROM [Sheet1$]"

Dim conn As New OleDbConnection(connString)

If conn.State = ConnectionState.Closed Then

conn.Open()

End If

Dim cmd As New OleDbCommand(query, conn)

Dim da As New OleDbDataAdapter(cmd)

Dim ds As New DataSet()

da.Fill(ds)

grvExcelData.DataSource = ds.Tables(0)

grvExcelData.DataBind()

da.Dispose()

conn.Close()

conn.Dispose()

End Sub

End Class

Demo

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS

Subscribe by Email

2 comments :

meet Doshi said...: Find this Error:
The Microsoft Office Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.
I have use Sheet1 in excel file; November 9, 2014 at 10:55 PM
ZCXszcZc said...: i got the same error; November 19, 2014 at 10:58 PM

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

Subscribe to: Post Comments ( Atom )

ASP.NET,C#.NET,VB.NET,JQuery,JavaScript,Gridview