Copy And Paste Excel / Clipboard Data To Gridview In ASP.net
Q Is there any means to copy the excel data to Gridview in ASP.net? It is obvious that you should use the native .net controls without borrowing any plugins / custom controls from a third party.
A If you are .net 2.0+, then there is defenitely an easy way to achieve things. All you need is an Update panel and a Gridview which loads asyncronously after a single line of javascript that captures the data from the clipboard.
We build a page with an UpdatePanel and GridView inside it. Also we will have a HiddenField to hold our clipboard data.
<div>
<asp:Button ID="ReloadCtl" runat="server" Text="Paste" OnClick="ReloadCtl_Click"
OnClientClick="return pasteContent();" />
<asp:ScriptManager ID="ScriptManager1" runat="server" />
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
<asp:HiddenField ID="ClipboardContent" runat="server" ClientIDMode="Static" />
</ContentTemplate>
<Triggers>
<asp:AsyncPostBackTrigger ControlID="ReloadCtl" />
</Triggers>
</asp:UpdatePanel>
</div>
We will have a simple javascript code which will read the clipboard data and paste it to the control.
<script language="javascript" type="text/javascript">
function pasteContent() {
document.getElementById('ClipboardContent').value = window.clipboardData.getData('Text');
return (true);
}
</script>
Our next job would be to write a method which will get a excel / clipboard data and convert it to a datatable so that we can bind it to the gridview.
public static DataTable GetDataTable(string content, bool IsFirstColumnHeader)
{
DataTable value = new DataTable();
string[] rowValues;
string[] colValues;
int colIndex = 0;
int rowIndex = 0;
if (string.IsNullOrEmpty(content) || content == "null")
return null;
content = content.Replace("\r", "");
rowValues = content.Split(RowDelemiter);
foreach (string rowItem in rowValues)
{
if (string.IsNullOrEmpty(rowItem))
continue;
colValues = new string[rowItem.Split(ColumnDelemiter).Length];
colIndex = 0;
foreach (string colItem in rowItem.Split(ColumnDelemiter))
{
if (rowIndex == 0 && IsFirstColumnHeader)
value.Columns.Add(colItem);
else if (rowIndex == 0)
value.Columns.Add();
if ((rowIndex == 0 && !IsFirstColumnHeader) || rowIndex != 0)
colValues[colIndex] = colItem;
colIndex++;
}
if ((rowIndex == 0 && !IsFirstColumnHeader) || rowIndex != 0)
value.Rows.Add(colValues);
rowIndex++;
}
return value;
}
The above code is written to generate a datatable with / without the first row as header. This holds good when you are not using and entity pattern for your code. But, this is not true 99% of times as any efficient application would require a entity mapping to be done for your data. In that case we could use the below code which takes advantage of the reflection to fill the properties.
public static List<T> GetData<T>(string content)
{
List<T> value = new List<T>();
T obj;
string[] rowValues;
string[] colValues;
int colIndex = 0;
PropertyInfo pInfo;
if (string.IsNullOrEmpty(content) || content == "null")
return null;
content = content.Replace("\r", "");
rowValues = content.Split(RowDelemiter);
foreach (string rowItem in rowValues)
{
if (string.IsNullOrEmpty(rowItem))
continue;
colValues = new string[rowItem.Split(ColumnDelemiter).Length];
colIndex = 0;
obj = (T)Activator.CreateInstance(typeof(T));
foreach (string colItem in rowItem.Split(ColumnDelemiter))
{
pInfo = obj.GetType().GetProperties()[colIndex];
pInfo.SetValue(obj, colItem, null);
colIndex++;
}
value.Add(obj);
}
return value;
}
It's obvious that the above code does not address any validation of the fields / properties but this gives you a better insight on how to work out this functionality so that you can customize to your neccessity.
How it Works
- Once you click the "Paste" button, the javascript copies the clipboard content to the hidden control and triggers an asynchronous postback.
- The code behind reads the content and converts to datatable or object list as per neccessity.
- Bind the datasource which is created.
Download the attachment for a sample project.