How to send datagridview data to excel file twice or more

Question

I have a datagridview in my windowsforms project and I can export it to an Excel file like as:

private void btnExcel_Click(object sender, EventArgs e)
    {
        copyAlltoClipboard();
        Microsoft.Office.Interop.Excel.Application xlexcel;
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;
        xlexcel = new Microsoft.Office.Interop.Excel.Application();
        xlexcel.Visible = true;

        xlWorkBook = xlexcel.Workbooks.Open("C:\\file.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[12, 3];
        CR.Select();

        xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
    }

private void copyAlltoClipboard()
    {
        dataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
        dataGridView1.MultiSelect = true;
        dataGridView1.SelectAll();
        DataObject dataObj = dataGridView1.GetClipboardContent();
        if (dataObj != null)
            Clipboard.SetDataObject(dataObj);
    }

I update the datagridview data by a button click. I want to copy these all of datagridview data and paste them to the same excel file sheet. Of course cell ranges should change. How can I do?

Answer 1

I don't use C# - here is a VB.Net version:

Use the C# version of Imports Microsoft.Office.Interop to save some typing.

In the OP the Dim CR... set the destination range. I added a second paste with a different range.

The code operates on the range directly instead of using the current selection. When you record macros the generated code operates on the selection but your code can define a range and operate on it directly.

Imports Microsoft.Office.Interop

Private Sub Button8_Click(sender As Object, e As EventArgs) Handles Button8.Click
    copyAlltoClipboard(dgv)
    Dim xlexcel As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    xlexcel = New Excel.Application
    xlexcel.Visible = True
    xlWorkBook = xlexcel.Workbooks.Open("C:\temp\file.xlsx")
    xlWorkSheet = xlWorkBook.Worksheets(1)

    Dim CR As Excel.Range = xlWorkSheet.Cells(12, 3)
    CR.PasteSpecial()

    ' change range and paste again
    CR = xlWorkSheet.Cells(1, 3)
    CR.PasteSpecial()
End Sub