In this article I will explain with an example, how to copy data from
Excel file or Clipboard and paste it to
GridView in ASP.Net using C# and VB.Net.
Note: Copied data or Clipboard cannot be pasted to GridView hence Excel data needs to be pasted into an ASP.Net Multiline TextBox or TextArea and then the GridView is populated.
HTML Markup
The HTML Markup consists of following controls:
GridView – For displaying pasted data of
Excel file.
Columns
The GridView consists of three BoundField columns.
TextBox – For capturing copied data of
Excel file.
Properties:
TextMode – – For setting the Mode. Here it is set to Multiline.
AutoPostBack – For enabling PostBack. Here it is set to TRUE.
Events:
The TextBox has been assigned with an OnTextChanged event handler.
<asp:GridView ID="gvCustomers" 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, 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 namespace.
C#
VB.Net
Populating GridView from the copied Clipboard data of Excel file
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.
Using cascading FOR EACH loops, Excel data is split into Rows and Columns using
New Line (\n) and
Tab (\t) characters and a
DataTable is populated.
Finally, the
GridView is populated with
DataTable with
Excel data and contents of the Multiline
TextBox are removed.
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++;
}
}
}
gvCustomers.DataSource = dt;
gvCustomers.DataBind();
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("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
gvCustomers.DataSource = dt
gvCustomers.DataBind()
txtCopied.Text = String.Empty
End Sub
Screenshot
![Copy and paste data from Excel / Clipboard to GridView in ASP.Net]()
Browser Compatibility
* All browser logos displayed above are property of their respective owners.
Demo
Downloads
