StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POProgrammatically (C#) convert Excel to an image
text
Body
copied!<p>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.</p> <p>So my current work-around is as follows:</p> <ul> <li>Open Excel file using Microsoft Interop;</li> <li>Find out the max range (that contains data);</li> <li>Use the CopyPicture() on that range, which will copy the data to the Clipboard.</li> </ul> <p>Now the tricky part (and my problems):</p> <p><strong>Problem 1:</strong></p> <p>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). </p> <p>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.</p> <p>Other things I tried were the Clipboard WINAPI's (OpenClipboard, GetClipboardData, CF_ENHMETAFILE), but the results were the same as using the .NET versions.</p> <p><strong>Problem 2:</strong></p> <p>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.</p> <p><strong>Some of the source code</strong></p> <pre><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(); } </code></pre> <p>Getting data from the clipboard using WINAPI succeeds, but with bad quality. Source:</p> <pre><code>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(); } } </code></pre> <p>Anyone got a solution? (I'm using .NET 2.0 btw)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload