In this tutorial you will learn how to import excel data to gridview control. I have already written multiple posts for exporting gridview data to excel with different techniques, now its time to import records. Let's have a look over how to do so

Import excel data to gridview

.aspx page

<table width="100%" cellpadding="0" cellspacing="0">
                    <tr>
                        <td align="left" class="text" style="width: 114px;">
                           Import Data from Excel File
                        </td>
                        <td align="left">
                            <asp:FileUpload ID="uploadExcel" runat="server" />
                            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                            <asp:Button ID="btnImport" runat="server" Text="Import Data" OnClick="btnImport_Click" />
                        </td>
                    </tr>
                    <tr>
                        <td>
                            <asp:GridView ID="GridView1" runat="server">
                                <Columns>
                                    <asp:TemplateField HeaderText="First Name">
                                        <ItemTemplate>
                                            <asp:Label ID="lblFirstName" runat="server" Text='<%# Bind("first_name") %>'></asp:Label>
                                        </ItemTemplate>
                                        <ItemStyle HorizontalAlign="Left" />
                                    </asp:TemplateField>
                                    <asp:TemplateField  HeaderText="Last Name
                                        <ItemTemplate>
                                            <asp:Label ID="lblLastName" runat="server" Text='<%# Bind("last_name") %>'></asp:Label>
                                        </ItemTemplate>
                                        <ItemStyle HorizontalAlign="Left" />
                                    </asp:TemplateField>
                                    <asp:TemplateField  HeaderText="Gender">
                                        <ItemTemplate>
                                            <asp:Label ID="lblGender" runat="server" Text='<%# Bind("gender") %>'></asp:Label>
                                        </ItemTemplate>
                                        <ItemStyle HorizontalAlign="Center" />
                                    </asp:TemplateField>
                                </Columns>
                            </asp:GridView>
                        </td>
                    </tr>
                </table>

Simply we have a fileupload control, button control and gridview control in our page. We will use file upload control to upload the excel file and then using its physical path we will get records from it and display them to gridview control.


.aspx.cs

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Configuration;

protected void btnImport_Click(object sender, EventArgs e)
    {
        try
        {
            string path = uploadExcel.FileName;
            uploadExcel.SaveAs(Server.MapPath("~/Upload/" + path));
            OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Server.MapPath(path)+";Extended Properties=Excel 8.0");                
            oconn.Open();    
//Getting records from Sheet1 of excel file. As you know one excel file may have many sheets                    
            OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
            DataSet ds = new DataSet();
            OleDbDataAdapter odapt = new OleDbDataAdapter(ocmd);
            odapt.Fill(ds);
            GridView1.DataSource = ds;
            GridView1.DataBind();
            oconn.Close();            
        }
        catch(Exception exp)
        {
            throw exp;
        }            
    }

Note:- Don't forget to import namespaces that i have used above.


So this is the way to import excel data to gridview


Happy Coding!!!