Click here to Skip to main content
15,867,834 members
Articles / Productivity Apps and Services / Microsoft Office / Microsoft Excel
Tip/Trick

Pasting excel data into an asp.net gridview

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
21 Dec 2011CPOL2 min read 26.2K   6  
Pasting excel data into an asp.net gridview
Disclaimer: There are many validators and other things which pasting into the gridview can upset. I have attempted to fix all bugs but I am using it for a limited scope, so be careful.

By intercepting the onpaste event in the textbox we can figure out row and and column to paste in data on the gridview. From Excel, paste data is intercepted with the delimiters as Column= \t and Row=\r\n

We can pull out the clipboard data on IE browsers with:
var clipboardData = window.clipboardData.getData('Text');


To debug the script remember you can hit f12 when debugger is actived in internet explorer.

To register your javascript I suggest putting it in a .js file rather than pasting it in the head or appending it to the control in the code behind as I have had a lot of trouble. However, either way should* work.

I put this in my masterpage in page load and it saved me many headaches.:
Page.ClientScript.RegisterClientScriptInclude("MyJSfunction", Page.ResolveUrl("~/YourJSXXXXXFile.js"));



The main thing to understand here is we use a textbox field to allow us to catch the paste and also have the ability to insert a value from the Js.

XML
<asp:GridView ID="gridviewPasterTest" runat="server" AutoGenerateColumns="False"
  DataSourceID="xmlDataSource1"  >
    <Columns>
        <asp:TemplateField HeaderText="MainID">
              <ItemTemplate>
                <asp:TextBox ID="textBox1"
                             runat="server"
                             Text='<%# Your data bind utility here%>'
                             OnTextChanged="textBoxCashFlow_OntextChanged"
                            onpaste="return excelPaste(event);"
                             >

                </asp:TextBox>
              </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>




This is very straight forward js, so I will not explain it but if you have questions I will always update my stuff - rj

cs<br
function excelPaste(evt, coloffset)
{
    //alert(evt);
    //alert('ver6');
    var txtSender = evt.srcElement;
    var gvtemplate = txtSender.parentElement;
    var gvrow = gvtemplate.parentElement;
    var gridview = gvrow.parentElement;
    var startingRow = gvrow.rowIndex;
    var currentColumn = gvtemplate.cellIndex;
    var clipboardData = window.clipboardData.getData('Text');
    var rows = clipboardData.split(/\r\n/);

    for (var currRow = 0; currRow < rows.length-1; currRow++)
    {
        var cols = rows[currRow].split(/\t/);
        for (var currCol = 0; currCol < cols.length; currCol++)
        {
            if ( currRow+startingRow < gridview.children.length )
            {
               if ( currCol+currentColumn < gridview.children[currRow+startingRow].children.length)
               {
                    gridview.children[currRow+startingRow].children[currCol+currentColumn].children[0].value = cols[currCol];
               }
            }
        }
    }

    return false;
}



btw- I was also working on a version where I do a postback on the paste event and parse it in codebehind but I couldn't find an elegant way to get the postback working. I found that you can do the postback in js by building it like so then you can derive from an interface to parse out the parameters but I never got it working very well and if anyone has a sample of mapping dynamic HTML events to new codebehind events I would really appreciate that snippit. Thanks!

The following works for building the postback command which you can call from JS.
postBackStr= Page.ClientScript.GetPostBackEventReference(this, "MyCustomArgument");



refrences: http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=6721&lngWId=14

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
Canada Canada
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
-- There are no messages in this forum --