Excel VSTO开发：如何实现DataGridView数据拖拽至工作表？

以下是完整的示例代码，实现点击功能区按钮打开带数据的DataGridView窗体，选中行后拖拽至Excel工作表的需求：

1. 功能区按钮代码（Ribbon1.cs） #

using Microsoft.Office.Tools.Ribbon;
using System.Windows.Forms;

namespace ExcelVSTODragDrop
{
    public partial class Ribbon1
    {
        private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
        {
        }

        private void btnOpenDataForm_Click(object sender, RibbonControlEventArgs e)
        {
            var dataForm = new DataGridViewForm(Globals.ThisAddIn.Application);
            dataForm.Show();
        }
    }
}

2. DataGridView窗体代码（DataGridViewForm.cs） #

先在窗体设计器中添加DataGridView控件（命名为dataGridView1），设置SelectionMode = FullRowSelect、MultiSelect = true。

using Excel = Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Windows.Forms;

namespace ExcelVSTODragDrop
{
    public partial class DataGridViewForm : Form
    {
        private Excel.Application _excelApp;
        private Point _dragStartPoint;

        public DataGridViewForm(Excel.Application excelApp)
        {
            InitializeComponent();
            _excelApp = excelApp;
            LoadTestData();
            dataGridView1.MouseDown += DataGridView1_MouseDown;
            dataGridView1.MouseMove += DataGridView1_MouseMove;
        }

        private void LoadTestData()
        {
            var dt = new System.Data.DataTable();
            dt.Columns.Add("ID", typeof(int));
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("Department", typeof(string));
            dt.Columns.Add("Salary", typeof(decimal));

            dt.Rows.Add(1, "John Doe", "Engineering", 85000);
            dt.Rows.Add(2, "Jane Smith", "Marketing", 78000);
            dt.Rows.Add(3, "Mike Johnson", "HR", 65000);
            dt.Rows.Add(4, "Sarah Lee", "Finance", 92000);
            dt.Rows.Add(5, "Tom Wilson", "Operations", 72000);

            dataGridView1.DataSource = dt;
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
        }

        private void DataGridView1_MouseDown(object sender, MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Left)
            {
                _dragStartPoint = new Point(e.X, e.Y);
            }
        }

        private void DataGridView1_MouseMove(object sender, MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Left)
            {
                var dragDistance = Math.Abs(e.X - _dragStartPoint.X) + Math.Abs(e.Y - _dragStartPoint.Y);
                if (dragDistance >= SystemInformation.DragSize)
                {
                    var selectedRows = GetSelectedRowsData();
                    if (selectedRows.Count > 0)
                    {
                        dataGridView1.DoDragDrop(selectedRows, DragDropEffects.Copy);
                    }
                }
            }
        }

        private List<object[]> GetSelectedRowsData()
        {
            var dataList = new List<object[]>();
            foreach (DataGridViewRow row in dataGridView1.SelectedRows)
            {
                if (!row.IsNewRow)
                {
                    var rowData = new object[dataGridView1.Columns.Count];
                    for (int i = 0; i < dataGridView1.Columns.Count; i++)
                    {
                        rowData[i] = row.Cells[i].Value;
                    }
                    dataList.Add(rowData);
                }
            }
            return dataList;
        }

        protected override void OnDragEnter(DragEventArgs e)
        {
            base.OnDragEnter(e);
            e.Effect = e.Data.GetDataPresent(typeof(List<object[]>)) ? DragDropEffects.Copy : DragDropEffects.None;
        }

        protected override void OnDragDrop(DragEventArgs e)
        {
            base.OnDragDrop(e);
            if (e.Data.GetDataPresent(typeof(List<object[]>)))
            {
                var selectedData = e.Data.GetData(typeof(List<object[]>)) as List<object[]>;
                if (selectedData?.Count > 0)
                {
                    try
                    {
                        Excel.Worksheet activeSheet = _excelApp.ActiveSheet as Excel.Worksheet;
                        Excel.Range startCell = _excelApp.ActiveCell as Excel.Range;

                        int rowCount = selectedData.Count;
                        int colCount = selectedData[0].Length;
                        Excel.Range targetRange = activeSheet.Range[startCell, startCell.Offset(rowCount - 1, colCount - 1)];
                        targetRange.Value2 = selectedData.ToArray();
                        targetRange.EntireColumn.AutoFit();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show($"写入失败: {ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
            }
        }
    }
}

关键实现细节 #

• Excel实例传递：通过窗体构造函数传入当前Excel应用实例，确保操作的是用户正在使用的工作簿。
• 拖拽触发逻辑：判断鼠标移动距离超过系统阈值才启动拖拽，避免误操作。
• 高效数据写入：一次性写入所有选中数据到Excel区域，比逐行写入性能更优。
• 异常处理：捕获Excel操作异常，避免程序崩溃并给出友好提示。

内容的提问来源于stack exchange，提问作者Srinivas Ch