PHPSpreadsheet Quickstart – A Beginner’s Tutorial

Welcome to a beginner’s tutorial on PHPSpreadsheet. So you are interested in generating Excel files straight from your PHP project and stumbled on PHPSpreadsheet… But the documentation is overwhelming and there’s not a good place to get started with.

Fret not. Let us walk through how to create a “hello world” spreadsheet in this tutorial, and slowly explore the common features such as formulas, colors, and headers – All with examples. Read on to find out!

ⓘ I have included a zip file with all the example source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

TABLE OF CONTENTS

Download & Notes How To Install The Basics
Useful Bits & Links The End

 

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

EXAMPLE CODE DOWNLOAD

Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

QUICK NOTES

A copy of PHPSpreadsheet is not included in the zip file – Please download the latest version from their official website instead. If you spot a bug, please feel free to comment below. I try to answer questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

HOW TO INSTALL

Have not gotten PHPSpreadsheet yet? Or want to upgrade? Here’s how to download and “install”.

 

DOWNLOAD COMPOSER

The easiest way to get the latest stable version of PHPSpreadsheet is to download and install Composer first – It’s a bit of an extra hassle, but Composer is a package manager that will help you deal with all the dependency stuff.

 

COMMAND LINE DOWNLOAD

Once you have installed Composer, fire up your command line (or terminal). Navigate to your project folder, and type in composer require phpoffice/phpspreadsheet:

D:\http>composer require phpoffice/phpspreadsheet
Using version ^1.9 for phpoffice/phpspreadsheet
./composer.json has been created
Loading composer repositories with package information
Updating dependencies (including require-dev)
Package operations: 4 installs, 0 updates, 0 removals
  - Installing markbaker/matrix (1.1.4): Downloading (100%)
  - Installing markbaker/complex (1.4.7): Downloading (100%)
  - Installing psr/simple-cache (1.0.1): Downloading (100%)
  - Installing phpoffice/phpspreadsheet (1.9.0): Downloading (100%)
phpoffice/phpspreadsheet suggests installing mpdf/mpdf (Option for rendering PDF with PDF Writer)
phpoffice/phpspreadsheet suggests installing dompdf/dompdf (Option for rendering PDF with PDF Writer)
phpoffice/phpspreadsheet suggests installing tecnickcom/tcpdf (Option for rendering PDF with PDF Writer)
phpoffice/phpspreadsheet suggests installing jpgraph/jpgraph (Option for rendering charts, or including charts with PDF or HTML Writers)
Writing lock file
Generating autoload files

Composer will do the rest of the magic by automatically downloading all the required files. All you have to do is to add require "vendor/autoload.php" in your script.

 

MANUAL & GITHUB DOWNLOAD

If you want the latest development build or have some issues with Composer, you can also check out their Github page.

 

 

PHPSPREADSHEET BASICS

All right, with PHPSpreadsheet safely downloaded, let us now move into the tutorial.

 

HELLO WORLD

1-hello-spreadsheet.php
<?php
// (A) LOAD & USE PHPSPREADSHEET LIBRARY
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// (B) CREATE A NEW SPREADSHEET
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// (C) SET CELL VALUE
$sheet->setCellValue('A1', 'Hello World !');

// (D) SAVE TO FILE
$writer = new Xlsx($spreadsheet);
$writer->save('hello world.xlsx');

Ripped right off from the official PHPSpreadsheet documentation page, it really isn’t too difficult to create an Excel file. Just use the library, create a spreadsheet, format your data, then save it somewhere.

 

FORCE DOWNLOAD

2-force-download.php
<?php
// (A) LOAD & USE PHPSPREADSHEET LIBRARY
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// (B) CREATE A NEW SPREADSHEET
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
 
// (C) SET CELL VALUE
$sheet->setCellValue('A1', 'Hello World !');

// (D) SEND DOWNLOAD HEADERS
// ob_clean();
// ob_start();
$writer = new Xlsx($spreadsheet);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="demo.xlsx"');
header('Cache-Control: max-age=0');
header('Expires: Fri, 11 Nov 2011 11:11:11 GMT');
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
header('Cache-Control: cache, must-revalidate');
header('Pragma: public');
$writer->save('php://output');
// ob_end_flush();

Don’t want to save the spreadsheet onto the server? Then simply save it to php://output, effectively sending it out as a download instead. But please take note of how we are also sending out several HTTP headers here as well – Some of you guys may get invalid downloads, depending on how your server is configured.

Firstly, it may be a caching/streaming issue that may be pretty easily resolved by using output buffering – ob_start() and ob_end(). But it could also be that some web servers send out their own HTTP headers, clashing with a few of the above headers.

So the only “fix” that I can recommend is to troubleshoot the headers sent – Try removing all the header(), slowly “reintroduce” them one-by-one, and test out the download. I shall leave links in the extras section below to my other tutorials on PHP buffering and headers.

 

 

READ/LOAD EXISTING SPREADSHEETS

3-load.php
<?php
// (A) LOAD PHPSPREADSHEET LIBRARY
require 'vendor/autoload.php';
 
// (B) READ FILE
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$spreadsheet = $reader->load("hello world.xlsx");
 
// (C) READ CELLS
$sheet = $spreadsheet->getSheet(0);
$cell = $sheet->getCell('A1');
$value = $cell->getValue();
echo $value;

Yep, we can read from existing files… Of course, Captain Obvious.

 

WORKSHEETS

4-worksheets.php
<?php
// (A) LOAD & USE PHPSPREADSHEET LIBRARY
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// (B) FIRST WORKSHEET
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle("First Sheet");
$sheet->setCellValue('A1', 'Hello World!');

// (C) ADD WORKSHEET
$spreadsheet->createSheet();
 
// (C1) WORKSHEETS ARE IN RUNNING SEQUENCE NUMBER - 0, 1, 2, ...
$sheet = $spreadsheet->getSheet(1);
 
// (C2) ALTERNATIVELY, WE CAN GET BY NAME (AFTER WE SET THE TITLE)
//$sheet = $spreadsheet->getSheetByName('TITLE');
 
// (C3) SET WORKSHEET TITLE + CELL VALUE
$sheet->setTitle("Second Sheet");
$sheet->setCellValue('A1', 'Foo Bar!');

// (D) COPY WORKSHEET
$evilClone = clone $spreadsheet->getSheet(0);
$evilClone->setTitle('Evil Clone');
$spreadsheet->addSheet($evilClone);

// (E) DELETE WORKSHEET
// $spreadsheet->removeSheetByIndex(0);

// (F) GET TOTAL NUMBER OF WORKSHEETS
// $total = $spreadsheet->getSheetCount();

// (G) SAVE TO SERVER
$writer = new Xlsx($spreadsheet);
$writer->save('worksheets.xlsx');
Function Description
$spreadsheet->getActiveSheet() Get current active worksheet.
$spreadsheet->getSheet(INDEX) Get worksheet in the specified index.
$spreadsheet->getSheetByName(TITLE) Get worksheet with the given title.
$spreadsheet->createSheet() Add a new worksheet.
clone $spreadsheet->getSheet(INDEX) Create an evil clone.
$spreadsheet->removeSheetByIndex(INDEX) Remove the given worksheet.
$spreadsheet->getSheetCount() Get the total number of worksheets.
$sheet->setTitle(TITLE) Set the title of the worksheet.

 

 

CELLS & VALUES

5-cells.php
<?php
// (A) LOAD & USE PHPSPREADSHEET LIBRARY
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// (B) CREATE WORKSHEET
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle("First Sheet");

// (C) SINGLE CELL
// (C1) GET SINGLE CELL THEN SET VALUE
$cell = $sheet->getCell('A1');
$cell->setValue("Hello");

// (C2) MORE WAYS SET VALUE
$sheet->setCellValue('A2', 'World!');
$sheet->setCellValueByColumnAndRow(1, 3, 'FOO!');

