Manish Dalal's blog

Exploring .net!

Cross Browser Copy and Paste in DataGrid with Excel support – Part 1

Silverlight 2 is a cross browser platform(plug-in), providing developers with a familiar .net programming model for building RIAs. However it is also a relatively young platform. This came to surface other day when a tester came to me complaining about our new Silverlight enabled web application. The tester was not happy, since he could not copy data from Silverlight DataGrid to Excel. He could copy data from rest of the application (a traditional asp.net web application) except from the Silverlight module!

So I decided to look into providing the standard clipboard functionality. This post chronicles my attempt to build Copy/Paste functionality, road blocks that I encountered to provide cross browser functionality and the ultimate solution that provides reusable clipboard functionality in multiple browsers, all within Silverlight!

Disclaimer: Clipboard functionality described here only works as long you are working with text data. Also I only tested in Internet Explorer 7 (Windows Vista SP1), Firefox 3.03(Windows Vista SP1 and Mac OS X 10.5.5) and Google Chrome 0.3.154.9(Windows Vista SP1). If you have resources to test on other browsers/ operating systems, please let me know how it works in other browsers.Person People Class Diagram

Setup

Create a new SilverlightApplication and the corresponding Web Application to host and test the SilverlightApplication. I will reuse the Person and People object model from my previous series on Building Business Application. For simplicity, I have removed all validation, and replaced with a simple rule where LastName and City fields are required. (If you will like to explore detail validation, please see my various posts on validation here and here).

Basic DataGrid

Add Person and People classes as shown

Person.cs

public class Person : INotifyPropertyChanged, IEditableObject {
 
    #region Constructors
    public Person() {
    }
 
    public Person(string firstName, string lastName, int age, string city): this() {
        this._firstName = firstName;
        this._lastName = lastName;
        this._age = age;
        this._city = city;
    }
    #endregion
 
    #region Properties
    private string _firstName;
    public string FirstName {
        get { return _firstName; }
        set {
            if (value == _firstName) return;
            _firstName = value;
            RaisePropertyChanged("FirstName");
        }
    }
 
    private string _lastName;
    public string LastName {
        get { return _lastName; }
        set {
            if (value == _lastName) return;
            _lastName = value;
            RaisePropertyChanged("LastName");
        }
    }
 
    private int _age;
    public int Age {
        get { return _age; }
        set {
            if (value == _age) return;
            if (value < 0 || value > 200) {
                throw new Exception("Age must be between 0 and 200");
            } 
            _age = value;
            RaisePropertyChanged("Age");
        }
    }
 
    private string _city;
    public string City {
        get { return _city; }
        set {
            if (value == _city) return;
            _city = value;
            RaisePropertyChanged("City");
        }
    }
    #endregion
 
    #region INotifyPropertyChanged Members
    public event PropertyChangedEventHandler PropertyChanged;
 
    protected void OnPropertyChanged(string name) {
        if (PropertyChanged != null)
            PropertyChanged(this, new PropertyChangedEventArgs(name));
    }
 
    internal void RaisePropertyChanged(string name) {
            OnPropertyChanged(name);
    }
    #endregion
 
    #region IEditableObject Members
    Person _backup;
    bool _editing;
 
    public void BeginEdit() {
        if (!_editing) {
            _editing = true;
            _backup = this.MemberwiseClone() as Person;
        }
    }
 
    public void CancelEdit() {
        if (_editing) {
            FirstName = _backup.FirstName;
            LastName = _backup.LastName;
            Age = _backup.Age;
            City = _backup.City;
            _editing = false;
        }
    }
 
    public void EndEdit() {
        if (_editing) {
            _editing = false;
            this._backup = null;
            if (null != ChangesCommitted) {
                if (!string.IsNullOrEmpty(LastName) && !string.IsNullOrEmpty(City)) {
                    Application.Current.RootVisual.Dispatcher.BeginInvoke(() => OnChangesCommitted());
                }
            }
        }
    }
 
    public event EventHandler<EventArgs> ChangesCommitted;
    protected void OnChangesCommitted() {
        if (null != ChangesCommitted) {
            ChangesCommitted(this, new EventArgs());
        }
    }
    #endregion
 
    #region Clipboard Helper methods
    //public override string ToString() {
    //    return FirstName + " " + LastName;
    //}
 
    //public string ToString(string format) {
    //    if (format.Equals("Copy")) {
    //        return FirstName
    //            + "\t" + LastName
    //            + "\t" + Age
    //            + "\t" + City;
    //    }
    //    return ToString();
    //}
 
