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.

Content on this blog is licensed under a Creative Commons Attribution 3.0 License.

Join Us

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
Jobs