// (C3) GET VALUE
$cell = $sheet->getCellByColumnAndRow(1, 2);
$value = $cell->getValue();
 
// (C4) GET HIGHEST ROW + COL
$highestRow = $sheet->getHighestRow();
$highestCol = $sheet->getHighestColumn();

// TIP - You can use $highestRow $highestCol to loop through the cells.
// for ($i=0; i<$highest; i++) { ... }
 
// (D) RANGE OF CELLS
// (D1) GET SELECTED RANGE INTO AN ARRAY
$data = $sheet->rangeToArray("A1:A3");
 
// (D2) SET DATA FROM ARRAY INTO CELLS
$data = [100, 53, 86];
$data = array_chunk($data, 1);
$sheet->fromArray($data, null, "B1");

// (E) FORMULAS ACCEPTED - JUST AS IN EXCEL
$sheet->setCellValue('B4', '=SUM(B1:B3)');

// (F) SAVE TO SERVER
$writer = new Xlsx($spreadsheet);
$writer->save('cells.xlsx');
Function Description
$sheet->getCell(COORD) Get the given cell by coordinates.
$sheet->getCellByColumnAndRow(COL, ROW) Get the given cell by column and row number.
$cell->getValue() Get the value of the given cell.
$cell->setValue(VALUE) Set value of the given cell.
$sheet->setCellValue(COORD, VALUE) Set value of the given cell.
$sheet->setCellValueByColumnAndRow(COORD, VALUE) Set value of the given cell by column and row number.
$sheet->getHighestRow() Get total number of rows.
$sheet->getHighestColumn() Get total number of columns.
$sheet->rangeToArray("START:END") Get value of specified cells into an array.
$sheet->fromArray(ARRAY, null, STARTING CELL) Put the given array into cells.

 

STRUCTURE

6-structure.php
<?php
// (A) LOAD & USE PHPSPREADSHEET LIBRARY
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// (B) CREATE A NEW SPREADSHEET & DUMMY DATA
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Hello, this is a very very long string.');
$sheet->setCellValue('A2', 'World!');
$sheet->setCellValue('A3', 'Foo');
$sheet->setCellValue('A4', 'Bar');

// (C) MERGE & UNMERGE CELLS
$sheet->mergeCells('A1:D1');
$sheet->mergeCells('A2:B2');
$sheet->unmergeCells('A2:B2');

// (D) INSERT ROW & COL
$sheet->insertNewColumnBefore('A', 1); // 1 new column before column A
$sheet->insertNewRowBefore(3, 1); // 1 new row before row 3

// (E) VISIBILITY
$sheet->getColumnDimension('A')->setVisible(false);
// $sheet->getColumnDimension('A')->setVisible(true);
$sheet->getRowDimension(4)->setVisible(false);
// $sheet->getRowDimension(4)->setVisible(true);

// (F) WIDTH & HEIGHT
$sheet->getRowDimension('4')->setRowHeight(100);
$sheet->getColumnDimension('C')->setWidth(100);

// (G) SAVE TO FILE
$writer = new Xlsx($spreadsheet);
$writer->save('structure.xlsx');
Function Description
$sheet->mergeCells(START:END) Merge the given cells.
$sheet->unmergeCells(START:END) Unmerge the given cells.
$sheet->insertNewColumnBefore(COL, NUMBER OF COLUMNS) Insert new columns.
$sheet->insertNewRowBefore(ROW, NUMBER OF ROWS) Insert new rows.
$sheet->getColumnDimension(COL)->setVisible(TRUE/FALSE) Set the visibility of the column.
$sheet->getRowDimension(ROW)->setVisible(TRUE/FALSE) Set the visibility of the row.
$sheet->getRowDimension(ROW)->setRowHeight(HEIGHT); Set the height.
$sheet->getColumnDimension(COL)->setWidth(WIDTH); Set the width.

 

 

FORMATTING

