How to read excel data with PhpSpreadsheet

PhpSpreadsheet provides a PHP library of classes to read and write formats such as LibreOffice Calc, Microsoft Office and CSV. The default encoding uses UTF-8.

Ref: https://phpspreadsheet.readthedocs.io/en/latest/faq/

In Laravel 5, 6 and 7, this is available through the Composer. 

composer require phpoffice/phpspreadsheet niklasravnsborg/laravel-pdf mpdf/mpdf:8.1.6 league/csv

OR

composer require phpoffice/phpspreadsheet niklasravnsborg/laravel-pdf mpdf/mpdf league/csv

Here is an example of how to read the first three columns of an xlsx file, where the first row is considered as headers.

Step 1: Import the class for LibreOffice

use PhpOffice\PhpSpreadsheet\Reader\Ods;

Or for Microsoft Excel

use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

Step 2: In the function that is to retrieve the data, declare object for LibreOffice

$reader = new Ods();

OR for Microsoft Excel

$reader = new Xlsx();

Step 3: Load the spreadsheet data 

$spreadsheet = $reader->load($path);
$sheet = $spreadsheet->getActiveSheet();

OR, load a sheet with specified name

$spreadsheet = $reader->setLoadSheetsOnly(["Sheet1"])->load($path);

$sheet = $spreadsheet->getActiveSheet();

Step 4: Process rows, and skip header in first row

$users = new Users();

if(!empty($sheet)) {

    foreach ($sheet->getRowIterator() as $row) {

      if ($row->getRowIndex() === 1) {

         continue; //Skip heading

      }

      $cells = iterator_to_array($row->getCellIterator("A", "H"));

      $data = [

            "Column A" => $cells["A"]->getValue(),

            "Column B" => $cells["B"]->getValue(),

            "Column C" => $cells["C"]->getValue(),

      ];

      $users->add($data);

}

The example can be said to retrieve the first 3 columns of the spreadsheet row as fields to create a new user.