The fatal was in a file called Font.php, in a method called getHashCode(), and the message was the plain old one:
PHP Fatal error: Maximum execution time of 30 seconds exceeded in .../PhpSpreadsheet/Style/Font.php
If you’ve followed this blog for a few weeks you’ll know about the itinerary builder: an advisor answers a set of questions, a model drafts the trip, and the plugin turns the result into a styled multi-tab .xlsx with PhpSpreadsheet. I’ve written about its timeouts more than once. Those were all about waiting on the model. This one had nothing to do with the model at all. The model was finished. PHP was just building a spreadsheet, and running out of time doing it.
Six seconds for a spreadsheet
On my dev box an 8-day trip took about six seconds to build the workbook. A 91-day trip took about 27.
Six seconds is already a long time to spend writing a few tabs of a spreadsheet. I’d assumed that was just what PhpSpreadsheet cost and moved on. It wasn’t.
Live hosts run the default 30-second max_execution_time, and neither of the two code paths that build the workbook raised it. So longer trips on slower hosts fell over, and short ones were fine, which is a nice way to ship a bug that passes every test you’d naturally run.
Where the time goes
Every time you do something like this in PhpSpreadsheet:
$sheet->getStyle('B4')->getFont()->setBold(true);
it doesn’t just flip a flag on that cell. Styles in an .xlsx are shared: the file holds one list of distinct styles and each cell points at one of them. So after the change, PhpSpreadsheet goes looking for an existing style that’s identical to the new one, to reuse it rather than add a duplicate. That lookup lives on the Spreadsheet class:
public function getCellXfByHashCode(string $hashcode): bool|Style
{
foreach ($this->cellXfCollection as $cellXf) {
if ($cellXf->getHashCode() === $hashcode) {
return $cellXf;
}
}
return false;
}
A linear walk over every style in the workbook, calling getHashCode() on each one. And getHashCode() isn’t a stored value, it’s an md5 over the font, fill, borders, alignment, number format and so on. Computed fresh, every time, for every style, on every style change.
So the cost of one style change grows with the number of styles already in the workbook, and a heavily formatted workbook makes a lot of style changes. Each new one scans further than the last. Quadratic in the number of styles. That’s where the 30 seconds went: hashing the same styles over and over again.
To be fair to PhpSpreadsheet, a linear scan is perfectly sensible for the spreadsheets most people build, and the hash has to be recomputed somewhere because styles in the collection can be mutated in place. I was just building one that was unusually fussy about formatting.
An index, with a safety net
The fix is a subclass of Spreadsheet that keeps a hash-to-style map next to the collection, so the lookup is a single array access instead of a scan. Trimmed down:
class IndexedSpreadsheet extends Spreadsheet {
private array $xfByHash = [];
private int $indexed = -1; // how many collection entries the map covers; -1 = rebuild
public function getCellXfByHashCode(string $hashcode): bool|Style {
if ($this->indexed !== count($this->getCellXfCollection())) {
$this->reindex();
}
$hit = $this->xfByHash[$hashcode] ?? null;
if ($hit === null) {
return false;
}
if ($hit->getHashCode() === $hashcode) {
return $hit;
}
// The style changed since we indexed it. Trust the full scan instead.
$this->indexed = -1;
return parent::getCellXfByHashCode($hashcode);
}
public function addCellXf(Style $style): void {
parent::addCellXf($style);
if ($this->indexed === count($this->getCellXfCollection()) - 1) {
$this->xfByHash[$style->getHashCode()] ??= $style;
$this->indexed++;
} else {
$this->indexed = -1;
}
}
// removeCellXfByIndex() and garbageCollect() also just set $indexed = -1.
}
The parts I cared most about are the defensive ones.
A hit gets re-checked against the style’s current hash before it’s returned. Styles in the collection can be changed in place (the default style is the obvious example), and an index that returns a style which no longer matches would quietly put the wrong formatting on a cell. On any doubt it falls through to the original scan.
A miss is always safe. The worst a wrong miss can do is add a duplicate style to the file. Slightly bigger file, same appearance. Never a wrong style.
And anything that rebuilds the collection throws the index away.
Only on the version I tested
There’s one more wrinkle, and it’s a WordPress one. The plugin vendors its own copy of PhpSpreadsheet. But on a WordPress site there’s always a chance some other plugin loaded its copy of the same library first, possibly a different version, and then the class names resolve to theirs. A subclass that overrides methods with typed signatures can fatal if the parent’s signatures differ.
So the subclass is only used when the loaded Spreadsheet class actually came from the plugin’s own copy:
function new_spreadsheet(): Spreadsheet {
static $fast = null;
if ($fast === null) {
$fast = false;
try {
$file = (new ReflectionClass(Spreadsheet::class))->getFileName();
$ours = wp_normalize_path(PLUGIN_DIR . 'lib/phpspreadsheet/');
if (is_string($file) && strpos(wp_normalize_path($file), $ours) === 0) {
require_once PLUGIN_DIR . 'includes/class-indexed-spreadsheet.php';
$fast = class_exists('IndexedSpreadsheet', false);
}
} catch (Throwable $e) {
$fast = false;
}
}
return $fast ? new IndexedSpreadsheet() : new Spreadsheet();
}
If someone else’s copy won the race, you get the plain class. Slow on a long trip, but never a fatal. I’ll take that trade.
Both build paths also got set_time_limit(180) while I was there. That’s not the fix, it’s headroom. A quadratic problem with a bigger time limit is still a quadratic problem; it just falls over on a longer trip.
Did it work
I rebuilt eight archived itineraries both ways and compared the files. Byte-identical output. The 91-day trip went from around 27 seconds to under one. The 8-day one went to about a quarter of a second. Somewhere between 20 and 28 times faster depending on the trip.
The byte-identical part mattered more to me than the speed. A performance fix that changes the output is a different fix, and I’d want to review it as one.
The takeaway
I nearly read past the useful part of the error. A max_execution_time fatal tells you which line was running when the clock ran out, and a line that’s running when the clock runs out is usually a line that runs a lot. It’s a profiler with a sample size of one. Crude, but it pointed straight at getHashCode(), and from there it was a short walk to the loop calling it.
I’d been treating the six seconds as the cost of doing business. It was the same bug, just on a short trip.