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" %>
  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. {
  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);
  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);
  5. Query = string.Format("Select [Name],[City],[Address],[Designation] FROM [{0}]", "Sheet1$");
  6. OleDbCommand Ecom = new OleDbCommand(Query, Econ);
  7. Econ.Open();
  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();
  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;
  6. public partial class _Default : System.Web.UI.Page
  7. {
  8. OleDbConnection Econ;
  10. string constr, Query;
  11. protected void Page_Load(object sender, EventArgs e)
  12. {
  13. }
  15. private void ExcelConn(string FilePath)
  16. {
  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);
  21. }
  23. private void ReadExcelRecords(string FilePath)
  24. {
  25. ExcelConn(FilePath);
  27. Query = string.Format("Select [Name],[City],[Address],[Designation] FROM [{0}]", "Sheet1$");
  28. OleDbCommand Ecom = new OleDbCommand(Query, Econ);
  29. Econ.Open();
  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();
  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.