Cannot display image in Excel.

Mohd Amir Ansari · April 22nd, 2014, 05:22 AM

Hi,

I am generating a report in Excel where in I am displaying the logo of the company at the top. What I have done is that I have converted the image in binary format and saved the binary data in the database. While generating the report,I fetch the binary data from the database and converted the binary data back to image format and displaying it every time the report is being generated. Everything is working fine. The problem is Image is not being displayed.

I have tried using Server.MapPath and also tried defining the path of the image in web.config file.

After deployment on the IIS, I am getting the logo when I access my project on the server machine. But, when I access it from other machine, the logo is not getting displayed.

Image is displayed with the alternative text:

"The linked image cannot be displayed. The file may have been removed,
renamed, or deleted. Verify that the link points to the correct file and location."

Here is my controller code where Path of the logo is specified in web.config file:

Code:

string filePath = System.Configuration.ConfigurationManager.AppSettings["LogoPath"];
                        
System.Drawing.Image imgPhoto = System.Drawing.Image.FromFile(filePath);
                        byte[] image = getImage(filePath);
                        
string imagepath = string.Format("<img src='{0}' width='{1}' height='{2}'/>", filePath, imgPhoto.Width, imgPhoto.Height);
                        imgPhoto.Dispose();
                        
                        HttpContext.Response.Write("<Td>");
                        HttpContext.Response.Write(imagepath);
                        HttpContext.Response.Write("</Td>");
                        HttpContext.Response.Write("</TR>");

I am using MVC4 and SQL server 2008r2.

NOTE: As per the requirement I don't have to use any third party support or dll.

Any idea? Please help me...

Thanks in advance.

June 12th, 2014, 09:15 AM

The images are stored on a sheet in the workbook, for example on the sheet named "Sheet2"

This technique requires three columns, for example:
Column A, from A2 to A5, the names of pictures (the value that will be used as trigger), this range will be named, for example Imag. For versions prior to Excel 2007, Insert/Name/Define or, since version 2007 Formula tab/Define a name (directly into the address bar). This naamed range will provide data to a validation list.
Column B, enter in each cell, the address of the cell where the corresponding photo is stored (sheet name and cell address).
Column C, the cells will be resized to store the pictures. Insert each photo in a separate cell.
On the sheet named "Sheet1" create a validation list, e.g in cell A3, click on Insert/Name/Define or Data/ Data Validation tab.
This validation list can be replaced by a formula to define the mean or sort items.. The result ... would call the related image file.

Excel 2007 and 2010

Create a field
Use the Formula tab/ Define a name for the field (in our case "Photo") and in "refers to: ", paste this formula:
=INDIRECT(VLOOKUP(Sheet1!$A$3,Sheet2!$A$2:$B$5,2,0 ))

You need to activate the camera feature of Excel:
Click the Office button.
Click on the "Excel Options".
Select the "Customize" menu.
In the "Choose commands in the following categories:" menu and select "Commands not present in the Ribbon".
Search for "Camera" from the dropdown menu and select the line
Click on "Add" button appears in the right column
Click on OK
The camera is now available from the Quick Access toolbar.

Activate a cell in Sheet2 (one containing a photo)
Click on the camera icon on the Quick Access toolbar
Back to Sheet1 and draw the field that will display photos
Without disabling the field, in the formula bar enter =Photo (the name that was given to the field).

Excel 2003 and earlier

You may proceed in the same way, or simply copy/paste a photo from Sheet2 to sheet1 to create a field, while the photo is active (Insert/Name/Define) give a name to field (here named "Photo") and in the "refers to" field paste this formula:
=INDIRECT(VLOOKUP(Sheet1!$A$3,Sheet2!$A$2:$B$5,2,0 ))
In the formula bar refer to the following field: =Photo
Save the file.

Using the OFFSET function

Let us make use of the above example: In Sheet2, column A, from A2 to A5 are the names of pictures. The range is named Imag and it will provide data to a validation list.
In this example, column B is unnecessary and you can removed it.
Create a new field that will be called "Photo_Bis" to avoid any interference with the previously named field in the first example.

Excel 2007 and 2010

Create a field:
Via the Formula tab > Define name and give a name to the field (in our case "Photo") and in "refers to" field paste this formula:

=OFFSET(Sheet2!$C$2,MATCH(Sheet3!$A$4,Sheet2!$A$2: $A$5,0)-1,)

As in the previous case, you need to activate the Camera feature of Cxcel:
Click the Office button.
Click on the "Excel Options".
Select the "Customize" menu.
In the "Choose commands in the following categories:" menu and select "Commands not present in the Ribbon".
Search for "Camera" from the dropdown menu and select the line
Click on "Add" button appears in the right column
Click on OK
The camera is now available from the Quick Access toolbar.

Activate a cell in Sheet2 (one containing a photo)
Click on the camera icon on the Quick Access toolbar
Back to Sheet1 and draw the field that will display photos
Without disabling the field, in the formula bar enter =Photo_Bis (the name that was given to the field).

Excel 2003 and earlier.

You may proceed in the same way, or simply copy/paste a photo from Sheet 2 to sheet 3 to create a field, while the photo is active (Insert/Name/Define) give a name to field (here named "Photo_Bis") and in the "refers to" field paste this formula:
=OFFSET(Sheet2!$C$2,MATCH(Sheet3!$A$4,Sheet2!$A$2: $A$5,0)-1,)
In the formula bar make reference to the =Photo_Bis field.
Save the file.