    //public static Person Create(string[] dataFields){
    //    return new Person(dataFields[(int)Fields.FirstName]
    //        , dataFields[(int)Fields.LastName]
    //        , int.Parse(dataFields[(int)Fields.Age])
    //        , dataFields[(int)Fields.FirstName]);
    //}
    //public static string[] GetDataFields(Person person) {
    //    return new string[] { person.FirstName, person.LastName, person.Age.ToString(), person.City };
    //}
 
    //public enum Fields {
    //    FirstName,
    //    LastName,
    //    Age,
    //    City
    //}
 
    public Person Clone() {
        // for demo only, please deep clone
        return (Person)this.MemberwiseClone();
    }
    #endregion
}

People.cs

public class People : ObservableCollection<Person> {
    public static People GetTestData() {
        return new People() {
            new Person("Homer", "Simpson", 38, "Springfield"),
            new Person("Marge", "Simpson", 33, "Springfield"),
            new Person("Bart", "Simpson", 8, "Springfield")
        };
    }
 
    private Person emptyPerson;
 
    public People() {
        emptyPerson = new Person();
        emptyPerson.ChangesCommitted += new EventHandler<EventArgs>(emptyPerson_ChangesCommitted);
        base.InsertItem(this.Count, emptyPerson);
    }
 
    void emptyPerson_ChangesCommitted(object sender, EventArgs e) {
        emptyPerson.ChangesCommitted -= new EventHandler<EventArgs>(emptyPerson_ChangesCommitted);
        emptyPerson = new Person();
        emptyPerson.ChangesCommitted += new EventHandler<EventArgs>(emptyPerson_ChangesCommitted);
        base.InsertItem(this.Count, emptyPerson);
    }
 
    protected override void InsertItem(int index, Person item) {
        if (index >= this.Count) {
            index = this.Count - 1;
            if (index < 0) index = 0;
        }
        base.InsertItem(index, item);
    }
 
    protected override void RemoveItem(int index) {
        Person personToRemove = this[index] as Person;
        if (emptyPerson != personToRemove) {
            base.RemoveItem(index);
        }
    }
 
    public Person SelectedPerson { get; set; }
 
    public bool IsEmptyPerson(Person person){
        return (emptyPerson == person);
    }
 
    internal Person GetPersonForPaste() {
        Person newPerson = new Person();
        if (!string.IsNullOrEmpty(emptyPerson.LastName) && !string.IsNullOrEmpty(emptyPerson.City)) {
            base.InsertItem(this.Count, newPerson);
        } else {
            base.Add(newPerson);
        }
        return newPerson;
    }
}

Next add DataGrid to Page.xaml and setup DataContext to display test data. Add reference to System.Windows.Controls.Data and following xaml to Page.xaml

<UserControl x:Class="SilverlightApplication.Page"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" 
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" 
    xmlns:data="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data"
    >
    <Grid x:Name="LayoutRoot" Background="White">
        <data:DataGrid x:Name="peopleDataGrid" AutoGenerateColumns="False"
            Margin="10" RowHeight="22"
            ItemsSource="{Binding}" SelectedItem="{Binding SelectedPerson,Mode=TwoWay}" >
            <data:DataGrid.Columns>
                <data:DataGridTextColumn Header="First Name" Binding="{Binding FirstName,Mode=TwoWay}" />
                <data:DataGridTextColumn Header="Last Name" Binding="{Binding LastName,Mode=TwoWay}" />
                <data:DataGridTextColumn Header="Age" Binding="{Binding Age,Mode=TwoWay}" />
                <data:DataGridTextColumn Header="City" Binding="{Binding City,Mode=TwoWay}" />
            </data:DataGrid.Columns>
        </data:DataGrid>
    </Grid>
</UserControl>

Note that peopleDataGrid’s ItemSource is set to Binding, which will bind to DataContext. Also SelectedItem is bound to SelectedPerson on People. Add following code to Page.xaml.cs to setup DataContext

public partial class Page : UserControl {
    People _data;

    public Page() {
        InitializeComponent();
        this.Loaded += new RoutedEventHandler(Page_Loaded);
    }

    void Page_Loaded(object sender, RoutedEventArgs e) {
        _data = People.GetTestData();
        DataContext = _data;
    }
}

F5 and test the application.

image

Adding Private Copy Paste Functionality

To provide DataGrid scoped Copy and Paste functionality, we will subscribe to KeyDown event and look for appropriate Key combinations

