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.
Prerequisites
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.
Then use composer to install PhpSpreadsheet into our project:
composer require phpoffice/phpspreadsheet 1.0.0How to create a basic spreadsheet?
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).
Spreadsheet creation
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.
use PhpOffice\PhpSpreadsheet\Spreadsheet;
protected function createSpreadsheet()
{
$spreadsheet = new Spreadsheet();
// Get active sheet - it is also possible to retrieve a specific sheet
$sheet = $spreadsheet->getActiveSheet();
// Set cell name and merge cells
$sheet->setCellValue('A1', 'Browser characteristics')->mergeCells('A1:D1');
// Set column names
$columnNames = [
'Browser',
'Developper',
'Release date',
'Written in',
];
$columnLetter = 'A';
foreach ($columnNames as $columnName) {
// Allow to access AA column if needed and more
$columnLetter++;
$sheet->setCellValue($columnLetter.'2', $columnName);
}
// Add data for each column
$columnValues = [
['Google Chrome', 'Google Inc.', 'September 2, 2008', 'C++'],
['Firefox', 'Mozilla Foundation', 'September 23, 2002', 'C++, JavaScript, C, HTML, Rust'],
['Microsoft Edge', 'Microsoft', 'July 29, 2015', 'C++'],
['Safari', 'Apple', 'January 7, 2003', 'C++, Objective-C'],
['Opera', 'Opera Software', '1994', 'C++'],
['Maxthon', 'Maxthon International Ltd', 'July 23, 2007', 'C++'],
['Flock', 'Flock Inc.', '2005', 'C++, XML, XBL, JavaScript'],
];
$i = 3; // Beginning row for active sheet
foreach ($columnValues as $columnValue) {
$columnLetter = 'A';
foreach ($columnValue as $value) {
$columnLetter++
$sheet->setCellValue($columnLetter.$i, $value);
}
$i++;
}
return $spreadsheet;
}Formatting
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.
use PhpOffice\PhpSpreadsheet\Style\Alignment;
$columnLetter = 'A';
foreach ($columnNames as $columnName) {
// Center text
$sheet->getStyle($columnLetter.'1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
$sheet->getStyle($columnLetter.'2')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
// Text in bold
$sheet->getStyle($columnLetter.'1')->getFont()->setBold(true);
$sheet->getStyle($columnLetter.'2')->getFont()->setBold(true);
// Autosize column
$sheet->getColumnDimension($columnLetter)->setAutoSize(true);
$columnLetter++;
}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:
use Symfony\Component\Form\AbstractType;
use Symfony\Component\Form\Extension\Core\Type\ChoiceType;
use Symfony\Component\Form\FormBuilderInterface;
public function buildForm(FormBuilderInterface $builder, array $options)
{
$builder
->add('format', ChoiceType::class, [
'choices' => [
'xlsx' => 'xlsx',
'ods' => 'ods',
'csv' => 'csv',
],
'label' => false,
'placeholder' => 'Select a format',
])
;
}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.
use AppBundle\Form\Type\ExcelFormatType;
use PhpOffice\PhpSpreadsheet\Writer\Csv;
use PhpOffice\PhpSpreadsheet\Writer\Ods;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Symfony\Component\HttpFoundation\StreamedResponse;
/**
* @Route("/export", name="export")
*/
public function exportAction(Request $request)
{
$form = $this->createForm(ExcelFormatType::class);
$form->handleRequest($request);
if ($form->isSubmitted() && $form->isValid()) {
$data = $form->getData();
$format = $data['format'];
$filename = 'Browser_characteristics.'.$format;
$spreadsheet = $this->createSpreadsheet();
switch ($format) {
case 'ods':
$contentType = 'application/vnd.oasis.opendocument.spreadsheet';
$writer = new Ods($spreadsheet);
break;
case 'xlsx':
$contentType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
$writer = new Xlsx($spreadsheet);
break;
case 'csv':
$contentType = 'text/csv';
$writer = new Csv($spreadsheet);
break;
default:
return $this->render('AppBundle::export.html.twig', [
'form' => $form->createView(),
]);
}
$response = new StreamedResponse();
$response->headers->set('Content-Type', $contentType);
$response->headers->set('Content-Disposition', 'attachment;filename="'.$filename.'"');
$response->setPrivate();
$response->headers->addCacheControlDirective('no-cache', true);
$response->headers->addCacheControlDirective('must-revalidate', true);
$response->setCallback(function() use ($writer) {
$writer->save('php://output');
});
return $response;
}
return $this->render('AppBundle::export.html.twig', [
'form' => $form->createView(),
]);
}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.
use PhpOffice\PhpSpreadsheet\IOFactory;
protected function loadFile($filename)
{
return IOFactory::load($filename);
}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.
use PhpOffice\PhpSpreadsheet\Reader\Csv as ReaderCsv;
use PhpOffice\PhpSpreadsheet\Reader\Ods as ReaderOds;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx as ReaderXlsx;
protected function readFile($filename)
{
$extension = pathinfo($filename, PATHINFO_EXTENSION);
switch ($extension) {
case 'ods':
$reader = new ReaderOds();
break;
case 'xlsx':
$reader = new ReaderXlsx();
break;
case 'csv':
$reader = new ReaderCsv();
break;
default:
throw new \Exception('Invalid extension');
}
$reader->setReadDataOnly(true);
return $reader->load($filename);
}Retrieve data
Now that we have a spreadsheet, we can iterate through each worksheet it contains to construct an array containing browser characteristics.
protected function createDataFromSpreadsheet($spreadsheet)
{
$data = [];
foreach ($spreadsheet->getWorksheetIterator() as $worksheet) {
$worksheetTitle = $worksheet->getTitle();
$data[$worksheetTitle] = [
'columnNames' => [],
'columnValues' => [],
];
foreach ($worksheet->getRowIterator() as $row) {
$rowIndex = $row->getRowIndex();
if ($rowIndex > 2) {
$data[$worksheetTitle]['columnValues'][$rowIndex] = [];
}
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false); // Loop over all cells, even if it is not set
foreach ($cellIterator as $cell) {
if ($rowIndex === 2) {
$data[$worksheetTitle]['columnNames'][] = $cell->getCalculatedValue();
}
if ($rowIndex > 2) {
$data[$worksheetTitle]['columnValues'][$rowIndex][] = $cell->getCalculatedValue();
}
}
}
}
return $data;
}Displaying data
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.
/**
* @Route("/import", name="import")
*/
public function importAction(Request $request)
{
$filename = $this->get('kernel')->getRootDir().'/../export/Browser_characteristics.xlsx';
if (!file_exists($filename)) {
throw new \Exception('File does not exist');
}
$spreadsheet = $this->readFile($filename);
$data = $this->createDataFromSpreadsheet($spreadsheet);
return $this->render('AppBundle::import.html.twig', [
'data' => $data,
]);
}
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!
