PHPExcel: How to Underline a Cell’s Text

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 underlining the value of a cell (for example, if you want to use that text as a link in your worksheet).

So let’s go then.

At this point you might of course like to know which underline options you do have available to you. The easiest way to see this is by checking up which PHPExcel constants have been set up for the underline type and to do this we browse to the Font.php file located in the ClassesPHPExcelStyle folder of your PHPExcel installation/folder structure.

Opening this file we find:

const UNDERLINE_NONE = 'none';
const UNDERLINE_DOUBLE = 'double';
const UNDERLINE_DOUBLEACCOUNTING = 'doubleAccounting';
const UNDERLINE_SINGLE = 'single';
const UNDERLINE_SINGLEACCOUNTING = 'singleAccounting';

Looking at this list, we can see that we pretty much have all the usual underline options that Excel offers us, so let’s go ahead and put this knowledge to action!

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 ‘Example’. Then we populate a cell with some data so that we’ve got something to underline:

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

$objPHPExcel->getActiveSheet()->setCellValue('A1','Underline me!');

Okay, so let’s apply a normal underlining to that text we just inserted:

$styleArray = array(
  'font' => array(
    'underline' => PHPExcel_Style_Font::UNDERLINE_SINGLE
  )
);

$objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray);
unset($styleArray);

Okay. So from above you can see that we first create a style array that holds our underline information. We’ve opted for the ‘single line’ style. Then we simply use the useful applyFromArray function to apply this style to our selected cell.

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 a nicely underlined cell of text to admire!

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

Nifty.