Paste from excel in to "Add New" row

9 posts, 0 answers
  1. Bradley
    Bradley avatar
    2 posts
    Member since:
    Aug 2014

    Posted 03 Aug 2014 Link to this post

    Im looking for a way to copy multiple rows from Excel and paste them in to an empty grid.  There doesnt seem to be a way to do this by default?  How would I go about implementing this.  If i add a blank row I can then paste the multiple rows but then the default values for the other columns dont get filled in.

    With a normal grid I used the following code:  This would also add the "Default Values" for the other columns as well.

    private void PasteClipboard()
           {
               char[] rowSplitter = { '\r', '\n' };
               char[] columnSplitter = { '\t' };
     
               // Get the text from clipboard
               IDataObject dataInClipboard = Clipboard.GetDataObject();
               string stringInClipboard = (string)dataInClipboard.GetData(DataFormats.Text);
     
               // Split it into lines
               string[] rowsInClipboard = stringInClipboard.Split(rowSplitter, StringSplitOptions.RemoveEmptyEntries);
     
               // Get the row and column of selected cell in grid
               int r = ColumnsGrid.SelectedCells[0].RowIndex;
               int c = ColumnsGrid.SelectedCells[0].ColumnIndex;
     
               // Add rows into grid to fit clipboard lines
               if (ColumnsGrid.Rows.Count < (r + rowsInClipboard.Length))
               {
                   ColumnsGrid.Rows.Add(r + rowsInClipboard.Length - ColumnsGrid.Rows.Count);
               }
     
               // Loop through the lines, split them into cells and place the values in the corresponding cell.
               for (int iRow = 0; iRow < rowsInClipboard.Length; iRow++)
               {
                   // Split row into cell values
                   string[] valuesInRow = rowsInClipboard[iRow].Split(columnSplitter);
     
                   // Cycle through cell values
                   for (int iCol = 0; iCol < valuesInRow.Length; iCol++)
                   {
                       // Assign cell value, only if it within columns of the grid
                       if (ColumnsGrid.ColumnCount - 1 >= c + iCol)
                       {
                           DataGridViewCell cell = ColumnsGrid.Rows[r + iRow].Cells[c + iCol];
     
                           if (!cell.ReadOnly)
                           {
                               cell.Value = valuesInRow[iCol];
                           }
                       }
                   }
               }
           }
  2. Bradley
    Bradley avatar
    2 posts
    Member since:
    Aug 2014

    Posted 05 Aug 2014 Link to this post

    Any one have ideas?  Odd that this functionality works with the default gridview but I cant replicate with a telerik?
  3. Dess | Tech Support Engineer, Sr.
    Admin
    Dess | Tech Support Engineer, Sr.  avatar
    3891 posts

    Posted 06 Aug 2014 Link to this post

    Hello Bradley,

    Thank you for writing.

    By design, RadGridView is not supposed to perform copy/paste operation inside the new row. However, you can easily achieve it customizing the default paste action in the MasterGridViewTemplate.Paste method:
    public Form1()
    {
        InitializeComponent();
     
        this.radGridView1.Columns.Add("Id");
        this.radGridView1.Columns.Add("Name");
        this.radGridView1.Columns.Add("CreatedOn");
        this.radGridView1.AutoSizeColumnsMode = Telerik.WinControls.UI.GridViewAutoSizeColumnsMode.Fill;
    }
     
    public class CustomGrid : RadGridView
    {
        protected override RadGridViewElement CreateGridViewElement()
        {
            return new CustomRadGridViewElement();
        }
     
        public override string ThemeClassName
        {
            get
            {
                return typeof(RadGridView).FullName;
            }
        }
    }
     
    public class CustomRadGridViewElement : RadGridViewElement
    {
        protected override MasterGridViewTemplate CreateTemplate()
        {
            return new CustomMasterGridViewTemplate();
        }
     
        protected override Type ThemeEffectiveType  
        {
            get 
            {
                return typeof(RadGridViewElement);  
            }
        }
    }
     
    public class CustomMasterGridViewTemplate : MasterGridViewTemplate
    {
        public override void Paste()
        {
            //stop the basic logic
            //base.Paste();
             
            if (Clipboard.ContainsData(DataFormats.Text))
            {
                string data = Clipboard.GetData(DataFormats.Text).ToString();
     
                if (data != string.Empty && this.Owner.CurrentRow is GridViewNewRowInfo)
                {
                    int columnIndex = this.Owner.CurrentColumn.Index;
                    string[] rowsInfo = data.Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries);
                    foreach (string rowInfo in rowsInfo)
                    {
                        string[] cellsInfo = rowInfo.Split(new string[] { "\t" }, StringSplitOptions.RemoveEmptyEntries);
     
                        GridViewRowInfo rowToInsert = this.Owner.Rows.NewRow();
                        for (int i = 0; i < cellsInfo.Length; i++)
                        {
                            rowToInsert.Cells[i + columnIndex].Value = cellsInfo[i];
                        }
                        //default value for the last column
                        rowToInsert.Cells["CreatedOn"].Value = DateTime.Now;
                        this.Owner.Rows.Add(rowToInsert);
                    }
                }
            }
        }
    }

    I would like to note that it is just a sample implementation, which demonstrates the approach. It may not cover all possible cases. Feel free to modify it on a way, which suits your requirement best.

    I hope this information helps. Should you have further questions, I would be glad to help.
     
    Regards,
    Desislava
    Telerik
     
    Check out Telerik Analytics, the service which allows developers to discover app usage patterns, analyze user data, log exceptions, solve problems and profile application performance at run time. Watch the videos and start improving your app based on facts, not hunches.
     
  4. Karl B
    Karl B avatar
    65 posts
    Member since:
    Dec 2013

    Posted 18 Feb 2016 in reply to Dess | Tech Support Engineer, Sr. Link to this post

    Note that if the RadGridView is using a binding source this will cause an exception-

    System.InvalidOperationException occurred
      Message=Items cannot be added to the RadListSource when is in data-bound mode
      Source=Telerik.WinControls
      StackTrace:
           at Telerik.WinControls.Data.RadListSource`1.Add(TDataItem item)

  5. Dess | Tech Support Engineer, Sr.
    Admin
    Dess | Tech Support Engineer, Sr.  avatar
    3891 posts

    Posted 19 Feb 2016 Link to this post

    Hello Karl,

    Thank you for writing.
     
    If the RadGridView is in bound mode, you should insert the new records to the associated DataSource, not to add rows directly in the Rows collection. Here is a sample code snippet:
    BindingSource bs = new BindingSource();
     
    BindingList<Item> items = new BindingList<Item>();
     
    public Form1()
    {
        InitializeComponent();
      
        bs.DataSource = items;
        this.radGridView1.DataSource = bs;
        this.radGridView1.AutoSizeColumnsMode = Telerik.WinControls.UI.GridViewAutoSizeColumnsMode.Fill;
    }
     
    public class Item
    {
        public int Id { get; set; }
     
        public string Name { get; set; }
     
        public Item()
        {
        }
     
        public Item(int id, string name)
        {
            this.Id = id;
            this.Name = name;
        }
    }
     
    public class CustomGrid : RadGridView
    {
        protected override RadGridViewElement CreateGridViewElement()
        {
            return new CustomRadGridViewElement();
        }
     
        public override string ThemeClassName
        {
            get
            {
                return typeof(RadGridView).FullName;
            }
        }
    }
     
    public class CustomRadGridViewElement : RadGridViewElement
    {
        protected override MasterGridViewTemplate CreateTemplate()
        {
            return new CustomMasterGridViewTemplate();
        }
     
        protected override Type ThemeEffectiveType
        {
            get
            {
                return typeof(RadGridViewElement);
            }
        }
    }
     
    public class CustomMasterGridViewTemplate : MasterGridViewTemplate
    {
        public override void Paste()
        {
            //stop the basic logic
            //base.Paste();
            if (Clipboard.ContainsData(DataFormats.Text))
            {
                string data = Clipboard.GetData(DataFormats.Text).ToString();
     
                if (data != string.Empty && this.Owner.CurrentRow is GridViewNewRowInfo)
                {
                    string[] rowsInfo = data.Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries);
                    foreach (string rowInfo in rowsInfo)
                    {
                        string[] cellsInfo = rowInfo.Split(new string[] { "\t" }, StringSplitOptions.RemoveEmptyEntries);
     
                        Item newItem = new Item();
                        newItem.Id = int.Parse(cellsInfo[0]);
                        newItem.Name = cellsInfo[1];
                        BindingSource bs = this.Owner.DataSource as BindingSource;
                        BindingList<Item> items = bs.DataSource as BindingList<Item>;
                        items.Add(newItem);
                    }
                }
            }
        }
    }

    I hope this information helps. Should you have further questions I would be glad to help.
     
    Regards,
    Dess
    Telerik
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  6. fabrizio
    fabrizio avatar
    21 posts
    Member since:
    Aug 2010

    Posted 13 Jul in reply to Dess | Tech Support Engineer, Sr. Link to this post

    Hi Dess
    Thanks a lot for the help, it's just what I was looking for, but it gives me an error in the CustomGrid class in the ThemeClassName property. I use VB.NET and this is the error.

    cannot override 'Public Overridable Overloads Property ThemeClassName As String Property' because they differ in 'ReadOnly' or 'WriteOnly'.

    Please do you know what this means and how can I fix the error?
    Thank you very much
    Fabrizio
  7. Dess | Tech Support Engineer, Sr.
    Admin
    Dess | Tech Support Engineer, Sr.  avatar
    3891 posts

    Posted 13 Jul Link to this post

    Hello, Fabrizio,

    Please have a look at the following code snippet demonstrating how to create a derivative of RadGridView and override its ThemeClassName:

        Public Class CustomGrid
            Inherits RadGridView
            Public Overrides Property ThemeClassName As String
                Get
                    Return GetType(RadGridView).FullName
                End Get
                Set(value As String)
                    MyBase.ThemeClassName = value
                End Set
            End Property
        End Class

    Additional information on this topic is available in the following help article: https://docs.telerik.com/devtools/winforms/telerik-presentation-framework/inherit-themes-from-radcontrols-derivatives 

    I think that it would be helpful to use the free Telerik online converter which convert C# to VB.NET and vice versa: https://converter.telerik.com/ 

    I hope this information helps. If you need any further assistance please don't hesitate to contact me. 

    Regards,
    Dess | Tech Support Engineer, Sr.
    Progress Telerik

  8. fabrizio
    fabrizio avatar
    21 posts
    Member since:
    Aug 2010

    Posted 14 Jul in reply to Dess | Tech Support Engineer, Sr. Link to this post

    Hi Dess

    Thanks a lot for the answer, it's true I have to use get and set in the property.
    I'm doing some tests if I still have some problems can I write without disturbing you?

  9. Dess | Tech Support Engineer, Sr.
    Admin
    Dess | Tech Support Engineer, Sr.  avatar
    3891 posts

    Posted 14 Jul Link to this post

    Hello, Fabrizio, 

    I am glad that the provided information was helpful for you.

    Note that most of the forum threads are reviewed by Telerik representatives and sometimes we address the questions asked by our customers in the forums as well. However, a post in the forum doesn't guarantee you a response from the Telerik support team. Moreover, threads are handled according to license and time of posting, so if it is an urgent problem, we suggest you use a support ticket, which would be handled before a forum thread.

    Thank you for your understanding.

    Regards,
    Dess | Tech Support Engineer, Sr.
    Progress Telerik

Back to Top