PHPExcel Cheatsheet

PHPExcel Cheatsheet

PHPExcel is a collection of classes that allows you to generate excel documents in PHP. It is incredibly powerful and you can perform almost all (if not all) task that you would normally use in excel.

It is really easy to install, either download the files or they now have a pear channel. For more details visit the PHPExcel site.

This post is mainly for myself so that I can have a quick reference guide for common functions. For full documentation see PHPExcel Documentation.

PHPExcel supports fluent interfaces so function calls can be chained together. The following: Can be written like:


  • Create a new object: $phpExcel = new PHPExcel();
  • Getting the active worksheet: $sheet = $phpExcel->getActiveSheet();
  • Add text to a cell: $sheet->setCellValue("A1", "Hello World");
  • Add a new worksheet: $sheet2 = $phpExcel->createSheet();
  • Add a formula to a cell: $sheet2->setCellValue("A5", "=SUM(A1:A4)");
  • Rename a worksheet: $sheet2->setTitle("Simple");
  • Merge cells: $sheet2()->mergeCells("A1:E3");
  • Unmerge cells: $sheet2()->unmergeCells("A1:E3");

Adding properties to your file

  • Set Author: $phpExcel ->getProperties()->setCreator("Tom Smith");
  • Set Last Modified By: $phpExcel->getProperties()->setLastModifiedBy("Tom Smith");
  • Set Title: $phpExcel->getProperties()->setTitle("Title");
  • Set Subject: $phpExcel->getProperties()->setSubject("Subject");
  • Set Description: $phpExcel->getProperties()->setDescription("A description of the file.");

Formatting A Cell

  • Setting Width: $sheet2->getColumnDimension("A")->setWidth(40);
  • Setting Auto Width: $sheet2->getColumnDimension("A")->setAutoSize(true);
  • Setting Text Alignment Right: $sheet2->getStyle("A1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  • Making text of a range of cells bold: $sheet2->getStyle("A1:F1")->applyFromArray(array("font" => array( "bold" => true)));
  • Adding an auto filter to a range of cells: $sheet2->setAutoFilter("A1:C9");

Output to the browser as a download.


Here is an example of PHPExcel code, it includes most of the functions described above.

Want to discuss a project?

Got a question? Want some more detail? We'd love to hear from you, contact Jason on +44 (0)1923 261166 or


26 May 2016

How to stop your customers' data being stolen

If we, as an industry, take anything from the data leaks at TalkTalk, British Gas and Morrisons, it should be that we must take every measure we can to secure customer data. Offering customers a more personalised experience means providing an environment where they are confident that the information they provide will be safe. Collecting and storing customer data and finding out more about your users is key to generating leads and gaining customer insight. But in the rush to get campaigns out the door and find affordable ways to create your digital products, ensuring third parties don’t risk your customers’ privacy and your reputations can be overlooked.

Screen Shot 2016-05-13 at 12.44.37.pngRead
16 May 2016

How to build, test, share and publish a javascript Hybrid mobile application using Cordova

Mobile Applications (Apps) are something every developer wants to create, however, not every developer wants to have to learn multiple languages to be able to create an App which works across different types of devices, such as Android and iOS. Learning Objective C (or Swift) and Java is probably enough to put most people off the idea of creating a cross-platform App. However, it’s possible to create one using technologies which most developers are familiar with. Good old HTML, CSS and JavaScript is all you need. Well, that and Apache Cordova, the mobile application development framework that allows you to build Apps for multiple platforms using a single code base.

26 April 2016

MongoDB Performance on ZFS and Linux

Here at Clock we love ZFS, and have been running it in production on our Linux file servers for several years. It provides us with numerous excellent features. With the recent release of Ubuntu Xenial 16.04 official support for ZFS is now here, and we are keen to integrate it fully into our next generation hosting stack.

Come and work for Clock

Clock is made up of bright, hard-working and talented people and we're always on the look out for more. You can browse the current jobs below or follow us @clock for the latest vacancies.

View Latest