Setting Your Worksheet Printing Layout Options in PHPExcel

I’ve gone on and on in length over just how awesome the PHPExcel OpenXML spreadsheet reader/writer project is over the last bit here in this blog, and today is no exception as I take a quick look at how one can set the page printing layout when generating an Excel spreadsheet.

Now we all know how annoying it is when we right-click on an Excel spreadsheet and select print, only to recoil in horror when we realise that the columns don’t exactly fit nicely on a single page, meaning a spill-over effect and thus a lot of wasted paper!

So ideally what one wants to do for a spreadsheet is preset the document’s printing preferences in order to avoid handing over this quick print annoyance to our spreadsheet users.

Naturally, being as awesome a system as what it is, PHPExcel comes bundled with a number of methods that allow us to specify page size/type and orientation, as well as scaling options.

For this example, we’re going to set our spreadsheet to print in A4 size with a portrait layout and scaled so that it fits to 1 page wide and infinite pages tall.

// Redirect output to a client’s web browser (Excel5)
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=demo.xls");
header('Cache-Control: max-age=0');
 
// PHPExcel
require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';
 
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
 
// Set Orientation, size and scaling
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToPage(true);
$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1);
$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0);
 
// Generate spreadsheet
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

As you can see above, setting the various options are pretty self explanatory. There are a number or preset orientation and papersizes to choose from (see Classes\PHPExcel\Worksheet\PageSetup.php for the options) and then the main full page scaling option is dealt with by the setFitToPage boolean flag. If set to true, you can then specify by how many pages wide you wish to force the layout and also how many pages tall you want it. Specifying a 0 means infinite pages.

If however you set FitToPage to false but still want to scale, you can make use of the setScale() function which allows you to set the percentage scaling you wish to apply.

And that’s precisely how simple all of this is!

Related Link: http://phpexcel.codeplex.com/