Copy and paste data from excel in asp.net

Description:-

I would like to be able to copy from an Excel spreadsheet to a table like structure on a ASP.NET web form. The idea is keeping the data in the exact from Excel to the table like control on the ASP.NET web-form. Then I want to be able to save to the contents of the ASP.NET control to a database by a button command click.

For doing this you required access database engine for Create Connection. 

Download: - AccessDatabaseEngine

You can also Save Data in Database if you want. Here in this Example I have given a Simple Example to Copy Data from Excel and Paste to Text Area to display in Gridview. In next Example we will bind data in database.

Web.Config:-

<connectionStrings>    
    <add name ="ExcelConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Extended Properties='Excel 8.0;HDR={1}'"/>
</connectionStrings>

Default.aspx:-

<script type="text/javascript">
   window.onload = function () {
      document.getElementById("<%=txtCopied.ClientID %>").onpaste = function () {
         var txt = this;
         setTimeout(function () {
            __doPostBack(txt.name, '');
          }, 100);
        }
     };
</script>

<div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
            <Columns>
                <asp:BoundField DataField="Id" HeaderText="Id" ItemStyle-Width="30" ItemStyle-HorizontalAlign="Center" ItemStyle-VerticalAlign="Middle" />
                <asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-Width="150" ItemStyle-HorizontalAlign="Center" ItemStyle-VerticalAlign="Middle"/>
                <asp:BoundField DataField="Gender" HeaderText="Gender" ItemStyle-Width="150" ItemStyle-HorizontalAlign="Center" ItemStyle-VerticalAlign="Middle"/>
                <asp:BoundField DataField="Mobile" HeaderText="Mobile" ItemStyle-Width="150" ItemStyle-HorizontalAlign="Center" ItemStyle-VerticalAlign="Middle"/>
                <asp:BoundField DataField="Email" HeaderText="Email" ItemStyle-Width="150" ItemStyle-HorizontalAlign="Center" ItemStyle-VerticalAlign="Middle"/>
            </Columns>
        </asp:GridView>
        <br />
        <asp:TextBox ID="txtCopied" runat="server" TextMode="MultiLine" AutoPostBack="true"
            OnTextChanged="PasteToGridView" Height="200" Width="400" />
    </div>

Default.aspx.cs:-

        protected void PasteToGridView(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[5] { new DataColumn("Id", typeof(int)),
                    new DataColumn("Name", typeof(string)),
                    new DataColumn("Gender",typeof(string)),
                    new DataColumn("Mobile",typeof(Int64)),
                    new DataColumn("Email",typeof(string))
            });

            string copiedContent = Request.Form[txtCopied.UniqueID];
            foreach (string row in copiedContent.Split('\n'))
            {
                if (!string.IsNullOrEmpty(row))
                {
                    dt.Rows.Add();
                    int i = 0;
                    foreach (string cell in row.Split('\t'))
                    {
                        dt.Rows[dt.Rows.Count - 1][i] = cell;
                        i++;
                    }
                }
            }
            GridView1.DataSource = dt;
            GridView1.DataBind();
            txtCopied.Text = "";
        }

Author : Umesh Patel

Related Posts