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');
References
First published: 19th February 2020