copy from excel sheet and paste it in radgrid

[S R]

hi,

i am very much impressed with telerik controls especially with radgrid.please let we know how can i copy data (rows,columns) from excel sheet and paste it to the rad grid


Thanks,

[Daniel]

Hello,

This is not supported out-of-the-box but you could try to implement it on your own. I attached a sample project to this post - you could use it as a base to your future developments.

Best regards,
Daniel
the Telerik team

Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
Follow the status of features or bugs in PITS and vote for them to affect their priority.

[Jonathan]

Hello,

I can't get this running is there any chance you could create a solution that does not need SQL express for the data source that I can just open end run?

[Jonathan]

OK I got it running by preattaching to SQL Server, however the javascript

pBody.rows

throws an error.  Could you help me figure out what is going on?

Thanks
Jonathan

[Jonathan]

Also my grid is not in edit mode, it has template columns whihch have textboxes in them.  how would I paste into a grid iterating through the rows and cells (columns) - can I use the masterTableView to iterate trough a row's cells?

function pasteFromExcel() {
    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 grid = $find("<%=grdMemberRates.ClientID %>");
    var masterTable = grid.get_masterTableView();
    var rows = masterTable.get_dataItems();
    var firstRow = rows[0];
    for (var row = 0; row < table.length; row++)
    {
        for (var col = 0; col < table[row].length; col++)
        {
            //copy to grid starting at col 0 row 0
            var gridcol = ???
        }
    }

[Jonathan]

I'm just going to embed a silverlight grid to get the funcionality I need.

[Daniel]

Hello Jonathan,

You can either traverse the HTML element that represents the master tableview or use the client-side API and traverse the client objects. The first approach will be faster and you may find it easier to implement as you will rely on your javascript knowledge only.

RadGrid class members
GridTableView class members
GridDataItem class members
GridColumn class members

Traversing an HTML table with JavaScript and DOM Interfaces - MDC Docs

Web Development: Tutorial on Printing and Traversing DOM Using ...

Regards,
Daniel
the Telerik team

[Gajanana]

Hello

pastefromexcel.zip which is attached from you does not work with Mozilla Firefox browser.

Please let us know the reason.

Thanks
Gajanana

[Daniel]

Hello Gajanana,

Indeed, this code works in Internet Explorer only, because it is the only browser that allows direct clipboard access. This is considered a security risk so it is understandable why the other browsers do not permit this.

Regards,
Daniel
the Telerik team

[Gajanana]

      Problem statement

            Problem in copy and paste from excel to RadGrid. For problem scenarios please look at the picture attached and JavaScript to achieve this also given.

Problem 1. After the paste, when we focus to the text box in the grid, the text automatically disappears.

Problem 2. After click on update button the copied data does not appear in the grid row.

 

JavaScriptfunction rmMode_ItemClicked(sender, args) {                pasteFromExcel();            }      var lastFocused;            function pasteFromExcel() {                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 grid = $find("<%=radGrid.ClientID %>");           var masterTable = grid.get_masterTableView();          var rows = masterTable.get_dataItems();          var colNum = 4;                  for (var col = 0; col < table[0].length; col++)             {          var cCell = asterTable.get_selectedItems()[0].get_element().cells[colNum]            colNum = colNum + 1;            var pInput = cCell.getElementsByTagName("input")[0];            pInput.style.backgroundColor = "#F7FAFF";            pInput.value = table[0][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;  }

 

 

[David]

Daniel,

   For me that example works well. However, i need to implement same without SqlDataSource. I removed SqlDataSource and was able to replicate whole functionality, but update. How can i extract values on updateCommand to implement update functionality in code behind? For now i wasn't able to do it.

Thank you

[Konstantin Dikov]

Hello David,

For extracting the values from the edited item you could refer to the following help article, where you will find detailed information and examples on this matter:

• Updating Values Using InPlace and EditForms Modes

Hope this helps.


Regards,
Konstantin Dikov
Telerik

[David]

Thank you Kostadin it was very helpful.

I resolved my problem, but have (as usual) one more question: by putting grid in edited state i lost my skin style. How can i add back style and alternating style. I tried to use EditItemStyle-CssClass, but it didn't help. Here is my code on PageLoad.

 For i As Integer = 0 To grdContingentDevelopment.PageSize - 1
                grdContingentDevelopment.EditIndexes.Add(i)
 Next​

 

[David]

Any help on this edited style question?

[Konstantin Dikov]

Hi David,

There is no alternating style for the edited items and each edit item will receive the same styles (coming from the rgEditRow class). If you need to have different styles for each row you will have to add different classes for each item:

Protected Sub RadGrid1_PreRender(sender As Object, e As EventArgs)
    Dim rowIndex As Integer = 0
    For Each item As GridDataItem In RadGrid1.Items
        item.CssClass = If(System.Math.Max(System.Threading.Interlocked.Increment(rowIndex),rowIndex - 1) Mod 2 = 0, "customEditRow", "customAltEditRow")
    Next
End Sub

And here is the CSS:

<style type="text/css">
    div.RadGrid .customEditRow {
        background: #b6ff00;
    }

    div.RadGrid .customAltEditRow {
        background: #00ff21;
    }
</style>

Best Regards,
Konstantin Dikov
Telerik

[David]

That is step in the right direction however my grid now "jumps" on hover (see video).

Please rename attached file to extension (.avi)

Thank you

[David]

Ideas?

[Konstantin Dikov]

Hello David,

I was able to replicate the issue only when I have enabled the RowHoverStyle of the grid. If that is the case, please include the rgRow and rgAltRow classes when you add your custom CSS classes (or use only them):

item.CssClass = If(System.Math.Max(System.Threading.Interlocked.Increment(rowIndex),rowIndex - 1) Mod 2 = 0, "rgRow customEditRow", "rgAltRow customAltEditRow")

Hope this helps.


Regards,
Konstantin Dikov
Telerik

[David]

Konstantin, that helped!

Rows though look a little "padded". How can i adjust my customEditRow and customAltEditRow classes to make rows slimmer?

 

[Konstantin Dikov]

Hi David,

Using the current implementation you can use a selector for the TD elements with your custom class names and change the padding:

div.RadGrid .customEditRow td,
div.RadGrid .customAltEditRow td {
    padding: 2px;
}

Please note that you can inspect the generated elements and classes and use them for creating your CSS selectors. You should only keep in mind that you will always have to provide a higher priority in order for the new styles to override the default ones.


Regards,
Konstantin Dikov
Telerik

[David]

Beautiful! Thank you so much

[David]

I have a followup question. I am setting my grid to be in edited state by using the following:

item.CssClass = If(System.Math.Max(System.Threading.Interlocked.Increment(rowIndex),rowIndex - 1) Mod 2 = 0, "rgRow customEditRow", "rgAltRow customAltEditRow")

It suits my needs well. Now, how can i add data formatting in edited mode to the following column, please?

<telerik:GridBoundColumn DataField="SameDayMarket_180min" HeaderText="Same-Day Market<br/>( ~ 180 min size)*"
                                                 HeaderStyle-HorizontalAlign="Center"  HeaderStyle-Width="130px"
                                                 FilterControlWidth="100px" ItemStyle-HorizontalAlign="Right" ItemStyle-BackColor="White"
                                                 ItemStyle-Width="130px" ItemStyle-CssClass="label_SS_8" DataFormatString="{0:N0}"/> 

 

[Konstantin Dikov]

Hello David,

I am not sure that I understand what you you are referring to with "edited state" by settings the CSS property, but in regards of the data format of the editor, there is no built-in functionality for settings such format for the generated TextBox control. However, if you need to display numeric values without decimals, you can use GridNumericColumn:

<telerik:GridNumericColumn DataFormatString="{0:N0}" DataField="NumericField" DecimalDigits="0"></telerik:GridNumericColumn>

Hope this helps.


Regards,
Konstantin Dikov
Telerik

[David]

Konstantin,

     I want to display standard number formatting like 1,234,567. However unable in state when all records are ready to be edited.

     See attachment.

Thank you

David

[Viktor Tachev]

Hello David,

The formatting of the numbers would generally depend on the culture that is used. Thus, if you use the settings suggested by Konstantin and the Culture is set to en-US, comma would be added as group separator automatically.


Regards,
Viktor Tachev
Telerik

[David]

Where exactly i shout set Culture to en-US? ​My numbers formatted just fine everywhere other than this page.

Did you try implement Konstantin's recommendation locally?

If you did, can you send me a example that actually working?

[Viktor Tachev]

Hello David,

You can set the Culture to the Page. For convenience I am attaching a sample that is working as expected on my end.

Note that when the columns are in insert or edit mode the number is not formatted in any way. This behavior is by default. You can replicate it with a RadNumericTextBox. While entering the digits there is no formatting applied. The digits are grouped after the control loses focus.

Regards,
Viktor Tachev
Telerik

[David]

Viktor, your example is not helpful. As you can clearly see i use "all records in edit mode" approach recommended by one of your people. Your example adds records one by one. In addition, you are saying "The digits are grouped after the control loses focus", which does not work in my case (see attached picture from my post on 26 Aug.

[Viktor Tachev]

Hi David,

The behavior shown on the screenshot is not replicated on my end. I modified the previous sample so that multiple records can be edited at a time. The numbers are still formatted as expected on my end. Check out the following video for illustration.

• http://screencast.com/t/m25KuADO8

I am also attaching the sample with modifications. Let me know what should be changed in the project in order to replicate the behavior you are seeing.

Regards,
Viktor Tachev
Telerik

[David]

Viktor,

    Your example was a little different than i needed, however it made me to come back to the post ​made by Daniel (Posted 02 Feb 2010 ) in the same thread that i used as a basis for my project. Using his example I ​was able to enable format by setting AutoGenerateColumns="True" and hiding definitions of columns. Can you suggest how can re-enable format in code behind if i set AutoGenerateColumns to "False" and defining columns explicitly, like the following:

 <telerik:GridBoundColumn DataField="SameDayMarket_180min" HeaderText="Same-Day Market<br/>( ~ 180 min size)*"
                                                 HeaderStyle-HorizontalAlign="Center"  HeaderStyle-Width="130px"
                                                 FilterControlWidth="100px" ItemStyle-HorizontalAlign="Right" ItemStyle-BackColor="White"
                                                 ItemStyle-Width="130px" ItemStyle-CssClass="label_SS_8" DataFormatString="{0:N0}"/>  
                       
Thank you

David

[Viktor Tachev]

Hello David,

The sample from my previous post illustrates how you can set the format in the markup of the columns. Since you will be displaying numeric values in the column you can use GridNumericColumn.

<telerik:GridNumericColumn DataField="SameDayMarket_180min" HeaderText="Same-Day Market<br/>( ~ 180 min size)*"
    HeaderStyle-HorizontalAlign="Center"  HeaderStyle-Width="130px"
    FilterControlWidth="100px" ItemStyle-HorizontalAlign="Right" ItemStyle-BackColor="White"
    ItemStyle-Width="130px" ItemStyle-CssClass="label_SS_8" DataFormatString="{0:N0}" DecimalDigits="0"></telerik:GridNumericColumn>

Regards,
Viktor Tachev
Telerik

[David]

Viktor,

   That worked!!!

 Thank you very much!

 David

[David]

Viktor,

   One more item: I added more columns to the grid and now my grid grew above width of the page. All my attempts to reduce columns width through setting don't work..

Just in case i attached firebug view

Thank you

[Daniel]

Hello David,

You can change the control width either programmatically (on ItemCreated as shown in the example below) or using a column editor (you will need GridNumericColumnEditor for the GridNumericColumn shown in your code snippet):
Various Built-in Column Editors
Referencing Controls in Grid Row Edit Form

I hope this helps.

Regards,
Daniel
Telerik

[David]

It did help!

Thank you