DataGridView Copy and Paste

• Download source code - 27.2 KB

Introduction

I regularly have a requirement that my users want to edit their data in Excel; they are after all spreadsheet junkies. I have played around with export/import CSV files, creating Excel templates from within VS and reading in the edited results. None of these seemed to meet my user's requirements.

Background

Recently, one of the users came to me and demonstrated that she could copy the contents of a DataGridView into Excel, and asked why she could not paste the results back into the DataGridView when she had finished editing the data. As I did not realise you could copy the data from the DataGridView, I said I would look into it. Here are the results.

Using the code

Getting the data

First, we need some data - I use MS sample databases for this. I have included some sample data in an XML file for testing. Note that when writing the XML from the datatable, you need to include the schema to be able to read the subsequent file in.

public DataTable GetData()
{
    DataTable oTable = new DataTable();
    FileInfo oFI = new FileInfo("SampleData.xml");

    //test for the existing sample data xml
    if (!oFI.Exists)
    {
        //get the data from the database
        string sSQL = "SELECT SalesPersonID,FirstName," + 
                      "LastName,SalesQuota FROM Sales.vSalesPerson";
        oTable = GetTableSQL(sSQL);

        //you need to write out the schema 
        //with the data to read it into the table.
        oTable.WriteXml(oFI.FullName, XmlWriteMode.WriteSchema);
    }

    //read in the data from the xml file
    oTable.ReadXml(oFI.FullName);

    return oTable;
}

Also, I have split the data retrieval methods into a separate class; this could be another project or a Web Service, and is a basic design principal for client/server. Segregate your UI from the business rules and the data processing layer.

Binding to the DataGridView

Up until recently, I have bound the datatable directly to the DataGridView; ,however I now impose a BindingSource into the mix as it has other benefits not relevant to this article. So, bind the data to the DataGridView. You need to make the columns you do not want edited read only. In this demo, I only want the last column to be editable. In a production environment, I would distinguish these columns so the user knows which columns will not be edited.

private void btnGetData_Click(object sender, EventArgs e)
{
    DataTable oTable = oData.GetData();
    oBS.DataSource = oTable;
    dgData.DataSource = oBS;

    //readonly the columns that cannot be edited
    dgData.Columns[0].ReadOnly = true;
    dgData.Columns[1].ReadOnly = true;
    dgData.Columns[2].ReadOnly = true;
}

I have added a context menu to make it easier to copy and paste the data. I have also implemented ctrl/shift insert/delete in the code-behind using the KeyDown event.

Note that the paste method should be moved to a utility class; this is relevant for the paste method as you do not want to have this code scattered across your UI classes. The copy method is simple, it uses the DataGridView's inbuilt property to get the data and places it in the clipboard.

DataObject d = dgData.GetClipboardContent();
Clipboard.SetDataObject(d);

This allows the user to manipulate the data in Excel. The user can then select any amount of data to paste back into the DataGridView.

Note: It is the user's responsibility to get the paste correct. There is no way to validate the placement of the data on paste although I have some ideas on checking the additional columns pasted to see if they match the cell content.

private void PasteClipboard()
{
    try
    {
        string s = Clipboard.GetText();
        string[] lines = s.Split('\n');
        int iFail = 0, iRow = dgData.CurrentCell.RowIndex;
        int iCol = dgData.CurrentCell.ColumnIndex;
        DataGridViewCell oCell;
        foreach (string line in lines)
        {
            if (iRow < dgData.RowCount && line.Length > 0)
            {
                string[] sCells = line.Split('\t');
                for (int i = 0; i < sCells.GetLength(0); ++i)
                {
                    if (iCol + i < this.dgData.ColumnCount)
                    {
                        oCell = dgData[iCol + i, iRow];
                        if (!oCell.ReadOnly)
                        {
                            if (oCell.Value.ToString() != sCells[i])
                            {
                                oCell.Value = Convert.ChangeType(sCells[i], 
                                                      oCell.ValueType);
                                oCell.Style.BackColor = Color.Tomato;
                            }
                            else
                                iFail++;
                                //only traps a fail if the data has changed 
                                //and you are pasting into a read only cell
                        }
                    }
                    else
                    { break; }
                }
                iRow++;
            }
            else
            { break; }
            if (iFail > 0)
                MessageBox.Show(string.Format("{0} updates failed due" + 
                                " to read only column setting", iFail));
        }
    }
    catch (FormatException )
    {
        MessageBox.Show("The data you pasted is in the wrong format for the cell");
        return;
    }
}

The data that is pasted is tab delimited lines of text. I am not interested in any formulas, only the text from Excel. So, loop through each line and split it on the tab character; one assumes the user has been warned against inserting tab characters into the data in Excel. Check to see if the data has changed from the original by comparing the text from the clipboard and the cell value as text. If the value has changed, then check if the cell is read-only. If all is OK, then try and convert the text data into the same format as the cell information, trapping the format error and halting the paste. Place the data into the cell and change the back colour to inform the user that something happened. Enable the Save button. Note, the data has not yet been saved back to the database, the changes only reside in the datatable supporting the DataGridView. The user still has the opportunity to undo the changes by reloading the data. This demo reloads from the source data, but it could be achieved by cancelling the changes in the datatable.

Saving the data to the database is done by filtering the datatable using a DataView and only getting the changed records. Writing the data to the database is not the object of this demo.

Points of Interest

Writing out the XML schema with the datatable was new to me; I have often used the dataset WriteXML but not the table method.

Trapping the incorrect format error made a huge difference to the usability.

This has not gone into production yet, so I cannot gauge the quality of the data from pasting information into the database. I have the nasty feeling, support is going to get a number of calls along the lines of "I don't know where it came from, it just appeared".

History

• 31/May/2009: Initial release.