Sunday, February 19

Upload a Excel file and display it in agridview as well as display this excel sheet from a starting row to ending row

Here is my Source Code:-

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_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>
</head>
<body>
    <form id="form1" runat="server">
    <center>
    <div style="width:940px">
    <li style="width:360px; padding-top:3px; float:left; text-align:right; list-style:none">Upload Only Excel File</li>
    <li style="width:20px; padding-top:3px; float:left; text-align:center; list-style:none; font-weight:900">:</li>
    <li style="width:560px; padding-top:3px; float:left; text-align:left; list-style:none"><asp:FileUpload ID="fu" runat="server" /></li>
    <li style="width:360px; padding-top:3px; float:left; text-align:right; list-style:none">&nbsp;</li>
    <li style="width:20px; padding-top:3px; float:left; text-align:center; list-style:none; font-weight:900">&nbsp;</li>
    <li style="width:560px; padding-top:3px; float:left; text-align:left; list-style:none">
        <asp:Button ID="btn" runat="server" Text="Display" Width="80" 
            onclick="btn_Click" /></li>
   <%-- <li style="width:360px; padding-top:3px; float:left; text-align:right; list-style:none">&nbsp;</li>
    <li style="width:20px; padding-top:3px; float:left; text-align:center; list-style:none; font-weight:900">&nbsp;</li>--%>
    <li style="width:940px; padding-top:3px; float:left; text-align:center; list-style:none"><asp:GridView Width="100%"  ID="gv" runat="server"></asp:GridView></li>
    <li style="width:360px; padding-top:3px; float:left; text-align:right; list-style:none">Header Row Number</li>
    <li style="width:20px; padding-top:3px; float:left; text-align:center; list-style:none; font-weight:900">:</li>
    <li style="width:560px; padding-top:3px; float:left; text-align:left; list-style:none"><asp:TextBox ID="tb" runat="server"></asp:TextBox></li>
    <li style="width:360px; padding-top:3px; float:left; text-align:right; list-style:none">Start Row Number</li>
    <li style="width:20px; padding-top:3px; float:left; text-align:center; list-style:none; font-weight:900">:</li>
    <li style="width:560px; padding-top:3px; float:left; text-align:left; list-style:none"><asp:TextBox ID="tb1" runat="server"></asp:TextBox></li>
    <li style="width:360px; padding-top:3px; float:left; text-align:right; list-style:none">End Row Number</li>
    <li style="width:20px; padding-top:3px; float:left; text-align:center; list-style:none; font-weight:900">:</li>
    <li style="width:560px; padding-top:3px; float:left; text-align:left; list-style:none"><asp:TextBox ID="tb2" runat="server"></asp:TextBox></li>
     <li style="width:360px; padding-top:3px; float:left; text-align:right; list-style:none">&nbsp;</li>
    <li style="width:20px; padding-top:3px; float:left; text-align:center; list-style:none; font-weight:900">&nbsp;</li>
    <li style="width:560px; padding-top:3px; float:left; text-align:left; list-style:none">
        <asp:Button ID="Button1" runat="server" Text="Save" Width="80" 
            onclick="Button1_Click" /></li>
<%--    <li style="width:360px; padding-top:3px; float:left; text-align:right; list-style:none">&nbsp;</li>
    <li style="width:20px; padding-top:3px; float:left; text-align:center; list-style:none; font-weight:900">&nbsp;</li>--%>
    <li style="width:940px; padding-top:3px; float:left; text-align:center; list-style:none"><asp:GridView Width="100%"  ID="gv1" runat="server"></asp:GridView></li>


    </div>
    </center>
    </form>
</body>
</html>

Here is my Code Behind:-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;

public partial class _Default : System.Web.UI.Page
{
    #region
   static  OleDbConnection cn = null;
    OleDbCommand cm = null;
    OleDbDataAdapter da = null;
    DataTable dt = new DataTable();
    DataTable dt1 = new DataTable();
    int i;
    #endregion
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btn_Click(object sender, EventArgs e)
    {
        string s = null;
        if (fu.HasFile)
        {
            string[] st = fu.PostedFile.ContentType.ToString().Split('/');
            if (st[0] == "application")
            {
               
                string[] st1 = fu.FileName.ToString().Split('.');
               
                s = "test" +"."+ st1[1].ToString();
                fu.SaveAs(Server.MapPath("~/upload/" + s));
                string t = Server.MapPath("~/upload/" + s);
                string test = st1[1].ToString().Trim();
                if (st1[1].ToString().Trim() == "xlsx".Trim())
                    cn = new OleDbConnection ( "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + t + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"");
                else
                    cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +t + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"");
                fill();
            }
           
        }
       
    }

    void fill()
    {
       
        da = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", cn);
        dt.Clear();
        da.Fill(dt);
        gv.DataSource = dt;
        gv.DataBind();
        ViewState["dt"] = dt;
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        fill();
        dt1.Columns.Add("ID", typeof(string));
        dt1.Columns.Add("NAME", typeof(string));
        dt1.Columns.Add("AGE", typeof(string));
        DataRow dr;

        for (i = (Convert.ToInt32(tb1.Text) - 1); i < (Convert.ToInt32(tb2.Text)); i++)
        {
            dr = dt1.NewRow();
            dr[0] = gv.Rows[i].Cells[0].Text;
            dr[1] = gv.Rows[i].Cells[1].Text;
            dr[2] = gv.Rows[i].Cells[2].Text;
            dt1.Rows.Add(dr);
        }
        gv1.DataSource = dt1;
        gv1.DataBind();
    }
}



First of all you have to create a folder as "upload" in your solution
Here is my designView:-


Thanks Shibashish Mohanty
Posted by at
Labels: , ,

No comments:

Post a Comment

Please don't spam, spam comments is not allowed here.

Subscribe to: Post Comments (Atom)

.

.
ShibashishMnty
shibashish mohanty