public Page() {
    InitializeComponent();
    this.Loaded += new RoutedEventHandler(Page_Loaded);
    peopleDataGrid.KeyDown += new KeyEventHandler(peopleDataGrid_KeyDown);
}

Person _copyFromPerson;
void peopleDataGrid_KeyDown(object sender, KeyEventArgs e) {
    if (peopleDataGrid != e.OriginalSource) {
        return;
    }
    // Copy uisng Ctrl-C
    if (e.Key == Key.C &&
        ((Keyboard.Modifiers & ModifierKeys.Control) == ModifierKeys.Control
        || (Keyboard.Modifiers & ModifierKeys.Apple) == ModifierKeys.Apple)
        ) {
        _copyFromPerson = _data.SelectedPerson.Clone();
    }
    // Paste using Ctrl-V
    else if (e.Key == Key.V &&
        ((Keyboard.Modifiers & ModifierKeys.Control) == ModifierKeys.Control
        || (Keyboard.Modifiers & ModifierKeys.Apple) == ModifierKeys.Apple)
        ) {
        if (null == _copyFromPerson ) {
            Dispatcher.BeginInvoke(() => MessageBox.Show("Please select a Person to copy from"));
            return;
        }
        Person pasteToPerson = _data.SelectedPerson;
        pasteToPerson.BeginEdit();
        pasteToPerson.FirstName = _copyFromPerson.FirstName;
        pasteToPerson.LastName = _copyFromPerson.LastName;
        pasteToPerson.Age = _copyFromPerson.Age;
        pasteToPerson.City = _copyFromPerson.City;
        pasteToPerson.EndEdit();
    } else if (e.Key == Key.Escape) {
        _copyFromPerson = null;
    }
}

When user presses Ctrl-C to copy, we clone currently selected item and save it to a private variable. Later when user presses Ctrl-V to paste, we copy data from previously stored private variable to currently selected item.

You can use above to provide DataGrid scoped copy/paste functionality in all browsers. You can also extend functionality to work with multiple DataGrids, as long as they are all in the same application.

Excel Support

In order to provide copy/paste support to/from Excel, we need to get to data that is stored in Clipboard. Start Excel and enter following in four cells (Lisa, Simpson, 5, Springfield)

image

Select row and copy to notepad. Note that data is tab separated. This is the default clipboard format for Excel. You can also go other way, create a tab separated values in notepad and paste it into different cells in Excel. We will use same format to copy and paste data from Silverlight DataGrid to Excel and vice-versa.

Adding Internet Explorer Only functionality using Clipboard object

Internet Explorer provides access to ClipboardData object and we can get to it using Silverlight HtmlBridge functionality

Modify KeyDown as shown

   1: void peopleDataGrid_KeyDown(object sender, KeyEventArgs e) {
   2:     if (peopleDataGrid != e.OriginalSource) {
   3:         return;
   4:     }
   5:     // Copy uisng Ctrl-C
   6:     if (e.Key == Key.C &&
   7:         ((Keyboard.Modifiers & ModifierKeys.Control) == ModifierKeys.Control
   8:         || (Keyboard.Modifiers & ModifierKeys.Apple) == ModifierKeys.Apple)
   9:         ) {
  10:         Person copyFromPerson = _data.SelectedPerson;
  11:         string textData = copyFromPerson.FirstName + "\t" + copyFromPerson.LastName 
  12:                        + "\t" + copyFromPerson.Age + "\t" + copyFromPerson.City;
  13:         ScriptObject clipboardData = (ScriptObject)HtmlPage.Window.GetProperty("clipboardData");
  14:         if (clipboardData != null) {
  15:             bool success = (bool)clipboardData.Invoke("setData", "text", textData);
  16:         } else {
  17:             Dispatcher.BeginInvoke(() => MessageBox.Show("Sorry, this functionality is only avaliable in Internet Explorer."));
  18:             return;
  19:         }
  20:     }
  21:     // Paste using Ctrl-V
  22:     else if (e.Key == Key.V &&
  23:         ((Keyboard.Modifiers & ModifierKeys.Control) == ModifierKeys.Control
  24:         || (Keyboard.Modifiers & ModifierKeys.Apple) == ModifierKeys.Apple)
  25:         ) {
  26:         ScriptObject clipboardData = (ScriptObject)HtmlPage.Window.GetProperty("clipboardData");
  27:         if (null == clipboardData) {
  28:             Dispatcher.BeginInvoke(() => MessageBox.Show("Sorry, this functionality is only avaliable in Internet Explorer."));
  29:             return;
  30:         }
  31:         string textData = null;
  32:         if (clipboardData != null) {
  33:             textData = (string)clipboardData.Invoke("getData", "text");
  34:         } 
  35:         Person copyFromPerson = null;
  36:         string[] rows = textData.Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
  37:         if (0 != rows.Length) {
  38:             string[] fields = rows[0].Split(new string[] { "\t" }, StringSplitOptions.None);
  39:             if (4 == fields.Length) {
  40:                 copyFromPerson = new Person(fields[0], fields[1], int.Parse(fields[2]), fields[3]);
  41:             }
  42:         }
  43:         if (null == copyFromPerson) {
  44:             Dispatcher.BeginInvoke(() => MessageBox.Show("Please select a Person to copy from"));
  45:             return;
  46:         }
  47:         Person pasteToPerson = _data.SelectedPerson;
  48:         pasteToPerson.BeginEdit();
  49:         pasteToPerson.FirstName = copyFromPerson.FirstName;
  50:         pasteToPerson.LastName = copyFromPerson.LastName;
  51:         pasteToPerson.Age = copyFromPerson.Age;
  52:         pasteToPerson.City = copyFromPerson.City;
  53:         pasteToPerson.EndEdit();
  54:     } 
  55: }
 
