Knowledge Point: Import/Upload Excel Data to Asp.net Gridview in C#, VB.NET

Sunday, 19 January 2014

Import/Upload Excel Data to Asp.net Gridview in C#, VB.NET

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

Description:

In previous article I explained 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 import data from excel to gridview in asp.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

Knowledge Point

Sunday, 19 January 2014

Import/Upload Excel Data to Asp.net Gridview in C#, VB.NET

No comments:

Post a Comment