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
No comments:
Post a Comment