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

63 comments :

SQL SRINIVAS 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

Abhishek Agrawal 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 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.

Unknown said...

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

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

Unknown said...

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

Unknown 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

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

Unknown 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

Unknown said...

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

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

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

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

Unknown said...

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

Unknown said...

Hello Sir
This Code Help Me A Lot
Thanks

Unknown 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?

Unknown 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

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

Unknown 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();
}

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

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

VIJAYAKUMAR said...

Can we do this same using partial page-load ?

Badhars said...

HI SURESH ...I AM WORKING WITH LARGE excel file which has 300 columns . I want to upload that file to database...but it is uploading only 256 columns only...please help me to get upload up to or above 300 columns....

Anonymous said...

Hi suresh
Above code is working in local system.
But showing error on server.
Please Can you tell me how to resolve this.

Anonymous said...

string connString = ""; //Microsoft Excel 97-2003 Worksheet (.xls)
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="E:\\address.xls ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connString);

DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); // used to get sheet name
string query = @"SELECT * FROM ["+dt.Rows[0]["TABLE_NAME"]+"]";

joeller said...

I would like to add my two bits to the plethora of issues with the Excel Provider. Chandan's code did not work on test server. It is essentially no different than my app. The only difference is he uses some kind of file upload device to put file into a Files folder whereas my file resides directly under the root of the web app, permanently. It works fine on local machine but when compiled and deployed to test server the same old error.

Anonymous said...

I am getting error conn.open(); oledbException was unhandled by user code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Data;

namespace WebApplication4
{
public partial class WebForm1 : System.Web.UI.Page
{

protected void Button1_Click(object sender, EventArgs e)

{
string connString = "";

string strFileType = Path.GetExtension(FileUpload1.FileName).ToLower();

string path = FileUpload1.PostedFile.FileName;

//Connection String to Excel Workbook

if (strFileType.Trim() == ".xlsx")
{

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 [film_id],[title],[description] 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);

GridView1.DataSource = ds.Tables[0];

GridView1.DataBind();

da.Dispose();

conn.Close();

conn.Dispose();

}

}
}

Anonymous said...

suresh bhai uper questions ka reply karoge ki nahai

Anonymous said...

Suresh Bhai apka koi code work nahai karta

Anonymous said...

no

Anonymous said...

Thank You...Got the required output

Manju said...

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

string path = fileuploadExcel.PostedFile.FileName;
path = path + "" + strFileType;
string fileLocation = Server.MapPath("~/Excel/" + path);
fileuploadExcel.SaveAs(fileLocation);
//Connection String to Excel Workbook
if (strFileType.Trim() == ".xls")
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (strFileType.Trim() == ".xlsx")
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";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();
}

try like this..It will get the hole file path.

Unknown said...

i can't upload because visual studio said "The ConnectionString property has not been initialized." help me please? thanks

Anonymous said...

weh suresh inade ya thambi balaca nakalaponde yal guru shiwaliyam salmi toya ang
how to settle this problem dont give excuse u busy and dont had time to spend to solve my problem
just read this and solve it with easy way or hardway u little bald einsten duckling joker
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.

now solve it or u are the one problem must be solve

Suresh Dasari said...

could not find the object 'Sheet1$' this problem occurred might be because of your excel sheet name different than Sheet1 check it

Unknown said...
This comment has been removed by the author.
Anonymous said...

no suresh thambi i already check it again and again still same error
Sheet1 was same with excel sheet name...now what ur suggestion or any idea to you fix it suresh
doooo it....toya ang la wei
puneto...coyoou

Anonymous said...

hey suresh where u be gone from this blog...u must help the other suresh to solve the problem...dont sleep u porokun sesame street buffon sam parker cannabis...help them

Unknown said...

Hi this is Raju
i got 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.

Jatin said...

Hi there Suresh, This is the first time I have to say this code is not working as expected.. Anyways for your good work.. All the best..
As usual your code is always simple and effective ...
Thanks much

Unknown said...

hi,
i am trying import password protected file and Convert into DataTable

Unknown said...

Hello Suresh Sir,
I am trying to Export data from excel to sql server database using sql bulk copy but getting error "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine." but this error fix on set "Enable 32-Bit Application='true'" in Application pool. But I don't want to set it "true" so what should I do for it. Please give me response asap.

Exception Details: System.InvalidOperationException: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

Thanks
(Mohd. Danish)

Unknown said...

send me any solution on email id: danishdotnet@gmail.com

Unknown said...

Are you available to help us or any other way to contact with you for any query.

Unknown said...

Hello Suresh Sir,
I am trying to import data from excel to grid datatable using this code getting error "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine." am using Visual sudio 2012 64 bit and office 2016 64bit, when upload button click i am getting 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine." error please help me as soon. i tried to target cpu X86 but same thing happen.

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 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.