Click here to Skip to main content
Click here to Skip to main content

DataGridView Copy and Paste

, 31 May 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
Copy and paste between a DataGridView and Excel.

BeforeCopy.jpg

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.

EditInExcel.jpg

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.

AfterPaste.jpg

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Mycroft Holmes
Software Developer (Senior) Contractor
Singapore Singapore
Started my programming life writing Excel 1.0 macros, God what a long time ago.
 
Now I'm a dotnet developer, I get to influence direction, play with new toys, build stuff, life is wonderful.
 
Greatest buzz you can get, walk past a row of desks and see your application running on all of them (and getting paid).
 
Greatest irritant, pouring 12 months of knowledge and experience into an empty head only to have it leave.

Comments and Discussions

 
Questionpaste to Excel any other Encoding ? Pinmembermhsmity1-Sep-14 2:34 
Questionmycroft holmes???? Pinmemberarpanworld7-May-13 14:29 
SuggestionHandling Boolean PinmemberJepy12-Sep-12 8:06 
QuestionAdd new Rows to bindingsource if there are not enough rows? PinmemberFlorian Hackl5-Jul-12 4:27 
GeneralRe: Add new Rows to bindingsource if there are not enough rows? [modified] PinmemberDr.Wummi5-Jul-12 22:54 
GeneralRe: Add new Rows to bindingsource if there are not enough rows? PinmemberMycroft Holmes7-Jul-12 14:26 
QuestionThanks and pasting suggestions Pinmembermvogel19-Apr-12 7:20 
AnswerRe: Thanks and pasting suggestions PinmemberMycroft Holmes7-Jul-12 14:30 
GeneralMy vote of 5 PinmemberPaartha16-Apr-12 1:11 
GeneralMy vote of 5 Pinmembermanoj kumar choubey27-Mar-12 21:29 
QuestionProblem with Editing Mode Pinmembermurx28-Jan-12 1:00 
AnswerRe: Problem with Editing Mode PinmemberMycroft Holmes28-Jan-12 13:17 
QuestionAwsome! Pinmemberreeselmiller210-Jan-12 16:58 
AnswerRe: Awsome! PinmemberMycroft Holmes10-Jan-12 19:16 
GeneralMy vote of 5 Pinmemberjp2code20-Jun-11 9:45 
GeneralRe: My vote of 5 Pinmemberpianocomposer14-Oct-11 5:57 
GeneralThanks again PingroupYZK29-Mar-11 23:46 
GeneralThanks PingroupYZK29-Mar-11 22:46 
GeneralRe: Thanks PinmemberMycroft Holmes29-Mar-11 23:07 
QuestionHow to copy the excel formula into datagridview PinmemberVijay25429-Feb-11 22:46 
AnswerRe: How to copy the excel formula into datagridview PinmemberMycroft Holmes9-Feb-11 23:58 
GeneralOne Problem and One Observation PinmemberMember 46890861-Sep-10 14:42 
GeneralRe: One Problem and One Observation PinmvpMycroft Holmes1-Sep-10 14:59 
GeneralAnother little bug Pinmemberbmegli3-Aug-10 7:27 
GeneralLittle bug Pinmemberdiemesa8-Jun-09 8:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.141015.1 | Last Updated 31 May 2009
Article Copyright 2009 by Mycroft Holmes
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid