How to Import Data from Excel to GridView in Asp.Net C# and Vb.Net

I have previously written an article on how to import data from an Excel file in Asp.Net and upload the data to an SQL Server database table using SqlBulkCopy Class. Now here in this post I am sharing an example on how to Import data from Excel to a GridView control in Asp.Net C# and Vb.Net.

Import Data From Excel and Bind to GridView

Note: Using this method you do not have to worry about the format in Excel. The procedure will extract and import Excel data, as it is, and bind it to a GridView control.

The data binding process is very simple. We’ll first extract the data from an Excel file using methods and properties provided by the OleDb class. The namespace System.Data.OleDb provides the methods and you will need to add it in your code behind section. It provides simple methods to set connection with the Excel file, extract data from the sheet and later using a 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.

The Markup

In the markup section, we will add a FileUpload control, a button and a GridView control. 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>
<head>
   <title>Import Data from Excel and Bind Data to GridView</title>
</head>
<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>

Code behind (C#)

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;
                }
            }
        }
    }
}

Also Read: How to Export Data from a Paging Enabled GridView to Excel in Asp.Net C# and Vb.Net

Code behind (Vb)

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 →

How to Import Data from Excel to GridView in Asp.Net C# and Vb.Net

Tools you can use