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)

Herbst

Could you share your source code? In what format do you want to get the copied data? As bitmap? – Savagism

Answer 1

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

Edgell

Answer 2

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.

Savagism

Thank you for updating. In my case, Clipboard.ContainsData(DataFormats.EnhancedMetafile) returns true, but the Clipboard.GetData(DataFormats.EnhancedMetafile) afterwards always returns null. mspaint.exe (as always) is able to paste the data which is copied to the clipboard by CopyPicture() – Herbst

@Zurb: Do you get null from Clipboard.GetData as well when you run the sample code above? Note that it is using the WPF clipboard class in System.Windows and not the one from Windows forms. Can you check that no other application is locking the clipboard? – Savagism

@divo: I forgot to mention I'm using .NET 2.0, so I cannot run your code completely as posted. I added some of my source code to the question. – Herbst

I get data from clipboard, but when trying save it a "generic GDI+" exepction hapen – Something

FWIW, I've experimented with the CopyPicture function on several systems with different versions of Excel (2000, 2003, 2007 & 2010). The xlScreen, xlBitmap combination has always produced a better image than xlPrinter, xlPicture. – Grory

Ran this code on version 15 of interop and it throws the "a generic error occured in GDI+" from InteropServices.ExternalException. There are ways to bypass this as mentioned in stackoverflow.com/questions/15571022 but it only creates a blank PNG in my case. – Artamas

Answer 3

Because asp.net thread does not have the right ApartmentState to access Clipboard Class, so you must write code to access Clipboard in new thread. For example:

private void AccessClipboardThread()
{
    // access clipboard here normaly
}

in main thread:

....
Excel.Range range = sheet.get_Range(startRange, endRange); //Save range image to clipboard
Thread thread = new Thread(new ThreadStart(AccessClipboardThread));
thread.ApartmentState = ApartmentState.STA;
thread.Start();
thread.Join(); //main thread will wait until AccessClipboardThread finish.
....

Commendatory

The code I posted was implemented to work in a standard C# Windows Form Application (still not working btw), although I was planning to use it on the Web. Thanks anyways. – Herbst

I can confirm that using this works for a web solution. – Ermines

Answer 4

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.

Nordgren

Answer 5

Interestingly I have been doing this in a STA compartment for some while with success. I wrote an app that runs on a weekly basis and mails out project status reports including some graphs I generate programmatically using Excel.

Last night this failed the graphs all returned null. I'm debugging today and find no explanation just that the method Clipboard.GetImage() returns null suddenly which it did not. By setting a breakpoint at this call, I can effectively demonstrate (by pressing CTRL+V in MS-Word) that the image IS indeed in the clipboard. Alas continuing on Clipboard.GetImage() returns null (whether I'm snooping like this or not).

My code runs as a console app and the Main method has the [STAThread] attribute. I debug it as a windows forms app (all my code is in a library and I simply have two front ends to that).

Both return null today.

Out of interest I spun off the chart fetcher into a thread as outlined (and note that thread.ApartmentState is deprecated), and it runs sweet, but still, nulls are returned.

Well, I gave up and did what any IT geek would do, rebooted.

Voila ... all was good. Go figure ... is this why we all loathe computers, Microsoft Windows and Microsoft Office? Hmmmm ... There is something , something entirely transient that can happen to you PC that makes Clipboard.GetImage() fail!

Senarmontite

Answer 6

If you don't mind Linux (style), you can use OpenOffice (or LibreOffice) to convert the xls first to pdf, then use ImageMagic to convert the pdf to image. A basic guide can be found at http://www.novell.com/communities/node/5744/c-linux-thumbnail-generation-pdfdocpptxlsimages .

Also, there seems to be .Net APIs for both of the programs mentioned above. See: http://www.opendocument4all.com/download/OpenOffice.net.pdf and http://imagemagick.net/script/api.php#dot-net

Onitaonlooker

Answer 7

Although the question has been answered but I want to note that DevExpress can solve it too.

using DevExpress.Spreadsheet;
// ...

using (Workbook workbook = new Workbook())
{
    workbook.LoadDocument("InvestmentPortfolio.xlsx", DocumentFormat.Xlsx);
    Worksheet worksheet = workbook.Worksheets.ActiveWorksheet;
    worksheet.Range["B1:I25"].ExportToImage("RangeImage.png", ImageFileFormat.Png);
}

Villalba