Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

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

Dec 17, 2012
Introduction:

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

<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" />&nbsp;&nbsp;
<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 RSS subscribe by email Subscribe by Email

40 comments :

Mallik said...

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."

sab solutions said...

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

Anonymous said...

actually where you copied all these codings

Anonymous said...

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."

Anonymous said...

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

abhishekagrwl said...

Could not find installable ISAM.....:(........anyone help me out...

Anonymous said...

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

Sarvesh Pathak said...

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.

Suresh Dasari said...

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

Adi said...

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.

Adi said...

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.

nilesh monde said...

i copy paste above code ... how to obtain connectionstring ?

nilesh monde said...

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.

nilesh monde said...

i copy paste above code ... how to obtain connectionstring ?

Yogesh Gautam said...

its still not working sir

Arif Khan said...

Hello sir,

which excel connection string is used if we upload the website on remote server

SAMA RANGA REDDY said...

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.

Gayathri N said...

hi
am getting this error
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

Suresh Dasari said...

@gayathri n...
check this article http://www.aspdotnet-suresh.com/2012/12/iis7-microsoftaceoledb120-provider-is.html

Gayathri N said...

Thank you suresh.I tried that only.but did not get the answer.All your posts are very usefull.Thank you very much.

Aabasaheb Sangale said...

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

Naina said...

if wrong data (datatype) is entered, i dont know how it catchs it and returning   (space) to gridview at that element's place. This is a big problem for me. please help asap ! Thanx in advance

avinash said...

thank u for ur valuable solutions.
i have done and its working here i want to get some more solution that i want to make some changes to data in grid view and to store in sql server.

plz tell me the solution or else mail me abhid.mca@gmail.com

avinash said...

hello suresh,

please reply to my post.

shilpa monde said...

yes i got the result...

Anonymous said...

am getting an error which states:

Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

Suggest me an answer...

Thanks in Advance....

Rahul Kore said...

i hv 50 tables in MS access database with data
& same tables in the SQL database in visual studio..................)
If I update Ms access database then how can i show updated data in Sql server.
plz tell me this one hw can i do this ....how can i show updated data in SQL database & this data display on grid view on browser.
can u upload this type of artical................
all articals r good.....................):


thank you.!!!!!!!

Anonymous said...

"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." I have this problem. Hot to fix it. Thanks.

Sudhakar said...

I need to add a column with a file upload control to my grid view so that I can upload files against any particular row. Is it possible to do this, ideally I need to be able to do this without putting the gridview into it's edit state.

Vasantha D said...

i am getting error when i use inherits System.Web.Ui.Page
and
fileuploadExcel is not declared

Pradip Panjwani said...

Hello Sir
This Code Help Me A Lot
Thanks

Pradip Panjwani said...

One More Thing,Please Can You Suggest Me once we got data from excel file into data table than how we insert that data into sql server database without pre creating table into database?

harsh pathania said...

Hello sir i have read your blog it is so much informative and valueable can you suggest or tell me a way where data from two excelsheets can be transferred to one single database

Anonymous said...

Hi Guys This is "Chandan". Those who are getting the following bug "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."

can be checked out with this code

protected void btnImport_Click(object sender, EventArgs e)
{
string connString = "";
string strFileType = Path.GetExtension(fileuploadExcel.FileName).ToLower();
string path = fileuploadExcel.PostedFile.FileName;

string fileBasePath = Server.MapPath("~/Files/");
string fileName = Path.GetFileName(this.fileuploadExcel.FileName);
string fullFilePath = fileBasePath + fileName;
//Connection String to Excel Workbook
if (strFileType.Trim() == ".xls")
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fullFilePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
}
else if (strFileType.Trim() == ".xlsx")
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fullFilePath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
}
if (fileuploadExcel.HasFile)
{
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();
}
}

Assume that "fileuploadExcel" is the asp.net upload control and you must have your excel file inside the "Files" folder of your application.

Anonymous said...

thank you , you coding helped alot

Anurag Nayak said...

wowwwwwwwwwwwwwww it worked :) thank yo

Anonymous said...

it is fine for localhost..., But what if we are doing same on remote server. we will have to upload the file first in our project directory on server??? How to do it on remote server by selecting file from clients pc....

Rajkumar Patro said...

protected void btnImport_Click(object sender, EventArgs e)
{
string path = Server.MapPath("~/App_Data/") + fileuploadExcel.FileName;
fileuploadExcel.SaveAs(path);
string a = fileuploadExcel.PostedFile.FileName;

OleDbConnection con_excel = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 8.0");

OleDbDataAdapter adpt = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", con_excel);
DataSet ds = new DataSet();
adpt.Fill(ds, "tbl_temp");

grvExcelData.DataSource = ds.Tables["tbl_temp"];
grvExcelData.DataBind();
}

Vishal Prajapati said...

Hi....Sir i am getting 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.

pratibha choudhary said...

hi....suresh i am getting 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.

Give your Valuable Comments

Other Related Posts

© 2010-2012 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.