C#实现excle数据复制粘贴到DataGrideView表格中
//重写系统控件的检测系统快捷按键命令，只要放在展示dgv表格的窗体中就行
using System;using System.Collections.Generic;using System.Data;using System.Data.OleDb;using System.Linq;using System.Text.RegularExpressions;using System.Windows.Forms;protected override bool ProcessCmdKey(ref Message msg, Keys keyData){//在检测到按Ctrl+V键后，系统无法复制多单元格数据，重写ProcessCmdKey方法，屏蔽系统粘贴事件，使用自定义粘贴事件，在事件中对剪贴板的HTML格式进行处理，获取表数据，更新DataGrid控件内容if (keyData == (Keys.V | Keys.Control)) // &&{IDataObject idataObject = Clipboard.GetDataObject();string[] s = idataObject.GetFormats();string data;if (!s.Any(f => f == "OEMText")){if (!s.Any(f => f == "HTML Format")){}else{//data = idataObject.GetData("HTML Format").ToString();//多个单元格//copyClipboardHtmltoGrid(data);PasteData();//msg = Message.;msg = new Message();return base.ProcessCmdKey(ref msg, Keys.Control);}}elsedata = idataObject.GetData("OEMText").ToString();//单个单元格,使用系统功能，无需处理}dataGridView1.Update();return base.ProcessCmdKey(ref msg, keyData);}
//从粘贴板中读取EXCLE格式的数据
private void PasteData(){string clipboardText = Clipboard.GetText(); //获取剪贴板中的内容if (string.IsNullOrEmpty(clipboardText)){return;}int colnum = 0;int rownum = 0;for (int i = 0; i < clipboardText.Length; i++){if (clipboardText.Substring(i, 1) == "\t"){colnum++;}if (clipboardText.Substring(i, 1) == "\n"){rownum++;}}colnum = colnum / rownum + 1;int selectedRowIndex, selectedColIndex;selectedRowIndex = this.dataGridView1.CurrentRow.Index;selectedColIndex = this.dataGridView1.CurrentCell.ColumnIndex;if (selectedRowIndex + rownum > dataGridView1.RowCount || selectedColIndex + colnum > dataGridView1.ColumnCount){MessageBox.Show("粘贴区域大小不一致");return;}String[][] temp = new String[rownum][];for (int i = 0; i < rownum; i++){temp[i] = new String[colnum];}int m = 0, n = 0, len = 0;while (len != clipboardText.Length){String str = clipboardText.Substring(len, 1);if (str == "\t"){n++;}else if (str == "\n"){m++;n = 0;}else{temp[m][n] += str;}len++;}for (int i = selectedRowIndex; i < selectedRowIndex + rownum; i++){for (int j = selectedColIndex; j < selectedColIndex + colnum; j++){this.dataGridView1.Rows[i].Cells[j].Value = temp[i - selectedRowIndex][j - selectedColIndex];}}}
//从粘贴板中读取网页表格式的数据
private void copyClipboardHtmltoGrid(string data){//截取出HTML内容int start, end = -1, index, rowStart = 0, columnStart = 0;Regex regex = new Regex(@"StartFragment:\d+");Match match = regex.Match(data);if (match.Success){start = Convert.ToInt16(match.Value.Substring(14));}else{return;}regex = new Regex(@"EndFragment:\d+");match = regex.Match(data, match.Index + match.Length);if (match.Success){end = Convert.ToInt16(match.Value.Substring(12));}else{return;}if (this.dataGridView1.SelectedCells.Count > 0){rowStart = this.dataGridView1.SelectedCells[0].RowIndex;columnStart = this.dataGridView1.SelectedCells[0].ColumnIndex;}data = data.Substring(start, end - start);MatchCollection matchcollection = new Regex(@"<TR>[\S\s]*?</TR>").Matches(data), sub_matchcollection;int count = rowStart + matchcollection.Count - this.dataGridView1.RowCount;if (count >= 0){this.dataGridView1.Rows.Add(count + 1);}for (int i = 0; i < matchcollection.Count && i + rowStart < this.dataGridView1.RowCount; i++){sub_matchcollection = new Regex(@"<TD>[\S\s]*?</TD>").Matches(matchcollection[i].Value);for (int j = 0; j < sub_matchcollection.Count && j + columnStart < this.dataGridView1.ColumnCount; j++){this.dataGridView1.Rows[i + rowStart].Cells[j + columnStart].Value = sub_matchcollection[j].Value.Substring(4, sub_matchcollection[j].Length - 9).Trim();}}}
