Import Export to Excel and CSV using Maatwebsite in Laravel 5

Maatwebsite is a PHP package that allows you to easily import and export data to and from Excel and CSV files. In this article, we'll explore how to use Maatwebsite in Laravel 5 to import and export data to and from Excel and CSV files.

Installation

To install Maatwebsite, run the following command:

composer require maatwebsite/excel

Once the package is installed, you'll need to install some additional dependencies:

composer require phpoffice/phpspreadsheet
composer require phpoffice/phpspreadsheet

Exporting to Excel

To export data to Excel using Maatwebsite in Laravel 5, you can follow these steps:

Syntax

Excel::create('filename', function($excel) {
    $excel->sheet('sheetname', function($sheet) {
        // add data to the sheet
    });
})->export('xls');

Example

use Maatwebsite\Excel\Facades\Excel;

public function exportToExcel()
{
    Excel::create('users', function($excel) {

        $excel->sheet('sheet1', function($sheet) {

            $users = User::select('name', 'email')->get();

            $sheet->fromArray($users);

        });

    })->export('xls');
}

Output

This will create an Excel file named "users.xls" with the data from the "users" table.

Explanation

First, we create a new Excel file by calling the Excel::create method and passing in the name of the file we want to create. Inside the closure, we create a new sheet by calling $excel->sheet. Inside the sheet closure, we fetch the data we want to export (in this case, all columns from the users table) and add it to the sheet by calling $sheet->fromArray.

Use

Exporting data to Excel is useful when you need to create reports or share data with other applications that support Excel files.

Important Points

• The data you want to export must be in the form of an array or a Collection.
• The Excel::create method takes a closure that is used to define the file and its contents, including any sheets and data.

Importing from Excel

To import data from an Excel file using Maatwebsite in Laravel 5, you can follow these steps:

Syntax

Excel::load($filename, function($reader) {
    // read data from $reader
});

Example

use Maatwebsite\Excel\Facades\Excel;

public function importFromExcel()
{
    $filename = storage_path('app/public/uploads/users.xls');

    Excel::load($filename, function($reader) {

        $reader->each(function($row) {

            User::create([
                'name' => $row->name,
                'email' => $row->email
            ]);

        });

    });
}

Output

This will read the data from the "users.xls" file and create new User records in the database.

Explanation

To import data from an Excel file, we call the Excel::load method and pass in the filename of the file we want to import. The closure passed to Excel::load is called once for each row in the sheet, allowing us to process the data and create new records in the database.

Use

Importing data from Excel is useful when you need to bulk upload data or migrate data from one application to another.

Important Points

• The closure passed to Excel::load is called once for each row in the sheet.
• The data from Excel is returned as a Collection of rows.
• You can use Eloquent to create new records in the database.

Exporting to CSV

To export data to CSV using Maatwebsite in Laravel 5, you can follow these steps:

Syntax

Excel::create('filename', function($excel) {
    $excel->sheet('sheetname', function($sheet) {
        // add data to the sheet
    });
})->export('csv');

Example

use Maatwebsite\Excel\Facades\Excel;

public function exportToCSV()
{
    Excel::create('users', function($excel) {

        $excel->sheet('sheet1', function($sheet) {

            $users = User::select('name', 'email')->get();

            $sheet->fromArray($users);

        });

    })->export('csv');
}

Output

This will create a CSV file named "users.csv" with the data from the "users" table.

Explanation

The process for exporting to CSV is almost identical to exporting to Excel. The only difference is that we call $excel->export('csv') instead of $excel->export('xls').

Use

Exporting data to CSV is useful when you need to share data with other applications that support CSV files.

Important Points

• The data you want to export must be in the form of an array or a Collection.
• The Excel::create method takes a closure that is used to define the file and its contents, including any sheets and data.

Summary

In this article, we explored how to use Maatwebsite in Laravel 5 to import and export data to and from Excel and CSV files. Maatwebsite is a powerful package that allows you to easily work with Excel and CSV files, making it perfect for applications that need to create reports or bulk upload data. By following the steps outlined in this article, you can quickly and easily use Maatwebsite in your Laravel 5 applications.