PHPSpreadsheet - How do i place a image from link into my excel file?

Question

I would like to place a image in cell a1 from my excel file.

I tryed different things from phpspreadsheet website but all without succes. Like this example from them that does'nt work.:

$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
$drawing->setName('Paid');
$drawing->setDescription('Paid');
$drawing->setPath('https://pngimage.net/wp-content/uploads/2018/06/rfhnbyrb-png-6.png');
$drawing->setCoordinates('B15');
$drawing->setOffsetX(110);
$drawing->setRotation(25);
$drawing->getShadow()->setVisible(true);
$drawing->getShadow()->setDirection(45);

If i create a black bar with text inside it it works with this code.:

$gdImage = @imagecreatetruecolor(2000, 20) or die('Cannot Initialize new GD image stream');
$textColor = imagecolorallocate($gdImage, 255, 255, 5);
imagestring($gdImage, 1, 5, 5, date("F Y"), $textColor);

//  Add the In-Memory image to a worksheet
$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing();
$drawing->setName('In-Memory image 1');
$drawing->setDescription('In-Memory image 1');
$drawing->setCoordinates('A1');
$drawing->setImageResource($gdImage);
$drawing->setRenderingFunction(\PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::RENDERING_JPEG);
$drawing->setMimeType(\PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::MIMETYPE_DEFAULT);
$drawing->setHeight(36);
$drawing->setWorksheet($spreadsheet->getActiveSheet());

what i try i don't get a image in my excel file, please who wants to help me.

score 21 · Accepted Answer · answered Feb 20 '19 at 12:58

21

I got a solution for you. But unfortunately i couldn't find a way to use a url but an image localy. take a look.

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
$drawing->setName('Paid');
$drawing->setDescription('Paid');
$drawing->setPath('images/paid.png'); // put your path and image here
$drawing->setCoordinates('B15');
$drawing->setOffsetX(110);
$drawing->setRotation(25);
$drawing->getShadow()->setVisible(true);
$drawing->getShadow()->setDirection(45);
$drawing->setWorksheet($spreadsheet->getActiveSheet());

$writer = new Xlsx($spreadsheet);
$writer->save('image.xlsx');

It's working just fine, enjoy :)

answered Feb 20 '19 at 12:58

mindmaster

1,828
12
22

Oke thx, i will try that tomorrow, today i haven't got the time for it – Pascal Feb 21 '19 at 15:57
That works great THX! little question, i have a blue logo horizontal placed, but i want actual motn and year on top of it, is that possible? i know with GDimage in spreadsheet its possible, but i don't if this is possible with this. – Pascal Feb 21 '19 at 19:41
I updated my code with a code what i mantiond in my previous question. – Pascal Feb 21 '19 at 19:51
This better answer but does not solve the answer that is an image of a url – MrEduar Sep 16 '20 at 13:42
I have a template with preset images, each image has a specific title (ie: imageBanner_1). When I import the template I want to be able to replace the images based on their title. Is this possible? Ideally, the image swap would simply just replace the existing image in the same location with all the same properties, stylings, etc. I haven't seen any method to `replace` a picture. – rolinger Jul 29 '22 at 13:51

score 4 · Answer 2 · answered Sep 25 '19 at 12:11

$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing();
                    $drawing->setImageResource(imagecreatefrompng('https://pngimage.net/wp-content/uploads/2018/06/rfhnbyrb-png-6.png');
                    $drawing->setCoordinates($cell->getCoordinate());
                    $drawing->setWidthAndHeight(50, 50);
                    $drawing->setRenderingFunction(\PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::RENDERING_JPEG);
                    $drawing->setMimeType(\PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::MIMETYPE_DEFAULT);
                    $drawing->setOffsetX(110);
                    $drawing->setRotation(25);
                    $drawing->getShadow()->setVisible(true);
                    $drawing->getShadow()->setDirection(45);
                    $drawing->setWorksheet($csv->getActiveSheet());

Why do you use RENDERING_JPEG and MIMETYPE_DEFAULT if it is a png image? — MrEduar, Sep 16 '20 at 13:43
Hi Naveen, please consider explaining your answer rather than just posting code. It will be more helpful that way and easier to learn from. — Christian, Mar 21 '23 at 02:05

score 2 · Answer 3 · answered Apr 15 '20 at 12:34

I am using phpspreedsheet in PHP 7.2 with latest phpspreedsheet version.

<?php

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

$IMG = 'https://pngimage.net/wp-content/uploads/2018/06/rfhnbyrb-png-6.png';
$row_num = 2;
if (isset($IMG) && !empty($IMG)) {
    $imageType = "png";

    if (strpos($IMG, ".png") === false) {
        $imageType = "jpg";
    }

    $drawing = new MemoryDrawing();
    $sheet->getRowDimension($row_num)->setRowHeight(80);
    $sheet->mergeCells('A'.$row_num.':H'.$row_num);

    $gdImage = ($imageType == 'png') ? imagecreatefrompng($IMG) : imagecreatefromjpeg($IMG);
    $drawing->setName('Company Logo');
    $drawing->setDescription('Company Logo image');
    $drawing->setResizeProportional(false);
    $drawing->setImageResource($gdImage);
    $drawing->setRenderingFunction(\PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::RENDERING_JPEG);
    $drawing->setMimeType(\PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing::MIMETYPE_DEFAULT);
    $drawing->setWidth(211);
    $drawing->setHeight(40);
    $drawing->setOffsetX(5);
    $drawing->setOffsetY(30);
    $drawing->setCoordinates('C'.$row_num);
    $drawing->setWorksheet($spreadsheet->getActiveSheet());
    $row_num++;
}

$writer = new Xlsx($spreadsheet);
$writer->save('hello world.xlsx');

Why do you use RENDERING_JPEG and MIMETYPE_DEFAULT if it is a png image? — MrEduar, Sep 16 '20 at 13:43
Hi Atanu please consider explaining your answer rather than just posting code. It will be more helpful that way and easier to learn from. — Christian, Mar 21 '23 at 02:05

PHPSpreadsheet - How do i place a image from link into my excel file?

3 Answers3

Linked