PHPExcel: How to Link to another Worksheet CodeUnit 11 APR 2010

The CodePlex project PHPExcel is an absolutely brilliant implementation around Microsoft’s OpenXML standard, giving us a powerful PHP object driven engine that is capable of creating and reading from Excel 2007, PDF, HTML and even Excel 2003 spreadsheets and documents.

Today’s little hint is on how one can go about creating a link on one worksheet in your spreadsheet that will then take a user to another worksheet in your spreadsheet by clicking the link, using the PHPExcel engine. (Of course, we’re not limited to linking to another worksheet, the same process will allow you to link even to web sites from within your Excel spreadsheet!)

So let’s go then.

First we initiate our PHPExcel object in the following fashion:

header("Content-type: application/");
header("Content-Disposition: attachment; filename=example.xls");
header('Cache-Control: max-age=0');

require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';

$objPHPExcel = new PHPExcel();

Then we set the active sheet to the default first sheet in the workbook and give it a title, in this case ‘Result’. The next step is then to create a second sheet which will be the one you are linking to. I’ve named it ‘ResultDetails’ for this example.



Now we go back to our original base sheet and add in the cell text which will act as the link. With that done, we then apply the hyperlink to it:

$objPHPExcel->getActiveSheet()->setCellValue('A1','Click to see details');

And that is pretty much it. Finish off the spreadsheet generation by using the code below and then open up your freshly generated spreadsheet. As you can see, you should have at least two worksheets in the workbook, the first of which contains a link that by clicking on it will take you straight to the second worksheet.

Very nifty indeed.

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

If of course if you would rather just have that link you created open up a normal webpage address instead, you simply change the setUrl function call to point to a normal http:// address! Simple as that! :)

Related Posts:

About Craig Lotter

South African software architect and developer at Touchwork. Husband to a cupcake baker and father to two little girls. I don't have time for myself any more.

  • cipak

    That's great, any hint of how to make only part of a cell's contents into a link? For example, “click >>here<< to get more details” (only “here” would be clickable, not the whole text in the cell).


  • That's a good question cipak and to be honest, I'm not really sure if one can do what you want to do, other than trying to trick Excel by maybe placing raw HTML into a cell? Sorry, guess you'll have to pose this one to the team behind the product! :)