Paste to Excel From DataGridView

Question

I would like to copy a region of cells from a dataGridView and then paste that data into MS Excel. I am able to copy data and paste into MS Word or Notepad but not excel. There are lots of examples of copying from Excel and Pasting into a DataGridView but not the other way around.

private void frm_KeyDown(object sender, KeyEventArgs e)
{
    if (e.Control && e.KeyCode == Keys.C)
    {
        this.dataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableWithoutHeaderText;

        if (this.dataGridView1.GetCellCount(DataGridViewElementStates.Selected) > 0)
        {
            try
            {
                DataObject d = dataGridView1.GetClipboardContent();
                Clipboard.SetDataObject(d);
            }

            catch (System.Runtime.InteropServices.ExternalException)
            {
                MessageBox.Show("Could Not Copy To Clipboard");
            }
        }
    }
}

Is there some way to paste into excel? I have struggled to find a solution.

Edit

It appears after trying some other programs with dataGridViews that you can by default copy and paste to excel or other programs after selecting a group of cells in dataGridView. I can't figure out right now if it was unsupported data in the DGV or properties of the DGV that I changed in the properties manager or I just needed to close and re-open excel because there was some error.

What happens when you paste it? No data, bad data, malformed data? What you actually get on the clipboard is typically formatted text. Perhaps the format doesn't match what Excel is expecting. You might have to work backwards, format the text yourself, and put that on the clipboard manually. — DonBoitnott, Sep 17 '13 at 18:39
Forgot to mention, no data would paste. I could paste data to word, and notepad. — Matt, Sep 17 '13 at 18:47
Okay I just realized after trying some other programs with DataGridViews that they can be copied and pasted to excel without adding any code to specifically do so. Hence why there are so few questions or answers about this topic. I think it might be some setting I changed on the DataGridView property manager. — Matt, Sep 18 '13 at 12:44

DonBoitnott · Answer 1 · 2013-09-17T19:01:51.480

I can't say how this will line up with your grid, but here's an example of putting formatted text on the clipboard. This was written with the intent of pasting into Excel:

StringBuilder sb = new StringBuilder();
for (Int32 r = Selection.r1; r <= Selection.r2; r++)
{
    if (Rows[r].Visible)
    {
        if (!String.IsNullOrEmpty(sb.ToString()))
            sb.Append(Environment.NewLine);
        for (Int32 c = Selection.c1; c <= Selection.c2; c++)
        {
            if (!sb.ToString().EndsWith(Environment.NewLine) &&
                !String.IsNullOrEmpty(sb.ToString()) &&
                !sb.ToString().EndsWith("\t"))
                sb.Append("\t");
            if (String.IsNullOrEmpty(this[r, c] as String))
                sb.Append(" ");
            else
                sb.Append(this[r, c].ToString());
        }
    }
}

if (sb.Length > 0)
    ClipboardEx.SetTextThreadSafe(sb.ToString());

Note: Selection is my grid's reference to what is highlighted, and this is a cell reference to get to data in a cell.

Basically, it's trying to construct a text block something like this:

R1C1\tR1C2\tR1C3\tR1C4\n
R2C1\tR2C2\tR2C3\tR2C4\n
R3C1\tR3C2\tR3C3\tR3C4\n

The tabs (\t) tell it to move right a column, the newlines (\n) to move down a row. It's a pretty standard format. What Excel would give you if you were copying there and pasting into your grid.

score 0 · Answer 2 · answered Sep 17 '13 at 18:39

0

Well I ended up finding the answer as I kept searching.

If I change Clipboard.SetDataObject(d); to Clipboard.SetData(DataFormats.Html, d); this allows me to paste selected cell ranges to excel.

answered Sep 17 '13 at 18:39

Matt

358
3
9
23

Be aware that the HTML formatting might not be backwards-compatible with older versions of Excel. – DonBoitnott Sep 17 '13 at 18:40
@DonBoitnott Is there a better way to do it? – Matt Sep 17 '13 at 18:41
@AntonioBakula That is _not_ a standard clipboard format, and a bad idea. See: http://stackoverflow.com/questions/329918/how-to-paste-csv-data-to-windows-clipboard-with-c-sharp. – DonBoitnott Sep 17 '13 at 18:58

tewuapple · Answer 3 · 2016-09-19T10:41:51.167

public class MyDataGridView : DataGridView    
{    
    [SecurityPermission(SecurityAction.LinkDemand, Flags = SecurityPermissionFlag.UnmanagedCode)]    
    protected override bool ProcessDataGridViewKey(KeyEventArgs e)    
    {    
        switch (e.KeyCode)    
        {    
            case Keys.Insert:    
            case Keys.C:    
                return this.ProcessInsertKey(e.KeyData);    
            default:    
                break;    
        }    
        return base.ProcessDataGridViewKey(e);    
    }    

    [SecurityPermission(SecurityAction.LinkDemand, Flags = SecurityPermissionFlag.UnmanagedCode)]    
    protected new bool ProcessInsertKey(Keys keyData)    
    {    
        if ((((keyData & (Keys.Alt | Keys.Control | Keys.Shift)) == Keys.Control) ||    
            (((keyData & (Keys.Alt | Keys.Control | Keys.Shift)) == (Keys.Control | Keys.Shift))    
            && ((keyData & Keys.KeyCode) == Keys.C)))    
            && (this.ClipboardCopyMode != DataGridViewClipboardCopyMode.Disable))    
        {    
            DataObject clipboardContent = this.GetClipboardContent();    
            if (clipboardContent != null)    
            {     
                Clipboard.SetText(clipboardContent.GetData(DataFormats.UnicodeText).ToString());    
                return true;    
            }    
        }    
        return false;    
    }    
}

you can use this Custom Control.

BachPhi · Answer 4 · 2020-11-27T14:52:01.620

Why not simply do this [ no need to check clipboard since you actually doing the CTRL-C ]

private void frm_KeyDown(object sender, KeyEventArgs e)
    {
        if (e.Control && e.KeyCode == Keys.C)
        {
            dataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableWithoutHeaderText;
            dataGridView1.SelectAll();
            Clipboard.SetDataObject(dataGridView1.GetClipboardContent());               
        }
    }

Paste to Excel From DataGridView

4 Answers4