Post
Ask Question

Read Excel Sheet Data and Bind With ASP.NET GridView

Introduction

This article explains how to retrieve data from Excel using ADO.NET then store it into a dataset stored as DataSets data to be shown in a GridView.

I am showing you it step-by-step:

Step 1: Open Visual Studio and create a new ASP.NET project and create a webform.

Step 2: Now on that webform use one FileUploadControl. Using this we will upload an Excel file from our system. Create one button to upload the data to the server, one label to show you what file's data is showing in the grid view, and use one GridView control in which we will show our Excel sheet's data.

So my design aspx page is like; this you can just copy and paste the aspx page given below:
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ImportExelDataInGridView.aspx.cs" Inherits="ReadDataFromExcel.ImportExelDataInGridView" %>    
  2.     
  3. <!DOCTYPE html>    
  4.     
  5. <html xmlns="http://www.w3.org/1999/xhtml">    
  6. <head runat="server">    
  7.     <title></title>    
  8. </head>    
  9. <body>    
  10.     <form id="form1" runat="server">    
  11.     <div>    
  12.         
  13.         Import Excel File:    
  14.         <asp:FileUpload ID="FileUpload1" runat="server" />    
  15.         <br />    
  16.         <br />    
  17.         <asp:Button ID="btnUpload" runat="server" OnClick="btnUpload_Click" Text="Upload" />    
  18.         <br />    
  19.         <br />    
  20.         <asp:Label ID="Label1" runat="server"></asp:Label>    
  21.         <br />    
  22.         <asp:GridView ID="gvExcelFile" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">    
  23.             <AlternatingRowStyle BackColor="White" ForeColor="#284775" />    
  24.             <EditRowStyle BackColor="#999999" />    
  25.             <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />    
  26.             <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />    
  27.             <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />    
  28.             <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />    
  29.             <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />    
  30.             <SortedAscendingCellStyle BackColor="#E9E7E2" />    
  31.             <SortedAscendingHeaderStyle BackColor="#506C8C" />    
  32.             <SortedDescendingCellStyle BackColor="#FFFDF8" />    
  33.             <SortedDescendingHeaderStyle BackColor="#6F8DAE" />    
  34.         </asp:GridView>    
  35.         
  36.     </div>    
  37.     </form>    
  38. </body>    
  39. </html> 
Using the code above you will get output of the page such as the following:

design aspx page

Step 3: Now on the code behind of this aspx page, use the following two namespaces:
  1. System.Data;  
  2. System.Data.OleDb;  
Step 4: For the click event of the upload button you should write the following code:
  1. protected void btnUpload_Click(object sender, EventArgs e)  
  2. {  
  3.     //Coneection String by default empty  
  4.     string ConStr = "";  
  5.     //Extantion of the file upload control saving into ext because   
  6.     //there are two types of extation .xls and .xlsx of Excel   
  7.     string ext = Path.GetExtension(FileUpload1.FileName).ToLower();  
  8.     //getting the path of the file   
  9.     string path = Server.MapPath("~/MyFolder/"+FileUpload1.FileName);  
  10.     //saving the file inside the MyFolder of the server  
  11.     FileUpload1.SaveAs(path);  
  12.     Label1.Text = FileUpload1.FileName + "\'s Data showing into the GridView";  
  13.     //checking that extantion is .xls or .xlsx  
  14.     if (ext.Trim() == ".xls")  
  15.     {  
  16.         //connection string for that file which extantion is .xls  
  17.         ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";  
  18.     }  
  19.     else if (ext.Trim() == ".xlsx")  
  20.     {  
  21.         //connection string for that file which extantion is .xlsx  
  22.         ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";  
  23.     }  
  24.     //making query  
  25.     string query = "SELECT * FROM [Sheet1$]";  
  26.     //Providing connection  
  27.     OleDbConnection conn = new OleDbConnection(ConStr);  
  28.     //checking that connection state is closed or not if closed the   
  29.     //open the connection  
  30.     if (conn.State == ConnectionState.Closed)  
  31.     {  
  32.         conn.Open();  
  33.     }  
  34.     //create command object  
  35.     OleDbCommand cmd = new OleDbCommand(query, conn);  
  36.     // create a data adapter and get the data into dataadapter  
  37.     OleDbDataAdapter da = new OleDbDataAdapter(cmd);  
  38.     DataSet ds = new DataSet();  
  39.     //fill the Excel data to data set  
  40.     da.Fill(ds);  
  41.     //set data source of the grid view  
  42.     gvExcelFile.DataSource = ds.Tables[0];  
  43.     //binding the gridview  
  44.     gvExcelFile.DataBind();  
  45.     //close the connection  
  46.     conn.Close();  
  47. }  
Step 5: Now create one Excel file such as the following:

Excel file
 
Now save this Excel file with any name like MyNewExcelFile.xlsx.
 
Step 6: Now run the project.

run the project
 
After choosing the Excel file:

Choosing excel file
 
After clicking on the Upload button you will see the following output in the GridView:
 
Upload 


Similar Articles