Click here to Skip to main content
15,867,594 members
Articles / Desktop Programming / Windows Forms

DataGridView Copy and Paste

Rate me:
Please Sign up or sign in to vote.
4.75/5 (33 votes)
31 May 2009CPOL4 min read 320.1K   17.5K   97   37
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.

C#
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.

C#
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.

C#
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

C#
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)


Written By
Retired None
Australia Australia
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.

And now I'm retired, no deadlines, no meetings, no managers (except the ONE) and no users!

Comments and Discussions

 
PraiseFreezed Column Pin
Nyoman Rahmadi14-Feb-19 4:03
Nyoman Rahmadi14-Feb-19 4:03 
QuestionPasting excel data into multiple datagridview Pin
saculrm27-Oct-16 21:39
saculrm27-Oct-16 21:39 
AnswerRe: Pasting excel data into multiple datagridview Pin
Mycroft Holmes28-Oct-16 0:37
professionalMycroft Holmes28-Oct-16 0:37 
GeneralRe: Pasting excel data into multiple datagridview Pin
saculrm30-Oct-16 22:55
saculrm30-Oct-16 22:55 
QuestionPaste into all selected cells Pin
emilius9420-Jun-16 21:26
emilius9420-Jun-16 21:26 
AnswerRe: Paste into all selected cells Pin
Mycroft Holmes21-Jun-16 1:29
professionalMycroft Holmes21-Jun-16 1:29 
Generalthanks! really helpful! Pin
Daniel Brousser16-Apr-15 6:50
Daniel Brousser16-Apr-15 6:50 
GeneralRe: thanks! really helpful! Pin
Mycroft Holmes16-Apr-15 12:35
professionalMycroft Holmes16-Apr-15 12:35 
QuestionReally helped. Thanks so much for posting this! Pin
Member 1055846617-Mar-15 5:40
Member 1055846617-Mar-15 5:40 
QuestionProblen Pin
Yogendra Gupta16-Dec-14 1:20
Yogendra Gupta16-Dec-14 1:20 
AnswerRe: Problen Pin
Mycroft Holmes16-Dec-14 11:36
professionalMycroft Holmes16-Dec-14 11:36 
Questionpaste to Excel any other Encoding ? Pin
mhsmity1-Sep-14 2:34
professionalmhsmity1-Sep-14 2:34 
Questionmycroft holmes???? Pin
arpanworld7-May-13 14:29
arpanworld7-May-13 14:29 
SuggestionHandling Boolean Pin
Jepy12-Sep-12 8:06
Jepy12-Sep-12 8:06 
QuestionAdd new Rows to bindingsource if there are not enough rows? Pin
Dr.Wummi5-Jul-12 4:27
Dr.Wummi5-Jul-12 4:27 
GeneralRe: Add new Rows to bindingsource if there are not enough rows? Pin
Dr.Wummi5-Jul-12 22:54
Dr.Wummi5-Jul-12 22:54 
GeneralRe: Add new Rows to bindingsource if there are not enough rows? Pin
Mycroft Holmes7-Jul-12 14:26
professionalMycroft Holmes7-Jul-12 14:26 
QuestionThanks and pasting suggestions Pin
mvogel19-Apr-12 7:20
mvogel19-Apr-12 7:20 
AnswerRe: Thanks and pasting suggestions Pin
Mycroft Holmes7-Jul-12 14:30
professionalMycroft Holmes7-Jul-12 14:30 
Thanks for the added info, I put this together to meet a specific requirement, I didn't try and cover edge cases.

Because of the way I work I would never allow insert by pasting, this was designed to allow the users to grab the data and perform calcs on the numbers only, never to create records.
Never underestimate the power of human stupidity
RAH

GeneralMy vote of 5 Pin
Paartha16-Apr-12 1:11
Paartha16-Apr-12 1:11 
GeneralMy vote of 5 Pin
Manoj Kumar Choubey27-Mar-12 21:29
professionalManoj Kumar Choubey27-Mar-12 21:29 
QuestionProblem with Editing Mode Pin
murx28-Jan-12 1:00
murx28-Jan-12 1:00 
AnswerRe: Problem with Editing Mode Pin
Mycroft Holmes28-Jan-12 13:17
professionalMycroft Holmes28-Jan-12 13:17 
QuestionAwsome! Pin
reeselmiller210-Jan-12 16:58
reeselmiller210-Jan-12 16:58 
AnswerRe: Awsome! Pin
Mycroft Holmes10-Jan-12 19:16
professionalMycroft Holmes10-Jan-12 19:16 

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

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