Paste from excel in to "Add New" row

5 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. UI for WinForms is Visual Studio 2017 Ready
  4. Dess
    Admin
    Dess avatar
    1601 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.
     
  5. Karl
    Karl avatar
    35 posts
    Member since:
    Dec 2013

    Posted 18 Feb in reply to Dess 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)

  6. Dess
    Admin
    Dess avatar
    1601 posts

    Posted 19 Feb 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
Back to Top
UI for WinForms is Visual Studio 2017 Ready