7-formatting.php
<?php
// (A) LOAD & USE PHPSPREADSHEET LIBRARY
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// (B) CREATE A NEW SPREADSHEET & DUMMY DATA
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('B1', 'Hello');
$sheet->setCellValue('B2', 'World!');
$sheet->setCellValue('B3', 'Foo');
$sheet->setCellValue('B4', 'Bar');
$sheet->getRowDimension('3')->setRowHeight(50);

// (C) SET STYLE
$styleSet = [
  // (C1) FONT
  'font' => [
    'bold' => true,
    'italic' => true,
    'underline' => true,
    'strikethrough' => true,
    'color' => ['argb' => 'FFFF0000'],
    'name' => "Cooper Hewitt",
    'size' => 22
  ],

  // (C2) ALIGNMENT
  'alignment' => [
    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT,
    // \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT
    // \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER
    'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_BOTTOM
    // \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP
    // \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
  ],

  // (C3) BORDER
  'borders' => [
    'top' => [
      'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
      'color' => ['argb' => 'FFFF0000']
    ],
    'bottom' => [
      'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
      'color' => ['argb' => 'FF00FF00']
    ],
    'left' => [
      'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM,
      'color' => ['argb' => 'FF0000FF']
    ],
    'right' => [
      'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
      'color' => ['argb' => 'FF0000FF']
    ]
    /* ALTERNATIVELY, THIS WILL SET ALL
    'outline' => [
        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
        'color' => ['argb' => 'FFFF0000']
    ]*/
  ],

  // (C4) FILL
  'fill' => [
    // SOLID FILL
    'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
    'color' => ['argb' => 'FF110000']

    /*  GRADIENT FILL
    'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,
    'rotation' => 90,
    'startColor' => [
        'argb' => 'FF000000',
    ],
    'endColor' => [
        'argb' => 'FFFFFFFF',
    ]*/
  ]
];
$style = $sheet->getStyle('B3');
// $style = $sheet->getStyle('B1:B4'); 
$style->applyFromArray($styleSet);

// (D) SAVE TO FILE
$writer = new Xlsx($spreadsheet);
$writer->save('formatting.xlsx');
Function Description
$sheet->getStyle(COORD) Get the current style for the given cell (can select a range of cells as well).
$style->applyFromArray(ARRAY) Apply styles to the given cells.

 

HEADERS

8-headers.php
<?php
// (A) LOAD & USE PHPSPREADSHEET LIBRARY
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// (B) CREATE A NEW SPREADSHEET & DUMMY DATA
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'First');
$sheet->setCellValue('B1', 'Second');
$sheet->setCellValue('C1', 'Third');
$sheet->setCellValue('A2', 'Hello');
$sheet->setCellValue('A3', 'World!');
$sheet->setCellValue('A4', 'Foo');
$sheet->setCellValue('B2', 12);
$sheet->setCellValue('B3', 34);
$sheet->setCellValue('B4', 56);
$sheet->setCellValue('C2', true);
$sheet->setCellValue('C3', false);
$sheet->setCellValue('C4', true);

// (C) AUTO FILTER
$sheet->setAutoFilter('A1:C4');

// (D) FREEZE PANE
$sheet->freezePane('C2');

// (E) SAVE TO FILE
$writer = new Xlsx($spreadsheet);
$writer->save('headers.xlsx');
Function Description
$sheet->setAutoFilter(START:END) Set auto-filter for the given range.
$sheet->freezePane(COORD) Freeze pane. Slightly confusing though – Take note that freezePane('C2') will freeze the first row AND columns A and B.

 

 

USEFUL BITS & LINKS

That’s all for this tutorial, and here is a small section on some extras and links that may be useful to you.

 

LINKS & REFERENCES

 

THE OFFICIAL EXAMPLES

This beginner’s tutorial should cover most of the basics, but PHPSpreadsheet actually comes with a lot of sample files. If you need more “advanced spreadsheet yoga” – Simply open up the \vendor\phpoffice\phpspreadsheet\samples folder and check the files inside.

 

THE END

Thank you for reading, and we have come to the end of this guide. I hope that it has helped you with your project, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published. Required fields are marked *