I am trying to export some data gathered by webforms, and match it up with one or more files included for each webform. My chosen format is .xls not .xlsx for backwards compatibility.
I've learned here, and on the internet in general, that multiple links IS possible if we use shapes or images/thumbnails to add hyperlinks to, but I can't seem to make it work with PHPSpreadsheet and xls files.
So far I've managed to set hyperlink to individual cells, but I can't seem to make it work on drawings.
Working code for cells:
$coordinates = $sheet->getCellByColumnAndRow($column,$row)->getCoordinate(); // get coordinate like "A1", "B5" etc.
$sheet->setCellValueByColumnAndRow($column,$row,$cellValue); // set link text
$sheet->getStyle($coordinates)->getFont()->setUnderline('single'); // set underline like links have
$sheet->getStyle($coordinates)->getFont()->getColor()->setRGB('#0000FF'); // set default link color
$sheet->getCellByColumnAndRow($column,$row)->getHyperlink()->setUrl('http://www.test.com'); // setting url or local link
This works great, but in my spreadsheet I would like to have multiple links in one cell if there are more than one file sent in by a single user.
Attempt at making it work for drawings:
// create a new drawing object
$drawing = new Drawing();
// set properties
$drawing->setName('Testname');
$drawing->setDescription('Test description');
$drawing->setPath($url); // put your path and image here
$drawing->setCoordinates($coordinates);
$drawing->setOffsetX($width);
$drawing->setHeight($height);
//$drawing->getHyperlink()->setUrl('http://www.test.com'); // error: Call to a member function setUrl() on null
//$drawing->getHyperlink()->setTooltip('tooltip works?'); // error: Call to a member function setTooltip() on null
// Connect drawn image to the spreadsheet
$drawing->setWorksheet($sheet);
The images works great, and I can place multiple images in one cell, but when I try to add hyperlink to each image, PHPSpreadsheet fails me. Are there any other way, perhaps with shapes or other things that I haven't thought about that might do the trick?
And if adding hyperlinks to multiple shapes / images with hyperlinks inside one cell is impossible with standard PHPSpreadsheet, is there a way to force one or more excel functions into one cell, achieving the same thing somehow?
I found a workaround. I answered my own question for future reference and hopefully to help others. :)
The solution was to add a new row for each extra link I needed, and merge all other cells vertically in the columns that was not the link column. This made it possible to seemingly make 2 or more cells inside one cell, not affecting the other columns. E.g. one result that needed 3 links for the file cell, would be taking up 3 rows in the spreadsheet, but all the other columns corresponding to that result would be merged individually vertically, making it look like one row with a file cell containing 3 cells.
Because of the limitation on one link per cell, this is what needed to be done: