如何将C# WinForms的DataGridView数据导出至现有Excel模板？

我有一个基于C#.NET、前端为Windows Forms的应用，用户可在其中添加数据，流程结束后可将所有数据导出为Excel文件。目前已实现将DataGridView数据导出到新建Excel文件，但现在需要将数据导入到指定的Excel模板文件中——该模板已包含列标题和样式格式，需将数据填入对应单元格并另存为新文件（保留原模板供后续导出使用）。

对当前导出方法的代码理解不足，不清楚该修改哪些部分来实现需求，附上现有代码：

private void buttonConfirmExport_Click(object sender, EventArgs e)
{
    string userInputFileName = textBoxUserInputFileName.Text;
    if (userInputFileName.Equals(""))
    {
        userInputFileName = "No Title";
    }
    userInputFileName += ".xls";
    Microsoft.Office.Interop.Excel.Application xlApp;
    Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
    Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
    object misValue = System.Reflection.Missing.Value;

    xlApp = new Microsoft.Office.Interop.Excel.Application();
    xlWorkBook = xlApp.Workbooks.Add(misValue);
    xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet) xlWorkBook.Worksheets.get_Item(1);

    Microsoft.Office.Interop.Excel.Range last = xlWorkSheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
    Microsoft.Office.Interop.Excel.Range range = xlWorkSheet.get_Range("A1", last);
    range.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
    range.Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop;

    for (int i = 1; i < advancedDataGridViewCompleteBeoordeling.Columns.Count + 1; i++)
    {
        xlWorkSheet.Cells[1, i] = advancedDataGridViewCompleteBeoordeling.Columns[i - 1].HeaderText;
    }

    int imageColumn = advancedDataGridViewCompleteBeoordeling.ColumnCount - 1;

    for (int i = 0; i <= advancedDataGridViewCompleteBeoordeling.RowCount - 1; i++)
    {
        for (int j = 0; j <= advancedDataGridViewCompleteBeoordeling.ColumnCount - 1; j++)
        {
            DataGridViewCell cell = advancedDataGridViewCompleteBeoordeling[j, i];
            string text = (cell.Value != null) ? cell.Value.ToString() : "";
            string correctText = text.Replace("*ENTER*", "\n\n");
            if (j == imageColumn)
            {
                try
                {
                    Microsoft.Office.Interop.Excel.Range oRange = (Microsoft.Office.Interop.Excel.Range) xlWorkSheet.Cells[i + 2, j + 1];
                    float Left = (float)((double) oRange.Left);
                    float Top = (float)((double) oRange.Top);
                    const float ImageSize = 128;
                    xlWorkSheet.Shapes.AddPicture(text, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, ImageSize, ImageSize);
                    Console.WriteLine("row = " + i);
                }
                catch (Exception err)
                {
                    xlWorkSheet.Cells[i + 2, j + 1] = correctText;
                    Console.WriteLine(err);
                }
            }
            else
            {
                xlWorkSheet.Cells[i + 2, j + 1] = correctText;
            }
        }
    }

    try
    {
        xlWorkBook.SaveAs(userInputFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlApp);

        MessageBox.Show("Excel file created , you can find the file at 'this pc/documents/'" + userInputFileName);
    }
    catch (Exception error)
    {
        MessageBox.Show(error.ToString() + "\n\n error code: export 129");
    }
}

核心逻辑是打开现有模板而非新建空白工作簿，跳过标题写入步骤，填充数据后另存为新文件，具体修改点如下：

• 替换工作簿创建逻辑：把新建空白工作簿的代码改为打开指定路径的模板文件，建议通过文件选择框让用户指定模板，避免硬编码路径的兼容性问题。
• 移除列标题写入代码：模板已自带标题，无需重复生成。
• 确认数据起始行：根据模板标题所在行调整数据写入的起始位置（示例中模板标题在第1行，数据从第2行开始，对应原代码i+2无需修改）。
• 保护原模板：保持SaveAs逻辑，用用户输入的文件名另存为新文件，原模板不会被修改。

修改后的完整代码：

private void buttonConfirmExport_Click(object sender, EventArgs e)
{
    string userInputFileName = textBoxUserInputFileName.Text;
    if (string.IsNullOrEmpty(userInputFileName))
    {
        userInputFileName = "No Title";
    }
    userInputFileName += ".xls";
    
    // 让用户选择模板文件
    string templatePath = string.Empty;
    using (OpenFileDialog openFileDialog = new OpenFileDialog())
    {
        openFileDialog.Filter = "Excel模板|*.xlsx;*.xls";
        openFileDialog.Title = "选择Excel模板";
        if (openFileDialog.ShowDialog() != DialogResult.OK)
        {
            return; // 用户取消选择
        }
        templatePath = openFileDialog.FileName;
    }

    Microsoft.Office.Interop.Excel.Application xlApp = null;
    Microsoft.Office.Interop.Excel.Workbook xlWorkBook = null;
    Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = null;
    object misValue = System.Reflection.Missing.Value;

    try
    {
        xlApp = new Microsoft.Office.Interop.Excel.Application();
        // 打开指定模板
        xlWorkBook = xlApp.Workbooks.Open(templatePath);
        // 获取模板中的目标工作表（建议用名称而非索引，更可靠）
        xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets["数据页"];
        // 若不确定工作表名称，可用索引：xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets[1];

        int imageColumn = advancedDataGridViewCompleteBeoordeling.ColumnCount - 1;

        // 从模板标题下一行开始写入数据
        for (int i = 0; i < advancedDataGridViewCompleteBeoordeling.RowCount; i++)
        {
            for (int j = 0; j < advancedDataGridViewCompleteBeoordeling.ColumnCount; j++)
            {
                DataGridViewCell cell = advancedDataGridViewCompleteBeoordeling[j, i];
                string text = (cell.Value != null) ? cell.Value.ToString() : "";
                string correctText = text.Replace("*ENTER*", "\n\n");

                if (j == imageColumn)
                {
                    try
                    {
                        Microsoft.Office.Interop.Excel.Range oRange = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[i + 2, j + 1];
                        float Left = (float)((double)oRange.Left);
                        float Top = (float)((double)oRange.Top);
                        const float ImageSize = 128;
                        xlWorkSheet.Shapes.AddPicture(text, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, ImageSize, ImageSize);
                    }
                    catch (Exception err)
                    {
                        xlWorkSheet.Cells[i + 2, j + 1] = correctText;
                        Console.WriteLine(err.Message);
                    }
                }
                else
                {
                    xlWorkSheet.Cells[i + 2, j + 1] = correctText;
                }
            }
        }

        // 另存为新文件，原模板保持不变
        string savePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), userInputFileName);
        xlWorkBook.SaveAs(savePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, 
            misValue, misValue, misValue, misValue, 
            Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, 
            misValue, misValue, misValue, misValue, misValue);
        
        MessageBox.Show($"Excel文件已创建，路径：{savePath}");
    }
    catch (Exception error)
    {
        MessageBox.Show($"导出错误：{error.Message}\n错误码：export 129");
    }
    finally
    {
        // 确保Office对象被正确释放，避免进程残留
        if (xlWorkBook != null)
        {
            xlWorkBook.Close(false); // 不保存原模板
            releaseObject(xlWorkBook);
        }
        if (xlApp != null)
        {
            xlApp.Quit();
            releaseObject(xlApp);
        }
        releaseObject(xlWorkSheet);
    }
}

// 保留原有的对象释放方法
private void releaseObject(object obj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
        MessageBox.Show($"释放对象失败：{ex.Message}");
    }
    finally
    {
        GC.Collect();
    }
}

额外提示 #

如果模板的列顺序与DataGridView不一致，需要根据列标题映射单元格位置，比如先遍历模板的标题行，找到每个标题对应的列索引，再将DataGridView的对应列数据写入，确保数据对应正确。

内容的提问来源于stack exchange，提问作者maurice brouwers