Tuesday, January 13, 2009

Reading Image from Excel file using C# .Net

Office Primary Interop Assemblies

These assemblies/dlls contain classes which help us to work or interact with the Office files through our .Net programs.They maintain clean object model which represents the structure of office files like .xls,.doc etc...To work with these different office file formats we have to use separate office interop assemblies.

These assemblies are different for different office versions.OfficeXP uses 10.0.0.0, Office2003 uses the interop version of 11.0.0.0 and Office 2007 uses the version of 12.0.0.0 etc...

More details here.

Microsoft Excel interop assembly.

This assembly gives access to excel files.We can interact with the excel workbooks,worksheets,cells etc.We can do a bunch of operations such as creating a work book , referring the cells inside a sheet and lot more using this.The main interop dll of excel is Microsoft.Office.Interop.Excel.dll .It contains the excel object model.The main classes which we need to interact are ApplicationClass,Workbook & Worksheet

Application flow

If you want to use the excel interop you have to refer the concerned assembly in your application.Then create instance of ApplicationClass.Using the ApplicationClass object you can create excel Workbook which is equivalent to the actual .xls or .xlsx file.Workbook contains a collection of Worksheets which again contains cells,rows and columns.We can set or get the values from these cells as per the requirement.

Some links to create and read Excel files using office interop.

Creation

Reading

Full list

The normal reading of cells will go smoothly with these things.But when we come to reading of images we can not do that very easily because the images are not bound to the cells.More specifically ,they are bound to the sheet.Here is one method to read images from worksheet

 

Basic idea of Reading Image

Let us first check the object model.These is no property collection in the Worksheet interface to get images directly.We have to use the method

workSheet.Pictures("pictureName")

Thus this method need the name of the image.We have to iterate through the WorkSheet.Shapes collection, if you don't know the image name in advance.


Once we execute the Pictures method we gets the image as object Picture interface.This again don't have any direct method which returns the image as useful.


We have to copy the image into clipboard using the method

pict.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlBitmap);
 

Yes we now have the image in clipboard.Get the image from there and show or save as per the requirement.

if (Clipboard.ContainsImage())
{
Image img=Clipboard.GetImage();
pictureBox.Image = img;
}

Sample


Here is a small sample which reads first image from a .xls file and shows in a picture box.I have used Office 2003 interop assemblies (11.0.0.0)


ExcelImageRead.zip

11 comments:

tony.hogbom said...

Great post! I need to extract images from an excel document thats being uploaded from a web form. Would it be possible to run your code on a web server, and if so, how?

pierre.garnero said...

Hello.

I experience 2 issues with this code.
1 - On Excel 2003, if I insert images (bmp or jpeg) around 40kb, the resulting saved images (jpeg) are around 4k ... Is there any trick to keep the initial resolution ?
2 - If the Excel file contains "a lot" of images (more than 100) most of the saved images are around 1k (and debugger show that image after Clipboard.GetImage() have heigth=1).

Now, forgiving C#, if I save the document as (HTML or Web page), I get all the images (duplicated, 1 low resolution image, the other one identical to the image inserted in the document). So, clearly it is possible to extract inserted picture without any resolution loss but...how ?

pierre.garnero said...

Hello.

I experience 2 issues with this code.
1 - On Excel 2003, if I insert images (bmp or jpeg) around 40kb, the resulting saved images (jpeg) are around 4k ... Is there any trick to keep the initial resolution ?
2 - If the Excel file contains "a lot" of images (more than 100) most of the saved images are around 1k (and debugger show that image after Clipboard.GetImage() have heigth=1).

Now, forgiving C#, if I save the document as (HTML or Web page), I get all the images (duplicated, 1 low resolution image, the other one identical to the image inserted in the document). So, clearly it is possible to extract inserted picture without any resolution loss but...how ?

Pierre

Chirag said...

hii !! nice post ! I had a question.
I have a worksheet containing a table of data. One of the columns contains images( each row may or may not contain an image ). Is there any way I can figure out which row I am picking the image from ?

pierre.garnero said...

I think you could use Shape.TopLeftCell.Row property.

Alex said...

I know a lot of good programs and one of them pleased me. It to my surprise decided all my old problems with damaged excel files. Perhaps it will help in similar conditions, for example in this - recover Excel.

Guilherme Bazilio said...
This comment has been removed by the author.
Guilherme Bazilio said...

What if I had previous data on my clipboard? My private data will be lost. It'll be overwritten by the image.

What to do in that case?

stacy said...

This is my Good luck that I found your post which is according to my search and topic, I think you are a great blogger, thanks for helping me outta my problem..
Dissertation Writing Service

josiem said...

I must show some thanks to this writer just for bailing me out of this particular issue. As a result of browsing through the net and seeing views which are not productive, I assumed my life was well over. Being alive devoid of the solutions to the issues you have fixed as a result of your entire post is a serious case, and ones that could have negatively damaged my entire career if I had not come across your web site. That expertise and kindness in dealing with a lot of stuff was crucial. I am not sure what I would’ve done if I had not encountered such a subject like this. I’m able to at this time look forward to my future. Thanks so much for your skilled and sensible guide. I will not hesitate to suggest your blog to anybody who needs and wants support about this topic. Cell Phone Lookup

Doctor'sDad said...

Hi, Many thanks for this I have already used it successfully to solve one problem. Now, however, I want to do the reverse trick.

I want to locate an image in the shape collection, then replace it with another one that I already have in memory.

Do you have any idea how I can do this?