Laravel Excel – Excel spreadsheet handling

Laravel Excel is a powerful package for Laravel that simplifies the import and export of Excel spreadsheets. It provides an intuitive API for working with Excel files, making it easy to handle large datasets efficiently. With features like chunk reading, automatic row validation, and customizable exports, Laravel Excel is a great choice for applications that require spreadsheet functionality.

Key Features of Laravel Excel:

Import and Export: Easily import data from Excel files and export data to Excel format.
Chunk Reading: Handle large datasets by reading them in chunks to avoid memory issues.
Data Validation: Automatically validate data during import to ensure it meets your requirements.
Customizable Exports: Create customizable Excel files with complex formatting and styling.
Multiple Formats: Supports various file formats like .xlsx, .xls, .csv, and .ods.

Installation

To get started with Laravel Excel, follow these steps:

Require the Package: Install the package via Composer:

composer require maatwebsite/excel

1

composer require maatwebsite/excel
Publish the Configuration (Optional): You can publish the configuration file if you need to customize it:

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config

1

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config

Importing Excel Files

You can import Excel files using the Excel::import method. First, you need to create an import class.

Creating an Import Class

You can create an import class using the Artisan command:

php artisan make:import UsersImport --model=User

1	php artisan make:import UsersImport --model=User

This command will create a new import class in the App\Imports directory. Here’s how you can structure the import class:

Example Import Class (app/Imports/UsersImport.php):

namespace App\Imports;

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

class UsersImport implements ToModel, WithHeadingRow
{
    public function model(array $row)
    {
        return new User([
            'name'  => $row['name'],
            'email' => $row['email'],
            'password' => bcrypt($row['password']),
        ]);
    }
}

namespace App\Imports;

use App\Models\User;

use Maatwebsite\Excel\Concerns\ToModel;

use Maatwebsite\Excel\Concerns\WithHeadingRow;

class UsersImport implements ToModel, WithHeadingRow

{

public function model(array $row)

{

return new User([

'name' => $row['name'],

'email' => $row['email'],

'password' => bcrypt($row['password']),

]);

}

In this example, the import class implements the ToModel and WithHeadingRow interfaces, which allows you to map the imported data to the User model and automatically use the first row as headers.

Importing the File

You can import an Excel file in your controller like this:

use Maatwebsite\Excel\Facades\Excel;
use App\Imports\UsersImport;

public function import()
{
    Excel::import(new UsersImport, request()->file('file'));

    return redirect('/')->with('success', 'All good!');
}

use Maatwebsite\Excel\Facades\Excel;

use App\Imports\UsersImport;

public function import()

{

Excel::import(new UsersImport, request()->file('file'));

return redirect('/')->with('success', 'All good!');

}

Exporting Excel Files

To export data to Excel, you can create an export class.

Creating an Export Class

You can create an export class using the Artisan command:

php artisan make:export UsersExport --model=User

1	php artisan make:export UsersExport --model=User

Example Export Class (app/Exports/UsersExport.php):

namespace App\Exports;

use App\Models\User;
use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromCollection
{
    public function collection()
    {
        return User::all();
    }
}

namespace App\Exports;

use App\Models\User;

use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromCollection

{

public function collection()

{

return User::all();

}

This class implements the FromCollection interface, which allows you to export a collection of users.

Exporting the Data

You can export the data in your controller like this:

use Maatwebsite\Excel\Facades\Excel;
use App\Exports\UsersExport;

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

use Maatwebsite\Excel\Facades\Excel;

use App\Exports\UsersExport;

public function export()

{

return Excel::download(new UsersExport, 'users.xlsx');

}

Handling Large Datasets

When dealing with large datasets, you can read and write files in chunks to optimize memory usage.

Importing in Chunks

You can import large files in chunks using the chunkSize method:

Excel::filter('chunk')->import(new UsersImport, request()->file('file'), null, \Maatwebsite\Excel\Excel::XLSX, [
    'chunk_size' => 1000,
]);

Excel::filter('chunk')->import(new UsersImport, request()->file('file'), null, \Maatwebsite\Excel\Excel::XLSX, [

'chunk_size' => 1000,

]);

Exporting in Chunks

For exporting, you can also use the FromQuery interface to handle large queries efficiently:

Example Export Class:

namespace App\Exports;

use App\Models\User;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithHeadings;

class UsersExport implements FromQuery, WithHeadings
{
    public function query()
    {
        return User::query();
    }

    public function headings(): array
    {
        return [
            'ID',
            'Name',
            'Email',
            'Created At',
            'Updated At',
        ];
    }
}

namespace App\Exports;

use App\Models\User;

use Maatwebsite\Excel\Concerns\FromQuery;

use Maatwebsite\Excel\Concerns\WithHeadings;

class UsersExport implements FromQuery, WithHeadings

{

public function query()

{

return User::query();

}

public function headings(): array

{

return [

'ID',

'Name',

'Email',

'Created At',

'Updated At',

];

}

Conclusion

Laravel Excel is a comprehensive solution for handling Excel spreadsheets in Laravel applications. With its rich features and ease of use, it can significantly simplify the process of importing and exporting data.

Additional Considerations

Validation: Consider adding validation rules to your import classes to ensure data integrity.
Styling Exports: You can customize the styling of your exports by implementing additional interfaces such as WithStyles.
Documentation: Refer to the official Laravel Excel documentation for more advanced features and use cases.

Laravel Excel – Excel spreadsheet handling

Key Features of Laravel Excel:

Installation

Importing Excel Files

Creating an Import Class

Importing the File

Exporting Excel Files

Creating an Export Class

Exporting the Data

Handling Large Datasets

Importing in Chunks

Exporting in Chunks

Conclusion

Additional Considerations

Related Posts:

Leave a Comment Cancel Reply