Implementing a "Paste From Excel" button in RadGrid

8 posts, 0 answers
  1. blablabla
    blablabla avatar
    23 posts
    Member since:
    Jul 2011

    Posted 21 Dec 2011 Link to this post

    Hi,

    I am looking to implement a feature within the RadGrid that allows the pasting of clipboard information copied from an Excel sheet.

    I found an old thread that seeks to implement this, and I managed to get it working with GridBoundColumns that contain a regular string. Basically, I copy items from Excel, I click on a cell in the Grid, then I click on a "Paste From Excel" button I added to the CommandItemTemplate, and it pastes the clipboard informatoin.

    Thing is, I am having issues getting this to work with other types of columns, such as a GridDateTimeColumn or a GridNumericColumn.

    In the case of the GridDateTimeColumn, nothing appears in the cell, and I'm thinking it's due to formatting issues within javascript.

    In the case of the GridNumericColumn, the value is pasted into the cell, but as soon as I click on the cell, the value disappears.

    Here's the javascript code:

    var lastFocused;
     
                    function pasteFromExcel() {
                        debugger;
                        if (!lastFocused) return;
                        var clipData = window.clipboardData.getData('Text');
                        var crlf = String.fromCharCode(13) + String.fromCharCode(10);
                        var table = clipData.split(crlf);
                        for (var tRow = 0; tRow < table.length - 1; tRow++)
                            table[tRow] = table[tRow].split(String.fromCharCode(9));
                        Array.remove(table, table[table.length - 1]);
                        fillTable(table);
                    }
     
                    function fillTable(table) {
                        var pCell = lastFocused.parentNode;
                        var pRow = pCell.parentNode.parentNode.parentNode.parentNode.parentNode.parentNode.parentNode;
                        var pBody = pRow.parentNode;
                        var maxRows = pBody.rows.length;
                        var maxCols = pRow.cells.length;
     
                        for (var row = 0; row < table.length; row++) {
                            for (var col = 0; col < table[row].length; col++) {
                                debugger;
                                var cCellIndex = pCell.parentNode.cellIndex + col;
                                var cRowIndex = pRow.sectionRowIndex + row;
     
                                if (cRowIndex < maxRows && cCellIndex < maxCols) {
                                    var cCell = pBody.rows[cRowIndex].cells[cCellIndex];
                                    var pInput = cCell.getElementsByTagName("input")[0];
     
                                    pInput.style.backgroundColor = "#F7FAFF";
     
                                    if (cCellIndex == 0)
                                        pInput.value = new Date(Date.parse(table[row][col]));
                                    else if (cCellIndex == 2)
                                        pInput.value = parseFloat(table[row][col]);
                                    else
                                        pInput.value = table[row][col];
                                }
                            }
                        }
                    }
     
                    function gridFocus(e) {
                        e = e || window.event;
                        var target = e.target || e.srcElement;
                        if (target.tagName.toLowerCase() == "input" && target.type.toLowerCase() == "text")
                            lastFocused = target;
                    }

    Please note that I hard-coded some condition checks for the cCellIndex variable for testing purposes.

    Here's some of the relevant RadGrid definition:

    <telerik:RadGrid ID="RadGrid1" runat="server" AllowFilteringByColumn="false" AllowMultiRowEdit="true"
        AutoGenerateColumns="false" AllowPaging="false" PageSize="1000" AllowSorting="True"
        CellSpacing="0" DataSourceID="RadGrid1TableDataSource" GridLines="None" Skin="Office2007"
        RegisterWithScriptManager="false" ShowFooter="true" EnableLinqExpressions="false" ValidationSettings-EnableValidation="false"  OnClick="gridFocus(event)">
     <MasterTableView EditMode="InPlace" DataKeyNames="PeriodID" AllowAutomaticDeletes="true"
            AllowAutomaticInserts="true" CommandItemDisplay="Bottom">
    <columns>
    <telerik:GridBoundColumn DataField="PeriodID" UniqueName="PeriodID"
                    HeaderText="<%$ Resources:GlobalResources, PeriodID %>"
                    Visible="false" />
    <telerik:GridTemplateColumn DataField="RepaymentDate" UniqueName="RepaymentDate"
                    HeaderText="<%$ Resources:GlobalResources, RepaymentDate %>"
                    FooterText="Totals:">
                    <ItemTemplate>
                        <%# DataBinder.Eval(Container.DataItem, "RepaymentDate", "{0:dd MMM yyyy}")%>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <telerik:RadDatePicker ID="RepaymentDatePicker" runat="server" RegisterWithScriptManager="false"
                            SelectedDate='<%# DataBinder.Eval(Container.DataItem, "RepaymentDate") == DBNull.Value ? (DateTime?)null : DataBinder.Eval(Container.DataItem, "RepaymentDate")%>' />
                    </EditItemTemplate>
                    <InsertItemTemplate>
                        <telerik:RadDatePicker ID="RepaymentDatePicker" runat="server" RegisterWithScriptManager="false" />
                    </InsertItemTemplate>
                </telerik:GridTemplateColumn>
                <telerik:GridBoundColumn DataField="MovementTypeText" UniqueName="MovementTypeText"
                    HeaderText="<%$ Resources:GlobalResources, MovementTypeText %>" />
                <telerik:GridNumericColumn DataField="OutstandingBeforeRepaymentAmount" UniqueName="OutstandingBeforeRepaymentAmount"
                    HeaderText="<%$ Resources:GlobalResources, OutstandingBeforeRepaymentAmount %>"
                    NumericType="Number" />
    </columns>
    <CommandItemTemplate>
                <table cellpadding="5" style="width: 100%">
                    <tr>
                        <td align="left">
                            <asp:LinkButton ID="btnAddNewRecord" runat="server" CommandName="InitInsertSpecial">
                                <img style="border:0px" alt="" src="../Images/add.png" />Add New Record
                            </asp:LinkButton>
                            <telerik:RadNumericTextBox ID="txtLineNum" runat="server"
                                Type="Number" Value="1" MinValue="1" MaxValue="30" ClientEvents-OnKeyPress="CommandKeyPress">
                                <NumberFormat GroupSeparator="" DecimalDigits="0" />
                            </telerik:RadNumericTextBox>
                        </td>
                        <td align="center">
                            <asp:LinkButton ID="btnPasteFromExcel" runat="server" OnClientClick="pasteFromExcel(); return false;">
                                Paste From Excel
                            </asp:LinkButton>
                        </td>
                        <td align="right">
                            <asp:LinkButton ID="btnDeleteSelected" runat="server" CommandName="DeleteSelected"
                                OnClientClick="javascript:return confirm('Delete all selected periods?')">
                                <img src="../Images/delete.png" alt="" style="border:0px" />Delete Selected
                            </asp:LinkButton>
                        </td>
                    </tr>
                </table>
            </CommandItemTemplate>
    </MasterTableView>
    </telerik:RadGrid>

    I hope this is clear enough.

    Anybody has any ideas?

    Thank you.
  2. Bruno
    Bruno avatar
    105 posts
    Member since:
    Mar 2008

    Posted 21 Dec 2011 Link to this post

    The editor controls for these columns seems to be different. Looking at the page source I think you should find a way to differentiate whether the current input is part of a composite control like rad.$$..box (i'd try the classname, parentnode ,etc). If this is so, you should set the value to the client object (using set_value) instead of the html elements that represent it.
    Thats my 2¢ but you better try it cause my findings are based on firebug experiments + some googling.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. blablabla
    blablabla avatar
    23 posts
    Member since:
    Jul 2011

    Posted 21 Dec 2011 Link to this post

    Thanks for the suggestion, but it seems that the HTML elements are the ones always returned and the set_value() method is always undefined.

    I did solve my problem for the Date column in the ItemTemplate. Since I have an ID for the control, I first got the MasterTableView of the grid, I retrieved the gridRow, and then used the findControl() method to find the control, which allows me to access the set_selectedDate() method. Sample code:

    var masterTableView = $find("<%= RadGrid1.ClientID %>").get_masterTableView();
      
    var gridRow = masterTableView.get_dataItems()[cRowIndex]; // cRowIndex defined earlier
      
    gridRow.findControl("RepaymentDatePicker").get_dateInput().set_selectedDate(new Date(Date.parse(table[row][col]))); // table[row][col] define earlier

    Now I can't do the same thing for the other columns because they are GridNumericColumns and I can't find the control using an ID. Potentially, I could change them all to use an ItemTemplate with a RadNumericTextBox in them, but I'd rather find a solution to this problem first.

    Thanks.
  5. blablabla
    blablabla avatar
    23 posts
    Member since:
    Jul 2011

    Posted 22 Dec 2011 Link to this post

    So I moved forward in this, but I still have troubles.

    The code: cCell.getElementsByTagName("input") actually returns 4 inputs for the GridNumericColumn. I have to set the value of all 4 inputs for it to appear all well and good in the grid.

    So I got the Copy/Paste to work, but now I have a new problem with the RadGrid: In the culture we use, commas are the decimal separators. If I have the value "85,43" in one of the columns, and I go to save this data in the database, I get a primitive JSON error: 43.

    This leaves me very confused.

    I attempted to use the javascript method parseFloat thinking that perhaps I need to assign a float value to the value input, but I still have the same issue.

    Any ideas? 

    --------------

    Quick edit: I finally got it working. The 4 inputs all have different IDs. First one ends with Uniquename_Text, 2nd one ends w/ Uniquename, 3rd one ends with Uniquename_Value, 4th one ends with Uniquename_ClientState.

    You should only set the values for the 3 first inputs. The first 2 require a comma in my case, while the value requires a value with a dot. That's it...
  6. Tom
    Tom avatar
    1 posts
    Member since:
    Mar 2012

    Posted 23 Mar 2012 Link to this post

    Hi 'blablabla',

    Is there a publically accessible demo that demonstrates this working?

    I'm in need of a datagrid with a dynamic number of rows.

     I intend to populate a table in a MySQL database with the data entered.  The columns would obviously be pre-determined, but I had hoped to make it as easy to enter 3 lines of data, as it would be 300 lines – therefore I’m trying to find a tool that is ‘clipboard compatible’ with Microsoft Excel.

    This is proving to be tougher than I expected!


  7. cheekl
    cheekl avatar
    55 posts
    Member since:
    Jul 2012

    Posted 27 Apr 2012 Link to this post

    Is there any solution to this problem? 
  8. Antonio Stoilkov
    Admin
    Antonio Stoilkov avatar
    530 posts

    Posted 02 May 2012 Link to this post

    Hi,

    As discussed in this thread paste from excel could be achieved by getting the clipboard data, looping through cells of the grid and applying the values from the data retrieved.

    If someone have already implemented such scenario it could be shared so it is available for all the people who are interested in such functionality.

    Kind regards,
    Antonio Stoilkov
    the Telerik team
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
  9. Elsa
    Elsa avatar
    2 posts
    Member since:
    Mar 2012

    Posted 19 Jun 2012 Link to this post

    Has the latest version of the RadControls for ASP.NET AJAX Grid control solved this paste from Excel problem?
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017