Introduction:
Here I will explain how to import data from excel to gridview in asp.net using OLEDB 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.
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.
 |
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