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:

Basics

  • 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.

Example

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 jason.treloar@clock.co.uk.

Related

Blog-post-img-01.jpgRead
Article
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.

node blog post.jpgRead
Article
9 March 2016

A Simple Website in Node.js – 2016 Edition

Four years ago I wrote a post on how to build a simple website in Node.js. Seeing as it’s still the most popular article on this blog, I thought I’d take a look at how things have evolved, what I would do differently today, and some additional nuggets of advice.

legoblog.jpgRead
Article
24 February 2016

JavaScript Frameworks in 2016

As JavaScript's popularity continues to grow and with it, the surrounding ecosystem of modern libraries & tools, it can sometimes be hard to keep-up, leading to what some describe as JavaScript Fatigue. This post will delve into some of the advances we're seeing in the JavaScript community and how such solutions may benefit you and your team, without making you fatigued.

Blog-image-16x9.jpgRead
Article
22 October 2015

Moving from Mac to Linux

Shortly before I moved house about a month ago, unsatisfied with the impending chaos, my hand (without first consulting my head) decided to launch a full pint of squash at my MacBook Air. I swiftly unplugged the power cable and took it to my neighbourhood Mac engineer. It was already too late – it was pronounced dead on arrival.

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
Vacancies