views:

2531

answers:

5

Hi,

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)

+1  A: 

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.

0xA3
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()
Zurb
@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?
0xA3
@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.
Zurb
+2  A: 

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

Joe Erickson
A: 

Hi Zurb,

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.
....

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.
Zurb
A: 

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!

Bernd Wechner
+1  A: 

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.