PHPSpreadsheet - Read Excel (.xlsx) File To Array
Clients often prefer to send me Excel .xlsx files that are basically just CSV files. E.g. there is just one sheet and it's simply a table of data starting from A0. In such circumstances, one can manually convert them to CSV format, or one can have PhpSpreadsheet read them directly as-is. This tutorial will show you how to do the latter.
Below is an commented example of how to read a .xlsx spreadsheet that does better to explain than I could do with words.
<?php // include the autoloader, so we can use PhpSpreadsheet require_once(__DIR__ . '/vendor/autoload.php'); # Create a new Xls Reader $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx(); // Tell the reader to only read the data. Ignore formatting etc. $reader->setReadDataOnly(true); // Read the spreadsheet file. $spreadsheet = $reader->load(__DIR__ . '/path/to/file.xlsx'); $sheet = $spreadsheet->getSheet($spreadsheet->getFirstSheetIndex()); $data = $sheet->toArray(); // output the data to the console, so you can see what there is. die(print_r($data, true));
With the following example file, you get the following output:
Array (  => Array (  => ID  => Circumstance )  => Array (  => 1  => Metal-Detecting )  => Array (  => 2  => Chance find )  => Array (  => 3  => Fieldwalking )  => Array (  => 4  => Mudlarking )  => Array (  => 5  => Gardening )  => Array (  => 6  => Other chance find )  => Array (  => 7  => Archaeological investigation )  => Array (  => 8  => Construction )  => Array (  => 9  => Agriculture or drainage work )  => Array (  => 10  => Investigation of shipwreck )  => Array (  => 11  => Unknown ) )
However, I found that if I opened the spreadsheet (in LibreOffice), and pressed Ctrl + A to select all cells, and then manually set the font to Arial, I generated the following
that would consider every cell to having an empty value. This suggests to me that the
$reader->setReadDataOnly(true); doesn't quite cover me. If you know of a change that will
resolve this, then please paste a solution in the comments.
First published: 23rd February 2022