Add images to a excel file using PHP

Question

I'm using the following function to add content to an excel file using PHP :

function __writeString($row, $col, $value ) {
        $L = strlen($value);
        $this->file .= pack("ssssss", 0x204, 8 + $L, $row, $col, 0x0, $L);
        $this->file .= $value;
}

I would like to know how I can add images in my cells the same way, by supplying its url as a value for instance.

Answer 1

Take a look at this: PHPExcel. It will provide you will all the tools you need to read and write Excel from PHP.

Once you have PHPExcel installed, you can use something like this to insert:

$objDrawing = new PHPExcel_Worksheet_Drawing();

$objDrawing->setPath('./images/picture.png');

$objDrawing->setCoordinates('A11');

Answer 2

"Trigun", your suggestion really helped. I was able to download the latest PHPExcel Classes from https://github.com/PHPOffice/PHPExcel and was up and running in no time. However, it took some extra time to figure out how to add an image to the excel file. Your explanation didn't help much.

Here is a complete description of how to do it:

First, download the library and place it in a logical place on your website:

sites/all/libraries/phpexcel/Classes

Now you create your PHP file anywhere in your website that you would like it to be and add the following into the file:

1) Allow the error messages to be printed on the screen:

error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br \>');

2) Include the Excel Classes file:

/** Include PHPExcel */
require_once $_SERVER["DOCUMENT_ROOT"] . "/sites/all/libraries/phpexcel/Classes/PHPExcel.php";

3) Create the "PHPExcel" object:

// Create new PHPExcel object
echo date('H:i:s') , " Create new PHPExcel object" , EOL;
$objPHPExcel = new PHPExcel();

4) Set some Excel metadata such as title and description.

// Set document properties
echo date('H:i:s') , " Set document properties" , EOL;
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
->setLastModifiedBy("Maarten Balliauw")
->setTitle("PHPExcel Test Document")
->setSubject("PHPExcel Test Document")
->setDescription("Test document for PHPExcel, generated using PHP classes.")
->setKeywords("office PHPExcel php")
->setCategory("Test result file");

5) Add some data to the "B1" cell:

// Add some data
echo date('H:i:s') , " Add some data" , EOL;
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('B1', 'Hello world!')

6) Create a "drawing" object that we can load our image into. Remember to replace the image URL with a valid image URL in your web server:

// Add a drawing to the worksheet
echo date('H:i:s') , " Add a drawing to the worksheet" , EOL;
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Thumb');
$objDrawing->setDescription('Thumbnail Image');
$objDrawing->setPath($_SERVER["DOCUMENT_ROOT"] . '/sites/default/files/product-images/10098.jpg');
$objDrawing->setHeight(21);

7) Copy the image into the "A1" cell on our "$objPHPExcel" object.

$objDrawing->setCoordinates('A1');
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

8) Saving the "$objPHPExcel" object as an Excel file format.

// Save Excel 95 file
echo date('H:i:s') , " Write to Excel5 format" , EOL;
$callStartTime = microtime(true);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save(str_replace('.php', '.xls', __FILE__));

9) Print some mildly useful information to the screen:

$callEndTime = microtime(true);
$callTime = $callEndTime - $callStartTime;

echo date('H:i:s') , " File written to " , str_replace('.php', '.xls', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
echo 'Call time to write Workbook was ' , sprintf('%.4f',$callTime) , " seconds" , EOL;
// Echo memory usage
echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;


// Echo memory peak usage
echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL;

// Echo done
echo date('H:i:s') , " Done writing files" , EOL;
echo 'Files have been created in ' , getcwd() , EOL;

This is the whole thing!