CODEWITHSUNDEEP
c#excelchartscom
Moonwalker
Moonwalker

Reputation: 3812

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.

Upvotes: 0

Views: 3396

Answers (2)

Davit Escobedo
Davit Escobedo

Reputation: 21

In real life is very difficult or even impossible to keep track of all intermediate COM Objects to be released and just missing one will get you into trouble.

That's why Microsoft seems to recommend simply passing the Garbage collector once you're sure all references to any COM object are out of scope:

// Instead of calling releaseComObject on every single COM object we use, we call the GC when they are all out of scope as suggested in
// https://msdn.microsoft.com/en-us/library/aa679807(v=office.11).aspx#officeinteroperabilitych2_part2_usingrco
GC.Collect();
GC.WaitForPendingFinalizers();
// https://web.archive.org/web/20150907193302/https://code.msdn.microsoft.com/office/VBAutomateExcel-b6ecaff3
// GC needs to be called twice in order to get the Finalizers called the first time in, it simply makes a list of what is to be  
// finalized, the second time in, it actually is finalizing. Only then will the object do its automatic ReleaseComObject. 
GC.Collect();
GC.WaitForPendingFinalizers();

References:

https://msdn.microsoft.com/en-us/library/aa679807(v=office.11).aspx#officeinteroperabilitych2_part2_usingrco

https://web.archive.org/web/20150907193302/https://code.msdn.microsoft.com/office/VBAutomateExcel-b6ecaff3

Upvotes: 0

Moonwalker
Moonwalker

Reputation: 3812

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

Upvotes: 0

Related Questions