How to Handle Excel Files in Laravel

Time to read: 5 minutes

December 15, 2023
Written by
Prosper Ugbovo
Contributor
Opinions expressed by Twilio contributors are their own
Reviewed by
Matthew Setter
Twilion

Excel, the most popular spreadsheet software around, features a grid-based interface that allows users to organize, compute, and analyze data using rows and columns. It is extensively used for tasks such as creating budgets, managing financial data, tracking inventories, and conducting sophisticated computations.

Excel also has extensive functionality for quickly manipulating and analyzing data. This includes formulae and functions for conducting computations, sorting and filtering of data, building charts and graphs, conditional formatting, pivot tables for summarizing and analyzing data, data validation to ensure data integrity, and much more.

Data handling in web applications sometimes requires manipulating Excel files. There are powerful libraries available for Laravel to perform these operations effectively, whether importing data from Excel files into a database or exporting data from a database to Excel files.

Assume for a moment that you operate a newsletter and require a list of your subscribers in a spreadsheet; Excel's functionality would be very useful.

So, this tutorial series will give you a step-by-step understanding of Excel file handling in Laravel. It will cover everything from reading and writing Excel files to importing and exporting data, validating and sanitizing data, handling file uploads securely, error handling and reporting, along with some advanced Excel features and best practices for efficient development.

Prerequisites

  • PHP 8.2 or newer
  • Access to a MySQL database
  • Composer globally installed
  • Your preferred text editor or IDE
  • Prior knowledge of Laravel and PHP

Set up the project

To begin, you need to create a new Laravel project via Composer, navigate into the project folder, and start the application by running the commands below.

composer create-project laravel/laravel excel-app
cd excel-app
php artisan serve

Next, open the .env file (in your preferred IDE or text editor) to configure your database, replacing the applicable default configuration settings with the applicable values for your database.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=<<YOUR_DATABASE_NAME>>
DB_USERNAME=<<YOUR_DATABASE_USERNAME>>
DB_PASSWORD=<<YOUR_DATABASE_PASSWORD>>

After that, the next step is to install an Excel file-handling library. The Laravel community provides various libraries for interacting with Excel files, one of which stands out, which we will use in this tutorial, is Maatwebsite\Excel.

Ensure you install the required PHP extensions for the library to work properly.

To install the library, execute the following command in a new terminal or terminal session.

composer require maatwebsite/excel

Next, you will need to register the package's Facade and ServiceProvider. To do that, add the following to the "Package Service Providers" section in config/app.php:

Maatwebsite\Excel\ExcelServiceProvider::class,  

Then, add the following to the aliases array in config/app.php.

'Excel' => Maatwebsite\Excel\Facades\Excel::class,

Read an Excel file

Now that the Laravel project has been created and the Excel library has been installed, the next step is to learn how to read Excel files.

You'll need an Excel file, naturally. Fortunately, Microsoft provides an example file you can use in the project, avoiding the need to create something meaningful yourself. To make use of it, open the routes/web.php file and add the following code to it.

use Illuminate\Support\Facades\Storage;
Route::get('/download-file', function () {
    $path = "https://download.microsoft.com/download/1/4/E/14EDED28-6C58-4055-A65C-23B4DA81C4DE/Financial%20Sample.xlsx";
    Storage::disk('local')->put('/data.xlsx', file_get_contents($path));
    return response('done!');
});

When http://127.0.0.1:8000/download-file is accessed, the file from the file path is downloaded and stored in the project's storage/app directory.

An Import class would be required to import this file. To create this class, the package offers an artisan command. Run the command below to create this class.

php artisan make:import DataImport

The command creates a new file named DataImport.php in the app/import directory. This is where all the import files are stored. When you open the file in your favorite IDE or text editor, the file content should match the following.

<?php

namespace App\Imports;

use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;

class DataImport implements ToCollection
{
    /**
    * @param Collection $collection
    */
    public function collection(Collection $collection)
    {
        //
    }
}

