Welcome to a beginner’s tutorial on PHPSpreadsheet. So you want to create Excel files in your project and stumbled on PHPSpreadsheet… But the documentation is overwhelming and that is not a good place to get started with. Fret not. Let us walk through some common examples such as formulas, colors, and headers in this guide – Read on!
TABLE OF CONTENTS
Download & Notes
Install PHPSpreadsheet
PHPSpreadsheet Basics
Extras
The End
DOWNLOAD & NOTES
Here is the download link to the example code, so you don’t have to copy-paste everything.
EXAMPLE CODE DOWNLOAD
Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.
SORRY FOR THE ADS...
But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.
Buy Me A Coffee Code Boxx eBooks
INSTALL PHPSPREADSHEET
Have not gotten PHPSpreadsheet yet? Here’s how to download and “install” the latest version.
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:
- Open the command line (or terminal).
- Navigate to your project folder
cd YOUR-HTTP-FOLDER. - Run
composer require phpoffice/phpspreadsheet.
Composer will automatically download the latest version into the vendor/ folder, all you have to do is to add require "vendor/autoload.php" in your script.
PHPSPREADSHEET BASICS
All right, with PHPSpreadsheet safely downloaded, let us now move into the tutorial.
1) HELLO WORLD
<?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("1-hello.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.
2) FORCE DOWNLOAD
<?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=\"2-download.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.
3) READ/LOAD EXISTING SPREADSHEETS
<?php
// (A) LOAD PHPSPREADSHEET LIBRARY
require "vendor/autoload.php";
// (B) READ FILE
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$spreadsheet = $reader->load("1-hello.xlsx");
// (C) READ CELLS
$sheet = $spreadsheet->getSheet(0);
$cell = $sheet->getCell("A1");
$value = $cell->getValue();
echo $value;Yep, we can read from existing Excel files… Of course, Captain Obvious.
4) WORKSHEETS
<?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("4-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. |
5) CELLS & VALUES
<?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("5-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. |
6) STRUCTURE
<?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("6-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. |
7) FORMATTING
<?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("7-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. |
8) HEADERS
<?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("8-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. |
EXTRAS
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
- Official PHPSpreadsheet Manual.
- PHPSpreadsheet – Github
- A massive list of Excel Formula – Exceljet
- PHP Output Buffering – A Beginner’s Guide – Code Boxx
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!
I get: Warning: require(vendor/autoload.php): failed to open stream: No such file or directory in /var/www/html/test.php
So it does not work for me…
See “quick notes” and “install PHPSpreadsheet” above.
Also – https://code-boxx.com/php-absolute-relative-path/