Implementing a "Paste From Excel" button in RadGrid

[blablabla]

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.

[Bruno]

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.

[blablabla]

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.

[blablabla]

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...

[Tom]

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!

[cheekl]

Is there any solution to this problem? 

[Antonio Stoilkov]

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

[Elsa]

Has the latest version of the RadControls for ASP.NET AJAX Grid control solved this paste from Excel problem?