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.
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
<html xmlns="http://www.w3.org/1999/xhtml"> <head> <title>Read and Display Data From an Excel File (.xsl or .xlsx) in ASP.NET</title> </head> <body> <form id="form1" runat="server"> <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"> <HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" /> </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
No comments:
Post a Comment