Use Excel Interop to grab image of Excel chart without writing to disk or using the clipboard

Question

The interface to the chart object in Excel only allows access to the image representing the chart through two methods:

• export, which saves the image to a file
• CopyPicture, which saves the image to the clipboard

Both of these options are problematic (saving to a file is really slow, and saving to the clipboard could clobber user data).

Is there a better way to do this? The only way I can think of is to use to create a temporary ram disk and mount it under say '%TEMP%\tmp_ram_disk\' so that the save-to-disk option won't be slow, but sure how to do that or if it's even possible. This is a C# project.

Answer 1

One option that may work would be to use a memory mapped file. Of course disk can be involved there, so you should profile the standard export to disk vs. using a memory mapped file (assuming the slowness of disk i/o is your only concern with that approach).

They don't have out of the box support in .Net yet, but there likely plenty of implementations out there.

Answer 2

SpreadsheetGear for .NET can load an Excel workbook and return an image from a chart with a few lines of code:

namespace GetChartImage
{
    class Program
    {
        static void Main(string[] args)
        {
            // Open the workbook.
            var workbook = SpreadsheetGear.Factory.GetWorkbook(@"t:\tmp\Chart.xlsx");
            // Get a chart named "Chart 1" on the sheet named "Sheet1".
            var chart = workbook.Worksheets["Sheet1"].Shapes["Chart 1"].Chart;
            // Get a System.Drawing.Bitmap image of the chart.
            var bitmap = new SpreadsheetGear.Drawing.Image(chart).GetBitmap();
            // Save it to a file and launch just to see that it worked.
            bitmap.Save(@"t:\tmp\Chart.png", System.Drawing.Imaging.ImageFormat.Png);
            System.Diagnostics.Process.Start(@"t:\tmp\Chart.png");
        }
    }
}

SpreadsheetGear does not support every Excel charting feature (yet) but you can see a representative sample of what it can do in the live ASP.NET "Dynamic Chart Gallery" sample here, or you can download the free trial here and try it yourself.

Disclaimer: I own SpreadsheetGear LLC