ASP.NET

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.     }  
  16.   
  17.     private void ExcelConn(string FilePath)  
  18.     {  
  19.   
  20.         constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", FilePath);  
  21.         Econ = new OleDbConnection(constr);  
  22.   
  23.     }  
  24.        
  25.     private void ReadExcelRecords(string FilePath)  
  26.     {  
  27.         ExcelConn(FilePath);  
  28.   
  29.         Query = string.Format("Select [Name],[City],[Address],[Designation] FROM [{0}]", "Sheet1$");  
  30.         OleDbCommand Ecom = new OleDbCommand(Query, Econ);  
  31.         Econ.Open();  
  32.   
  33.         DataSet ds = new DataSet();  
  34.         OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);  
  35.         Econ.Close();  
  36.         oda.Fill(ds);  
  37.         GridView1.DataSource = ds;
            GridView1.DataBind();
  38.   
  39.     }  
  40.     protected void Button1_Click(object sender, EventArgs e)  
  41.     {  
  42.         string CurrentFilePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);  
  43.         ReadExcelRecords(CurrentFilePath);  
  44.     }  
  45. } 
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.
 

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs