Programmatically (C#) convert Excel to an image

Question

I want to convert an excel file to an image (every format is ok) programmatically (c#). Currently I'm using Microsoft Interop Libraries & Office 2007, but it does not support saving to an image by default.

So my current work-around is as follows:

• Open Excel file using Microsoft Interop;
• Find out the max range (that contains data);
• Use the CopyPicture() on that range, which will copy the data to the Clipboard.

Now the tricky part (and my problems):

Problem 1:

Using the .NET Clipboard class, I'm not able to get the EXACT copied data from the clipboard: the data is the same, but somehow the formatting is distorted (the font of the whole document seems to become bold and a little bit more unreadable while they were not); If I paste from the clipboard using mspaint.exe, the pasted image is correct (and just as I want it to be).

I disassembled mspaint.exe and found a function that it is using (OleGetClipboard) to get data from the clipboard, but I cannot seem to get it working in C# / .NET.

Other things I tried were the Clipboard WINAPI's (OpenClipboard, GetClipboardData, CF_ENHMETAFILE), but the results were the same as using the .NET versions.

Problem 2:

Using the range and CopyPicture, if there are any images in the excel sheet, those images are not copied along with the surrounding data to the clipboard.

Some of the source code

Excel.Application app = new Excel.Application();
app.Visible = app.ScreenUpdating = app.DisplayAlerts = false;
app.CopyObjectsWithCells = true;
app.CutCopyMode = Excel.XlCutCopyMode.xlCopy;
app.DisplayClipboardWindow = false;

try {
    Excel.Workbooks workbooks = null;
    Excel.Workbook book = null;
    Excel.Sheets sheets = null;

    try {
        workbooks = app.Workbooks;
        book = workbooks.Open(inputFile, false, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
                              Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
                              Type.Missing, Type.Missing);
        sheets = book.Worksheets;
    } catch {
        Cleanup(workbooks, book, sheets);   //Cleanup function calls Marshal.ReleaseComObject for all passed objects
        throw;
    }

    for (int i = 0; i < sheets.Count; i++) {
        Excel.Worksheet sheet = (Excel.Worksheet)sheets.get_Item(i + 1);

        Excel.Range myrange = sheet.UsedRange;
        Excel.Range rowRange = myrange.Rows;
        Excel.Range colRange = myrange.Columns;

        int rows = rowRange.Count;
        int cols = colRange.Count;

        //Following is used to find range with data
        string startRange = "A1";
        string endRange = ExcelColumnFromNumber(cols) + rows.ToString();

        //Skip "empty" excel sheets
        if (startRange == endRange) {
            Excel.Range firstRange = sheet.get_Range(startRange, endRange);
            Excel.Range cellRange = firstRange.Cells;
            object text = cellRange.Text;
            string strText = text.ToString();
            string trimmed = strText.Trim();

            if (trimmed == "") {
                Cleanup(trimmed, strText, text, cellRange, firstRange, myrange, rowRange, colRange, sheet);
                continue;
            }
            Cleanup(trimmed, strText, text, cellRange, firstRange);
        }

        Excel.Range range = sheet.get_Range(startRange, endRange);
        try {
            range.CopyPicture(Excel.XlPictureAppearance.xlScreen, Excel.XlCopyPictureFormat.xlPicture);

            //Problem here <-------------
            //Every attempt to get data from Clipboard fails
        } finally {
            Cleanup(range);
            Cleanup(myrange, rowRange, colRange, sheet);
        }
    }   //end for loop

    book.Close(false, Type.Missing, Type.Missing);
    workbooks.Close();

    Cleanup(book, sheets, workbooks);
} finally {
    app.Quit();
    Cleanup(app);
    GC.Collect();
}

Getting data from the clipboard using WINAPI succeeds, but with bad quality. Source:

protected virtual void ClipboardToPNG(string filename) {
    if (OpenClipboard(IntPtr.Zero)) {
        if (IsClipboardFormatAvailable((int)CLIPFORMAT.CF_ENHMETAFILE)) {
            int hEmfClp = GetClipboardDataA((int)CLIPFORMAT.CF_ENHMETAFILE);

            if (hEmfClp != 0) {
                int hEmfCopy = CopyEnhMetaFileA(hEmfClp, null);

                if (hEmfCopy != 0) {
                    Metafile metafile = new Metafile(new IntPtr(hEmfCopy), true);

                    metafile.Save(filename, ImageFormat.Png);
                }
            }
        }

        CloseClipboard();
    }
}

Anyone got a solution? (I'm using .NET 2.0 btw)

Answer 1

From what I understand from your question I am not able to reproduce the problem.

I selected a range manually in Excel, chose Copy As Picture with the options as shown on screen and Bitmap selected, then I used the following code to save the clipboard data:

using System;
using System.IO;
using System.Windows;
using System.Windows.Media.Imaging;
using System.Drawing.Imaging;
using Excel = Microsoft.Office.Interop.Excel;

public class Program
{
    [STAThread]
    static void Main(string[] args)
    {
        Excel.Application excel = new Excel.Application();
        Excel.Workbook wkb = excel.Workbooks.Add(Type.Missing);
        Excel.Worksheet sheet = wkb.Worksheets[1] as Excel.Worksheet;
        Excel.Range range = sheet.Cells[1, 1] as Excel.Range;
        range.Formula = "Hello World";

        // copy as seen when printed
        range.CopyPicture(Excel.XlPictureAppearance.xlPrinter, Excel.XlCopyPictureFormat.xlPicture);

        // uncomment to copy as seen on screen
        //range.CopyPicture(Excel.XlPictureAppearance.xlScreen, Excel.XlCopyPictureFormat.xlBitmap);

        Console.WriteLine("Please enter a full file name to save the image from the Clipboard:");
        string fileName = Console.ReadLine();
        using (FileStream fileStream = new FileStream(fileName, FileMode.Create))
        {
            if (Clipboard.ContainsData(System.Windows.DataFormats.EnhancedMetafile))
            {
                Metafile metafile = Clipboard.GetData(System.Windows.DataFormats.EnhancedMetafile) as Metafile;
                metafile.Save(fileName);
            }
            else if (Clipboard.ContainsData(System.Windows.DataFormats.Bitmap))
            {
                BitmapSource bitmapSource = Clipboard.GetData(System.Windows.DataFormats.Bitmap) as BitmapSource;

                JpegBitmapEncoder encoder = new JpegBitmapEncoder();
                encoder.Frames.Add(BitmapFrame.Create(bitmapSource));
                encoder.QualityLevel = 100;
                encoder.Save(fileStream);
            }
        }
        object objFalse = false;
        wkb.Close(objFalse, Type.Missing, Type.Missing);
        excel.Quit();
    }
}

Regarding your second problem: As far as I know it is not possible in Excel to select both a cell range and an image at the same time. If you want to get both in an image at the same time you might have to print the Excel sheet to an image/PDF/XPS file.

Answer 2

SpreadsheetGear for .NET will do it.

You can see our ASP.NET (C# and VB) "Excel Chart and Range Imaging Samples" samples here and download a free trial here if you want to try it out.

SpreadsheetGear also works with Windows Forms, console applications, etc... (you did not specify what type of application you are creating). There is also a Windows Forms control to display a workbook in your application if that is what you are really after.

Disclaimer: I own SpreadsheetGear LLC

Answer 3

This is a bug with GDI+ when it comes to converting metafiles to a bit map format.
It happens for many EMFs that displays charts with texts. To re-create, you simply need to create a chart in excel that displays data for its X and Y axis. Copy the chart as a picture and paste in word as a metafile. Open the docx and you will see an EMF in the media folder. If you now open that EMF in any windows based paint program that converts it to a bitmap, you will see distortions, in particular, text and lines become larger and distorted. Unfortunately, it is one of those issues that Microsoft is not likely to admit or do anything about. Let's hope Google and Apple take over the office/word processing world soon as well.