Adding image to the spreadsheet

[Gyvastis]

Is there a possibility to embed an image into the excel sheet?

[Gyvastis]

Is anyone interested in this feature? I've implemented a solution and could finish up for a pull request.

[joenilson]

Yes, can be interesting to embed a png graph into the excel sheet!

[segunmicheal27]

This is absolutely perfect solution, but i will love to know how can i add image to the sheet both header or body

[FlorianChretien]

+1

[Gyvastis]

You can take at the extended repo with image support here. Use XLSWriterPlus class instead of XLSWriter. Check out the class file for more details.

[FlorianChretien]

The simple fact of doing --> $writer->addImage('images / logo.png', 1); I made a error 500. Despite having included the new file like this --> include_once("xlsxwriterplus.class.php");

Do you have an idea ? :/

[bartmika]

Massive +1

[bartmika]

Hi @Gyvastis, I have used your library but it does not seem to be working. May you please help? I got the page to successfully render without error but when I open the spreadsheet it does not work. Are you able to upload a sample test file which works? Here is my attempt:

// // DISPLAY ALL ERRORS error_reporting(E_ALL); ini_set('display_errors', 'On');

// TO RUN: // http://localhost/PHP_XLSXWriterPlus/examples/ex08-image.php

set_include_path( get_include_path().PATH_SEPARATOR."..");

include_once("xlsxwriter.class.php"); // FIX DEPENDENCY ERROR. include_once("xlsxwriterplus.class.php"); // LOAD EXTENDED LIBRARY.

$writer = new XLSWriterPlus(); // USING THE EXTENDED LIBRARY.

$header = array( 'c1-text'=>'string',//text 'c2-text'=>'@',//text 'c3-integer'=>'integer', 'c4-integer'=>'0', 'c5-price'=>'price', 'c6-price'=>'#,##0.00',//custom 'c7-date'=>'date', 'c8-date'=>'YYYY-MM-DD', ); $rows = array( array('x101',102,103,104,105,106,'2018-01-07','2018-01-08'), array('x201',202,203,204,205,206,'2018-02-07','2018-02-08'), array('x301',302,303,304,305,306,'2018-03-07','2018-03-08'), array('x401',402,403,404,405,406,'2018-04-07','2018-04-08'), array('x501',502,503,504,505,506,'2018-05-07','2018-05-08'), array('x601',602,603,604,605,606,'2018-06-07','2018-06-08'), array('x701',702,703,704,705,706,'2018-07-07','2018-07-08'), );

$writer->writeSheetHeader('Sheet1', $header); foreach($rows as $row) $writer->writeSheetRow('Sheet1', $row);

$writer->addImage("my_logo.PNG", 1); // HERE IS WHERE I AM TESTING.

//$writer->writeSheet($rows,'Sheet1', $header);//or write the whole sheet in 1 call

$writer->writeToFile('xlsx-image.xlsx'); // CHECK FILE ONCE CREATED. //$writer->writeToStdOut(); //echo $writer->writeToString();

[Gyvastis]

@bartmika I've updated the code. You should renew your xlsxwriterplus.class.php and the example is in example-image.php

[bartmika]

@Gyvastis Thank you for looking into this, I appreciate it.

Two issues in regards:

1. I confirm my code works on LibreOffice and Google Docs but when I load up in MS Excel 2016, it gives me the following error Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.. I think this is exciting to see! Once it works for MS Excel then we are good. Are you able to open the spreadsheet in MS Excel on your machine?

2. I have an image which is 497x143 and the library was generating an image with a very large height and a short width, which is the opposite of the current image. I wasn't sure how to modify the image sizes so I basically modified the following code in your library to get it to the size I wanted:

public function buildDrawingXML($imagePath, $imageId) { $imageOptions = $this->imageOptions[$imageId]; list($width, $height) = getimagesize($imagePath);

 if($imageOptions['endColNum'] == 0 && $imageOptions['endRowNum'] == 0) {
     $imageOptions['endColNum'] = round($height / 15); // MY EDIT
     $imageOptions['endRowNum'] = round($width / 40); // MY EDIT

}

I was thinking if I can get more details in the API docs on how to adjust the size, that would be great!

[Gyvastis]

I was thinking if I can get more details in the API docs on how to adjust the size, that would be great!

I would love to know that as well, haha. No library I came across had the actual image size replication in the generated file. Thus I'm just creating it on the fly - I've added a 500x500 image through macOS Numbers app (a.k.a mac excel), extracted the file(it's a zip, even if it's a xslx), checked the values for the image drawing file and reverse-engineered the constant values I use to calculate the offsets for rows and columns.

Hence the main problem here is that for every app you can open the excel on (from what I've researched) the cell width and height is different, on some they say it's even font-size dependant. At this point, I can only think of one solution - do the same thing I did in Numbers in other apps like Microsoft Excel, Libre Office, etc, and create a config selection of some sort so you can at least select the image settings preferred to your platform.

There should be a general solution to solve this issue, maybe there's some equation to calculate these things but I didn't came across any.

[bartmika]

@Gyvastis Oh I see. Thank you for your help and your detailed explanation! I gave some effort to see what I could do afterwords and sadly to no avail.

I looked at alternative libraries and I managed to get images working, more specifically PHPSpreadsheet. Having done a little research in their source code, I've tracked down the following file which contains the code in how they handled it:

• https://github.com/PHPOffice/PhpSpreadsheet/blob/29208e9d995eb27445647a3968c59de6207b73b8/src/PhpSpreadsheet/Shared/Drawing.php

Out of curiosity @mk-j, would it be possible for this library to support drawings / images ?

[Gyvastis]

@bartmika great finding. I wonder how did I miss PHPSpreadsheet as it seems more sophisticated than PHP_XLSXWriter.

[mk-j]

PHP_XLSXWriter is meant to be able to handle 500K row spreadsheets, but doesn't support an extensive feature set as the tradeoff. If you don't care about creating gigantic spreadsheets with low memory usage, then feel free to try out other PHP spreadsheet libraries.

[vmorreel]

Hey @Gyvastis, found your pull request for image support with XLSXWriter. I'm trying to use, but I got same problem as @bartmika. The xlsx generated file can be opened properly on LibreOffice or Google Sheet but, still got an error on MSO Excel. Can't find out why, maybe you got suggestion ?

[webzexperts]

Hey @Gyvastis,I have used xlsxwriterplus.class.php class and example-image.php as you suggested but the images still not adding in my excel.

Is there any another way to add images in excel?

[TylonPake]

Hi There,

I altered the code in order to show on MS EXCEL. Edit the $imageRelationshipXML var in the class.

$imageRelationshipXML = '<?xml version="1.0" encoding="UTF-8"?> <xdr:wsDr xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"> <xdr:twoCellAnchor> <xdr:from> <xdr:col>' . $imageOptions['startColNum'] . '</xdr:col> <xdr:colOff>0</xdr:colOff> <xdr:row>' . $imageOptions['startRowNum'] . '</xdr:row> <xdr:rowOff>0</xdr:rowOff> </xdr:from> <xdr:to> <xdr:col>' . $imageOptions['endColNum'] . '</xdr:col> <xdr:colOff>' . $endColOffset . '</xdr:colOff> <xdr:row>' . $imageOptions['endRowNum'] . '</xdr:row> <xdr:rowOff>' . $endRowOffset . '</xdr:rowOff> </xdr:to> <xdr:pic> <xdr:nvPicPr> <xdr:cNvPr id="' . $imageId . '" name="Picture ' . $imageId.'"/> <xdr:cNvPicPr> <a:picLocks noChangeAspect="1"/> </xdr:cNvPicPr> </xdr:nvPicPr> <xdr:blipFill> <a:blip r:embed="rId' . $imageId . '"> <a:extLst/> </a:blip> <a:stretch><a:fillRect/></a:stretch> </xdr:blipFill> <xdr:spPr> <a:prstGeom prst="rect"> <a:avLst/> </a:prstGeom> <a:ln w="12700" cap="flat"> <a:noFill/><a:miter lim="400000"/> </a:ln> <a:effectLst/> </xdr:spPr> </xdr:pic> <xdr:clientData/> </xdr:twoCellAnchor> </xdr:wsDr> ';

[IacopoMelani]

Hi @Gyvastis,

after some tests to make working on MS Excel i altered the xlsxwriterplus.class.php and in my case starts working.

https://gist.github.com/IacopoMelani/df8c2c3ecc748bd0c9f478bd270dccf2

[NitemareReal]

Hi @Gyvastis,

after some tests to make working on MS Excel i altered the xlsxwriterplus.class.php and in my case starts working.

https://gist.github.com/IacopoMelani/df8c2c3ecc748bd0c9f478bd270dccf2

Hello, I have modified @IacopoMelani fork to improve his "addImage" method. Now, you can add as many images as you want and you can select which sheet the image will be inserted on. I have improved too the part where the size of the image is calculated and now, if you just provide a top left pair of cells as starting point, the image keeps its original size.

https://gist.github.com/NitemareReal/c4274f62c9239f64b3a18a7d07f7d118

[gabolander]

Hi @Gyvastis, after some tests to make working on MS Excel i altered the xlsxwriterplus.class.php and in my case starts working. https://gist.github.com/IacopoMelani/df8c2c3ecc748bd0c9f478bd270dccf2

Hello, I have modified @IacopoMelani fork to improve his "addImage" method. Now, you can add as many images as you want and you can select which sheet the image will be inserted on. I have improved too the part where the size of the image is calculated and now, if you just provide a top left pair of cells as starting point, the image keeps its original size.

https://gist.github.com/NitemareReal/c4274f62c9239f64b3a18a7d07f7d118

Hi @NitemareReal , first of all thank you and thank to @Gyvastis (ciao Iacopo) for your contributions. I found that even with your versions of library, in Micro$oft Excel 2016 kept getting issue with error Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded, while Libreoffice Calc opened with no problems. After investigating a bit, and comparing the structure with the one repaired by MS excel, I realized that the problem is in creation of [Content_Types].xml ...

So I changed the buildContentTypesXML() method in the following:

    protected function buildContentTypesXML()
    {
        $content_types_xml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n";
        $content_types_xml .= '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">';
        $content_types_xml.='<Default Extension="jpeg" ContentType="image/jpeg"/>';
        $content_types_xml.='<Default Extension="png" ContentType="image/png"/>';
        $content_types_xml.='<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>';
        $content_types_xml.='<Default Extension="xml" ContentType="application/xml"/>';

		$content_types_drawing_xml = "";
		$i = 1;
        foreach ($this->sheets as $sheet_name => $sheet) {
			$content_types_xml .= '<Override PartName="/xl/worksheets/' . ($sheet->xmlname) . '" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>';
			if(isset($this->images[$sheet_name]) && \count($this->images[$sheet_name]) > 0){
				$content_types_drawing_xml .= '<Override PartName="/xl/drawings/drawing' . $i . '.xml" ContentType="application/vnd.openxmlformats-officedocument.drawing+xml"/>';
			}
			$i++;
        }
        $content_types_xml .= '<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>';
		$content_types_xml .= '<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>';
		$content_types_xml .= $content_types_drawing_xml;
// 		$content_types_xml .= "\n";
		$content_types_xml .= '<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>';
		$content_types_xml .= '<Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>';
		$content_types_xml .= '</Types>';

        return $content_types_xml;
    }

I substantially omitted to write any spare blank or \n character after the first line, and added the lines of "/docProps/" Content Types. Now, here everything works fine, and my xlsx files with images can be opened correctly by MS Excel too. ;-)

Thank you all guys for the good work!

Gabo

[NitemareReal]

Hi @Gyvastis, after some tests to make working on MS Excel i altered the xlsxwriterplus.class.php and in my case starts working. https://gist.github.com/IacopoMelani/df8c2c3ecc748bd0c9f478bd270dccf2

Hello, I have modified @IacopoMelani fork to improve his "addImage" method. Now, you can add as many images as you want and you can select which sheet the image will be inserted on. I have improved too the part where the size of the image is calculated and now, if you just provide a top left pair of cells as starting point, the image keeps its original size. https://gist.github.com/NitemareReal/c4274f62c9239f64b3a18a7d07f7d118

Hi @NitemareReal , first of all thank you and thank to @Gyvastis (ciao Iacopo) for your contributions. I found that even with your versions of library, in Micro$oft Excel 2016 kept getting issue with error Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded, while Libreoffice Calc opened with no problems. After investigating a bit, and comparing the structure with the one repaired by MS excel, I realized that the problem is in creation of [Content_Types].xml ...

So I changed the buildContentTypesXML() method in the following:

    protected function buildContentTypesXML()
    {
        $content_types_xml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n";
        $content_types_xml .= '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">';
        $content_types_xml.='<Default Extension="jpeg" ContentType="image/jpeg"/>';
        $content_types_xml.='<Default Extension="png" ContentType="image/png"/>';
        $content_types_xml.='<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>';
        $content_types_xml.='<Default Extension="xml" ContentType="application/xml"/>';

		$content_types_drawing_xml = "";
		$i = 1;
        foreach ($this->sheets as $sheet_name => $sheet) {
			$content_types_xml .= '<Override PartName="/xl/worksheets/' . ($sheet->xmlname) . '" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>';
			if(isset($this->images[$sheet_name]) && \count($this->images[$sheet_name]) > 0){
				$content_types_drawing_xml .= '<Override PartName="/xl/drawings/drawing' . $i . '.xml" ContentType="application/vnd.openxmlformats-officedocument.drawing+xml"/>';
			}
			$i++;
        }
        $content_types_xml .= '<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>';
		$content_types_xml .= '<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>';
		$content_types_xml .= $content_types_drawing_xml;
// 		$content_types_xml .= "\n";
		$content_types_xml .= '<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>';
		$content_types_xml .= '<Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>';
		$content_types_xml .= '</Types>';

        return $content_types_xml;
    }

I substantially omitted to write any spare blank or \n character after the first line, and added the lines of "/docProps/" Content Types. Now, here everything works fine, and my xlsx files with images can be opened correctly by MS Excel too. ;-)

Thank you all guys for the good work!

Gabo

THANK YOU SO MUCH for your work. I knew about this error but since some months ago, I don't have mucht time to investigate the source of it, so I REALLY, REALLY appreciate your work. I promiss I'll merge your code with mine, but I need some weeks to get some free time. THANK YOU SO MUCH again for you effort

[gabolander]

THANK YOU SO MUCH for your work. I knew about this error but since some months ago, I don't have mucht time to investigate the source of it, so I REALLY, REALLY appreciate your work. I promiss I'll merge your code with mine, but I need some weeks to get some free time. THANK YOU SO MUCH again for you effort

You're very very Welcome. The credit is yours too, man. If You hadn't started patching the initial version, I would most likely never have reached out to make the last fix. So .. bounced thanks to you, to @Gyvastis and of course, to @mk-j
:-) Best, Gabo

[NitemareReal]

You're very very Welcome. The credit is yours too, man. If You hadn't started patching the initial version, I would most likely never have reached out to make the last fix. So .. bounced thanks to you, to @Gyvastis and of course, to @mk-j :-) Best, Gabo

Hello, your code is now incorporated into https://github.com/NitemareReal/xlswriterplus