Programster's Blog

Tutorials focusing on Linux, programming, and open-source

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.

Steps

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
(
    [0] => Array
        (
            [0] => ID
            [1] => Circumstance
        )

    [1] => Array
        (
            [0] => 1
            [1] => Metal-Detecting
        )

    [2] => Array
        (
            [0] => 2
            [1] => Chance find
        )

    [3] => Array
        (
            [0] => 3
            [1] => Fieldwalking
        )

    [4] => Array
        (
            [0] => 4
            [1] => Mudlarking
        )

    [5] => Array
        (
            [0] => 5
            [1] => Gardening
        )

    [6] => Array
        (
            [0] => 6
            [1] => Other chance find
        )

    [7] => Array
        (
            [0] => 7
            [1] => Archaeological investigation
        )

    [8] => Array
        (
            [0] => 8
            [1] => Construction
        )

    [9] => Array
        (
            [0] => 9
            [1] => Agriculture or drainage work
        )

    [10] => Array
        (
            [0] => 10
            [1] => Investigation of shipwreck
        )

    [11] => Array
        (
            [0] => 11
            [1] => 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 bad example 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.

Last updated: 23rd February 2022
First published: 23rd February 2022

This blog is created by Stuart Page

I'm a freelance web developer and technology consultant based in Surrey, UK, with over 10 years experience in web development, DevOps, Linux Administration, and IT solutions.

Need support with your infrastructure or web services?

Get in touch