Post
Ask Question

Bind GridView From Excel File Records Using ASP.Net C#

Background
 
I have often read the common question in forum posts of how to upload Excel file records and bind them to a GridView but no one has provided the proper solution and many solutions contain a lot of code that is not required so by considering the preceding requirements I have decided to write this article to provide the solution to bind a GridView from Excel file records with a minimum amount of code. So let us start creating an application so beginners can also understand.
 
First create the Excel file named Employee as:
 
 
Now we have records to bind to the GridView, let us create the sample web application as follows:
  1. "Start" - "All Programs" - "Microsoft Visual Studio 2010".
  2. "File" - "New WebSite" - "C#" - "Empty WebSite" (to avoid adding a master page).
  3. Provide the web site a name such as "BindGridviewFromExcelFileRecords" or another as you wish and specify the location.
  4. Then right-click on Solution Explorer and select  "Add New Item" and Add Web Form.
  5. Drag and drop one Button, a GridView and a FileUploader control onto the <form> section of the Default.aspx page.

Now the default.aspx page source code will look such as follows.

  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>    
  2.     
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">    
  4. <html xmlns="http://www.w3.org/1999/xhtml">    
  5. <head id="Head1" runat="server">    
  6.     <title>Article by Vithal Wadje</title>    
  7. </head>    
  8. <body bgcolor="blue">    
  9.     <form id="form1" runat="server">    
  10.     <div style="color: White;">    
  11.         <h4>    
  12.             Article for C#Corner    
  13.         </h4>    
  14.         <table>    
  15.             <tr>    
  16.                 <td>    
  17.                     Select File    
  18.                 </td>    
  19.                 <td>    
  20.                     <asp:FileUpload ID="FileUpload1" runat="server" />    
  21.                 </td>    
  22.                 <td>    
  23.                 </td>    
  24.                 <td>    
  25.                     <asp:Button ID="Button1" runat="server" Text="Upload" OnClick="Button1_Click" />    
  26.                 </td>    
  27.             </tr>    
  28.         </table>    
  29.     </div>  
  30. <asp:GridView ID="GridView1" runat="server">
    </asp:GridView> 
  31.     </form>    
  32. </body>    
  33. </html>   
Now open the Default.aspx.cs page and write the following code to create an oledbconnection for the Excel file as in the following:
  1. private void ExcelConn(string FilePath)    
  2. {    
  3.     
  4.     constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", FilePath);    
  5.     Econ = new OleDbConnection(constr);    
  6.          
  7. }   
Create a function to read the Excel File records and bind the GridView as:
  1. private void ReadExcelRecords(string FilePath)  
  2. {  
  3.     ExcelConn(FilePath);  
  4.   
  5.     Query = string.Format("Select [Name],[City],[Address],[Designation] FROM [{0}]", "Sheet1$");  
  6.     OleDbCommand Ecom = new OleDbCommand(Query, Econ);  
  7.     Econ.Open();  
  8.   
  9.     DataSet ds = new DataSet();  
  10.     OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);  
  11.     Econ.Close();  
  12.     oda.Fill(ds);  
  13.     GridView1.DataSource = ds;
        GridView1.DataBind();
  14.   
  15. } 
Now call the preceding function upon an Upload button click as:
  1. protected void Button1_Click(object sender, EventArgs e)  
  2. {  
  3.     string CurrentFilePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);  
  4.     ReadExcelRecords(CurrentFilePath);  
  5.   } 
The entire code of the default.aspx.cs page will look as follows:
  1. using System;  
  2. using System.Data;  
  3. using System.IO;  
  4. using System.Data.OleDb;  
  5.   
  6. public partial class _Default : System.Web.UI.Page  
  7. {  
  8.     OleDbConnection Econ;  
  9.   
  10.     string constr, Query;  
  11.     protected void Page_Load(object sender, EventArgs e)  
  12.     {
  13.     }  
  14.   
  15.     private void ExcelConn(string FilePath)  
  16.     {  
  17.   
  18.         constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", FilePath);  
  19.         Econ = new OleDbConnection(constr);  
  20.   
  21.     }   
  23.     private void ReadExcelRecords(string FilePath)  
  24.     {  
  25.         ExcelConn(FilePath);  
  26.   
  27.         Query = string.Format("Select [Name],[City],[Address],[Designation] FROM [{0}]", "Sheet1$");  
  28.         OleDbCommand Ecom = new OleDbCommand(Query, Econ);  
  29.         Econ.Open();  
  30.   
  31.         DataSet ds = new DataSet();  
  32.         OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);  
  33.         Econ.Close();  
  34.         oda.Fill(ds);  
  35.         GridView1.DataSource = ds;
            GridView1.DataBind();
  36.   
  37.     }  
  38.     protected void Button1_Click(object sender, EventArgs e)  
  39.     {  
  40.         string CurrentFilePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);  
  41.         ReadExcelRecords(CurrentFilePath);  
  42.     }  
  43. } 
Now run the application and the UI will look as follows:
 
 
Now select the file by using the browse button as:
 
 
Now click on the Upload button. The records in GridView will then look as in the following:
 
 
Now you have seen how the records are displayed in the GridView using an Excel file with a minimal amount of code and effort.
 
Notes
  • For detailed code please download the sample Zip file.

  • Do a proper validation such as date input values when implementing.
Summary

From all the preceding examples you have learned how to bind a GridView with Excel file records. I hope this article is useful for all readers, if you have a suggestion then please contact me.


Similar Articles