Paste Multiple Rows into a DataGridView

So I had a new problem recently that made me really have to work. I had someone who wanted to bulk copy multiple rows into a DataGridView of new data. The catch is the data is data bound. Needless to say I have not seen a data grid behave like that. After a little experimentation and a bunch of research, I have come up with something that while still rough around the edges is pretty good. So lets dig into how to paste multiple rows into a DataGridView.

Setup of the DataGridView

For this example, we are just going to setup a table and have the DataGridView setup a using data bindings. Because this is just a drag and drop sort of operation, we get stuck with some code behind.

First setup a WinForm project. Next setup the data binding to the table. Drag the DataGridView on the form. All the bar stuff will come along for the ride. You will also want to set up a context menu with a copy and paste option, attached to the grid.

Here is a screenshot of the table in SQL Server.

Table Structure

Here is what the GUI will look like.

Finally, we need to have an Excel file with data in it. Here is the data we will enter.

Excel Data

Under normal circumstances, you can only add one row at a time. Because we want to add all of them in a copy and paste sort of way, this won’t do.

If you have trouble with setting this up, let me know in the comments and maybe I will do a post on it in the future.

You’ll want to be careful since we are on the edge case of GUI stuff here, I would love to figure out how to uncode behind the databinding since I run into this method semi frequently in my travels. If you have any suggestion, I would love to hear them in the comments or send me a DM.

The Magic Behind the GUI for how to Paste Multiple Rows into a DataGridView

Because we are trapped by the data binding, we only have the code behind to worry about in this example.

Copy

CopyToClipBoard()

private void CopyToClipboard()
{
    DataObject data = sPDDataGridView.GetClipboardContent();
 
    if (data != null)
    {
        Clipboard.SetDataObject(data);
    }
}

Because we are pasting, we should probably address copying real quick. Here is a function for copying the DataGridView to a WinForm’s DataObject. We check it for being null then assign it to the clipboard. So I hope you enjoyed your freebee because you will get another. I lifted his Copy function, but I hated the paste. It just seemed like it was too complicated for what I was trying to do. With that said, his solution would have worked for me.

Paste

Next we need to deal with the paste function. Because this is a pretty fast and quick solution, there is a definite need for some serious refactoring.

PasteFromClipboard()

private void PasteFromClipboard()
{
    const int columnCount = 5;
 
    List<string> lines = new List<string>();
    lines.AddRange(Clipboard.GetText().Split('\n'));
 
    lines.RemoveAt(lines.Count - 1);
 
    List<string> elements;
    int coulumnCounter = 0;
 
    foreach (string i in lines)
    {
        coulumnCounter = 0;
        elements = new List<string>();
        elements.AddRange(i.Split('\t'));
 
        for (int col = coulumnCounter; col < columnCount; col++)
        {
            elements.Add(string.Empty);
        }
 
        this.sPDDataSet.SPD.AddSPDRow(elements[0].ToString(), elements[1].ToString(), elements[2].ToString(), elements[3].ToString(), elements[4].ToString());
 
    }
}

This one is a bit more complex bits so lets dig into it and unpack what is going on here. First we have to establish how many columns we have in our Excel/Datagrid. This could be handled in a parameter, in a refactor but here it’s a constant.

After that we have our lines list. This will store each line we want to process. Speaking of lines, we get them from getting the text from the clipboard. We break it up by new line character with a Split function. There needs to be logic to kill of the last line since it is blank. We accomplish that with the RemoveAt function.

Because the data is stored in a tabbed delimited fashion we need a collection to store these items. We will use the collection to store these values. Since we might have a disconnect between what is copied and pasted in terms of being shorter, I have a columnCounter, which will track the column number.

Now we process each line one at a time through the foreach loop. We reset our columnCounter for the new line, renew the list to store the elements and add the lines items broken up by tabs.

After all that we go into a for loop to fill out any potential shortfalls in the line with the expected number of columns. If we find any then we add an empty string. If you want to know more about empty strings, read here.

Finally we call the data set being bound to add a row to it.

Didn’t you say something about free stuff?

Because we have a copy and paste, wouldn’t it be nice if we could wire into our datagrid’s so we can use the short cut keys on the keyboard. You want to use Ctrl+C or Ctrl+V to do the code we just created then we need to tell the data grid about it.

While it would make sense to add this to the key down property of the data grid. Don’t do it. Add it to the Key Up. You will be happier.

KeyUp Event

private void sPDDataGridView_KeyUp(object sender, KeyEventArgs e)
{
    if (e.Control && e.KeyCode == Keys.C)
    {
        CopyToClipboard();
    }
    else if (e.Control && e.KeyCode == Keys.V)
    {
        PasteFromClipboard();
    }
}
 

After all this, you should have the ability to paste multiple lines into a DataGridView that is data bound. It is an awful niche thing but I would have killed for a post like this one, this morning.