Import excel data to gridview

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!!!

3 comments:

  • santhosh
     

    It showed me a error since the total path could not be resolved. And also only one excel file could be saved.

    I modified with the following code:

    protected void btnImport_Click(object sender, EventArgs e)
    {
    try
    {
    string path = uploadExcel.FileName;
    string newName= Guid.NewGuid().ToString();
    string location = AppDomain.CurrentDomain.BaseDirectory + "/Upload/" + newName + path;
    uploadExcel.SaveAs(location);
    OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + location+ ";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 oadapt = new OleDbDataAdapter(ocmd);
    oadapt.Fill(ds);
    GridView1.DataSource = ds;
    GridView1.DataBind();
    oconn.Close();
    }
    catch (Exception exp)
    {
    throw exp;
    }

  • Adeel Fakhar
     

    Hi santhosh great to see you here. Well santhosh this example is working fine at my end. One thing that i forgot to mention in this tutorial and might be proved problematic for you is the .aspx page is located in the upload folder.

    Yeah definitely the same folder where all uploaded excel files are located. I think you have your .aspx page outside the upload folder that's why you have faced this problem :)

    Anyways, thanks for sharing your feedback.

  • Adeel Fakhar
     

    One more thing, Upload folder is located at root directory of website.