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.
Comments
Anonymous
June 20, 2011
Hey, first I'd like to say thanks for the code! I really needed it for something I'm building. That being said, I need to be able to limit the number of columns that the users may paste to say... 4 columns. Would I accomplish this in GridView by setting AutoGenerateColumns="false", and then specifying <asp:BoundField> in between the column tags? I need to be able to retrieve the pasted data, which can have a variable amount of rows but only four columns. Thanks in advance, -JakeAnonymous
July 03, 2012
its not working in my systeam please provide fully codeAnonymous
August 05, 2012
javascript function pasteContent only works for IE.Anonymous
September 11, 2012
Thanks so much it is a beautiful tool to start with!Anonymous
June 21, 2013
Same issue as JNewby here, would love to know how to edit how many columns are displayed to the user.Anonymous
July 14, 2013
Thanks for this code, dude u solved my problem... Thanks alot.Anonymous
December 24, 2013
Same issue as JNewby here, would love to know how to edit how many columns are displayed to the user.Anonymous
December 24, 2013
Please tell how to edit how many columns are displayed to the user???Anonymous
May 30, 2014
Is there a way to target a specific column?