RadGridView - Pasting from excel

[Charles]

How may I configure RadGridView to allow pasting of multiple rows of data?  I am able to paste over existing selected rows, but when I need to paste more rows than are in the RadGridView it just stops at the last existing row.

This can't be a new requirement, and I imagine there is just some documentation I can't find with my google foo... I found a few very old posts about manually handling pasting from the clipboard, but they were nearly a decade old.

Basically, I'd like to use the RadGridView to manage x, y, z data rows.  It would start empty, and then be able to paste n rows from Excel into the empty grid.  Is this possible?  I was able to do it with the Spreadsheet control, but it's rather slow to load and crashes on occasion when disposing. 

Thanks in advance!

[Dess | Tech Support Engineer, Principal]

Hello, Charles,    

Thank you for writing back. 

Your question has already been answered in the other thread you have opened on the same topic "Pasting new rows from Excel into RadGridView". Please, see our answer there for more information.
We kindly ask you to use just one thread for a specific problem to contact us. Posting the same questions numerous times slows down our response time because we will need to review and address two or more tickets instead of one. 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
Progress Telerik

[Charles]

The generic (assuming the datasource implements IList) solution I came up with which allows you to paste from Excel when the gridview is empty or populated while adding rows if over running existing rows, for anyone googling a solution.  I used CsvHelper to reliably parse the string from the clipboard to a dynamic object.  The properties are them mapped in order when it is possible to convert to the property so that it supports partial columns. I wanted to use AutoMapper for that part, but doing an optimistic index based mapping with it looks to be more of a hassle than just doing it manually.

 

public class CustomGrid : RadGridView
{
    protected override RadGridViewElement CreateGridViewElement()
    {
        return new CustomRadGridViewElement();
    }

    public override string ThemeClassName => typeof(RadGridView).FullName;
}

public class CustomRadGridViewElement : RadGridViewElement
{
    protected override MasterGridViewTemplate CreateTemplate()
    {
        return new CustomMasterGridViewTemplate();
    }

    protected override Type ThemeEffectiveType => typeof(RadGridViewElement);
}

public class CustomMasterGridViewTemplate : MasterGridViewTemplate
{
    public static bool CanChangeType(object value, Type conversionType)
    {
        if (conversionType == null)
        {
            return false;
        }

        if (value == null)
        {
            return false;
        }

        IConvertible convertible = value as IConvertible;

        if (convertible == null)
        {
            return false;
        }

        return true;
    }

    public override void Paste()
    {
        if (Clipboard.ContainsData(DataFormats.Text))
        {
            var dataSource = this.Owner.DataSource as IList;
            if (dataSource != null)
            {
                string data = Clipboard.GetData(DataFormats.Text).ToString();
                if (!string.IsNullOrWhiteSpace(data))
                {
                    using (TextReader reader = new StringReader(data))
                    {
                        var csv = new CsvReader(reader, new Configuration(){HasHeaderRecord = false, Delimiter = "\t"});
                        IEnumerable<object> records = null;
                        var type = dataSource.GetType().GetGenericArguments().Single();
                        try
                        {
                            records = csv.GetRecords<dynamic>().ToList();
                            var count = records.Count();
                        }
                        catch { }

                        if (records != null)
                        {
                            var rowIndex = 0;
                            if (this.Owner.SelectedCells.Any())
                                rowIndex = this.Owner.SelectedCells.Select(c => c.RowInfo.Index).Min();
                            if (this.Owner.SelectedRows.Any())
                                rowIndex = this.Owner.SelectedRows.Select(r => r.Index).Min();
                            if (rowIndex == -1)
                                rowIndex = this.Owner.RowCount;

                            foreach (var record in records)
                            {
                                if (dataSource.Count == rowIndex)
                                {
                                    dataSource.Add(Activator.CreateInstance(type));
                                }
                                var propertyValues = (IDictionary<string, object>)record;
                                var properties = dataSource[rowIndex].GetType().GetProperties();
                                var propertyIndex = 0;
                                foreach (var propertyValue in propertyValues)
                                {
                                    while (!CanChangeType(propertyValue.Value, properties[propertyIndex].PropertyType) && propertyIndex < properties.Length)
                                    {
                                        propertyIndex++;
                                    }

                                    if (propertyIndex < properties.Length)
                                    {
                                        var value = Convert.ChangeType(propertyValue.Value, properties[propertyIndex].PropertyType);
                                        properties[propertyIndex].SetValue(dataSource[rowIndex], value);
                                    }
                                }
                                rowIndex++;
                            }
                            this.Refresh();
                            return;
                        }
                    }
                }
            }
        }
        base.Paste();
    }
}

 

 

 

 

[Charles]

Missed a line in the paste, but this should be used for the value assignment.  The above missed the propertyIndex++ after writing the value.  

 

if (propertyIndex < properties.Length)
{
    var value = Convert.ChangeType(propertyValue.Value, properties[propertyIndex].PropertyType);
    properties[propertyIndex].SetValue(dataSource[rowIndex], value);
    propertyIndex++;
}

[Dess | Tech Support Engineer, Principal]

Hello, Charles,    

Thank you for writing back. 

I am glad that the suggested approach in the support thread that you have on the same topic was useful. Thank you for sharing your solution with the community and someone can benefit from it.

If you have any additional questions, please let me know. 

 Regards,
Dess
Progress Telerik