How to retrieve bitmap image from excel using oledbadapter in vb.net

Question

0.00/5 (No votes)

See more:

Hello right now I have a program that uses the oledpadapter to retrieve information from an excel file and displays the data into a datagridview. I'm hoping to find a way to retrieve a bitmap image from my excel file and display it into my datagridview. Is there an easy way of doing this? I greatly appreciate any help or suggestions anyone can give!

Posted 6-Aug-13 10:01am

CAS1224

Add a Solution

Comments

Appokea 19-Feb-16 4:38am

As mentioned below this task will require some alternative approach from OLEDB.
What I did in the past is I used a VB.NET library for excel processing, I would iterate through all the images in my spreadsheet file like the following:

Dim workbook = ExcelFile.Load("Sample.xlsx")

For Each worksheet In workbook.Worksheets
For Each picture In worksheet.Pictures
Dim picPosition As ExcelDrawingPosition = picture.Position
Dim picStream As MemoryStream = picture.PictureStream
' ...
Next
Next

Also I should point out that you can actually directly export your excel file into a DataGridView control with VB.NET code. Beside excel cells data this will also export hyperlinks, formattings, images, etc.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-08-06T10:44:00

Solution 1

In my opinion it's not possible because of the way in which Excel stores images in its files. Have a look here: http://stackoverflow.com/questions/2632715/retrieve-pictures-from-excel-file-using-oledb[^]

Posted 6-Aug-13 10:44am

Maciej Los

**Zoltán Zörgő** · Answer 2 · 2013-08-06T10:45:00

Solution 2

Not this way, since the ole interface is giving you access to the cells only. The images are not in the cells, they are separate objects shown above the actual Excel grid. But you can use Excel interop[^] or third party libraries that can read excel files without excel being installed. But with this approach, you need to parse the sheet cell by cell - object by object, you won't be able to see it as a table. If you are using Excel 2007 or above, there is a "Table" concept, you could also tale advantage of (start with this VBA intro: http://www.jkp-ads.com/Articles/Excel2007TablesVBA.asp[^], but you will find it's VB.NET equivalent also).

Posted 6-Aug-13 10:45am

Zoltán Zörgő

Comments

Maciej Los 6-Aug-13 17:15pm

Zoltan, i really do appreciate your answer, but you need to know that fetching images from Excel using interop is a torture, especially when picture is stored in Shape object (Excel 97-2003)...
A5 - for alternative way to fetch images from Excel (even if it's not the best and the simplest way).

Zoltán Zörgő 7-Aug-13 8:25am

Hi, well, I am using interop often, so I know how it works. But I can't call it a torture. You can use the TopLeft and BottomRight properties to get shapes position. But you can use third party components (like this: http://epplus.codeplex.com/) that are able to read (and write) excel files without interop - pictures too. This one is for 2007 and up, but you can find some, that can do the same with the old format too.

Maciej Los 7-Aug-13 8:38am

Maybe my words were to strong in this context, but... how do you recognize which picture belongs to every single record? It's a real torture ;)
Cheers!
Maciej

Zoltán Zörgő 7-Aug-13 8:40am

Well, if the table is properly formatted, the images should fit into their rows. If they don't - well, than it is really a torture - but it would be for a human player too, not only for a code.

Maciej Los 7-Aug-13 9:01am

Agree ;)