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

Read the data from Excel Sheet and bind it to Gridview in asp.net

Jun 21, 2010
Here i will explain how to read the data from Excel sheet and bind that data to gridview in asp.net

1) First take one upload control, one button and one gridview like this


  2) Design Aspx page like this you just copy and paste the below aspx page

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
 <title>Reading Excel data</title>
</head>
<body>
 <form id="form1" runat="server">
 <div>
 <p>
 <asp:Label ID="Label1" runat="server">SpreadSheetContents:</asp:Label></p>
 <asp:FileUpload ID="fileupload" runat="server" /><br />
 <asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click" />
 <asp:DataGrid ID="DataGrid1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">
 <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
 <SelectedItemStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
 <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
 <AlternatingItemStyle BackColor="White" />
 <ItemStyle BackColor="#FFFBD6" ForeColor="#333333" />
 <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
 </asp:DataGrid>
</div>
 </form>
</body>
</html>
 
3) In button click you should write the following code



protected void btnSubmit_Click(object sender, EventArgs e)
    {
string path = fileupload.PostedFile.FileName;
 string strmail = string.Empty;
 string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";
 OleDbConnection objConn = new OleDbConnection(connectionString);
 objConn.Open();
 String strConString = "SELECT UserName,EmailId FROM [Sheet1$]";
 //where date = CDate('" + DateTime.Today.ToShortDateString() + "')";
 OleDbCommand objCmdSelect = new OleDbCommand(strConString, objConn);
 // Create new OleDbDataAdapter that is used to build a DataSet
 // based on the preceding SQL SELECT statement.
 OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
 // Pass the Select command to the adapter.
 objAdapter1.SelectCommand = objCmdSelect;
 // Create new DataSet to hold information from the worksheet.
 DataSet objDataset1 = new DataSet();
 // Fill the DataSet with the information from the worksheet.
 objAdapter1.Fill(objDataset1, "ExcelData");
 DataGrid1.DataSource = objDataset1;
 DataGrid1.DataBind();
   // Clean up objects.
   objConn.Close();
}


4) For accessing oledb conncetion you should enter name spaces
using System.Data.SqlClient;
using System.Data.OleDb;

After Completion of  all the steps you should press F5
and upload the excel sheet by using upload control and press submit button after completion of this you will find output like this 

 
  Here one important point is you need format your excel sheet like this 

 
String strConString = "SELECT UserName,EmailId FROM [Sheet1$]";
Here in string i have written query to get the data from UserName,EmailId from Sheet1 thats why i have format excel sheet with First column UserName  Second Column with EmailId and i have given difault name Sheet1 for that Sheet.if you want columns with anthor names you should change the query also. In query you should write the Columns whatever you have enter in Excel Sheet

i think it help you
 

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

33 comments :

Anonymous said...

may i ask a question?

whats the "exceldata" stands for?

can you plz explain to me how to use it properly.

thnx so much!

Suresh Dasari said...

Hi Here ExcelData means we will store some data regarding users or some thing else if you observe above excel sheet here we are storing Users information in Excel sheet. The information whatever we store in excel that is exceldata

Anonymous said...

its says it cant find the sheet1

Anonymous said...

the reader cant find the sheet name even when its renamed to my own names

darshan said...

The Microsoft 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.
what to do for that??

Anonymous said...

Please Can you post a code: for displaying excel sheets name in asp.net dropdownlist

Thanks in advanced

Anonymous said...

Hai sir,

I have fully finished my coding...in above your example code. but one error to be continue, pls clear that error sir..

Error:
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.

how is solve this error sir...

Himanshu Pandey said...

hi suresh
please tell me one thing
Your above post is for binding the data to grid view.Is it possible to import all the data of to database and then bind to grid view,so that when user logins,the data could be displayed(using bind method).The above post stores the data temporary,right?I want to store the data from excel column to Database so that I can display it to grid view
Please Help!!

Suresh Dasari said...

@Himanshu Pandey..
check this post to export excel data to database using asp.net
http://www.aspdotnet-suresh.com/2010/09/import-data-from-excel-to-sql-database.html

Himanshu Pandey said...

thanks suresh,
Please tell me one more thing,the same code will also work in mysql database?The sqlbulk copy class is replaced by???
Thanks and regards

Himanshu Pandey said...

waiting for your precious reply suresh.please tell me how can we do the same thing in mysql database.my project has mysql database unfortunately.
Please do reply,i am in trouble!!

Himanshu Pandey said...

its done...thanks suresh
Himanshu
audacious.himanshu@gmail.com

Mohamed Hussain said...

i imported the excel file into my gridview.now how to edit that data in the gridview. waiting for your valuable answer.

Regards,
A.Mohamed HussAiN

Suresh Dasari said...

@Mohamed Hussain...
Check this article http://www.aspdotnet-suresh.com/2011/02/how-to-inserteditupdate-and-delete-data.html

hemendra said...

Hey ...
One thing, while making connection you are taking it from User Local Path, if you publish this functionality will it work, I mean how can you read data from user location.

Hemendra

Yara said...

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.

Yara said...

How to solve this

Anonymous said...

SIR THERE IS A ERROR SHOWING WHEN I CLICK BUTTON..... CAN U HELP ME
ERROR SHOWN TO ME IS====The Microsoft 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.======

PLEASE HELP ME OUT

Anonymous said...

Hi suresh i got error Microsoft.ACE.OLEDB.12.0 not register local machine.

My name samyraj please help me urgent please

spandana said...
This comment has been removed by the author.
SirigineediRavi said...

Hi suresh sir!
How r u?

Can you please tell me how to import the sheets with user's sheet name(not 'Sheet1$') dynamically.

Other wise just tell me how to Rename that excel
before querying it????

Thank you!

Anonymous said...

sir,
how to find sheet1$
please help me urgent please

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

Unknown said...

Hi sir,
i retrieve data from database to grid view in normal way then i want to click that grid view then that particular row of cell value will be displayed in one label or textbox.

pls help me to solve this sir

Unknown said...

Hi,

My excel sheet has, columns Date,Status and Message.
I'm trying to dispaly the excel sheet content for range of dates.(SELECT * FROM [Sheet1$] where date between'12-01-2013' and '16-01-2013').
But I'm error as "Data type mismatch in criteria expression and Oledbexceptionwas handled by usercode". Pls help me out

Ramakrishna said...

thanks sir, for providing this valuable information.....
this was very useful to us.
but when i am executing an error occur and saying
----------error--------
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.
------------------
plz help to solve this.....
thanks in advance

Anonymous said...

sdfsdf

Anonymous said...

how to copy and paste excel data into gridview in asp.net c#(not uploading a excel doc) for all browsers. I tried using clipboard but its working in IE only

Unknown said...

please help suresh about paypal means how it works when i submit payment through payment getway, there is some code which we write

Unknown said...

Hi Suresh Ji ,
Can you please tell me how to read data from an Excel file to a GridView with different column names.

Anonymous said...

Why we need to use OLEDB not Sqlclient?

Shriguru said...

Hi
Gridview header always shows as F1,F2,F3...Whenever I tried to bind any excel data into gridview. Excel sheet does not contain such columns. Can you please tell me the cause of this issue

Anonymous said...

Hi suresh is it posible bind excel sheet with gridview in fully editable mode as well as can update in excel sheet too.

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.