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">
<Columns>
<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.
<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 namespace.
C#
VB.Net
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.
C#
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
Next
End If
Next
GridView1.DataSource = dt
GridView1.DataBind()
txtCopied.Text = ""
End Sub
The
above code has been tested in the following browsers
* All
browser logos displayed above are property of their respective owners.
Demo
Downloads