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.

<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

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