Copy Excel Chart Sheet to clipboard using C#

问题:

I am writing a C# app.

One of the tasks is to open an Excel spreadsheet and paste a chart sheet from it into the the C# app. Please note I am referring to a CHART SHEET...not a chart object embedded on an ordinary Worksheet.

There is a reason why I am trying to be precise here.

I use ordinary COM to communicate from C# to Excel...

using using Microsoft.Office.Interop.Excel;  

In a situation where a chart is embedded in an ordinary worksheet, the API works. I retrieve the worksheets from workbook, from the worksheet I get chartObject and issuing a copy on chartObject works as expected i.e. the contents of the clipboard are as if you initiated the copy from Excel itself.

Things are different when dealing with a chart sheet. I get Charts from the workbook, then get Chart instance I am interested in. All good. However, issuing a Copy() on a Chart here does not result in Clipboard looking anything like it would should the action be initiated from Excel. CopyPicture is also not helping because the pic is really blown up out of proportion and Clipboard is missing the usual PNG,GIFF,JPEG formats - Clipboard.getImage() returns null while doI see the image is in the Clipboard. Copy() creates only Preview, DataObject and OLE Private Data formats on the Clipboard instead of many many more...

//this does not work - it is for chart sheets
Excel.Charts charts = wb.Charts;
Excel.Chart chart = charts["mychartname"];
chart.Copy(); or chart.CopyPicture();    <-- these do not work as expected...


// this works fine but only covers embedded charts
Excel.ChartObjects charts = ws.ChartObjects;
Excel.ChartObject chart = charts["mychartname"];
chart.Copy();

Why would a Copy on ChartObject work different from Copy on a Chart? Copy() is such a trivial operation...and to see it not working on Chart is a huge surprise...

Is there a workaround? I basically want to have the Clipboard look the same way regardless whether the Copy action was initiated programmatically from my C# app or via user actions in Excel itself.

And in case Chart Sheet sounds foreign...it is basically a sheet with just a chart on it. You can make one by first creating a basic embedded chart and then moving it (design tab) to a dedicated sheet.

Thank you.

回答1:

After some trial and error I found an easy and very acceptable solution.

Instead of calling Copy() on the Chart instance, get the ChartArea from Chart and call Copy on it.

Excel.Charts charts = wb.Charts;
Excel.Chart chart = charts["mychartname"];
Excel.ChartArea chartArea = chart.ChartArea;
chartArea.Copy();

Remember to COM Release all intermediate COM objects :)