This is a migrated thread and some comments may be shown as answers.
Implementing a "Paste From Excel" button in RadGrid
7 Answers 100 Views
This is a migrated thread and some comments may be shown as answers.
blablabla
Top achievements
Rank 1
blablabla asked on 21 Dec 2011, 05:56 PM
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.

7 Answers, 1 is accepted

Sort by
0
Bruno
Top achievements
Rank 2
answered on 21 Dec 2011, 06:19 PM
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.
0
blablabla
Top achievements
Rank 1
answered on 21 Dec 2011, 07:15 PM
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.
0
blablabla
Top achievements
Rank 1
answered on 22 Dec 2011, 05:03 PM
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...
0
Tom
Top achievements
Rank 1
answered on 23 Mar 2012, 05:19 PM

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!


0
cheekl
Top achievements
Rank 2
answered on 27 Apr 2012, 09:05 AM
Is there any solution to this problem? 
0
Antonio Stoilkov
Telerik team
answered on 02 May 2012, 07:08 AM
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.
0
Elsa
Top achievements
Rank 1
answered on 19 Jun 2012, 07:01 PM
Has the latest version of the RadControls for ASP.NET AJAX Grid control solved this paste from Excel problem?
Tags
Grid
Asked by
blablabla
Top achievements
Rank 1
Answers by
Bruno
Top achievements
Rank 2
blablabla
Top achievements
Rank 1
Tom
Top achievements
Rank 1
cheekl
Top achievements
Rank 2
Antonio Stoilkov
Telerik team
Elsa
Top achievements
Rank 1
Share this question
or