Managing spreadsheets with Symfony and PhpSpreadsheet

Spreadsheets are still everywhere. Business teams use them to share figures, prepare imports or check exports, and they expect a web application to speak their language. Letting users export their data to Excel, or import a file they prepared in a spreadsheet, is often one of the most appreciated features of a business application.

PhpSpreadsheet is the reference PHP library to read and write spreadsheet files: Excel (.xlsx, .xls), LibreOffice Calc (.ods) and CSV. In this guide, we use it in a Symfony 7 application to:

1. create and format a spreadsheet;
2. let users download it in the format of their choice;
3. read a file uploaded by a user.

Prerequisites

• PHP 8.1 or newer, with the zip, xml, mbstring and gd extensions enabled.
• A Symfony 7 application.

Install the library with Composer:

composer require phpoffice/phpspreadsheet

1. Create a spreadsheet

We build a sheet listing a few web browsers with their developer, release date and programming languages. The creation logic lives in a small service, so it can be reused by a controller, a command or a message handler.

namespace App\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

final class BrowserSpreadsheetFactory
{
    private const HEADERS = ['Browser', 'Developer', 'Release date', 'Written in'];

    private const ROWS = [
        ['Google Chrome', 'Google', '2008-09-02', 'C++'],
        ['Firefox', 'Mozilla Foundation', '2002-09-23', 'C++, JavaScript, Rust'],
        ['Microsoft Edge', 'Microsoft', '2015-07-29', 'C++'],
        ['Safari', 'Apple', '2003-01-07', 'C++, Objective-C'],
        ['Opera', 'Opera Software', '1995', 'C++'],
    ];

    public function create(): Spreadsheet
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Browsers');

        // Title on the first row, over the four columns
        $sheet->setCellValue('A1', 'Browser characteristics');
        $sheet->mergeCells('A1:D1');

        // Column names on row 2, data from row 3
        $sheet->fromArray(self::HEADERS, null, 'A2');
        $sheet->fromArray(self::ROWS, null, 'A3');

        return $spreadsheet;
    }
}

The code reads like what a user would do in a spreadsheet application: take the active sheet, write a title, merge cells, fill rows. fromArray() writes a whole array at once from a starting cell, which avoids handling column letters by hand.

2. Format the sheet

PhpSpreadsheet exposes the styles of a cell or a range: font, alignment, borders, fill, number formats. Let's make the two first rows bold and centered, and adjust the width of each column to its content. Add these lines before return $spreadsheet;:

$header = $sheet->getStyle('A1:D2');
$header->getFont()->setBold(true);
$header->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

foreach (range('A', 'D') as $column) {
    $sheet->getColumnDimension($column)->setAutoSize(true);
}

Styles apply to a whole range in one call: no need to loop over each cell.

3. Download the file in the chosen format

The user chooses the format in the URL (/export/xlsx, /export/ods or /export/csv). The route only accepts these three values, and the file is streamed to the browser without being written to the server's disk.

namespace App\Controller;

use App\Spreadsheet\BrowserSpreadsheetFactory;
use PhpOffice\PhpSpreadsheet\IOFactory;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\HeaderUtils;
use Symfony\Component\HttpFoundation\StreamedResponse;
use Symfony\Component\Routing\Attribute\Route;

final class SpreadsheetController extends AbstractController
{
    private const CONTENT_TYPES = [
        'xlsx' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        'ods' => 'application/vnd.oasis.opendocument.spreadsheet',
        'csv' => 'text/csv',
    ];

    #[Route('/export/{format}', name: 'export', requirements: ['format' => 'xlsx|ods|csv'])]
    public function export(string $format, BrowserSpreadsheetFactory $factory): StreamedResponse
    {
        $writer = IOFactory::createWriter($factory->create(), ucfirst($format));

        $response = new StreamedResponse(fn () => $writer->save('php://output'));
        $response->headers->set('Content-Type', self::CONTENT_TYPES[$format]);
        $response->headers->set('Content-Disposition', HeaderUtils::makeDisposition(
            HeaderUtils::DISPOSITION_ATTACHMENT,
            'browser-characteristics.'.$format,
        ));

        return $response;
    }
}

