18

I am isung PHPExcel and have a URL in a string. When doing:

$url = 'http://dx.doi.org/10.1016/j.phymed.2005.11.003'
$xls = new PHPExcel();
$xls->setActiveSheetIndex(0);
$xls->getActiveSheet()->setCellValueByColumnAndRow(1,2,$url);

The url is set as simple text.

I also tried: 

$xls->getActiveSheet()->getCellByColumnAndRow(1,2)->getHyperlink()->setUrl('"'.$url.'"');

But then, when clicking on the link, it tries to open a local folder.

Any idea how to do that?

Thank you.

EDIT

When I try do do this without quotes:

$xls->getActiveSheet()->getCellByColumnAndRow(1,2)->getHyperlink()->setUrl($url);

Then I am getting the error:

Exception' with message 'Invalid parameters passed.'

My real url is 

http://dx.doi.org/10.1016/j.phymed.2005.11.003

I noticed that when setting a slash at the end, the hyperlink works, but the url is then wrong.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Milos Cuculovic
  • 19,631
  • 51
  • 159
  • 265
  • what are you using `url` for? – Shairyar Apr 16 '14 at 05:42
  • I am parsing some url from another excel file. The right url I have is http://dx.doi.org/10.1016/j.phymed.2005.11.003 for example. I just noticed that when i sed a slash "/" after this url, the hyperlink works, but the url is wrong then. – Milos Cuculovic Apr 16 '14 at 05:43

4 Answers4

24

I have found the solution, somehow the url I had was not recognized by excel.

$url = str_replace('http://', '', $link);
$xls->getActiveSheet()->getCellByColumnAndRow(1,2)->getHyperlink()->setUrl('http://www.'.$url);

And now it works. Hope this will help.

Milos Cuculovic
  • 19,631
  • 51
  • 159
  • 265
19

I am guessing your field value has integer value. If it is so, then you first have to convert the data type of that cell and then set the hyperlink. Below is how I have done this.

//set the value of the cell
$this->phpExcelObj->getActiveSheet()->SetCellValue('A1',$id);
//change the data type of the cell
$this->phpExcelObj->getActiveSheet()->getCell("A1")->setDataType(PHPExcel_Cell_DataType::TYPE_STRING2);
///now set the link
$this->phpExcelObj->getActiveSheet()->getCell("A1")->getHyperlink()->setUrl(strip_tags($link));
vsingh
  • 470
  • 4
  • 9
  • 6
    I think this should be the best answer... anyway thank you this helped me a lot. I've reduced the code to a single line using a different method to set the cell value: `$this->phpExcelObj->getActiveSheet()->setCellValueExplicit('A1', $id, PHPExcel_Cell_DataType::TYPE_STRING2, TRUE)->getHyperlink()->setUrl(strip_tags($link));` **setCellValueExplicit()** allows you to set the datatype directly on the same function call, and the last argument, if set to true, returns the cell (similar to getCell) instead of the sheet (similar to getActiveSheet). – Gonzalingui Jan 03 '15 at 23:47
14

Try to write your code as below line:

$objSheet->setCellValue('A1', '=Hyperlink("https://www.someurl.com/","Mi web")');

Timo002
  • 3,138
  • 4
  • 40
  • 65
pedro.caicedo.dev
  • 2,269
  • 2
  • 16
  • 19
  • 1
    I don't know why this answer is downvoted, but it works perfectly! – Timo002 Sep 05 '17 at 15:16
  • This code kind of hardcodes a hyperlink, the cell can't be convert to text by clicking remove hyperlink, also the sorting and some stuff doesn't work. – Abdul Rehman Nov 20 '18 at 09:18
1

Just lost an hour on the same issue. Finally (after checking PHPexcel source) figured out, that ->getCell('A1') is not required and allways lead me to the following error: Call to a member function getHyperlink() on a non-object. Instead you have to pass the cell-coordinates directly to getHyperlink('A1') like this:

$YourActiveSpreadsheet->getHyperlink('A1')->setUrl($url);
Patrick
  • 11
  • 3