This is a migrated thread and some comments may be shown as answers.

Implementing a "Paste From Excel" button in RadGrid

7 Answers 266 Views

Grid

This is a migrated thread and some comments may be shown as answers.

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

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.

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

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.