How to read Excel data an bind with GridView using C# and VB

Last updated: 26th April 2024

In my previous article I have shared an example showing how to import data from an Excel file and upload the data to an SQL Server database table in Asp.Net using SqlBulkCopy Class. Now let me show you a simple method on how to import or read data from Excel file and bind with a GridView contron using C# and VB.

Import Data From Excel and Bind to GridView

The Markup

First, lets add few controls. I have a GridView control, a FileUpload control and a button, to populate the grid. The Grid is bound to nothing, since we will bind it with data from Excel using code behind procedures. The button’s click event will call a procedure to extract and populate data to the grid.

<!DOCTYPE html>
<html>
<body>
    <form runat="server">
    <div>
        <div>
            <div>
                <!-- ADD A FILE UPLOAD CONTROL AND A BUTTON TO EXECUTE. -->
                Select a file: <asp:FileUpload ID="FileUpload" runat="server" />
                <p>
                    <input type="button" 
                        onserverclick="PopulateGrid" 
                            value="Populate Grid" 
                                runat="server" />
                </p>
            </div>
            <!-- ADD A GRIDVIEW CONTROL. -->
            <div>
                <asp:GridView ID="GridView1" CssClass="Grid" runat="server">
                </asp:GridView>

                <p><asp:Label id="lblConfirm" runat="server"></asp:Label></p>
            </div>
        </div>
    </div>
    </form>
</body>
</html>

The data binding process is very simple. We’ll first extract data from an Excel file. The OleDb class provides all the methods and properties to do this.

The namespace System.Data.OleDb provides the methods set a connection with the Excel file, extract data from a particular "sheet"and later using datareader you can read or share the data with other objects.

Once the datareader gets the data, we’ll add a DataTable object and load the extracted data to the DataTable.

Finally, all you have to do is, bind the DataTable object to the GridView control.

➡️ GridView examples

C# Code

using System;

using System.Data;              // FOR "DataTable".
using System.Data.OleDb;

public partial class SiteMaster : System.Web.UI.MasterPage
{
    protected void PopulateGrid(object sender, EventArgs e)
    {
        // CHECK IF A FILE HAS BEEN SELECTED.
        if ((FileUpload.HasFile))
        {
            if (!Convert.IsDBNull(FileUpload.PostedFile) &
                    FileUpload.PostedFile.ContentLength > 0)
            {
                // SAVE THE SELECTED FILE IN THE ROOT DIRECTORY.
                FileUpload.SaveAs(Server.MapPath(".") + "\\" + FileUpload.FileName);

                // SET A CONNECTION WITH THE EXCEL FILE.
                OleDbConnection myExcelConn = new OleDbConnection
                    ("Provider=Microsoft.ACE.OLEDB.12.0; " +
                        "Data Source=" + Server.MapPath(".") + "\\" + FileUpload.FileName +
                        ";Extended Properties=Excel 12.0;");
                try
                {
                    myExcelConn.Open();

                    // GET DATA FROM EXCEL SHEET.
                    OleDbCommand objOleDB =
                        new OleDbCommand("SELECT *FROM [Sheet1$]", myExcelConn);

                    // READ THE DATA EXTRACTED FROM THE EXCEL FILE.
                    OleDbDataReader objBulkReader = null;
                    objBulkReader = objOleDB.ExecuteReader();

                    DataTable dt = new DataTable();
                    dt.Load(objBulkReader);

                    // FINALLY, BIND THE EXTRACTED DATA TO THE GRIDVIEW.
                    GridView1.DataSource = dt;
                    GridView1.DataBind();

                    lblConfirm.Text = "DATA IMPORTED TO THE GRID, SUCCESSFULLY.";
                    lblConfirm.Attributes.Add("style", "color:green");
                }
                catch (Exception ex)
                {
                    // SHOW ERROR MESSAGE, IF ANY.
                    lblConfirm.Text = ex.Message;
                    lblConfirm.Attributes.Add("style", "color:red");
                }
                finally
                {
                    // CLEAR.
                    myExcelConn.Close(); myExcelConn = null;
                }
            }
        }
    }
}

VB Code

Option Explicit On

Imports System.Data             ' FOR "DataTable".
Imports System.Data.OleDb

Partial Class Site
    Inherits System.Web.UI.MasterPage

    Protected Sub PopulateGrid(sender As Object, e As EventArgs)
        If (FileUpload.HasFile) Then        ' CHECK IF ANY FILE HAS BEEN SELECTED.

            If Not IsDBNull(FileUpload.PostedFile) And _
                FileUpload.PostedFile.ContentLength > 0 Then

                ' SAVE THE SELECTED FILE IN THE ROOT DIRECTORY.
                FileUpload.SaveAs(Server.MapPath(".") & "\" & FileUpload.FileName)

                ' SET A CONNECTION WITH THE EXCEL FILE.
                Dim myExcelConn As OleDbConnection = _
                    New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                        Server.MapPath(".") & "\" & FileUpload.FileName() & _
                        ";Extended Properties=Excel 12.0;")
                Try
                    myExcelConn.Open()

                    ' GET DATA FROM EXCEL SHEET.
                    Dim objOleDB As New OleDbCommand("SELECT *FROM [Sheet1$]", myExcelConn)

                    ' READ THE DATA EXTRACTED FROM THE EXCEL FILE.
                    Dim objBulkReader As OleDbDataReader
                    objBulkReader = objOleDB.ExecuteReader

                    Dim dt As DataTable = New DataTable
                    dt.Load(objBulkReader)

                    ' FINALLY, BIND THE EXTRACTED DATA TO THE GRIDVIEW.
                    GridView1.DataSource = dt
                    GridView1.DataBind()

                    lblConfirm.Text = "DATA IMPORTED TO THE GRID, SUCCESSFULLY."
                    lblConfirm.Attributes.Add("style", "color:green")

                Catch ex As Exception
                    ' SHOW ERROR MESSAGE, IF ANY.
                    lblConfirm.Text = ex.Message
                    lblConfirm.Attributes.Add("style", "color:red")
                Finally
                    ' CLEAR.
                    myExcelConn.Close() : myExcelConn = Nothing
                End Try
            End If
        End If
    End Sub
End Class

Well, that’s it. Once you have populated the extracted data to the GridView, you can now perform CRUD operations using the data in the grid.

← PreviousNext →