How to export chart as picture with highest resolution

Question

I have the written the following code to select a range and create a chart. Chart is later exported as picture. It works, However the clarity of the picture is very bad as the range selected is from A2:U111. The code that am using is.

Dim sSheetName2 As String
Dim sSheetName3 As String
Dim oRangeToCopy As Range
Dim oCht As Chart
Dim comm As String
Workbooks("Workbook").Activate

Worksheets("Worksheet").Activate
sSheetName2 = "Worksheet" ' worksheet to work on



Worksheets(sSheetName2).Range("A2:U111").CopyPicture xlScreen, xlBitmap
Set oCht = Charts.Add
Charts("Chart1").Activate
ActiveWindow.Zoom = 400
With oCht
    .Paste
    .Export Filename:="D:\rr.jpg", Filtername:="JPG"
End With

I have also tried to reduce the range size, yet it gets pixelated.

Answer 1

Here is my solution in C# by saving the chart to .emf. Then I open the .emf file and convert the content to a Bitmap object and then to a BitmapSource object. The reason is this acts as an enhanced version of Excel.Chart.CopyPicture and the user should be able to paste the result image in the clipboard.

I assume you know how to create a Excel VSTO project and already have a Excel ribbon, which contains a button invoking the following OnActionClick method in Ribbon.cs:

public void OnActionClick(Office.IRibbonControl control)
{
    var chart = Globals.ThisAddIn.Application?.ActiveChart;

    if (chart == null)
        return;

    chart.CopyPicture(
        Excel.XlPictureAppearance.xlScreen,
        Excel.XlCopyPictureFormat.xlPicture,
        Excel.XlPictureAppearance.xlScreen);

    ImageHelper.ImproveClipboardImageQuality();
}

And here is ImageHelper.cs:

using System;
using System.Drawing;
using System.Drawing.Imaging;
using System.Runtime.InteropServices;
using System.Windows;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using PixelFormat = System.Windows.Media.PixelFormat;

namespace ExcelAddIn1
{
    [ComVisible(false)]
    public static class ImageHelper
    {
        [DllImport("user32.dll")]
        public static extern long CloseClipboard();

        [DllImport("gdi32.dll")]
        public static extern long CopyEnhMetaFileA(long hemfSrc, string lpszFile);

        [DllImport("gdi32.dll")]
        public static extern long DeleteEnhMetaFile(long hdc);

        [DllImport("gdi32.dll")]
        internal static extern bool DeleteObject(IntPtr hObject);

        [DllImport("user32.dll")]
        public static extern long GetClipboardData(long uFormat);

        [DllImport("user32.dll")]
        public static extern long OpenClipboard(long hwnd);

        private static string GetTemporaryImageFileFullPath() => ".\\TemporaryImageFile.emf";

        // https://social.msdn.microsoft.com/Forums/de-DE/62521f13-8edb-4490-9a81-b337d6ee876f/how-to-export-excel-sheet-to-jpgpng-with-high-resolution-in-vba?forum=isvvba&prof=required
        private static void ExportClipboardImageToEmf(string emfFileName)
        {
            OpenClipboard(0);

            var clipboardData = GetClipboardData(14);
            var copiedEnhMetaFile = CopyEnhMetaFileA(clipboardData, emfFileName);

            if (DeleteEnhMetaFile(copiedEnhMetaFile) == 0)
                throw new Exception("Failed to delete the enhanced meta file");

            CloseClipboard();
        }

        private static Bitmap EmfToBitmap(string path)
        {
            using (var emf = new Metafile(path))
            {
                var bmp = new Bitmap(emf);
                bmp.SetResolution(emf.HorizontalResolution, emf.VerticalResolution);
                return bmp;
            }
        }

        private static BitmapSource Convert(Bitmap bitmap)
        {
            var outputPixelFormat = GetMatchingPixelFormat(bitmap.PixelFormat);

            var bitmapData = bitmap.LockBits(
                new Rectangle(0, 0, bitmap.Width, bitmap.Height),
                ImageLockMode.ReadOnly,
                bitmap.PixelFormat);

            var bitmapSource = BitmapSource.Create(
                bitmapData.Width,
                bitmapData.Height,
                bitmap.HorizontalResolution,
                bitmap.VerticalResolution,
                outputPixelFormat,
                null,
                bitmapData.Scan0,
                bitmapData.Stride * bitmapData.Height,
                bitmapData.Stride);

            bitmapSource.Freeze();
            bitmap.UnlockBits(bitmapData);
            return bitmapSource;
        }

