ExcelFileReader.php

<?php

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

/**
 * ExcelFileReader.
 */
class ExcelFileReader
{
    /**
     * Load excel worksheet and return all values as array.
     *
     * @param string $fileName filename
     *
     * @return array Values
     */
    public function loadAll(string $fileName): array
    {
        $reader = new Xlsx();
        $reader->setReadDataOnly(true);

        $excel = $reader->load($fileName);
        $worksheet = $excel->getActiveSheet();
        $highestRow = $worksheet->getHighestRow();
        $highestColumn = $worksheet->getHighestColumn();
        $highestColumnIndex = Coordinate::columnIndexFromString($highestColumn);

        $rows = [];
        for ($row = 1; $row <= $highestRow; $row++) {
            for ($col = 1; $col <= $highestColumnIndex; $col++) {
                $cell = $worksheet->getCellByColumnAndRow($col, $row);
                $rows[$row][$col] = $cell !== null ? $cell->getValue() : null;
            }
        }

        $result = [];
        if (empty($rows)) {
            return [];
        }
        // Remove first row (field titles) and extract field names
        $fields = array_splice($rows, 0, 1);
        $fields = $fields[0];
        $unknownField = 1;
        foreach ($fields as $index => $field) {
            if (!isset($field)) {
                $fields[$index] = 'unknown_' . $unknownField++;
            }
        }

        // Copy values to field names
        foreach ($rows as $rowNumber => $row) {
            foreach ($row as $index => $value) {
                if (!isset($fields[$index])) {
                    continue;
                }
                $name = $fields[$index];
                $result[$rowNumber][$name] = $value;
            }
        }

        return $result;
    }
}