How to write spreadsheet file in PHP?

There are times that your client requires you to add a feature to export spreadsheet file in a website. There are many ways on how to do this. You can do it yourself by using native PHP code. But the most preferred way is to use a third-party library. And the most popular library for this is - PhpSpreadsheet.

PhpSpreadsheet is a library written in pure PHP and providing a set of classes that allow you to read from and to write to different spreadsheet file formats, like Excel and LibreOffice Calc.

These are the following formats supported in PHPSpreadsheet:

• Open Document Format/OASIS (.ods)
• Office Open XML (.xlsx) Excel 2007 and above
• BIFF 8 (.xls) Excel 97 and above
• BIFF 5 (.xls) Excel 95
• SpreadsheetML (.xml) Excel 2003
• Gnumeric
• HTML
• SYLK
• CSV
• PDF

Before we get started, let us consider first the requirements needed in order to fully utilized the capability of this library. The following are the required software to develop using PhpSpreadsheet.

• PHP version 5.6 or newer
• PHP extension php_zip enabled
• PHP extension php_xml enabled
• PHP extension php_gd2 enabled (if not compiled in)

Just make sure to check and install the aforementioned software in your server installation.

You can install PhpSpreadsheet using Composer like so:

composer require phpoffice/phpspreadsheet

After the files has been downloaded, you can now create a basic spreadsheet. For demo purposes, go ahead open your favorite code editor and paste the below code and open it in your browser.

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Hello World !');

$writer = new Xlsx($spreadsheet);
$writer->save('hello world.xlsx');

The finally, go to the directory of your project, you should see the generated file with the filename hello world.xlsx. There you have it, your first ever spreadsheet file in PHP.