asked on

Exporting image to excel from php document

Hi experts, I have a button on my form where if clicked it exports all data from my sql server table to an excel sheet. All of the data is exported fine besides my image. On my form where the image is submitted, the image is saved in a folder called "Upload" where the name is stored in the database in the column "FILES". I will post the code where I am trying to export the image. On other attempts to retrieve the images for display I am able to, but not for exporting.

 
	<td><img src="'http://localhost/noncompliance/Upload/<?php echo $result['FILES'];?>" alt=" " height="95" width="95"></td>

Open in new window

Ray Paseur

Hi, Peter. Can you please go into the generated HTML document and show us the exact HTML that comes from the line you posted above? Thanks. I am wondering if maybe the extraneous quote mark is messing things up. Chrome seems to tolerate it, but I'm not sure about other browsers.

Julian Hansen

If the excel document is being generated with the image link (not the image) then when the document is opened Excel will need to be able to access the link for the image. If the link is not available - image will not display.

Remember an html page does not have images in the document - only link to the image.

I don't know what you are using to generate the Excel file but it would need to support embedding images if you want the document to be portable out of range of the server.

peter Ojeda

ASKER

Hi Ray sorry I am not familiar with what you are referring to when you say generated HTML document, but I have attached the code that I've used to perform the export function

<form action="ncconsumercomplainttexport.php">


<?php

	ini_set('display_errors', 1);
	error_reporting(~0);

	ini_set('display_errors', 1);
	error_reporting(~0);

	$serverName = "my_server";
	$userName = "";
	$userPassword = '';
	$dbName = "my_db";

	$connectionInfo = array("Database"=>$dbName, "UID"=>$userName, "PWD"=>$userPassword, "MultipleActiveResultSets"=>true);

	$conn = sqlsrv_connect( $serverName, $connectionInfo);

	if( $conn === false ) {
		die( print_r( sqlsrv_errors(), true));
	}

			$filename = "Non-Compliance_Consumer_Complaints".date('Y-m-d h:i:sa').".xls";

		header("Content-Disposition: attachment; filename=\"$filename\"");
		header("Content-Type: application/vnd.ms-excel");


  $stmt = "SELECT * FROM NC_CONSUMER_COMPLAINT";
   $query = sqlsrv_query($conn, $stmt);

?>
<table width="150" border="1" name="results">
  <tr>
	<th width="91"> <div align="center">ID</div></th>
    <th width="91"> <div align="center">STATUS</div></th>
    <th width="91"> <div align="center">DATE_CLOSED</div></th>
    <th width="200"> <div align="center">DATE</div></th>
    <th width="91"> <div align="center">SOURCE_OF_CLAIM_ORIGIN </div></th>
    <th width="91"> <div align="center">COST_OF_GOODS</div></th>
    <th width="91"> <div align="center">CUSTOMER_NAME </div></th>
    <th width="98"> <div align="center">COST_OF_SH </div></th>
    <th width="98"> <div align="center">ITEM_NAME </div></th>
    <th width="98"> <div align="center">FGSKU </div></th>
    <th width="98"> <div align="center">DESCRIPTION_OF_CLAIM_VARIANCE </div></th>
    <th width="98"> <div align="center">INTERIM_ACTION_TAKEN </div></th>
    <th width="98"> <div align="center">SUPPLIER </div></th>
    <th width="98"> <div align="center">COMMENTS </div></th>
    <th width="98"> <div align="center">FORMAL_CORRECTIVE_ACTION_REQUIRED </div></th>
    <th width="98"> <div align="center">CAPA_ID </div></th>
    <th width="98"> <div align="center">EMAIL_SENT_TO </div></th>
    <th width="98"> <div align="center">EMAIL_NOTES </div></th>
	<th width="98"> <div align="center">FILES </div></th>
  </tr>

<?php
while($result = sqlsrv_fetch_array($query, SQLSRV_FETCH_ASSOC))
{
?>
  <tr>
	<td align="left"><div align="center"><?php echo $result["ID"];?></div></td>
<td><?php echo $result["STATUS"];?></td>
    <td><?php echo $result["DATE_CLOSED"] ->format("Y-m-d") ;?></td>
    <td><?php echo $result["DATE"]->format("Y-m-d") ;?></td>
    <td><?php echo $result["SOURCE_OF_CLAIM_ORIGIN"];?></td>
    <td align="right"><?php echo $result["COST_OF_GOODS"];?></td>
	
    <td align="right"><?php echo $result["CUSTOMER_NAME"];?></td>
    <td align="right"><?php echo $result["COST_OF_SH"];?></td>   
    <td align="right"><?php echo $result["ITEM_NAME"];?></td>   
    <td align="right"><?php echo $result["FGSKU"];?></td>   
    <td align="right"><?php echo $result["DESCRIPTION_OF_CLAIM_VARIANCE"];?></td>   
    <td align="right"><?php echo $result["INTERIM_ACTION_TAKEN"];?></td>   
    <td align="right"><?php echo $result["SUPPLIER"];?></td>   
    <td align="right"><?php echo $result["COMMENTS"];?></td>   
    <td align="right"><?php echo $result["FORMAL_CORRECTIVE_ACTION_REQUIRED"];?></td>   
    <td align="right"><?php echo $result["CAPA_ID"];?></td>   
    <td align="right"><?php echo $result["EMAIL_SENT_TO"];?></td>   
    <td align="right"><?php echo $result["EMAIL_NOTES"];?></td>   
	<td><img src="Upload/<?php echo $result['FILES'];?>" alt=" " height="95" width="95"></td>  

	

  </tr>
<?php
}
?>
</table>


<?php
sqlsrv_close($conn);
?>
  </table>

</form>

Open in new window

Ray Paseur

OK, now that I see the bigger picture, let's talk through how to debug this,

The first thing we want to do is be able to look at the output that is created when you run the PHP script. At present it's going out as an attachment, but we want to change that so we can visualize the output document. To do that, comment out lines 27 & 28. Without those, the document will be sent to the browser and you can use view source. Please do that, then copy the page from view source and post that here in the code snippet.

Julian Hansen

That is not an Excel document. What you are "exporting" to Excel is just HTML that Excel is clever enough to read.

In that HTML document you have a link to your image - the image is not actually in the document so Excel is going to have to fetch that image from the server just as the browser would.

If the server is not visible to Excel (or to be more precise the images on it) then they won't display in the document.

Solution
1. Make the images publicly available so Excel can find them
2. Find a true Excel export library that embeds images in the exported Excel file

Ray Paseur

A little more about wanting a fully-qualified URL that points to the image on an internet-connected server... This is the "new version" of that line:

<td><img src="Upload/<?php echo $result['FILES'];?>" alt=" " height="95" width="95"></td>

My guess is that when you try to use a local URL like this, you're not getting something that Excel can locate the same way that a browser might. You would need a URL that starts with http:// pointing to the image.

You might also consider using a CSV file for the Excel document. It may be a little easier to work with, and the CSV extension is almost universally associated with Excel.

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Julian Hansen

He has that in his OP

<td><img src="'http://localhost/noncompliance/Upload/<?php echo $result['FILES'];?>" alt=" " height="95" width="95"></td>

But it is localhost and the code seems to indicate that he added the http://... afterwards.

Even if he puts a full URL in there - if the document is being mailed or moved to a different environment localhost is going to fail as well.

Ray Paseur

Yes, localhost was in the original. When we got to the PHP code, we can see that it is not using a fully-qualified URL. That's what I was trying to explain here. And that's what I used in the code example that generates the CSV.