C# Paste Clipboard content into Excel worksheet

Question

As the title says, I try to actually paste what is in my Clipboard into an Excel.

I've following code:

Clipboard.SetText(html);
sheet.Range("A1").Value = Clipboard.GetText();

Actually, the variable html contains a html code file, when I do it like that, I actually paste only the html content into the Range, but, if I open Excel and do by hand, Paste Special... I can paste the html code, but it transforms the code into a real table instead of a html code and this is the real result that I want, without doing it by hand.

Excel.Range.Copy() paste with Clipboard.GetText()

Another way was:

foreach (Excel.Worksheet sheet in workbook.Sheets)
{
    foreach (Excel.Shape shape in sheet.Shapes)
    {
        Clipboard.SetText(html);

        //doesn't work:                              
        sheet.Range("A1").Value = sheet.PasteSpecial(Clipboard.GetText()); 

        sheet.PasteSpecial(Clipboard.GetText()); //throws error
    }
}

But this way doesn't work too. I can do it with an html -> image and paste the image, but real values should be accessible and not a picture.

Hope someone can clarify how to solve it.

Thanks.

possible duplicate ? http://stackoverflow.com/questions/10147152/excel-paste-special-and-add-operation — Nahum, Mar 27 '13 at 08:50

score 3 · Accepted Answer · edited Mar 27 '13 at 10:38

3

You may try to use SetData instead of SetText:

 Clipboard.SetData(DataFormats.Html, html);

to copy the string into the clipboard and tag it as HTML (if this does not work in your case, SetTextmay be ok).

The call to PasteSpecial for the cell range where you want the insert to happen, taking regard to your comments:

ActiveSheet.Range("A1").PasteSpecial(
         Excel.Enums.XlPasteType.xlPasteAll,
         Excel.Enums.Xl‌PasteSpecialOperation.xlPasteSpecialOperationNone,
         false, false);

Note that assigning a cell a new value by using the Value property never copies any formats etc.

edited Mar 27 '13 at 10:38

Uwe Keim

39,551
56
175
291

answered Mar 27 '13 at 08:56

Doc Brown

19,739
7
52
88

I was thinking about that way, but it still throws me an exception, I'm using NetOffice instead of Microsoft.Office libraries that's why, because I can just do a .PasteSpecial without parameters. I'll wait a reply from admin there to ask if it might be an assembly issue or a wrong implementation. It seems to compile with : sheet.Range("A1").PasteSpecial(Excel.Enums.XlPasteType.xlPasteAll,Excel.Enums.XlPasteSpecialOperation.xlPasteSpecialOperationAdd,false, false); But still get some exception error. – mike27015 Mar 27 '13 at 09:11
I did it with xlPasteSpecialOperationAdd , because I already have existing values from an template ... And want to do calculations from previous existing values, if I leave it with xlPasteSpecialOperationNone, old values are kept, but still get an exception error. RTFM was read, since you even gave me the right Operation. – mike27015 Mar 27 '13 at 09:26
Just for copying it works with Clipboard.SetText(html); sheet.Range("A1").PasteSpecial(); He doesn't like the Clipboard.SetData(DataFormats.Htlm, html); – mike27015 Mar 27 '13 at 09:48
1

@mike27015: ok, changed my answer accordingly. – Doc Brown Mar 27 '13 at 10:36

score 1 · Answer 2 · edited Oct 11 '17 at 10:23

Use this: xlWorkSheet.PasteSpecial(Missing.Value, false, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

//below is the complete code

            Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;

            Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            xlApp.Visible = true;
            xlApp.UserControl = true;
            xlApp.WindowState = Excel.XlWindowState.xlMaximized;
            xlWorkBook = xlApp.Workbooks.Add(Type.Missing);
           // xlWorkBook = xlApp.Workbooks.Open(excel_filename);
            xlApp.ActiveWorkbook.Sheets[1].Activate();
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            xlWorkSheet.PasteSpecial(Missing.Value, false, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            xlWorkBook.SaveAs(save_file_name);
            Console.WriteLine("saved file name" + save_file_name);
            xlWorkBook.Close();
            xlApp.Quit();

score 1 · Answer 3 · answered May 23 '16 at 14:07

1

I managed to get the copied cells from the Clipboard by using

    Clipboard.GetData("XML Spreadsheet");

Then, after I did some copy-pasting through my code, I put it back with

    Clipboard.SetData("XML Spreadsheet", originalObject);

Happy coding!

answered May 23 '16 at 14:07

Zlatin Todorinski

41
1
6

Standa · Answer 4 · 2019-02-28T11:39:53.633

my simple C# Copy & Paste ( from Input/Sheet[1] to Output/Sheet[1] )

using System.Reflection;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

// prepare Input
        Excel.Application xlApp = new Excel.Application();
        xlApp.DisplayAlerts = false;
        Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(fileNameIn);
        Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
        Excel.Range xlRange = xlWorksheet.UsedRange;      //I copy everything
// prepare Output
        Excel.Application oXL = new Excel.Application();
        oXL.DisplayAlerts = false;
        Excel.Workbook mWorkBook = oXL.Workbooks.Open(fileNameOut, 0, false, 5,
                                "", "", false, Excel.XlPlatform.xlWindows,
                                "", true, false, 0, true, false, false);
        Excel.Worksheet mWSheet1 = mWorkBook.Sheets[1];
// make Copy&Paste in PC memory
        xlRange.Copy(Type.Missing);
        Excel.Range targetRange = mWSheet1.Cells[11, 1];   //initial cell for Paste
        mWSheet1.Paste(targetRange);
// save Output
        mWorkBook.SaveAs(fileNameOut, Excel.XlFileFormat.xlWorkbookNormal, 
                                Missing.Value, Missing.Value,
                                Missing.Value, Missing.Value,  
                                Excel.XlSaveAsAccessMode.xlExclusive,
                                Missing.Value, Missing.Value,
                                Missing.Value, Missing.Value, Missing.Value);
// clean the waste !
        mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);
        mWSheet1 = null; mWorkBook = null; oXL.Quit();
        GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers();
        GC.Collect(); GC.WaitForPendingFinalizers();
        Marshal.ReleaseComObject(xlRange); Marshal.ReleaseComObject(xlWorksheet);
        xlWorkbook.Close(); Marshal.ReleaseComObject(xlWorkbook);
        xlApp.Quit(); Marshal.ReleaseComObject(xlApp);

score 0 · Answer 5 · answered Mar 14 '18 at 11:11

this code is for dump html string into the excel

using Microsoft.Office.Interop.Excel;

private void ExcelExport()
{
    var excel = new Application { Visible = true };
    excel.WindowState = XlWindowState.xlMaximized;
    Workbook workbook = excel.Workbooks.Add(XlSheetType.xlWorksheet);
    Worksheet sheet = workbook.Sheets[1];
    sheet.Activate();

    string s = "<html><body><table>";

    for (int i = 1; i <= 100; i++)
    {
        s += "<tr>";

        for (int f = 1; f <= 100; f++)
        {
            s += "<td>" + i.ToString() + "," + f.ToString() + "</td>";
        }

        s += "</tr>";
    }

    s += "</table></body></html>";

    System.Windows.Forms.Clipboard.SetText(s);

    sheet.Range["A1"].Select();
    sheet.PasteSpecial(Type.Missing, false, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    sheet.Range["A1"].Select();
}

C# Paste Clipboard content into Excel worksheet

5 Answers5