In this article I will explain with an example, how to insert copied data from Excel file into database in ASP.Net using C# and VB.Net.
 
 

Database

I have made use of the following table Customers with the schema as follow.
Copy Paste Excel data into Database in ASP.Net
 
I have already inserted few records in the table.
Copy Paste Excel data into Database in ASP.Net
 
Note: You can download the database table SQL by clicking the download link below.
          Download SQL file
 
 

HTML Markup

The HTML Markup consists of following control:
TextBox – For capturing copied data of Excel file.
 
Properties:
The TextBox has been assigned with following properties:
TextMode – For setting the Mode. Here it is set to Multiline.
AutoPostBack – For enabling PostBack.
 
Events:
The TextBox has been assigned with an OnTextChanged event handler.
<asp:TextBox ID="txtCopied" runat="server" TextMode="MultiLine" AutoPostBack="true" OnTextChanged="PasteToGridView" Height="200" Width="400" /> 
 
 

Implementing the JavaScript OnPaste Event handler

Inside the window.onload event handler, a dynamic onpaste event handler is assigned to the Multiline ASP.Net TextBox.
Thus, when data is pasted in the TextBox, it does a PostBack and the OnTextChanged event handler of the Multiline TextBox is triggered.
<script type="text/javascript">
    window.onload = function () {
        document.getElementById("<%=txtCopied.ClientID %>").onpaste = function () {
            var txt = this;
            setTimeout(function () {
                __doPostBack(txt.name, '');
            }, 100);
        }
    };
</script>
 
 

Namespaces

You will need to import the following namespaces.
C#
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
 
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
 
 

Converting copied data of Excel into DataTable in C# and VB.Net

When Excel data is pasted in the Multiline TextBox, a DataTable with schema same as Excel file is created.
Then, the Excel data is fetched from the Request.Form collection.
After that, Using cascading FOR EACH loops, Excel data is split into Rows and Columns using New Line (\n) and Tab (\t) characters and a populated DataTable is passed as parameter to Insert method (explained later).
Finally, the contents of the Multiline TextBox are removed.
protected void PasteToGridView(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Customer Id", typeof(int)),  
                                            new DataColumn("Name", typeof(string)),  
                                            new DataColumn("Country", 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++;
            }
        }
    }
    this.Insert(dt);
    txtCopied.Text = string.Empty;
}
 
VB.Net
Protected Sub PasteToGridView(sender As Object, e As EventArgs)
    Dim dt As New DataTable()
    dt.Columns.AddRange(New DataColumn(2) { New DataColumn("Customer Id", GetType(Integer)),  
                                        New DataColumn("Name", GetType(String)),  
                                        New DataColumn("Country", GetType(String))})
 
    Dim copiedContent As String = Request.Form(txtCopied.UniqueID)
    For Each row As String In copiedContent.Split(ControlChars.Lf)
        If Not String.IsNullOrEmpty(row)Then
            dt.Rows.Add()
            Dim i As Integer = 0
            For Each cell As String In row.Split(ControlChars.Tab)
                dt.Rows(dt.Rows.Count - 1)(i) = cell
                i += 1
            Next
        End If
    Next
    Me.Insert(dt)
    txtCopied.Text = String.Empty
End Sub
 
 

Inserting Copied Data of Excel into Database using C# and VB.Net

Inside the Insert method, the data present in the DataTable which is accepted as parameter is inserted into the database using SqlBulkCopy class.
Note: For more details on inserting records using SqlBulkCopy class, please refer my article Using SqlBulkCopy to insert bulk data from GridView to database in ASP.Net.
 
C#
private void Insert(DataTable dt)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
        {
            //Set the database table name.
            sqlBulkCopy.DestinationTableName = "dbo.Customers";
 
            //[OPTIONAL]: Map the DataTable columns with that of the database table.
            sqlBulkCopy.ColumnMappings.Add("Customer Id", "CustomerId");
            sqlBulkCopy.ColumnMappings.Add("Name", "Name");
            sqlBulkCopy.ColumnMappings.Add("Country", "Country");
            con.Open();
            sqlBulkCopy.WriteToServer(dt);
            con.Close();
        }
    }
}
 
VB.Net
Private Sub Insert(ByVal dt As DataTable)
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As New SqlConnection(constr)
        Using sqlBulkCopy As New SqlBulkCopy(con)
            'Set the database table name.
            sqlBulkCopy.DestinationTableName = "dbo.Customers"
 
            '[OPTIONAL]: Map the DataTable columns with that of the database table.
            sqlBulkCopy.ColumnMappings.Add("Customer Id", "CustomerId")
            sqlBulkCopy.ColumnMappings.Add("Name", "Name")
            sqlBulkCopy.ColumnMappings.Add("Country", "Country")
            con.Open()
            sqlBulkCopy.WriteToServer(dt)
            con.Close()
        End Using
    End Using
End Sub
 
 

Screenshot

Form

Copy Paste Excel data into Database in ASP.Net
 

Inserted records

Copy Paste Excel data into Database in ASP.Net
 
 

Downloads

Download Code