Setting Your Worksheet Printing Layout Options in PHPExcel CodeUnit 18 APR 2010

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/");
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

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

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 ClassesPHPExcelWorksheetPageSetup.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:

Related Posts:

About Craig Lotter

Software developer, husband and dad to two little girls. Writer behind An Exploring South African. I don’t have time for myself any more.

  • Hi,

    Thanks for this article. I think there are some typos in the code snippet that need to be fixed.

  • Hi,

    Thanks for this article. I think there are some typos in the code snippet that need to be fixed.

  • can you write other post, zen-cart etc, thanks

  • Dfghdfgh

    jgfdh fdg

  • Any solution to modify the font style of yAxisTitle? I’m using the framework PHPExcel

    • I’m sorry, but I have no idea. It has been a very, very long time since I worked with PHPExcel I’m afraid.

  • Eric

    Thank you! Your solution fixed my problem with excel forcingheight to one page no matter the height. I was missing


  • iacsoc

    Great! Thanks Craig.. really helpfull..
    same case with Eric, i miss set fit to height..

  • feitian

    Thanks! Very helpfull!

  • swizzmagik

    This is so helpful you saved my butt thank you X1000!

    • Wow, I’m surprised this still works after five years. Great! :)

  • Thanks Craig.
    Is there a way to write all of this into PDF as well? I need copies of xls and pdf.

    • whatever libraries that you are using to generate your xls and pdf files should have some sort of print layout option settings available. I don’t know of any single library that gives you both xls and pdf output though if that’s what you’re asking.

  • Tolga Hayıt

    Thanksss! Great work

  • Tony COUPÉ

    Merci Craig !