Display Uploaded excel files in Gridview

In this blog we will know how to upload a excel file and display the contents of the file into a Gridview.

 

 

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Display_excel_files_in_Gridview._Default" %>

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

 

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

    <title>Untitled Page</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <asp:Table ID="TableButtons" runat="server">

            <asp:TableRow ID="TableRow1" runat="server">

                <asp:TableCell ID="TableCell1" runat="server">

                    <asp:Button ID="ButtonUpload" runat="server"

                    Text="Upload Excel Files" OnClick="ButtonUpload_Click" />

                </asp:TableCell>

                <asp:TableCell ID="TableCell2" runat="server">

                    <asp:Button ID="ButtonView" runat="server"

                    Text="View Excel Data"  OnClick="ButtonView_Click" />

                </asp:TableCell>

               

            </asp:TableRow>

        </asp:Table>

        <asp:Panel ID="PanelUpload" runat="server" Visible="False">

            <asp:FileUpload ID="FileUploadExcel" runat="server" />

            <br />

            Please select an Excel file to import:<br />

            <asp:Button ID="ButtonUploadFile" runat="server"

                Text="Upload File" OnClick="ButtonUploadFile_Click" /><br />

            <asp:Label ID="LabelUpload" runat="server" Text=""></asp:Label>

        </asp:Panel>

        <asp:Panel ID="PanelView" runat="server" Visible="False">

            <asp:Label ID="LabelGrid" runat="server" Text=""></asp:Label>

            <asp:GridView ID="GridViewExcel" runat="server" BackColor="#DEBA84" BorderColor="#DEBA84"

                BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2">

                <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />

                <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />

                <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />

                <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />

                <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />

            </asp:GridView>

        </asp:Panel>

        <asp:Panel ID="PanelImport" runat="server" Visible="False">

            <asp:Label ID="LabelImport" runat="server" Text=""></asp:Label>

        </asp:Panel>

        <asp:Label ID="lblError" runat="server" Text=""></asp:Label>

    </div>

    </form>

</body>

</html>

 

 

 

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.OleDb;

using System.Data.SqlClient;

 

namespace Display_excel_files_in_Gridview

{

    public partial class _Default : System.Web.UI.Page

    {

        protected void ButtonUploadFile_Click(object sender, EventArgs e)

        {

            if (FileUploadExcel.HasFile)

            {

                try

                {

                    FileUploadExcel.SaveAs(Server.MapPath("~/ExcelImport.xls"));

                    LabelUpload.Text = "Upload File Name: " +

                        FileUploadExcel.PostedFile.FileName + "<br>" +

                        "Type: " + FileUploadExcel.PostedFile.ContentType +

                        " File Size: " + FileUploadExcel.PostedFile.ContentLength +

                        " kb<br>";

                }

                catch (System.NullReferenceException ex)

                {

                    LabelUpload.Text = "Error: " + ex.Message;

                }

            }

            else

            {

                LabelUpload.Text = "Please select a file to upload.";

            }

        }

        protected OleDbCommand ExcelConnection()

        {

 

            string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("ExcelImport.xls") + ";" + "Extended Properties=Excel 8.0;";

            OleDbConnection Con = new OleDbConnection(ConnStr);

            Con.Open();

            OleDbCommand com = new OleDbCommand("SELECT * FROM [sheet1$]", Con);

            return com;

 

        }

      

        protected void ButtonView_Click(object sender, System.EventArgs e)

        {

            PanelUpload.Visible = false;

            PanelView.Visible = true;

            PanelImport.Visible = false;

 

            OleDbDataAdapter oledbda = new OleDbDataAdapter();

            oledbda.SelectCommand = ExcelConnection();

            DataSet ds = new DataSet();

            oledbda.Fill(ds);

            GridViewExcel.DataSource = ds.Tables[0].DefaultView;

            GridViewExcel.DataBind();

        }

        protected void ButtonUpload_Click(object sender, System.EventArgs e)

        {

            PanelUpload.Visible = true;

            PanelView.Visible = false;

            PanelImport.Visible = false;

        }

    }

}

 

 

 

Ebook Download
View all
Learn
View all