Getting Started With PhpSpreadsheet

Installation

Install Dependencies

If you are running PHP 7.2 on Ubuntu, you will need to run the following commands to install the required package dependencies:

sudo apt-get install php7.2-gd php7.2-mbstring php7.2-zip -y

If you are running PHP 7.4 on Ubuntu, you will need to run the following commands to install the required package dependencies:

sudo apt-get install php7.4-gd php7.4-mbstring php7.4-zip -y

Install The Package

Run the following command to install the spreadsheet package.

composer require phpoffice/phpspreadsheet

Hello World Example

The following example will create an Excel spreadsheet with the first row of cells having "Hello" and "World".

<?php

require_once(__DIR__ . '/vendor/autoload.php');


$spreadsheet = new PhpOffice\PhpSpreadsheet\Spreadsheet();
$spreadsheet->getProperties();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Hello');
$sheet->setCellValue('B1', 'World');

$writer = new PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$writer->save('HelloWorld.xlsx');

Why Have A Writer?

The reason there is a "writer" is because the package is for creating spreadsheets and is not specific to Excel. Thus if you want to produce a CSV file you could just change the last two lines to:

$writer = new PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet);
$writer->save('HelloWorld.csv');

The "writer" is like a "driver" that takes what you programmed in the "spreadsheet" and is responsible for creating the file in the relevant format. This is a good idea in principle as it creates a separation of concerns and allows you to easily create whatever file type you wish with very little changes to your code (in theory). However, this can lead you to an "invalid state". E.g. if you create a spreadsheet that performs calculations, that will not get "saved out" to a CSV. That format can literally only save the values in it.

Looping Through Cell Indexes.

The examples for using this tool tend to show cell index values like A1, and B3. However, cell indexes can be accessed using an array format like [1,1] instead, which can be a lot more useful from a programmatic perspective. E.g. you just want the next cell to the right/left, and don't want to worry about which letter that correlates to.

<?php

require_once(__DIR__ . '/vendor/autoload.php');

$spreadsheet = new PhpOffice\PhpSpreadsheet\Spreadsheet();
$spreadsheet->getProperties();
$sheet = $spreadsheet->getActiveSheet();

$sheet->getCellByColumnAndRow(1, 1)->setValue('header column 1');
$sheet->getCellByColumnAndRow(2, 1)->setValue('header column 2');
$sheet->getCellByColumnAndRow(1, 2)->setValue('second row value 1');
$sheet->getCellByColumnAndRow(2, 2)->setValue('second row value 2');

$writer = new PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$writer->save('HelloWorld.xlsx');

Just remember that these indexes start at 1, not 0, and its column first, not row.

References

• PHP Spreadsheet - Official Docs
• PHP Spreadsheet - Official Docs - File Formats