Using Spout to read MS Excel sheet

In this tutorial we’ll use Spout PHP library to read MS Excel sheet and insert sheet’s data into database. But the first question is why Spout PHP library instead of ubiquitous PhpSpreadsheet. The simple answer is read speed. There is no doubt that for handling MS Excel or other spreadsheet files in PHP based app the first choice is PhpSpreadsheet with its extensive features and reliability. However, it’s also infamous for its read speed. That’s where Spout PHP library shines with its lightweight architecture. It reads MS Excel files quite fast with low memory footprint (3MB as claimed on its website). So let’s dive into using Spout PHP library to extract data from MS Excel sheet.

You can start by creating a fresh laravel 8 app by entering following command:

composer create-project laravel/laravel example 8.*

Now install phpspreadsheet by executing the following command inside project directory:

composer require box/spout

Next, setup database and Laravel environment file as usual. Create new SQLite database by creating a new empty file named ‘database.sqlite’ in ‘database’ folder. For this purpose, if you are using any *nix based OS, you can enter following commands in the app folder:

touch database/database.sqlite

Now, edit .env file as follows:

DB_CONNECTION = sqlite

You can remove DB_HOST, DB_PORT, DB_DATABASE, DB_USERNAME, DB_PASSWORD from .env when using SQLite database.

Enter the following command to create Salary model along with the migration:

php artisan make:model Salary -m

Open newly created migration file from database/migrations/ which is named something like …create_salaries_table.php and then insert the desired table fields e.g.

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateSalariesTable extends Migration
{
    public function up()
    {
        Schema::create('salaries', function (Blueprint $table) {
            $table->id();
            $table->date('joining_date');
            $table->string('name');
            $table->string('department');
            $table->string('designation');
            $table->decimal('gross_salary',14,2);
            $table->decimal('medical',14,2);
            $table->decimal('conveyance',14,2);
            $table->timestamps();
        });
    }
    public function down()
    {
        Schema::dropIfExists('salaries');
    }
}

Enter following command to migrate the above migration:

php artisan migrate

Now, amend the Salary model by inserting fillable array into the model. For that, open app/Models/Salary.php and insert protected fillable array as follows:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Salary extends Model
{
    use HasFactory;

    protected $fillable = [
        'joining_date', 'name', 'department', 'designation', 'gross_salary', 'medical', 'conveyance'
    ];
}

For data input we’ll use an Excel sheet which can be downloaded from here. After downloading, put this Excel file in public folder of the project.

In Laravel, there is a convenient way of handling a complex controller action – by creating a Single Action Controller. This type of controller contains single method __invoke() and can be called by controller name only without mentioning the method name. So, let’s create a Single Action Controller for handling data input from MS Excel sheet:

php artisan make:controller Excel --invokable

Now, open the newly created file Excel.php in app/Http/Controllers/ and enter the following code:

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Box\Spout\Reader\Common\Creator\ReaderEntityFactory;
use App\Models\Salary;
use Carbon\Carbon;

class Excel extends Controller
{
    public function __invoke(Request $request)
    {
        $reader = ReaderEntityFactory::createXLSXReader();
        $reader->open('data.xlsx');
        foreach ($reader->getSheetIterator() as $sheet) {
            // only read data from 1st sheet
            if ($sheet->getIndex() === 0) { // index is 0-based
                foreach ($sheet->getRowIterator() as $rowIndex => $row) {
                    if($rowIndex === 1) continue; // skip headers row
                    $col1 = $row->getCellAtIndex(0); // id
                    $col2 = $row->getCellAtIndex(1)->getValue(); // joining date - using getValue() method of Cell object to get actual date value
                    $col3 = $row->getCellAtIndex(2); // name
                    $col4 = $row->getCellAtIndex(3); // department
                    $col5 = $row->getCellAtIndex(4); // designation
                    $col6 = $row->getCellAtIndex(5); // gross salary
                    $col7 = $row->getCellAtIndex(6); // medical
                    $col8 = $row->getCellAtIndex(7); // conveyance
                    Salary::create([
                        // no need to insert $col1 as the id auto-generates
                        'joining_date' => $col2,
                        'name' => $col3,
                        'department' => $col4,
                        'designation' => $col5,
                        'gross_salary' => $col6,
                        'medical' => $col7,
                        'conveyance' => $col8,
                    ]);
                }
                break; // no need to read more sheets
            }
            $reader->close();
        }
    }
}

Finally, open routes/web.php and put the following line at the top of it:

use App\Http\Controllers\Excel;

And then insert following route into this web.php:

Route::get('/excel', Excel::class)->name('excel');

Run Laravel development server by executing:

php artisan serve

Enter the following URL in your browser:

localhost:8000/excel

By executing the above URL, salaries table in the database will be filled with data from the data.xlsx MS Excel sheet.