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/vnd.ms-excel");
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.

$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle('Results');

$objPHPExcel->createSheet()->setTitle('ResultDetails');

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->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1','Click to see details');
$objPHPExcel->getActiveSheet()->getCell('A1')
  ->getHyperlink()
  ->setUrl("sheet://'ResultDetails'!A1");

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');
$objWriter->save('php://output');
exit;

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! :)