When the library attempts to read an Excel file, it examines the interface implemented by the Import object to determine the data that will be returned. The class implements the ToCollection interface, which allows the library to detect that the data type that will be returned is a collection.

Another interface available to the import class is the ToModel class. As you can imagine, it alerts the library that it needs to convert the Excel file data to a database-ready model.

Create a controller using the following command to handle all the Excel logic.

php artisan make:controller ExcelController

Replace the code in the newly created file (app/Http/Controllers/ExcelController.php) with the following code.

<?php

namespace App\Http\Controllers;

use Maatwebsite\Excel\Facades\Excel;

class ExcelController extends Controller
{
    public function import()
    {
        return Excel::toCollection(new \App\Imports\DataImport(), 'data.xlsx');
    }
}

Then, in the routes/web.php file paste the below code.

Route::get('/import', [\App\Http\Controllers\ExcelController::class,'import']);

When you request the newly created route, http://127.0.0.1:8000/import, you'll notice that it returns the data in a file named data.xlsx as a collection — but you didn't return anything in the DataImport class.

This is because the Excel library performs it in the background. When a file is imported via the entry point, the Excel Facade attempts to determine the data type through the file extension, as the library handles various file types.

Then, it delegates the handling to the Maatwebsite\Excel\Reader class which reads the data and returns it to the Facade, which returns the data in the specified format. It’s worth knowing that whatever you return in the collection() method will not be returned to the controller.

Import data from an Excel file into the database

What would you do if you needed to import financial data from an Excel spreadsheet into your Laravel application? To begin with, you'd need to create a model and a migration file. To achieve this, run the following command.

php artisan make:model FinancialData -m

In the newly created migration file, stored in the database/migrations/*create_financial_data_table.php, update the up() method to match the code below.

public function up(): void
{
    Schema::create('financial_data', function (Blueprint $table) {
        $table->id();
        $table->string('segment');
        $table->string('country');
        $table->string('product');
        $table->string('Discount Band');
        $table->string('Units Sold');
        $table->string('Manufacturing Price');
        $table->string('Sale Price');
        $table->string('Gross Sales');
        $table->string('Discounts');
        $table->string('Sales');
        $table->string('COGS');
        $table->string('Profit');
        $table->string('Date');
        $table->string('Month Number');
        $table->string('Month Name');
        $table->string('Year');
        $table->timestamps();
    });
}

The migration file now contains columns matching the headers of the Excel file.  In the app/models/FinancialData.php, add the following line of code to guard the table's id:

protected $guarded = ['id'];

This will prevent the id column from being mass-assigned, which can help to protect your data from being accidentally overwritten or deleted. Next, you would create an import class to handle the import. 

First, run the command below to generate the model.

php artisan make:import FinancialDataImport --model=FinancialData

Then, paste the code below into the newly generated file.

<?php

namespace App\Imports;

use App\Models\FinancialData;
use Maatwebsite\Excel\Concerns\ToModel;

class FinancialDataImport implements ToModel
{
    /**
     * @param array $row
     *
     * @return \Illuminate\Database\Eloquent\Model|null
     */
    public function model(array $row)
    {
        return new FinancialData([
            'segment' => $row[0],
            'country' => $row[1],
            'product' => $row[2],
            'Discount Band' => $row[3],
            'Units Sold' => $row[4],
            'Manufacturing Price' => $row[5],
            'Sale Price' => $row[6],
            'Gross Sales' => $row[7],
            'Discounts' => $row[8],
            'Sales' => $row[9],
            'COGS' => $row[10],
            'Profit' => $row[11],
            'Date' => $row[12],
            'Month Number' => $row[13],
            'Month Name' => $row[14],
            'Year' => $row[15],
        ]);
    }
}

Next, run the migration using the following command.

php artisan migrate

Finally, update the import() method in the ExcelController to match the code below.

public function import()
{
    Excel::import(new \App\Imports\FinancialDataImport(), 'data.xlsx');
}

