Copy and paste data from Excel Clipboard to GridView in ASPNet

In this article I will explain how to copy data from Excel file or Clipboard and paste it to GridView in ASP.Net using C# and VB.Net.

We cannot paste the copied data from Excel file or Clipboard to GridView and hence we need to paste the data to an ASP.Net Multiline TextBox or TextArea and then populate GridView.

HTML Markup

The HTML Markup has a GridView and a Multiline TextBox to which OnTextChanged event handler is assigned and AutoPostBack property is set to true.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">

<asp:BoundField DataField="Id" HeaderText="Id" ItemStyle-Width="30" />

<asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-Width="150" />

<asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="150" />

</Columns>

</asp:GridView>

<br />

<asp:TextBox ID="txtCopied" runat="server" TextMode="MultiLine" AutoPostBack="true"

OnTextChanged="PasteToGridView" Height="200" Width="400" />

Implementing the OnPaste Event handler

Inside the window onload event handler, a dynamic OnPaste event handler is assigned to the Multiline ASP.Net TextBox. Thus as soon as we paste something, it does a PostBack and the OnTextChanged event handler of the Multiline TextBox is triggered.

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 namespace.

using System.Data;

VB.Net

Imports System.Data

Populating GridView from the copied Clipboard data

As soon as something is pasted in the Multiline TextBox, the following event handler is triggered.

First it creates a DataTable and then the copied content is split using New Line and Tab characters and using a loop the data is saved into the DataTable.

Finally the DataTable is used to populate the GridView.

Note: The Column structure of Excel and the DataTable must be equal otherwise this code will not work.

protected void PasteToGridView(object sender, EventArgs e)

{

DataTable dt = new DataTable();

dt.Columns.AddRange(new DataColumn[3] { new DataColumn("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++;

}

GridView1.DataSource = dt;

GridView1.DataBind();

txtCopied.Text = "";

}

VB.Net

Protected Sub PasteToGridView(sender As Object, e As EventArgs)

Dim dt As New DataTable()

dt.Columns.AddRange(New DataColumn(2) {New DataColumn("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

End If

GridView1.DataSource = dt

GridView1.DataBind()

txtCopied.Text = ""