以编程方式 (C#) 将 Excel 转换为图像

Programmatically (C#) convert Excel to an image

提问by Zurb

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.

我想以编程方式 (c#) 将 excel 文件转换为图像（每种格式都可以）。目前我正在使用 Microsoft Interop Libraries & Office 2007，但默认情况下它不支持保存到图像。

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.

• 使用 Microsoft Interop 打开 Excel 文件；
• 找出最大范围（包含数据）；
• 在该范围内使用 CopyPicture()，它将数据复制到剪贴板。

Now the tricky part (and my problems):

现在棘手的部分（和我的问题）：

Problem 1:

问题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).

使用 .NET Clipboard 类，我无法从剪贴板中获取精确复制的数据：数据是相同的，但不知何故格式被扭曲了（整个文档的字体似乎变得粗体，而且有点多）不可读而它们不可读）；如果我使用 mspaint.exe 从剪贴板粘贴，粘贴的图像是正确的（正如我希望的那样）。

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.

我反汇编了 mspaint.exe 并找到了一个函数，它正在使用 (OleGetClipboard) 从剪贴板获取数据，但我似乎无法在 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.

我尝试过的其他东西是剪贴板 WINAPI（OpenClipboard、GetClipboardData、CF_ENHMETAFILE），但结果与使用 .NET 版本相同。

Problem 2:

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

使用 range 和 CopyPicture，如果 Excel 表中有任何图像，则这些图像不会与周围数据一起复制到剪贴板。

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:

使用 WINAPI 从剪贴板获取数据成功，但质量不佳。来源：

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)

有人有解决方案吗？（顺便说一句，我正在使用 .NET 2.0）

采纳答案by Joe Erickson

SpreadsheetGear for .NETwill do it.

.NET 的 SpreadsheetGear可以做到。

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

您可以在此处查看我们的 ASP.NET（C# 和 VB）“ Excel 图表和范围成像示例”示例，如果您想试用，请在此处下载免费试用版。

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.

SpreadsheetGear 还适用于 Windows 窗体、控制台应用程序等......（您没有指定您正在创建的应用程序类型）。还有一个 Windows 窗体控件可以在您的应用程序中显示工作簿，如果这是您真正想要的。

Disclaimer: I own SpreadsheetGear LLC

免责声明：我拥有 SpreadsheetGear LLC

回答by Dirk Vollmar

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 Picturewith the options as shown on screenand Bitmapselected, then I used the following code to save the clipboard data:

我在 Excel 中手动选择了一个范围，选择了复制为图片，并选择了屏幕上显示的选项并选择了位图，然后我使用以下代码保存剪贴板数据：

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.

关于您的第二个问题：据我所知，在 Excel 中不可能同时选择单元格范围和图像。如果您想同时在图像中获取两者，您可能需要将 Excel 工作表打印为图像/PDF/XPS 文件。

回答by Joe Erickson

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:

因为asp.net线程没有正确的ApartmentState来访问Clipboard Class，所以必须在新线程中编写访问Clipboard的代码。例如：

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

回答by Bernd Wechner

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.

有趣的是，我已经成功地在 STA 隔间中这样做了一段时间。我编写了一个每周运行的应用程序，并邮寄出项目状态报告，包括我使用 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).

昨晚这失败了，所有图表都返回了空值。我今天正在调试，没有找到任何解释，只是方法 Clipboard.GetImage() 突然返回 null 而它没有。通过在此调用中设置断点，我可以有效地证明（通过在 MS-Word 中按 CTRL+V）图像确实在剪贴板中。唉，继续在 Clipboard.GetImage() 上返回 null（无论我是否像这样窥探）。

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

我的代码作为控制台应用程序运行，并且 Main 方法具有 [STAThread] 属性。我将它作为 Windows 窗体应用程序调试（我的所有代码都在一个库中，我只有两个前端）。

Both return null today.

今天两者都返回 null。

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.

出于兴趣，我按照概述将图表提取器分离到一个线程中（并注意 thread.ApartmentState 已弃用），它运行良好，但仍然返回空值。

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

好吧，我放弃了，做了任何 IT 极客都会做的事情，重新启动。

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!

瞧……一切都很好。想想看……这就是我们讨厌计算机、Microsoft Windows 和 Microsoft Office 的原因吗？嗯......有一些事情，一些完全短暂的事情可能会发生在你的 PC 上，导致 Clipboard.GetImage() 失败！

回答by mike01010

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.

在将图元文件转换为位图格式时，这是 GDI+ 的一个错误。
许多显示带有文本的图表的 EMF 都会发生这种情况。要重新创建，您只需在 Excel 中创建一个图表，显示其 X 和 Y 轴的数据。将图表复制为图片并粘贴到 word 作为图元文件。打开 docx，您将在 media 文件夹中看到一个 EMF。如果您现在在任何将其转换为位图的基于 Windows 的绘图程序中打开该 EMF，您将看到扭曲，特别是文本和线条变得更大和扭曲。不幸的是，这是微软不太可能承认或做任何事情的问题之一。让我们希望 Google 和 Apple 也能尽快接管办公/文字处理领域。

回答by Interarticle

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.

如果你不介意Linux（风格），你可以先使用OpenOffice（或LibreOffice）将xls 转换为pdf，然后使用ImageMagic 将pdf 转换为图像。可以在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.pdfand http://imagemagick.net/script/api.php#dot-net

此外，上面提到的两个程序似乎都有 .Net API。请参阅：http: //www.opendocument4all.com/download/OpenOffice.net.pdf和 http://imagemagick.net/script/api.php#dot-net