In the template, three links are enough:

<a href="{{ path('export', {format: 'xlsx'}) }}">Excel</a>
<a href="{{ path('export', {format: 'ods'}) }}">LibreOffice</a>
<a href="{{ path('export', {format: 'csv'}) }}">CSV</a>

IOFactory::createWriter() picks the right writer from its name (Xlsx, Ods, Csv), which replaces the switch of the first version of this article.

4. Read a file uploaded by a user

Now the other way around: the user uploads a spreadsheet, and we display its content. We choose the reader from the file extension, and only accept the three expected formats.

use PhpOffice\PhpSpreadsheet\IOFactory;
use Symfony\Component\HttpFoundation\File\UploadedFile;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\HttpKernel\Exception\BadRequestHttpException;

#[Route('/import', name: 'import', methods: ['GET', 'POST'])]
public function import(Request $request): Response
{
    $file = $request->files->get('spreadsheet');
    if (!$file instanceof UploadedFile) {
        return $this->render('spreadsheet/import.html.twig');
    }

    $extension = strtolower($file->getClientOriginalExtension());
    if (!in_array($extension, ['xlsx', 'ods', 'csv'], true)) {
        throw new BadRequestHttpException('Please upload an xlsx, ods or csv file.');
    }

    $reader = IOFactory::createReader(ucfirst($extension));
    $reader->setReadDataOnly(true); // values only, styles are ignored
    $sheet = $reader->load($file->getPathname())->getActiveSheet();

    // One array per row; formulas are calculated
    $rows = $sheet->toArray(null, true, false, false);

    return $this->render('spreadsheet/import.html.twig', [
        'headers' => $rows[1] ?? [],
        'rows' => array_slice($rows, 2),
    ]);
}

• setReadDataOnly(true) only loads cell values, which is faster and lighter when the formatting does not matter.
• toArray() returns the whole sheet as an array of rows. With our file, row 0 holds the title, row 1 the column names and the next rows the data.

The template displays the data in a table:

<form method="post" enctype="multipart/form-data">
    <input type="file" name="spreadsheet" accept=".xlsx,.ods,.csv" required>
    <button type="submit">Import</button>
</form>

{% if rows is defined %}
    <table>
        <thead>
            <tr>{% for header in headers %}<th>{{ header }}</th>{% endfor %}</tr>
        </thead>
        <tbody>
            {% for row in rows %}
                <tr>{% for value in row %}<td>{{ value }}</td>{% endfor %}</tr>
            {% endfor %}
        </tbody>
    </table>
{% endif %}

In a real application, the upload goes through a Symfony form with a File constraint (maximum size, MIME types), and the data is validated before being saved.

Going further

Large files. PhpSpreadsheet loads the whole workbook in memory. Beyond a few tens of thousands of rows, two options:

• read the file by chunks with a read filter (IReadFilter), and process them in a Symfony Messenger handler rather than in the HTTP request;
• switch to a streaming library such as OpenSpout, which reads and writes row by row with a constant memory footprint, at the cost of fewer formatting options.

CSV and formulas. A cell starting with =, +, - or @ can be interpreted as a formula when the CSV is opened in Excel. When you export data typed by users, escape these values (for example by prefixing them with a quote) to avoid CSV injection.

Tests. The spreadsheet factory is a plain service: a unit test can create the spreadsheet, read a few cells with getCell('A3')->getValue() and check the result, without any HTTP request.

With these building blocks, you can cover most spreadsheet needs of a business application: exports, imports, reporting. Need help on a Symfony project? Discover our Symfony agency.

Partager : LinkedIn WhatsApp E-mail