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
 |
|
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
|
|||
|
|
21 comments :
Hi suresh
i am getting the following error can u help
'The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data."
hii suresh i am getting error like
The Microsoft 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. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator.
provide me some help
actually where you copied all these codings
Hi;
can you help me solving this error...
'The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data."
The Microsoft 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
Could not find installable ISAM.....:(........anyone help me out...
Hi suresh
i am getting the following error can u help
'The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data."
please solve the problem its necessary
when program is rununning
and i click on import data after choose file
this error occur
The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.
whoever is having problem like "The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data." check below article to solve that problem
http://www.aspdotnet-suresh.com/2013/01/c-microsoft-office-access-database.html
How can i read excel file and make update sql script based upon some column value .Also i want to write these update sql script to a text file ..
Someone please help.
How can i read excel file and make update sql script based upon some column value .Also i want to write these update sql script to a text file ..
Someone please help.
i copy paste above code ... how to obtain connectionstring ?
i got an error
The Microsoft Office Access database engine could not find the object 'Sheet2$'. Make sure the object exists and that you spell its name and the path name correctly.
i copy paste above code ... how to obtain connectionstring ?
its still not working sir
Hello sir,
which excel connection string is used if we upload the website on remote server
hi suresh.... i am getting the following error can u help me plz sir....
The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.
hi
am getting this error
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine
@gayathri n...
check this article http://www.aspdotnet-suresh.com/2012/12/iis7-microsoftaceoledb120-provider-is.html
Thank you suresh.I tried that only.but did not get the answer.All your posts are very usefull.Thank you very much.
hii suresh i am getting error like
The Microsoft 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. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator.
provide me some help