Friday 14 February 2014

Import And Upload Excel File data into Gridview in Asp.net Usiing C#

Hello Friends!!
In this post I will Explain Import And Upload Excel or we can can Display Excel file data into GridView
In Asp .net Using C#.
I am importing/uploading  an excel file data into a gridview. I have one fileupload control where I need to select an excel file and I have a gridview to show the excel file data and a button where need to click after choosing one excel file so that I can show my excel file data into gridview in asp.net c#.
1  Open Visual Studio
2  Create a new Project(ASP.NET Web Application)
 name it (ImportExcelData)

The whole solution look like in the below image.


To Implement This concept I need to make Excel file that shown in below.


After The Creation Of Excel File ,write the following code in Default.aspx page.
Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ImportExcelData._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></title>
    <script type="text/javascript">
        function Check(sender, args) {
          var fileextension =
           if (fileextension == ".xlsx")
           {
            alert('Select only Xlsx file');
           }
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>
        <div>
            <asp:FileUpload ID="FileUpload1" runat="server" />
            <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
        </div>
        <div>
            <asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional">
                <ContentTemplate>
                    <asp:GridView ID="Gridview1" runat="server" AutoGenerateColumns="false">
                        <Columns>
                            <asp:BoundField DataField="Name" HeaderStyle-BackColor="Chocolate" HeaderText="Name"
                                ApplyFormatInEditMode="true" ShowHeader="true" NullDisplayText="Null" />
                            <asp:BoundField DataField="Address" HeaderStyle-BackColor="Chocolate" HeaderText="Address"
                                ShowHeader="true" />
                            <asp:BoundField DataField="Salary" HeaderStyle-BackColor="Chocolate" HeaderText="Salary"
                                ShowHeader="true" />
                        </Columns>
                    </asp:GridView>
                </ContentTemplate>
            </asp:UpdatePanel>
            <asp:Button runat="server" ID="BtnUpload" OnClick="BtnUpload_OnClick" Text="Upload" />
        </div>
    </div>
    </form>
</body>
</html>
C# code of Default.aspx.cs
Default.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data.OleDb;
namespace ImportExcelData
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        protected void BtnUpload_OnClick(object sender, EventArgs e)
        {
            Label1.Text = FileUpload1.PostedFile.FileName;
            if (FileUpload1.HasFile)
            {
                string connectionstring = "";
                string filename = Path.GetFileName(FileUpload1.PostedFile.FileName);
                string fileextension = Path.GetExtension(FileUpload1.PostedFile.FileName);
                string filelocation = Server.MapPath("~/file/") + filename;
                FileUpload1.SaveAs(filelocation);
                if (fileextension == ".xlsx")
                {
                    connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filelocation + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=2\"";
                    OleDbConnection conn = new OleDbConnection(connectionstring);
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.CommandText = "select * from [Sheet1$]";
                    cmd.Connection = conn;
                    OleDbDataAdapter oda = new OleDbDataAdapter(cmd);
                    System.Data.DataTable dt = new System.Data.DataTable();
                    oda.Fill(dt);
                    Gridview1.DataSource = dt;
                    Gridview1.DataBind();
                }
            }
            else
            {
                BtnUpload.Attributes.Add("OnClientClick", "javascript:alert('Select Excel file');");
                Gridview1.DataSource = null;
                Gridview1.DataBind();
            }
            FileUpload1.Attributes.Clear();
        }
    }
}

Run the application and see the output as below.




0 comments:

Post a Comment

Popular Posts

Recent Posts

Sample Text

Stats

Powered by Blogger.

Popular Posts

Popular Posts

Join US on Facebook