Search code examples
c#datagridviewcom-interopexcel-interopclipboarddata

Worksheet.PasteSpecial Function pasting data as bitmap image

I am using below code to copy data from DataGridView and then pasting in an excel file. 

private void copyAlltoClipboard()
{
    //to remove the first blank column from datagridview
    dataGridView1.RowHeadersVisible = false;
    dataGridView1.SelectAll();
    DataObject dataObj = dataGridView1.GetClipboardContent();
    if (dataObj != null)
        Clipboard.SetDataObject(dataObj);
}
private void button3_Click_1(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 Excel.Application();
    xlexcel.Visible = true;
    xlWorkBook = xlexcel.Workbooks.Add(misValue);
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
    CR.Select(); // CR is a COM Object
    // WorkSheet.PasteSpecial(object,object,object,object,object);
    xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);          
}

this yields this result in excel file :

enter image description here

It works great on almost all machines but a few machines in production, it pastes as a black dot picture ( I guess it is converting the data in bitmap representation). This is what it pastes on some machines :

enter image description here

I tried using function PasteSpecial(). But then it wont copy anything in any machine. I am not sure what is going wrong here.

I could not find the difference in terms of configuration in machines in which it is working well and in which it is not working. Any idea how to handle this and display data instead of picture in all machines?

Solution

  • I also encountered this problem. The fix for the above code is to correctly configure the PasteSpecial method. The first parameter for the method should be the format. Correct the last line of button3_Click_1 as follows and all should work.

    xlWorkSheet.PasteSpecial("Text", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);

    Another option that appears to have fixed the problem is to change the line as follows. This signals that the pasted text is not a link and is not an icon.

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