Despite digital development, spreadsheets are still very popular to store and display data as text and numerals or in graphical form.
Modern spreadsheets also provide built-in functions for common financial and statistical operations.
Thus managing spreadsheets in a web application can be really helpful to allow users to keep their habits.
PhpSpreadsheet is a library written in pure PHP providing a set of classes that allow to read from and to write to different spreadsheet file formats, like Excel and LibreOffice Calc.
Let’s see how to use it with a Symfony app.
First, we need PHP version 5.6 or newer and ensure that PHP extensions php_zip, php_xml and php_gd2 are enabled before developing using PhpSpreadsheet.
In this first part, we will see how to create a spreadsheet showing characteristics of some browsers (name, developer, release date and programming language used).
First we must create a workbook using the Spreadsheet class.
Once we have a single worksheet, it is time to write some data.
The main advantage of PhpSpreadsheet is that all parts of our code is easy to understand even by a non-developer as it describes simple actions that are usually done by a user writing in spreadsheet applications.
Thus, we will construct our spreadsheet accessing cells and settings values.
PhpSpreadsheet also provides a way to set style to our spreadsheet thanks to several classes gathered in the \PhpSpreadsheet\Style folder.
The following code shows basic methods that can be used to center text, change font weight and autosize column.
Managing file formats and save spreadsheet
Now, we have our spreadsheet correctly set but we still want to use it in spreadsheet applications.
Supported file formats are listed here.
We will then have to choose a format to write our spreadsheet into a file.
To easily choose between famous spreadsheet formats, we will create a simple form like this:
We can use this form in our controller to create correct file according to a given format and then stream the response back to the client.
How to retrieve data from a spreadsheet?
Once we are able to create spreadsheets, we also need to know how to read from existing ones.
In this second part, we will see how to retrieve data from the spreadsheet we have just created.
Loader or Reader
To load a workbook, it is possible to simply use static load() method of the \PhpOffice\PhpSpreadsheet\IOFactory class.
This method will attempt to identify the file type and instantiate the corresponding loader in order to load the file.
If we do not want to be able to manage all supported file types or if we want to add some specific logic, we can instantiate a specific reader object for our file and use the reader’s load() method to correctly read the file.
Moreover, if we are not sure of the file type, it is possible to use the IO Factory’s identifiy() method combined with createReader() method to identify and instantiate the reader object we need.
Now that we have a spreadsheet, we can iterate through each worksheet it contains to construct an array containing browser characteristics.
Let’s assume that our spreadsheets are all located in a folder named export and that we want to retrieve data from a specific spreadsheet named Browser_characteristics.xlsx.
By combining the two previous parts, we can display our data with a basic Twig template.
With this article, you are now able to correctly manage PhpSpreadsheet basic usages.
If you need an example of this test case, we got a github repository to help you test that!
If you have other questions related to PhpSpreadsheet, don’t hesitate to comment down below, and we’ll try our best to answer quickly and if you are interested in other articles about PhpSpreadsheet be sure to tell us!