With all of the changes made, visit the http://127.0.0.1:8000/import route to test that the code works. Although it appears that no action has occurred, if you check your database you will see that the data has been added. Examining the table closely, you will see that the first row contains the header row of the Excel file — which isn't meant to be. 

To fix this, the library offers the interface WithHeadingRow.The interface selects the first row of the file as the heading row by default. But if this is not the case, you may change it using the headingRow() method, which returns the row as an integer.

First, update the code In the app/imports/FinancialDataImport.php file to match the code below:

<?php

namespace App\Imports;

use App\Models\FinancialData;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class FinancialDataImport implements ToModel, WithHeadingRow
{
    /**
     * @param array $row
     *
     * @return \Illuminate\Database\Eloquent\Model|null
     */
    public function model(array $row)
    {
        return new FinancialData([
            'segment' => $row['segment'],
            'country' => $row['country'],
            'product' => $row['product'],
            'Discount Band' => $row['discount_band'],
            'Units Sold' => $row['units_sold'],
            'Manufacturing Price' => $row['manufacturing_price'],
            'Sale Price' => $row['sale_price'],
            'Gross Sales' => $row['gross_sales'],
            'Discounts' => $row['discounts'],
            'Sales' => $row['sales'],
            'COGS' => $row['cogs'],
            'Profit' => $row['profit'],
            'Date' => $row['date'],
            'Month Number' => $row['month_number'],
            'Month Name' => $row['month_name'],
            'Year' => $row['year'],
        ]);
    }

    public function headingRow(): int
    {
        return 1;
    }
}

Run a fresh migration by running the following command.

php artisan migrate:fresh

Then, visit the /import route again. This time the header row isn’t passed alongside other data into the database.

Export data from the database to an Excel file

Just like you imported data from an Excel file into the database, you can also export data from the database into an Excel file. To demonstrate this, let's export all of the users in the user database.

At this point, there are no users in the table. To resolve this, you would need to run the database seeder. In the run() function in the database/seeders/DatabaseSeeder.php file, add the following line of code.

\App\Models\User::factory(20)->create();

Then, seed the database by running the command below.

php artisan db:seed

This adds 20 records to the database's user table. The library also provides an artisan command to create a class to handle the data export.

php artisan make:export UsersExport --model=User

This creates a UsersExport.php file in the app/exports directory. In that file, a function is defined and it returns all records of users in the database which is to be sent to the Maatwebsite\Excel\Writer.php that handles the dataexport.

What’s left to do is to create the export route and a corresponding controller. In the routes/web.php add the below code.

Route::get('/export', [\App\Http\Controllers\ExcelController::class,'export']);

Next, in the ExcelController class, add the function below to the file.

public function export()
{
    return Excel::download(new \App\Exports\UsersExport, 'users.xlsx');
}

Finally, in the browser, visit to http://127.0.0.1:8000/export. In the background, the library executes the query and retrieves all of the data from the given model before parsing the data into an Excel format and invoking the download function, which is dependent on Laravel’s Response class, then downloads the file in your browser.

That's the essentials of reading and writing Excel files in Laravel

You've learned how to handle an Excel file in Laravel throughout this tutorial. You've learned Excel file handling, from importing to creating a custom template for exporting to an Excel file.

The second part of this series will cover upload security considerations, error handling, dealing with charts, and many more topics.

Prosper is a freelance Laravel web developer and technical writer who enjoys working on innovative projects that use open-source software. When he's not coding, he searches for the ideal startup opportunities to pursue. You can find him on Twitter and Linkedin.

Related Posts

Related Resources

A newspaper article

Twilio Docs

From APIs to SDKs to sample apps

API reference documentation, SDKs, helper libraries, quickstarts, and tutorials for your language and platform.
An Open book

Resource Center

The latest ebooks, industry reports, and webinars

Learn from customer engagement experts to improve your own communication.
User group reactions

Ahoy

Twilio's developer community hub

Best practices, code samples, and inspiration to build communications and digital engagement experiences.