Paste from excel in to "Add New" row

[Bradley]

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];
                       }
                   }
               }
           }
       }

[Bradley]

Any one have ideas?  Odd that this functionality works with the default gridview but I cant replicate with a telerik?

[Dess | Tech Support Engineer, Principal]

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

 

 

[Karl B]

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)

[Dess | Tech Support Engineer, Principal]

Hello Karl,

Thank you for writing.
 
If the RadGridView is 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

[fabrizio]

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

[Dess | Tech Support Engineer, Principal]

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

[fabrizio]

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?

[Dess | Tech Support Engineer, Principal]

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