复制Excel图表并将其移至另一张表 [英] Duplicate an Excel chart and move it to another sheet

问题描述

我正在使用C＃Excel互操作，并且我想从一张纸上创建图表的副本，但是我希望将此副本放在另一张纸上。我尝试了以下操作：

  Excel.ChartObject chartTemplate =（Excel.ChartObject）sheetSource.ChartObjects（ chart 1）; 
对象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）; 
  

但是当我尝试此操作时，最后一行抛出错误：

projectname.exe中发生了'System.Exception'类型的未处理异常

其他信息：读取Excel错误文件C：\ ...文件路径... \template.xlsx：值不在
预期范围内。

我也尝试过传递表格而不是范围：

  chart.Chart.Location（Excel .XlChartLocation.xlLocationAsObject，sheetDestination）; 
  

，但这给出了相同的错误。我无法理解错误的原因或如何解决/绕过错误。

我试图避免将剪贴板插入其中，但是即使我尝试复制和粘贴后，我仍然只能将其粘贴为图像，这确实不理想：

  Excel.ChartArea chartArea = chart .ChartArea; 
 chartArea.Copy（）; 
 range = sheetDestination.Range [ T + chartRowCoutner.ToString（）]; ///请注意，图表不在工作表sheetDestination 
范围内。PasteSpecial（Excel.XlPasteType.xlPasteAll）; 
  

我现在唯一想到的另一种解决方案是在VBA中执行此操作，然后通过互操作。但是肯定可以使用不带剪贴板的互操作以干净的方式完成。

解决方案

您已经有了解决方案但是我不会给您一天的时间，而是给您一个正确的答案，该答案将帮助您完成任何C＃Excel编码任务。

用于Excel的C＃Interop模型是

这意味着将VBA 记录的宏转换为C＃并不容易。让我们通过诸如将图表移动到另一张纸之类的练习进行尝试。在：Sheet2>单击确定>单击停止宏录制。

要查看录制的宏，请按 Alt + F11 调出VB编辑器：

请参见上文屏幕快照VBA如何向您显示Location（）的第二个参数是 Name ，它实际上是一个字符串参数 ...

让转换此VBA宏到C＃：

由@Ama编辑

以下建议已过时，实际上不必担心释放COM对象，这是自动完成的通常在发布模式下（调试模式不）。请参阅 Hans Passant对使用IDisposable清理Excel Interop对象的答案。。

---

这里的窍门是：

永远不要在com对象中使用2个点。

注意我怎么写：

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

但是它有两个点，所以我这样写：

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

参考：如何正确清理Excel互操作对象？

您将看到 AutoReleaseComObject 代码，例如 VSTOContrib> a 使用。

这是完整的代码：

 使用Microsoft.Office.Interop.Excel; 
 ... 
 var missing = Type.Missing; 
使用（AutoReleaseComObject< Microsoft.Office.Interop.Excel.Application> excelApplicationWrapper = new AutoReleaseComObject< Microsoft.Office.Interop.Excel.Application>（new Microsoft.Office.Interop.Excel.Application（）））
 {
 var excelApplicationWrapperComObject = excelApplicationWrapper.ComObject; 
 excelApplicationApplicationWrapperComObject.Visible = true; 
 
 var excelApplicationWrapperComObjectWkBooks = excelApplicationWrapperComObject.Workbooks; 
尝试
 {
使用（AutoReleaseComObject< Workbook> workbookWrapper = new AutoReleaseComObject< Workbook>（excelApplicationWrapperComObjectWkBooks.Open（@``C： ，缺少，缺少，缺少，正确，丢失，丢失，正确，丢失，丢失，丢失，丢失，丢失）））
 {
 var workbookComObject = workbookWrapper.ComObject; 
工作表sheetSource = workbookComObject.Sheets [ Sheet1]; 
 ChartObject chartObj =（ChartObject）sheetSource.ChartObjects（ Chart 3）; 
 Chart chart = chartObj.Chart; 
 chart.Location（XlChartLocation.xlLocationAsObject， Sheet2）; 
 
 ReleaseObject（图表）; 
 ReleaseObject（chartObj）; 
 ReleaseObject（sheetSource）; 
 
 workbookComObject.Close（false）; 
} 
} 
最后
 {
 excelApplicationWrapperComObjectWkBooks.Close（）; 
 ReleaseObject（excelApplicationWrapperComObjectWkBooks）; 
 
 excelApplicationWrapper.ComObject.Application.Quit（）; 
 excelApplicationWrapper.ComObject.Quit（）; 
 ReleaseObject（excelApplicationWrapper.ComObject.Application）; 
 ReleaseObject（excelApplicationWrapper.ComObject）; 
 
 GC.Collect（）; 
 GC.WaitForPendingFinalizers（）; 
 GC.Collect（）; 
} 
} 
 
私有静态无效ReleaseObject（object obj）
 {
 try 
 {
 while（System.Runtime .InteropServices.Marshal.ReleaseComObject（obj）> 0）; 
 obj = null; 
} 
捕获（异常例外）
 {
 obj = null; 
 Console.WriteLine（无法释放对象 + ex.ToString（））; 
} 
} 
  

我知道使用GC.Collect而不使用两个来释放所有对象分配时的点似乎在顶部，但至少在我退出Excel实例时，该过程已释放，我不必以编程方式杀死Excel进程！

引用： Microsoft KB：从.NET客户端自动化后，Office应用程序不会退出

I am using the C# Excel interop and I want to create a copy of a chart from one sheet but I want this copy on another sheet. I have 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 this, the last line throws an error:

An unhandled exception of type 'System.Exception' occurred in projectname.exe

Additional information: Error reading Excel file C:\ ...the file path...\template.xlsx: Value does not fall within the expected range.

I have also tried passing a sheet in instead of a range:

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

but this gives the same error. I can't understand the reason for the error or how to fix it / bypass it.

I am trying to avoid bringing the clipboard into this, but even if I try copying and pasting, I can still only paste it as an image, which is really not ideal:

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);

The only other solution I can think of now is to do this in VBA and then execute the macro via the interop. But surely it can be done in a clean way just using the interop without the clipboard.

解决方案

You've already got the solution but instead of giving you a fish for a day I'll give you a proper answer that will help you with any C# Excel coding task.

The C# Interop Model for Excel is almost identical to the VBA Excel Model.

This means its trivial to convert VBA recorded macro's to C#. Lets try this with an exercise like moving a chart to a different sheet.

---

In the Developer Tab in Excel click Record Macro > right click Chart > select Move Chart > choose Object in: Sheet2 > click OK > click Stop Macro Recording.

To see the recorded Macro press Alt + F11 to bring up the VB Editor:

See in the above screenshot how VBA shows you the second parameter for Location() is Name and its actually a string argument...

Lets convert this VBA Macro to C#:

EDIT by @Ama

The advice below is outdated, there's actually no need to worry about releasing COM objects, this is done automatically at RELEASE mode (DEBUG mode does not). See Hans Passant's answer to "Clean up Excel Interop Objects with IDisposable".

---

The trick here is:

Never use 2 dots with com objects.

Notice how I could have written:

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

but that has two dots, so instead I write this:

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

Ref: How do I properly clean up Excel interop objects?

You will see the AutoReleaseComObject code in the above QA that projects like VSTOContrib use.

Here is the complete code:

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());
    }
}

I know Releasing all the Objects, using GC.Collect and not using two dots when assigning seems over the top but at least when I quit the instance of Excel the process is freed, I don't have to programmatically Kill the Excel Process!!

Ref: Microsoft KB: Office application does not quit after automation from .NET client

这篇关于复制Excel图表并将其移至另一张表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！