        private static PixelFormat GetMatchingPixelFormat(System.Drawing.Imaging.PixelFormat pixelFormat)
        {
            var outputPixelFormat = PixelFormats.Default;

            switch (pixelFormat)
            {
                case System.Drawing.Imaging.PixelFormat.Indexed:
                    outputPixelFormat = PixelFormats.Indexed8;
                    break;
                case System.Drawing.Imaging.PixelFormat.Gdi:
                    outputPixelFormat = PixelFormats.Default;
                    break;
                case System.Drawing.Imaging.PixelFormat.Alpha:
                    outputPixelFormat = PixelFormats.Default;
                    break;
                case System.Drawing.Imaging.PixelFormat.PAlpha:
                    outputPixelFormat = PixelFormats.Default;
                    break;
                case System.Drawing.Imaging.PixelFormat.Extended:
                    outputPixelFormat = PixelFormats.Default;
                    break;
                case System.Drawing.Imaging.PixelFormat.Canonical:
                    outputPixelFormat = PixelFormats.Default;
                    break;
                case System.Drawing.Imaging.PixelFormat.Undefined:
                    outputPixelFormat = PixelFormats.Default;
                    break;
                case System.Drawing.Imaging.PixelFormat.Format1bppIndexed:
                    outputPixelFormat = PixelFormats.Indexed1;
                    break;
                case System.Drawing.Imaging.PixelFormat.Format4bppIndexed:
                    outputPixelFormat = PixelFormats.Indexed4;
                    break;
                case System.Drawing.Imaging.PixelFormat.Format8bppIndexed:
                    outputPixelFormat = PixelFormats.Indexed8;
                    break;
                case System.Drawing.Imaging.PixelFormat.Format16bppGrayScale:
                    outputPixelFormat = PixelFormats.Gray16;
                    break;
                case System.Drawing.Imaging.PixelFormat.Format16bppRgb555:
                    outputPixelFormat = PixelFormats.Bgr555;
                    break;
                case System.Drawing.Imaging.PixelFormat.Format16bppRgb565:
                    outputPixelFormat = PixelFormats.Bgr565;
                    break;
                case System.Drawing.Imaging.PixelFormat.Format16bppArgb1555:
                    outputPixelFormat = PixelFormats.Bgr555;
                    break;
                case System.Drawing.Imaging.PixelFormat.Format24bppRgb:
                    outputPixelFormat = PixelFormats.Bgr24;
                    break;
                case System.Drawing.Imaging.PixelFormat.Format32bppRgb:
                    outputPixelFormat = PixelFormats.Bgr32;
                    break;
                case System.Drawing.Imaging.PixelFormat.Format32bppArgb:
                    outputPixelFormat = PixelFormats.Bgra32;
                    break;
                case System.Drawing.Imaging.PixelFormat.Format32bppPArgb:
                    outputPixelFormat = PixelFormats.Pbgra32;
                    break;
                case System.Drawing.Imaging.PixelFormat.Format48bppRgb:
                    outputPixelFormat = PixelFormats.Rgb48;
                    break;
                case System.Drawing.Imaging.PixelFormat.Format64bppArgb:
                    outputPixelFormat = PixelFormats.Rgba64;
                    break;
                case System.Drawing.Imaging.PixelFormat.Format64bppPArgb:
                    outputPixelFormat = PixelFormats.Prgba64;
                    break;
                case System.Drawing.Imaging.PixelFormat.Max:
                    break;
                default:
                    outputPixelFormat = PixelFormats.Default;
                    break;
            }

            return outputPixelFormat;
        }

        private static BitmapSource BitmapToBitmapSource(Bitmap bitmap)
        {
            if (bitmap == null)
                return null;

            var hBitmap = bitmap.GetHbitmap();
            BitmapSource source = null;

            try
            {
                source = Convert(bitmap);
            }
            catch (Exception)
            {
                // Trace Error:
                // $"Failed to create bitmap image source from file path {hBitmap.ToInt32()}. Details: {ex.Message}"
            }
            finally
            {
                DeleteObject(hBitmap);
            }

            source.Freeze();

            return source;
        }

        public static void ImproveClipboardImageQuality()
        {
            var emfFileName = GetTemporaryImageFileFullPath();
            ExportClipboardImageToEmf(emfFileName);
            var bitmap = EmfToBitmap(emfFileName);
            var bitmapSource = BitmapToBitmapSource(bitmap);
            Clipboard.SetImage(bitmapSource);
        }
    }
}

Answer 2

Dim sSheetName2 As String
Dim sSheetName3 As String
Dim oRangeToCopy As Range
Dim oCht As Chart
Dim comm As String
Workbooks("workbook").Activate

Worksheets("worksheet").Activate
Range("A2:U111").Select
With Selection.Font
    .Size = 9
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .TintAndShade = 0
End With
sSheetName2 = "worksheet" ' worksheet to work on
Worksheets(sSheetName2).Range("A2:U20").CopyPicture xlScreen, xlBitmap
Set oCht = Charts.Add
oCht.Activate
ActiveWindow.Zoom = 400
With oCht
    .Paste
    .Export Filename:="D:\rr.jpg", Filtername:="JPG"
End With

I was running the code multiple times and the code was zooming in the chart1 as mentioned in the code.

 oCht.Activate
 ActiveWindow.Zoom = 400
 With oCht
     .Paste
     .Export Filename:="D:\rr.jpg", Filtername:="JPG"
 End With

Now it selects the chart that was recently created. I have also increased the font size.