Upload and Display Excel File Content in GridView Using Asp.Net

Upload and Display Excel File Content in GridView Using Asp.Net

In this post we will learn how to Upload and Display Excel File Content in GridView Using Asp.Net. Earlier we had discussed How to Import XML data into Gridview. Here we simply upload excel file(.xls, .xlsx) and then upload it to server, finally it will be displayed on gridview control. Here we have also added one feature i.e. we want to display header of excel file in gridview or not.

Create asp.net project then drag and drop the file upload and gridview control into aspx page and format the gridview control as like below. On Upload button click event we have written code to import data from excel to gridview.

Design View:

<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
    CodeFile="Default.aspx.cs" Inherits="_Default" %>

<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <h2>Welcome to ASP.NET!
    </h2>
    <p>
        To learn more about ASP.NET visit <a href="http://www.asp.net" title="ASP.NET Website">www.asp.net</a>.
    </p>
    <p>
        You can also find <a href="http://go.microsoft.com/fwlink/?LinkID=152368&amp;clcid=0x409"
            title="MSDN ASP.NET Docs">documentation on ASP.NET at MSDN</a>.
    </p>
    <br />
    <br />

    <asp:FileUpload ID="FileUpload1" runat="server" />
    <asp:Button ID="Upload" runat="server" Text="Upload"
        OnClick="Upload_Click" />
    <br />
    <asp:Label ID="Label1" runat="server" Text="Has Header ?" />
    <asp:RadioButtonList ID="HEADER" runat="server">
        <asp:ListItem Text="Yes" Value="Yes" Selected="True">
        </asp:ListItem>
        <asp:ListItem Text="No" Value="No"></asp:ListItem>
    </asp:RadioButtonList>

    <asp:GridView ID="GridView1" runat="server" Width="100%" HeaderStyle-BackColor="Gray"
        OnPageIndexChanging="PageIndexChanging" AllowPaging="true" HeaderStyle-ForeColor="White" PageSize="30">
    </asp:GridView>

    <br />
    <br />
    <asp:Button ID="Button1" runat="server" Text="Excel Export"
        OnClick="Button1_Click" />
</asp:Content>

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.OleDb;
using System.Data;
using System.IO;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Upload_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
            string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            string FolderPath = ConfigurationManager.AppSettings["FolderPath"];

            string FilePath = Server.MapPath(FolderPath + FileName);
            FileUpload1.SaveAs(FilePath);
            Import_To_Grid(FilePath, Extension, HEADER.SelectedItem.Text);
        }
    }

    private void Import_To_Grid(string FilePath, string Extension, string isHDR)
    {
        string conStr = "";
        switch (Extension)
        {
            case ".xls":
                conStr = ConfigurationManager.ConnectionStrings["excel2003"]
                         .ConnectionString;
                break;
            case ".xlsx":
                conStr = ConfigurationManager.ConnectionStrings["excel2007"]
                          .ConnectionString;
                break;
        }
        conStr = String.Format(conStr, FilePath, isHDR);
        OleDbConnection connExcel = new OleDbConnection(conStr);
        OleDbCommand cmdExcel = new OleDbCommand();
        OleDbDataAdapter oda = new OleDbDataAdapter();
        DataTable dt = new DataTable();
        cmdExcel.Connection = connExcel;

        //Get the name of First Sheet
        connExcel.Open();
        DataTable dtExcelSchema;
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
        connExcel.Close();

        //Read Data from First Sheet
        connExcel.Open();
        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
        oda.SelectCommand = cmdExcel;
        oda.Fill(dt);
        connExcel.Close();

        //Bind Data to GridView
        GridView1.Caption = Path.GetFileName(FilePath);
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }

    protected void PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
        string FileName = GridView1.Caption;
        string Extension = Path.GetExtension(FileName);
        string FilePath = Server.MapPath(FolderPath + FileName);
        Import_To_Grid(FilePath, Extension, HEADER.SelectedItem.Text);
        GridView1.PageIndex = e.NewPageIndex;
        GridView1.DataBind();
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        string attachment = "attachment; filename= ";
        Response.ClearContent();
        Response.AddHeader("content-disposition", attachment);
        Response.ContentType = "application/excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        GridView1.RenderControl(htw);
        Response.Write(sw.ToString());
        Response.End();
    }
}

Hope it will helps you guys!!!