C# 识别复制的EXCEL表格

输出：1\t\2\t\3\r\n4\t\5\t\6\r\n

2、单列数据

 

输出：1\r\n 2\r\n 3\r\n\ 4\r\n  5\r\n 6\r\n

 

3、在有单元格合并的情况下

 

输出：1\t\t\t4\r\n2\t\t\t5\r\n 3\t\t\t6\r\n

 

C#对EXCEL行以\n分割，单元格以\t分割

 //获取剪贴板内容
string pasteText = Clipboard.GetText();
//判断是否有字符存在
if (string.IsNullOrEmpty(pasteText))
    return;
//以换行符分割的数组
string[] lines = pasteText.Trim().Split('\n');
//以制表符分割的数组
string[] vals = lines[0].Split('\t');

class ExcelData {
        /// <summary>
        /// 得到来自excel中复制的数据
        /// </summary>
        /// <returns></returns>
        public string[,] GetExcelPaste()
        {
            try
            {
                //从剪贴板获取EXCEL粘贴的数据
                string pasteText = Clipboard.GetText();
                //判断是否有字符存在
                if (string.IsNullOrEmpty(pasteText)) return null;

                //以换行符分割的数组
                string[] lines = pasteText.Trim().Split('\n');
                //以制表符分割的数组
                string[] vals = mergesame_t(lines[0]).Split('\t');
                //得到数组的行和列数
                int row = lines.Length;
                int col = vals.Length;

                string[,] array = new string[row, col];
                for (int i = 0; i < row; i++)
                {
                    string[] vals1 = mergesame_t(lines[i]).Split('\t');
                    for (int j = 0; j < col; j++)
                    {
                        array[i, j] = vals1[j];
                    }
                }
                return array;
            }
            catch (SystemException ex)
            {
                return null;//excel复制的不是规则的二维数据，可能存在每行列数不一样
            }
        }
        /// <summary>
        /// 合并连续的\t
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
       private  string mergesame_t(string str)
        {
            bool isContinue = false;//是否是连续的\ｔ
            string result = "";
            for (int i = 0; i < str.Length; i++)
            {
                char ch = str[i];
                if (ch == '\r') continue;
                if (ch == '\t')
                {
                    if (!isContinue) isContinue = true;
                    else continue;
                }
                else isContinue = false;
                result += ch;

            }
            char tail = result[result.Length - 1];
            if (tail == '\t') result = result.Substring(0, result.Length - 1);
            return result;
        }
    }

 

识别剪贴板的HTML格式数据，CopyClipboardHtmltoGrid(data);方法，并将结果动态地赋值给this.dataGridView1

protected override bool ProcessCmdKey(ref Message msg, Keys keyData)
{
    //在检测到按Ctrl+V键后，系统无法复制多单元格数据，重写ProcessCmdKey方法，屏蔽系统粘贴事件，使用自定义粘贴事件，在事件中对剪贴板的HTML格式进行处理，获取表数据，更新DataGrid控件内容

    if (keyData == (Keys.V | Keys.Control)&&this.dataGridView1.SelectedCells.Count > 0&& !this.dataGridView1.SelectedCells[0].IsInEditMode)
    {
        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();//多个单元格
                data = idataObject.GetData("System.String").ToString();//多个单元格
                copyClipboardTexttoGrid(data);
                //msg = Message.;
                msg = new Message();

                return base.ProcessCmdKey(ref msg, Keys.Control);
            }
        }
        else
        {
            data = idataObject.GetData("OEMText").ToString();//单个单元格,处于未编辑状态
            int rowStart = this.dataGridView1.SelectedCells[0].RowIndex;
            int columnStart = this.dataGridView1.SelectedCells[0].ColumnIndex;
            this.dataGridView1.Rows[rowStart].Cells[columnStart].Value = data;
            return base.ProcessCmdKey(ref msg, Keys.Control);
        }
    }
    return base.ProcessCmdKey(ref msg, keyData);
}

private void copyClipboardTexttoGrid(string data)
{
    string[] rows = data.Split(new string[] { "\r\n" }, StringSplitOptions.None);
    string[] cols;
    int rowStart = 0,columnStart=0,i = 0,j = 0;
    if (this.dataGridView1.SelectedCells.Count > 0)
    {
        rowStart = this.dataGridView1.SelectedCells[0].RowIndex;
        columnStart = this.dataGridView1.SelectedCells[0].ColumnIndex;
    }
    int count = rowStart + rows.Length - this.dataGridView1.RowCount;
    if (count >= 0)
    {
        this.dataGridView1.Rows.Add(count + 1);
    }
    for (i = 0; i < rows.Length && i + rowStart < this.dataGridView1.RowCount; i++)
    {
        cols = rows[i].Split(new string[] { "\t" }, StringSplitOptions.None);
        for (j = 0; j < cols.Length&& j + columnStart < this.dataGridView1.ColumnCount; j++)
        {
            this.dataGridView1.Rows[i + rowStart].Cells[j + columnStart].Value = cols[j];
        }
    }
    this.dataGridView1.ClearSelection();

    this.dataGridView1.Rows[i + rowStart - 1].Cells[ j + columnStart - 1].Selected = true;
}

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();
        }
    }
}