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

10 comments:

Unknown 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?

Unknown 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 ?

Unknown 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

Unknown 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 ?

Unknown said...

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

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?

Anonymous 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?

tcs said...

thanks seems like most of us are trying to get the image file from excel

nitin83sharma said...

i am having a erroe in this code
errer line is:
ApplicationClass app = new ApplicationClass();
and error is:
Error 1 Interop type 'Microsoft.Office.Interop.Excel.ApplicationClass' cannot be embedded. Use the applicable interface instead.