Duplicate Excel chart and move it to another sheet

Question

I use C # Excel interop and I want to create a copy of the chart from one sheet, but I want this copy on another sheet. I tried the following:

Excel.ChartObject chartTemplate = (Excel.ChartObject)sheetSource.ChartObjects("chart 1");
object o = chartTemplate.Duplicate();
Excel.ChartObject chart = (Excel.ChartObject)sheetSource.ChartObjects("chart 2");
chart.Name = "Skew" + expiry.ToString("MMMyy");
range = sheetDestination.Range["T" + chartRowCoutner.ToString()];

chart.Chart.Location(Excel.XlChartLocation.xlLocationAsObject, range);

But when I try to do this, the last line throws an error:

An unhandled exception of type "System.Exception" occurred in the projectname.exe file

Additional information: Error reading Excel file C: \ ... path to file ... \ template.xlsx: value does not fall into the expected range.

I also tried passing a sheet instead of a range:

chart.Chart.Location(Excel.XlChartLocation.xlLocationAsObject, sheetDestination);

but it gives the same error. I can not understand the cause of the error or how to fix / work around it.

I try to avoid transferring the clipboard to this, but even if I try to copy and paste, I can still paste it only as an image, which is really not perfect:

Excel.ChartArea chartArea = chart.ChartArea;
chartArea.Copy();
range = sheetDestination.Range["T" + chartRowCoutner.ToString()]; // Note that chart is not on the sheet sheetDestination
range.PasteSpecial(Excel.XlPasteType.xlPasteAll);

, , - VBA, interop. , , , interop .

Answer 1

, , , , # Excel.

# Excel Excel VBA.

, VBA, #. , .

---

"" Excel " " > "" > " " > "" : "2" > "" > " ".

, Alt + F11, VB:

, VBA Location() - ...

VBA #:

:

2 com.

, :

var sheetSource = workbookWrapper.ComObject.Sheets["Sheet1"];

, :

var workbookComObject = workbookWrapper.ComObject;
var sheetSource = workbookComObject.Sheets["Sheet1"];

: Excel?

QA AutoReleaseComObject, VSTOContrib.

:

using Microsoft.Office.Interop.Excel;
...
var missing = Type.Missing;
using (AutoReleaseComObject<Microsoft.Office.Interop.Excel.Application> excelApplicationWrapper = new AutoReleaseComObject<Microsoft.Office.Interop.Excel.Application>(new Microsoft.Office.Interop.Excel.Application()))
{
    var excelApplicationWrapperComObject = excelApplicationWrapper.ComObject;
    excelApplicationWrapperComObject.Visible = true;

    var excelApplicationWrapperComObjectWkBooks = excelApplicationWrapperComObject.Workbooks;
    try
    {
        using (AutoReleaseComObject<Workbook> workbookWrapper = new AutoReleaseComObject<Workbook>(excelApplicationWrapperComObjectWkBooks.Open(@"C:\Temp\ExcelMoveChart.xlsx", false, false, missing, missing, missing, true, missing, missing, true, missing, missing, missing, missing, missing)))
        {
            var workbookComObject = workbookWrapper.ComObject;
            Worksheet sheetSource = workbookComObject.Sheets["Sheet1"];
            ChartObject chartObj = (ChartObject)sheetSource.ChartObjects("Chart 3");
            Chart chart = chartObj.Chart;
            chart.Location(XlChartLocation.xlLocationAsObject, "Sheet2");

            ReleaseObject(chart);
            ReleaseObject(chartObj);
            ReleaseObject(sheetSource);

            workbookComObject.Close(false);
        }
    }
    finally
    {
        excelApplicationWrapperComObjectWkBooks.Close();
        ReleaseObject(excelApplicationWrapperComObjectWkBooks);

        excelApplicationWrapper.ComObject.Application.Quit();
        excelApplicationWrapper.ComObject.Quit();
        ReleaseObject(excelApplicationWrapper.ComObject.Application);
        ReleaseObject(excelApplicationWrapper.ComObject);

        GC.Collect();
        GC.WaitForPendingFinalizers();
        GC.Collect();    
    }
}

private static void ReleaseObject(object obj)
{
    try
    {
        while (System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) > 0);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
        Console.WriteLine("Unable to release the Object " + ex.ToString());
    }
}

, GC.Collect , , , Excel, , t Excel!

: Microsoft KB: Office .NET-

Answer 2

MSDN:

https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.chart.location.aspx

, Name type:

: System.Object , , Where is xlLocationAsObject , Where is xlLocationAsNewSheet.

. , . ( ):

chart1.Location(Excel.XlChartLocation.xlLocationAsNewSheet, 
    "Sales");

, :

chart1.Location(Excel.XlChartLocation.xlLocationAsObject, 
        "ExistingSheetName");

, . .

, MSDN, , , , , :

, P: Microsoft.Office.Interop.Excel.ChartArea.Top P: Microsoft.Office.Interop.Excel.ChartArea.Left. ChartArea. ChartArea , ChartArea.

, , . , .

Answer 3

, .

. .

, "" Excel. - , excel. , , .

-E

, - , , - , , .

' , "" - , ; .