o422-4349207 info@abiletechnologies.com

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

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.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
About the author