Paste to Excel From DataGridView
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.
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:
R1C1tR1C2tR1C3tR1C4n
R2C1tR2C2tR2C3tR2C4n
R3C1tR3C2tR3C3tR3C4n
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.
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.
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;
}
}
您可以使用此自定义控件。
链接地址: http://www.djcxy.com/p/62262.html