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
<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> |
{ 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(); } |
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
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 Email
|
|||
33 comments :
may i ask a question?
whats the "exceldata" stands for?
can you plz explain to me how to use it properly.
thnx so much!
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
its says it cant find the sheet1
the reader cant find the sheet name even when its renamed to my own names
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??
Please Can you post a code: for displaying excel sheets name in asp.net dropdownlist
Thanks in advanced
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...
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!!
@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
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
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!!
its done...thanks suresh
Himanshu
audacious.himanshu@gmail.com
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
@Mohamed Hussain...
Check this article http://www.aspdotnet-suresh.com/2011/02/how-to-inserteditupdate-and-delete-data.html
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
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.
How to solve this
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
Hi suresh i got error Microsoft.ACE.OLEDB.12.0 not register local machine.
My name samyraj please help me urgent please
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!
sir,
how to find sheet1$
please help me urgent please
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
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
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
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
sdfsdf
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
please help suresh about paypal means how it works when i submit payment through payment getway, there is some code which we write
Hi Suresh Ji ,
Can you please tell me how to read data from an Excel file to a GridView with different column names.
Why we need to use OLEDB not Sqlclient?
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
Hi suresh is it posible bind excel sheet with gridview in fully editable mode as well as can update in excel sheet too.
Note: Only a member of this blog may post a comment.