One open() for XLSX, XLS, and CSV: what's new in FastExcelReader 4.2

We have a small tradition here: no sooner do we write a blog post about a new version than the next one ships. The ink had not dried on the article about 4.0 and its long-awaited legacy XLS support, and here we are writing about 4.2. So let's do both at once: briefly — what happened between the releases, and in detail — the headline feature of 4.2.

And the headline is simple and pleasant: Excel::open() now reads XLSX, XLS, and CSV — one method, one code path, without a single if on the file extension.

The pain: an import that branches on the extension#

The classic “accept a file from the user” snippet looks like this — and you have almost certainly written one yourself:

use avadim\FastExcelReader\Excel;

$reader = match (strtolower(pathinfo($file, PATHINFO_EXTENSION))) {
    'csv', 'txt', 'tsv' => Excel::openCsv($file),
    default             => Excel::open($file), // xlsx / xls
};

foreach ($reader->withHeader()->nextRow() as $rowNum => $row) {
    importRow($row, $rowNum);
}

It works — as long as the extension tells the truth. And it lies constantly:

• a manager saved a “CSV” out of Excel, and inside it is a real XLSX carrying a .csv extension;
• an integration drops its export into report.dat, or into a file with no extension at all;
• a user renamed .xls to .xlsx because “the system asked for it,” and inside sits an old OLE2 binary;
• the upload handler passes you a temporary file named php7A2B.tmp, and pathinfo() throws up its hands.

Every one of these cases ends either in a cryptic error somewhere deep in the parser or, worse, in a silently corrupted import. The extension is a hint from the file system, not a fact about the content.

The fix: format detection by signature#

In 4.0 we taught Excel::open() to pick the reader from the file signature rather than the extension — back then that covered the XLSX/XLS pair. In 4.2 CSV joined the same entry point. Now open() looks at the first bytes of the file and decides on its own:

• the OLE2 signature (D0 CF 11 E0 …) — legacy XLS;
• the ZIP signature (PK\x03\x04) — XLSX;
• anything else — read as delimited text, that is, CSV.

The extension is not consulted at all. The same handler is now a single line:

use avadim\FastExcelReader\Excel;

$book = Excel::open($file); // xlsx, xls, or csv — decided by content

foreach ($book->withHeader()->nextRow() as $rowNum => $row) {
    importRow($row, $rowNum);
}

A CSV file now comes back from open() as a Csv\CsvBook — an ordinary workbook with a single sheet that exposes the very same reading interface (AbstractSheet) as XLSX and XLS: the same withHeader(), nextRow(), readRows(), readColumns(), the KEYS_* key modes, and read areas. An import that accepts whatever it is given stops being three branches of code and becomes one.

For parity with XLSX, column keys through open() default to letters — A, B, C — so code written for Excel reads CSV without edits.

When you want to say it explicitly#

Auto-detection is reliable, but sometimes the format is known in advance — and then you can pin it down. The second argument of open() (added in 4.2, backward compatible) takes CSV reader options, and 'format' => 'csv' forces CSV mode:

// force CSV reading and configure the parser right away
$book = Excel::open($file, [
    'format'    => 'csv',
    'delimiter' => ';',
    'encoding'  => 'Windows-1251',
]);

If what you need is not the workbook but the low-level parsing engine (with onError(), tolerant mode, and the rest of the CSV specifics covered in the separate article about the CSV reader), it is right at hand:

$reader = $book->getReader();   // Csv\CsvReader from inside CsvBook
// or directly, as before:
$reader = Excel::openCsv($file);

openCsv() has not gone anywhere and still returns the Csv\CsvReader engine directly — for when that is exactly what you want instead of the workbook API.

Along the way — clear errors instead of cryptic ones#

Now that open() recognizes formats, it also stopped failing mysteriously on unsuitable files:

• a ZIP that is not an XLSX (a DOCX or PPTX, say — those are ZIP containers too) produces the readable message “Not an XLSX workbook: the ZIP archive has no xl/workbook.xml” with a hint about DOCX/PPTX — instead of the former cryptic “Internal file not found: xl/_rels/workbook.xml.rels”;
• binary garbage (a NUL byte or a high share of control characters) is rejected right away with “file appears to be binary” rather than breaking somewhere deep in the parser. UTF-16/UTF-32 text does not trip this check; if needed, it can be disabled with the allow_binary option (or CsvOptions::setAllowBinary(true)).

And for those who need detection separately from reading, there is now Excel::isXlsx() — a PK\x03\x04 signature check, symmetrical to the long-standing Excel::isXls().

What 4.1 brought: dates no longer depend on the locale#

That is the release that slipped in between 4.0 and 4.2. Built-in date formats (numFmtId codes 14–22, including “short date” under code 14) could previously render differently depending on whether ext-intl was installed and which locale the server ran — the same file produced different strings on different machines. In 4.1 those codes resolve to fixed patterns regardless of the environment.

If you deliberately want the old behavior (locale-driven date rendering), you now turn it on explicitly:

$excel->useLocaleFormats('en_US'); // requires ext-intl; without an argument — the process locale

Honest boundaries#

A CSV read through open() is still a CSV, not an Excel workbook:

• it has no styles, no number and date typing, no merged cells, no images. The common reading methods that every format shares simply return emptiness for CSV: readCellStyles() gives values without formatting, getMergedCells() returns an empty array. A unified API means unified calls, not the magical appearance of data the format does not carry;
• the library still only reads — writing and editing files is FastExcelWriter's job;
• and it still does not evaluate formulas — it returns the formula text and the last saved value.

Who should upgrade#

Anyone whose input is files “from the outside”: user uploads, integrations, migrations, exports from accounting systems and other software where the file extension is a wish rather than a guarantee. In 4.2 the handler for such files shrinks to a single Excel::open($file) that works out for itself whether it is looking at XLSX, XLS, or CSV and returns everything through one and the same interface. The upgrade is safe: the second argument of open() is optional, and openCsv() behaves as before.

composer require avadim/fast-excel-reader

Documentation and examples are in the GitHub repository. As for 4.3 — we will try to write about it before 4.4 ships. We will try.