Create Excel (Xslx) Spreadsheets Using PHPSpreadsheet

Somehow, I've managed to get this far before someone actually required an Excel document to be generated instead of CSV files, so that the document could hold data across multiple "tabs" (worksheets). In order to achieve this, I am going to use PhpSpreadsheet, which is a library written in pure PHP that offers a set of classes that allow you to read and write various spreadsheet file formats, including the xlsx format.

Setup

The first thing we need to do is install the PHPSpreadsheet package.

composer require phpoffice/phpspreadsheet 

Install Required Extensions

Unfortunately, this package requires a a few PHP extensions. If you are using PHP 8.0 on Ubuntu 20.04, then you can install these with:

sudo apt update \
  && sudo apt install -y php8.0-gd php8.0-zip php8.0-mbstring php8.0-xml

This will require the GD extension for PHP. If running PHP 8.0 on Ubuntu, then you can install that with sudo apt install php8.0-gd -y

Example Code

Below is some example code that will write out two lots of two-dimensional arrays of data to two separate worksheets named "Sheet 1" and "Sheet 2".

<?php

require_once(__DIR__ . '/vendor/autoload.php');

$mySpreadsheet = new PhpOffice\PhpSpreadsheet\Spreadsheet();

// delete the default active sheet
$mySpreadsheet->removeSheetByIndex(0);

// Create "Sheet 1" tab as the first worksheet.
// https://phpspreadsheet.readthedocs.io/en/latest/topics/worksheets/adding-a-new-worksheet
$worksheet1 = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($mySpreadsheet, "Sheet 1");
$mySpreadsheet->addSheet($worksheet1, 0);

// Create "Sheet 2" tab as the second worksheet.
$worksheet2 = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($mySpreadsheet, "Sheet 2");
$mySpreadsheet->addSheet($worksheet2, 1);

// sheet 1 contains the birthdays of famous people.
$sheet1Data = [
    ["First Name", "Last Name", "Date of Birth"],
    ['Britney',  "Spears", "02-12-1981"],
    ['Michael',  "Jackson", "29-08-1958"],
    ['Christina',  "Aguilera", "18-12-1980"],
];

// Sheet 2 contains list of ferrari cars and when they were manufactured.
$sheet2Data = [
    ["Model", "Production Year Start", "Production Year End"],
    ["308 GTB",  1975, 1985],
    ["360 Spider",  1999, 2004],
    ["488 GTB",  2015, 2020],
];


$worksheet1->fromArray($sheet1Data);
$worksheet2->fromArray($sheet2Data);


// Change the widths of the columns to be appropriately large for the content in them.
// https://stackoverflow.com/questions/62203260/php-spreadsheet-cant-find-the-function-to-auto-size-column-width
$worksheets = [$worksheet1, $worksheet2];

foreach ($worksheets as $worksheet)
{
    foreach ($worksheet->getColumnIterator() as $column)
    {
        $worksheet->getColumnDimension($column->getColumnIndex())->setAutoSize(true);
    }
}

// Save to file.
$writer = new PhpOffice\PhpSpreadsheet\Writer\Xlsx($mySpreadsheet);
$writer->save('output.xlsx');

Converting Array Data Format

If you're like me, you probably have your data in an associative array format instead, like so:

$sheet1Data = [
    [
        "First Name" => "Britney", 
        "Last Name" => "Spears", 
        "Date of Birth" => "02-12-1981"
    ],
    [
        "First Name" => "Michael", 
        "Last Name" => "Jackson", 
        "Date of Birth" => "29-08-1958"
    ],
    [
        "First Name" => "Christina", 
        "Last Name" => "Aguilera", 
        "Date of Birth" => "18-12-1980"
    ],
];

This is the format you would get if you were to run a mysqli_fetch_all call.

In order to convert it to the "flattened" style for the spreadsheet, then you can use the following conversion function (which I used for my post on creating Google charts):

function convertDataToChartForm($data)
{
    $newData = array();
    $firstLine = true;

    foreach ($data as $dataRow)
    {
        if ($firstLine)
        {
            $newData[] = array_keys($dataRow);
            $firstLine = false;
        }

        $newData[] = array_values($dataRow);
    }

    return $newData;
}

This relies on all rows in the input data having the same keys, and in the same order.

References

• PhpSpreadsheet Documentation