Laravel Excel import and export example from scratch

In this article we will discuss about exporting database data to excel file and importing database data from excel file into database using laravel excel by maatwebsite https://github.com/maatwebsite/Laravel-Excel. Some basic requirements for installation includes PHP > 7.0 and Laravel 5.5.

We will first implement import function and later export function. Export function was introduced in laravel excel 3.0 and import function was added in laravel 3.1.

Features of Laravel Excel includes:

• Easily export collections to Excel.
• Export queries with automatic chunking for better performance.
• Queue exports for better performance.
• Easily export Blade views to Excel.
• Easily import to collections.
• Read the Excel file in chunks.
• Handle the import inserts in batches.

Let’s get started by installing package first and implementing import and export.

Installation

We will use composer to install this package which will download this package and PhpSpreadsheet.

composer require maatwebsite/excel

To publish the config, run the vendor publish command:

php artisan vendor:publish

Database Seeding

We will use users table to export to excel. First of all, we need to seed users table. For this, we will use tinker. Laravel comes with pre-built Factory class for User model, so we will run the following command in tinker. More information can be found about tinker at this article.

factory(App\User::class, 10)->create();

Export

We will implement export function by creating UsersExport class. We will create the export class inside App/Exports by running artisan command:

php artisan make:export UsersExport --model=User

The UsersExport looks like below:

Please note that we will be exporting into excel file from collection. You can implement the same using Eloquent Query and View Html table.
Now, we can call the class from controller as like below:
use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;
use App\Http\Controllers\Controller;

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

This will download the users.xlsx file from the browser.
Import
Similar to export class, we will create export class inside App/Imports using artisan command as below:
php artisan make:import UsersImport --model=User

We will edit model function of the recently created UsersImport class. Our final class looks like below:
 $row[0],
           'email'    => $row[1],
           'password' => Hash::make($row[2]),
        ]);
    }
}

On each row that gets imported, we create a new user. We simply return a new User instance, the saving is handled by the package.
In your UsersController you can now add an import method that will call our UsersImport class.
with('success', 'All good!');
    }
}

This will import the file from default disk. But we can also import from external location. Third parameter is used to give the location of file.
Excel::import(new UsersImport, 'users.xlsx', 's3');

We can also import from file from uploaded files using the following syntax.
Excel::import(new UsersImport, request()->file('your_file'));

Please don't forget to add routes which point to controller method for import and export functions.
This is all for exporting from database and importing to database using maatwebsite laravel-excel package. If you face any issues or have comments regarding this article, please don't forget to leave a comment.

  




Read More Articles