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

  1. Once you click the "Paste" button, the javascript copies the clipboard content to the hidden control and triggers an asynchronous postback.
  2. The code behind reads the content and converts to datatable or object list as per neccessity.
  3. Bind the datasource which is created.

Download the attachment for a sample project.