PHPExcel: How to Place a Border Around a Cell Range

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 putting a border around a whole range of cells (or just one cell at that).

So let’s go then.

At this point you might of course like to know which border 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 border type and to do this we browse to the Border.php file located in the ClassesPHPExcelStyle folder of your PHPExcel installation/folder structure.

Opening this file we get:

const BORDER_NONE = 'none';
const BORDER_DASHDOT = 'dashDot';
const BORDER_DASHDOTDOT = 'dashDotDot';
const BORDER_DASHED = 'dashed';
const BORDER_DOTTED = 'dotted';
const BORDER_DOUBLE = 'double';
const BORDER_HAIR = 'hair';
const BORDER_MEDIUM = 'medium';
const BORDER_MEDIUMDASHDOT = 'mediumDashDot';
const BORDER_MEDIUMDASHDOTDOT = 'mediumDashDotDot';
const BORDER_MEDIUMDASHED = 'mediumDashed';
const BORDER_SLANTDASHDOT = 'slantDashDot';
const BORDER_THICK = 'thick';
const BORDER_THIN = 'thin';

Looking at this list, we can see that we pretty much have all the usual border 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 four cells with data so that we’ve got something to border:

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

$objPHPExcel->getActiveSheet()->setCellValue('A1','Text 1');
$objPHPExcel->getActiveSheet()->setCellValue('A2','Text 3');
$objPHPExcel->getActiveSheet()->setCellValue('B1','Text 2');
$objPHPExcel->getActiveSheet()->setCellValue('B2','Text 4');

Okay, so let’s apply a normal border that will border each and every cell on each and every wall:

$styleArray = array(
  'borders' => array(
    'allborders' => array(
      'style' => PHPExcel_Style_Border::BORDER_THIN
    )
  )
);

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

Okay. So from above you can see that we first create a style array that holds our border information. We’ve opted for the ‘allborders’ switch and have selected the classic thin border style. Then we simply use the useful applyFromArray function to apply this style to our selected cell range.

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 bordered little table of four to admire!

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

Nifty.