For Ctrl-C, we first build tab separated list of person fields and next use clipboardata to setData to clipboard via Invoke. Conversely on Ctrl-V, we use clipboardData to getdata and parse data using tab into array of fields. That array of fields is used to build private copy person that is used to copy data into currently selected item. To test functionality, Copy Data from Excel and paste it into empty row in DataGrid. New Person (Lisa) is added to DataGrid. Next select first row (Homer) in DataGrid and Paste it into Excel.

Adding Cross Browser functionality image

Access to ClipboardData is limited to Internet Explorer only. If you try to run code in FireFox, clipboardData returns null. However, I did not want to tell my users that copy/paste functionality is only available in Internet Explorer. (Specially after having told them about Silverlight Cross Browser advantage!). So I decided to do some search. I found couple of approaches that use Flash and/or JavaScript, but did not come up with a Silverlight only solution.

If you play around with Silverlight, you will notice that TextBox control provides Copy and Paste functionality in multiple browsers. So I fired up Reflector and tried to see what TextBox was doing. Alas, I quickly ran into brick wall. It calls into underlying system. That was not going to work for our Transparent code. However that got me thinking… If DataGrid will not support, can we use TextBox as a helper proxy? It turns out you can! Instead of trying to use Internet Explorer specific ClipboardData object, just delegate task to TextBox. TextBox does the heavy lifting and interacts with clipboard in multiple browsers.

Add new class call ClipboardTextBox.cs as shown

public class ClipboardTextBox  : TextBox{
    public ClipboardTextBox() {
        AcceptsReturn = true;
    }
    protected override void OnKeyDown(KeyEventArgs e) {
        base.OnKeyDown(e);
    }
    public void ProcessKeyDown(KeyEventArgs e) {
        OnKeyDown(e);
    }
}

Next add ClipboardTextBox control to Page.xaml

<src:ClipboardTextBox x:Name="dataTextBox" Visibility="Collapsed"/>

Also add src as xml namespace declaration to UserControl start tag

xmlns:src="clr-namespace:SilverlightApplication"

Modify KeyDown as shown

void peopleDataGrid_KeyDown(object sender, KeyEventArgs e) {
    if (peopleDataGrid != e.OriginalSource) {
        return;
    }
    // Copy uisng Ctrl-C
    if (e.Key == Key.C &&
        ((Keyboard.Modifiers & ModifierKeys.Control) == ModifierKeys.Control
        || (Keyboard.Modifiers & ModifierKeys.Apple) == ModifierKeys.Apple)
        ) {
        Person copyFromPerson = _data.SelectedPerson;
        string textData = copyFromPerson.FirstName + "\t" + copyFromPerson.LastName
                       + "\t" + copyFromPerson.Age + "\t" + copyFromPerson.City;
        dataTextBox.Text = textData.ToString();
        dataTextBox.SelectAll();
        dataTextBox.ProcessKeyDown(e);
    }
    // Paste using Ctrl-V
    else if (e.Key == Key.V &&
        ((Keyboard.Modifiers & ModifierKeys.Control) == ModifierKeys.Control
        || (Keyboard.Modifiers & ModifierKeys.Apple) == ModifierKeys.Apple)
        ) {
        dataTextBox.Text = string.Empty;
        dataTextBox.ProcessKeyDown(e);
        string textData = dataTextBox.Text;
        Person copyFromPerson = null;
        string[] rows = textData.Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
        if (0 != rows.Length) {
            string[] fields = rows[0].Split(new string[] { "\t" }, StringSplitOptions.None);
            if (4 == fields.Length) {
                copyFromPerson = new Person(fields[0], fields[1], int.Parse(fields[2]), fields[3]);
            }
        }
        if (null == copyFromPerson) {
            Dispatcher.BeginInvoke(() => MessageBox.Show("Please select a Person to copy from"));
            return;
        }
        Person pasteToPerson = _data.SelectedPerson;
        pasteToPerson.BeginEdit();
        pasteToPerson.FirstName = copyFromPerson.FirstName;
        pasteToPerson.LastName = copyFromPerson.LastName;
        pasteToPerson.Age = copyFromPerson.Age;
        pasteToPerson.City = copyFromPerson.City;
        pasteToPerson.EndEdit();
    }
}

Code above is similar to one for Internet Explorer, except for replacement of dataTextBox for clipboardData. For Ctrl-C, we set data into TextBox, select all text and pass KeyEventArgs to TextBox, which results in data being copied to clipboard. For Ctrl-V, we clear TextBox, process KeyDown and read Text to get paste data to process. F5 and run the application. Start FireFox. Copy data from Excel, Paste it into FireFox. Try other way around, copy data from Silverlight DataGrid and paste it into Excel.

image image

We now have a basic (one row) copy and paste functionality that is 100% Silverlight and cross browser. Lets refactor code to create a reusable ClipboadHelper class.

ClipboardHelper

ClipboardHelper provides methods to Get and Set clipboard data that works in multiple browsers. It internally uses an instance of ClipboardTextBox to carry out actual operations. Here is code for ClipboardHelper.cs

public static class ClipboardHelper {

    private static ClipboardTextBox dataTextBox;

    static ClipboardHelper() {
        dataTextBox = new ClipboardTextBox();
    }

    public static void SetData(KeyEventArgs e, string textData) {
        dataTextBox.Text = textData;
        dataTextBox.SelectAll();
        dataTextBox.ProcessKeyDown(e);
    }

    public static string GetData(KeyEventArgs e) {
        dataTextBox.Text = string.Empty;
        dataTextBox.ProcessKeyDown(e);
        return dataTextBox.Text;
    }
}

Usage:
In order to incorporate cross browser clipboard functionality, just add reference to MD.Silverlight.Utilities and call methods on ClipboardHelper class

To copy data, handle KeyDown event with key combinations (Ctrl-C) and call

// set copy data
ClipboardHelper.SetData(e, textData);

To past data, handle KeyDown event with key combinations (Ctrl-V) and call

// get paste data
string textData = ClipboardHelper.GetData(e);

Hopefully ClipboardHelper will enable you to provide cross browser clipboard functionality in Silverlight till copy paste functionality is built into the base framework. In the next post I will extend functionality to support multiple rows and introduce reusable DataGridCopyPasteService, that imparts copy paste functionality to any DataGrid.

Source Code: CopyPaste.zip

Note: Source code includes enhanced demo application with 4 tabs and a reusable MD.Silverlight.Utilities.dll class library.

CopyPasteApp1 

Tab1 show cases for private copy paste functionality

Tab2 show cases Internet Explorer specific copy paste functionality

Tab3 show cases usage of ClipboardTextBox for copy paste functionality

Tab4 show cases usage of ClipboardHelper for cross browser reusable copy paste functionality

 

 

 

Technorati Tags:

Comments

Syed Mehroz Alam said:

A very useful post. Great work, Manish.

# November 13, 2008 12:37 AM

manor said:

Thanks manish! Can you please include the downloadable code?

# December 5, 2008 11:12 PM

Jessy said:

HI,

   Your post is very informative, could you please also explain how to modify clipboardhelper and clipboardtextbox class when we want to paste data with the help of a button and not a key event??

# February 12, 2009 5:09 PM

Ronak Patel said:

Sir what to do when i want to copy more than one row from excel and paste them in datagrid?

Right Now,if i try to copy more than one row from excel to datagrid,then only the last selected row will be pasted in Datagrid.

Reply Please

# November 21, 2009 4:10 AM

Kam said:

Thanks for the post very useful. I do have one question, if I put a button in my Silverlight application to "Copy" a text, how can I pass the click event to a KeyEventArgs and use your code after that.

# March 7, 2010 3:58 PM